# How-To: Persist the MariaDB Hardening (and stop it drifting back)

**Status:** proposal + runbook — nothing below has been applied.
**Written:** 2026-08-27
**Context:** [MARIADB_OOM_INVESTIGATION.md](../../evolution/audits/MARIADB_OOM_INVESTIGATION.md)

---

## 1. Why this exists

On 2026-07-27 two changes were made live with `SET GLOBAL`: `max_connections`
lowered to 90, and the slow query log turned on. Neither was written to
`/etc/my.cnf`. Both were lost at the next MariaDB restart — and MariaDB has since
been OOM-killed and restarted **ten more times** between 08-12 and 08-25, so they
are long gone.

The `/etc/my.cnf` changes made the same evening (`tmp_table_size`,
`max_heap_table_size`, `key_buffer_size`) **did** survive, and are still correct on
prod today. That is the whole point: what went in a file is still there, what was
set at runtime is not.

`SET GLOBAL` is a runtime override. On a box whose database is being killed weekly,
it has a half-life measured in days. Everything below goes in a file.

---

## 2. Live state, read from prod 2026-08-27

Verified directly against prod (read-only `claude` account), **not** inferred from the July forensic:

| Variable | Live now | Phase-1 intent (07-27) | Verdict |
|---|---:|---:|---|
| `max_connections` | **110** | 90 | **drifted** |
| `tmp_table_size` | 32 MB | 32 MB | persisted ✓ |
| `max_heap_table_size` | 32 MB | 32 MB | persisted ✓ |
| `key_buffer_size` | 512 MB | 512 MB | persisted ✓ |
| `innodb_buffer_pool_size` | 1,024 MB | 1,024 MB | ✓ |
| `long_query_time` | 2.0 | 3.0 | ✓ (finer than intended — keep) |
| `slow_query_log` | **OFF** | ON | **lost** |
| `performance_schema` | OFF | OFF | ✓ correct for this box |

Version: MariaDB **10.6.24**. Uptime **219,967 s = 2 d 13 h**, which dates the last restart to
**2026-08-25 03:29** — exactly the OOM kill recorded in Round 5.1. The database has not survived three
days.

**This corrects an earlier reading of this document.** Phase 1's `/etc/my.cnf` changes *did* persist —
`tmp_table_size`, `max_heap_table_size` and `key_buffer_size` are all still at their intended values.
Only two things need doing, and one of them is a single line:

1. **`max_connections` is 110, not the intended 90.** `Max_used_connections` has reached **91**, so the
   pool is being genuinely exhausted, not merely mis-set.
2. **`slow_query_log` is OFF.** `Slow_queries` = 247 proves it was briefly on during this uptime and is
   not now. `long_query_time` is already 2 s, so only the switch is missing.

Status counters over the current 2 d 13 h uptime:

| Counter | Value | Reading |
|---|---:|---|
| `Key_read_requests` / `Key_reads` | 2,401,809,042 / 679,879 | **99.97 % hit** — 512 MB key buffer is ample; do **not** enlarge it |
| `Created_tmp_tables` / `..._disk_tables` | 2,412,275 / 732,756 | **30.4 % spilling to disk** |
| `Table_locks_waited` | 4,092 | low against that query volume — MyISAM locking is not the current bottleneck |
| `Max_used_connections` | 91 | against a ceiling of 110 |

The 30.4 % temp-table spill rate is worth a note but **not** a reason to raise `tmp_table_size`: that
value is charged *per in-flight temp table*, so raising it from 32 MB trades a bounded disk cost for an
unbounded RAM spike on a box that is already OOM-killing its database. Most spills are forced by
TEXT/BLOB columns regardless of the setting. Leave it.

## 3. The block

Append to `/etc/my.cnf` under `[mysqld]`. cPanel preserves this file across
updates; it does not rewrite the `[mysqld]` section.

```ini
###############################################################################
# OOM hardening — added 2026-08-27, ref audits/MARIADB_OOM_INVESTIGATION.md
#
# Phase 1 (2026-07-27) already persisted tmp_table_size, max_heap_table_size and
# key_buffer_size and they are STILL CORRECT on prod -- do not re-set them here
# and do not "tune" them. Only the two settings below actually need changing.
###############################################################################
[mysqld]

# --- connection ceiling -----------------------------------------------------
# Currently 110; Phase 1 intended 90 and Max_used_connections has hit 91, so the
# pool is genuinely being exhausted. Each connection can charge sort/join/read
# buffers plus a temp table, so the ceiling is a memory claim, not just a count.
# 90 returns "too many connections" to the offender instead of taking the box.
max_connections                 = 90

# Reserve one slot so root can still get in when the pool is exhausted.
extra_max_connections           = 1

# --- evidence ---------------------------------------------------------------
# OFF right now. Eleven MariaDB deaths have produced zero query evidence because
# the 07-27 SET GLOBAL was wiped by the first restart 8 minutes later.
# long_query_time is ALREADY 2.0 on prod -- only the switch is missing.
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql-slow.log
long_query_time                 = 2
log_slow_verbosity              = query_plan,explain
# Deliberately OFF: this box is MyISAM-heavy and it would drown the log.
log_queries_not_using_indexes   = 0
```

Deliberately **not** included, and the reason, so nobody adds them later:

| Setting | Live value | Why left alone |
|---|---:|---|
| `key_buffer_size` | 512 MB | 99.97 % hit rate. Enlarging wastes RAM; shrinking costs MyISAM index IO. |
| `tmp_table_size` / `max_heap_table_size` | 32 MB | charged *per in-flight temp table*; raising it to cut the 30 % disk-spill rate trades bounded disk for unbounded RAM spike |
| `innodb_buffer_pool_size` | 1 GB | ERP core tables are MyISAM; InnoDB is a minority here |
| `performance_schema` | OFF | costs ~400 MB we do not have |

```

Create the log file with the right ownership *before* restarting, or MariaDB will
fail to start:

```bash
touch /var/log/mysql-slow.log
chown mysql:mysql /var/log/mysql-slow.log
chmod 640 /var/log/mysql-slow.log
```

Add rotation — a slow log at `long_query_time=2` on a busy box grows fast:

```bash
cat > /etc/logrotate.d/mysql-slow <<'EOF'
/var/log/mysql-slow.log {
    daily
    rotate 14
    missingok
    notifempty
    compress
    delaycompress
    create 640 mysql mysql
    postrotate
        /usr/bin/mysqladmin flush-logs 2>/dev/null || true
    endscript
}
EOF
```

---

## 4. Stop MariaDB being the sacrificial victim

The Linux OOM killer picks the largest RSS. MariaDB, holding ~2 GB of buffers by
design, is almost always the largest single process — so it dies for a memory
crime committed by 40 php-cgi workers that are individually small. This does not
fix the cause; it stops the database being the thing that dies from it.

```bash
mkdir -p /etc/systemd/system/mariadb.service.d
cat > /etc/systemd/system/mariadb.service.d/oom.conf <<'EOF'
[Service]
# The OOM killer scores by RSS, so mariadbd (2GB of buffers by design) is
# almost always chosen -- for memory pressure caused by php-cgi. Bias the
# kernel away from it so the actual allocator is killed instead.
OOMScoreAdjust=-500
EOF
systemctl daemon-reload
```

Takes effect at the next MariaDB restart. Verify with:

```bash
cat /proc/$(pgrep -o mariadbd)/oom_score_adj    # expect -500
```

**Caveat, stated plainly:** this makes some *other* process the victim. That is
the point — the other candidates are php-cgi workers, which are per-request and
disposable, whereas killing MariaDB takes every account on the box down at once.
But it is a redirection, not a cure. The cure is the concurrency cap in
[PHP-HANDLER-CONCURRENCY-CAP-HOWTO.md](PHP-HANDLER-CONCURRENCY-CAP-HOWTO.md).

---

## 5. Apply

```bash
cp /etc/my.cnf /root/my.cnf.bak-$(date +%F)      # ALWAYS
vi /etc/my.cnf                                    # paste the block from §3
touch /var/log/mysql-slow.log && chown mysql:mysql /var/log/mysql-slow.log

mysqld --help --verbose >/dev/null 2>/root/mycnf-check.txt; grep -i error /root/mycnf-check.txt

/usr/local/cpanel/scripts/restartsrv_mysql
```

**Restarting MariaDB drops every live connection.** Do it out of hours. All
accounts on the box are affected, not just the ERP.

---

## 6. Verify

```bash
mysql -e "SHOW VARIABLES WHERE Variable_name IN
 ('max_connections','tmp_table_size','max_heap_table_size','key_buffer_size',
  'slow_query_log','slow_query_log_file','long_query_time','performance_schema')"
```

Expect `max_connections 90` and `slow_query_log ON`. The other four should be
**unchanged** from their current values (`tmp_table_size 33554432`,
`max_heap_table_size 33554432`, `key_buffer_size 536870912`, `long_query_time
2.000000`) — if any of them moved, something else edited `/etc/my.cnf` and that is
worth knowing about before going further.

Then, after 24 h, the payoff — the first query evidence this investigation has
ever had:

```bash
mysqldumpslow -s t -t 25 /var/log/mysql-slow.log      # slowest by total time
mysqldumpslow -s c -t 25 /var/log/mysql-slow.log      # most frequent
```

Watch these two for regressions:

```bash
mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN
 ('Key_reads','Key_read_requests','Created_tmp_tables','Created_tmp_disk_tables',
  'Max_used_connections','Aborted_connects')"
```

- `Key_reads / Key_read_requests` — currently 0.028 %. If it climbs past ~3 % the
  512M key buffer has become too small for a grown dataset. Not expected soon.
- `Created_tmp_disk_tables / Created_tmp_tables` — currently 30.4 %. Watch it, but
  **do not** raise `tmp_table_size` to fix it; that converts a bounded disk cost
  into an unbounded RAM spike on a box that is OOM-killing its database.
- `Max_used_connections` pinned at 90 → something is genuinely opening 90+
  connections and needs investigating, **not** a higher ceiling. It has already
  reached 91 against the current 110, so expect this to bite; that is the signal
  we want, not a reason to undo the change.

---

## 7. Rollback

```bash
cp /root/my.cnf.bak-<date> /etc/my.cnf
rm -f /etc/systemd/system/mariadb.service.d/oom.conf
systemctl daemon-reload
/usr/local/cpanel/scripts/restartsrv_mysql
```

---

## 8. Drift check — add this to the monthly routine

The whole reason this document exists is that a fix applied at runtime silently
disappeared. Guard against a repeat:

```bash
# Anything set at runtime that does NOT match the file is drift.
diff <(mysql -N -e "SHOW VARIABLES" | sort) /root/mysql-vars-baseline.txt
```

Capture `/root/mysql-vars-baseline.txt` immediately after §6 passes.