WordPress.org

Ready to get started?Download WordPress

Forums

database maxing cpu usage after upgrade to 3.1 (31 posts)

  1. user65
    Member
    Posted 3 years ago #

    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,

  2. user65
    Member
    Posted 3 years ago #

    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

  3. user65
    Member
    Posted 3 years ago #

    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?

  4. Leirith
    Member
    Posted 3 years ago #

    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.

  5. user65
    Member
    Posted 3 years ago #

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

  6. Leirith
    Member
    Posted 3 years ago #

    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.

  7. rsgrone
    Member
    Posted 3 years ago #

    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...

  8. user65
    Member
    Posted 3 years ago #

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

  9. rsgrone
    Member
    Posted 3 years ago #

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

  10. rsgrone
    Member
    Posted 3 years ago #

    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...

  11. Leirith
    Member
    Posted 3 years ago #

    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?

  12. rsgrone
    Member
    Posted 3 years ago #

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

  13. Leirith
    Member
    Posted 3 years ago #

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

  14. user65
    Member
    Posted 3 years ago #

    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.

  15. fudj
    Member
    Posted 3 years ago #

    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;
  16. rsgrone
    Member
    Posted 3 years ago #

    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?

  17. fudj
    Member
    Posted 3 years ago #

    ubuntu 10.04 xen server
    4 cpus, 6gb ram

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

  18. rsgrone
    Member
    Posted 3 years ago #

    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....

  19. fudj
    Member
    Posted 3 years ago #

    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

  20. rsgrone
    Member
    Posted 3 years ago #

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

  21. fudj
    Member
    Posted 3 years ago #

    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.. :-P

  22. rsgrone
    Member
    Posted 3 years ago #

    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...

  23. fudj
    Member
    Posted 3 years ago #

    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

  24. xtraxtra
    Member
    Posted 3 years ago #

    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.

  25. rsgrone
    Member
    Posted 3 years ago #

    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...

  26. xtraxtra
    Member
    Posted 3 years ago #

    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!

  27. fudj
    Member
    Posted 3 years ago #

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

  28. fudj
    Member
    Posted 3 years ago #

  29. rsgrone
    Member
    Posted 3 years ago #

    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...

  30. xtraxtra
    Member
    Posted 3 years ago #

    @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).

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.