Option for MySQL partitioning to make cleanup faster/more efficient; index on "time"
|Status:||Needs Decision||Start date:||05/23/2012|
|Branch:||Affected Dashboard version:|
I understand that not everyone is using MySQL….however, for those that are….the current procedure to clean up the database is to run commands that DELETE rows, and then run OPTIMIZE to defragment.
There is a better way. In MySQL 5.1 and up, you can use partitioning. What partitioning does is creates one physical file per partition, and it means you can easily do a DROP PARTITION statement and it takes only the time it takes to delete the file (MySQL does this for you), and there’s no defragmentation (or lengthy locking!) to deal with.
Basically I’d recommend making one partition per day, and then the cleanup script can do a DROP partition.
Even if you don’t go this route, I noticed that there’s no appropriate index. This command takes a LONG time:
reports WHERE (time < ‘2012-05-19 13:54:11’)
Because there’s no index on time. So at the very least, there should be an index on time.
Here’s and example of partitioning:
mysql> select TO_DAYS(‘2012-05-23’); +———————————–+ | TO_DAYS(‘2012-05-23’) | +———————————–+ | 735011 | +———————————–+ 1 row in set (0.31 sec)
ALTER TABLE reports
PARTITION BY RANGE( TO_DAYS(`time`) ) ( PARTITION p20120522 VALUES LESS THAN (735011), PARTITION p20120523 VALUES LESS THAN (735012), PARTITION p20120524 VALUES LESS THAN (735013), PARTITION p20120525 VALUES LESS THAN (735014) );
And then when you want to cleanup the 5/22 partition, you do ALTER TABLE reports DROP PARTITION p20120522;
To add new partitions, do: ALTER TABLE ADD PARTITION (PARTITION p20120526 VALUES LESS THAN (735015));
I would add cron scripts or MySQL events to do the adding of partitions.
#2 Updated by Sheeri Cabral over 1 year ago
There are some gotchas with MySQL partitioning that I’m running into, but in the end, there’s a problem, because of the foreign key from resource_statuses to reports – MySQL doesn’t yet support partitioning and foreign keys on the same table. :(
But it’s something to keep an eye out for…..and I’d definitely put an index on