Wordpress crash on slow queries related to wp_term and wp_term_taxonomy

Question

I have a WordPress blog with around 90K articles. The problem is I get frequent crashes on the server and browsing is slow due to some slow queries on the website. I am suspecting the problem is with the WordPress tags since I have millions of them stored in the database. Any help would be very appreciated. Please check the attached mysqlslowquerydump:

wp_term_relationships 7,634,252 rows wp_yoast_indexable 4,299,877 rows wp_term_taxonomy 4,133,254 rows wp_terms 4,133,260 rows wp_postmeta 2,491,310 rows

P.S.: I have disabled the Yoast plugin and I am not planning to use it anymore.

Reading mysql slow query log from /var/lib/mysql/hostname-slow.logCount: 1  Time=51.98s (51s)  Lock=95.40s (95s)  Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
      OPTIMIZE TABLE `wp_term_relationships`, `wp_yoast_indexable`, `wp_terms`, `wp_yoast_indexable_hierarchy`, `wp_postmeta`, `wp_usermeta`, `wp_asp_index`, `wp_yoast_seo_links`, `wp_woocommerce_order_itemmeta`, `wp_wc_download_log`, `wp_posts`
    
    Count: 1  Time=49.95s (49s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.*, tt.*, tr.object_id FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (278733) ORDER BY t.name ASC
    Count: 1  Time=49.95s (49s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost  SELECT  t.*, tt.*, tr.object_id FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (339484) ORDER BY t.name ASC
    Count: 1  Time=49.95s (49s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') AND t.slug IN ('download-4-files-firmware-a115az-u2-10q')
    
    Count: 1  Time=49.93s (49s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('wp_theme') AND t.name IN ('mharty')
    
    Count: 1  Time=49.69s (49s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.*, tt.*, tr.object_id FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category', 'post_tag', 'post_format') AND tr.object_id IN (355657, 355658, 355659, 355660, 355661, 355673, 355693, 355696, 355699, 355703) ORDER BY t.name ASC
    
    Count: 1  Time=46.20s (46s)  Lock=0.00s (0s)  Rows_sent=4261696.0 (4261696), Rows_examined=4261696.0 (4261696), Rows_affected=0.0 (0), root[root]@localhost
      SELECT `id`, `permalink`, `permalink_hash`, `object_id`, `object_type`, `object_sub_type`, `author_id`, `post_parent`, `title`, `description`, `breadcrumb_title`, `post_status`, `is_public`, `is_protected`, `has_public_posts`, `number_of_pages`, `canonical`, `primary_focus_keyword`, `primary_focus_keyword_score`, `readability_score`, `is_cornerstone`, `is_robots_noindex`, `is_robots_nofollow`, `is_robots_noarchive`, `is_robots_noimageindex`, `is_robots_nosnippet`, `twitter_title`, `twitter_image`, `twitter_description`, `twitter_image_id`, `twitter_image_source`, `open_graph_title`, `open_graph_description`, `open_graph_image`, `open_graph_image_id`, `open_graph_image_source`, `open_graph_image_meta`, `link_count`, `incoming_link_count`, `prominent_words_version`, `created_at`, `updated_at`, `blog_id`, `language`, `region`, `schema_page_type`, `schema_article_type`, `has_ancestors`, `estimated_reading_time_minutes` FROM `ha***_ar`.`wp_yoast_indexable`
    
    Count: 1  Time=42.09s (42s)  Lock=0.00s (0s)  Rows_sent=275500.0 (275500), Rows_examined=8892513.0 (8892513), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%de%' ORDER BY t.name ASC
    
    Count: 1  Time=41.28s (41s)  Lock=0.00s (0s)  Rows_sent=18674.0 (18674), Rows_examined=8378861.0 (8378861), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%dea%' ORDER BY t.name ASC
    
    Count: 1  Time=40.20s (40s)  Lock=0.00s (0s)  Rows_sent=42440.0 (42440), Rows_examined=8426393.0 (8426393), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%boot%' ORDER BY t.name ASC
    
    Count: 14  Time=39.81s (557s)  Lock=0.00s (0s)  Rows_sent=4104652.6 (57465136), Rows_examined=16487204.4 (230820862), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_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
    
    Count: 2  Time=37.62s (75s)  Lock=0.00s (0s)  Rows_sent=16781.0 (33562), Rows_examined=8375075.0 (16750150), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%dead%' ORDER BY t.name ASC
    
    Count: 1  Time=36.31s (36s)  Lock=0.00s (0s)  Rows_sent=1.0 (1), Rows_examined=8341515.0 (8341515), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%emcp%' ORDER BY t.name ASC
    
    Count: 1  Time=35.89s (35s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=8341513.0 (8341513), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%emcp cid%' ORDER BY t.name ASC
    
    Count: 1  Time=35.07s (35s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=8341513.0 (8341513), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%emmc cid%' ORDER BY t.name ASC
    
    Count: 4  Time=34.53s (138s)  Lock=0.00s (0s)  Rows_sent=1.0 (4), Rows_examined=8328579.0 (33314316), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT COUNT(T.term_id)
      FROM wp_term_taxonomy AS T
      LEFT JOIN wp_yoast_indexable AS I
      ON T.term_id = I.object_id
      AND I.object_type = 'term'
      AND I.link_count IS NOT NULL
      WHERE I.object_id IS NULL
      AND T.taxonomy IN ('category', 'post_tag', 'post_format', 'product_cat', 'product_tag', 'product_shipping_class')
    
    Count: 1  Time=34.38s (34s)  Lock=0.00s (0s)  Rows_sent=12281.0 (12281), Rows_examined=8366075.0 (8366075), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%dead boot%' ORDER BY t.name ASC
    
    Count: 2  Time=33.86s (67s)  Lock=0.00s (0s)  Rows_sent=3397.0 (6794), Rows_examined=8348307.0 (16696614), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%mtk%' ORDER BY t.name ASC
    
    Count: 2  Time=33.14s (66s)  Lock=0.00s (0s)  Rows_sent=50559.0 (101118), Rows_examined=8442631.0 (16885262), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%emmc%' ORDER BY t.name ASC
    
    Count: 1  Time=32.11s (32s)  Lock=0.00s (0s)  Rows_sent=4455.0 (4455), Rows_examined=8350423.0 (8350423), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%dead boor%' ORDER BY t.name ASC
    
    Count: 1  Time=30.22s (30s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=8341513.0 (8341513), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.term_id, tt.parent, tt.count, t.name, tt.taxonomy FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_idWHERE tt.taxonomy IN ('post_tag') AND t.name LIKE '%mtk cid%' ORDER BY t.name ASC
    
    Count: 3  Time=29.50s (88s)  Lock=0.00s (0s)  Rows_sent=1.0 (3), Rows_examined=8328579.0 (24985737), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT COUNT(term_id)
      FROM wp_term_taxonomy AS T
      LEFT JOIN wp_yoast_indexable AS I
      ON T.term_id = I.object_id
      AND I.object_type = 'term'
      AND I.permalink_hash IS NOT NULL
      WHERE I.object_id IS NULL
      AND taxonomy IN ('category', 'post_tag', 'post_format', 'product_cat', 'product_tag', 'product_shipping_class')
    
    Count: 1  Time=23.82s (23s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=4173251.0 (4173251), Rows_affected=64169.0 (64169), root[root]@localhost
      DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 )
    
    Count: 1  Time=22.91s (22s)  Lock=0.11s (0s)  Rows_sent=0.0 (0), Rows_examined=0.0 (0), Rows_affected=0.0 (0), root[root]@localhost
      OPTIMIZE TABLE `wp_term_taxonomy`
    
    Count: 1  Time=16.60s (16s)  Lock=0.00s (0s)  Rows_sent=49.0 (49), Rows_examined=9862255.0 (9862255), Rows_affected=0.0 (0), root[root]@localhost
      SELECT * FROM `wp_term_relationships` WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy)
    
    Count: 1  Time=14.98s (14s)  Lock=0.00s (0s)  Rows_sent=4173251.0 (4173251), Rows_examined=4173251.0 (4173251), Rows_affected=0.0 (0), root[root]@localhost
      SELECT `term_id`, `name`, `slug`, `term_group` FROM `ha***_ar`.`wp_terms`
    
    Count: 1  Time=14.30s (14s)  Lock=0.00s (0s)  Rows_sent=3.0 (3), Rows_examined=8346499.0 (8346499), Rows_affected=0.0 (0), root[root]@localhost
      SELECT * FROM `wp_term_taxonomy` WHERE term_id not IN (SELECT term_id FROM wp_terms)
    
    Count: 1  Time=12.57s (12s)  Lock=0.00s (0s)  Rows_sent=4173248.0 (4173248), Rows_examined=4173248.0 (4173248), Rows_affected=0.0 (0), root[root]@localhost
      SELECT `term_taxonomy_id`, `term_id`, `taxonomy`, `description`, `parent`, `count` FROM `ha***_ar`.`wp_term_taxonomy`
    
    Count: 1  Time=11.12s (11s)  Lock=0.00s (0s)  Rows_sent=5689007.0 (5689007), Rows_examined=5689007.0 (5689007), Rows_affected=0.0 (0), root[root]@localhost
      SELECT `object_id`, `term_taxonomy_id`, `term_order` FROM `ha***_ar`.`wp_term_relationships`
    
    Count: 1  Time=8.97s (8s)  Lock=0.00s (0s)  Rows_sent=4069496.0 (4069496), Rows_examined=4069496.0 (4069496), Rows_affected=0.0 (0), root[root]@localhost
      SELECT `indexable_id`, `ancestor_id`, `depth`, `blog_id` FROM `ha***_ar`.`wp_yoast_indexable_hierarchy`
    
    Count: 1  Time=8.70s (8s)  Lock=0.00s (0s)  Rows_sent=10.0 (10), Rows_examined=4104655.0 (4104655), Rows_affected=0.0 (0), ha***_ar[ha***_ar]@localhost
      SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') ORDER BY tt.count DESC LIMIT 10
    
    Count: 1  Time=8.41s (8s)  Lock=0.00s (0s)  Rows_sent=2489262.0 (2489262), Rows_examined=2489262.0 (2489262), Rows_affected=0.0 (0), root[root]@localhost
      SELECT `meta_id`, `post_id`, `meta_key`, `meta_value` FROM `ha***_ar`.`wp_postmeta`
    
    Count: 1  Time=7.66s (7s)  Lock=0.00s (0s)  Rows_sent=194762.0 (194762), Rows_examined=194762.0 (194762), Rows_affected=0.0 (0), root[root]@localhost
      SELECT `ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count` FROM `ha***_ar`.`wp_posts`
    
    Count: 1  Time=5.93s (5s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=4173248.0 (4173248), Rows_affected=64172.0 (64172), root[root]@localhost
      DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms)

WP's "meta" tables are inadequately indexed. This plugin speed up some of those queries a bunch: wordpress.org/plugins/index-wp-mysql-for-speed — Rick James, Aug 14, 2021 at 18:03

Rick James · Accepted Answer · 2021-08-14 18:17:50Z

Get rid of OPTIMIZE TABLE. It is slow, and rarely does anything useful.

It seems that most of the 49-second queries are blocked by the OPTIMIZE. So, getting rid of the OPTIMIZE would probably let the other queries go at normal speed -- much faster.

(stackoverflow.com is probably a better place for WP issues.)

This particular query dumps out about 4M rows! Do you really need that, especially as often as it is run? What will the client do with that much data?

SELECT  t.term_id, tt.parent, tt.count, tt.taxonomy
    FROM  wp_terms AS t
    INNER JOIN  wp_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

As for this:

SELECT  COUNT(T.term_id)
    FROM  wp_term_taxonomy AS T
    LEFT JOIN  wp_yoast_indexable AS I  ON T.term_id = I.object_id
      AND  I.object_type = 'term'
      AND  I.link_count IS NOT NULL
    WHERE  I.object_id IS NULL
      AND  T.taxonomy IN ('category', 'post_tag', 'post_format',
                'product_cat', 'product_tag', 'product_shipping_class'
                         )

please provide SHOW CREATE TABLE wp_yoast_indexable`; it sounds like it does not have any useful indexes!

Deal with the above, flush the slowlog, see if there are other problems, then provide a fresh pt-query-digest or mysqldumpslow -s t for further critique.

Stack Exchange Network

Wordpress crash on slow queries related to wp_term and wp_term_taxonomy

1 Answer 1

Your Answer

Hot Network Questions

Wordpress crash on slow queries related to wp_term and wp_term_taxonomy

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions