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.

Viewing 5 replies - 1 through 5 (of 5 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.

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

You must be logged in to reply to this ticket.