Magento 2 reindex pinned MySQL to 100%: a stale flat row

The 07:14 ping

The Slack message from the ops lead at a Dutch agency we work with came in at 07:14 on a Tuesday. Their Magento 2.4.6 catalog had been fine when the night-shift batch wrapped at 02:00. By the time the first warehouse picker logged in to print labels, the admin would not load. New Relic showed MySQL CPU pegged at 100% on a single mysqld process. The first guess was a cron flood. It was not. The Magento 2 catalog reindex had started on schedule at 06:33 and had been running for 41 minutes by the time we got the ping.

The store sits on a single-box LAMP stack: 14,000 simple products, three store views, 16 GB of RAM, a typical mid-market B2B catalog. The agency moved off a separate DB host last year to cut costs, against our advice. That decision is the only reason this incident registered as an outage instead of a slow morning, and it is worth naming up front: catalog_product_flat trouble on a shared box hits everything, including the admin login. The pickers were locked out because mysqld was starving everyone else for the cores PHP-FPM needed to render the dashboard. The warehouse manager works the floor from 06:30 and is patient about ten-minute outages. He is not patient about forty-minute ones.

First look at the box

SSH in. top confirms mysqld eating one core flat. No swap pressure, no disk I/O wait, just CPU. iostat -x 2 showed the data partition under 5% utilisation. That ruled out a stuck disk and confirmed the bottleneck was inside the query planner, not the storage layer. Before assuming Magento was the problem, we also checked the connection count and the InnoDB row-lock state:

SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW ENGINE INNODB STATUS\G

Threads_running was 23 on a box that normally idles at 4. InnoDB status showed no deadlock waiters and no long-held locks other than the one held by the indexer's own INSERT. So this was not contention; it was a single query running hot. We opened a second shell and ran the obvious thing:

SHOW FULL PROCESSLIST;

One query had been running for 41 minutes. It was an INSERT ... SELECT into catalog_product_flat_1. The Magento process had spawned it from the indexer. Confirming the indexer state took one command:

bin/magento indexer:status

That showed catalog_product_flat in the working state since 06:33. The reindex had started fine. It just never finished. Killing the query and restarting would buy maybe 90 seconds before the same INSERT pinned the same core again, and we had verified that pattern on a smaller incident at a different client two weeks earlier. We needed to know which row the query was tripping on, not just that it was tripping.

Tracing the query

Magento's flat indexer rebuilds catalog_product_flat_<store_id> from catalog_product_entity joined against the EAV attribute tables. The query the indexer emits looks roughly like this:

INSERT INTO catalog_product_flat_1 (entity_id, sku, type_id, ...)
SELECT cpe.entity_id, cpe.sku, cpe.type_id, ...
FROM catalog_product_entity AS cpe
LEFT JOIN catalog_product_entity_varchar AS v_name
  ON v_name.entity_id = cpe.entity_id
 AND v_name.attribute_id = 73
 AND v_name.store_id IN (0, 1)
LEFT JOIN catalog_product_entity_int AS i_status
  ON i_status.entity_id = cpe.entity_id
 AND i_status.attribute_id = 97
WHERE cpe.entity_id IN (...);

We pulled the full text from the slow query log at /var/log/mysql/slow.log, which the agency had already configured at a one-second threshold. Then we ran EXPLAIN on the SELECT half. The plan came back with this row, trimmed for width:

id  select_type  table  rows      Extra
1   SIMPLE       cpe    4823951   Using temporary; Using filesort

Eighteen thousand SKUs in the catalog should not produce four million scanned rows. The optimizer had decided one of the join sides was unselective and had given up on the index, falling back to a sort-merge plan that wrote to disk-backed temporary tables on every batch. The reference pages for SHOW PROCESSLIST and EXPLAIN output are the two MySQL docs worth bookmarking for this class of triage; the "Using temporary; Using filesort" combination on a join is the single biggest red flag in either.

The INSERT side made it worse. An INSERT ... SELECT holds shared locks on every row it reads in the SELECT half for the duration of the statement. So the longer the SELECT ran, the more of catalog_product_entity was locked against everything else. The admin login times out reading the same table to look up the user's store-view permissions, which is why the ops lead saw the admin go down before the storefront did. The storefront caches list pages at the edge for ninety seconds; the admin caches nothing.

Finding the orphan

We checked the second case first, because the query is cheap to run:

SELECT cpf.entity_id
FROM catalog_product_flat_1 AS cpf
LEFT JOIN catalog_product_entity AS cpe
  ON cpf.entity_id = cpe.entity_id
WHERE cpe.entity_id IS NULL;

It returned 47 rows. The oldest was from March. The newest had been added at 01:47 the same morning, in the middle of the night-shift import. We grepped /var/log/syslog for the same window:

grep -i 'killed process' /var/log/syslog | grep '01:4'

One hit at 01:48: the kernel OOM killer had taken out a PHP process holding 4.2 GB. That was the night-shift import, killed mid-stream on a 14,000-product CSV. The outer transaction wrapping each product create had rolled back. The inner work the indexer plugin had already committed on its own connection had not. So catalog_product_entity reverted. catalog_product_flat_1 did not. The morning reindex then tried to rebuild flat over the top of 47 entity_ids that no longer existed, and the join planner gave up on the index.

We sanity-checked one of the orphan rows directly:

SELECT * FROM catalog_product_entity WHERE entity_id = 184421;
-- Empty set

SELECT entity_id, sku, type_id, attribute_set_id
FROM catalog_product_flat_1 WHERE entity_id = 184421;
-- 1 row: sku NULL, type_id 'simple', attribute_set_id 4

The flat row had a null SKU. The indexer's downstream filters that prune by status were not pruning it, because NULL is not falsy in the way the indexer assumes. The join then expanded the scan across the EAV attribute tables, and the temporary table ballooned. Forty-seven null-SKU rows had turned an 18k-product reindex into a four-million-row table scan.

Cross-checking the other stores

Store 1 was clean. Before unlocking the admin we checked the other two store views, because the killed import would have written to all three flat tables in the same way. The same LEFT JOIN run against catalog_product_flat_2 returned 47 rows; against catalog_product_flat_3 it returned 31. The deltas matched what we expected: stores 2 and 3 share an attribute set and a status filter that excluded a handful of products, so the import had touched fewer of them in each.

We also widened the check to the search index, because the catalogsearch fulltext indexer reads from the flat tables on rebuild and a stale flat row will silently mismatch search results against product-detail-page availability until the next nightly reindex catches up. Same join, different left side, returned twelve mismatches. Better to flush all of them while you already have the admin offline.

The cleanup

Before deleting anything, we dumped the orphan rows. Even on an 11 KB result set, a flat-row delete that hits the wrong store view will take down product listings, and we wanted a paper trail. We used mysqldump rather than SELECT INTO OUTFILE so the dump came back as INSERT statements ready to replay on a staging instance if needed, and so the file ownership stayed under the agency's normal backup user rather than the mysql daemon:

mysqldump \
  --no-create-info \
  --where="entity_id IN (SELECT entity_id FROM (
    SELECT cpf.entity_id FROM catalog_product_flat_1 cpf
    LEFT JOIN catalog_product_entity cpe
      ON cpf.entity_id = cpe.entity_id
    WHERE cpe.entity_id IS NULL) x)" \
  client_db catalog_product_flat_1 \
  > /var/backups/orphan-flat-rows-2026-06-10.sql

The dump went to the agency's S3 bucket and to a local copy on the box, with the same dump repeated for stores 2 and 3. Then the deletes, one per store:

DELETE cpf
FROM catalog_product_flat_1 AS cpf
LEFT JOIN catalog_product_entity AS cpe
  ON cpf.entity_id = cpe.entity_id
WHERE cpe.entity_id IS NULL;
-- 47 rows affected

We reran the orphan-row SELECT against all three stores to confirm zero hits, then killed the wedged INSERT and reset the affected indexers:

mysql> KILL 4471823;

$ bin/magento indexer:reset catalog_product_flat catalogsearch_fulltext
$ bin/magento indexer:reindex catalog_product_flat catalogsearch_fulltext

The flat reindex finished in 94 seconds and fulltext in another 38. CPU dropped to baseline. Admin came back. The warehouse started printing labels. Total time from first ping to green: 38 minutes, including a coffee.

One sanity check before walking away: we timed a 1,000-SKU subset reindex against the full 18,000-SKU run. The subset took 5 seconds, the full run 94. The ratio matched the SKU ratio almost exactly, which is the cleanest signal you can get that no drift is hiding in the corners. A full reindex that runs more than 10–15% slower than its SKU-proportional baseline is still carrying ballast somewhere.

Why flat tables drift

Three things make catalog_product_flat_<n> a recurring sore spot on Magento 2 stores.

The first is denormalisation. The flat tables duplicate data from catalog_product_entity and the EAV attribute tables, by design, so storefront product listings can read from one table instead of nine. Any partial write that touches one source table but not the others leaves the flat table holding stale references. Adobe's own indexing documentation lists the catalog flat indexers among the most expensive of the standard set, and the reason is that they fan out across every store view. A store with three views and 18,000 products is rebuilding 54,000 flat rows on every full reindex.

The second is transaction-boundary mismatch. The indexer plugin opens its own database connection. A bulk import that gets killed by the OOM killer or by a deploy will roll back catalog_product_entity on its own connection, but the flat-table inserts on the indexer connection can already have committed. We have now seen this pattern on three different Magento 2 stores over the last 18 months, always after an OOM kill during a bulk import. Magento Commerce installations behave the same way; the asynchronous indexer queue does not change the connection model, it only defers when the writes happen.

The third is silent failure mode. The drift produces no error. The site keeps serving. The indexer keeps reporting valid on its status output, because as far as it is concerned, the last successful rebuild succeeded. The first symptom is a CPU spike during the next reindex, by which time the import that caused it is hours or days in the past and nobody connects the two. The Magento default monitoring stack has no probe for orphan rows. New Relic will tell you the INSERT is slow; it will not tell you why. We have seen drift survive eight weeks of uneventful reindexes on a store whose catalog rebuild ran inside its budget every night, only to collapse the morning a different import pushed the row count past whatever threshold flipped the optimizer's plan.

The check that prevents the next outage

The single highest-value thing you can add to a Magento 2 legacy site you inherit is an orphan-row check that runs before the indexer cron, not after the outage. Five lines of bash and a cron entry at 03:00:

#!/usr/bin/env bash
set -euo pipefail
for STORE in 1 2 3; do
  COUNT=$(mysql -N -e "
    SELECT COUNT(*) FROM catalog_product_flat_${STORE} cpf
    LEFT JOIN catalog_product_entity cpe ON cpf.entity_id = cpe.entity_id
    WHERE cpe.entity_id IS NULL;" client_db)
  if [ "$COUNT" -gt 0 ]; then
    curl -s -X POST -d "text=Magento orphan rows on flat_${STORE}: ${COUNT}" \
      https://hooks.slack.com/services/.../...
  fi
done

Adjust the store IDs to match whatever store views the merchant has configured. Run it before the indexer cron, so you have time to clear the rows before traffic arrives. The check has caught two outages-in-waiting on this same agency's stores in the eight weeks since we put it in.

You can also disable the flat catalog entirely (Stores, Configuration, Catalog, Storefront, set Use Flat Catalog Product to No) and the indexer goes away with it. On a store with this many filterable attributes that is a measurable category-page slowdown, on the order of 200 to 400 ms on listing renders, which is the cost of regular customer-service calls about page latency. The flat tables exist for a reason. Keeping them and running the orphan-row check costs less.

The agency also took two longer-term steps. They raised the import process memory limit to 6 GB and added a swap file the OOM killer can reach for before it goes after PHP, which buys headroom on a single-box stack without paying for a separate DB host. And they switched the catalog indexers from realtime to schedule mode, so flat-table writes route through the mview changelog queue instead of firing synchronously on every catalog write. Schedule mode does not eliminate the transaction-boundary drift, but it batches changes and surfaces failures as cron job errors rather than silent partial commits. Neither change replaces the orphan-row check. They reduce how often it has anything to report.

When we built Pier we ran into this exact pattern often enough that the MySQL editor ships with a saved query template for finding orphan rows in catalog_product_flat_<n>, and every destructive query goes through version history so a 03:00 cleanup that hits the wrong row can be rolled back in one click. The point is not the tooling, though. The point is that the orphan-row check belongs on every Magento 2 store you maintain, whatever you use to run it.

If you do one thing today, open the slow query log on your busiest Magento 2 store and grep for catalog_product_flat. Anything older than your reindex window is worth a look.