[ILUG] MySQL partitioned tables/performance

[John McCormac]

On 24/02/2011 09:04, Barry O'Donovan wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>
> On 23/02/11 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.
>
> Yes, exactly. It's /very/ dependant on your scenario, your queries,
> indexing and primary keys. Proper benchmarking may be your only option.

It probably means toasting more HDs. But benchmarking is the only way to 
be certain with this.

> - From your post, you have multiple tables containing historical data:
> take a look at myiasmpack which can lead to performance improvements. See:

I've used myisampack on one version of the database and it worked well 
with compression. The current live version uses packed tables/indices 
and has been serving up to 200K pages a day for the last year or so on a 
P4 box with 4G of RAM.

> Look also at MERGE tables which may be an easier transition along with
> packing with your current scenario: you can place individual MYD/MYI on
> different disks, symlink them to the original database directory and a
> MERGE table will provide a single interface to the lot.

The MERGE tables might be a better option. Again it is more testing and 
benchmarking.

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
**********************************************************