40GB wp_options autoload: één nacht terug naar 800ms TTFB

Dinsdag, 23:41

De Loom-link kwam binnen om 23:41. Een lead van een bureau in Utrecht waar we mee werken zat sinds net na het eten in een screenshare met de IT-manager van hun B2B-klant. De WooCommerce-shop van die klant, PHP 8.1, MariaDB 10.6, zo'n 14.000 SKU's, was de afgelopen twee weken steeds trager geworden. Die avond brak de boel. De TTFB van de homepage ging over de acht seconden. De salesafdeling stuurde screenshots door van de tollende favicon en vroeg wanneer dit ophield. Tegen middernacht wees de slow query log steeds naar één tabel: wp_options.

De site zelf was bekend werk. Zes jaar oud, in die tijd door twee bureaus heen gegaan, het soort plugin-lijst dat in archeologische lagen groeit. New Relic wees naar MySQL. Het bureau had al een CDN geprobeerd, een nieuwe object cache die niet wilde warmlopen, en een spoed-tuningronde op PHP-FPM. Geen van die dingen had iets uitgemaakt.

Het eerste dat we ze lieten draaien was de query op tabelgrootte.

SELECT
  table_name,
  ROUND(((data_length + index_length) / 1024 / 1024 / 1024), 2) AS size_gb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 10;

wp_options kwam terug op 40,2 GB. De volgende grootste tabel, wp_postmeta, was 1,1 GB.

Wat er werkelijk in zat

Een tabel van die omvang ontstaat niet langzaam. Het is het gevolg van één specifieke beslissing ergens in de codebase die al heel lang door niemand in twijfel is getrokken. De manier om die te vinden is sorteren op rijgrootte.

SELECT
  option_name,
  LENGTH(option_value) AS bytes,
  autoload
FROM wp_options
ORDER BY LENGTH(option_value) DESC
LIMIT 30;

Bovenaan stond één enkele rij, autoload = yes, 4,1 GB groot. De naam droeg een custom integratie-prefix die de vorige inhouse-developer drie jaar eerder had gebruikt. Het bleek een queue-tabel te zijn die binnen wp_options was gebouwd in plaats van als eigen tabel. Elke mislukte call naar het ERP van de klant, elke payload, elke retry, was toegevoegd aan een serialized PHP-array opgeslagen onder één option-naam. De cron job die de queue moest leeghalen was sinds een PHP 8-upgrade stilletjes aan het falen.

De overige 36 GB was makkelijker te verklaren. WordPress schrijft veel kleine dingen naar wp_options zonder ze ooit op te ruimen. De default transient cache leeft daar. Verlopen transients worden niet automatisch verwijderd. Een handvol bekende plugins schrijft hun session-, log- en rate-limit-data er ook in. We zijn dit tegengekomen bij Yoast indexable-rebuilds, bij Wordfence attack logs, bij WP Rocket cache-headers, en bij minstens twee membership-plugins die elke login-poging als eigen option-rij opslaan.

De schade stapelt zich op door autoload. Bij elke WordPress-request, voordat er iets anders gebeurt, draait de functie wp_load_alloptions() één enkele query die alle rijen waar autoload = 'yes' staat in het PHP-geheugen trekt. Op een gezonde site is die result set een of twee megabyte. Op deze site was hij 6,4 GB. De query liep op elke pagina-load, elke admin-ajax-call, elk WP-CLI-commando. MySQL serveerde hem vanaf disk, decodeerde hem, en PHP unserialiseerde hem in process-geheugen voordat er ook maar besloten kon worden wat te renderen.

Triage om één uur 's nachts

Het plan dat we in het gedeelde document schreven had drie stappen en voor elke stap een rollback. Er ging niets gebeuren zonder een volledige mysqldump en een vastgelegde binlog-positie.

Stap één: snapshot

We maakten een logische back-up van alleen wp_options, niet de hele database. Een volledige dump van een tabel van 40 GB over het publieke netwerk zou langer duren dan we geduld voor hadden, dus we gebruikten eerst de snapshot-tooling van de host op volume-niveau, en daarna draaiden we een gerichte mysqldump van alleen de tabel naar een tweede schijf.

mysqldump \
  --single-transaction \
  --quick \
  --skip-lock-tables \
  --default-character-set=utf8mb4 \
  --databases shop_prod --tables wp_options \
  | gzip > /backups/wp_options-pre-cleanup.sql.gz

De dump kwam uit op 6,3 GB gecomprimeerd. Dat gaf ons een vertrouwd restore-punt dat niet afhing van het snapshot-beleid van de hostingprovider.

Stap twee: het bloeden stelpen

We verwijderden de queue-rij van 4,1 GB niet meteen. Verleidelijk, want dat zou de grootste zichtbare winst hebben opgeleverd, maar de integratie-code zou de rij bij de volgende cron-tick weer opbouwen. De eerste zet was autoload uitzetten, waarmee de rij uit het hot path van elke request verdwijnt zonder de data aan te raken.

UPDATE wp_options
SET autoload = 'no'
WHERE option_name = 'acme_erp_queue';

Dat ene statement, om 01:14 uitgevoerd, bracht de TTFB van de homepage van 8,1 seconden terug naar 1,9 seconden. We keken tien minuten naar het monitoring-dashboard van het bureau voordat we iets anders deden.

Hetzelfde principe geldt voor de long tail. Een rij in deze tabel is alleen duur als hij autoloadt. Plugins die hun options per ongeluk op autoload hebben gezet kun je in bulk corrigeren, met de nodige zorg.

SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload = 'yes'
  AND LENGTH(option_value) > 1048576
ORDER BY bytes DESC;

Alles boven een megabyte dat autoloadt is vrijwel zeker een fout. We liepen elke naam langs met de lead van het bureau. Die nacht gingen er nog drie rijen van autoload af: een Yoast indexable-cache, een Wordfence scan-log, en een interne "settings backup"-array die een vorige developer had gebouwd en daarna vergeten.

Stap drie: de transient-sweep

De transient-API van WordPress gebruikt wp_options als default opslag zolang er geen persistent object cache is geconfigureerd. Elke transient krijgt twee rijen, één voor de waarde en één voor de timeout. Wanneer transients verlopen, wordt geen van beide rijen verwijderd; de waarde wordt simpelweg genegeerd. Op een site die veel transients gebruikt en geen object cache heeft, hoopt dit zich lineair op tot in het oneindige.

De veilige opruimactie is om alleen verlopen transients te verwijderen, en het in batches te doen zodat de replicatie kan bijblijven.

DELETE a, b
FROM wp_options a
INNER JOIN wp_options b
  ON b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12))
WHERE a.option_name LIKE '\_transient\_%'
  AND a.option_name NOT LIKE '\_transient\_timeout\_%'
  AND b.option_value < UNIX_TIMESTAMP()
LIMIT 50000;

We draaiden hem in een loop met een korte sleep tussen de batches. De transient-sweep alleen al verwijderde 18,7 miljoen rijen.

De schijfruimte terugwinnen

Tegen 03:00 was de autoload-set teruggebracht naar 11 MB. De TTFB lag stabiel op 740 ms over tien koude homepage-requests. De site was, naar elke redelijke maatstaf, weer in orde. Maar het bestand wp_options.ibd was op disk nog steeds 40 GB, vol gaten van alle verwijderde rijen. MySQL zou die ruimte met plezier hergebruiken voor nieuwe inserts, maar uit zichzelf gaf het niets terug aan het filesystem.

We draaiden OPTIMIZE TABLE in het eerstvolgende onderhoudsvenster drie dagen later, met de site twaalf minuten in read-only stand.

OPTIMIZE TABLE wp_options;

Het uiteindelijke bestand kwam uit op 92 MB. De MariaDB-documentatie over OPTIMIZE TABLE is het waard om volledig door te lezen voor je hem op iets groots in productie draait; ze legt precies uit welke locks hij neemt en welke storage engines online operation ondersteunen.

Wat de codebase verkeerd deed

De fix hierboven is het soort werk dat een avond winst oplevert. De moeilijkere vraag is waarom een database überhaupt in deze staat terechtkwam, en wat er moet veranderen zodat het niet opnieuw gebeurt.

Het patroon van queue-in-options is de grootste boosdoener. WordPress maakt het al te makkelijk om update_option() aan te roepen met van alles, inclusief een megabyte JSON, en standaard staat die option op autoload. Een junior onder deadlinedruk grijpt liever naar update_option() dan dat hij een migratie schrijft voor een nieuwe tabel, elke keer weer. De fix is institutioneel: code review op elke nieuwe option-naam, en een harde regel dat data die met de tijd groeit een eigen tabel krijgt.

Het transient-probleem is structureel. Tot je een persistent object cache voor WordPress zet (Redis is het gebruikelijke antwoord; de Redis Object Cache-plugin doet de bedrading), zal elke transient die je themes en plugins zetten voorgoed in de options-tabel blijven staan. Zelfs mét cache is een wekelijkse scheduled job die verlopen transients verwijdert goedkope verzekering.

De plugin-zondaars zijn lastiger in te dammen omdat ze standaard met autoload aan worden geleverd en de meeste site-eigenaren er nooit naar kijken. De audit is dezelfde query waarmee we begonnen, gesorteerd op autoloaded bytes, eens per kwartaal gedraaid en geplakt in het maandelijkse health log van het project.

De aantekening die we steeds opnieuw schrijven

Dit is de derde options-tabel van 30 GB-plus die we sinds januari zien. Het patroon is consistent genoeg dat we, toen we Pier bouwden voor het bewerken van dit soort verouderde sites, de MySQL editor hebben uitgerust met een opgeslagen query voor "autoloaded options boven 1 MB" en een één-klik-toggle op de autoload-kolom. Elke option-rij die je wijzigt loopt door dezelfde version history als een bestandswijziging, dus als een plugin-update een gigantische array terugschrijft in een rij die jij had omgezet, zie je het gebeuren en draai je het terug zonder de hele tabel te hoeven restoren.

Het kleinste dat je vandaag kunt doen

Open de database van de drukste WordPress-site die je beheert en draai de query voor autoloaded rows boven 1 MB uit deze post. Komt er iets terug, dan heb je al een avond werk klaarliggen; komt er niets, voeg de query dan toe aan je maandelijkse checklist en ga verder.