[Résolu] Views Causing Lots of SQL Queries and Using up All Memory

[Auteur]

Publications

[DavidAlexander]

On the website hidden link I believe the views plugin randomly started doing 1000's of queries and is killing the memory and the site.

wp-views/embedded/inc/wpv-module-manager.php on line 686 shows an issue and even if I deactivate and reactivate all plugins the SQL queries begin again like below...

[15:44:44] ZXHOST: ( mt1.meta_key = '_featured' AND CAST(mt1.meta_value AS CHAR) = 'yes' )
) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date ASC LIMIT 0, 8 made by require('wp-blog-header.php'), r$
[06-Jul-2016 22:34:07 UTC] WordPress database error Server shutdown in progress for query SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.po$
( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog') )
AND
( mt1.meta_key = '_featured' AND CAST(mt1.meta_value AS CHAR) = 'yes' )
) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date ASC LIMIT 0, 8 made by require('wp-blog-header.php'), r$
[06-Jul-2016 22:34:07 UTC] WordPress database error Server shutdown in progress for query SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.po$
( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog') )
AND
( mt1.meta_key = '_featured' AND CAST(mt1.meta_value AS CHAR) = 'yes' )
) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date ASC LIMIT 0, 8 made by require('wp-blog-header.php'), r$
[06-Jul-2016 22:34:07 UTC] WordPress database error Server shutdown in progress for query SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.po$
( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog') )
AND
( mt1.meta_key = '_featured' AND CAST(mt1.meta_value AS CHAR) = 'yes' )
) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date ASC LIMIT 0, 8 made by require('wp-blog-header.php'), r$
[06-Jul-2016 22:34:07 UTC] WordPress database error Server shutdown in progress for query SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.po$
( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog') )
AND
( mt1.meta_key = '_featured' AND CAST(mt1.meta_value AS CHAR) = 'yes' )
) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date ASC LIMIT 0, 8 made by require('wp-blog-header.php'), r$
[06-Jul-2016 22:34:07 UTC] WordPress database error Server shutdown in progress for query SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.po$
( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog') )
AND
( mt1.meta_key = '_featured' AND CAST(mt1.meta_value AS CHAR) = 'yes' )
) AND wp_posts.post_type = 'product' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date ASC LIMIT 0, 8 made by require('wp-blog-header.php'), r$
[06-Jul-2016 22:34:07 UTC] WordPress database error Server shutdown in progress for query SELECT wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.po$
( wp_postmeta.meta_key = '_visibility' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('visible','catalog') )
[15:44:50] ZXHOST: That is just part of the huge query its running
[15:45:10] ZXHOST: Just keeps running that query

Any ideas? I can't use or access the site while it is like this.

[DavidAlexander]

Why is it stuck in a loop doing these queries? any ideas? I am not running anything but these queries end up sucking up all of the memory. I haven't made any changes to the site this week however this just began and the site has now been down for 24 hours.

[Minesh Supporter Languages: Anglais (English ) Timezone: Asia/Kolkata (GMT+05:30)]

Hello. Thank you for contacting the Toolset support.

I see that you are using the cache plugin.

Could you please try to resolve your issue by deactivating all third-party plugins as well as with the default theme to check for any possible conflicts with any of the plugins or themes?

[DavidAlexander]

I have tried that already, I can't deactivate everything before the site runs out of memory. But I deactivated most and I am sure its coming from WP Views.

I started by deactivating the two cache tools, 1) w3 total cache and 2) Autoptimize. This didn't fix the problem.

Is there anyway to kill any processes being run by the views plugin?

[DavidAlexander]

Okay UPDATE:

So I hired someone on UpWork to assist me and work out the cause. Most of the queries were a result of crawlers so I have followed most of the steps in hidden link to block crawlers and bots I don't care about and have reduced queries by Google, Bing and some others. They may take a while to take effect but already the site is running a bit more smoothly however performance isn't great.

So I want to look at the issues and the queries going on with wp-views to see if I have an issue on my products template or single product template where a query is wrong or a query within a query is leading to some "query loop" I may have read about on this forum when doing research.

So can someone look at my views templates without editing/saving so they can advise as to if something is wrong if I provide the logins now the site is accessible again? The site is far too big to export etc for replication and I have spent a lot of money already trying to fix this and the site has been down for 2-3 days and losing money.

Thanks

[Minesh Supporter Languages: Anglais (English ) Timezone: Asia/Kolkata (GMT+05:30)]

1)
For Toolset, this DOC gives great help on Performance and Parametric Search improvements:
=> https://toolset.com/documentation/user-guides/how-to-use-views-parametric-search-on-large-sites/

2)
W3TC is VERY tricky and if set up wrongly it will SLOW DOWN your site instead of speeding it up, additionally most of times it conflicts with Toolset, as Toolset provides it's own cache mechanism, as above DOC also illustrates.
hidden link

3)
Could you please also try to check performance with only Toolset plugins + default theme?

Does this help?