How to Tune MariaDB for High Performance

To tune MariaDB for high performance, measure before you change anything, then size the InnoDB buffer pool to most of the RAM the database may use, match the disk I/O settings to your storage, cap connections so memory cannot run out, and find the slow queries that matter. On a dedicated server with NVMe storage and 16 GB of RAM, that usually means a buffer pool of around 10 GB, I/O settings set for fast disks, and indexes that stop the most expensive queries from scanning whole tables. The changes below are for a server that is already doing real work. For a small 1 GB server, follow the low-RAM guide instead.

The examples use MariaDB 10.11 and 11.4 on Ubuntu 22.04 and 24.04, where the configuration lives in /etc/mysql/mariadb.conf.d/. MySQL 8.0 uses the same ideas with a few renamed settings, which are flagged where they differ.

Step 1: Measure the starting point#

Tuning without numbers means guessing. Record these first, and again after each change.

sudo mariadb -e "SELECT VERSION();"
sudo mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Uptime','Threads_connected','Max_used_connections','Questions','Slow_queries','Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests');"
sudo mariadb -e "SELECT ROUND(SUM(data_length+index_length)/1024/1024/1024,2) AS data_gb FROM information_schema.tables;"

The last number is your total data size in gigabytes. Keep it: the buffer pool should be sized against it.

Next, the buffer pool hit ratio. It is the share of reads served from memory rather than disk:

sudo mariadb -N -e "SELECT ROUND(100 - (100 * (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests')), 3) AS hit_pct;"

A healthy server shows 99.5 or higher, measured after it has been busy for a day. A lower figure means too many reads reach the disk, which the next steps address.

Step 2: Size the InnoDB buffer pool#

The buffer pool caches table data and indexes in memory. It is the single most important setting for MariaDB performance. On a dedicated database server, give it about 60 to 70 per cent of RAM. Leave the rest for the operating system’s file cache, for connections, and for any PHP-FPM or application workers that share the machine.

If your total data size from step 1 is smaller than the buffer pool, extra memory is wasted: make the pool the size of the data plus some headroom, and leave the rest free for the system.

Step 3: Write the configuration file#

Create a separate file, so the Ubuntu defaults stay untouched and the changes are easy to find and remove. This example is for a 16 GB dedicated server with NVMe storage:

sudo tee /etc/mysql/mariadb.conf.d/99-performance.cnf >/dev/null <<'EOF'
[mysqld]
# Memory
innodb_buffer_pool_size        = 10G
innodb_buffer_pool_instances   = 8
innodb_log_file_size           = 1G
innodb_log_buffer_size         = 64M

# Disk I/O (NVMe)
innodb_flush_method            = O_DIRECT
innodb_io_capacity             = 2000
innodb_io_capacity_max         = 4000
innodb_file_per_table          = 1

# Durability: keep these at their safe values unless you accept losing up to one second of commits
innodb_flush_log_at_trx_commit = 1
sync_binlog                    = 1

# Connections and temporary tables
max_connections                = 200
thread_cache_size              = 64
table_open_cache               = 4000
tmp_table_size                 = 64M
max_heap_table_size            = 64M

# Name lookups: connect faster without reverse DNS
skip_name_resolve              = 1

# Slow query log, for finding the queries that matter
slow_query_log                 = 1
slow_query_log_file            = /var/log/mysql/mariadb-slow.log
long_query_time                = 1
EOF
sudo mkdir -p /var/log/mysql && sudo chown mysql:mysql /var/log/mysql

MySQL 8.0.30 and later use innodb_redo_log_capacity instead of innodb_log_file_size. Check the names your version accepts with sudo mysqld --help --verbose | grep innodb_redo. The instances setting only takes effect when the buffer pool is 1 GB or larger, which this server exceeds.

What each group does#

• Memory: the pool holds hot data. Splitting it into instances reduces lock contention when many queries run at once. The redo log size controls how much write activity can be buffered before a checkpoint. A larger redo log makes writes smoother, but a restart takes longer.
• Disk I/O: O_DIRECT stops the operating system caching the same data twice. The I/O capacity figures tell InnoDB how much the disk can handle; set them from a benchmark of your own disk, not from these examples, if your storage is slower.
• Durability: innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 make every committed transaction survive a crash. Setting the first to 2 is faster on write-heavy workloads, but a power failure can then lose up to a second of committed writes. Make that choice deliberately, never by accident.
• Connections: each connection can use memory for sorting and joins, so a high max_connections can use more memory than the machine has. Cap it, and handle the rest in the application with a connection pool.
• Name lookups: without skip_name_resolve, every new connection waits for a reverse DNS lookup. Turning it off is safe as long as your grants use IP addresses or localhost, not host names.

Step 4: Apply the changes safely#

Some settings, the redo log size in particular, require a restart. Take a backup first, then restart at a quiet time:

sudo mariadb-dump --all-databases --single-transaction --routines --events | gzip > /root/pre-tuning-$(date +%F).sql.gz
sudo systemctl restart mariadb
sudo systemctl status mariadb --no-pager | head -5
sudo tail -n 20 /var/log/mysql/error.log

The error log must show no warnings about an unknown variable, and the service must report active (running). Then confirm that the new buffer pool size took effect:

sudo mariadb -e "SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS buffer_pool_gb, @@max_connections, @@skip_name_resolve;"

If the restart fails, read the error log’s last line. The most common cause is a size the machine cannot allocate, which means the buffer pool is set too large for the RAM actually free. Lower it and restart again.

Step 5: Find the queries that cost the most#

Tuning the server helps every query a little. Fixing one bad query can help the whole site a lot. The slow query log from step 3 records every query slower than one second:

sudo tail -n 200 /var/log/mysql/mariadb-slow.log | grep -E "Query_time|SELECT|UPDATE|INSERT" | head -40
sudo mariadb-dumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log

mariadb-dumpslow (called mysqldumpslow on some releases) groups similar queries and sorts them by total time, so the query that costs most in aggregate appears first. That ordering matters: a query that takes 200 milliseconds but runs 50,000 times a day is a bigger problem than one slow report that runs once.

Check a query’s plan#

Copy the slow query, put EXPLAIN in front of it, and run it:

sudo mariadb shop_wp -e "EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key = '_price' AND meta_value > 100\G"

In the output, the type column should read ref, range or const. A value of ALL means the query reads every row of the table. The key column shows which index, if any, the query uses; NULL means none.

Add the index it needs#

If the plan shows a full scan on a filtered column, an index usually fixes it. Build it online, so the site keeps working:

sudo mariadb shop_wp -e "CREATE INDEX idx_meta_key_value ON wp_postmeta (meta_key, meta_value(20));"

Run EXPLAIN again to confirm the new index is used. Indexes make reads faster and writes a little slower, and they take disk space, so add one for a query that matters, not one for every column. WordPress’s own tables already have the indexes its core queries need; the gaps usually come from plugins that filter on columns WordPress never indexes.

Step 6: Check connections and memory#

sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; SHOW GLOBAL STATUS LIKE 'Threads_created';"

Max_used_connections should stay well below max_connections. If it reaches the limit, the application is opening more connections than it closes; fix that before raising the number. Threads_created climbing steadily means the thread cache is too small for the connection rate; raise thread_cache_size until the number stops growing.

Check the whole picture with the operating system too, because MariaDB shares the machine with everything else:

free -h
sudo journalctl -k --since "1 day ago" | grep -i "out of memory" || echo "no OOM kills"

An out-of-memory kill of mariadbd means the total memory settings are too high for the machine. Lower the buffer pool first; it is the largest allocation.

Step 7: Keep it tuned#

• Re-measure weekly for a month, then monthly. Data grows, and the buffer pool that was right in January may be too small by summer.
• Keep the slow query log on, with the one-second threshold. It costs little and is the best evidence you have when something slows down.
• Rotate the logs. Ubuntu’s MariaDB package rotates its error log; add the slow log to the same rotation, or it will grow without limit.
• Keep backups tested. Tuning changes are easy to undo, but a restore is the only protection against data loss; see how to back up MariaDB and PostgreSQL on a VPS.

Common mistakes to avoid#

• Setting the buffer pool to 90 per cent of RAM. The operating system and the connections then run out of memory, and the database is killed.
• Copying a configuration from the internet unchanged. Every value depends on RAM, disk and workload. Use the examples as a starting point and measure.
• Raising max_connections to hide a connection leak. The leak then costs more memory, not less.
• Turning durability settings down for speed without deciding to. A crash can then lose committed orders.
• Adding indexes for every slow query. Each index slows every write. Fix the query, then add the index that serves it.
• Tuning without a backup. Take one before the first change, as in step 4.

Frequently asked questions#

How do I know the buffer pool is big enough?#

When the hit ratio from step 1 stays above 99.5 per cent under normal load, and the pool holds most of your data, it is big enough. A pool larger than your data gives no further benefit.

Should I use MariaDB or MySQL?#

Either works well for a WordPress, Laravel or general application database. Choose the one your application and its packages are tested against, and tune it with the same principles.

Does tuning help a site that is already fast?#

Rarely, and it is not worth the risk. Tune when the measurements show a problem: slow pages, a falling hit ratio, connection limits reached, or queries in the slow log.

Can I tune MariaDB on shared hosting?#

No. Shared plans do not give you access to the server’s configuration. On cPanel, the database server is set up by the host; ask support if you need a change.

What about the query cache?#

MariaDB still has a query cache setting, but it is usually disabled or harmful on busy servers because it locks around every write. Leave it off unless your own measurements show a benefit.

The short version: measure the hit ratio and data size, give the buffer pool most of the RAM, match the I/O settings to the disk, cap connections, keep durability on, and fix the slow queries with indexes. Re-measure after each change, and keep a backup before you start.