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

Members

Help

Home

Home » Performance » MySQL » Database unavailability during optimize table, etc.

Show: Today's Messages :: Show Polls :: Message Navigator

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

Re: Database unavailability during optimize table, etc. [message #975 is a reply to message #973 ]

Fri, 23 March 2007 09:18

linuxrunner
Messages: 15
Registered: February 2007
Location: NYC

Junior Member

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.

Report message to a moderator

Previous Topic:	help with index use on query
Next Topic:	Is This ODBC connectivity error or VB application error

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 10 01:30:45 EDT 2009

Total time taken to generate the page: 0.01104 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.