CODEWITHSUNDEEP
mysqlwordpress
user12207241
user12207241

Reputation: 31

High MySQL Usage on WordPress database

I noticed MySQL has a tendency to fluctuate from 9% CPU to 192% CPU in a heartbeat.

I found the database responsible, temporarily disabled it (to verify it was indeed responsible) and MySQL remained at a steady 9%.

The database in question is WordPress, and I recorded the following from MySQL's slow query log:

# Time: 220919 12:33:22
# User@Host: mysite_wp153[mysite_wp153] @ localhost []
# Thread_id: 873  Schema: mysite_wp153  QC_hit: No
# Query_time: 2.172042  Lock_time: 0.000194  Rows_sent: 6  Rows_examined: 1248320
# Rows_affected: 0  Bytes_sent: 155
SET timestamp=1663587202;
SELECT   wpak_posts.ID
                                        FROM wpak_posts  LEFT JOIN wpak_term_relationships ON (wpak_posts.ID = wpak_term_relationships.object_id)
                                        WHERE 1=1  AND (
  wpak_term_relationships.term_taxonomy_id IN (18,19,20,21)
) AND ((wpak_posts.post_type = 'post' AND (wpak_posts.post_status = 'publish')))
                                        GROUP BY wpak_posts.ID
                                        ORDER BY wpak_posts.post_date DESC
                                        LIMIT 0, 6;

Any thoughts/feedback would be appreciated.

Upvotes: 1

Views: 994

Answers (1)

Rick James
Rick James

Reputation: 142296

  • Install WP Index Improvements Plugin

  • Speed up the group-order-by. By making them the same, thereby avoiding an extra sort:

      GROUP BY  wpak_posts.post_date DESC, wpak_posts.ID DESC
  ORDER BY  wpak_posts.post_date DESC, wpak_posts.ID DESC

  • For further discussion, provide the EXPLAIN SELECT ... for the particular query.

Upvotes: 1

Related Questions