MySQL / MariaDB tuning for production

Introduction

MySQL/MariaDB run by default with minimalist configuration, made to boot on any machine. On a VPS with 4+ GB RAM, you can 10x performance in minutes.

This guide covers:

1. The InnoDB buffer pool (single most important tuning)
2. Slow queries detection
3. Adding indexes on filtered columns
4. Managing simultaneous connections

Prerequisites

• Linux VPS with MySQL 8 or MariaDB 10.6+
• MySQL root access
• At least 2 GB free RAM
• Database backup before any tuning

Step 1: Mandatory backup

mysqldump -u root -p --all-databases --single-transaction > /root/all-databases-$(date +%F).sql

Step 2: Identify your situation

# Version
mysql --version

# Current config
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# Available RAM
free -h

Step 3: Configure InnoDB buffer pool

This is the most important parameter. The buffer pool caches InnoDB data and indexes in RAM, avoiding disk reads.

Rule: 70-80% of available RAM on a MySQL-dedicated server. On a shared VPS (Nginx + PHP + MySQL), count 40-50%.

sudo nano /etc/mysql/mariadb.conf.d/99-tuning.cnf

(Or /etc/mysql/mysql.conf.d/99-tuning.cnf for MySQL)

[mysqld]
# === InnoDB Buffer Pool ===
# Adapt to your RAM: 4G for 8GB RAM, 8G for 16GB, etc.
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4  # 1 instance per GB (max 8)

# === InnoDB logs ===
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2  # 1 for banking, 2 for web
innodb_flush_method = O_DIRECT

# === I/O ===
innodb_io_capacity = 2000           # SSD: 2000, NVMe: 5000+
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# === Connections ===
max_connections = 500
thread_cache_size = 50
table_open_cache = 4000

# === Query cache (MariaDB < 10.6, MySQL 5.x only) ===
# query_cache_size = 0
# query_cache_type = 0

# === Per-connection buffers ===
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M

# === Temp tables ===
tmpdir = /tmp

Restart:

sudo systemctl restart mariadb  # or mysql
sudo systemctl status mariadb

Step 4: Measure impact

Before/after, run:

SHOW ENGINE INNODB STATUS\G

Look for the BUFFER POOL AND MEMORY section:

Buffer pool size   262144  -- (16K pages = 4 GB)
Buffer pool hit rate 999 / 1000  -- should be close to 1000/1000

If hit rate < 990/1000, increase innodb_buffer_pool_size.

Step 5: Enable slow queries

To identify slow queries to optimize:

# In 99-tuning.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1                  # Seconds: log any query > 1s
log_queries_not_using_indexes = 1    # Also log queries without index

Prepare the file:

sudo touch /var/log/mysql/mysql-slow.log
sudo chown mysql:mysql /var/log/mysql/mysql-slow.log
sudo systemctl restart mariadb

After a few hours of production:

sudo less /var/log/mysql/mysql-slow.log

Analyze with pt-query-digest (Percona Toolkit):

sudo apt install -y percona-toolkit
sudo pt-query-digest /var/log/mysql/mysql-slow.log | head -50

Shows the top most resource-consuming queries.

Step 6: Add indexes

The #1 optimization after buffer pool. A slow query is almost always a query without index.

Identify badly indexed queries

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Lines to watch in the result:

• type = ALL: full table scan (bad)
• rows = 1000000: huge scan
• key = NULL: no index used

Create an index

CREATE INDEX idx_email ON users(email);

Then re-EXPLAIN:

type = ref
rows = 1
key = idx_email

Perfect.

Composite indexes

For WHERE status = 'active' AND created_at > '2024-01-01':

CREATE INDEX idx_status_date ON users(status, created_at);

Column order matters: put the column with fewest distinct values first.

Step 7: Automatic audit tools

MySQLTuner

sudo apt install -y mysqltuner
sudo mysqltuner

Gives a prioritized report on what's missing/badly tuned.

tuning-primer

wget https://launchpadlibrarian.net/78745738/tuning-primer.sh
chmod +x tuning-primer.sh
./tuning-primer.sh

Recommendations based on usage history.

⚠️ Don't run these tools on a freshly restarted server: they don't have enough data to give relevant recommendations. Wait at least 24h of usage.

Step 8: Optimize for many short connections (PHP, API)

If your app makes many short connections (typical PHP with PDO without persistence):

# Connection reuse
wait_timeout = 60
interactive_timeout = 60
max_connect_errors = 10000

# Thread pool (MariaDB)
thread_handling = pool-of-threads
thread_pool_size = 16

Step 9: Optimize for large queries (ETL, BI)

If you do heavy analytical queries:

# Buffer for heavy joins
join_buffer_size = 32M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M

# Increase packet size for big INSERT/UPDATE
max_allowed_packet = 256M

Step 10: Enable performance schemas

MySQL 5.7+ and MariaDB 10.2+ have detailed stats schemas:

performance_schema = ON
performance_schema_max_sql_text_length = 4096

Allows real-time inspection:

SELECT * FROM performance_schema.events_statements_summary_by_digest 
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Step 11: Hot InnoDB backups without locking

For hot backups (no lock) with mariabackup:

sudo apt install -y mariadb-backup

# Backup
sudo mariabackup --backup --target-dir=/backup/$(date +%F) \
    --user=root --password='your_password'

# Prepare (replays InnoDB logs for a consistent state)
sudo mariabackup --prepare --target-dir=/backup/2026-05-16

Much faster than mysqldump on databases > 10 GB.

Step 12: Grafana monitoring

To visualize MySQL perfs continuously, see the Prometheus + Grafana tutorial and install mysqld_exporter.

Troubleshooting

"InnoDB: Could not allocate memory"

You allocated too much buffer pool. Reduce innodb_buffer_pool_size.

"Too many connections"

Increase max_connections. If really lots of apps:

max_connections = 1000

⚠️ Each connection consumes ~10-20 MB. 1000 connections = ~15 GB RAM on top of buffer pool.

Queries stay slow even with increased buffer pool

Buffer pool is useless if queries scan the whole disk (no index). Check slow query log.

"Out of sort memory"

Increase sort_buffer_size. But it's also a sign of a badly written query (ORDER BY on a non-indexed column).

Useful commands

-- General status
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free';

-- Current queries
SHOW FULL PROCESSLIST;

-- Kill a blocked query
KILL <id>;

-- Per-table stats
SELECT table_name, table_rows, data_length, index_length 
FROM information_schema.TABLES 
WHERE table_schema = 'your_db' 
ORDER BY data_length DESC;

-- Total DB size
SELECT table_schema, 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "MB"
FROM information_schema.TABLES 
GROUP BY table_schema;

-- Current locks
SELECT * FROM information_schema.INNODB_LOCKS;

-- Reset slow query log
mysql -e "SET GLOBAL slow_query_log = 0; SET GLOBAL slow_query_log = 1;"

Conclusion

With these optimizations:

• The buffer pool absorbs 90%+ of reads (vs disk) → +500% perf
• Indexes on the right columns → queries 10-1000x faster
• Slow query log shows you what to optimize next

Going further:

• Set up a read replica (master/slave replication) to distribute reads
• Use ProxySQL to load balance between instances
• Migrate to TiDB or Vitess if you exceed single-node capacity

Resources

• MySQL documentation: https://dev.mysql.com/doc/refman/8.0/en/
• MariaDB documentation: https://mariadb.com/kb/en/
• Percona Toolkit: https://www.percona.com/software/database-tools/percona-toolkit