Magento 2 reindex op 100% MySQL: een verouderde flat-rij

De ping om 07:14

Het Slack-bericht van de ops lead bij een Nederlands bureau waar we mee werken kwam binnen om 07:14 op een dinsdag. Hun Magento 2.4.6 catalogus had prima gedraaid toen de nachtbatch om 02:00 klaar was. Tegen de tijd dat de eerste magazijnpicker inlogde om labels te printen, laadde de admin niet meer. New Relic liet MySQL CPU zien, vastgenageld op 100% op één mysqld-proces. Eerste gok: een cron-overflow. Dat was het niet. De Magento 2 catalogus-reindex was om 06:33 op schema gestart en draaide al 41 minuten toen we de ping kregen.

De winkel draait op een single-box LAMP-stack: 14.000 simple products, drie store views, 16 GB RAM, een typische mid-market B2B-catalogus. Het bureau heeft vorig jaar de aparte DB-host opgeheven om kosten te besparen, tegen ons advies in. Die beslissing is de enige reden dat dit incident als storing telde in plaats van als trage ochtend, en het is de moeite waard om dat vooraf te benoemen: trouble met catalog_product_flat op een gedeelde bak raakt alles, ook de admin-login. De pickers waren buitengesloten omdat mysqld iedereen anders uithongerde voor de cores die PHP-FPM nodig had om het dashboard te renderen. De warehouse manager loopt vanaf 06:30 op de vloer en is geduldig bij storingen van tien minuten. Bij veertig minuten is hij dat niet.

Eerste blik op de bak

SSH erin. top bevestigt dat mysqld één core volledig opeet. Geen swap-druk, geen disk-I/O-wait, alleen CPU. iostat -x 2 liet zien dat de data-partitie onder de 5% gebruik zat. Dat sloot een vastzittende disk uit en bevestigde dat de bottleneck in de query planner zat, niet in de storage layer. Voordat we aannamen dat Magento het probleem was, controleerden we ook het aantal connecties en de InnoDB row-lock state:

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

Threads_running stond op 23 op een bak die normaal idle is op 4. InnoDB-status liet geen deadlock waiters zien en geen langlopende locks anders dan die van de eigen INSERT van de indexer. Dit was dus geen contention; het was één query die heet draaide. We openden een tweede shell en deden het voor de hand liggende:

SHOW FULL PROCESSLIST;

Eén query draaide al 41 minuten. Het was een INSERT ... SELECT in catalog_product_flat_1. Het Magento-proces had hem vanuit de indexer afgevuurd. De indexer-status bevestigen kostte één commando:

bin/magento indexer:status

Dat liet catalog_product_flat zien in de working state sinds 06:33. De reindex was prima gestart. Hij was alleen nooit klaar gekomen. De query killen en herstarten zou misschien 90 seconden opleveren voordat dezelfde INSERT dezelfde core opnieuw zou vastzetten, en we hadden dat patroon twee weken eerder geverifieerd bij een kleiner incident bij een andere klant. We moesten weten op welke rij de query struikelde, niet alleen dát hij struikelde.

De query traceren

De flat indexer van Magento herbouwt catalog_product_flat_<store_id> uit catalog_product_entity, gejoined tegen de EAV attribute tables. De query die de indexer uitzendt ziet er ongeveer zo uit:

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 trokken de volledige tekst uit de slow query log op /var/log/mysql/slow.log, die het bureau al had ingesteld op een drempel van één seconde. Daarna draaiden we EXPLAIN op de SELECT-helft. Het plan kwam terug met deze rij, ingekort voor breedte:

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

Achttienduizend SKU's in de catalogus zouden geen vier miljoen gescande rijen mogen opleveren. De optimizer had besloten dat een van de join-kanten niet-selectief was en had de index opgegeven, terugvallend op een sort-merge plan dat bij elke batch naar disk-gebackte temporary tables schreef. De referentiepagina's voor SHOW PROCESSLIST en EXPLAIN output zijn de twee MySQL-docs die bookmarken waard zijn voor dit soort triage; de combinatie "Using temporary; Using filesort" op een join is in beide de grootste rode vlag.

De INSERT-kant maakte het erger. Een INSERT ... SELECT houdt shared locks op elke rij die hij in de SELECT-helft leest, voor de duur van de statement. Dus hoe langer de SELECT draaide, hoe meer van catalog_product_entity gelocked stond tegen al het andere. De admin-login leest dezelfde tabel uit om de store-view-rechten van de gebruiker op te zoeken, en daarom zag de ops lead de admin uitvallen voordat de storefront dat deed. De storefront cachet listingpagina's aan de edge voor negentig seconden; de admin cachet niets.

De wees vinden

We checkten het tweede geval eerst, omdat de query goedkoop is om te draaien:

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;

Hij gaf 47 rijen terug. De oudste was van maart. De nieuwste was om 01:47 dezelfde ochtend toegevoegd, midden in de nacht-shift import. We grepten /var/log/syslog voor hetzelfde tijdvenster:

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

Eén hit om 01:48: de kernel OOM killer had een PHP-proces uitgeschakeld dat 4,2 GB vasthield. Dat was de nacht-shift import, gekilled midden in een CSV van 14.000 producten. De buitenste transactie die elke product-create wikkelde was teruggerold. Het binnenste werk dat de indexer-plugin al had gecommit op zijn eigen connectie was dat niet. Dus catalog_product_entity rolde terug. catalog_product_flat_1 niet. De ochtend-reindex probeerde toen flat te herbouwen over 47 entity_ids die niet meer bestonden, en de join planner gaf de index op.

We controleerden een van de orphan rows direct:

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

De flat-rij had een null SKU. De downstream filters van de indexer die op status snoeien, snoeiden hem niet, omdat NULL niet falsy is op de manier die de indexer aanneemt. De join breidde de scan vervolgens uit over de EAV attribute tables, en de temporary table zwol op. Zevenenveertig null-SKU-rijen hadden van een 18k-product reindex een tabelscan van vier miljoen rijen gemaakt.

De andere stores tegenchecken

Store 1 was schoon. Voordat we de admin ontgrendelden, controleerden we de andere twee store views, omdat de gekilde import op dezelfde manier naar alle drie de flat-tabellen zou hebben geschreven. Dezelfde LEFT JOIN tegen catalog_product_flat_2 gaf 47 rijen terug; tegen catalog_product_flat_3 31. De deltas kwamen overeen met wat we verwachtten: stores 2 en 3 delen een attribute set en een statusfilter dat een handvol producten uitsloot, dus de import had bij elk minder producten geraakt.

We breidden de check ook uit naar de search index, omdat de catalogsearch fulltext indexer bij herbouw uit de flat-tabellen leest, en een verouderde flat-rij zorgt er stilletjes voor dat search-resultaten mismatchen met de beschikbaarheid op de productdetailpagina, totdat de volgende nachtelijke reindex bijtrekt. Zelfde join, andere left side, twaalf mismatches terug. Beter om ze allemaal door te spoelen terwijl je de admin toch al offline hebt.

De opschoning

Voordat we iets verwijderden, dumpten we de orphan rows. Zelfs op een result set van 11 KB legt een flat-row delete die de verkeerde store view raakt productlijsten plat, en we wilden een papieren spoor. We gebruikten mysqldump in plaats van SELECT INTO OUTFILE zodat de dump terugkwam als INSERT statements die desgewenst op een staging instance te replayen zijn, en zodat het file ownership bij de normale backup-user van het bureau bleef in plaats van bij de 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

De dump ging naar de S3-bucket van het bureau en naar een lokale kopie op de bak, met dezelfde dump herhaald voor stores 2 en 3. Daarna de deletes, één 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 draaiden de orphan-row SELECT opnieuw tegen alle drie de stores om nul hits te bevestigen, killden de vastzittende INSERT en resetten de getroffen indexers:

mysql> KILL 4471823;

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

De flat reindex was in 94 seconden klaar, fulltext in nog eens 38. CPU zakte naar baseline. Admin kwam terug. Het magazijn begon labels te printen. Totale tijd van eerste ping naar groen: 38 minuten, inclusief een koffie.

Eén sanity check voor we wegliepen: we timeden een reindex van een subset van 1.000 SKU's tegen de volledige run van 18.000 SKU's. De subset duurde 5 seconden, de volle run 94. De ratio kwam vrijwel exact overeen met de SKU-ratio, en dat is het schoonste signaal dat je kunt krijgen dat er nergens nog drift verstopt zit. Een volledige reindex die meer dan 10 à 15% langzamer draait dan zijn SKU-proportionele baseline draagt ergens nog ballast mee.

Waarom flat-tabellen driften

Drie dingen maken catalog_product_flat_<n> een terugkerend pijnpunt op Magento 2 stores.

Het eerste is denormalisatie. De flat-tabellen dupliceren data uit catalog_product_entity en de EAV attribute tables, by design, zodat storefront-productlijsten uit één tabel kunnen lezen in plaats van negen. Elke gedeeltelijke write die één brontabel raakt maar niet de andere, laat de flat-tabel met verouderde referenties zitten. Adobe's eigen indexing-documentatie noemt de catalog flat indexers tot de duurste van de standaardset, en de reden is dat ze uitwaaieren over elke store view. Een winkel met drie views en 18.000 producten herbouwt 54.000 flat-rijen op elke volledige reindex.

Het tweede is een mismatch in transactiegrenzen. De indexer-plugin opent zijn eigen databaseconnectie. Een bulk import die door de OOM killer of door een deploy wordt gekilled, rolt catalog_product_entity terug op zijn eigen connectie, maar de flat-tabel inserts op de indexer-connectie kunnen al gecommit zijn. We hebben dit patroon inmiddels gezien op drie verschillende Magento 2 stores in de afgelopen 18 maanden, telkens na een OOM kill tijdens een bulk import. Magento Commerce-installaties gedragen zich op dezelfde manier; de asynchrone indexer queue verandert het connectiemodel niet, hij stelt alleen uit wanneer de writes gebeuren.

Het derde is de silent failure mode. De drift produceert geen error. De site blijft serven. De indexer blijft valid rapporteren op zijn status-output, want wat hem betreft is de laatste herbouw geslaagd. Het eerste symptoom is een CPU-spike tijdens de volgende reindex, tegen die tijd ligt de import die hem heeft veroorzaakt uren of dagen terug en niemand legt het verband. De standaard monitoring-stack van Magento heeft geen probe voor orphan rows. New Relic vertelt je dat de INSERT traag is; hij vertelt je niet waarom. We hebben drift acht weken zien overleven op een winkel waarvan de catalog-herbouw elke nacht binnen budget draaide, om vervolgens in te storten op de ochtend dat een andere import het aantal rijen voorbij de drempel duwde waar het plan van de optimizer kantelde.

De check die de volgende storing voorkomt

Het enige met het hoogste rendement dat je kunt toevoegen aan een Magento 2 legacy site die je overneemt, is een orphan-row check die vóór de indexer-cron draait, niet ná de storing. Vijf regels bash en een cron-entry om 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

Pas de store-IDs aan op de store views die de merchant heeft geconfigureerd. Draai hem voor de indexer-cron, zodat je tijd hebt om de rijen te verwijderen voordat het verkeer aankomt. De check heeft op de stores van ditzelfde bureau twee wachtende storingen onderschept in de acht weken sinds we hem hebben ingezet.

Je kunt de flat catalog ook in zijn geheel uitschakelen (Stores, Configuration, Catalog, Storefront, zet Use Flat Catalog Product op No) en de indexer verdwijnt mee. Op een winkel met zoveel filterable attributes is dat een meetbare vertraging op categoriepagina's, in de orde van 200 tot 400 ms op listing-renders, oftewel de kostprijs van reguliere customer-service-telefoontjes over paginalatentie. De flat-tabellen bestaan met een reden. Ze houden en de orphan-row check draaien kost minder.

Het bureau heeft ook twee langetermijn-stappen genomen. Ze hebben de geheugenlimiet van het import-proces opgetrokken naar 6 GB en een swap-bestand toegevoegd waar de OOM killer eerst naar kan reiken voor hij PHP aanvalt, wat headroom oplevert op een single-box stack zonder dat je voor een aparte DB-host hoeft te betalen. En ze hebben de catalog indexers omgezet van realtime naar schedule mode, zodat flat-tabel writes door de mview changelog queue lopen in plaats van synchroon af te vuren op elke catalog-write. Schedule mode elimineert de transactiegrens-drift niet, maar het batcht veranderingen en brengt failures aan de oppervlakte als cron-job errors in plaats van stille gedeeltelijke commits. Geen van beide vervangt de orphan-row check. Ze beperken hoe vaak hij iets te rapporteren heeft.

Toen we Pier bouwden, kwamen we dit exacte patroon vaak genoeg tegen dat de MySQL editor wordt geleverd met een opgeslagen query-template voor het vinden van orphan rows in catalog_product_flat_<n>, en elke destructieve query loopt door versiegeschiedenis zodat een opschoonactie van 03:00 die de verkeerde rij raakt met één klik terug te draaien is. Het punt is niet de tooling. Het punt is dat de orphan-row check op elke Magento 2 winkel hoort die je onderhoudt, wat je ook gebruikt om hem te draaien.

Als je vandaag één ding doet, open dan de slow query log op je drukste Magento 2 winkel en grep voor catalog_product_flat. Alles ouder dan je reindex-window is een blik waard.