---
title: "How to Tune MySQL and MariaDB Performance with my.cnf"
description: "Tune MySQL 8.0 and MariaDB on Ubuntu 24.04 through my.cnf: size the InnoDB buffer pool and redo log, set connection limits, log slow queries and measure gains."
category: "Database Management"
language: en
source: https://cubepath.com/docs/database-management/mysql-mariadb-performance-optimization-my-cnf
updated: 2026-09-25
---

# 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:

```bash
nproc
free -h
```

```output
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:

```sql
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:

```bash
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:

```ini
[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%.

| Server RAM | Dedicated server | Shared with an application |
|------------|------------------|----------------------------|
| 2 GB | 1G | 512M |
| 4 GB | 2G to 2560M | 1G |
| 8 GB | 5G | 2G to 3G |
| 16 GB | 11G | 5G |

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

```ini
innodb_buffer_pool_size = 5G
```

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

```sql
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:

```ini
innodb_redo_log_capacity = 1G
```

On MariaDB, use `innodb_log_file_size = 1G` instead.

Next, control how InnoDB flushes to disk:

```ini
# 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:

| Value | Behaviour | Data at risk on a crash |
|-------|-----------|-------------------------|
| `1` | Write and flush on every commit | None |
| `2` | Write on commit, flush once per second | Up to about 1 second if the OS crashes |
| `0` | Write and flush once per second | Up to about 1 second even if only MySQL crashes |

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:

```ini
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:

```ini
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:

```bash
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:

```ini
[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:

```bash
sudo mysqld --validate-config
```

Restart the service (`mariadb` on MariaDB):

```bash
sudo systemctl restart mysql
sudo systemctl status mysql
```

Confirm the running values:

```bash
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');"
```

```output
+--------------------------+------------+
| 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:

```sql
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:

```bash
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.