database maxing cpu usage after upgrade to 3.1

  • Hello,

    I’ve been frantically working with my host to help resolve this issue I’ve been having. My dedicated server hosted through Rackspace is continually overloading. The support claims this is the main query causing the issue:

    | 675957 | user | localhost | database | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (953) ) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 15

    They sent me a huge list of similar queries that had a locked state. They aren’t being to helpful since they seem to only manage hardware.

    I’ve disabled all plugins.
    Replaced w3 total cache with wp-super cache.
    I’d prefer not activating the twentyten theme since it’s a very active site and I’d risk losing even more visitors by changing the theme.

    List of queries sent from host:

    +——–+——–+———–+——–+———+——+———————-+——————————————————————————————————+
    | Id | User | Host | db | Command | Time | State | Info |
    +——–+——–+———–+——–+———+——+———————-+——————————————————————————————————+
    | 675356 | user | localhost | db | Query | 131 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675379 | user | localhost | db | Sleep | 30 | | |
    | 675409 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675412 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    …
    FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675572 | user | localhost | db | Query | 7 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675573 | user | localhost | db | Query | 31 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675574 | user | localhost | db | Query | 27 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675576 | user | localhost | db | Query | 75 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675578 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675579 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675581 | user | localhost | db | Query | 75 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675582 | user | localhost | db | Query | 75 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675583 | user | localhost | db | Query | 31 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675589 | user | localhost | db | Query | 75 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675594 | user | localhost | db | Query | 27 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675620 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675621 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675636 | user | localhost | db | Query | 27 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675638 | user | localhost | db | Query | 31 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675650 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675665 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675668 | user | localhost | db | Query | 31 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675691 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675693 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675730 | user | localhost | db | Query | 74 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675735 | user | localhost | db | Query | 73 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675736 | user | localhost | db | Query | 69 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675738 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675739 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675740 | user | localhost | db | Query | 70 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675742 | user | localhost | db | Query | 69 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675745 | user | localhost | db | Query | 70 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675746 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675747 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675750 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675751 | user | localhost | db | Query | 6 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675752 | user | localhost | db | Query | 68 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675753 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675754 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675757 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675759 | user | localhost | db | Query | 53 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675774 | user | localhost | db | Query | 31 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675775 | user | localhost | db | Query | 30 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675782 | user | localhost | db | Query | 26 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675783 | user | localhost | db | Query | 26 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675787 | user | localhost | db | Query | 8 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675793 | user | localhost | db | Query | 19 | Locked | UPDATE wp_posts SET comment_count = 28 WHERE ID = 86827 |
    | 675795 | user | localhost | db | Query | 17 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = ‘post’ AND |
    | 675797 | user | localhost | db | Query | 16 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675799 | user | localhost | db | Query | 15 | Locked | SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND YEAR(wp_posts.post_date)=’2011′ AND MONTH(wp_posts |
    | 675801 | user | localhost | db | Query | 12 | Locked | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name = ‘1e36ecec-afbb-4236-beb2-84838731e |
    | 675802 | user | localhost | db | Query | 12 | Locked | SELECT ID, post_name, post_parent FROM wp_posts WHERE post_name = ‘fd5d69bd-252d-4052-87e2-03a0c359e |
    | 675803 | user | localhost | db | Query | 12 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675804 | user | localhost | db | Query | 11 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675805 | user | localhost | db | Query | 10 | Locked | SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND YEAR(wp_posts.post_date)=’2010′ AND MONTH(wp_posts |
    | 675806 | user | localhost | db | Query | 9 | Locked | SELECT * FROM wp_posts WHERE ID = 85192 LIMIT 1 |
    | 675807 | user | localhost | db | Query | 7 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675808 | user | localhost | db | Query | 8 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675809 | user | localhost | db | Query | 8 | Locked | SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND YEAR(wp_posts.post_date)=’2009′ AND MONTH(wp_posts |
    | 675810 | user | localhost | db | Query | 6 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675811 | user | localhost | db | Query | 5 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675812 | user | localhost | db | Query | 5 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675813 | user | localhost | db | Query | 5 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675814 | user | localhost | db | Query | 5 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675815 | user | localhost | db | Query | 5 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675816 | user | localhost | db | Query | 4 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675817 | user | localhost | db | Query | 4 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675818 | user | localhost | db | Query | 4 | Locked | SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND YEAR(wp_posts.post_date)=’2011′ AND MONTH(wp_posts |
    | 675819 | user | localhost | db | Query | 4 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675820 | user | localhost | db | Query | 3 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675821 | user | localhost | db | Query | 2 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675822 | user | localhost | db | Query | 1 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675823 | user | localhost | db | Query | 1 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675824 | user | localhost | db | Query | 2 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675825 | user | localhost | db | Query | 2 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675826 | user | localhost | db | Query | 1 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675827 | user | localhost | db | Query | 1 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675828 | user | localhost | db | Query | 1 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675829 | user | localhost | db | Query | 1 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675830 | user | localhost | db | Query | 1 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675831 | user | localhost | db | Query | 1 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675832 | user | localhost | db | Query | 0 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675833 | user | localhost | db | Query | 0 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675834 | user | localhost | db | Query | 0 | Locked | SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts. |
    | 675838 | user | localhost | | Query | 0 | | show processlist |
    +——–+——–+———–+——–+———+——+———————-+————————————————————–

    Thanks in advance,

Viewing 15 replies - 16 through 30 (of 30 total)

  • ubuntu 10.04 xen server
    4 cpus, 6gb ram

    it chugs on that query without any load on the server at all

    FYI, on mySQL 5.0 the old query would take around 3-5seconds compared with 180sec on mySQL 5.1. YMMV

    I do not understand this statement….

    this query:

    SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND ( wp_posts.ID IN (
					SELECT object_id
					FROM wp_term_relationships
					WHERE term_taxonomy_id IN (4594,4600)
					GROUP BY object_id HAVING COUNT(object_id) = 2
				) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10;

    when run on the 5.0 server would take around 3-5seconds
    when run on thr 5.1 server it would take 3 minutes

    on the same data

    oh so you are saying it is a MySQL issue vs. depreciated code?

    the code is new in version WP3.1
    it’s just an inefficient way of doing the subquery

    the new subquery works faster on 5.0 as well… i’ll submit this to trac when i work out how to use it.. ??

    I am running MySQL 5.1.50 on one port and 5.1.11 on a different port and really haven’t seen anything that would indicate any issues other than what has been stated above regarding plugins… alot of the plugins still through depreciated code errors in the php logs…

    i am running 5.1.41 with 40k+ rows in wp-posts
    it’s just to do with the way the db server is optimising the query
    it doesn’t have anything to do with plugins?!
    it’s the actual wordpress code in taxonomy.php

    We’ve been experiencing the same issue on an installation with 60k+ posts. After the upgrade to 3.1, database CPU usage regularly maxes with the slow queries log showing SELECT SQL_CALC_FOUND_ROWS wp_posts.* etc as the culprit.

    We’re runnning MySQL 5.0.51a.

    I’ve attempted your fix to see how it works.

    Would be keen if you’d add it to trac to see comments from the developers.

    I gave it a whirl… I have several sites the two main sites are the two I made the above change… I also run two seperate MySQL 5.1.50 on one port and 5.1.11 on a different port

    What I have noticed is, once the the pages have been cached (I am running WP-Super Cache) that is when the performance it appears to improve… otherwise, if I clear the cache, the load on the CPU is about the same on the first request for a “non-cached” page…

    Interesting… I will leave the changes as it for a while and check my logs… I have only made the view of the pages using Chrome however, I have IE 9, FireFlox (no idea which version, just not my favorite), Safari, and Opera… I will test each browser and post back later…

    An hour after making this change, I have not had a single entry in my slow query log.

    That’s a tremendous improvement … I was seeing hundreds an hour and they were killing our database servers (which are pretty powerful, hardware-wise).

    My only nervousness is that the change affects WordPress functionality … but my testing so far shows no impact on features/our live site.

    Thanks fudj … so far, so good!

    @xtraxtra great news!
    i’m not a wordpress guru, but i think it’s one of the plugins that is calling this particular query.

    @xtraxtra https://core.trac.www.ads-software.com/ticket/16706

    I would have to agree with fudj regarding this… as I too, am leary about making changes to the core without fully knowing the consequences… however, so far I haven’t noticed any problems… It would be nice if a wordpress guru would chime in on this one…

    @fugj: Great to see that it’s a known issue and the fixes are going to be applied in an upcoming release. I’m applying the three patches to our install in the meantime. Thanks again!

    @rsgrone: Check out the ticket fudj references above. There’s three patches relevant to this issue, one with includes fudj’s suggestions (albeit a little more efficient).

    hi there, recently i’ve noticed my server keep on spiking with cpu usage. my host give me a large list of similar stuff as threadstarter.

    just wanted to know if this issue is already fixed in 3.3.1? thanks

Viewing 15 replies - 16 through 30 (of 30 total)
  • The topic ‘database maxing cpu usage after upgrade to 3.1’ is closed to new replies.