MySQL Performance: MySQL => Database unavailability during optimize table, etc.

Database unavailability during optimize table, etc. [message #973]

Fri, 23 March 2007 04:54

Wicked
Messages: 1
Registered: March 2007
Location: Novosibirsk, Russia

Junior Member

Hello Peter.

I've taken a part in a discussion on phpclub.ru about the OPTIMIZE TABLE ( http://phpclub.ru/talk/showthread.php?threadid=93499 , if you have no problems with Russian ). People mentioned that the optimize locks a table for a few minutes which can make any high-loaded site unreachable for this time. So I would like to get your advice how people (including me) can optimize tables w/o such long downtime. For example does MySQL have some sort of progressive optimize table which would optimize table by small steps? Or probably some replication is needed so that servers one by one could optimize their tables and then rapidly update their state from a binlog or something else and then return back to the order. What would you recommend?

Of course, optimize table is not the only case of course.

Thanks in advance.

Report message to a moderator