MySQL and MariaDB Tuning Basics for a Busy Site

MySQL's default configuration is designed to start on anything, including a machine with 512 MB of RAM. On a server with 8 GB, that means the database uses a fraction of what it could and reads from disk for data that would fit in memory ten times over. Four settings fix most of it; then the slow query log tells you what the application is doing wrong.

Find the config

Ubuntu/Debian: /etc/mysql/mariadb.conf.d/50-server.cnf. AlmaLinux/Rocky: /etc/my.cnf.d/mariadb-server.cnf or /etc/my.cnf. Plesk: /etc/mysql/my.cnf or /etc/my.cnf; edit there, not through the panel. Settings go under [mysqld].

1. innodb_buffer_pool_size — the one that matters

InnoDB caches table data and indexes in the buffer pool. If the pool is bigger than your data, every read after the first comes from memory. The default of 128 MB is far too small for any real site.

Size it to 50–70% of RAM on a dedicated database server, 25–40% on a server that also runs PHP and the web server. Check how big your data actually is:

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024) AS mb FROM information_schema.tables;

If that is 600 MB and you have 8 GB of RAM, a 2 GB pool holds everything with room to grow:

[mysqld]
innodb_buffer_pool_size = 2G

2. innodb_log_file_size

The redo log absorbs writes before they are flushed to the data files. Too small and write-heavy sites stall on checkpoints. A quarter of the buffer pool is a sensible default:

innodb_log_file_size = 512M

3. Connections and per-connection buffers

max_connections = 200

Enough that PHP-FPM's pm.max_children plus a margin can all connect at once. Beware the per-connection buffers (sort_buffer_size, join_buffer_size, read_buffer_size): they are allocated per connection and raising them "for performance" is how a server with 200 connections runs out of memory. Leave them at defaults.

4. Temporary tables

Queries with GROUP BY, ORDER BY and DISTINCT on large sets create temp tables; when they exceed the in-memory limit they go to disk.

tmp_table_size = 64M
max_heap_table_size = 64M

Check how often disk temp tables happen: SHOW GLOBAL STATUS LIKE 'Created_tmp%tables'; — if Created_tmp_disk_tables is a large fraction of Created_tmp_tables, raise these.

Apply

sudo systemctl restart mariadb
sudo journalctl -u mariadb -n 20    # confirm it came up cleanly

Then measure

Let it run for a day and check the numbers that show whether the pool is big enough:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_reads (disk) divided by Innodb_buffer_pool_read_requests (all) should be well under 1%. If it is not, the pool is too small for the working set.

The slow query log

Configuration can only do so much; the largest gains come from the queries. Turn on the slow log:

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

After a day:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

lists the ten queries that consumed the most total time. For each, EXPLAIN it in the MySQL client; a type: ALL with a large rows estimate is a full table scan that wants an index. On WordPress, the usual offenders are wp_postmeta lookups from plugins (an index on meta_key(191), meta_value(191) often helps), wp_options autoloaded rows that have grown to megabytes, and WooCommerce order queries without the HPOS tables enabled.

Tools that do the arithmetic

mysqltuner.pl reads your status counters and config and prints recommendations. It is a good second opinion, with the caveat that it will suggest raising anything that is ever hit; read its output with the memory budget in mind.

wget -q http://mysqltuner.pl -O mysqltuner.pl && perl mysqltuner.pl

What not to do

• Do not set query_cache_size — the query cache was removed in MySQL 8 and is off by default in MariaDB for good reason: it serialises writes.
• Do not disable innodb_flush_log_at_trx_commit (set it to 0) for speed on a production database; a power loss then loses the last second of committed transactions. 2 is an acceptable middle ground on a VPS with reliable storage.
• Do not tune per-connection buffers upwards.

On a VPSPioneer managed VPS the buffer pool, log size and connection limit are set from the server's RAM and the application's data size at setup, and the slow log is on from day one so that when a site slows down, the query responsible is already in a file.