Yearly sitemaps use too much memory and make very long queries

Hi Igor, I’m not sure why the memory usage is so high. It should not be that elevated for 2k posts… The plugin does use some mild caching but that will be improved in future releases. They will remain dynamic, however, for reasons of compatibility with multisite.

For now, in your case it sounds best to switch to monthly sitemaps.

Forgot to answer your question:

Question is, why is a sitemap being generated on every request? Especially for a year, that has already concluded?

This is because the dates in a sitemap are modification dates, not publication dates. And depending on plugin settings, even a new comment can be seen as a modification. So past years can still change regularly ??

Hello, Ravan. That’s why I suggested updating it when a new object is added or existing one is updated. If it’s configured to count comment as a modification (not in our case, we don’t even have comments), then update it when a new comment is added. In all other cases, to me personally, it makes no sense to have it dynamic. As I see it, if you made it the way I suggest it’d be a huge performance boost. As for multi site compatibility, I do not see what prevents you from having slightly different logic for single and multi site installation.

Regarding you suggestion to use monthly sitemaps, that’s just a temporary solution.

Regarding you suggestion to use monthly sitemaps, that’s just a temporary solution.

Why do you see that as temporary?

Because the amount of posts keeps growing fast in our case. Even monthly sitemap will use a lot of resources. And if someone has weaker environment, they could encounter such a problem with a smaller amount of posts, don’t you think?

… ridiculously high usages of memory (500+ MB) and very long queries (17k+ character).

The thing is, this is not normal. The plugin as been tested with 4.5k posts and did not go higher than around 120 MB peak memory usage.

With WP_DEBUG on, the debug info in the full sitemap (all posts in the same sitemap, no splitting by year or month) shows on the first request:
<!-- Queries executed: 11651 | Peak memory usage: 120.56M | Memory limit: 256M -->
and on subsequent requests, when the post meta cache has been primed:
<!-- Queries executed: 31 | Peak memory usage: 120.56M | Memory limit: 256M -->

With a persistent object cache active (like WP Redis) this can even go as low as:
<!-- Queries executed: 2 | Peak memory usage: 69.93M | Memory limit: 256M -->
when both post meta cache and object cache are fully primed.

I cannot explain the 17k+ character queries either…

What other plugins are you using?

You can see an example of the debug info in the response source code (Ctrl+u, at the bottom) on https://dev.status301.net/sitemap-posttype-post.2019.xml with 3.4k posts in this yearly sitemap.

These posts where created with the plugins FakerPress, Posts Generator and importing WordPress sample data. The post sitemap settings are Automatic Priority calculation ON; Update the Last Changed date on each new comment ON; Add image tags for “Attached images” which should be the most resource hungry.

There are no other plugins active, WP_DEBUG is set to true.

Response times are not optimal but not excessive either, served from a low-end VPS shared with nearly 200 other WordPress sites.

I really wonder why memory usage could go up to and over 500 MB on your site because that’s about 5 times the memory used for 2/3 of the amount of posts…

<!-- Queries executed: 36 | Peak memory usage: 154.18M | Memory limit: 512M -->
For a year of 2016, 832 posts.
<!-- Queries executed: 58 | Peak memory usage: 412.63M | Memory limit: 512M -->
For a year of 2017, 1828 posts.
<!-- Queries executed: 56 | Peak memory usage: 512.05M | Memory limit: 512M -->
For a year of 2018, 2096 posts.
<!-- Queries executed: 2549 | Peak memory usage: 25.5M | Memory limit: 512M -->
For a year of 2019, 2495 posts.
And now,
<!-- Queries executed: 7395 | Peak memory usage: 59.54M | Memory limit: 512M -->
for no split, 7317 posts.

No idea why there is such a difference in queries and mem usage for current year and previous ones. But this seems to be a direction to dig in.

• This reply was modified 4 years, 11 months ago by Igor Yavych.

That’s indeed very strange. So it seems that the No Split option is the best one, which is not logical. By the way, on second request, the no-split one should have much less queries as results are cached in the meta cache. Still, the total response time may be too slow…

Can you share a link to your sitemap?

Hello. Happy New Year, Ravan.
Amount of queries does not get lower during subsequent requests.
Unfortunately, I can’t keep it at no split option as this is production environment and it still causes very long queries that get killed, ~49k to 57k characters long

… very long queries that get killed, ~49k to 57k characters long

The weird thing is that this plugin does not create its own queries for regular post sitemaps. It uses the main WP query and loop.

Any possibility you can send me an example of these queries? You can contact me directly via https://status301.net/contact/

Sent you these queries via your contact form. As you can see, problem with long queries arises due an in condition in them.

OK, received them… so you have more than 7k posts in one year? You really should switch to monthly sitemaps ??

That one was for non split.
I did switch to monthly split, but like I said that’s just a temporary solution. Something should be done about these uber long conditions. For one, you could process them in chunks of 100, 200 or whatever else works. Current way causes problems, as you can see, for basically any installation with a lot of posts.
And then, there is still that problem of previous years and memory usage for generating those sitemaps. No matter how you look at it, that kind of behavior is not normal.

that’s just a temporary solution

Yes, you said that before ?? but I don’t see why. Or are you planning to post more than 2k posts per month? That seems kind of spammy to me…