Plugin crash cause MySQL query load time

Mysql query which crash:

SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, 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 IN('post', 'page') AND v.view_datetime > DATE_SUB('2018-04-22 13:02:12', INTERVAL 29 DAY) AND p.post_password = '' AND p.post_status = 'publish' GROUP BY v.postid ORDER BY pageviews DESC LIMIT 10 OFFSET 0

• This reply was modified 6 years, 11 months ago by DreamOn11.

Hi @dreamon11,

Please add the following code to your theme’s functions.php file to force enable Data Sampling:

function wp77654_force_enable_data_sampling(){
    return true;
}
add_filter( 'wpp_data_sampling', 'wp77654_force_enable_data_sampling' );

… or this much shorter version:

add_filter( 'wpp_data_sampling', '__return_true' );

If you have a caching plugin installed, flush its cache afterwards so changes are applied.

Remove this from your theme’s functions.php file when you’re done tweaking WPP’s performance settings.

Thanks @hcabrera for help.

Unfortunately, same result: 504 Gateway Time-out…

Running on VPS dual-core + SSD.

The query you posted earlier seems to come from the WPP widget. For some reason it’s taking WPP a lot of time to read from your database (which should be a fairly quick operation).

Remove the widget from your site and give it another shot.

As I wrote, I have disabled all WPP calls in current WP theme.

I think it is the first page of WPP admin page which show visits summary (day, month…).

That can’t be it, the query would look quite a bit different. The one posted above belongs to the “stock” query WPP builds when displaying the popular lists on the front-end. See:

SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, SUM(v.pageviews) AS pageviews

None of those fields are used in the back-end.

Other things I’d try:

• Disable all plugins -except for WPP- and check again.
• If the problem persists even after that, try switching to another theme for a moment and check one more time.

@hcabrera

Miraculously, this morning, the admin page works (maybe because low traffic).

I confirm the query is generated by this page, because admin page show stats : https://ps.w.org/wordpress-popular-posts/assets/screenshot-4.gif

So, I have activated Data Sampling and Caching.

I have a question about Data Logging.

“After enabling this feature the old data will be deleted every day around midnight.”
“Data older than the specified time frame will be automatically discarded.” ex: 180 days

So, if I active this option, views older than 180 days will be deleted?

Thanks.

So, if I active this option, views older than 180 days will be deleted?

Correct! Records older than N days will be deleted from the database automatically.