MySQL Forums
Forum List  »  MyISAM

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
9222
December 31, 2006 12:46AM


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.