Re: Move from unpartitioned database to partitioned database

[Rick James]

Setting up partitioning in the first place will take a long time.
* Have a long outage, or
* Look into Facebook's way of using triggers to help copy all your data to a new (already partitioned) table, or
* Other -- There are many different ways to minimize downtime; they depend heavily on how the data comes in, whether you do UPDATEs/DELETEs, etc.

If you want 2 years' worth of data, I suggest 27 monthly partitions --

start VALUES LESS THAN ('2000-01-01'),
from201003 VALUES LESS THAN (TO_DAYS('2010-04-01')),
from201004 VALUES LESS THAN (TO_DAYS('2010-05-01')),
...
from201203 VALUES LESS THAN (TO_DAYS('2012-04-01')),
future VALUES LESS THAN MAXVALUE

Rationale...
* "start" will catch any bad dates.
* Note how the partition is named "from..." and the TO_DAYS is the start of the next month.
* There are 24+1 partitions with data (normally). This lets you build tne current month and still have a full 24 months of history.
* "future" exists to catch new data if the monthly maintenance fails. (This saved me once.)

Caution: Virtually all methods need enough spare disk space to hold an extra copy of the table.

Monthly maintenance...
(cron or EVENT -- your choice) Example for what to do on or about 4/1 (the exact timing is not critical):
ALTER TABLE foo REORGANIZE PARTITION future INTO
from201204 VALUES LESS THAN (TO_DAYS('2012-05-01')),
future VALUES LESS THAN MAXVALUE;
ALTER TABLE foo DROP PARTITION from201003;
Notes:
* Check the exact syntax.
* You have to compute 2 partition names and one date.
* Yes, it must be two separate ALTERs, but each is very fast.
* There are severe restrictions on what datatype you can use for RANGE partitioning; above assumes DATETIME.

You could do it with 105 weeks or 732 days, but those seem excessive. Unless... The latest partition (recent data) is a 'hot spot' and may need table scans of the one (or two) partition and/or its index. (This being case #2 for using PARTITIONing; case #1 is the rolling data that you have.)

Be sure to think about the odd rules for UNIQUE and PRIMARY KEYs. If you have an AUTO_INCREMENT, it must be the first column in _some_ index, and you do not need to (in fact, cannot) specify UNIQUE on just that column.

If this is InnoDB, I recommend you have a PRIMARY KEY, and it not start with the date. Rather start with the field you most need to SELECT on. You get "partition pruning" to help with any date range, which is likely to be in the WHERE clause.

In two situations, I took the date range further... My data reached back a few years, but nearly all accesses were for the last few days. I chose to have annual partitions through 2009, then monthly until 3 weeks ago, followed by 3 weekly partitions. It works beautifully. (Yeah, the Perl code to merge partitions, etc, is a bit contorted.)

Let me know if you would like to dig deeper into any of these suggestions.