Query overload and kill server

  • Resolved sjsusunny

    (@sjsusunny)


    7 years, 5 months ago

    hi,
    I am using ver 3.3.4
    The 24h popular vote was working fine for many months, then it showed no data on the widget window for past few days. Today I changed the time frame to 7 days and it crashed the server after few hours.
    This is query that cause high CPU load and timeout
    SELECT p.ID AS ‘id’, p.post_title AS ‘title’, p.post_date AS ‘date’, p.post_author AS ‘uid’, SUM(v.pageviews) AS ‘pageviews’ FROM wp_popularpostssummary v LEFT JOIN wp_posts p ON v.postid = p.ID WHERE 1 = 1 AND p.post_type = ‘post’ AND p.ID IN (
    SELECT object_id
    FROM wp_term_relationships AS r
    JOIN wp_term_taxonomy AS x ON x.term_taxonomy_id = r.term_taxonomy_id
    WHERE x.taxonomy = ‘category’ AND x.term_id IN(106,98,107,60,99,137,108,165)
    ) AND v.last_viewed > DATE_SUB(‘2017-09-22 19:51:13’, INTERVAL 1 WEEK) AND p.post_password = ” AND p.post_status = ‘publish’ GROUP BY v.postid ORDER BY pageviews DESC LIMIT 15

    If I update to latest version, would this be fixed? I have 13,000+ posts in my DB.

    Thanks,
    Calvin
    [email protected]

Viewing 6 replies - 1 through 6 (of 6 total)

  • I have this same exact problem.
    In fact, I was going to open a new thread.

    I love the plugin but I was forced to deactivate it. It consumes so many resources it’s crazy. Multiplying my server cPU usage by 2x 3x or even more.
    This couple past days I noticed it was worse and my VPS was toast.

    Last I tried I had:
    Refresh cache every 30 minutes
    Data sampling enabled.
    Sample rate 51.

    Any thoughts? thx.

    Plugin Author Hector Cabrera

    (@hcabrera)

    Hey guys!

    Let’s go by parts.
    – Jack The Ripper

    I am using ver 3.3.4
    The 24h popular vote was working fine for many months, then it showed no data on the widget window for past few days.

    That happened due to a breaking change in WordPress 4.8.2, released around two days ago now. They made a change that basically prevented WPP from updating views count. Check the changelog for more (and please upgrade WPP to the latest version available!)

    Today I changed the time frame to 7 days and it crashed the server after few hours.

    I love the plugin but I was forced to deactivate it. It consumes so many resources it’s crazy.

    Yes, due to its nature (basically a page views tracking script with cool additional features) WPP is a resource intensive plugin.

    While I’ve taken measures to make sure the code has been properly optimized and uses as little resources as possible, the truth is that no matter how optimized / fast the code is, without a proper configuration it won’t make a difference.

    I take it that both of your sites are large / high-traffic ones, so:

    • Make sure to enable the Data Sampling feature as suggested in the Installation instructions (I know you already did this @paulg2555, but @sjsusunny didn’t say if he was using it or not.)
    • Make sure you using the right Data Sampling Rate for your site. The default one (100) is recommended for sites that receive up to 250k visits/day. If you’re getting more than 250k visits per day, you should consider increasing the sampling rate. Similarly, if your site doesn’t get that many visitors but it has a lot of content (like Calvin’s does), a high sampling rate is recommended. How high? It depends on the amount of traffic your site gets, so you’ll have to play around with this value for a couple of days while keeping an eye on the performance of your site until you’re satisfied with the result.

    @paulg2555, you didn’t mention how large your site is but from the looks of it your data sampling rate might be a bit too low. Try increasing it a bit and see how it goes for a couple of days.

    I recommend reading this in its entirety to make sure you’re doing everything you can to help your site’s performance while using this plugin.

    Thanks. In my case 3k posts. around 25K daily users.
    I kind of solved the issue by just using the WPP widget and just displaying the most popular posts for the last 24H.

    Originally I was displaying popular posts in the homepage in 3 tabs with the shortcodes: 24H 7Days and all time.
    It was pretty cool but my CPU was going crazy, not sure why.

    Plugin Author Hector Cabrera

    (@hcabrera)

    If you’re not doing these already, here are a few other things I’d try:

    • Install a caching plugin. It’ll help speed up the site and have WordPress uses less resources, which should help reduce the performance hit on the server.
    • Check if the InnoDB engine is available (you can follow these instructions to see by yourself.)
    • If memcached is available on your server, consider installing the memcached plugin.

    Originally I was displaying popular posts in the homepage in 3 tabs with the shortcodes: 24H 7Days and all time.
    It was pretty cool but my CPU was going crazy, not sure why.

    If you can use memcached and you also enable WPP’s caching, I believe you should be able to add your 3 tabs back ?? (as long as you set a reasonable cache expiry time).

    • This reply was modified 7 years, 5 months ago by Hector Cabrera. Reason: Fixed typo

    Wow, you really made me suffer with this man!
    I’ve been struggling with this for more than 15 days and the truth was that I haven’t identified the problem because I’ve been using the plugins for more than a year and I haven’t caused any problems. You even made me buy a new theme thinking that’s what it was. Hahaha

    I’ve also read everything and set up the plugin correctly and everything is working beautifully now. The problem was actually escalating to the point where my site collapsed completely.
    I love this plugin and my page wouldn’t be the same without it.

    Many thanks for that brother! I’m proud of you.

    Saludos desde Barquisimeto!

    Plugin Author Hector Cabrera

    (@hcabrera)

    You could have said something sooner, @zontir ?? Glad to know everything’s fine now ??

    ?Un abrazo desde Caracas!

Viewing 6 replies - 1 through 6 (of 6 total)
  • The topic ‘Query overload and kill server’ is closed to new replies.