Re: CPU Usage during DELETE and OPTIMIZE
InnoDB or MyISAM?
Use an ID and LIMIT, not OFFSET to do the deletes.
Plan A:
While...
DELETE FROM tbl WHERE ... ORDER BY id LIMIT 1000;
sleep 1
...endwhile
However, that will get stuck once you have 1000 rows that don't match the WHERE.
Plan B:
SELECT @a := min(id) FROM TBL;
While...
SELECT @z := id FROM tbl WHERE ... ORDER BY id LIMIT 1000, 1;
DELETE FROM tbl WHERE ... ORDER BY id BETWEEN @a AND @z;
SELECT @a := @z;
sleep 1
...endwhile
What are you deleting on? Why are you using DELETE + INSERT, not REPLACE? Etc.
Do the OPTIMIZE after the DELETEs and before the INSERTs; the table will be smaller then.
In some situations, ALTER TABLE ... is faster than OPTIMIZE, and possibly better. Suggest a simple ordering:
ALTER TABLE tbl ORDER BY some_field;
If you provide these, I might have more suggestions:
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
and surround them with [ code ] and [ / code ]
Subject
Written By
Posted
Re: CPU Usage during DELETE and OPTIMIZE
May 05, 2009 08:14PM
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.