MariaDB MySQL Performance Tuning: Buffer Pool, Slow Log, Indexes
MariaDB and MySQL performance tuning comes down to four things: give InnoDB enough memory with innodb_buffer_pool_size, find slow queries with the slow query log, fix them with the right indexes, and keep port 3306 away from the public internet. This guide walks through a clean install on Linux and then covers each of those four steps with commands you can run today.
The examples assume a single Linux server (Ubuntu/Debian or AlmaLinux/Rocky) with root access. Run them in a test environment first, and take a backup before changing anything on production.
Step 1: Install MariaDB or MySQL
Pick one engine. They share most syntax and config options, but not everything, and the differences are noted below.
Ubuntu / Debian (MariaDB):
sudo apt update
sudo apt install mariadb-server
sudo mariadb-secure-installation
Ubuntu / Debian (MySQL):
sudo apt install mysql-server
sudo mysql_secure_installation
AlmaLinux / Rocky (MariaDB):
sudo dnf install mariadb-server
sudo systemctl enable --now mariadb
sudo mariadb-secure-installation
The secure-installation script removes anonymous users, drops the test database, and disables remote root login. Say yes to all of those.
Config file locations differ by distro:
| Distro / engine | Typical config path |
|---|---|
| Debian/Ubuntu MariaDB | /etc/mysql/mariadb.conf.d/50-server.cnf |
| Debian/Ubuntu MySQL | /etc/mysql/mysql.conf.d/mysqld.cnf |
| RHEL-family | /etc/my.cnf.d/ |
A cleaner habit: put your changes in a separate file, for example /etc/mysql/conf.d/99-tuning.cnf (or /etc/my.cnf.d/99-tuning.cnf), so package upgrades never overwrite them. Use the [mysqld] section.
Step 2: Size the InnoDB buffer pool
innodb_buffer_pool_size is the single most important setting. InnoDB caches data and index pages there. If the working set fits in memory, most reads never touch disk.
Rules of thumb:
- Dedicated database server: 50-70% of RAM.
- Shared with a web app on the same host: 25-40%, and leave room for PHP-FPM or your app workers.
- Remember that each connection also uses per-thread buffers (sort, join, read buffers), so a huge
max_connectionsmultiplies memory use.
Example for a 16 GB dedicated database host:
[mysqld]
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
max_connections = 200
skip_name_resolve = ON
Notes on each line:
innodb_buffer_pool_instancesreduces mutex contention on large pools. On recent versions it only matters for pools of several GB or more.O_DIRECTavoids double caching in the OS page cache. It is a good default on Linux with local disks.innodb_log_file_sizecontrols redo log size. Too small causes frequent checkpoint stalls under write load. On MySQL 8.0.30 and later, useinnodb_redo_log_capacityinstead.skip_name_resolvestops reverse DNS lookups on every connection. If you use it, grants must use IP addresses, not hostnames.- Keep
innodb_flush_log_at_trx_commit = 1(the default) unless you accept losing up to about a second of transactions on a crash.
Both engines let you resize the buffer pool online, so you can test without a restart:
SET GLOBAL innodb_buffer_pool_size = 10 * 1024 * 1024 * 1024;
Check whether it is big enough
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
Compare Innodb_buffer_pool_reads (reads that had to go to disk) with Innodb_buffer_pool_read_requests (logical reads). If disk reads are well under 1% of requests after the server has been warm for a while, the pool is fine. A high ratio on a busy server suggests more memory or better indexes. Also watch for swapping with vmstat 1. If the server swaps, the buffer pool is too large for the machine.
Step 3: Turn on the slow query log
Do not tune by guessing. Let the server tell you which queries hurt.
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
Create the directory and set ownership if needed:
sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
You can also enable it live, without a restart:
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
Start with one second, then lower it to 0.5 or 0.2 once the worst offenders are fixed. Setting it to 0 logs everything. That is useful for a short sampling window, but it grows the log fast and adds I/O.
Be careful with log_queries_not_using_indexes. On a busy system it floods the log with harmless small-table scans. If you enable it, pair it with log_throttle_queries_not_using_indexes (MySQL) or filter results afterward.
Read the log
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
This shows the top 10 query patterns by total time. For deeper analysis, Percona Toolkit's pt-query-digest groups queries by fingerprint and ranks them by total time, which usually matters more than the single slowest execution. A query that runs 40 ms but 500,000 times an hour is a bigger problem than one 3-second report.
Rotate the log with logrotate so it does not fill the disk.
Step 4: Fix queries with indexes
Take a slow query from the log and run EXPLAIN:
EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 4821 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;
Look at these columns:
type:ALLmeans a full table scan. You wantref,range, orconst.key: the index actually used.NULLmeans none.rows: the estimated rows examined. Compare it with the rows returned.Extra:Using filesortorUsing temporaryon large sets is a warning sign.
For the query above, a composite index fits the access pattern:
ALTER TABLE orders
ADD INDEX idx_cust_status_created (customer_id, status, created_at);
Equality columns go first, then the sort column. That lets the engine filter and return rows already ordered.
For real timings rather than estimates, use EXPLAIN ANALYZE (MySQL 8.0.18+) or ANALYZE FORMAT=JSON (MariaDB).
Index rules that prevent most problems
- Leftmost prefix: an index on
(a, b, c)helps queries filtering ona, ora, b, but not onbalone. - Do not wrap indexed columns in functions.
WHERE DATE(created_at) = '2025-01-10'cannot use an index oncreated_at. Use a range:created_at >= '2025-01-10' AND created_at < '2025-01-11'. - Match types. Comparing a string column to a number forces implicit conversion and can disable the index.
- Avoid
SELECT *when a covering index could answer the query from the index alone. - Do not index everything. Every index slows writes and uses buffer pool memory. Remove unused ones. MySQL's
sys.schema_unused_indexesview helps find them, but check after a long enough uptime.
On large tables, run ALTER TABLE during low traffic. Both engines support online DDL for many index operations, but test first and watch replication lag if you have replicas.
Step 5: Secure remote access
By default, MariaDB and MySQL on most distros listen only on localhost. Keep it that way unless you have a real need for remote connections. If your app and database are on separate servers, use these layers together.
1. Bind to a private or specific address
[mysqld]
bind-address = 192.0.2.10
Use the private interface if you have one. Avoid 0.0.0.0 unless a firewall restricts it.
2. Firewall: allow only known clients
With nftables or ufw, allow 3306 from the app server only:
sudo ufw allow from 203.0.113.10 to any port 3306 proto tcp
sudo ufw deny 3306/tcp
Order matters. Make sure the allow rule is evaluated before the deny. Check with sudo ufw status numbered. Then confirm from outside: nmap -p 3306 192.0.2.10 from an unauthorized host should show filtered or closed.
3. Create restricted accounts
CREATE USER 'appuser'@'203.0.113.10' IDENTIFIED BY 'use-a-long-random-password' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'appuser'@'203.0.113.10';
FLUSH PRIVILEGES;
Never create 'root'@'%'. Do not grant ALL PRIVILEGES ON *.* to application users. For admin work, use an SSH tunnel:
ssh -L 3307:127.0.0.1:3306 admin@192.0.2.10
mysql -h 127.0.0.1 -P 3307 -u admin -p
A VPN such as WireGuard is another good option for teams, because the database never needs a public listener at all.
4. Encrypt the connection
If traffic crosses any untrusted network, enable TLS in the server config:
[mysqld]
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key = /etc/mysql/ssl/server-key.pem
ssl_ca = /etc/mysql/ssl/ca.pem
Restart the service, then verify with SHOW SESSION STATUS LIKE 'Ssl_cipher'; from a client session. An empty value means the session is not encrypted.
5. Watch for brute force
Public 3306 gets scanned constantly. Set max_connect_errors sensibly, log failed logins, and ban repeat offenders at the firewall. Better still, do not expose the port.
Common mistakes and fixes
| Mistake | Fix |
|---|---|
| Buffer pool set to 90% of RAM, server swaps | Lower to 50-70% and check vmstat |
bind-address = 0.0.0.0 with no firewall |
Bind to a private IP and allow only specific sources |
| Config edit does nothing | Check which file is loaded: mysqld --help --verbose \| grep -A1 "Default options" |
| Slow log empty | Check file permissions and that long_query_time was set globally, since a session value does not affect new connections |
| Index exists but is not used | Look for functions on the column, type mismatch, or a wrong column order |
Grants fail after skip_name_resolve |
Recreate users with IP addresses instead of hostnames |
Where to run it
Database tuning depends on predictable RAM and disk I/O. On a BGP-enabled VDS from Nixcon Group LLC you get dedicated resources for a small or mid-size database, and you can keep it on a private path behind your app server. For larger datasets that need local NVMe or all of a host's memory, a dedicated server is the better fit. Either way, a firewall in front of 3306 is the first line of defense, so set that up before you open any remote access.
Quick checklist
- Install, run the secure-installation script, put changes in a separate
.cnffile. - Set
innodb_buffer_pool_sizefor your RAM and check the read ratio. - Enable the slow log at 1 second and review it weekly.
- Use
EXPLAINand composite indexes for the top offenders. - Bind to a private address, firewall 3306, use host-restricted users and TLS.
- Test restores of your backups regularly.
Frequently asked questions
How much RAM should I give innodb_buffer_pool_size?
On a dedicated database server, 50-70% of RAM is a common starting point. If the server also runs a web app, use 25-40%. Verify with the Innodb_buffer_pool_reads and read_requests counters, and make sure the server is not swapping.
Should I enable log_queries_not_using_indexes?
Only for short investigations. On busy systems it logs many harmless queries on small tables and hides real problems. Start with long_query_time = 1 and review results with mysqldumpslow or pt-query-digest.
Is it safe to expose MySQL or MariaDB port 3306 to the internet?
It is best avoided. Bind to a private IP, allow only known client addresses in the firewall, use host-restricted users with TLS, or reach the database through an SSH tunnel or VPN.
Should I choose MySQL or MariaDB?
Both work well for most web workloads and share much of their syntax and tuning options. Check your application's supported versions first, since some features and config names differ, such as the redo log setting in newer MySQL releases.