Stop MySQL Crashes: High-Performance my.cnf Tuning Guide
MySQL or MariaDB keeps dying under load? Learn the my.cnf settings that stop crashes on North Sydney portals—buffer pool, connections, logs—with copy-paste checks and when to book Fixwebnode remote tuning.
Site owners and small businesses running portals on MySQL or MariaDB often hit sudden restarts, lock waits, and “server has gone away” errors when traffic spikes—especially on shared or lightly tuned VPS hosts. This guide walks through practical my.cnf diagnostics and fixes you can run yourself, then shows when remote specialist tuning is the safer path.
If you manage a content, membership, or course-style portal in or around North Sydney and the database is the weak link, Fixwebnode provides direct remote help for Optimize MySQL/MariaDB my.cnf: Stopping MySQL Database Crashes: High-Performance configuration—no marketplace bidding, just hands-on tuning against your real workload.
Why my.cnf tuning matters when portals keep crashing
Default MySQL and MariaDB packages ship conservative memory and I/O settings. That is fine for a quiet install. It fails when WordPress, LMS plugins, reporting jobs, and API traffic share one instance. Underrated buffer pools thrash disk; oversized pools get the OOM killer; unbounded connections exhaust RAM; unrotated binary logs fill the volume and the server stops cleanly—or not cleanly.
High-performance tuning is not “set every knob to maximum.” It is matching InnoDB memory, connection limits, temporary tables, and logging to the RAM, CPU, and disk you actually have, then verifying with status counters and error logs. The steps below assume Linux (Debian/Ubuntu or RHEL-like), root or sudo access, and that you back up my.cnf before editing.
Why does MySQL keep crashing after traffic spikes on my portal?
Most post-spike crashes come from three mismatches: InnoDB buffer pool too small or too large for RAM, connection or thread storms without a safe ceiling, and disk pressure from binary logs or temp tables. Fix the mismatch in my.cnf, restart cleanly, and re-check status variables—do not only restart the service and hope.
| Symptom | Quick check / fix | When to call Fixwebnode |
|---|---|---|
| OOM kill or sudden mysqld exit | Size innodb_buffer_pool_size to ~50–70% RAM; confirm with free -h | Repeated OOM after safe limits, mixed workloads |
| “Too many connections” / hung PHP | Cap max_connections; raise carefully with thread cache | Bursty LMS or checkout traffic still saturates |
| Disk full / binary log errors | Expire binlogs; move datadir off full volume | Replication, point-in-time recovery required |
Common issues that crash MySQL/MariaDB under portal load
These problems show up repeatedly on production portals. Each has a different root cause—do not treat them as one generic “MySQL is slow” ticket.
- InnoDB buffer pool thrash or OOM: High
Innodb_buffer_pool_reads, heavy disk I/O, or kernel OOM killer entries for mysqld after campaigns or course enrolments. - Connection and thread exhaustion: Errors like “Too many connections,” PHP-FPM workers blocked on the DB, or
Threads_connectedstuck near the ceiling. - Binary log or temp-table disk fill: mysqld refuses writes, error log mentions errno 28, or
/var/lib/mysqlconsumes the whole root volume. - Unsafe crash recovery after unclean shutdown: Long InnoDB recovery on restart, corrupted tables flagged in the error log, portals stuck in maintenance loops.
Issue 1 — Buffer pool wrong for RAM (thrash or OOM)
If the buffer pool is tiny, InnoDB reads data pages from disk constantly and latency spikes. If it is larger than available RAM (plus OS and PHP needs), the kernel kills mysqld. North Sydney portals on 4–8 GB VPS boxes hit this often after a plugin or traffic change.
Step 1 — Capture RAM and current InnoDB settings
free -h
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool%';"
sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"Note total RAM and how many buffer pool reads come from disk versus the pool. A high ratio of Innodb_buffer_pool_reads to Innodb_buffer_pool_read_requests means the pool is undersized.
Step 2 — Locate and back up my.cnf
mysqld --help --verbose 2>/dev/null | grep -A1 'Default options'
sudo cp -a /etc/mysql/my.cnf /etc/mysql/my.cnf.bak.$(date +%F)
# Debian/Ubuntu often uses drop-ins:
ls /etc/mysql/mysql.conf.d/ /etc/mysql/mariadb.conf.d/ 2>/dev/nullStep 3 — Set a safe pool size and instances
On a dedicated DB host with 8 GB RAM, a common starting point is 4–5G for the pool, leaving headroom for the OS and connections. Prefer one file under the conf.d directory so package upgrades do not clobber you.
sudo tee /etc/mysql/mysql.conf.d/99-performance.cnf <<'EOF'
[mysqld]
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT
EOFAdjust the size downward on smaller VPS hosts (for example 1G–2G on 4 GB total RAM if PHP runs on the same box).
Step 4 — Restart and verify
sudo systemctl restart mysql || sudo systemctl restart mariadb
sudo systemctl is-active mysql mariadb
sudo mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
sudo journalctl -u mysql -u mariadb -n 50 --no-pagerConfirm the variable matches your file and the error log has no allocation failures. Re-check buffer pool read ratios after peak hour.
If mysqld still exits with OOM after a conservative size, stop DIY guessing—Fixwebnode can map pool size against real process RSS, PHP-FPM workers, and concurrent queries remotely.
Issue 2 — Connection storms and thread pile-ups
Portals that open a new DB connection per page view, plus cron and queue workers, can hit max_connections in seconds. Raising the limit without enough RAM multiplies memory use per thread and recreates OOM risk.
Step 1 — Measure live pressure
sudo mysql -e "SHOW VARIABLES LIKE 'max_connections';"
sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_%';"
sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections';"
sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Aborted_%';"If Max_used_connections is glued to the ceiling, or aborted connects climb during enrolments or flash sales, you need both a higher safe ceiling and fewer wasteful clients.
Step 2 — Apply controlled limits and caches
sudo tee -a /etc/mysql/mysql.conf.d/99-performance.cnf <<'EOF'
[mysqld]
max_connections = 150
thread_cache_size = 32
wait_timeout = 120
interactive_timeout = 120
table_open_cache = 4000
EOF
sudo systemctl restart mysql || sudo systemctl restart mariadbStep 3 — Align the app tier
# Example: reduce PHP-FPM pm.max_children if it exceeds DB capacity
sudo grep -R "pm.max_children\|pm =" /etc/php/*/fpm/pool.d/ 2>/dev/null
sudo tail -n 100 /var/log/mysql/error.log 2>/dev/null
sudo tail -n 100 /var/log/mariadb/mariadb.log 2>/dev/nullPersistent object caching (Redis/Memcached) and connection pooling at the app layer often cut required max_connections more effectively than raw increases. After changes, watch Threads_connected during a normal busy window.
Call Fixwebnode when you cannot tell whether the bottleneck is MySQL, PHP-FPM, or a plugin opening unbounded connections—especially on LMS-style stacks similar to our LearnDash WordPress LMS Setup in Hawthorn for Online Course Portals work, where concurrent learners amplify DB sessions.
Issue 3 — Disk full from binary logs or temp tables
With binary logging on (common for backups and replication), logs grow until the data partition hits 100%. Large ORDER BY / GROUP BY queries also spill huge temp tables to disk. Either path stops writes and looks like a “random” crash to end users.
Step 1 — Confirm disk and log footprint
df -h
sudo du -sh /var/lib/mysql/* 2>/dev/null | sort -h | tail -n 20
sudo mysql -e "SHOW VARIABLES LIKE 'log_bin%';"
sudo mysql -e "SHOW VARIABLES LIKE 'binlog%';"
sudo mysql -e "SHOW BINARY LOGS;"Step 2 — Expire old binlogs safely (keep a retention window)
sudo mysql -e "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY);"
# Persist retention so it does not grow back unchecked:
sudo tee -a /etc/mysql/mysql.conf.d/99-performance.cnf <<'EOF'
[mysqld]
binlog_expire_logs_seconds = 259200
# MariaDB older-style alternative if needed:
# expire_logs_days = 3
max_binlog_size = 256M
tmp_table_size = 64M
max_heap_table_size = 64M
EOF
sudo systemctl restart mysql || sudo systemctl restart mariadb
df -hStep 3 — Find queries forcing on-disk temps
sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
# Enable slow query log briefly if not already on
sudo mysql -e "SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 2;"Review the slow log for sorts and joins missing indexes. Raising tmp_table_size without indexes only hides the problem until RAM runs out.
Book a specialist when you rely on point-in-time recovery, replicas, or cannot purge logs without breaking backup jobs—disk layout and binlog policy should be designed together.
Issue 4 — Unclean shutdown and painful InnoDB recovery
Power loss, OOM kills, or kill -9 on mysqld force crash recovery. Mis-set innodb_flush_log_at_trx_commit or tiny redo logs lengthen downtime and raise corruption risk.
Step 1 — Read the error log before changing durability
sudo journalctl -u mysql -u mariadb -n 200 --no-pager
sudo grep -iE 'InnoDB|crash|recovery|corrupt' /var/log/mysql/error.log /var/log/mariadb/mariadb.log 2>/dev/null | tail -n 50Step 2 — Prefer safe durability defaults on portals that take payments or enrolments
sudo tee -a /etc/mysql/mysql.conf.d/99-performance.cnf <<'EOF'
[mysqld]
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_io_capacity = 400
innodb_io_capacity_max = 1000
EOF
sudo systemctl restart mysql || sudo systemctl restart mariadbOnly consider innodb_flush_log_at_trx_commit = 2 when you fully accept a small loss window on host power failure and have tested backups. After any crash, run checks on critical schemas once the server is up:
sudo mysqlcheck -u root -p --all-databases --check --optimizeIf recovery loops or tables fail checks, do not keep restarting—take a filesystem snapshot if possible and escalate.
When DIY is enough vs when to book Fixwebnode
DIY is reasonable when you have SSH, a recent backup, a single primary with no replicas, and symptoms map cleanly to one of the issues above. Apply one change set at a time, restart once, and measure with SHOW GLOBAL STATUS and the error log.
Book Fixwebnode when any of the following is true: crashes continue after conservative pool and connection caps; you run replication or strict point-in-time recovery; the portal mixes heavy LMS, e-commerce, and reporting on one instance; or you lack time to correlate slow queries with hardware limits. Remote sessions work well for this subject—we inspect configs, status counters, and logs over a secure link and leave you with a documented my.cnf baseline.
Geography-wise, Fixwebnode supports clients across our All service areas, including remote database work for North Sydney and broader metro teams. Deeper architecture reviews pair naturally with Melbourne CBD Database Architecture & MySQL/PostgreSQL Tuning when you are redesigning schemas or splitting read load—not only patching one variable.
Talk through your my.cnf and crash pattern
Bring your RAM size, whether PHP shares the host, a redacted error-log snippet, and what changed before the first crash (plugin, traffic campaign, or host resize). We will map that to a stable high-performance baseline rather than a pile of unverified internet snippets.
Start the conversation on the service page for Optimize MySQL/MariaDB my.cnf: Stopping MySQL Database Crashes: High-Performance and book a remote tuning session with Fixwebnode—direct specialist support so your North Sydney portal stays up when traffic arrives.
