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 - 1 through 15 (of 30 total)
  • Thread Starter user65

    (@user65)

    I am also getting this error consistently in the mysql logs:

    /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_6e35_43.MYI’; try to repair it

    Basically the host is telling me that the tmp table is filling up from the wp_post query.

    anyone else having these issues?

    here is my list of errors from mysqld:
    110324 14:25:59 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_32.MYI’; try to repair it
    110324 14:25:59 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_82.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_67.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_68.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_71.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_35.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_13.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_15.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_49.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_86.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_39.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_33.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_5.MYI’; try to repair it
    110324 14:26:20 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_0.MYI’; try to repair it
    110324 14:26:41 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_36.MYI’; try to repair it
    110324 14:26:41 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_54.MYI’; try to repair it
    110324 14:26:41 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_72.MYI’; try to repair it
    110324 14:26:41 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_46.MYI’; try to repair it
    110324 14:26:41 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_24.MYI’; try to repair it
    110324 14:26:41 [ERROR] /usr/libexec/mysqld: Incorrect key file for table ‘/tmp/#sql_3713_63.MYI’; try to repair it

    Thread Starter user65

    (@user65)

    Here is what my host is recommending:

    For example, the following queries are being executed rather frequently and even using indexes are scanning over 25,561 rows and only returning 10 back to the client.

    # User@Host: pnnsql[pnnsql] @ localhost []
    # Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 25561
    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, 10;

    mysql> explain 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, 10 \G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: wp_posts
    type: index
    possible_keys: PRIMARY,type_status_date
    key: PRIMARY
    key_len: 8
    ref: NULL
    rows: 27077
    Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: wp_term_relationships
    type: eq_ref
    possible_keys: PRIMARY,term_taxonomy_id
    key: PRIMARY
    key_len: 16
    ref: dbname.wp_posts.ID,const
    rows: 1
    Extra: Using index
    2 rows in set (0.00 sec)

    You may benefit from adding a separate entry for the wp_posts.post_date field. This is being referenced in the type_status_date index, but not in the leftmost order from where it can be properly utilised.

    Basically any time I schedule or mess with the timestamp of a post and save, the whole site goes down sending mysql into a spin/cpu peak.

    My best option to downgrade?

    Hi user, I’m currently experiencing the same issue after upgrading from 3.1 to 3.1.1. Were you able to resolve it? If so what did you do? Thanks!

    If anyone else has any ideas, I’d very much appreciate your assistance.

    Thread Starter user65

    (@user65)

    i removed platinum seo plugin and that seemed to do the trick.

    Thanks very much for the reply! I’m currently considering a plugin that creates sitemaps for Google search as a possible culprit (Google News Sitemap). I suspect it’s reading the whole posts table and locking everything else out.

    You mentioned it was occurring when saving posts, which is probably the hook that the plugin would use to do its work.

    I am using Google News Plugin without any problems (fyi) and in fact, I have two copies of MySQL running on my server, one one port, and another on a different port, two different “revisions” and have not seen or noticed any problems with any of my sites…

    Thread Starter user65

    (@user65)

    I had google sitemaps enabled, tried disabling it without any positive effect. For me it was platinum seo.

    there is a WPMU “Chat” program out there that will kill your blog as well (same issues just “steals the show”)

    IS this now “resolved”? You can change the status to “resolved”… fyi

    Thanks for the heads-up on that plugin.. another to steer clear is any plugin that uses Google Translate… I had it running months ago, the first time we started getting serious traffic it took the site down because every user was linking back to google for a “possible” translate…

    Unfortunately disabling Google News Sitemap doesn’t seem to have helped. Here’s a list of currently active plugins.

    • Akismet
    • Easy Disable Visual Editor
    • Facebook OpenGraph
    • Legacy Permalinks
    • Role Manager
    • Topsy Retweet Button
    • WP No Category Base
    • WP Super Cache
    • WPtouch Pro
    • You Can Javascript

    The problem query is

    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 (146)
					GROUP BY object_id HAVING COUNT(object_id) = 1
				) ) 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;

    Could anyone offer tips on identifying the source?

    Well, the easiest thing to do is simply disable all of them however, I would suspect the Javascript plugin, or the Role Manager plugin

    Thank you. I’ll deactivate those two to start with, see if that helps and post an update.

    Thread Starter user65

    (@user65)

    i have role manager and kept it enabled.

    i found the best way to solve my problem was to examine what plugins affected the post edit screen. So basically enable all screen options and check out which plugins affect the post. I started with that method and found the seo plugin. it has to be a plugin that being activated or used through the post edit page.

    the problem is in taxonomy.php (wordpress 3.1.1)

    mySQL 5.1.43 (which is what I am running) optimises the query incorrectly, which is particularly slow on my site that has 2000+ posts (40000+ rows in wp_posts)

    the way to get around it is to change the sub query in taxonomy.php:

    LINE 697-702 – replace with:

    $where[] = "EXISTS (
	SELECT 1
	FROM $wpdb->term_relationships
	WHERE term_taxonomy_id IN ($terms)
	AND object_id = $primary_table.$primary_id_column
	GROUP BY object_id HAVING COUNT(object_id) = $num_terms
)";

    it forces the DB to evaluate inside first. YMMV
    I’d be interested if this works on other people’s servers.
    There is a faster version using an inner join, but it changes the query structure so requires a bit more code editing

    —
    FYI, on mySQL 5.0 the old query would take around 3-5seconds compared with 180sec on mySQL 5.1. YMMV
    —
    OLD 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;

    NEW QUERY (code edit above gives this query)

    SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND EXISTS (
                   SELECT 1
                   FROM wp_term_relationships
                   WHERE term_taxonomy_id IN (4594,4600)
AND object_id = wp_posts.ID
                   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;

    POSSIBLE NEWER BETTER QUERY (requiring more code editing)

    SELECT SQL_CALC_FOUND_ROWS  wp_posts.*
FROM wp_posts
        INNER JOIN (
                SELECT object_id
        FROM wp_term_relationships
        WHERE term_taxonomy_id IN (4594,4600)
        GROUP BY object_id
                HAVING COUNT(object_id) = 2
        ) as term ON term.object_id = wp_posts.ID
WHERE 1=1
        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;

    I have not witnessed this problem since I disabled the role manager plugin…

    What type of server are you running and the specs?

    I am running WIndows 2008 R2 on a VPS with four Xeon core processors, and 3 gigs of ram and have not experienced any of the above problems.

    How many hits are you taking on your server?

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