I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' state and stay that way indefinitely until it is either killed or until mysqld is restarted. After restarting mysqld the problem goes away, the (identical) query once again takes <1 second to execute. This leads me to believe this is a configuration problem.
How do I go about solving this problem? The query itself is not too intensive, and my server is not experiencing any sudden traffic spikes. The tables themselves are all InnoDB format.
Here is my my.cnf: http://pastebin.com/9UMPxfAr
The query:
SELECT ctt.term_id AS catid, p.ID, p.post_title AS title, GROUP_CONCAT(tt.term_id) as terms_id
FROM (SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type = 'post' AND post_date > '2013-09-09 17:00:00' AND post_date < '2013-09-10 08:14:00') AS p
JOIN wp_postmeta AS pm
ON (p.ID = pm.post_id AND pm.meta_key = 'wpo_sourcepermalink')
JOIN wp_term_relationships AS ctr
ON (p.ID = ctr.object_id)
JOIN wp_term_taxonomy AS ctt
ON (ctr.term_taxonomy_id = ctt.term_taxonomy_id AND ctt.taxonomy = 'category' AND ctt.term_id IN ('8','314','6'))
JOIN wp_term_relationships AS tr
ON (p.ID = tr.object_id)
JOIN wp_term_taxonomy AS tt
ON (tr.term_taxonomy_id = tt.term_taxonomy_id AND (tt.taxonomy = 'post_tag' OR tt.taxonomy = 'post_author'))
GROUP BY tr.object_id
ORDER BY pm.meta_value ASC;
An EXPLAIN of the query: http://pastebin.com/m5ndBfVX
And the output of "SHOW ENGINE INNODB STATUS" when a query is stuck in the 'copying to tmp table' state: http://pastebin.com/h0xv4Sfa
Indices of some of the tables in the query:
/var/tmpfs
? Did you make that yourself? – Michael Hampton Sep 13 '13 at 14:54EXPLAIN
. – Kermit Sep 13 '13 at 14:59