How to run OPTIMIZE TABLE with the least downtime

I have a MySQL 5.5 DB of +-40GB on a 64GB RAM machine in a production environment. All tables are InnoDB. There is also a slave running as a backup.

One table – the most important one – grew to 150M rows, inserting and deleting became slow. To speed up inserting and deleting I deleted half of the table. This did not speed up as expected; inserting and deleting is still slow.

I’ve read that running OPTIMIZE TABLE can help in such a scenario. As I understand this operation will require a read lock on the entire table and optimizing the table might take quite a while on a big table.

What would be a good strategy to optimize this table while minimizing downtime?

EDIT The specific table to be optimized has +- 91M rows and looks like this:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| channel_key | varchar(255) | YES  | MUL | NULL    |                |
| track_id    | int(11)      | YES  | MUL | NULL    |                |
| created_at  | datetime     | YES  |     | NULL    |                |
| updated_at  | datetime     | YES  |     | NULL    |                |
| posted_at   | datetime     | YES  |     | NULL    |                |
| position    | varchar(255) | YES  | MUL | NULL    |                |
| dead        | int(11)      | YES  |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+

Answer

Percona Toolkit’s pt-online-schema-change does this for you. In this case it worked very well.

Advertisement

Answer