(root@db_server) [db_name]> ALTER TABLE table_name \ ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; Query OK, 825994826 rows affected (8 days 14 hours 23 min 47.08 sec) Records: 825994826 Duplicates: 0 Warnings: 0
It's too bad we couldn't use all the cores on the machine for the ALTER TABLE, huh?
On the plus side, the file sizes aren't too bad.
-rw-rw---- 1 mysql mysql 1638056067072 2009-05-24 09:23 table_name.ibd
-rw-rw---- 1 mysql mysql 587617796096 2009-05-27 07:14 table_name.ibd
I'll have more to say about XtraDB and the compression options in a later post. But given the interest that my Twitter messages about this big ALTER TABLE generated yesterday, I figured I'd share a bit more detail here.
For anyone doing the math at home, that's going from rougly 1.5TB to 500GB (the new file size is slightly inflated, since this slave managed to replicate about a week's worth of data before I caught it). I was hoping for 4:1 compression and managed about 3:1.
Posted by jzawodn at May 27, 2009 07:34 AM