wp_w3tc_cdn_queue filled up, bad query on non-indexed field

  • For the last two days our site has been running slower and slower, until today it ground to a halt. I traced it to 210,000 rows in wp_w3tc_cdn_queue that were causing the database to run at 100% CPU. All were some variant of SELECT remote_path from wp_w3tc_cdn_queue where remote_path=<path>;.

    1. Why are you querying for remote path when you already have the path?
    2. Why are you doing a full table scan on a non-indexed field?

    It appears, based on server performance, that these queries are piggybacked on queries for real data, so when this table exceeded a certain size, it recursively took out legitimate traffic until the whole site timed out. I deleted the table contents, and the site returned.

    At the very least, please set an index on remote_path, but I’m still curious why you are making a query for remote_path when you already have the path. It’s like “select id from foo where id=1” – it’s a total waste. This query is on line 640 of lib/W3/Plugin/Cdn.php.

    https://www.ads-software.com/plugins/w3-total-cache/

Viewing 1 replies (of 1 total)
  • Thread Starter oskapt

    (@oskapt)

    Looking at your code further, it seems that you’re simply trying to determine if a file is _already_ in the queue so that you don’t insert it twice. In that case, a better query would be:

    SELECT id from wp_w3tc_cdn_queue where remote_path=<path>;

    EDIT: My earlier comment about this being an indexed field is actually incorrect. You’re still searching on a field that is non-indexed, so it’ll still be slow. All this does is reduce the size of the response, which is insignificant.

    You need an index on remote_path.

Viewing 1 replies (of 1 total)
  • The topic ‘wp_w3tc_cdn_queue filled up, bad query on non-indexed field’ is closed to new replies.