- This topic has 21 replies, 2 voices, and was last updated 4 years, 1 month ago by
.
-
Posts
-
Hi Tom,
I’ve been contacted by my web hosting company due to heavy MySQL usage. Here are the queries identified as causing the issue:
======================================================================================================== SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (5533377,1029699,26435,26298,26283,26280,26279,26278,26277,26276,26275,26274,26273,26272,26271,26270,26269,26268,26267,26246,26260,26259,26258,26257,26256,26255, 26254,26253,26252,26251,26250,26249,26248,26247,26238,26237,26233,26231,26230,26228,26226,26227,26224,26225,26223,26222,26221,26220,26219,26217,26216,26215,26214, 26211,26210,26208,26209,26207,26203,26202,26201,26200,26199,26198,26197,26196,26195,26194,26178,26184,26183,26182,26181,26180,26179,26177,26176,26175,26174,26173,26169,26168,26163,26164,26165,26154,26152,26151,26150,26149,26148,26147,26146,26145,26144,26143,26142,26137,26136,26129,26133,26132,26131,26130,26128,26127,26126, 26008,25969,25965,25966,25967,25968,25970,25923,25922,25932,25931,25930,25929,25928,25927,25926,25925,25924,25921,25912,25913,25915,25914,25916,25892,25891,25876, 25881,25880,25879,25878,25877,25875,25874,25859,25860,25842,25841,25848,25847,25846,25845,25844,25843,25816,25815,25814,25795,25802,25801,25800,25799,25798,25797, 25796,25782,25754,25741,25739,25738,25734,25733,25732,25731,25730,25729,25727,25708,25395,25408) ORDER BY meta_id ASC SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = 'wbcr_inp_snippet_scope' AND wp_postmeta.meta_value = 'shortcode' ) AND ( mt1.meta_key = 'wbcr_inp_snippet_activate' AND mt1.meta_value = '1' ) ) AND wp_posts.post_type = 'wbcr-snippets' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC ========================================================================================================I’m not 100% sure, but this may be linked to wpshowposts. I say this because the first query seems to be referencing all of the WP Show Post IDs I have on the site (and nothing else).
Comment from my web hosting company:
“Note: The first query is being executed at least 20-30 times per minute.
The queries might seem light but are being executed continuously on probably every visitor/bot.”
20-30 times per minute is about the average traffic to my site, so it seems this is executing every time a visitor visits the site.
I currently cache the site via Cloudflare (full cache – including HTML) and W3 total cache, but that doesn’t seem to be helping.
Also I’m unsure if this is related – but I currently use the following code (as recommended by you previously) to limit access to the admin area of WPSP:
add_filter( 'register_post_type_args', function( $args, $post_type ) { if ( 'wp_show_posts' !== $post_type ) { return $args; } $args['capability_type'] = 'manage_options'; return $args; }, 10, 2 );I mention this in part because the second part of the above query seems to be referencing woody snippits (https://wordpress.org/plugins/insert-php/). Any insight you could provide would be much appreciated. Thank you for your help Tom.
Hi Tom,
After disabling the plugin the queries seemed to disappear, so we’re fairly sure these queries are coming from wpshowposts.
Any ideas on how we can reduce this mySQL usage? I’m also a bit confused as to what this query is doing, since it seems to be referencing all of the POST IDs exclusive to WP Show Posts.
Hi there,
That looks like a standard query WordPress makes to display a list of posts. It’s a heavy query because:
1. There’s a bunch of individual IDs specified.
2. It’s also querying a meta key/valueI wonder if it’s a specific list you’ve created?
Hmm, that’s odd.
To clarify my previous comment, all of the Post IDs mentioned in the query are posts from WP Show Posts. I don’t actually have any “Posts” (literally none) on the site – it’s all pages.
There are only two types of lists I’ve created (using wp show posts):
- A list for every category (references the wordpress category). Each one of these wp show post shortcodes appears on 1 page only, often with other similar wordpress category wp show post lists (e.g. https://imgur.com/bcmNE95)
- A list that includes every page on the site (https://imgur.com/S7l9S4w). This appears on basically every page on the site. Although this is suspect given its prominence across the site, note that this references pages – not posts. And all of the IDs listed in the query above are posts (from wp show posts)
Really am stumped on this one. Is there any way to reverse engineer where this query is coming from? As mentioned, it disappears when I disable the plugin.
Thank you for your help Tom.
So every single one of those IDs is a WPSP list? This query is happening on the front-end? There isn’t anything that would query every list created – the only queries should be ones made by an individual list.
Does the query go away if you un-publish (draft) your lists?
That’s correct Tom – all of them are from WPSP (yes I have that many – I love your work ;)). I’m not sure what you mean by “happening” on the front end – but the query is being executed every time someone visits the site. Note that all pages have at least 1 WPSP on the page, since I use one of them as a shortcode executing in the sidebar.
When I drafted one of the posts, the same query executed but without the post that I had drafted (i.e. the drafted post disappeared from the query).
To further troubleshoot this issue, I have added a “post” to the site (there are currently none, only pages). I will see if this “post” appears in the query, or if the post IDs are still exclusive to WP Show Posts. I will report back with this information asap.
To clarify the above:
*When I drafted one of the WPSP lists
posts, the same query executed but without the post ID (i.e. the WPSP list) that I had draftedHmm, so the query is happening on the front-end of the site if it’s happening each time someone visits it.
I wonder where exactly the query is coming from, as WPSP doesn’t query its own lists, it only queries posts.
If you add the Query Monitor plugin to your site, can you share the “Caller” stack?
Apologies for the late reply Tom, I was trying to sort out this issue (we fixed the caching on Cloudflare’s side, but the queries are still present in bulk when I’m forced to purge the cache). I couldn’t find the query using the query monitor plugin.
I spoke to my hosting about it, and they said:
“The queries are most likely not shown in the query monitor because they are somehow connected to the ‘admin-ajax.php’.
Unfortunately, we do not have the debug tools in order to identify the direct caller.”
We tried disabling other plugins but the queries were still present. Do you have any other ideas as to what this might be?
Query Monitor should actually log AJAX queries.
WPSP does use
admin-ajax.php, but our scripts that make AJAX calls only exist in the Dashboard – not on the frontend of the site: https://github.com/tomusborne/wp-show-posts/blob/master/admin/admin.php#L15Thanks for the information and link Tom. Response from my hosting company:
“This was one of the first things that I have tried that but didn’t see the query in the Query Monitor on any of the pages of the staging site.
If it’s a background task then it might be a WordPress cronjob but all cronjobs that are currently active on the site are with a minimum recurrence of 1 hour.
I doubt that the query will be displayed because the ‘admin-ajax.php’ is being called with a delay on the front-end due to the loading priority of the files/scripts.”
So if your plugin only makes calls on the dashboard, then can we rule out that its WP Show Posts directly? It seems like we’re leaning towards the possibility that it’s another plugin that’s for whatever reason iterating through the posts in WP Show Posts?
Hi Tom,
My hosting company (WPX Hosting – I need to shout them out because they have been gems in holding my hand through this) has just made a very useful discovery. I’ll quote:
*Begin quote*
I have just tested that but the query is directly called from the action ‘admin_init’ with function ‘wpsp_pro_update_read_more_values’ in WP Show Posts Pro – ./wp-show-posts-pro/inc/admin.php – https://imgur.com/3Gpa3pw which is somehow bound to every ‘admin-ajax.php’ call.Note: admin_init – “It runs on admin-ajax.php and admin-post.php as well.” – Source: https://codex.wordpress.org/Plugin_API/Action_Reference/admin_init
Here is what I tested on the staging site:
1. I disabled/enabled each plugin and checked on which request was the query called.
-The query was called on every ‘admin-ajax.php’ request when I was logged in in the ‘wp-admin’ area of the site even if all plugins were disabled except for ‘WP Show Posts/Pro’.2. When there aren’t any ‘admin-ajax.php’ calls then there isn’t an issue because this specific function is not being executed on the front-end of the site.
-If Thrive Leads is disabled then there aren’t any ‘admin-ajax.php’ calls and the queries are not being called on the front-end for non-logged in users.
-This can happen with other plugins that use the ‘admin-ajax.php’ for handling actions, as proof, I have tested it with the plugin ‘Post Views Counter’ that runs an ‘admin-ajax.php’ call on every post/page visit and managed to see the query running on each one when Thrive Leads was disabled.If ‘WP Show Posts’ doesn’t make the query via the ‘admin_init’ on each ‘admin-ajax.php’ call then those queries won’t be called on the front and backend of the site.
I hope this helps and the plugin developer might be able to provide a fix that can avoid the query call on each ‘admin-ajax.php’ request.
*End quote*
Don’t really have much else to add to this – I hope that helps Tom 🙂
Strange, let’s try something.
Open that
admin.phpfile and find the function:function wpsp_pro_update_read_more_values() {Then we want to check if we’re in the admin directly inside the function:
function wpsp_pro_update_read_more_values() { if ( ! is_admin() ) { return; }Let me know if that fixes it.
Hi Tom,
Thank you for the code fix. Response from WPX (paraphrasing):
“!is_admin() won’t work because it returns ‘true’ on ajax requests but !is_user_logged_in() seems to work for front-end requests but I don’t know what is best, so the developer should decide.
The query will run on every back-end request so there will be less usage, hopefully, the plugin will be updated with a similar fix as the developer recommends.
I’m not sure that users will need this query to run on every page(with ‘admin-ajax.php’) when they are logged in.
Thank you to the developer because he didn’t ignore the case, we have had similar cases from other plugins that were just ignored without any performance fixes!”
So it seems like a significant improvement at the very least! Thank you very much for your assistance on this issue Tom. I’m sure you have loads of priorities, but do you plan on implementing this or a similar fix as an update to the plugin?
Thank you kind sir.
- You must be logged in to reply to this topic.