Post-Mortem: Magento Store With 28-Second Page Loads After a Catalog Import

Incident Overview

Two days after a 60,000-SKU bulk import, a Magento 2.4 store’s category pages were taking 20–30 seconds to render. Checkout still worked — eventually — but analytics showed cart abandonment tripling and paid-ad quality scores dropping as the landing pages timed out. The agency that ran the import had already doubled the server size twice; both upgrades bought nothing, which is the classic sign the bottleneck is algorithmic, not capacity.

Raw Diagnostic Logs

Time to first byte, measured from outside:

$ curl -o /dev/null -s -w "ttfb: %{time_starttransfer}s\n" \
    https://example-store.com/audio/headphones.html
ttfb: 27.834s

MySQL slow query log — one query dominating, running once per category request:

# Query_time: 24.11  Lock_time: 0.00  Rows_sent: 24  Rows_examined: 5872304
SELECT `e`.*, ... FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product` AS `cat_index`
   ON cat_index.product_id = e.entity_id AND cat_index.category_id = 847
 LEFT JOIN `catalog_product_index_price` AS `price_index` ...

5.8 million rows examined to return 24 products. The indexer state explained why:

$ bin/magento indexer:status
+---------------------------+----------------+-----------+
| Title                     | Status         | Schedule  |
+---------------------------+----------------+-----------+
| Category Products         | Reindex needed | invalid   |
| Product Price             | Reindex needed | invalid   |
| Catalog Search            | Reindex needed | invalid   |
+---------------------------+----------------+-----------+

And the reason the indexers had silently stopped:

$ tail -2 var/log/cron.log
[2026-07-18 03:12:40] main.CRITICAL: SQLSTATE[HY000]: General error: 1114
  The table 'catalog_product_index_price_temp' is full

Forensic Root Cause Analysis

Three stacked failures:

1. The import invalidated every index — expected behavior for a 60,000-SKU import via the standard importer.
2. Reindexing failed silently. MySQL’s tmp_table_size/max_heap_table_size were still at defaults (16 MB) while the price-index temp table now needed several gigabytes. Every scheduled reindex died with “table is full”, logged only to a cron log nobody watched.
3. Magento fell back to non-indexed queries. With flat indexes invalid, category rendering joined live EAV tables — the 5.8-million-row scan above — turning every uncached category view into a 24-second query. Redis full-page cache masked it briefly until the import’s cache flush, after which cold pages hit the slow path constantly.

Root cause: undersized MySQL temp-table limits made reindexing impossible after a catalog-scale change; everything else was downstream.

Remediation & Command Log

Fix the ceiling that killed the indexers, then reindex:

# /etc/my.cnf.d/zz-magento.cnf
[mysqld]
tmp_table_size      = 2G
max_heap_table_size = 2G
innodb_buffer_pool_size = 12G   # was 128M default on a 32G host

systemctl restart mysqld

php bin/magento indexer:reset
php -d memory_limit=4G bin/magento indexer:reindex
# Category Products index has been rebuilt successfully in 00:07:41
# Product Price index has been rebuilt successfully in 00:19:03
php bin/magento indexer:set-mode schedule   # mview/changelog mode — imports no longer block reads
php bin/magento cache:flush

Verify from the outside, cold cache:

$ curl -o /dev/null -s -w "ttfb: %{time_starttransfer}s\n" \
    https://example-store.com/audio/headphones.html
ttfb: 0.412s

28 seconds to 412 ms with no application code changed — the entire collapse was the invalidated index path.

Preventative Hardening

• Moved indexers permanently to schedule mode so bulk imports queue changelog entries instead of invalidating whole indexes mid-day.
• Added a post-import runbook step: indexer:status must show all green before the import ticket closes.
• Cron failures now alert: Magento’s cron.log CRITICAL lines ship to the monitoring channel instead of rotting on disk.
• Documented MySQL sizing in the post-repair report — the store had been running an enterprise catalog on distribution-default database limits since launch.
• Scheduled a quarterly load test of the top 10 uncached URLs with the numbers to expect, so “slow” has an objective baseline the client can check themselves.

---

Details in this post-mortem are anonymized and composited from recurring incidents of the same failure class. The logs and commands are representative of the actual diagnostic path. Store crawling after a catalog change? Our speed optimization service finds the query, not just the symptom.