Mysql can do partitioning on the fly :)

Posted by KsI on July 23, 2013 Leave a comment (0) Go to comments

Mysql tuning in action…
Yesterday me, and our development team made some tuning of one old project we have couple of tables with 1-10 million records.
It`s NOT BIGDATA, but application makes huge writes to this table.
Only current date records, but you understand partitioning affects index size.
Table can be partitioned by date field. So how to do it:

make sure that dt not null.
recreate primary key. Field for partition should be part of primary key.
create partition for old years and partition for every month.

ALTER TABLE legacy_site_stats MODIFY dt DATE NOT NULL;

ALTER TABLE legacy_site_stats DROP PRIMARY KEY, ADD PRIMARY KEY (id, dt);

ALTER TABLE legacy_site_stats PARTITION BY RANGE COLUMNS (dt) ( PARTITION p2009 VALUES LESS THAN ('2009-01-01'), PARTITION p2010 VALUES LESS THAN ('2010-01-01'), PARTITION p2011 VALUES LESS THAN ('2011-01-01'), PARTITION p2012 VALUES LESS THAN ('2012-01-01'),
PARTITION p201212 VALUES LESS THAN ('2013-01-01'),
PARTITION p201301 VALUES LESS THAN ('2013-02-01'),
PARTITION p201305 VALUES LESS THAN ('2013-06-01'),
PARTITION p201306 VALUES LESS THAN ('2013-07-01'),
PARTITION p201307 VALUES LESS THAN ('2013-08-01'),
PARTITION p201308 VALUES LESS THAN ('2013-09-01'),
PARTITION p201309 VALUES LESS THAN ('2013-10-01'),
PARTITION p201310 VALUES LESS THAN ('2013-11-01'),
PARTITION p201311 VALUES LESS THAN ('2013-12-01'),
PARTITION p201312 VALUES LESS THAN ('2014-01-01'),
PARTITION p201401 VALUES LESS THAN ('2014-02-01'),
PARTITION p201402 VALUES LESS THAN ('2014-03-01'),
PARTITION p201403 VALUES LESS THAN ('2014-04-01'),
PARTITION p201404 VALUES LESS THAN ('2014-05-01'),
PARTITION p201405 VALUES LESS THAN ('2014-06-01'),
PARTITION p201406 VALUES LESS THAN ('2014-07-01'),
PARTITION p201407 VALUES LESS THAN ('2014-08-01'),
PARTITION p201408 VALUES LESS THAN ('2014-09-01'),
PARTITION p201409 VALUES LESS THAN ('2014-10-01'),
PARTITION p201410 VALUES LESS THAN ('2014-11-01'),
PARTITION p201411 VALUES LESS THAN ('2014-12-01'),
PARTITION p201412 VALUES LESS THAN ('2015-01-01'),
PARTITION p201501 VALUES LESS THAN ('2015-02-01'),
PARTITION p201502 VALUES LESS THAN ('2015-03-01'),
PARTITION p201503 VALUES LESS THAN ('2015-04-01'),
PARTITION p201504 VALUES LESS THAN ('2015-05-01'),
PARTITION p201505 VALUES LESS THAN ('2015-06-01'),
PARTITION p201506 VALUES LESS THAN ('2015-07-01'),
PARTITION p201507 VALUES LESS THAN ('2015-08-01'),
PARTITION p201508 VALUES LESS THAN ('2015-09-01'),
PARTITION p201509 VALUES LESS THAN ('2015-10-01'),
PARTITION p201510 VALUES LESS THAN ('2015-11-01'),
PARTITION p201511 VALUES LESS THAN ('2015-12-01'),
PARTITION p6 VALUES LESS THAN MAXVALUE );

Recovery plan:

ALTER TABLE legacy_site_stats REMOVE PARTITIONING;

It takes 1.5 minutes on
32 gb Ram
Intel(R) Xeon(R) CPU E31245 @ 3.30GHz
Sata software mirror 2 HDD
for 8 000 000 records. I mean very fast.
Recovery takes almost same time.

It`s working!
No database deadlocks for now 🙂