WordPress.org

Ready to get started?Download WordPress

Forums

[Plugin: Simple Tags] Slow query in 1.5.7 (19 posts)

  1. distobj
    Member
    Posted 5 years ago #

    Hi. We've recently started using this plugin and have noticed the sites' performance slowing considerably. Some debugging turned up a query which was regularly taking a long time, and sucking up a *lot* of CPU;

    # Query_time: 8 Lock_time: 0 Rows_sent: 15 Rows_examined: 72220
    SELECT t.*, tt.*
    FROM wp_terms AS t
    INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
    INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
    INNER JOIN wp_posts AS p ON tr.object_id = p.ID
    WHERE tt.taxonomy IN ( 'post_tag' )
    AND p.post_date_gmt < '2008-10-27 23:24:03'
    AND tt.count > 0
    GROUP BY t.term_id
    ORDER BY tt.count DESC
    LIMIT 15;

    Has anybody else encountered this problem, and if so, what have you done to alleviate it?

  2. iridiax
    Member
    Posted 5 years ago #

    Why not use a different plugin or a non-plugin option? This plugin only states that it is compatible up to WordPress 2.5.

    http://wordpress.org/extend/plugins/simple-tags/

  3. beggers
    Member
    Posted 5 years ago #

    This is a truly great plugin. That's why we're hoping that the author updates it.

  4. Matt B.
    Member
    Posted 5 years ago #

    I agree, that plugin needs some serious performance improvements.

  5. Amaury Balmer
    Member
    Posted 5 years ago #

    If you have tips for optimize my plugin... please contact me...

  6. france1972
    Member
    Posted 5 years ago #

    I am having these slow queries as well. the plugin is perfect and great but I keep getting lots of slow queries. of course i dont know how to optimize it and i m not here to criticize its author since he has done an excellent job. I am here to let him know that his plugin *maybe* has got some performance issue. Apart from this i love Simple Tags. The slow queries i am getting are the same as distobj. it took me a while to figure where they came from. Is anyone else having this issue? what can we do to solve it? Caching the blog? Limiting tags on posts? Any reply is very welcomed!

    here are my slow queries:

    Tue Mar 31 13:01:03 2009
    # Query_time: 8 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    use mydatabasename;
    SELECT p.post_title, p.comment_count, p.post_date, p.ID, COUNT(tr.object_id) AS counter
    FROM wp_posts AS p
    INNER JOIN wp_term_relationships AS tr ON (p.ID = tr.object_id)
    INNER JOIN wp_term_taxonomy AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
    WHERE (tt.taxonomy = 'post_tag' AND tt.term_id IN ("71", "187", "3", "138"))
    AND p.ID <> 2090
    AND p.post_status = 'publish'
    AND p.post_date_gmt < '2009-03-31 13:00:55'
    AND p.post_type = 'post'
    GROUP BY tr.object_id
    ORDER BY counter DESC, p.post_title DESC
    LIMIT 0, 6

    # Tue Mar 31 13:02:21 2009
    # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    use mydatabasename;
    SELECT wp_posts.* FROM wp_posts WHERE 1=1 AND YEAR(wp_posts.post_date)='2009' AND MONTH(wp_posts.post_date)='2' AND DAYOFMONTH(wp_posts.post_date)='9' AND wp_posts.post_name = 'blog article' AND wp_posts.post_type = 'post' ORDER BY wp_posts.post_date DESC

  7. nast0
    Member
    Posted 5 years ago #

    I had these issues amongst others on my blog. I have 15,000 posts with 5,000 tags, so my relationships table is getting a little swollen (104k rows).

    As i couldn't debug it, I started caching (which helped a little), then removed some of the more frequent tags from my posts. I had some tags for example with 9,000 occurences, so deleting them had a big impact on my relationships table, and overall CPU usage by MySQL.

    I managed to get server load down from 2.00 to 1.00 doing this. It's only a temporary fix, and i need a long-term resolution too.

    Can I ask how you guys narrowed it down to these specific queries?

    Thanks,
    n

  8. nast0
    Member
    Posted 5 years ago #

    That being said, I just ran the 2 pieces of code posted above by france with the following results.

    Showing rows 0 - 5 (6 total, Query took 0.0857 sec)

    MySQL returned an empty result set (i.e. zero rows). (Query took 0.0016 sec)

    Still any pointers on finding & debugging slow queries who be great, as somewhere in my install I have probs :(

  9. france1972
    Member
    Posted 5 years ago #

    i didnt debug it at all to tell you the truth. How did i reckon what it was simple tags to create those queries? Simple. I have few plugins on my blog (only 10 plugins). I started disbling them one by one, also trying to enable them by couples, triplets etc. This process took around 10 days. In the end every time i enabled simple tags alone or with other plugins, the slow queries appeared. I know this is not a very professional way to find out what plugin was the culprit, nonetheless i found out what was causing my queries.

  10. france1972
    Member
    Posted 5 years ago #

    by the way, what if i index my database in a better way? I have heard that this could help a little bit. If you think that this may be useful, where can I get help? do you know a professional site able to offer valuable help?

  11. nast0
    Member
    Posted 5 years ago #

    I was going to start looking around for a freelancer to take a look at it, but it's not got bad enough to part with cash yet! :) I've had good experiences with some freelancers for Joomla, WP should be no different.

    Good Luck!

  12. OzPoker
    Member
    Posted 5 years ago #

    Does the same with mine - I have 10,000 posts and 6000 tags and I can't leave it running as the sleeping queries bring the whole things to a halt.

  13. france1972
    Member
    Posted 5 years ago #

    I have only 1300 posts and 90 tags but it is the same. I also think that these slow queries are caused by servers themsevles. in fact if you are on a shared server *maybe* simple tags can give you some issue. But this is a thought of mine...what do u think?

  14. OzPoker
    Member
    Posted 5 years ago #

    Mines on my own VPS

  15. durkindm
    Member
    Posted 5 years ago #

    I've been having a problem which I think is related to SImple Tags, that causes high CPU and active MYSQL connections not closing.

    see http://wordpress.org/support/topic/259071?replies=1#post-1035924

    I'm now wondering having read all the above comments by people whether the two are related.

    I like the functionality of the plugin and am the first to admit I wouldn't have a clue where to start in writing one, but if the author of SImple Tags could have a look at this we'd all be very grateful.

  16. nast0
    Member
    Posted 5 years ago #

    Mine is on a dedicated Dual Xenon, with 2 other sites. Both other sites load pages in ~1sec. Something in my WP install though is making sites on this site load in ~6 secs.

  17. nast0
    Member
    Posted 5 years ago #

    I've been doing more digging, and using the dbprofiling plugin (http://wordpress.org/extend/plugins/wpdb-profiling/) I've been able to figure out that it IS the query you pasted above that causes my problem.

    It's the tag cloud that hogs the resource, I had one included in my sidebar of every page... removing it makes my site 5x faster instantly (even with caching off).

    Here's the offender on my server, a test of 10 runs of this query returned an average runtime of 4.8 seconds.

    SELECT t . * , tt . *
    FROM wp_terms AS t
    INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
    INNER JOIN wp_term_relationships AS tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
    INNER JOIN wp_posts AS p ON tr.object_id = p.ID
    WHERE tt.taxonomy
    IN (
    'post_tag'
    )
    AND p.post_date_gmt < '2009-04-10 14:42:55'
    AND (
    t.term_id <>12
    AND t.term_id <>355
    )
    AND tt.count >0
    GROUP BY t.term_id
    ORDER BY tt.count DESC
    LIMIT 70

    My solution will be to run a tag cloud once, steal the source code, and paste it into my sidebar. It won't be dynamic, but I can update it weekly... so i don't lose functionality for the user.

    Hoorah!!!!
    n

  18. lawvol
    Member
    Posted 4 years ago #

    My solution will be to run a tag cloud once, steal the source code, and paste it into my sidebar. It won't be dynamic, but I can update it weekly... so i don't lose functionality for the user.

    I did this and finally, my server load has dropped back down to a reasonable amount.

    That said, did anyone ever figure out how to keep the Tag Cloud without having the MySQL problems?

    This problem has been plaguing me for a long time too...

  19. nast0
    Member
    Posted 4 years ago #

    Not as yet, although there have been 2/3 updates to the plugin since I wrote that. I'll be updating to WP 2.8 next week, and will give it a try with everything updated.

    There is probably a balance to be had, as my server handles a tag cloud of 6 tags (pasted by france1972) pretty well, it was my query of 70 tags which hogged all the load.

    That being said, I don't consider the cloud being static a problem. The users have no idea, and in my particular circumstance (5,000 tags on 18,000 posts), it would change very little even if i made 50 posts!

Topic Closed

This topic has been closed to new replies.

About this Topic