Database Performance Tuning Basics (WordPress VPS)
Database tuning is most effective when you measure first, change one thing at a time, and validate the result. On WordPress VPS servers, many "slow site" incidents are caused by one of three categories: memory pressure (buffer pool too small), slow queries (missing indexes or heavy plugins), or disk I/O bottlenecks (storage saturation during backups or peak writes).
Start with measurement: confirm CPU/memory/disk health, check current MySQL/MariaDB variables, then enable the slow query log to find real query offenders.
Incorrect MySQL/MariaDB configuration changes can prevent the database from starting. Always keep a rollback plan and validate syntax before restarting.
Mental Model
Prerequisites
- Access to the database CLI (
mysql). - Permission to view variables/status and (if needed) edit DB configuration.
- Enough disk space for logs if you enable slow query logging.
Core Workflow
- Confirm the server is healthy (CPU, memory, disk).
- Check MySQL/MariaDB runtime variables (buffer pool size, max connections).
- Capture symptoms (slow queries, high threads running, lock waits).
- Enable slow query log (short window) and identify the real offenders.
- Tune/optimize, then verify.
Examples (Commands + Expected Output)
Variable names are consistent, but values depend on your RAM and workload.
Check InnoDB buffer pool size
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
Expected output:
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 1073741824|
+-------------------------+-----------+
Use case: The buffer pool is a key driver of read performance.
Check max connections
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"
Expected output:
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
Use case: Too many connections can exhaust RAM; too few can cause errors under load.
Check current running threads (pressure signal)
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
Expected output:
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_running | 12 |
+-----------------+-------+
Use case: Spikes often correlate with slow admin pages and timeouts.
Inspect active queries (processlist)
mysql -u root -p -e "SHOW FULL PROCESSLIST;"
Expected output (excerpt):
Id User Host db Command Time State Info
10 wpuser localhost wordpress Query 5 Sending SELECT * FROM wp_posts ...
Use case: Identify long-running queries and stuck states.
Check table engine distribution (InnoDB vs others)
mysql -u root -p -e "SELECT ENGINE, COUNT(*) AS tables FROM information_schema.TABLES WHERE TABLE_SCHEMA='wordpress' GROUP BY ENGINE;"
Expected output:
+--------+--------+
| ENGINE | tables |
+--------+--------+
| InnoDB | 12 |
+--------+--------+
Use case: Confirms whether --single-transaction is safe for dump consistency (InnoDB).
Use EXPLAIN to understand a query plan
mysql -u root -p -D wordpress -e "EXPLAIN SELECT ID, post_date FROM wp_posts WHERE post_type='post' AND post_status='publish' ORDER BY post_date DESC LIMIT 10;"
Expected output (excerpt):
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | wp_posts | ref | type_status | ... | ... | ... | 1000 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------------+
Use case: Confirm whether a query is using indexes or scanning too many rows.
Slow Query Logging (Safe Default Pattern)
Enable slow query logging for a short window to capture real offenders.
Example configuration (paths vary):
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1
Slow logs can grow quickly on busy sites. Ensure /var/log has space and log rotation is configured.
WordPress VPS Use Cases
| Symptom | Likely DB-related cause | What to check |
|---|---|---|
| Slow wp-admin | Expensive queries / too many running threads | SHOW FULL PROCESSLIST, slow query log |
| CPU spikes on DB | Query storms or missing indexes | sar, top, slow query log |
| Disk I/O wait | Heavy writes (backups, imports, logs) | iostat -xz, vmstat |
Troubleshooting
| Problem | Likely cause | Fix |
|---|---|---|
| DB restart fails | Config syntax error | Revert recent changes, check logs |
| Performance worse after tuning | Wrong assumption | Roll back and re-measure |
| Slow query log not created | Path/permission issue | Fix directory ownership and file permissions |
Best Practices
- Measure first, tune second.
- Apply one change at a time and validate.
- Keep a tested backup and a rollback plan.
Cheat Sheet
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
mysql -u root -p -e "SHOW FULL PROCESSLIST;"
mysql -u root -p -e "SELECT ENGINE, COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA='wordpress' GROUP BY ENGINE;"