---
name: mariadb_oom_investigation
description: Multi-week project tracking the 2026-07-10 MariaDB OOM outage root cause + remediation; living doc in repo
metadata: 
  node_type: memory
  type: project
  originSessionId: 1726bcab-e571-495d-8a23-5ab31149d293
  modified: 2026-07-30T02:32:03.932Z
---

Production MariaDB was OOM-killed 2026-07-10 ~11:15–11:17 AEST. Living audit + progress tracker at
`evolution/audits/MARIADB_OOM_INVESTIGATION.md` — read it first each session; update its Progress log
and Remediation checklist as work proceeds.

**Verdict (investigation phase done 2026-07-10, no fixes applied yet):** structural time-bomb × concurrency
spike, not one query. Non-obvious durable facts (verified via EXPLAIN on dev tenant, see [[dev_mysql_access]]):
- **All heavy ERP tables are MyISAM** (quotes, quoteitems, dispatch, invoiceitems, jobs, gledger, uploads,
  inventory, contacts) → table-level locking + freely-spilling temp tables.
- **`gledger` has ZERO secondary indexes** (PRIMARY only) → every GL/P&L/sales report is `type: ALL` +
  temp table + filesort. Relates to [[evolution_gl_pl_data_model]].
- **`quoteitems` (largest ERP table, ~119MB prod) has no `itemid` index and no free-text index** →
  item lookups / `LIKE '%..%'` are full 367K-row scans.
- **Config amplifier:** prod `tmp_table_size`=`max_heap_table_size`=97MB × `max_connections`=151 over a
  1GB buffer pool → ~20-30 concurrent temp-table queries can demand 2-3GB+ transient RAM.
- The 3 timed-out screens (inventory_picking, quotedit, jobeditdispatch) are **victims not cause** —
  each is scoped to one quote/job.
- Correction: `jobInventory.jobid`/`dispatch.jobid` ARE indexed, so jobeditsave correlated subqueries are
  `ref` not scans — that screen is moderate.

**⚠️ Round 2 RETRACTED 2026-07-27.** It claimed a "jobedit-led edit-screen storm, **128 hits**". The
extraction script's greedy `page=` regex read the Apache **Referer** field, not the request — every AJAX
call fired *from* a jobedit page counted as a page load. Correct parse: jobedit **4**, quotedit 2,
invedit 1. There was no storm. The per-load cost analysis it drove (uncached `getSetting`, write-on-read
`UPDATE jobs`, jobeditinv N+1) was EXPLAIN-verified independently and stays valid — only the *ranking*
was wrong. **Never re-prioritise off "jobedit is the #1 offender by hits".**

**⚠️ Rounds 1–2 below are superseded by ROUND 3 (see bottom) on *cause*.** The ERP query/index findings
stay valid as performance work; they are not what fires the OOM.

**Revised verdict (2026-07-27, 3 more outages: 07-23 07:08, 07-27 10:05, 07-27 17:52):** all are global
Linux OOM kills of `mariadbd` (`global_oom`), invoked by netdata and exim — nothing ERP. **MariaDB is the
victim, not the allocator**: its RSS at kill ≈ its configured global footprint, i.e. just the biggest RSS
on the box. The real failure is a **swap death spiral** — page cache fully evicted (4.6 GB → 404 MB),
`%commit` 117, load1 284, D-state 182, `pswpout` 1066/s — which is why SSH and every service died too.
**The OOM kill is the recovery, not the failure.** Prod is overcommitted **1.76×** by config (peak
17,037 MB on 9,681 MB RAM); `slow_query_log` is OFF (keeps P0 blocked). **#1 open question: commit rose
6.9→16.5 GB in <13 min and ~9.6 GB is unattributed** — `sar` is process-blind, so `forensic-watch.sh`
(always-on `/proc` recorder + incident dumps) was built to name it.

**Forensic tooling** lives in `projects/OOM and Server Load Forensic/` (not a git repo — copy to prod
manually): `forensic-host.sh`, `forensic-web.sh`, `forensic-watch.sh`, `start-watcher.sh` (launcher,
run as root, `@reboot` persistence). The two original `erp_forensic*.sh` are superseded — three fatal
bugs incl. the Referer regex above.

**Scope boundary vs [[evolution_evomodal_declarative]] branch `quoteOptimizeV3Htmx`:** that branch owns
jobedit/quotedit **modal** deferral (`$moveModalsToAjax`, branch-only — absent from `staging`), built but
unmerged. Don't re-scope it here and don't edit modal `.inc` bodies while it's open. **Tab lazy-loading
(~15 eager jobedit tabs) is still THIS doc's** — verified tab includes are byte-identical on both sides.

**P1 APPLIED 2026-07-27 — overcommit eliminated.** Prod RAM **9,681 → 14,336 MB** (needed a reboot);
`max_connections` 151→**90**, `tmp_table_size`/`max_heap_table_size` 97→**32 MB**, `key_buffer_size`
954→**512 MB**. Theoretical peak **17,037 → 4,668 MB = 0.33× RAM** (was 1.76×). Verified live from the
repo with `mysql -h my.evolutionerp.com.au -u claude` (read-only `sqlClaude*` acct — this works and is
the fastest way to check prod config/status). **Watcher is live on prod** (root, 10 s samples →
`/var/log/erp-forensics`, `@reboot` entry). MariaDB can no longer exhaust the box, so a recurrence now
*proves* the allocator is something else.

**P0 UNBLOCKED 2026-07-27 19:38 — slow log now capturing:** `slow_query_log=ON`, `long_query_time=3`,
`log_slow_verbosity=query_plan,explain`, `log_queries_not_using_indexes=**OFF**` (deliberate —
`log_slow_filter` already has `full_scan`, and at ~7,460 selects/min the flag would bury the signal),
`log_slow_rate_limit=1`, file `/var/lib/mysql/server2-slow.log`. The 2026-07-10 window is long gone, so
this is **forward** capture. Disk is fine (130 GB free of 641 GB).

**Post-crash integrity sweep 2026-07-27: CLEAN** — 54/54 tenant DBs + 26 central `evolution` tables,
read-only `CHECK TABLE … FAST`, zero corruption, ~3.5 min. Note MyISAM is non-transactional: clean ≠
"no data lost", so never upgrade the claim beyond "no database corruption".

**PDF pipeline throttled + committed 2026-07-27** — see [[evolution_pdf_render_throttle]]. Host-RAM
contributor removed; does **not** explain the 9.6 GB allocator.

**ROUND 3 — 2026-07-28: ALLOCATOR NAMED. The ~9.6 GB is a php-cgi burst on a CO-TENANT WORDPRESS
VHOST, not the ERP and not MariaDB.** Watcher ran 07-27 19:00 → 07-28 12:35 (6,289 samples) and caught
it live at 09:38:38: **69 php workers / 6,086 MB in one 10 s sample** from a <1 GB all-day baseline,
gone by 09:39:00 (~20 s total). 24 of the top 30 by RSS were `php-cgi` owned by cPanel user **`alison`**
(a WordPress docroot, `alison_wp_z9crm`) — the ERP runs as user `evolution`. Prod is a **shared cPanel
box with ~20 accounts, mostly WordPress/WooCommerce + Wordfence**, all under constant bot scanning.
MariaDB's RSS *fell* during the spike (swapped out) and peak `Threads_connected` all day was **3–7**
vs `max_connections` 90 — victim at micro-scale as at macro. Causal chain: connection burst → Apache
prefork 100–150 children → php-cgi fork storm → GBs in seconds; pre-P1 that was fatal, post-P1 it cost
23 % avail memory for 10 s and self-recovered. **P1 bought headroom, it did not fix the cause.**
Apache spawns to 100–153 ~15×/day (each a near-miss); only 09:38 had PHP follow.

**Two corrections:** (a) prod `MemTotal` is **11,729 MB (~12 GB), NOT 14,336** — overcommit restates
0.33× → **0.40×**; (b) the "post-crash sweep CLEAN" claim **excluded the WordPress DBs**, and the
17:53 restart log shows ERP tenant tables (`85-1559715131` jobs/modules/quotes/quoteitems/uploads,
`108-1481181613`, `85-1701079776`, `85-1539867819`, central `evolution.users`) were **marked crashed
and MyISAM-auto-repaired** before that sweep ran — so "repaired then verified clean", not "untouched".

**Watcher v2 built 2026-07-28** (tested, **not yet deployed**). v1 named the account but not the
request because it tailed only the ERP domlog on a 20-account box. v2: all-vhost domlog ranking +
top client IPs, per-user RSS rollup over ALL processes (v1's top-30 truncated 39 of 69), **leading**
triggers `php_procs≥25`/`php_rss≥2000MB`/`tcp_estab≥150` (the old mem/swapout triggers are lagging —
the 07:56 dump fired after the event ended and caught nothing), inlined 30-sample run-up, 3 appended
TSV columns (`php_top_user/procs/rss_mb`), `user:20` (no more `evoluti+`), 5 `ps` calls → 1.
`DOMLOG_DIRS` env override for testing. Old v1 kept as `forensic-watch.v1.sh`.

**ROUND 3.1 — 2026-07-30: box STABLE, no recurrence, but round 3's causal chain was WRONG.** 8,319
samples 07-29 12:09 → 07-30 11:23 (10 dumps). MemAvailable floor **38%/47%**, peak php_rss 2,320 MB
(vs 6,086 on 07-28) — Phase 1 holding. **Corrections:** (a) apache spawn storms are NOT the burst
mechanism — all 28 `apache≥100` samples carried near-zero PHP (`apache=153` at `php_rss=350MB`);
bots hitting nonexistent `.php` are 404'd without starting an interpreter, so **`tcp_estab` was a bad
trigger** (3 empty dumps), now retired. (b) It's a **multi-tenant pile-up, not one account** — at
21:51 `madalice` ramped to 1,527 MB then `customc` added 1,542 MB *in the same second*, mysql_conn
1→19; **15 accounts** run PHP, four peak >1.3 GB (`alison` 1590, `customc` 1542, `madalice` 1527,
`corpkicks` 1362). Round 3's `alison`-only framing was a one-event artefact. The ERP peaked at
**832 MB** — lowest significant tenant (3rd confirmation it's not the allocator).
**Separate failure mode found:** 07-29 22:18 lost 2.9 GB avail and recovered in 30 s with only 427 MB
PHP resident at 18% iowait = **page-cache eviction** (backup/mysqldump/AV walk), not anon pressure.
**Cloudflare is NOT in the path:** zero CF edge IPs in all 10 dumps; Azure webshell scanners
(`/wso.php`, `/alfa.php`, `/bless.php`) hit the **origin IP directly**, so CF's WAF/rate-limiting
never applies. CF also can't *cause* spawns (proxy ≤1:1) and by default doesn't cache HTML at all —
WooCommerce cookies force bypass regardless. Fix = firewall origin to CF ranges + `mod_remoteip`.
**`madalice` is OUR site** ([[shoe_customizer_project]]) — tinting is client-side canvas, no GD/Imagick,
so 250 MB workers are plain WooCommerce baseline; cap workers, don't optimise code. **Its workers have
distinct PPIDs + 1–3 s lifetimes = the EA4 `cgi` handler (one interpreter per request), NOT `fcgi`** —
confirm with `/usr/local/cpanel/bin/rebuild_phpconf --current`; if `cgi`, switching handler beats any
cap. Sizing if capping: max_children/FcgidMaxProcessesPerClass **6**, MinProcessesPerClass **0**
(default 3 × 15 accounts × 4 PHP versions), PHP-FPM `pm: ondemand` not `dynamic`; **server-wide
`FcgidMaxProcesses ~30` is worth more than any per-account cap** — it's the only thing that bounds the
coincident case. 4 PHP versions live (ea-php74/81/82/83).

**SLOW QUERY LOG IS A DEAD END — resolved 2026-07-30.** OFF since 07-27 19:46:22: the 19:38
`SET GLOBAL`s were wiped by the Phase 1 restart 8 min later and never persisted. **Nothing to pull.**
Don't ask for `/var/lib/mysql/server2-slow.log` again until the `my.cnf` block (in Phase 0) is applied.

**Watcher v2.1 built + tested 2026-07-30, NOT deployed.** v2's triggers worked; its vhost tables were
unusable, so *which URL* is still open. F1 inode-dedupe (cPanel keeps both `domlogs/<domain>` and
`domlogs/<user>/<domain>` for one file → each vhost ranked 2–4× and top-IP counts skewed unevenly);
F2 **every ranking row read exactly `200`** (non-static lines within `tail -200` saturates → it ranked
log *length*) → now counts inside a real time window via the Apache timestamp; F3 `'*-bytes_log'`
missed `kurri-imapbytes_log`; F4 **top-request-path tables** (global + per-vhost) — "last 25 lines"
never aggregated, so 40 one-off scanner paths looked identical to 40 hits on one expensive endpoint;
F5 retire `tcp_estab`, `php_rss` 2000→**1500**, add `mysql_conn≥15`; F6 vm.swappiness/PSI/
Committed_AS/Cached + backup-dump-AV scan for the 22:18 class. TSV stays **26 cols** (no roll).
Gotcha baked in: the F6 evictor scan needs **both** bracketed alternatives (`[c]pbackup|[b]ackup|…`)
**and** whole-ancestry exclusion — a bare `pgrep -f` reports the search itself, and excluding `$$`
alone fails because the command-substitution subshell has the same argv under a different PID.

**HANDOFF — open items, highest value first:**
1. **Deploy watcher v2.1 to prod** (`--stop`, copy, restart; `@reboot` line unchanged) → next trip
   names the *URL*. v2's existing dumps CANNOT answer it retrospectively (`madalice` appears only in
   their process/RSS sections, never the vhost detail).
2. **Per-account resource limits — the real fix, needs Shane's decision.** ~15 PHP accounts, zero
   isolation. WHM entry-process/memory limits, CloudLinux LVE, or move the ERP off the shared host.
   Cheapest partial: server-wide `FcgidMaxProcesses`.
3. **Confirm the PHP handler** (`rebuild_phpconf --current`) — changes the whole remedy.
4. **Persist the slow-log settings in `my.cnf`** — already wiped once by the RAM reboot.
5. **Lock origin to Cloudflare ranges + `mod_remoteip`** — affects every vhost, Shane's call.
6. `OOMScoreAdjust=-500` on the mariadb systemd unit — recommended, unconfirmed.
7. **Approve release note 147** (prod `evolution.releasenotes`, still `status=0` = invisible).
8. Chase the external prod scheduler job list + the cPanel backup schedule (would likely explain
   22:18). Root crontab also runs a per-minute `top` snapshot into `/root/toplogs/` (last 30 kept).

**Watcher survived the reboot — open item #1 from the 07-27 handoff is answered from the data**
(continuous samples either side of the 18:14→19:00 reboot gap). Don't ask again.

**Remaining phases** (in the audit doc): P2 indexes (migrations/file), P3 code (kill SQL_CALC_FOUND_ROWS,
LIKE→=, tab lazy-loading, see [[evolution_sort_injection_validator]]), P4 MyISAM→InnoDB migration.
P4's wkhtmltopdf-throttle item is now **done**.

**Why:** multi-week effort; must survive multiple sessions without losing progress, and round 2 shows how
easily a bad parse sends the whole investigation down the wrong path.
**How to apply:** always open the audit doc and read the 2026-07-27 sections before acting on anything
from rounds 1–2; only query dev tenant per [[dev_db_only]]; the outstanding external evidence is the
watcher output naming the 9.6 GB allocator, then the slow-query log.