Post-Mortem: WooCommerce Checkout 504s From a Locked wp_options Table

Incident Overview

An established WooCommerce store began returning HTTP 504 Gateway Timeout on the checkout endpoint during evening traffic peaks. Product pages stayed up (served from full-page cache), which masked the severity: checkout is uncacheable, so paying customers were the only users hitting the failing path. The store was losing its highest-intent traffic while monitoring dashboards showed a “mostly up” site.

Blast radius: checkout conversion dropped to near zero for roughly 90 minutes per evening across four days before we were engaged.

Raw Diagnostic Logs

NGINX error log, repeating in bursts during the outage windows:

2026/07/06 19:41:22 [error] 1187#1187: *48211 upstream timed out
  (110: Connection timed out) while reading response header from upstream,
  request: "POST /?wc-ajax=checkout HTTP/2.0",
  upstream: "fastcgi://unix:/run/php/php8.2-fpm.sock"

PHP-FPM was saturated — every worker occupied and a growing listen queue:

$ curl -s localhost/fpm-status | grep -E 'active|listen'
active processes:      24
max active processes:  24
listen queue:          61
max listen queue:      128

The workers were not busy computing — they were waiting on the database. The processlist showed the real problem:

MariaDB> SELECT id, time, state, LEFT(info, 60) FROM information_schema.processlist
      -> WHERE command != 'Sleep' ORDER BY time DESC LIMIT 5;
+------+------+---------------+--------------------------------------------------------------+
| id   | time | state         | LEFT(info, 60)                                               |
+------+------+---------------+--------------------------------------------------------------+
| 8811 |  47  | Updating      | UPDATE `wp_options` SET `option_value` = '...' WHERE `option |
| 8907 |  46  | Waiting for   | SELECT option_name, option_value FROM wp_options WHERE autol |
| 8909 |  46  | Waiting for   | SELECT option_name, option_value FROM wp_options WHERE autol |
+------+------+---------------+--------------------------------------------------------------+

Forensic Root Cause Analysis

Every WordPress page load runs SELECT option_name, option_value FROM wp_options WHERE autoload = 'on'. That query is only cheap when the autoloaded set is small. Here it was not:

MariaDB> SELECT ROUND(SUM(LENGTH(option_value))/1024/1024, 1) AS autoload_mb,
      -> COUNT(*) AS rows_autoloaded
      -> FROM wp_options WHERE autoload = 'on';
+-------------+-----------------+
| autoload_mb | rows_autoloaded |
+-------------+-----------------+
|       612.4 |          188237 |
+-------------+-----------------+

612 MB of autoload data, dominated by two sources: an abandoned marketing plugin that wrote one autoloaded row per visitor session and never cleaned up, and tens of thousands of expired _transient_ rows that a broken WP-Cron had stopped garbage-collecting.

The failure mechanics: a plugin’s scheduled task issued a long-running UPDATE against wp_options. Under MariaDB’s row-level locking this would normally be survivable, but the table had grown to the point where the autoload query did a large scan, the UPDATE held locks the scans queued behind, and every PHP-FPM worker piled up waiting on the same table. With all 24 workers blocked, NGINX’s 60-second fastcgi_read_timeout expired and customers got 504s.

Remediation & Command Log

Full database image before any repair:

mariadb-dump --single-transaction --routines --triggers shop_db \
  | gzip > /root/pre-repair-shop_db-$(date +%F).sql.gz

Purge expired transients and verify the orphaned session rows against the plugin’s own prefix before deleting:

wp transient delete --expired --network
wp db query "SELECT COUNT(*) FROM wp_options WHERE option_name LIKE 'mkt_sess_%';"
wp db query "DELETE FROM wp_options WHERE option_name LIKE 'mkt_sess_%';"

Flip the remaining oversized rows out of the autoload set instead of deleting them (reversible, zero data loss):

wp db query "UPDATE wp_options SET autoload='off'
  WHERE autoload='on' AND LENGTH(option_value) > 65536;"

Rebuild the table to reclaim 14 GB of file space and refresh index statistics, then restart the stack cleanly:

wp db query "OPTIMIZE TABLE wp_options;"
systemctl restart php8.2-fpm nginx

Result: autoload set down from 612 MB to 3.1 MB; checkout p95 response time from timeout to 480 ms under the same evening load.

Preventative Hardening

• Replaced WP-Cron’s traffic-dependent trigger with a real system cron so transient garbage collection can never silently stop again:

# /etc/cron.d/wp-cron — run WordPress cron every 5 minutes as the site user
*/5 * * * * www-data /usr/local/bin/wp --path=/var/www/shop cron event run --due-now >/dev/null 2>&1

• Added define('DISABLE_WP_CRON', true); to wp-config.php to match.
• Removed the abandoned marketing plugin and its residual rows entirely.
• Set a PHP-FPM slow log (request_slowlog_timeout = 10s) so the next database stall produces a stack trace in minutes instead of days of guesswork.
• Left the client a monthly wp_options size check in the post-repair report, with the exact query and the healthy baseline number to compare against.

---

Details in this post-mortem are anonymized and composited from recurring incidents of the same failure class. The logs, queries, and commands are representative of the actual diagnostic path. If your checkout is timing out right now, our database repair service follows exactly this process.