Site Lockups due to Slow Queries (log info inside)

So, I have my site randomly locking up once or twice daily. Normally, VPS resource usage is low, usually 1-2% CPU and 400mb out of 1.4GB memory being used. During lockup, site goes unresponsive and memory consumption goes all the way up to like 1.2 gb and the site either stays locked up or Apache http goes down. This happens regardless of whether traffic is low or high.

Asking my web host to try and diagnose what’s going on, they gave me the below explanation, that there are a lot of slow queries. Can anyone verify (log below) that this might be the cause, and what do I do about it?

Thanks.

“I found a lot of slow queries on the server. You can check the show query log for further information about the same. The location of the log is as follows:

—
/var/log/mysql-slow.log
—

On checking this log, I found the following type of queries.

—
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.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 (18028) ORD
ER BY t.name ASC;
# Time: 120827 11:40:55
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships
AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (‘topic-tag’) AND tr.object_id IN (192476) ORDER BY t.name ASC;
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 46 Rows_examined: 230
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.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 (21810, 196
555, 194798, 194838, 195997) ORDER BY t.name ASC;
# Time: 120827 11:40:56
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 10 Rows_examined: 11870
SELECT SQL_CALC_FOUND_ROWS wp_posts.*, eventStart.meta_value as EventStartDate, eventEnd.meta_value as EventEndDate FROM wp_posts INNER JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)LEFT JOIN wp_postmeta as eventStart ON( wp_posts.ID = eventStart.post_
id ) LEFT JOIN wp_postmeta as eventEnd ON( wp_posts.ID = eventEnd.post_id ) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (2) ) A
ND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘closed’) AND ( eventStart.meta_key = “_EventSta
rtDate” AND eventEnd.meta_key = “_EventEndDate” ) GROUP BY wp_posts.ID ORDER BY eventStart.meta_value ASC LIMIT 0, 10;
# Time: 120827 11:41:07
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 1 Rows_examined: 370
SELECT COUNT(*) FROM wp_posts WHERE post_author = 492 AND post_type = ‘post’ AND (post_status = ‘publish’);
# Time: 120827 11:51:49
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 1632
SELECT COUNT(*) FROM wp_posts WHERE post_author = 616 AND post_type = ‘post’ AND (post_status = ‘publish’);
# Time: 120827 11:53:35
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 15 Rows_examined: 51898
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.po
st_parent = 24129 AND wp_posts.post_type = ‘topic’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘closed’)
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.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 (18028) ORD
ER BY t.name ASC;
# Time: 120827 11:40:55
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships
AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (‘topic-tag’) AND tr.object_id IN (192476) ORDER BY t.name ASC;
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 46 Rows_examined: 230
SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.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 (21810, 196
555, 194798, 194838, 195997) ORDER BY t.name ASC;
# Time: 120827 11:40:56
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 10 Rows_examined: 11870
SELECT SQL_CALC_FOUND_ROWS wp_posts.*, eventStart.meta_value as EventStartDate, eventEnd.meta_value as EventEndDate FROM wp_posts INNER JOIN
wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)LEFT JOIN wp_postmeta as eventStart ON( wp_posts.ID = eventStart.post_
id ) LEFT JOIN wp_postmeta as eventEnd ON( wp_posts.ID = eventEnd.post_id ) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (2) ) A
ND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘closed’) AND ( eventStart.meta_key = “_EventSta
rtDate” AND eventEnd.meta_key = “_EventEndDate” ) GROUP BY wp_posts.ID ORDER BY eventStart.meta_value ASC LIMIT 0, 10;
# Time: 120827 11:41:07
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 1 Rows_examined: 370
SELECT COUNT(*) FROM wp_posts WHERE post_author = 492 AND post_type = ‘post’ AND (post_status = ‘publish’);
# Time: 120827 11:51:49
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 1632
SELECT COUNT(*) FROM wp_posts WHERE post_author = 616 AND post_type = ‘post’ AND (post_status = ‘publish’);
# Time: 120827 11:53:35
# User@Host: txmma2[txmma2] @ localhost []
# Query_time: 5 Lock_time: 0 Rows_sent: 15 Rows_examined: 51898
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.po
st_parent = 24129 AND wp_posts.post_type = ‘topic’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘closed’)
—

Please note that these kind of queries which use multiple joins and sorts cause high resource usage as the data needs to be sorted and resorted and combined, thereby increasing the overall memory usage. It would be advisable to contact a developer and optimize such queries. The simpler the queries, the lower will be the resource usage.”