MySQL’s defaults are designed to start on almost any machine, not to run your workload well. A handful of settings — and, more importantly, good indexes — make the biggest difference. This guide focuses on what matters on a typical VPS running MySQL 8 or MariaDB 10.6+.

Before you touch anything: measure

Tuning without data is guesswork. Collect a baseline:

sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'; SHOW GLOBAL STATUS LIKE 'Slow_queries'; SHOW GLOBAL STATUS LIKE 'Uptime';"
free -h

Tools that summarise the status counters into recommendations:

  • MySQLTuner — sudo apt install mysqltuner && sudo mysqltuner. Run it after the server has been up for at least 24 hours of normal traffic.
  • Percona Toolkit — pt-query-digest for analysing slow logs.

Treat their advice as hints, not commands.

Where to put settings

Create your own file so package upgrades don’t overwrite it:

sudo nano /etc/mysql/mysql.conf.d/99-tuning.cnf      # MySQL on Ubuntu
sudo nano /etc/mysql/mariadb.conf.d/99-tuning.cnf    # MariaDB

Start it with [mysqld], and restart the service after changes.

1. InnoDB buffer pool (the big one)

The buffer pool caches table data and indexes in RAM. Reads from memory are orders of magnitude faster than from disk.

[mysqld]
innodb_buffer_pool_size = 2G

Guidelines:

  • Dedicated database server: 60–75% of RAM.
  • Shared with web server and PHP: 25–40% of RAM, leaving room for everything else.
  • Bigger than your data is pointless — check total data + index size:
SELECT ROUND(SUM(data_length + index_length)/1024/1024) AS total_mb
FROM information_schema.tables WHERE engine='InnoDB';

Check the hit rate after a day:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_reads (from disk) should be a tiny fraction of Innodb_buffer_pool_read_requests. More in InnoDB buffer pool sizing.

2. Redo log size

A small redo log forces frequent flushing during write-heavy periods.

# MySQL 8.0.30+
innodb_redo_log_capacity = 1G
# MariaDB / older MySQL
innodb_log_file_size = 512M

A good size holds roughly an hour of writes at peak. For mostly-read websites, 256–512 MB is plenty.

3. Flushing and durability

innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
  • O_DIRECT avoids double-caching data in the OS page cache (default on recent MySQL versions on Linux).
  • innodb_flush_log_at_trx_commit = 1 is fully durable. Setting 2 speeds up heavy write workloads but can lose up to about a second of transactions if the server crashes. Leave it at 1 unless you understand and accept that.

4. Connections

max_connections = 150

Each connection uses memory (buffers per thread). More connections isn’t better: if you regularly approach the limit, the cause is usually slow queries or an app that doesn’t release connections. Check the historical peak:

SHOW GLOBAL STATUS LIKE 'Max_used_connections';

Set max_connections comfortably above that peak. See too many connections.

5. Temporary tables

Complex GROUP BY/ORDER BY queries use in-memory temp tables, spilling to disk when they’re too big:

tmp_table_size = 64M
max_heap_table_size = 64M

Check Created_tmp_disk_tables vs Created_tmp_tables. A high disk ratio usually means queries need better indexes, not bigger buffers.

6. The query cache — don’t

MySQL 8 removed the query cache. In MariaDB it still exists but is disabled by default and scales poorly with concurrent writes. Use application caching (Redis) instead.

7. Find slow queries

Settings won’t save a query that scans a million rows. Enable the slow query log:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 0

Analyse it:

sudo pt-query-digest /var/log/mysql/slow.log | less

Then EXPLAIN the worst offenders and add indexes. See the slow query log guide, EXPLAIN and indexing basics.

A starting config for a 4 GB VPS (web + DB together)

[mysqld]
innodb_buffer_pool_size = 1G
innodb_redo_log_capacity = 512M      # or innodb_log_file_size = 256M on MariaDB
innodb_flush_method = O_DIRECT
max_connections = 100
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
skip-name-resolve

For an 8 GB dedicated database server, raise the buffer pool to around 5–6 GB and the redo log to 1–2 GB.

Operating system

  • Swap: a little swap prevents OOM kills; set vm.swappiness=1–10 so MySQL memory isn’t swapped out eagerly.
  • Storage: SSD storage is essential for databases.
  • Open files: the packaged systemd unit already raises limits; check SHOW VARIABLES LIKE 'open_files_limit';.

Measure again

After a day under normal load, compare: query response times in your app, slow log volume, buffer pool hit rate, CPU and I/O wait (iostat -x 5). Change one thing at a time.

Indexes and caching usually beat hardware, but when the working set no longer fits in RAM, more memory is the fix. Our VPS plans range up to 40 GB of RAM, and you can move to a bigger plan by ticket.

  • MySQL
  • MariaDB
  • Performance