Re: table lock for deleting and inserting
Posted by:
Rick James
Date: October 23, 2009 08:12PM
300K rows / (45 minutes * 60 seconds/minute) = 120 rows/second. How fast does your disk run? It can probably handle about 120 I/Os per second. You are using InnoDB. Conclusion: The DELETEs cannot go any faster.
Well, they can. But it will take some changes.
I am assuming it auto_commit is on, therefore each DELETE must be committed before moving on -- that causes at least one disk hit. So, let's batch the DELETEs.
Plan A. BEGIN; do all the deletes; COMMIT; -- Not a good idea; this will block all other operations for a long time.
Plan B. BEGIN; do 100 DELETEs; COMMIT; loop until finished -- This should work faster. I'll guess that it will cut the time down to under 5 minutes.
Please do the following for further analysis:
SHOW VARIABLES LIKE 'innodb%';
I'm looking for the size of the buffer pool (can make a big difference in caching); certain sync settings, etc.
How much RAM do you have?
How big is the table + index? I'll guess 2GB.
Subject
Views
Written By
Posted
6105
October 21, 2009 08:25AM
2876
October 22, 2009 08:02PM
2684
October 23, 2009 11:19AM
Re: table lock for deleting and inserting
2459
October 23, 2009 08:12PM
2966
October 26, 2009 10:21AM
2581
October 26, 2009 08:24PM
2434
October 26, 2009 08:51PM
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.