Magento 2 Sitemap Generation Performance: Why It Spikes CPU, Memory and MySQL

Sitemap generation is one of those background jobs everyone ignores — until the sitemap cron starts pegging CPU and memory on a large catalog at midnight, collides with the price indexer, and makes the store crawl. The XML sitemap is tiny compared to your catalog, but the process that builds it walks your entire product, category and CMS content, so its cost scales linearly with catalog size. This guide explains exactly how Magento builds the sitemap, where the hidden cost comes from, and how to make generation fast, batched and off-peak.

How Magento builds the sitemap

Behind the scenes, Magento runs the sitemap_generate cron job, which is registered in Magento\Sitemap\etc\crontab.xml at the default schedule of 0 0 2 1 * — i.e. at 2 AM on the first of January by default. Most production setups override this in env.php or the admin (Stores > Configuration > Catalog > XML Sitemap) to run nightly or weekly. When it fires, the Sitemap model calls:

• Sitemap::generateXml() which collects products, categories and CMS pages per configured store view and renders each entry as a <url> node
• It resolves each entity's final URL via the URL rewrite table (url_rewrite), computes priority and change frequency from admin config, and optionally appends image URLs configured on products (images under sitemap settings)
• It writes the resulting XML to pub/media/sitemap/sitemap.xml (one file per store view)

The observable cost looks cheap: a few seconds, a few megabytes of XML. The real cost is internal.

Where the hidden cost comes from

1. Whole-catalog iteration on every run

Sitemap generation is not incremental. Every scheduled run walks every product, every category and every CMS page again, even if nothing changed. On a 100k-SKU catalog that is tens of thousands of entities each run, regardless of whether a single page changed since the last sitemap.

2. Per-entity URL and attribute resolution

Each product entry needs its final store_id-scoped URL, so the generator joins against url_rewrite and reads core EAV attributes for the entity. Naive versions of this pattern perform several queries per entity — a classic N+1. Newer Magento releases (2.4.x, especially 2.4.7+) batch entity resolution via SitemapItemResolver and getCollection() with chunked iteration, which is dramatically cheaper than the older per-entity resource models. If you are on 2.4.6 or older, part of your sitemap slowness is simply the un-batched resolver.

3. Whole-XML-in-memory

generateXml() builds the complete XML document in memory before writing it to disk. On a very large catalog the sitemap can reach tens of megabytes, so peak memory on the CLI worker spikes proportionally to catalog size. And because this runs on the same cron consumer pool, a memory-hungry sitemap run can tip a PHP worker over memory_limit and abort mid-generation, leaving a truncated sitemap on disk.

4. MySQL load on a full-table scan

Reading every product and rewriting it joins the catalog tables, url_rewrite, and the media gallery. On stores with heavy url_rewrite tables (the URL rewrite performance area), the sitemap cron can trigger a costly scan right when your nightly cron optimization and indexer jobs are also running — three heavy jobs competing for the same pool.

Diagnosing a slow sitemap run

Get a baseline before changing anything:

# Time a single generation run
time bin/magento sitemap:generate

# Watch real peak memory (set -d for the run only, keep the pool default)
/usr/bin/time -v php -d memory_limit=2G bin/magento sitemap:generate

Also record:

• The size and line count of pub/media/sitemap/sitemap.xml — compare across stores
• sitemap.log under var/log/ — by default Magento logs generation progress
• The MySQL query time during the run (SHOW FULL PROCESSLIST or your slow-query log) to catch full scans on url_rewrite or catalog_product_entity
• Whether the run is killed or times out — that is a truncated sitemap, not a completed one

If the run is crash-safe, quick, but still late at night colliding with other jobs, the fix is scheduling, not code.

The optimization playbook

1. Batch: upgrade your resolver

If you are on Magento 2.4.5/2.4.6, the single cheapest win is upgrading to a release with the batched SitemapItemResolver. It replaces per-entity queries with chunked collection iteration and cuts both runtime and query count by an order of magnitude on large catalogs. Measure before and after — this is the highest-ROI step.

2. Offset generation per store

The sitemap generates one file per store view. If you run multiple store views (see multistore performance), spread them instead of generating all simultaneously. You can trigger per-store generation programmatically with --store on bin/magento sitemap:generate, then schedule each store on a different time slot so no single cron window runs all stores at once.

3. Move it off the main cron window

Sitemap does not need to run alongside the price indexer or full reindex. Move it to a low-traffic, low-contention window — ideally a separate schedule from the admin cron group that runs indexers. A dedicated cron schedule (config:crontab:set wget "https://..." or a separate consumer) keeps sitemap out of the main cron pileup.

4. Shrink what gets included

Fewer entries means a smaller file and less work:

• Disable sitemap for store views you do not need (Stores > Configuration > Catalog > XML Sitemap > Enabled, per scope)
• Exclude entities that should not be indexed
• If product image URLs are enabled, they multiply XML size and add media-gallery joins — disable them unless your SEO setup needs rich results
• Keep url_rewrite clean and pruned; the sitemap reads it per entity, so orphaned rewrites make every scan slower (see the URL rewrite deep-dive)

5. Stream instead of buffer (large catalogs only)

If you are on an older version and a very large catalog, the in-memory generateXml() is the bottleneck. Replace the default model with a custom generator that iterates the collection in chunks and writes directly to a temp file, then renames it into place at the end. Chunked file writes keep peak memory flat and guarantee the live sitemap is never a truncated one. This is a small custom module and is the correct fix rather than raising memory_limit on the pool.

6. Serve it smartly

Share-cached sitemaps are served as static files from pub/media. Make sure the path is served by your web server directly (not routed through PHP-FPM) so the CDN and Varnish cache it, and confirm robots.txt points at the correct store-scoped file. If you route the sitemap through an HTTP handler, you can also delegate generation to a lightweight background request instead of the cron worker.

Monitoring

Add sitemap generation to your automated regression testing budget: assert that bin/magento sitemap:generate on your staging catalog (or a sized-down clone) completes under a budgeted wall-clock time and stays under a memory ceiling. A regression — e.g. a third-party module that hooks sitemap generation and triples its runtime — should fail CI, not silently slow your nightly job.

Summary

Sitemap generation looks trivial but walks your whole catalog on every run. The three levers that matter: use a batched resolver (upgrade for large catalogs), schedule it off-peak and per-store so it never collides with indexers, and shrink or stream the output so memory stays flat. With that, a midnight sitemap cron is a blip, not a spike.