WordPress.org

Ready to get started?Download WordPress

Forums

WordPress Popular Posts
[Plugin: WordPress Popular Posts] To the Admin of WPP - Slow Queries (25 posts)

  1. SantaCruze
    Member
    Posted 2 years ago #

    Heya there,

    First of all, thanks for the nice plugin. Ive it running on a Tech-Network on several News-Blogs for about 1,5 Months. But...this problem has started to make me much problems.

    In the last 4 Weeks i encountered several Crashes of the Webserver. My Provider supported me with a Slow-Query Log and some of em are related to the WPP Plugin. Ive wrote u some down, maybe u can work something out:

    SELECT wpdb_posts.ID, wpdb_posts.post_title , (SUM(wpdb_popularpostsdatacache.pageviews)) AS 'pageviews' FROM wpdb_posts RIGHT JOIN wpdb_popularpostsdatacache ON wpdb_posts.ID = wpdb_popularpostsdatacache.id WHERE wpdb_posts.post_status = 'publish' AND wpdb_posts.post_password = '' AND gadget_popularpostsdatacache.day >= '2012-03-22 15:14:12' - INTERVAL 1 DAY AND wpdb_posts.post_type = 'post' GROUP BY wpdb_posts.ID ORDER BY pageviews DESC LIMIT 10;

    and

    # Query_time: 26.749901 Lock_time: 0.584690 Rows_sent: 10 Rows_examined: 7400
    use db xxxxx;
    SET timestamp=1332417066;
    SELECT xxx_posts.ID, xxx_posts.post_title , (SUM(xxx_popularpostsdatacache.pageviews)) AS 'pageviews' FROM xxx_posts RIGHT JOIN xxx_popularpostsdatacache ON xxx_posts.ID = xxx_popularpostsdatacache.id WHERE xxx_posts.post_status = 'publish' AND xxx_posts.post_password = '' AND xxx_popularpostsdatacache.day >= '2012-03-22 13:50:39' - INTERVAL 1 DAY AND xxx_posts.post_type = 'post' GROUP BY xxx_posts.ID ORDER BY pageviews DESC LIMIT 10;

    Maybe you also have some quick tips how i can reduce the queries for the plugin.

    http://wordpress.org/extend/plugins/wordpress-popular-posts/

    PS: I use this code to show the most popular topics per day. Not using the Widget.
    <?php if (function_exists('wpp_get_mostpopular')) wpp_get_mostpopular("range=daily&order_by=views&limit=10"); ?>

    PSS: I use a Virtual Server Managed, most configured Version with about 4GB RAM. My Provider told me, it rans out of memory cuz of that many slow queries. Not sure about that. Also the Network has about 6k to 7k users a day with about 20k Page Impressions.

  2. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    Hi SantaCruze,

    Ever since this plugin was published many people has reported similar issues and you're the first one to actually share a slow query log (parcially, at least) with me, so thank you.

    While reading it, a few ideas came to me on how to improve a couple of things that in the end should make things easier on the database. Will start testing this upcoming weekend and get back to you later.

  3. alexluft
    Member
    Posted 2 years ago #

    Hi Hector -- thanks for developing the plugin! We've been using it as part of our theme for the last two years and it's been nothing but useful for our users! Unfortunately, it seems that our host has recently pulled down our database due to overloading the server.

    According to them:

    Problematic query:
    SELECT wp_posts.ID, wp_posts.post_title , (SUM(wp_popularpostsdatacache.pageviews)) AS 'pageviews' FROM wp_posts RIGHT JOIN wp_popularpostsdatacache ON wp_posts.ID = wp_popularpostsdatacache.id WHERE wp_posts.post_status = 'publish' AND wp_posts.post_password = '' AND wp_popularpostsdatacache.day >= '2012-04-25' - INTERVAL 30 DAY GROUP BY wp_posts.ID ORDER BY pageviews DESC LIMIT 7

    EXPLAIN:
    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE wp_popularpostsdatacache ALL id 34841 Using where; Using temporary; Using filesort
    1 SIMPLE wp_posts eq_ref PRIMARY PRIMARY 8 GMCweekly.wp_popularpostsdatacache.id 1 Using where

    This query examines 34841 rows.

    Since then, we've disabled the plugin so we could be put back up... but I was hoping there would be something you would recommend us do to work around the issue at hand.

    Again, thanks for your hard work!

    Alex

  4. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    Hi Alex,

    The query part has been completely rewritten and I'm currently testing the plugin. Hopefully, it should be less heavy on the database.

    If you want to give it a try let me know and I'll send you the beta version (well, it's more like an alpha since there are some other things that need to be done but it works).

  5. Racer-X
    Member
    Posted 2 years ago #

    Héctor, we have a heavy traffic site suffering from the same issue here, can you send the beta version to test on our site ?

  6. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    Hi Racer-X,

    It's still under development and some features have been disabled. If you don't mind that, I'll upload it and post the link here so you can try it out.

  7. Racer-X
    Member
    Posted 2 years ago #

    It´s not a problem. Send me the bytes :D

  8. brokentwig
    Member
    Posted 2 years ago #

    Hector, it appears the problem with the query is the INTERVAL function. I took Alex's query and changed:

    AND wp_popularpostsdatacache.day >= '2012-04-25' - INTERVAL 30 DAY

    with

    AND wp_popularpostsdatacache.day >= '2012-04-25'
     AND wp_popularpostsdatacache.day <= '2012-05-25'

    The query time went from 1.5 - 3.0 seconds down to .009 - .1 seconds. I tested it about 20 times with the same results. I couldn't find any concensus on the Internets that the INTERVAL function in MySQL is heavy, but it appears to be in this query. I looked through your plugin and this should be an easy fix using the ADDDATE() function.

    Using the ADDDATE() function under testing, I was getting .05 seconds average query time.

    $range = $table."cache.day >= '".gmdate("Y-m-d")."' AND ".$table."cache.day <= ADDDATE('".gmdate("Y-m-d")."', INTERVAL 30 DAY)";
  9. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    Hi brokentwig,

    That part of the query has been already changed on the development version. Actually I made some major changes to all queries and wrote them from scratch using a different approach, and now they're using the DATE_SUB function instead. For example:

    AND comment_date > DATE_SUB('".$this->now()."', INTERVAL 1 MONTH)

    Will try your solution and use whichever gives the best performance.

  10. brokentwig
    Member
    Posted 2 years ago #

    Cool. Would you mind posting a link of your alpha version?

  11. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    Alright, here it is! Please keep in mind that this is a development version and even though I did a full test of everything some bugs might show up.

    There are some new, yet to be documented features in this release. You might want to give them a try and post feedbacks here.

  12. clecompte
    Member
    Posted 2 years ago #

    I noticed Exclude Categories was quoted out. Is this still under development?

  13. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    Nope. It's still there, only that it works in a different way now.

    If you're using the widget, find it in Post Filter. Once enabled, set the category(ies) you want to exclude with a minus sign in front. For example: -25,-3,-15.

    If you are using the template tag, this is how it is done:

    wpp_get_mostpopular('order_by=views&cat=-25,-3,-15');

    P.S.: same parameters apply for the shortcode version.

  14. clecompte
    Member
    Posted 2 years ago #

    I couldn't find the Post Filter in the widget. Maybe I'm blind.

    So I tried the shortcode, and the category I excluded still appears in the list. Does data need to collect first?

    Thanks for your help. Great plugin!

  15. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    Sorry, my bad. It's Post type (haven't got used to it yet hehe). Maybe I should rename it to Post filter instead.

    If the posts also belong to a non-excluded category these will still be included in the listing. If it's not the case, please let me know.

  16. jimbocook
    Member
    Posted 2 years ago #

    I see a number of files in the download that look like they are no longer used. Can you give us a list of the files that are required to be uploaded for the plugin? Thanks.

  17. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    The files you need are:

    - wordpress-popular-posts.php
    - admin.php

    Also, if you intend to use thumbnails you might wanna grab timthumb.php and the cache folder as well (which must be writable).

  18. SantaCruze
    Member
    Posted 2 years ago #

    Heya Hector,

    Its been some time since my last post. Do you´ve any new Informations or Releases for the Plugin? I had to deactivate the Plugin for now, cuz it crushes my Server :-) Hope you can fix the slow query problem soon.

  19. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    @SantaCruze: Yes. If you check above, you'll see that I posted a link to a development version around 4 weeks ago now ^^. It included a bunch of stuff, including re-worked queries that should be more DB friendly. Unfortunately, I just took the link down hehe. If you want to try it out, please post your e-mail here so I can send it to you.

    Everyone: I'm removing the development version now since all pending updates / changes to the plugin have been done already. Will test it for a few more days and release an official update soon.

    There are a couple of new features not present in the development version posted here that might break stuff once you upgrade to the official version. If that happens, please let me know.

    Thanks for your feedback!

  20. SantaCruze
    Member
    Posted 2 years ago #

    Heya Hector,

    Well iam not in a hurry. If u r going to release it in a few days, i ll just wait for it :)

  21. SantaCruze
    Member
    Posted 2 years ago #

    Heya Hector,

    Sorry havent looked in here for a while. You can send me a Link to the Plugin at SantaCruze@gmx.de. I ll have a look at it then.

    Sincerely,

    Stefan

  22. SantaCruze
    Member
    Posted 2 years ago #

    Hey Hector,

    Havent received any E-Mail from you yet. Would nice if u could send me the new version of the plugin.

  23. Héctor Cabrera
    Member
    Plugin Author

    Posted 2 years ago #

    Hi there,

    Sorry. Been really busy in the past few weeks and haven't been able to work on the plugin nor check the forums.

    I'm sending you the plugin now. It's still in beta stage since there's one final thing that needs fixing, but other than that it should be what you will get once it goes public.

    Sorry for the delay.

  24. alexluft
    Member
    Posted 1 year ago #

    Hey Hector -- I installed the alpha and a few weeks after running it, got in trouble with the host again. Here's their message as it relates to the query:

    Problematic query:
    SELECT p.ID AS 'id', p.post_title AS 'title', p.post_date AS 'date', p.post_author AS 'uid' , IFNULL(v.pageviews, 0) AS 'pageviews' FROM wp_posts p LEFT JOIN (SELECT id, SUM(pageviews) AS 'pageviews', day FROM (SELECT id, pageviews, day FROM wp_popularpostsdatacache WHERE day > DATE_SUB('2012-06-26 13:18:26', INTERVAL 1 MONTH) ORDER BY day) sv GROUP BY id) v ON p.ID = v.id WHERE p.post_status = 'publish' AND p.post_password = '' AND v.pageviews > 0 GROUP BY p.ID ORDER BY v.pageviews DESC LIMIT 7
    
    EXPLAIN:
    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY ALL 4342 Using where; Using temporary; Using filesort
    1 PRIMARY p eq_ref PRIMARY PRIMARY 8 v.id 1 Using where
    2 DERIVED ALL 112276 Using temporary; Using filesort
    3 DERIVED wp_popularpostsdatacache ALL 112489 Using filesort
    
    This query examines 54,838,656,573,688 rows, which is unacceptable in shared hosting.

    Would really appreciate any and all help. Thanks so much!

    Alex

  25. Héctor Cabrera
    Member
    Plugin Author

    Posted 1 year ago #

    Hi Alex,

    Well it seems I still need to fine-tune the queries a bit more. I'm not exactly a DB expert so it might take some time (unless anyone here is willing to lend a hand and offer a few suggestions).

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic