Skip navigation links

MySQL Forums :: MyISAM :: Optimize table locks the whole database


Advanced Search

Optimize table locks the whole database
Posted by: Dmitry Kochin ()
Date: December 31, 2006 12:46AM

I tried to make non-locking optimize by renaming table. The renamed table is isolated from other tables. No other clients access it. But running OPTIMIZE on this table slows all the queries too much, so they appear to be locked.

I need to optimize several tables in such a manner. But even if I split optimization into several subsequent OPTIMIZE TABLE statements locked queries are not processed between optimize statememets.

Does anyone have a clue how to lower the priority of the optimize? I can afford it taking more time, but I want server to continue process queries during optimization.

Here is the screenshot of MySQL processes:
http://haddan.ru/download/optimize_locked.gif

Here is the code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `haddanarc`.`ManageArchive` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ManageArchive`()
BEGIN
DECLARE lck INTEGER;

CREATE TABLE `tmp_battle` LIKE `battle`;
CREATE TABLE `tmp_battleeffect` LIKE `battleeffect`;
CREATE TABLE `tmp_battlelog` LIKE `battlelog`;
CREATE TABLE `tmp_battlemember` LIKE `battlemember`;
CREATE TABLE `tmp_battlemove` LIKE `battlemove`;
CREATE TABLE `tmp_battleview` LIKE `battleview`;

RENAME TABLE `battle` TO `work_battle`,
`tmp_battle` TO `battle`,
`battleeffect` TO `work_battleeffect`,
`tmp_battleeffect` TO `battleeffect`,
`battlelog` TO `work_battlelog`,
`tmp_battlelog` TO `battlelog`,
`battlemember` TO `work_battlemember`,
`tmp_battlemember` TO `battlemember`,
`battlemove` TO `work_battlemove`,
`tmp_battlemove` TO `battlemove`,
`battleview` TO `work_battleview`,
`tmp_battleview` TO `battleview`;

#Making many long time deletes from the renamed tables.
#These deletes do not lock the server
DELETE ...

#Now optimizing. Unfortunately optimize almost completely locks the server
OPTIMIZE LOCAL TABLE `work_battlemove`;
OPTIMIZE LOCAL TABLE `work_battlelog`;
OPTIMIZE LOCAL TABLE `work_battleeffect`;
OPTIMIZE LOCAL TABLE `work_battlemember`;
OPTIMIZE LOCAL TABLE `work_battle`;
OPTIMIZE LOCAL TABLE `work_battleview`;

RENAME TABLE
`battle` TO `tmp_battle`,
`work_battle` TO `battle`,
`battleeffect` TO `tmp_battleeffect`,
`work_battleeffect` TO `battleeffect`,
`battlelog` TO `tmp_battlelog`,
`work_battlelog` TO `battlelog`,
`battlemember` TO `tmp_battlemember`,
`work_battlemember` TO `battlemember`,
`battlemove` TO `tmp_battlemove`,
`work_battlemove` TO `battlemove`,
`battleview` TO `tmp_battleview`,
`work_battleview` TO `battleview`;

END $$

DELIMITER ;

Options: ReplyQuote


Subject Views Written By Posted
Optimize table locks the whole database 8138 Dmitry Kochin 12/31/2006 12:46AM
Re: Optimize table locks the whole database 3174 Ingo Strüwing 01/02/2007 12:34PM
Re: Optimize table locks the whole database 2900 Dmitry Kochin 01/04/2007 04:52AM
Re: Optimize table locks the whole database 2973 Dmitry Kochin 01/08/2007 02:10PM
Re: Optimize table locks the whole database 2492 Ingo Strüwing 01/17/2007 11:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.