Sitemap module queries that overloads mysql

  • Hi,

    Last couple oof days, I think since last update (in last update we updated several versions), I’m not quite sure, we noticed a huge amount of temp tables and queries that do not look odd at first sight, bit they just keep executing leading to mysql colapse.

    When I disable sitemap module everything goes back to normal. If I enable it again everything colapses in matter of minutes.

    • I have around 500k records in posts table.
    • I have caching that caches sitemaps to filesystem regardless of your plugin settings.
    • I have object caching with memcached.
    • I have disabled transients cache for sitemaps module since I noticed it adds additional load that I want to avoid.
    • I don’t have db caching on app level but rather on mysql level and the hit/miss ratios are quite fine.
    • I have regular cleanup processes in db, tmp, etc, so there is no garbadge data 🙂

    Everything was working like a charm until couple days ago.

    In this file /seo-by-rank-math/includes/modules/sitemap/providers/class-post-type.php:

    This query locks mysql totaly and creates a tons of temp tables
    (our disk temp tables are created for above 600MB temp tables)

    117  			if ( $max_pages > 1 ) {
    118  				$sql = "
    119: 				SELECT post_modified_gmt
    120  					FROM ( SELECT @rownum:=@rownum rownum, $wpdb->posts.post_modified_gmt
    121  					FROM ( SELECT @rownum:=0 ) r, $wpdb->posts

    Additionaly, this query

    257  		$sql = "
    258: 			SELECT COUNT({$wpdb->posts}.ID)
    259  			FROM {$wpdb->posts}
    260  			{$join_filter}

    Adds up to the chaos.

    First query is on top of the list of query times, and afterwards there are hundreds of second query.

    Just to give you some additional details. Server load doesn’t go above 2 on regular, or 8 on cache regeneration times, regardless of the visit, but when I enable sitemap module it goes obove 150 🙂

    If you need any additiona info just let me know.

    • This topic was modified 2 months ago by Simplicity.
Viewing 13 replies - 1 through 13 (of 13 total)
  • Hello,

    Thank you for contacting Rank Math and sorry for any inconvenience that might have been caused due to that.

    I am not sure, how the queries you mentioned would cause any problem. Those queries are needed to show and sort the posts in the Sitemap. Also, in the recent update, we have not changed anything in the Sitemap module. Did you change anything on your site a couple of days ago, which could have caused this issue?

    https://rankmath.com/kb/check-plugin-conflicts/
    To determine no other plugin is causing this, can you please follow this troubleshooting guide?

    I hope that helps.

    Hi,

    As I mentioned, queries by themselfes do not seem suspicious, but when you have hundreds or thousands of them, or you are working with large db things can get messy.

    Have you checked this https://rankmath.com/changelog/

    As I can see there were code changes to the sitemaps module in the last month or so.

    I will do your code inspection in the next couple of days and let you know.

    Regards.

    Hello,

    Thank you for contacting Rank Math today.

    In relation to Sitemap module, there has only been a fix which was in version v1.0.42. There has been two releases after the fix. Please let us know if you find any issues with code inspection.

    Looking forward to helping you. Thank you.

    ​​​​​​

    Hi,

    Do you have some issues related to page caching of sitemaps? I’m using W3TC Page Cache to disk – enhanced?

    Somehow I think Rang Math sitemaps bypass page cache. I have page cached sitemap files in w3tc cache dir but response times for sitemap requests are quite high – so they are not served from w3tc page cache but from other source.

    There are no files in /plugins/seo-by-rank-math/sitemap-cache btw.

    Thanks.

    Hello,

    Since Rank Math caches by itself the sitemaps, they shouldn’t be cached. The weird thing is you have no files in the sitemap-cache directory.

    Could we take a closer look at the settings? Please edit the first post on this ticket and include your WP logins in the designated Sensitive Data section.
    Sensitive Data Section

    It is completely secure and only our support staff has access to that section. If you want, you can use the below plugin to generate a temporary login URL to your website and share that with us instead:

    https://wordpress.org/plugins/temporary-login-without-password/

    You can use the above plugin in conjunction with the WP Security Audit Log to monitor what changes our staff might make on your website (if any):

    https://wordpress.org/plugins/wp-security-audit-log/

    We really look forward to helping you.

    Hi,

    I’ll activate sitemap module tomorrow morning and send you access details, just to be able to follow up the load and the situation.

    Regards.

    Hello,

    Perfect, we will be here to continue helping you, thank you for the follow-up.

    Looking forward to help you.

    Hello,

    I have updated the sensitive data as requested. Can you please check further?

    Thank you.

    Hi,

    I tried to activate sitemap module today and I got the same problem, load that makes mysql very unhappy.

    I had to disable it again.

    One thing I noticed is that I do not get high load right away, but after some time as soon as I have higher traffic + more post published (so the W3TC Page Cache invalidates sitemaps cache more often).

    As soon as I disable sitemap feature from Rank Math plugin (sometimes I have to restart php-fmp also) everythign goes back to normal.

    Hello,

    Thank you for contacting Rank Math today.

    I was able to access your site, I enabled the sitemap module in troubleshooting mode and took sometime on your site but I couldn’t replicate the issue. Your site seems to also have performance issues, please test and check the recommendations here: https://gtmetrix.com

    Looking forward to helping you. Thank you.

    ​​​​​​

    Hello,

    I have updated the sensitive data as requested. Can you please check further?

    Thank you.

    Hi,

    I’m aware that you need to fire those queries, but the question is when and how do you fire them.

    At night, when there is low traffic and more important no post publish events there are no issues with this.

    But right know for example, I have the same queries that are in mysql pool executing.

    There are no other queries but 18 of your plugin?

    (I added queries in sensitive data section)

    The server load can’t go bellow 20 with sitemap module active. When I deactive it it goes back to normal, beloow 1.

    Plese do not be Yoast SEO, I really want to help creating a great alternative.

    Hello,

    I can see that your site loads quite slowly right now. As my colleague said, we haven’t made any changes recently that could explain why this started to happen on your site.

    I can imagine that it’s related to the large number of posts on your site (you have almost 400k posts, as I see) or to the caching plugin you are using.

    I have taken note of this issue and we will investigate it in the future. For now I can only suggest disabling the sitemap module in Rank Math and using a different plugin for this purpose.

    As you may know, XML sitemaps functionality will soon be added in WordPress core. We are in the process of rewriting the sitemap module to make use of this new API soon to come in core. That will most probably solve this issue once and for all.

    Thank you for your patience in this matter. If there’s anything else, please let us know.

    Hello,

    Since we did not hear back from you for 15 days, we are assuming that you found the solution. We are closing this support ticket.

    If you still need assistance or any other help, please feel free to open a new support ticket, and we will be more than happy to assist.

    Thank you.

Viewing 13 replies - 1 through 13 (of 13 total)

The ticket ‘Sitemap module queries that overloads mysql’ is closed to new replies.