Plugin suggestion – Performance – Fatal error: Allowed memory size exhausted

As of v4.1.19, google-sitemap-generator/class-googlesitemapgeneratorstandardbuilder.php file inside the build_posts function, the plugin currently fetches all posts and then uses array_slice for offsetting.

This will work fine for sites with a small number of posts, however, as the number of posts increases, such that maybe post-sitemap1000.xml, this will have a huge memory footprint since we are slicing further into the array and handling a large number of posts.

Eventually, depending on the allocated WordPress memory, the function will run out of memory the further the slicing goes into the array.

Referring to $posts = array_slice( $posts, ( $limit - $links_per_page )

Current code

			// Statement to query the actual posts for this post type.
			$qs = "
				SELECT
					p.ID,
					p.post_author,
					p.post_status,
					p.post_name,
					p.post_parent,
					p.post_type,
					p.post_date,
					p.post_date_gmt,
					p.post_modified,
					p.post_modified_gmt,
					p.comment_count
				FROM
					{$wpdb->posts} p
				WHERE
					p.post_password = ''
					AND p.post_type = '%s'
					AND p.post_status = 'publish'
					{$ex_post_s_q_l}
					{$ex_cat_s_q_l}
				ORDER BY
					p.post_date_gmt DESC
				LIMIT
					{$limit}
			";
			// Query for counting all relevant posts for this post type.
			$qsc = "
				SELECT
					COUNT(*)
				FROM
					{$wpdb->posts} p
				WHERE
					p.post_password = ''
					AND p.post_type = '%s'
					AND p.post_status = 'publish'
					{$ex_post_s_q_l}
					{$ex_cat_s_q_l}
			";
			// phpcs:disable
			$q = $wpdb->prepare( $qs, $post_type );

			// phpcs:enable
			$posts      = $wpdb->get_results( $q ); // phpcs:ignore
			$posts      = array_slice( $posts, ( $limit - $links_per_page ) );
			$post_count = count( $posts );

Instead of fetching everything and then offsetting, why don’t we only fetch what we need. Use the database to offset instead of using PHP.

Proposed code

			// Statement to query the actual posts for this post type.
			$qs = "
				SELECT
					p.ID,
					p.post_author,
					p.post_status,
					p.post_name,
					p.post_parent,
					p.post_type,
					p.post_date,
					p.post_date_gmt,
					p.post_modified,
					p.post_modified_gmt,
					p.comment_count
				FROM
					{$wpdb->posts} p
				WHERE
					p.post_password = ''
					AND p.post_type = '%s'
					AND p.post_status = 'publish'
					{$ex_post_s_q_l}
					{$ex_cat_s_q_l}
				ORDER BY
					p.post_date_gmt DESC
				LIMIT
					%d, %d
			";
			// Query for counting all relevant posts for this post type.
			$qsc = "
				SELECT
					COUNT(*)
				FROM
					{$wpdb->posts} p
				WHERE
					p.post_password = ''
					AND p.post_type = '%s'
					AND p.post_status = 'publish'
					{$ex_post_s_q_l}
					{$ex_cat_s_q_l}
			";

			// Calculate the offset based on the limit and links_per_page
			$offset = max( 0, ( $limit - $links_per_page ) );

			// phpcs:disable
			$q = $wpdb->prepare( $qs, $post_type, $offset, $links_per_page );

			// phpcs:enable
			$posts = $wpdb->get_results( $q ); // phpcs:ignore
			
			$post_count = count( $posts );

During my tests, this solved memory leaks caused by huge datasets in the array, and could theoretically handle even post-sitemap9999.xml without timing out or running out of memory compared to the old code.

This will also fix

Fatal error: Allowed memory size of X bytes exhausted (tried to
allocate X bytes)

Happy to discuss further.

• This topic was modified 10 months, 1 week ago by humblearab. Reason: improvement