Magento sales_order_grid: zo krimpten we 'm met 4GB

De Loom van 23:41, vlak na Black Friday

Een Nederlands bureau waarmee we werken stuurde op een dinsdag om 23:41 een Loom. Hun Magento 2.4.6-shop was in de admin tot stilstand gekomen. De orderlijst deed er 14 seconden over om te renderen. Hun hosting-dashboard meldde de database op 11GB totaal, en één tabel vrat in z'n eentje 4GB: sales_order_grid.

De shop draaide sinds 2020. Zo'n 380.000 orders over zes jaar handel. Black Friday voegde er in 72 uur 9.200 aan toe. Er was nooit iets opgeschoond, gearchiveerd of geroteerd. De sales_order_grid-index was simpelweg meegegroeid, en het mview-gestuurde update-patroon had genoeg fragmentatie opgestapeld dat de tabel op disk bijna twee keer zo groot was als de logische rijgrootte.

De opdracht was helder. Krimp de tabel. Houd elke gearchiveerde order zichtbaar in de admin. Verlies geen enkele rij. Doe het binnen een onderhoudsvenster van 02:00 tot 04:00 Amsterdamse tijd.

Wat er in sales_order_grid zit

De tabel sales_order_grid is niet de bron van waarheid voor orders. De canonieke data staat in sales_order, sales_order_item en de rest van de sales_*-familie. sales_order_grid is een gedenormaliseerde projectie, gebouwd zodat de admin-orderlijst snel kan renderen zonder tien tabellen tegelijk te joinen. Hij draagt een platgeslagen kopie van de orderheader (nummer, status, klantnaam, factuur- en verzendnaam, totalen) en wordt opnieuw gevuld door Magento's materialised view indexer-laag.

Dat laatste detail telt. Je kunt sales_order_grid truncaten en Magento bouwt 'm opnieuw op vanuit sales_order. Je verliest geen enkele gearchiveerde order, want de orders staan daar niet. De grid is een cache. Een cache van 4GB, maar een cache.

De voetangel is de mview-staat. Truncate je zonder mview in te lichten, dan probeert het volgende changelog-event een tabel incrementeel bij te werken die niet meer matcht met de verwachte staat, en eindig je met een halve rebuild die meer tijd kost om te diagnosticeren dan de truncate zelf duurde.

De audit, vóór elke DDL

Het eerste wat we draaien op elke overmaatse Magento-database is een sizing-pass tegen information_schema. Die vertelt je of het probleem in data, indexes of fragmentatie zit, en dat bepaalt de procedure.

SELECT
  table_name,
  ROUND(data_length / 1024 / 1024)  AS data_mb,
  ROUND(index_length / 1024 / 1024) AS index_mb,
  ROUND(data_free   / 1024 / 1024)  AS free_mb,
  table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name LIKE 'sales_order_grid%'
ORDER BY data_length DESC;

De output op de database van deze klant las ongeveer zo:

table_name              data_mb  index_mb  free_mb  table_rows
sales_order_grid        2840     980       310      389214
sales_order_grid_arch   0        0         0        0

Twee conclusies. Eén: het aantal rijen (389k) liep keurig in de pas met het aantal orders, dus geen verweesde rijen die de tabel opbliezen. Twee: data_free op 310MB betekende dat InnoDB ongebruikte pages vasthield, en fragmentatie stapelt zich daar over jaren UPDATE-verkeer bovenop. De bulk van de overmaat (2,8GB aan data, bijna 1GB aan indexes) kwam uit jaren opgehoopte rijen op een gedenormaliseerde tabel die nooit was herbouwd.

Daarna de mview-staat. Magento stelt die bloot via één tabel:

SELECT view_id, status, updated, version_id
FROM mview_state
WHERE view_id LIKE 'sales_order%';

We wilden elke sales_order_*-view op idle voordat we ook maar iets aanraakten. Staat een view op working, dan zit een indexer-cron midden in een run, en elke DDL op zijn doeltabel zal óf deadlocken óf de changelog-pointer corrumperen. De MySQL-docs over online DDL-operations zijn er expliciet over: TRUNCATE op een InnoDB-tabel vereist een exclusieve metadata-lock, en die wil je niet uitvechten met een draaiende indexer.

De gefaseerde krimp

Met een tabel van 4GB en een venster van twee uur wilden we niet OPTIMIZE TABLE in place draaien. Op InnoDB herschrijft die de hele tabel terwijl 'ie metadata-locks vasthoudt, en op deze workload werd de admin-grid elke 90 seconden geraakt door een Klaviyo-sync en elke minuut door een Mirakl-marketplace-poller. We hadden iets nodig dat afbreekbaar, auditbaar en hervatbaar was.

Het plan, op volgorde:

1. Zet Magento alleen voor de order-admin in onderhoudsmodus.
2. Stop de indexer cron-groep netjes.
3. Pauzeer de Klaviyo- en Mirakl-integraties vanuit hun eigen dashboards.
4. Snapshot sales_order_grid naar een backuptabel op dezelfde server.
5. Truncate de live tabel.
6. Reset de mview-changelog zodat de volgende reindex een volledige rebuild is, geen incrementele.
7. Draai bin/magento indexer:reindex sales_order_grid.
8. Verifieer rij-aantallen en inhoud tegen sales_order.

De maintenance-vlag is per area, niet de globale bin/magento maintenance:enable. We hebben een eenregelige guard in app/etc/config.php gezet die alleen op /admin/sales_order-routes een 503 teruggaf. De storefront bleef gewoon orders in sales_order schrijven. Nieuwe orders verschenen simpelweg niet in de grid totdat we klaar waren. Dat was acceptabel omdat de admin van de shop om 02:00 leeg was.

De indexer cron-groep stoppen is rechttoe rechtaan:

php bin/magento cron:remove --group=index
ps -ef | grep "cron:run" | grep -v grep

Leeft er na de remove nog een cron:run --group=index-process, kill 'm op PID. Sla deze stap niet over. Een halverwege draaiende indexer die rij-locks vasthoudt op sales_order_grid terwijl jij 'm probeert te truncaten is de slechtste afloop die er is.

De snapshot is één statement:

CREATE TABLE sales_order_grid_bk_20260603 LIKE sales_order_grid;
INSERT INTO sales_order_grid_bk_20260603 SELECT * FROM sales_order_grid;

Dat verdubbelde de disk kort naar ~5GB, wat we hadden ingecalculeerd. Het punt van de snapshot is niet de data zelf (we kunnen elk moment vanuit sales_order rebuilden) maar het audit-spoor. Als een klantenservice-medewerker de ochtend erna een ontbrekende rij meldt, wilden we kunnen vergelijken met een bevroren kopie, niet met een bewegend doel.

Dan de truncate:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE sales_order_grid;
SET FOREIGN_KEY_CHECKS = 1;

En de mview-reset, de stap die de meeste teardown-guides overslaan:

UPDATE mview_state
SET status = 'idle', version_id = NULL
WHERE view_id IN (
  'sales_order_grid',
  'sales_order_invoice_grid',
  'sales_order_shipment_grid',
  'sales_order_creditmemo_grid'
);
TRUNCATE TABLE sales_order_grid_cl;

version_id = NULL zetten vertelt Magento de volgende indexer-run te behandelen als een volledige rebuild in plaats van een incrementele pickup. De changelog-tabel (sales_order_grid_cl) truncaten verwijdert verouderde wijzigingsrecords die anders tegen een nu lege doeltabel zouden ophopen.

Reindex en verificatie

Met de tabel leeg en mview gereset is de reindex één commando:

php bin/magento indexer:reindex sales_order_grid

Op deze database deed 'ie er 11 minuten en 40 seconden over om 389.214 rijen te herbouwen. We keken mee vanuit een tweede SSH-sessie met:

SELECT COUNT(*) FROM sales_order_grid;

elke 30 seconden, gewoon om de teller te zien klimmen. Toen 'ie stopte op exact het rij-aantal van sales_order, draaiden we de audit-query opnieuw.

table_name              data_mb  index_mb  free_mb  table_rows
sales_order_grid        612      188       0        389214

Van 3,8GB samen naar 800MB. De rest van de winst kwam uit een vers gebouwde clustered index zonder opgehoopte fragmentatie, plus de JSON-kolom items die compact opnieuw was geëncodeerd in plaats van jaren UPDATE-delta's mee te slepen.

De verificatiestap is niet onderhandelbaar. Alleen een matchend rij-aantal is niet genoeg, want een halve rebuild kan ook precies op het juiste totaal uitkomen als de indexer in de verkeerde volgorde liep. We draaiden ook een expliciete anti-join:

SELECT
  (SELECT COUNT(*) FROM sales_order)      AS orders,
  (SELECT COUNT(*) FROM sales_order_grid) AS grid,
  (SELECT COUNT(*) FROM sales_order o
   WHERE NOT EXISTS (
     SELECT 1 FROM sales_order_grid g
     WHERE g.entity_id = o.entity_id
   ))                                     AS missing_from_grid;

De waarde missing_from_grid kwam terug op nul. Elke gearchiveerde order vanaf 2020 verscheen nog steeds in de admin-grid. De lijstweergave van 14 seconden zakte naar onder de 800ms doordat de InnoDB buffer pool niet meer hoefde te thrashen op een gefragmenteerde index. We lieten de snapshot-tabel twee weken staan en dropten 'm toen het CS-team een rustige maandag had.

Laatste stap: de cron-groep weer aanzetten, caches flushen en de integraties unpauzen.

php bin/magento cron:install
php bin/magento cache:flush

Wat we bewust hebben laten staan

Drie dingen die we bewust niet hebben aangepast.

We hebben geen extra InnoDB-compressie aangezet. Op deze database stond die al aan. Meer compressie had nog wat afgeschaafd, ten koste van CPU bij elke order-write, en de hoofdklacht van het bureau was admin-latency, niet opslagkosten.

We hebben orders niet naar de archive-module gemigreerd. Magento heeft een ingebouwde order archive-feature die afgesloten orders uit de hoofdtabellen verplaatst. Een prima beleid voor shops met miljoenen orders, maar het verandert de admin-UX en de klant wilde pariteit met wat hun CS-team al kende.

We hebben de storefront niet aangeraakt. De krimp speelde zich volledig af op de grid-laag. Storefront-performance was een aparte opdracht.

Wat te volgen in de week erna

Zeven dagen na de krimp hielden we drie signalen laagfrequent in de gaten. Tabelgrootte, mview-lag en admin-latency. De delta van de tabelgrootte is dezelfde information_schema-query op een cron, weggeschreven naar een CSV in /var/log/. Hem een week terug zien klimmen geeft je een echte groei per order, en dat is het getal dat je nodig hebt als je het volgende onderhoudsvenster plant.

Mview-lag is het rij-aantal van sales_order_grid_cl op enig moment. Op een gezonde shop zit dat in de lage honderden, elke minuut leeggetrokken door de indexer-cron. Klimt 'ie de tienduizenden in, dan draait je indexer niet, of draait wel maar maakt niets af, en wordt de volgende krimp lelijker dan deze.

Admin-latency hebben we gemeten met dezelfde Lighthouse-run die het bureau al op de storefront gebruikte, nu gericht op /admin/sales_order. Ze hebben 'm aan hun interne dashboard toegevoegd. De grens ligt onder 1,5 seconde voor de eerste paint van de orderlijst. De dag dat 'ie weer boven de drie seconden klimt, grijpen ze naar de audit-query, niet naar het onderhoudsvenster.

Het kleinste wat je vandaag kunt doen

Draai de audit-query bovenaan deze post op je eigen database. Vijf seconden SQL vertelt je of sales_order_grid op jouw shop een probleem is of een non-issue. De meeste Magento-shops die we auditen zitten op 500MB tot 2GB op deze tabel. Een grid van 4GB is het soort ding dat zich niet aankondigt totdat de admin vastloopt, en dan is je onderhoudsvenster korter dan je het wilt hebben.

Toen we Pier bouwden liepen we op genoeg verouderde sites tegen exact dit patroon aan dat de MySQL editor standaard een table-sizes-weergave met één klik bevat die dezelfde query draait tegen elke aangesloten database. Elke destructieve stap zit verpakt in version history, dus de snapshot-tabel en de truncate staan in hetzelfde audit-spoor als de rest van het werk.