Re: System hangs during a big table purge

[Ingo Strüwing]

This sounds to be a similar effect as in the thread "Problems with system locking".

Since you mention the "end" phase, it could also be key cache flushing here. Do you see much I/O in this phase?

I cannot answer your questions as I have no clue what might happen. Even if it is related to the key cache, I can't explain why this could create tenth of seconds in a chunk and then work again.

Do you run with delay_key_write? If not, and you are willing to experiment with it, we should see if the problem is key cache related at all.

How many rows do you delete with each DELETE statement?

I would like to get at this problem, but for you it might be more important to have a working alternative. If your table works like a history table (you clear out all the oldest records without gaps), you might want to look at a MERGE table. I can be more verbose if you like.

Ingo Strüwing, Senior Software Developer - Storage Engines
MySQL AB, www.mysql.com