Problem Statement : 386 GB of MySQL 5.0 Compressed MyISAM tables (Merge engine used) need to be migrated to MySQL 5.1 Compressed/Partitioned tables. Also we had to add a few new columns and change the datatype of few existing columns.
Initial plan : Setup the MySQL 5.1 database with the schema with partitioning to which the data needs to be migrated and then run scripts to copy the .MYD ,.MYI and .frm files to the 5.1 database. Disable the keys using “Alter table <> disable keys“. Then migrate the data using the insert into newtable select * from old table. Then to compress the tables run myisampack and myisamchk.
As per plan we started the migration process. The plan worked perfectly though a bit on the slower side for most of the tables. For few of the tables, the import process kept dragging on and on. It was well past a week since the import was started and the system was writing at a speed of 1 – 1.5 Gig per hour on to disk. There was heavy contention on the key buffer and the behavior was seen on tables which had keys greater than the available buffer and those with where clause in the “insert into <> select from” statments. Then we started getting constrained on time, was looking for avenues to optimize this whole process.
After quite a number of tests and trials , we found the following 2 methods to be faster.
1. Dump the data using select into outfile in the primary key order in small chunks if the table is huge.
2. Alter table <> disable keys.
3. Load the data using load data infile statment.
4. Run myisampack and myisamchk to compress the data and rebuild the indexes respectively.
Method 1, was faster than the original plan.
Method 2 :
Method 2 would be a slight variation of method 1. Instead of “Alter table <> disable keys“, do the following :
For each partition on the table run myisamchk –keys-used=0 -rq <tablename>. This would disable all keys on the table. Now proceed from step 3.
With the second approach we, got a throughput of 1GB data written to disk in 1 minute approx. Will follow up this post with more detailed commands and steps. Whew ! What a week it has been so far !