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 4 years, 6 months ago by Simplicity.