Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

http://pastebin.com/XtTBL7Xr

http://pastebin.com/NamVSu4F

share|improve this question

migrated from serverfault.com Sep 13 '13 at 14:56

This question came from our site for professional system and network administrators.

    
When this occurs next, run "SHOW ENGINE INNODB STATUS" and return the output, also, check /var/tmpfs for running out of space and/or inodes (and the system memory usage). –  Trent Lloyd Sep 13 '13 at 4:18
    
What is that /var/tmpfs? Did you make that yourself? –  Michael Hampton Sep 13 '13 at 14:54
    
@MichaelHampton That comes from option #2 in the answer to this question: stackoverflow.com/questions/7532307/… –  Guillochon Sep 13 '13 at 14:58
    
Please post move your query into the question, and post the output from EXPLAIN. –  Kermit Sep 13 '13 at 14:59
    
OK, I have posted all the information that has been requested. Could someone give me some ideas of what to try? The problem is still ongoing... –  Guillochon Sep 19 '13 at 21:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.