MySQL Partitioning. One step before Sharding.

Many of you may consider Sharding to meet your large scale database demands. However, in some cases it seems too early since you may not have the needed bandwidth in the current phase or you just consider to adapt a Sharding COTS solution like Scalebase and you need a solution for mid term.


I don't have time so what should I do?
The answer is simple: you may choose MySQL built-in mechanism named MySQL Partitioning. 
This mechanism partitions your table using one of 4 different rule types. It main solution is keeping all this process hidden from the applicative DBA and the software engineer. Try one of this methods:

1. Range Partitioning: partition by column values. Most fit for known ranges like archive by date range.
2. List Partitioning: similar to the above based on discrete values. Most fit for archive by years.
3. Hash Partitioning: partition by hash function defined by user. Most fit when data ranges are that are unknown, but you know the spread of them. Should be used when you partition a table according to a foreign key. Please notice that in any case, the hashed key must be included in table primary key.
4. Key Partitioning: similar to Hash, but this time the MySQL Server takes care of the function. Most fit that the data distribution is uniform. Partition key should include the primary key.

How do I declare that?

Using DDL. just add few more lines to your CREATE TABLE statement and you will receive the partitioning. For example to split by year use the following statement:

CREATE TABLE grades (

...    

year INT NOT NULL,

...

)

PARTITION BY LIST(year) (

    PARTITION p2009 VALUES IN (2009),

    PARTITION p2010 VALUES IN (2010),

    PARTITION p2011 VALUES IN (2011),

    PARTITION p2012 VALUES IN (2012)

);

What do I get?

Mayflower has presented very nice numbers with 200M records table partitioned to 400 parts. They reached 4000 INSERT/UPDATE and 6000 SELECT statements per second.

Bottom Line

If don't have a lot of time and you need an instant solution, go for Partitioning

Keep Performing,

Moshe Kaplan