Cache clearing takes too long: What can I do to improve it?

Question

Everytime I clear all the caches, the process takes too long. This is from the log to a prod site I am currently working with.

368829 | [someuser] | dbname | Query | 3376 | updating | DELETE FROM cache_page WHERE expire != 0 AND expire <= 1370891317

As you can see, it was executing for 3376 seconds. I am wondering if this is a known issue, or it is simply because there is just too much in that table.

Also, is there anything I can do to improve this?

Answer 1

I have a pure MySQL approach to clearing the cache_page table quickly.

use dbname
ALTER TABLE cache_page RENAME cache_pageoff;
CREATE TABLE cache_pagenew LIKE cache_pageoff;
INSERT INTO cache_pagenew
    SELECT * FROM cache_pageoff
    WHERE expire > 1370891317
;
ALTER TABLE cache_pagenew RENAME cache_page;
TRUNCATE TABLE cache_pageoff;
DROP TABLE cache_pageoff;

This script will do the following:

• take the cache_page offline
• create a new cache_page
• load the new cache_page with the most current data
• put the new cache_page back online
• remove the old cache_page

Give it a Try !!!

BTW the reason the process is taking long has to do with the range of the delete and the storage engine is use.

• If your cache_page tables is using the MyISAM storage engine, the DELETE query would be traversing almost the entire table and deleting each row.
• If your cache_page tables is using the InnoDB storage engine, this is orders or magnitude worse that MyISAM. Why? All the rows you are deleting are stored in the Undo Logs within ibdata1.
  • When the DELETE query has completed successfully, all the Undo Logs for the cache_page table are purged.
  • If the DELETE query is interrupted by a rollback operation, all the Undo Logs are used to restore the cache_page table in its original state. If cache_page is InnoDB, I strongly recommend not interrupting the DELETE query. Let it goes its full course.