Category tag causing slow query CPU overload

  • Resolved simonebiffi

    (@simonebiffi)


    15 years, 1 month ago

    Hi there,

    since upgrading to 2.9.1 I noticed an incredible CPU overload apparently due to a slow query revolving around the Category tag.

    I assume this since the CPU report clearly shows an additional series of queries targeting categories’ paths that are consuming quite a lot of extra RAM; all categories are called at once as if by simply visiting a single one of them would trigger a query array on all others simultaneously. These very paths never showed up before the upgrade.

    I ran a Slow Query report on the DB and this is what it gets me:

    ### 1 Query
### Total time: 63, Average time: 63
### Taking 63 seconds to complete
### Rows analyzed 5381
SELECT * FROM <code>chictoday_com_-_daily</code>.<code>wp_posts</code>;
SELECT * FROM <code>chictoday_com_-_daily</code>.<code>wp_posts</code>;

### 1 Query
### Total time: 4, Average time: 4
### Taking 4 seconds to complete
### Rows analyzed 7869
SELECT * FROM <code>chictoday_com_-_daily</code>.<code>wp_postmeta</code>;
SELECT * FROM <code>chictoday_com_-_daily</code>.<code>wp_postmeta</code>;

### 1 Query
### Total time: 2, Average time: 2
### Taking 2 seconds to complete
### Rows analyzed 10809
use chictoday_com_-_daily;
SELECT DISTINCT object_id, post_title FROM wp_term_relationships r, wp_term_taxonomy t, wp_posts p WHERE t.term_id IN (SELECT t.term_id FROM wp_term_relationships r, wp_term_taxonomy t WHERE r.term_taxonomy_id = t.term_taxonomy_id AND t.taxonomy = 'XXX' AND r.object_id = XXX) AND r.term_taxonomy_id = t.term_taxonomy_id AND p.post_status = 'XXX' AND p.ID = r.object_id AND object_id <> XXX AND p.post_type = 'XXX' ORDER BY post_date DESC LIMIT XXX;

use chictoday_com_-_daily;
SELECT DISTINCT object_id, post_title FROM wp_term_relationships r, wp_term_taxonomy t, wp_posts p WHERE t.term_id IN (SELECT t.term_id FROM wp_term_relationships r, wp_term_taxonomy t WHERE r.term_taxonomy_id = t.term_taxonomy_id AND t.taxonomy = 'category' AND r.object_id = 18168) AND r.term_taxonomy_id = t.term_taxonomy_id AND p.post_status = 'publish' AND p.ID = r.object_id AND object_id <> 18168 AND p.post_type = 'post' ORDER BY post_date DESC LIMIT 5;

    I tried any kind of optimization, installed Wp Super Cache, deactivated plugins, optimized header expires, reduced useless calls to repeated queries, and so forth… I’ve been able to minimally reduce the overload of 1/5, but I am clueless as per how to put my hands on tables and DB and I’d prefer to hear an expert opinion on this matter.

    Thanks in advance.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter simonebiffi

    (@simonebiffi)

    Does anybody know anything about it? Help, please ??

    Thread Starter simonebiffi

    (@simonebiffi)

    OK, found the culprit. The Slow Query issue was generated by a bad behaved bot that was trying to copy material from the website. In case anyone stumbles upon it, the mischievous user agent is: BLP_bbot/0.1
    The bot consumes your CPU and you surely don’t want to pay any extra exceeded amount becasue of it, right?

    Should you have to deal with it you can block it by inserting the following code into the robot.txt file:

    User-agent: BLP_bbot/0.1
Disallow: /

    And reinforce your defenses by adding the following to your .htaccess file:

    #get rid of bad bots
RewriteEngine on
RewriteCond %{HTTP_USER_AGENT} ^BLP_bbot/0.1
RewriteRule ^(.*)$ https://go.away/

    And should the bot find a way to break through again, at that point it will be necessary to source its IP and block it in your .htaccess.

    Good luck.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Category tag causing slow query CPU overload’ is closed to new replies.