Support » Fixing WordPress » High CPU load caused by slow running queries

  • Resolved guauda

    (@guauda)


    Hello,

    My website has been down for the past 8 hours – at least.

    My hosting provider says it is because of the following queries:

    | 502106 | dicafasd_2 | dedi731.jnb2.host-h.net:49380 | dicafasd_db2 | Query | 2 | Sending data | SELECT post_id, meta_key, meta_value FROM mb_postmeta WHERE post_id IN (92089) ORDER BY meta_id ASC | 0.000 |
    | 502107 | dicafasd_2 | dedi731.jnb2.host-h.net:49382 | dicafasd_db2 | Query | 1 | Creating sort index | SELECT post_id, meta_key, meta_value FROM mb_postmeta WHERE post_id IN (83580,55891,10478,7) ORDER B | 0.000 |
    | 502108 | dicafasd_2 | dedi731.jnb2.host-h.net:49384 | dicafasd_db2 | Query | 1 | Update | INSERT INTO mb_adrotate_tracker (ipaddress, timer, bannerid, stat) VALUES (‘154.73.241.238 | 0.000 |
    | 502110 | dicafasd_2 | dedi731.jnb2.host-h.net:49390 | dicafasd_db2 | Sleep | 0 | |
    | 0.000 |
    | 502111 | dicafasd_2 | dedi731.jnb2.host-h.net:49392 | dicafasd_db2 | Query | 28 | Removing tmp table | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502112 | dicafasd_2 | dedi731.jnb2.host-h.net:49394 | dicafasd_db2 | Query | 28 | Removing tmp table | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502113 | dicafasd_2 | dedi731.jnb2.host-h.net:49396 | dicafasd_db2 | Query | 28 | Removing tmp table | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502114 | dicafasd_2 | dedi731.jnb2.host-h.net:49398 | dicafasd_db2 | Query | 25 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502115 | dicafasd_2 | dedi731.jnb2.host-h.net:49400 | dicafasd_db2 | Query | 26 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502116 | dicafasd_2 | dedi731.jnb2.host-h.net:49404 | dicafasd_db2 | Query | 24 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502117 | dicafasd_2 | dedi731.jnb2.host-h.net:49406 | dicafasd_db2 | Query | 24 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502118 | dicafasd_2 | dedi731.jnb2.host-h.net:49408 | dicafasd_db2 | Query | 24 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502119 | dicafasd_2 | dedi731.jnb2.host-h.net:49410 | dicafasd_db2 | Query | 24 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502120 | dicafasd_2 | dedi731.jnb2.host-h.net:49412 | dicafasd_db2 | Query | 17 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502121 | dicafasd_2 | dedi731.jnb2.host-h.net:49418 | dicafasd_db2 | Query | 14 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502122 | dicafasd_2 | dedi731.jnb2.host-h.net:49420 | dicafasd_db2 | Query | 14 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502123 | dicafasd_2 | dedi731.jnb2.host-h.net:49428 | dicafasd_db2 | Query | 11 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502124 | dicafasd_2 | dedi731.jnb2.host-h.net:49430 | dicafasd_db2 | Query | 11 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502125 | dicafasd_2 | dedi731.jnb2.host-h.net:49432 | dicafasd_db2 | Query | 11 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000 |
    | 502126 | dicafasd_2 | dedi731.jnb2.host-h.net:49434 | dicafasd_db2 | Query | 5 | Creating sort index | SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WH | 0.000

    EXPLAIN SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN (‘post_tag’) AND tt.count > 0 ORDER BY t.name ASC:

    *** row 1 ***
    table: tt
    type: ref
    possible_keys: term_id_taxonomy,taxonomy
    key: taxonomy
    key_len: 98
    ref: const
    rows: 20514
    Extra: Using index condition; Using where; Using temporary; Using filesort
    *** row 2 ***
    table: t
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 8
    ref: dicafasd_db2.tt.term_id
    rows: 1
    Extra: NULL

    A link to the txt doc is added

    The page I need help with: [log in to see the link]

Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator Steve Stern (sterndata)

    (@sterndata)

    Forum Moderator & Support Team Volunteer

    Please install the plugin “Query Monitor”. It should be able to tell you what code is creating that query. Once we have that, we have a better idea of, at least, who to ask about it.

    From an initial review of your log, mb_adrotate_tracker seems to be the key here. If you’re using the AdRotate plugin, I recommend asking at https://wordpress.org/support/plugin/adrotate/#new-post so the plugin’s developers and support community can help you with this.

    Dion

    (@diondesigns)

    Your problem is this query:

    SELECT t.*, tt.* FROM mb_terms AS t INNER JOIN mb_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN (‘post_tag’) AND tt.count > 0 ORDER BY t.name ASC

    The “Extra” in EXPLAIN tells you the issue, which is the creation of a temporary table and then generating an index for the temp table.

    The reason this is happening is the AND tt.count > 0 part of the query. The count column in the _term_taxonomy table doesn’t have an index, and that will result in an extremely slow query.

    You’ll need to determine where the above query is being made, and then either rewrite the query or find another way to achieve whatever is the result of running the query.

    Thread Starter guauda

    (@guauda)

    Thank you guys.

    Issue came from the theme.

    Weekly news:
    https://themeforest.net/item/weekly-news-wordpress-newsmagazine-theme/reviews/9214110

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘High CPU load caused by slow running queries’ is closed to new replies.