[Support request] Heavy MySQL Usage via admin-ajax.php

Please login to receive premium support.

Support for the free plugin can be found here.

Home Forums Pro Support Heavy MySQL Usage via admin-ajax.php

This topic contains 17 replies, has 2 voices, and was last updated by  Tom 2 days, 20 hours ago.

Viewing 15 posts - 1 through 15 (of 18 total)
  • Author
    Posts
  • #9429

    Jake
    Participant

    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.

    • This topic was modified 1 month ago by  Jake.
    #9443

    Jake
    Participant

    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.

    #9450

    Tom
    Keymaster

    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/value

    I wonder if it’s a specific list you’ve created?

    #9460

    Jake
    Participant

    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):

    1. 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)
    2. 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.

    #9482

    Tom
    Keymaster

    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?

    #9490

    Jake
    Participant

    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.

    #9491

    Jake
    Participant

    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 drafted

    #9505

    Tom
    Keymaster

    Hmm, 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?

    #9593

    Jake
    Participant

    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?

    #9613

    Tom
    Keymaster

    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#L15

    #9639

    Jake
    Participant

    Thanks 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?

    #9683

    Jake
    Participant

    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 🙂

    #9724

    Tom
    Keymaster

    Strange, let’s try something.

    Open that admin.php file 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.

    #9743

    Jake
    Participant

    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.

    #9760

    Tom
    Keymaster

    ! is_user_logged_in() should work for now. I’ll look into a better method for our next update.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic.