Database unavailability during optimize table, etc.

MySQL & MariaDB Other MySQL® Questions
1

Hello Peter.

I’ve taken a part in a discussion on phpclub.ru about the OPTIMIZE TABLE ( [URL]http://phpclub.ru/talk/showthread.php?threadid=93499[/URL] , 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.

2

I don’t believe there is anyway to do what you are asking…
What concerns me is this, from mysql docs “This statement requires SELECT and INSERT privileges for the table.” and “Note that MySQL locks the table during the time OPTIMIZE TABLE is running.” So it effectively bypasses the privileges if you don’t want your users locking tables. I had an issue where a user was calling optimize table repeatedly (every 30 minutes) and it took me a while to figure out how they were locking this table. Just my 2 cents.