Re: table lock for deleting and inserting

[Rick James]

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.