CRP can overload the DB server on a large site, even with the latest workarounds

  • Months ago, I noticed my very large site (over 370k posts) slowing down to a crawl, and then becoming unresponsive until the DB was restarted. I found #sql-temptable-*.MAD and .MAI files collecting in the tmp directory – it was a backlog of queries that the server couldn’t keep up with, as robots and users hit the site. Running mysql -e “show full processlist” | grep -v “Sending data” revealed this, and further diagnosis showed CRP was the WP plugin doing these expensive queries.

    Disabling CRP as soon as the #sql-temptable files begin appearing, allows the DB to catch up, and doesn’t destroy performance. This is a very crude way of protecting the DB server from the CRP plugin – I would rather see the plugin monitor itself, and refuse to run the query if SHOW PROCESSLIST tells it there is a backlog of queries forming.

    CRP is the best plugin of its kind in the “Related Posts” category. The query it runs seems essential to its success story – and so would a proactive check (as suggested) to assure it doesn’t take the DB server down in high-load periods. I’m sure any admin would rather see the plugin briefly/automatically stop listing Related Posts, and play it safe.

    Thanks!

    • This topic was modified 1 month, 2 weeks ago by glenstewart.
Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author Ajay

    (@ajay)

    Hi @glenstewart

    Firstly I’m pleased to hear my plugin running on such a huge site. And thank you for the kind words.

    Do you have the plugin’s inbuilt caching on? When this is turned on, it caches posts for a month by default and subsequent loads tend to be a few minor queries only. You can change that.

    A feature I am adding into CRP Pro is to set the max_execution_time of a mysql query and if it is above a certain time, the query doesn’t run.

    On SHOW PROCESSLIST, I don’t have the ability to test this at my end. When you’re noticing the backlog, how many queries are you seeing generated? It needs to be a sufficiently high number I would expect.

    Thread Starter glenstewart

    (@glenstewart)

    Hello – yes to “Do you have the plugin’s inbuilt caching on?”. In the extreme case, there were over 30 queries backlogged. That’s what happens when the robots and AI crawlers – and actual real people are at work ??

    I presently run an automated job to monitor the DB and it activates/deactivates the CRP plugin as conditions change. Unfortunately, this gives an inconsistent experience to users, but it’s better than nothing.

    Plugin Author Ajay

    (@ajay)

    What automated job are you running to monitor the DB? Is this via PHP? If so, one option could be to short circuit the plugin output.

    I have a feeling that the activate/deactivate is clearing out the plugin cache so it starts from scratch.

    A good way to monitor if the cache is to use Query monitor and you’ll see the first load this gets put into cache and subsequent ones don’t show the main query against the plugin.

    Thread Starter glenstewart

    (@glenstewart)

    I have a feeling that the activate/deactivate is clearing out the plugin cache so it starts from scratch.

    No, I have the setting on to retain cache.

    Here’s the cron job:

    locks=ls -1 /tmp/\#sql-temptable-*.MA[ID] 2>/dev/null
    if [ "$locks" == "" ]
    then
    sudo -u xxx -i -- wp --path=/usr/share/wordpress --quiet plugin status contextual-related-posts | grep "Status: Inactive" && sudo -u xxx -i -- wp --path=/usr/share/wordpress --quiet plugin activate contextual-related-posts
    else
    sudo -u xxx -i -- wp --path=/usr/share/wordpress --quiet plugin status contextual-related-posts | grep "Status: Active" && sudo -u xxx -i -- wp --path=/usr/share/wordpress --quiet plugin deactivate contextual-related-posts
    fi

Viewing 4 replies - 1 through 4 (of 4 total)
  • You must be logged in to reply to this topic.