[ILUG] MySQL partitioned tables/performance

[John McCormac]

On 23/02/2011 21:31, Gareth 'bigbro' Eason wrote:
> 	From my (limited) experience of doing this with MySQL - it depends.
> Specifically, it depends on the types of queries you are running and the
> hardware you're using. One of the great advantages of partitioning is
> that you can distribute a single table over multiple discrete storage
> containers, or discrete servers. If you want to do this (and your
> hardware is appropriate) you may see performance gains - though you will
> probably see a performance penalty for simple selects.

That distribution is a nice feature of partitioning but what I am aiming 
for is a horizontal duplication on cheap servers with each server having 
its own complete copy of the database.

> 	If you're willing to do some testing and publish the results you get,
> along with the queries (or at least indicative query types) that you're
> using to test, I'd certainly be interested in the results.

I've fragmented the tables and will be doing some tests on multiple 
small tables versus large alpha-numeric tables over the next few days 
before doing the partition tests. The queries are simple selects based 
on the domain names with minor field sorting but nothing complex. I'll 
probably use the 74m row S table as the test for partitioning.

Regards...jmcc
-- 
**********************************************************
John McCormac  *  e-mail: jmcc at hosterstats.com
MC2            *  web: http://www.hosterstats.com/
22 Viewmount   *  Domain Registrations Statistics
Waterford      *  And Historical DNS Database.
Ireland        *  Over 240 Million Domains Tracked.
IE             *  http://www.hosterstats.com/blog
**********************************************************