[Plugin: WordPress Popular Posts] To the Admin of WPP – Slow Queries

SantaCruze
(@santacruze)

12 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.

Viewing 15 replies - 1 through 15 (of 24 total)

1 2 →

Plugin Author Hector Cabrera
(@hcabrera)

12 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.

alexluft
(@alexluft)

11 years, 12 months 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

Plugin Author Hector Cabrera
(@hcabrera)

11 years, 11 months 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).

Racer-X
(@racer-x)

11 years, 11 months 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 ?

Plugin Author Hector Cabrera
(@hcabrera)

11 years, 11 months 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.

Racer-X
(@racer-x)

11 years, 11 months ago

It´s not a problem. Send me the bytes 😀
brokentwig
(@brokentwig)

11 years, 11 months 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)";
```
Plugin Author Hector Cabrera
(@hcabrera)

11 years, 11 months 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.

brokentwig
(@brokentwig)

11 years, 11 months ago

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

Plugin Author Hector Cabrera
(@hcabrera)

11 years, 11 months 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.

clecompte
(@clecompte)

11 years, 11 months ago

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

Plugin Author Hector Cabrera
(@hcabrera)

11 years, 11 months 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.

clecompte
(@clecompte)

11 years, 11 months 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!

Plugin Author Hector Cabrera
(@hcabrera)

11 years, 11 months 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.

jimbocook
(@jimbocook)

11 years, 10 months 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.

Viewing 15 replies - 1 through 15 (of 24 total)

1 2 →

The topic ‘[Plugin: WordPress Popular Posts] To the Admin of WPP – Slow Queries’ is closed to new replies.