MySql table optimization causing more problems than it solves


I have a heavily accessed table corresponding to user's private messages, with over 10 M rows. When I run the user deletion process, obsolete messages are deleted from the table, typically more than 1000 rows. The problem raises when this table is optimized, because that takes nearly 1 minute, during which the table is totally locked and all queries are delayed. The same applies for other large tables.

The question is, can I simply leave the table permanently un-optimized without major performance issues? Or should I optimize the table at least once a week, at a time with low traffic to prevent annoying my online users?

Best Answer

Yes, the performance will deteriorate - but we can't tell you how quickly, or what level of deterioration is acceptable - since you are already doing this why don't you measure the impact yourself.

Akber is correct in saying that using a cluster would allow you to optimize one system while the other still serves up data - but why not set them up as a master-master pair - then you don't need to upgrade and downgrade when you switch. And there's no need to write temporary records - just wait for the server lag to recover then switch over. It's also a really good solution for backups and, of course, high availability.

Another solution already in wide use for this kind of exercise is zero-downtime schema changes - there are 2 good implementations I know of: one written in Perl by the guys at Percona, and one written in PHP by the Facebook people.