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 | |+-------------+--------------+------+-----+---------+----------------+Advertisement
Answer
Percona Toolkit’s pt-online-schema-change does this for you. In this case it worked very well.