How to Tune MySQL and MariaDB Performance with my.cnf

MySQL and MariaDB ship with conservative defaults that leave most of a modern server's memory unused. In this tutorial you will measure a baseline, tune the settings that matter most for InnoDB workloads (buffer pool, redo log, flushing, connections and temporary tables) in a dedicated my.cnf drop-in file, enable the slow query log, and check the effect of each change. The examples use MySQL 8.0 on Ubuntu 24.04, with MariaDB differences noted.

Prerequisites

To follow this guide you need:

• A server running Ubuntu 24.04 LTS, for example a CubePath VPS, with MySQL 8.0 (mysql-server) or MariaDB 10.11 (mariadb-server) installed from the Ubuntu repositories.
• A non-root user with sudo privileges.
• SSD or NVMe storage (the I/O values below assume it).
• Ideally, a server dedicated to the database. If it also runs a web server or application, give MySQL a smaller share of RAM than the rules below suggest.

Step 1 - Measuring a baseline

Tuning without numbers is guessing. Check the server size first:

nproc
free -h

4
               total        used        free      shared  buff/cache   available
Mem:           7.8Gi       1.2Gi       4.9Gi        12Mi       1.9Gi       6.3Gi
Swap:          2.0Gi          0B       2.0Gi

Then look at a few counters that show whether memory is too small. Open a root session with sudo mysql and run:

SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

The key ratio is how often InnoDB has to read from disk instead of memory:

• Innodb_buffer_pool_read_requests: logical reads, served from memory or disk.
• Innodb_buffer_pool_reads: reads that had to go to disk.

If Innodb_buffer_pool_reads is more than about 1% of Innodb_buffer_pool_read_requests after the server has been running under normal load for a while, the buffer pool is too small for your working set. Write the numbers down; you will compare them after tuning. Counters reset on restart, so compare values taken after similar uptime.

Step 2 - Creating a tuning file

Do not edit the packaged configuration files. Ubuntu's /etc/mysql/my.cnf includes every .cnf file in /etc/mysql/mysql.conf.d/ (MySQL) or /etc/mysql/mariadb.conf.d/ (MariaDB) in alphabetical order, so a file starting with 99- overrides earlier defaults and survives package upgrades.

Create the file for MySQL:

sudo nano /etc/mysql/mysql.conf.d/99-performance.cnf

On MariaDB, use /etc/mysql/mariadb.conf.d/99-performance.cnf. The following steps add settings to this file one group at a time; the complete example is in Step 7.

Start it with the section header:

[mysqld]

Step 3 - Sizing the InnoDB buffer pool

The buffer pool caches table and index data in memory. It is by far the most important setting. On a dedicated database server, give it 50-70% of RAM; on a shared server, 25-40%.

For the 8 GB dedicated server in this example, add:

innodb_buffer_pool_size = 5G

A buffer pool larger than your total data and indexes gives no benefit. Check the data size with:

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024) AS total_mb
FROM information_schema.tables
WHERE engine = 'InnoDB';

Tip

On a server that runs only MySQL 8.0, you can set innodb_dedicated_server = ON instead, and MySQL sizes the buffer pool and redo log from the detected RAM. Do not combine it with explicit values for those settings.

Step 4 - Configuring the redo log and flushing

The redo log records changes before they are written to the data files. A redo log that is too small forces frequent checkpoints, which cause write stalls under heavy writes.

On MySQL 8.0.30 and later (Ubuntu 24.04 ships a newer 8.0 release), the size is set with a single variable:

innodb_redo_log_capacity = 1G

On MariaDB, use innodb_log_file_size = 1G instead.

Next, control how InnoDB flushes to disk:

# Bypass the OS page cache for data files; the buffer pool already caches them
innodb_flush_method = O_DIRECT

# Background I/O budget, suited to SSD/NVMe storage
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# 1 = flush the log on every commit (full durability, the default)
innodb_flush_log_at_trx_commit = 1

innodb_flush_log_at_trx_commit trades durability for speed:

Keep 1 for anything that stores orders, payments or user data. Consider 2 only for data you can rebuild. On spinning disks, lower innodb_io_capacity to around 200.

Step 5 - Setting connections, tables and temporary tables

Each connection uses memory for its own buffers, so max_connections should match what your applications actually open, not a large round number. Check the peak with Max_used_connections from Step 1 and add headroom:

max_connections = 300

# Open table handles kept in cache
table_open_cache = 4000

# In-memory temporary tables; the effective limit is the smaller of the two
tmp_table_size = 64M
max_heap_table_size = 64M

If Created_tmp_disk_tables grows quickly compared to Created_tmp_tables, queries are creating temporary tables too large for memory. Raising these limits helps a little; adding the right index or rewriting the query usually helps much more.

Leave the per-connection buffers (sort_buffer_size, join_buffer_size, read_buffer_size) at their defaults unless a specific query needs more; large values multiplied by hundreds of connections can exhaust RAM.

Note

The query cache was removed in MySQL 8.0. MariaDB still has it but disables it by default (query_cache_type = 0); leave it off, because it becomes a lock bottleneck under concurrent writes.

Step 6 - Enabling the slow query log

Configuration tuning has limits; most real gains come from fixing slow queries. The slow query log records every statement that takes longer than a threshold:

slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

Start with 1 second and lower it (for example to 0.5) once the worst queries are fixed. log_queries_not_using_indexes can help too, but it logs a lot on small tables, so enable it only for short investigations.

To summarize the log, install percona-toolkit from the Ubuntu repositories and run pt-query-digest, which groups similar queries and ranks them by total time:

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

Run EXPLAIN on the top queries to see whether they use indexes.

Step 7 - Applying and verifying the configuration

For the 8 GB dedicated MySQL server used in this guide, the complete 99-performance.cnf looks like this:

[mysqld]
# Memory
innodb_buffer_pool_size = 5G

# Redo log and flushing (MariaDB: innodb_log_file_size = 1G)
innodb_redo_log_capacity = 1G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_log_at_trx_commit = 1

# Connections and tables
max_connections = 300
table_open_cache = 4000
tmp_table_size = 64M
max_heap_table_size = 64M

# Slow query log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

On MySQL, check the file for errors before restarting. No output means the configuration is valid:

sudo mysqld --validate-config

Restart the service (mariadb on MariaDB):

sudo systemctl restart mysql
sudo systemctl status mysql

Confirm the running values:

sudo mysql -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','innodb_redo_log_capacity','innodb_flush_method','max_connections','slow_query_log');"

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| innodb_buffer_pool_size  | 5368709120 |
| innodb_flush_method      | O_DIRECT   |
| innodb_redo_log_capacity | 1073741824 |
| max_connections          | 300        |
| slow_query_log           | ON         |
+--------------------------+------------+

Many of these variables are dynamic and can be tested at runtime with SET GLOBAL before you make them permanent in the file. For example, MySQL 8.0 resizes the buffer pool online:

SET GLOBAL innodb_buffer_pool_size = 5368709120;

SET GLOBAL changes are lost on restart unless you also add them to 99-performance.cnf.

After a day of normal traffic, repeat the queries from Step 1. The buffer pool miss ratio should have dropped, and the slow query log shows you what to fix next.

Getting a second opinion with MySQLTuner

MySQLTuner is a script that reads the server's status counters and suggests changes. It is packaged in Ubuntu:

sudo apt install mysqltuner
sudo mysqltuner

Run it only after the server has been up for at least 24 hours under real load, and treat its output as hints to investigate, not values to paste in.

Troubleshooting

MySQL does not start after the change: a typo or an unsupported variable (for example innodb_redo_log_capacity on MariaDB) prevents startup. Read sudo journalctl -u mysql -n 30 and /var/log/mysql/error.log, fix the file and restart.

The server starts swapping or MySQL is killed by the OOM killer: the buffer pool plus per-connection memory exceeds available RAM. Lower innodb_buffer_pool_size or max_connections, and check dmesg | grep -i oom.

Too many connections errors: raise max_connections only after checking that the application closes connections and uses a pool. Hundreds of idle connections usually point to a leak in the application.

No improvement after tuning: the bottleneck is probably specific queries. Work through the slow query log with pt-query-digest and EXPLAIN, and add missing indexes.

Conclusion

You have measured a baseline, sized the InnoDB buffer pool and redo log for your hardware, set sensible flushing, connection and temporary table limits in a drop-in file, and enabled the slow query log to find the queries worth optimizing. As next steps, review the slow log weekly, monitor the server with a tool such as Percona Monitoring and Management or the Prometheus mysqld_exporter, and add a read replica if reads keep growing beyond what one server can handle.