fudj
Forum Replies Created
-
Forum: Fixing WordPress
In reply to: database maxing cpu usage after upgrade to 3.1Forum: Fixing WordPress
In reply to: database maxing cpu usage after upgrade to 3.1@xtraxtra great news!
i’m not a wordpress guru, but i think it’s one of the plugins that is calling this particular query.Forum: Fixing WordPress
In reply to: database maxing cpu usage after upgrade to 3.1i 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.phpForum: Fixing WordPress
In reply to: database maxing cpu usage after upgrade to 3.1the code is new in version WP3.1
it’s just an inefficient way of doing the subquerythe new subquery works faster on 5.0 as well… i’ll submit this to trac when i work out how to use it.. ??
Forum: Fixing WordPress
In reply to: database maxing cpu usage after upgrade to 3.1this 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 minuteson the same data
Forum: Fixing WordPress
In reply to: database maxing cpu usage after upgrade to 3.1ubuntu 10.04 xen server
4 cpus, 6gb ramit chugs on that query without any load on the server at all
Forum: Fixing WordPress
In reply to: MYSQL-Slow Queries WordPress 3.1check out my post on to see if it helps?
Forum: Fixing WordPress
In reply to: database maxing cpu usage after upgrade to 3.1the 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 QUERYSELECT 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;