[wp-testers] 2.3 and loooooong running queries
Les Bessant
les at lcb.me.uk
Wed Oct 3 06:08:43 GMT 2007
Now there's a funny thing.
I had *exactly* the same thing happen to me yesterday. It wasn't breaking
anybody else's site on my host's server[1], but it was using the maximum
number of connections, which made my site die. I have a remarkably similar
log, which I'll provide if it's of any help. I also imported tags from UTW.
The only tagging plugins I have are "Click Tags" and "Tag Managing Thing",
neither of which (AFAIK) have any interaction with displayed pages.
In my case, it appeared to be dying while looking at a single image page
generated by WPG2. I don't entirely understand the query, but it looked like
it was trying to do something "taggy" with every word in the description of
the photograph. After a friendly exchange of emails with Dreamhost support,
I've disabled WPG2 for the time being, and the site is back up. I was going
to look into this at the weekend, when I'll have more time, but since
Handy's had the same issue, I thought it was worth mentioning it.
[1] Ah, I see we're both on Dreamhost...
Les Bessant les at lcb.me.uk
Losing it[1] - http://lcb.me.uk
My flickr pictures - http://flickr.com/photos/lesbessant/
> -----Original Message-----
> From: wp-testers-bounces at lists.automattic.com [mailto:wp-testers-
> bounces at lists.automattic.com] On Behalf Of Handy
> Sent: 03 October 2007 04:29
> To: wp-testers at lists.automattic.com
> Subject: [wp-testers] 2.3 and loooooong running queries
>
> Help me Obi-wan{s), you're my last hope... :-)
>
> I upgraded to 2.3 over the weekend. Seemed to go smoothly enough. I
> imported my UTW tags and went "native" for tagging.
>
> Twice now I've managed to clobber MySQL with endless queries that use
> up my
> max_user_connections. I'm on shared hosting and my host is not
> amused...
>
> They sent me a snippet of the query, but it must be one that's
> programmatically built as I sure can't turn anything like it up with
> grep.
> The query is below for reference (check those joins and the where
> clause!).
>
> I've yet to determine if this is "core" or plugin related. This
> evening,
> when it clunked on me I did have two tagging plugins enabled:
>
> * Advanced Tag Entry: http://www.poplarware.com/tagplugin.html
> * WP 2.3 Related Posts:
> http://wordpress.org/extend/plugins/wordpress-23-related-posts-plugin/
>
> I didn't write any posts today, so that would presumably excuse the
> first.
> But the second doesn't seem to generate anything like what's below
> either
> (as far as I can tell). I monitored the mysql processlist most of the
> day
> and never saw any spikes, so I'm a bit bemused as to what triggers this
> in
> the first place.
>
> Any suggestions on how best to track this down? I'm a bit stumped at
> the
> moment.
>
> ----------------------------------
> Here is the query, below that the top of the "show processlist;" in
> mysql.
>
>
> #
>
> # Query_time: 77983 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
>
> SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN
> wp_term_relationships AS tr0 ON (wp_posts.ID = tr0.object_id) LEFT JOIN
> wp_term_taxonomy AS tt0 ON (tr0.term_taxonomy_id =
> tt0.term_taxonomy_id)
> LEFT JOIN wp_terms AS term0 ON (tt0.term_id = term0.term_id) LEFT JOIN
> wp_term_relationships AS tr1 ON (wp_posts.ID = tr1.object_id) LEFT JOIN
> wp_term_taxonomy AS tt1 ON (tr1.term_taxonomy_id =
> tt1.term_taxonomy_id)
> LEFT JOIN wp_terms AS term1 ON (tt1.term_id = term1.term_id) LEFT JOIN
> wp_term_relationships AS tr2 ON (wp_posts.ID = tr2.object_id) LEFT JOIN
> wp_term_taxonomy AS tt2 ON (tr2.term_taxonomy_id =
> tt2.term_taxonomy_id)
> LEFT JOIN wp_terms AS term2 ON (tt2.term_id = term2.term_id) LEFT JOIN
> wp_term_relationships AS tr3 ON (wp_posts.ID = tr3.object_id) LEFT JOIN
> wp_term_taxonomy AS tt3 ON (tr3.term_taxonomy_id =
> tt3.term_taxonomy_id)
> LEFT JOIN wp_terms AS term3 ON (tt3.term_id = term3.term_id) LEFT JOIN
> wp_term_relationships AS tr4 ON (wp_posts.ID = tr4.object_id) LEFT JOIN
> wp_term_taxonomy AS tt4 ON (tr4.term_taxonomy_id =
> tt4.term_taxonomy_id)
> LEFT JOIN wp_terms AS term4 ON (tt4.term_id = term4.term_id) LEFT JOIN
> wp_term_relationships AS tr5 ON (wp_posts.ID = tr5.object_id) LEFT JOIN
> wp_term_taxonomy AS tt5 ON (tr5.term_taxonomy_id =
> tt5.term_taxonomy_id)
> LEFT JOIN wp_terms AS term5 ON (tt5.term_id = term5.term_id) LEFT JOIN
> wp_term_relationships AS tr6 ON (wp_posts.ID = tr6.object_id) LEFT JOIN
> wp_term_taxonomy AS tt6 ON (tr6.term_taxonomy_id =
> tt6.term_taxonomy_id)
> LEFT JOIN wp_terms AS term6 ON (tt6.term_id = term6.term_id) LEFT JOIN
> wp_term_relationships AS tr7 ON (wp_posts.ID = tr7.object_id) LEFT JOIN
> wp_term_taxonomy AS tt7 ON (tr7.term_taxonomy_id =
> tt7.term_taxonomy_id)
> LEFT JOIN wp_terms AS term7 ON (tt7.term_id = term7.term_id) LEFT JOIN
> wp_term_relationships AS tr8 ON (wp_posts.ID = tr8.object_id) LEFT JOIN
> wp_term_taxonomy AS tt8 ON (tr8.term_taxonomy_id =
> tt8.term_taxonomy_id)
> LEFT JOIN wp_terms AS term8 ON (tt8.term_id = term8.term_id) LEFT JOIN
> wp_term_relationships AS tr9 ON (wp_posts.ID = tr9.object_id) LEFT JOIN
> wp_term_taxonomy AS tt9 ON (tr9.term_taxonomy_id =
> tt9.term_taxonomy_id)
> LEFT JOIN wp_terms AS term9 ON (tt9.term_id = term9.term_id) LEFT JOIN
> wp_term_relationships AS tr10 ON (wp_posts.ID = tr10.object_id) LEFT
> JOIN
> wp_term_taxonomy AS tt10 ON (tr10.term_taxonomy_id =
> tt10.term_taxonomy_id) LEFT JOIN wp_terms AS term10 ON (tt10.term_id =
> term10.term_id) WHERE 1=1 AND term0.slug = 'ubuntu' AND term1.slug =
> 'how' AND term2.slug =
>
> 'to' AND term3.slug = 'make' AND term4.slug = 'your' AND term5.slug
> =
> 'fonts' AND term6.slug = 'smooth' AND term7.slug = 'enough' AND
> term8.slug = 'to' AND term9.slug = 'drool' AND term10.slug = 'over'
> AND post_type = 'post' AND (post_status = 'publish') GROUP BY
> wp_posts.ID
> ORDER BY post_date DESC LIMIT 5, 5;
>
>
> mysql> show processlist;
>
> +--------+-------------+------------------------------------+----------
> -+
> ---------+-------+-------------------+---------------------------------
> --
> -------------------------------------------------------------------+
>
> | Id | User | Host | db
> |
> Command | Time | State | Info
> |
>
> +--------+-------------+------------------------------------+----------
> -+
> ---------+-------+-------------------+---------------------------------
> --
> -------------------------------------------------------------------+
>
> | 242858 | ckas10 | cupcake.dreamhost.com:60994 | wordpress
> |
> Query | 77883 | statistics | SELECT SQL_CALC_FOUND_ROWS
> wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships AS tr0 ON
> (wp_
> |
>
> | 242899 | ckas10 | cupcake.dreamhost.com:60689 | wordpress
> |
> Query | 77821 | statistics | SELECT SQL_CALC_FOUND_ROWS
> wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships AS tr0 ON
> (wp_
> |
>
> | 245087 | ckas10 | cupcake.dreamhost.com:33907 | wordpress
> |
> Query | 75612 | Locked | UPDATE wp_posts SET comment_count
> =
> '0' WHERE ID = '556' |
>
> | 245108 | ckas10 | cupcake.dreamhost.com:42645 | wordpress
> |
> Query | 75610 | Locked | SELECT wp_posts.* FROM wp_posts
> WHERE 1=1 AND YEAR(post_date)='2006' AND MONTH(post_date)='11' A |
>
> | 245137 | ckas10 | cupcake.dreamhost.com:35500 | wordpress
> |
> Query | 75584 | Locked | SELECT SQL_CALC_FOUND_ROWS
> wp_posts.* FROM wp_posts WHERE 1=1 AND YEAR(post_date)='2005' AND
> MONT
> |
>
> | 245151 | ckas10 | cupcake.dreamhost.com:36812 | wordpress
> |
> Query | 75575 | Locked | SELECT SQL_CALC_FOUND_ROWS
> wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships ON
> (wp_posts.I
> |
>
> etc. etc. etc. for several pages.
> _______________________________________________
> wp-testers mailing list
> wp-testers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-testers
More information about the wp-testers
mailing list