WordPress.org

Ready to get started?Download WordPress

Forums

Loooooong Running Queries, host is not amused (20 posts)

  1. Chris_K
    Member
    Posted 6 years ago #

    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.

  2. whooami
    Member
    Posted 6 years ago #

    wow.

  3. Chris_K
    Member
    Posted 6 years ago #

    The clot thickens...

    I just got the latest captures from my latest round of "kill the database". Basically, the same query -- especially the where clause.

    Look close to the example above and you can see it basically says, "how to make your fonts smooth enough to drool over"

    After some digging, I found a post with that phrase. The phrase was part of a messed up URL (it was the title attribute). I removed the link completely.

    Brings up the question though: What process is building that "heinous" query derived from words in a broken hyperlink?

  4. Chris_K
    Member
    Posted 6 years ago #

    I locked up my DB a few more times this morning.

    The where clause of that big ol' hairy query is always the same... just wish I knew what was causing that query to fire. I can't seem to make it happen.

    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')

    I also noticed that I was still running the WP 2.3 Related Posts plugin. I've disabled it (and quite a few others) for the time being while I continue to test.

  5. Samuel Wood (Otto)
    Tech Ninja
    Posted 6 years ago #

    I think that the category__and is doing it:

    In query.php:

    if ( !empty($q['category__and']) ) {
    $count = 0;
    foreach ( $q['category__and'] as $category_and ) {
    	$join .= " LEFT JOIN $wpdb->term_relationships AS tr$count ON ($wpdb->posts.ID = tr$count.object_id) LEFT JOIN $wpdb->term_taxonomy AS tt$count ON (tr$count.term_taxonomy_id = tt$count.term_taxonomy_id) ";
    	$whichcat .= " AND tt$count.term_id = '$category_and' ";
    	$count++;
    }
    }
  6. I had the same thing happen yesterday. In my case, it was triggered by the description of an image on a Gallery2 page (WPG2 integration). I suspected WPG2 at first, but as Handy's getting the same thing without its help, and ozgreg confirms that WPG2 isn't doing anything tag-related, it looks like it's an innocent victim.

    The insane series of joins related to each word of the description. I don't have any related posts plugins, and there shouldn't be any category or tagging going on with that kind of page.

    Similarities with Handy's situation - both upgraded to 2.3 at the weekend, both on Dreamhost.

  7. Chris_K
    Member
    Posted 6 years ago #

    Otto - I'd say you've nailed down where it's coming from. Now... why? What uses that, I wonder.

    And geeze, why is it searching for that particular phrase of words that aren't even tags?

    It's almost like a perverse denial of service... just build a big hairy tag intersection query string...

    Les - I want to blame a certain plugin, but apparently you're not using it. Hmmph.

    Welp, continuing to test

  8. Samuel Wood (Otto)
    Tech Ninja
    Posted 6 years ago #

    It could also be the tag__and or the tag_slug__and stuff doing it. That's some messed up query code.

    Do you have any multi-word tags, which use spaces in them? That could be an issue.

  9. mrmist
    Forum Janitor
    Posted 6 years ago #

    The query does look pretty hellish, though a lot of how it performs is going to depend on how those tables are indexed. With no indexes, it'll be forced to table scan each left joined thing each time. if you have a lot of entries in your wp_term_whatever tables that could impose a bit of load. wp_posts will probably be scanned anyway because it's pulling back * from the table. But then at the end you have that group by post id, which I can't even test how that would behave 'cos I only have sql server to test on just now and that won't allow you to run a query like this where the other terms that aren't grouped are not agregated, 'cos it ain't valid.

    I guess this post isn't much help to you. sorry. :)

  10. Samuel Wood (Otto)
    Tech Ninja
    Posted 6 years ago #

    If those slug checks were moved into the ON statements, then the query would work much better because the joins would not be as quite as fierce.

  11. mrmist
    Forum Janitor
    Posted 6 years ago #

    If those slug checks were moved into the ON statements, then the query would work much better because the joins would not be as quite as fierce.

    Agreed. The query optimiser may be intelligent enough to translate the where clauses, but there's nothing to be lost in this case by giving it a helping hand.

    I still get the feeling that there should be an altogether better method of doing it, though. If I just knew what "it" was I could probably piece it together.

  12. Samuel Wood (Otto)
    Tech Ninja
    Posted 6 years ago #

    Here's what I'm thinking:
    Join the posts to the tags and the relationships.
    Use the WHERE with OR's (instead of the AND's) to get all the posts that have any of your tags.
    Group by ID.
    Having count(*) = number of tags you're looking for.

    The having will let you only get the ones where your count matched them all.

    The tag__in then becomes the same thing, but without the having.

  13. mrmist
    Forum Janitor
    Posted 6 years ago #

    Could work. You just have to be careful 'cos with LEFT OUTER JOINS and nothing but OR statements in the WHERE clause you'll be hitting a shed load of data before the group by kicks in.

  14. Chris_K
    Member
    Posted 6 years ago #

    I have a related discussion also going in the WP-Testers list.

    ----
    > Tag intersections can be done using two queries, you can see an example
    > for tag_slug__and in http://pastebin.com/m38c901f3.

    I forgot about that method. That is much nicer. I created a ticket and attached a patch.

    http://trac.wordpress.org/attachment/ticket/5137/intersection.diff#preview

    I added an else condition so that we wouldn't fall through to the regular front page query if no posts matched the tag_slug_and query.

    source: http://comox.textdrive.com/pipermail/wp-testers/2007-October/005431.html

  15. More data - I do have plenty of multi-word tags with spaces in. But there are no tags on the WPG2 pages - in the case where it got terminally upset, the query was doing all that LEFT JOIN stuff on a series of words that aren't even linked to anything.

    I've since reactivated WPG2 and visited the page without any ill effects.

  16. Chris_K
    Member
    Posted 6 years ago #

    in the case where it got terminally upset, the query was doing all that LEFT JOIN stuff on a series of words that aren't even linked to anything.

    Same here. And in my case, it's always the same set of words -- and they don't even exist on my blog anymore.

    --

    Thought I had it narrowed down to a plugin. I was wrong. After a 36 hour reprieve, the issue returned.

    I'm now running Default theme and very few plugins and begging my host to not punt me. Any thoughts on how to proceed?

    Of course, it would wait to start up again after I had a nice "StumbleUpon" burst going...

  17. MichaelH
    Member
    Posted 6 years ago #

    With a blindfold on, throwing this dart...

    This isn't similar to the weird report on wp-hackers where wp-cache, thought to be disabled, really wasn't. Solution was to delete the
    define {'ENABLE_CACHE','false');
    from wp-config.php

    See: http://comox.textdrive.com/pipermail/wp-hackers/2007-October/015458.html

  18. Chris_K
    Member
    Posted 6 years ago #

    Alas Mr. MichaelH, nope.

    However... I finally peeked into my web server logs.

    Look at this log entry:

    65.55.209.75 - - [04/Oct/2007:17:56:14 -0700] "GET /blog/tag/UBUNTU%20How%20To:%20Make%20your%20fonts%20smooth%20enough%20to%20drool%20over./page/2/ HTTP/1.0" 200 955 "-" "msnbot/1.0 (+http://search.msn.com/msnbot.htm)"

    Pumping that into my browser does indeed cause the query from hell. I have to kill it off.

    So!

    A) WTF mate? (sorry, had to get that out of my system)

    B) why is "msnbot" hitting me like that? Those arguments aren't even tags! With the '/tag/' portion in there, surely it isn't a search string?

    C) Ignoring B, why is that query causing all subsequent queries to go into a "locked" state? That's really the crux of the matter. The query that results from that hit clobbers my database and runs forever, blocking all other queries. Theme/plugin be darned!

    How odd!

  19. Chris_K
    Member
    Posted 6 years ago #

    Update:

    I grabbed the query.php from http://trac.wordpress.org/browser/branches/2.3/wp-includes/query.php?rev=6187

    Now, the former database killer results in a 404. For the moment, I find that much more acceptable. Other more rational tag unions seem to work.

    Testing continues.

  20. Update update:

    My logs showed a similar query, so it looks like we have a common trigger for the condition. I've loaded the new query.php, too....

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.