MySQL/MariaDB on AlmaLinux series, part 6. Part 1 installed MariaDB, part 2 covered users and remote access, part 3 covered backups, part 4 built GTID replication and part 5, MariaDB Galera Cluster, turned db1, db2 and db3 into a three-node cluster. This part makes that database fast: memory, redo log, connections, and finding the queries that actually hurt.
A stock MariaDB install is tuned for a small machine: a 128 MiB InnoDB buffer pool, a 96 MiB redo log and a slow query log that is switched off. On a real server that means most reads go to disk and nobody knows which query is responsible. This guide sizes the InnoDB buffer pool and redo log, sets sensible flush, connection and temporary table settings, turns on the slow query log with log rotation, analyzes it with mariadb-dumpslow and pt-query-digest, and fixes a slow query with EXPLAIN, ANALYZE and an index. Every setting below was checked against the MariaDB documentation and a live MariaDB 10.11.18 server.
Applies to: AlmaLinux 10.x with MariaDB 10.11 from AppStream (mariadb-server, currently 10.11.18). The same steps work on RHEL 10, Rocky Linux 10 and Oracle Linux 10, and on the mariadb11.8-server stream with the few differences called out below. A short MySQL 8.4 section is at the end.
The tuning workflow
- Measure first. Record a baseline of status counters and the slowest queries before changing anything.
- Fix memory. The buffer pool is the single biggest win on an InnoDB server.
- Find the bad queries. A missing index costs more than any server variable can give back.
- Change one thing at a time, keep every change in one drop-in file, and compare against the baseline.
Settings go in /etc/my.cnf.d/zz-tuning.cnf, following the zz-*.cnf drop-in pattern used throughout the series. Files in /etc/my.cnf.d/ are read in alphabetical order and the last value wins, so zz-tuning.cnf is read after zz-galera.cnf and zz-local.cnf.
Lab layout
| Host | IP | Role |
|---|---|---|
db1.example.lan | 192.168.1.40 | Galera node (part 5), receives all writes through HAProxy |
db2.example.lan | 192.168.1.41 | Galera node |
db3.example.lan | 192.168.1.42 | Galera node |
Each node has 4 vCPUs, 16 GiB of RAM and SSD storage. If you skipped part 5 and run a single server, ignore the Galera notes; everything else is identical.
Prerequisites
- MariaDB installed and secured as in part 1; root logs in over the Unix socket with
sudo mariadb. - Enough uptime under normal load for the status counters to mean something. Counters reset on restart, so record values after at least a day of real traffic where possible.
- A recent backup (part 3). Tuning is low risk, but a typo in a
.cnffile stops the server from starting. - For a Galera cluster: all three nodes
Synced, and a plan to restart them one at a time.
Step 1: Record a baseline
Check the version and the current values of the settings this guide touches:
sudo mariadb -e "SELECT VERSION();"
sudo mariadb -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN (
'innodb_buffer_pool_size','innodb_buffer_pool_size_max','innodb_log_file_size',
'innodb_log_buffer_size','innodb_flush_log_at_trx_commit','innodb_flush_method',
'innodb_io_capacity','max_connections','thread_cache_size','tmp_table_size',
'max_heap_table_size','table_open_cache','slow_query_log','long_query_time');"On a fresh AlmaLinux 10 install you will see the upstream defaults:
| Variable | Default (10.11) | Notes |
|---|---|---|
innodb_buffer_pool_size | 128M | Far too small for a dedicated database server |
innodb_buffer_pool_size_max | 8T (virtual address reservation) | From 10.11.17; ceiling for growing the pool at runtime |
innodb_log_file_size | 96M | Single redo log file ib_logfile0 |
innodb_log_buffer_size | 16M | |
innodb_flush_log_at_trx_commit | 1 | Fully durable |
innodb_flush_method | O_DIRECT | Default since 10.6; leave it |
innodb_io_capacity / _max | 200 / 2000 | Background flushing budget in IOPS |
max_connections | 151 | |
thread_cache_size | 256, capped at max_connections | Shows 151 on a default install |
tmp_table_size / max_heap_table_size | 16M / 16M | The smaller of the two applies |
table_open_cache | 2000 | |
slow_query_log / long_query_time | OFF / 10 | Ten seconds catches almost nothing |
Save a snapshot of all status counters so you can compare later:
sudo mkdir -p /root/dbtuning
sudo mariadb -e "SHOW GLOBAL STATUS" | sudo tee /root/dbtuning/status-$(date +%F-%H%M).txt >/dev/null
sudo mariadb -e "SHOW GLOBAL VARIABLES" | sudo tee /root/dbtuning/variables-$(date +%F-%H%M).txt >/dev/nullFinally, see how much InnoDB data you have. The buffer pool does not need to hold all of it, only the part that is read often (the working set), but the total is a useful upper bound:
sudo mariadb -e "
SELECT table_schema,
ROUND(SUM(data_length)/1024/1024) AS data_mb,
ROUND(SUM(index_length)/1024/1024) AS index_mb
FROM information_schema.tables
WHERE engine = 'InnoDB'
GROUP BY table_schema WITH ROLLUP;"
free -gStep 2: Size the InnoDB buffer pool
The buffer pool caches table and index pages in memory. When the working set fits, reads are served from RAM and the disk only sees writes. MariaDB’s documentation suggests up to 80% of RAM on a server that runs only InnoDB, but that assumes nothing else needs memory. Leave room for:
- the operating system and page cache for logs and binlogs (at least 1 to 2 GiB);
- per-connection buffers (sort, join, read buffers and temporary tables) multiplied by your real peak connection count;
- on Galera, the gcache ring buffer (
gcache.size=1Gin part 5) and the memory used bymariabackupduring a state transfer; - anything else on the host: monitoring agents, backups compressing with zstd, and so on.
| RAM | Dedicated DB server | Shared with an application |
|---|---|---|
| 4 GiB | 2G | 1G |
| 8 GiB | 5G | 3G |
| 16 GiB | 10G to 11G | 6G |
| 32 GiB | 22G to 24G | 14G |
| 64 GiB | 48G to 50G | 30G |
For the 16 GiB lab nodes, 10G is a safe start. If the data set is smaller than that, there is no need to go bigger: a 3 GB database is fully cached by a 4G pool.
Grow the buffer pool without a restart
innodb_buffer_pool_size is dynamic. On MariaDB 10.11.17 and later (AlmaLinux 10 ships 10.11.18), innodb_buffer_pool_size_max defaults to an 8 TiB virtual address reservation, so you can grow the pool online:
sudo mariadb -e "SET GLOBAL innodb_buffer_pool_size = 10*1024*1024*1024;"
sudo mariadb -e "SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS buffer_pool_gb;"The statement blocks until the resize is finished. Two version quirks to know about:
- On 10.11.12 to 10.11.16,
innodb_buffer_pool_size_maxdefaulted to the startup size, so growing at runtime failed with warning 1292. On those versions set the value in the config file and restart, or setinnodb_buffer_pool_size_maxat startup. - From 10.11.12,
innodb_buffer_pool_chunk_sizeis deprecated and ignored; the pool is resized in 1 MiB steps. Remove it from old config files.
Do not set innodb_buffer_pool_instances
Old tuning guides tell you to split the pool into several instances. MariaDB 10.5 went back to a single buffer pool, and 10.6 removed innodb_buffer_pool_instances completely. A leftover line does not stop the server, but it logs this at every start and does nothing:
[Warning] 'innodb-buffer-pool-instances' was removed. It does nothing now and exists only for compatibility with old my.cnf files.The same applies to innodb_log_files_in_group, innodb_page_cleaners and innodb_thread_concurrency, which were all removed in 10.6. Find leftovers with:
sudo grep -rnE 'buffer_pool_instances|buffer-pool-instances|log_files_in_group|page_cleaners|thread_concurrency|chunk_size|query_cache_size' /etc/my.cnf /etc/my.cnf.d/
sudo journalctl -u mariadb -b | grep -i 'was removed\|deprecated'Keep the cache warm across restarts
innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup are both ON by default: InnoDB saves the list of cached pages at shutdown and reloads them after a restart, so you do not start cold. Watch the reload with:
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_load_status';"Step 3: Redo log size and flush settings
innodb_log_file_size
The redo log (ib_logfile0) records every change before the data pages are written. When it fills, InnoDB has to flush dirty pages aggressively to make room, which shows up as write stalls. The 96M default is small for any busy server. A common sizing goal is to hold roughly an hour of peak redo writes; 1G to 2G suits most servers. Measure how much redo you write per minute during peak load:
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written'"; sleep 60; \
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written'"Subtract the two values to get bytes per minute, then multiply by 60. Since MariaDB 10.9 the redo log can be resized online, and the server builds the new file in the background:
sudo mariadb -e "SET GLOBAL innodb_log_file_size = 1024*1024*1024;"
sudo mariadb -e "SELECT @@innodb_log_file_size/1024/1024 AS redo_mb;"Also put the value in the config file (step 5) so it survives a restart. Never delete ib_logfile0 to resize it; since 10.8 the server refuses to start without it. A larger redo log means less flushing pressure but a longer crash recovery.
To check whether the log is under pressure, compare Innodb_checkpoint_age with Innodb_checkpoint_max_age. If the age regularly gets close to the maximum, or Innodb_log_waits keeps rising (writes waiting on a full innodb_log_buffer_size), make the log or the log buffer bigger.
sudo mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN
('Innodb_checkpoint_age','Innodb_checkpoint_max_age','Innodb_log_waits');"innodb_flush_log_at_trx_commit
| Value | On COMMIT | Can lose on crash |
|---|---|---|
1 (default) | Write and fsync the redo log | Nothing; fully ACID |
2 | Write to the OS; fsync about once a second | Up to ~1 s of commits on an OS crash or power loss (not on a mariadbd crash) |
0 | Nothing; write and fsync once a second | Up to ~1 s of commits even if only mariadbd crashes |
Keep 1 on a standalone server or an async primary, together with sync_binlog=1 from part 4. On a Galera cluster, 2 is a common and reasonable choice: a committed transaction has already been replicated to the other nodes, so a single node losing its last second is repaired by IST when it rejoins. The risk is the whole cluster losing power at the same moment. Pick deliberately; do not copy 0 from the commented example in mariadb-server.cnf.
innodb_flush_method and innodb_io_capacity
Leave innodb_flush_method at its O_DIRECT default, which avoids double caching in the Linux page cache. On the MariaDB 11.8 stream it is deprecated in favour of four dynamic switches (innodb_log_file_buffering, innodb_data_file_buffering, innodb_log_file_write_through, innodb_data_file_write_through); the defaults are right for most servers there too.
innodb_io_capacity tells InnoDB how many IOPS background flushing may use. The default of 200 suits a single spinning disk. For SSD or NVMe start around 1000 and keep innodb_io_capacity_max at its automatic value (2000 or twice innodb_io_capacity, whichever is higher). MariaDB recommends staying on the low side, because a too-high value flushes pages before they can be modified again. If you do not know what the storage can do, measure it first; see finding hard drive IOPS on Linux.
Step 4: Connections, threads, temporary tables and caches
max_connections and the thread cache
sudo mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN
('Max_used_connections','Threads_connected','Threads_running',
'Threads_created','Connections','Aborted_connects','Aborted_clients');"- If
Max_used_connectionsis close tomax_connections, clients are about to getERROR 1040 (HY000): Too many connections. Raise it, but first ask why: a connection leak or a missing connection pool in the application is the usual cause. MariaDB always keeps one extra slot for an account withCONNECTION ADMIN, so you can still log in to investigate. Threads_runningis what actually burns CPU. Hundreds of open connections with 5 running are fine; 60 running on 4 vCPUs means queries are queueing.Threads_created / Connectionsis the thread cache miss rate. With the defaultthread_cache_sizeit is usually near zero; raisethread_cache_sizeonly if it is not.- Rising
Aborted_connectsmeans failed logins or network problems (see part 2); risingAborted_clientsusually means applications that drop connections without closing them.
Every connection can allocate session buffers: sort_buffer_size (2M), join_buffer_size (256K), read buffers and in-memory temporary tables. Leave these per-session buffers at their defaults; raising them globally multiplies by every connection and is a classic way to run a server out of memory. Raise them for a single session (SET SESSION sort_buffer_size = 16*1024*1024;) before a known heavy report instead.
For workloads with thousands of mostly idle connections, MariaDB also has a built-in thread pool (thread_handling = pool-of-threads). Test it under load before switching; for typical web applications with a connection pool the default one-thread-per-connection is fine.
Temporary tables
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';"Created_tmp_disk_tables counts internal temporary tables (from GROUP BY, DISTINCT, UNION, derived tables) that grew past the in-memory limit and were converted to on-disk Aria tables. If more than a few percent of Created_tmp_tables go to disk, raise both limits together, because the smaller of the two applies:
tmp_table_size = 64M
max_heap_table_size = 64MDo not go wild: each running query can create its own temporary table up to this size. Queries that sort or group millions of rows are better fixed with an index (step 9). Note that tables with TEXT or BLOB columns in the result may go straight to disk regardless of the size limits.
Table cache and open files
sudo mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Open_tables','Opened_tables','Opened_table_definitions');
SHOW GLOBAL VARIABLES WHERE Variable_name IN ('table_open_cache','table_definition_cache','open_files_limit');"If Opened_tables keeps climbing quickly while Open_tables sits at table_open_cache, the cache is too small for the number of tables in use. The default 2000 suits most servers; raise it to 4000 or more for applications with thousands of tables, and raise table_definition_cache (default 400) to cover the total number of tables. open_files_limit is sized automatically; if the error log warns that it could not raise it, add a systemd override:
sudo systemctl edit mariadb
# add:
[Service]
LimitNOFILE=65535
sudo systemctl daemon-reloadLeave the query cache off
The query cache is disabled by default (query_cache_type=OFF). It serializes on a global lock, is invalidated by every write to a table, and is not a win on multi-core servers. Do not set query_cache_size to a non-zero value: that silently switches the cache back on.
Step 5: Write zz-tuning.cnf
Collect the decisions in one file. This example is for a 16 GiB dedicated node with SSD storage; adjust the sizes for your hardware:
sudo tee /etc/my.cnf.d/zz-tuning.cnf >/dev/null <<'EOF'
# Performance tuning - MySQL/MariaDB series part 6
[mariadb]
# --- InnoDB memory and redo log ---
innodb_buffer_pool_size = 10G
innodb_log_file_size = 1G
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 1000
# innodb_flush_method: leave at the O_DIRECT default
# innodb_buffer_pool_instances: removed in 10.6, do not set
# --- connections ---
max_connections = 300
# --- temporary tables and caches ---
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 4000
table_definition_cache = 2000
# --- slow query log (step 6) ---
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/mariadb-slow.log
long_query_time = 1
log_slow_verbosity = query_plan
log_queries_not_using_indexes = 0
EOF
sudo chown root:mysql /etc/my.cnf.d/zz-tuning.cnf
sudo chmod 640 /etc/my.cnf.d/zz-tuning.cnfCheck what the server will actually read before you restart, including any duplicates in other files (the last one listed wins):
sudo my_print_defaults --mysqld | grep -E 'buffer_pool|log_file_size|flush_log|io_capacity|max_connections|tmp_table|heap_table|table_open|slow|long_query'Apply the settings. On a standalone server just restart. On the Galera cluster, copy the file to all three nodes and restart one node at a time, waiting for it to return to Synced before moving on. Start with db3 and finish with db1, the HAProxy writer:
# on db3, then db2, then db1
sudo systemctl restart mariadb
sudo mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN
('wsrep_local_state_comment','wsrep_cluster_size','wsrep_ready');"
# continue only when: Synced, 3, ONA short restart rejoins through IST from the gcache, so it takes seconds rather than a full SST. A buffer pool this large adds a few seconds to startup and to the page dump at shutdown; the unit file allows 300 seconds.
Step 6: The slow query log
The settings above already enable the log at startup. To turn it on immediately without waiting for a restart, set the same values at runtime:
sudo mariadb -e "
SET GLOBAL slow_query_log_file = '/var/log/mariadb/mariadb-slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_slow_verbosity = 'query_plan';
SET GLOBAL slow_query_log = 1;"/var/log/mariadb is owned by mysql and already carries the mysqld_log_t SELinux type, so the server can create the file there without any policy changes. A new long_query_time set with SET GLOBAL only applies to connections opened afterwards; existing sessions keep their old value.
| Setting | What it does |
|---|---|
slow_query_log | Turns the log on. Called log_slow_query from 10.11; both names work. |
slow_query_log_file | Log path. Default is <hostname>-slow.log in the datadir. Alias log_slow_query_file. |
long_query_time | Threshold in seconds; fractions allowed (0.5). Default 10. Alias log_slow_query_time. |
log_slow_verbosity | Extra detail: query_plan (full scans, temporary tables, filesorts), explain (EXPLAIN output per query), engine/innodb, warnings, all. |
log_queries_not_using_indexes | Also log queries that do no index lookup, regardless of time. Noisy; enable for short periods. |
min_examined_row_limit | Skip queries that examine fewer rows than this. Alias log_slow_min_examined_row_limit. |
log_slow_rate_limit | Log only one in N qualifying queries, to keep the log small on busy servers. |
log_slow_admin_statements | Log slow ALTER, OPTIMIZE, ANALYZE and similar. ON by default. |
Start with 1 second, look at what you catch, and lower it to 0.5 or 0.2 once the worst offenders are fixed. To hunt for missing indexes, enable log_queries_not_using_indexes together with min_examined_row_limit = 1000 for an hour, then turn it off again.
Test it with a deliberately slow statement:
sudo mariadb -e "SELECT SLEEP(2);"
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"
sudo tail -n 8 /var/log/mariadb/mariadb-slow.logA logged query with log_slow_verbosity = query_plan looks like this:
# Time: 261114 3:14:03
# User@Host: shop_app[shop_app] @ app1.example.lan [192.168.1.50]
# Thread_id: 7 Schema: shop QC_hit: No
# Query_time: 0.068686 Lock_time: 0.000012 Rows_sent: 20 Rows_examined: 500020
# Rows_affected: 0 Bytes_sent: 936
# Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: Yes
SET timestamp=1794647643;
SELECT id, total, created_at FROM orders WHERE customer_id = 1234 ORDER BY created_at DESC LIMIT 20;Rows_examined: 500020 for Rows_sent: 20 and Full_scan: Yes is the signature of a missing index. That query is fixed in step 9.
Rotate the slow log
The mariadb-server package ships /etc/logrotate.d/mariadb for the error log only. Add a separate file for the slow log rather than editing the packaged one, so updates do not leave an .rpmnew behind:
sudo tee /etc/logrotate.d/mariadb-slow >/dev/null <<'EOF'
/var/log/mariadb/mariadb-slow.log {
su mysql mysql
missingok
notifempty
daily
rotate 14
compress
delaycompress
create 0640 mysql mysql
postrotate
/usr/bin/mariadb-admin --local flush-slow-log
endscript
}
EOF
sudo logrotate -d /etc/logrotate.d/mariadb-slow
sudo logrotate -f /etc/logrotate.d/mariadb-slow
ls -l /var/log/mariadb/flush-slow-log makes the server close and reopen the file, and --local keeps the FLUSH out of the binary log (and therefore out of Galera replication). Like the packaged error-log rule, this relies on the mysql@localhost account that mariadb-install-db creates with Unix socket authentication. If the flush fails, test it by hand with sudo -u mysql mariadb-admin --local flush-slow-log.
Step 7: Summarize the log with mariadb-dumpslow
mariadb-dumpslow groups similar queries by replacing numbers with N and strings with 'S'. On AlmaLinux it is in the mariadb-server-utils package:
sudo dnf install -y mariadb-server-utils
# top 10 by total time
sudo mariadb-dumpslow -s t -t 10 /var/log/mariadb/mariadb-slow.log
# top 10 by rows examined, only queries touching orders
sudo mariadb-dumpslow -s e -t 10 -g orders /var/log/mariadb/mariadb-slow.logReading mysql slow query log from /var/log/mariadb/mariadb-slow.log
Count: 5 Time=0.09s (0s) Lock=0.00s (0s) Rows_sent=20.0 (100), Rows_examined=89262.2 (446311), Rows_affected=0.0 (0), shop_app[shop_app]@app1.example.lan
SELECT id, total, created_at FROM orders WHERE customer_id = N ORDER BY created_at DESC LIMIT NUseful sort orders are t (total time), at (average time, the default), c (count), e (rows examined) and l (lock time). mariadb-dumpslow is quick but simple: the extra comment lines written by log_slow_verbosity (temporary table sizes, EXPLAIN output) sometimes end up glued into the grouped query text, and the header line written when the server starts creates a junk entry. For anything more than a quick look, use pt-query-digest.
Step 8: Deeper analysis with pt-query-digest
pt-query-digest from Percona Toolkit ranks queries by total response time and prints percentiles, a time distribution and ready-to-run EXPLAIN statements for each one. It is not in EPEL 10; install it from Percona’s repository, which has EL10 packages:
sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release enable pt release
sudo dnf install -y percona-toolkit
pt-query-digest --versionAnalyze the whole log, or just a recent window:
sudo pt-query-digest --limit 10 /var/log/mariadb/mariadb-slow.log > /root/dbtuning/digest-$(date +%F).txt
sudo pt-query-digest --since 24h --limit 10 /var/log/mariadb/mariadb-slow.log | lessThe profile at the top is the part to read first:
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== =================================== ============= ===== ====== ====
# 1 0x89E39D9104C5B5C295D4FDB7C55E4C73 0.5696 81.7% 6 0.0949 0.00 SELECT orders
# 2 0xAF982714169E9936F63A12CF49529C46 0.1280 18.3% 2 0.0640 0.00 SELECT ordersFix queries in rank order. A 90 ms query that runs 50,000 times an hour matters far more than one 20-second monthly report. Each detailed section below the profile shows the Rows examine distribution, the databases, users and hosts involved, and an EXPLAIN line you can paste into the client.
If you prefer not to add another repository, MariaDB’s sys schema gives similar per-statement statistics from the Performance Schema, which is off by default. Enabling it needs performance_schema = ON in zz-tuning.cnf and a restart, and costs some memory and CPU:
sudo mariadb -e "SELECT query, exec_count, total_latency, rows_examined_avg
FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;"Step 9: Fix a slow query with EXPLAIN, ANALYZE and an index
Take the top query from the digest. The examples use a 500,000-row shop.orders table; to follow along on a test server, create it with the seq_1_to_500000 table from MariaDB’s built-in Sequence engine:
sudo mariadb shop -e "
CREATE TABLE IF NOT EXISTS orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
status VARCHAR(16) NOT NULL,
total DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL
) ENGINE=InnoDB;
INSERT INTO orders (customer_id, status, total, created_at)
SELECT FLOOR(1 + RAND()*5000),
ELT(1 + FLOOR(RAND()*3), 'new', 'paid', 'shipped'),
ROUND(RAND()*500, 2),
NOW() - INTERVAL FLOOR(RAND()*365*86400) SECOND
FROM seq_1_to_500000;
ANALYZE TABLE orders;"EXPLAIN: the plan the optimizer intends to use
sudo mariadb shop -e "EXPLAIN SELECT id, total, created_at FROM orders
WHERE customer_id = 1234 ORDER BY created_at DESC LIMIT 20;"+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 498839 | Using where; Using filesort |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+| Column | What to look for |
|---|---|
type | ALL = full table scan (bad on big tables). Better: index, range, ref, eq_ref, const. |
possible_keys / key | Which indexes could be used, and which one was. NULL means none. |
rows | Estimated rows to examine. Compare with how many you actually return. |
Extra | Using filesort and Using temporary mean extra sorting or temporary tables; Using index means the index alone answered the query (covering index). |
ANALYZE: run the query and show what really happened
MariaDB’s ANALYZE statement executes the query and adds measured columns next to the estimates (MySQL’s equivalent is EXPLAIN ANALYZE):
sudo mariadb shop -e "ANALYZE SELECT id, total, created_at FROM orders
WHERE customer_id = 1234 ORDER BY created_at DESC LIMIT 20;"+------+-------------+--------+------+---------------+------+---------+------+--------+--------+----------+------------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+--------+----------+------------+-----------------------------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 498839 | 20.00 | 100.00 | 100.00 | Using where; Using filesort |
+------+-------------+--------+------+---------------+------+---------+------+--------+--------+----------+------------+-----------------------------+r_rows and r_filtered are real measurements; a big gap between rows and r_rows means stale statistics (fix with ANALYZE TABLE orders;). ANALYZE FORMAT=JSON SELECT ... adds per-step timings (r_total_time_ms) and is the best tool for complex joins. Because ANALYZE really runs the statement, do not point it at an UPDATE or DELETE on production data.
Add the right index
The query filters on customer_id and sorts by created_at, so a composite index on (customer_id, created_at) serves both the lookup and the order:
sudo mariadb shop -e "ALTER TABLE orders
ADD INDEX idx_customer_created (customer_id, created_at),
ALGORITHM=INPLACE, LOCK=NONE;"+------+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+
| 1 | SIMPLE | orders | ref | idx_customer_created | idx_customer_created | 4 | const | 115 | Using where |
+------+-------------+--------+------+----------------------+----------------------+---------+-------+------+-------------+From scanning 498,839 rows with a filesort to reading about 115 index entries with no sort at all. Rules of thumb for indexes:
- Put equality columns first, then the range or
ORDER BYcolumn:(customer_id, created_at), not the other way round. - A composite index also serves queries on its leftmost columns, so
(customer_id, created_at)makes a separate index oncustomer_idredundant. - Functions on indexed columns defeat the index:
WHERE DATE(created_at) = '2026-11-01'scans,WHERE created_at >= '2026-11-01' AND created_at < '2026-11-02'uses the index. The same goes for a leading wildcard inLIKE '%term'. - Every index slows down writes and uses buffer pool memory. Drop indexes nothing uses, and check sizes with the query below.
sudo mariadb -e "
SELECT table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 1) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'shop' AND stat_name = 'size'
ORDER BY size_mb DESC;"On Galera, schema changes are cluster-wide events. With the default wsrep_OSU_method=TOI (total order isolation), an ALTER TABLE runs on all nodes at the same point in the replication stream and blocks writes to that table on every node until it finishes, even though InnoDB would do it online on a standalone server. Small tables are no problem; for big ones schedule a quiet window, use pt-online-schema-change from Percona Toolkit, or do a rolling schema upgrade with wsrep_OSU_method=RSU one node at a time (only for changes that stay compatible with replication, such as adding an index).
Step 10: Monitor the important status counters
Buffer pool hit ratio
sudo mariadb -e "
SELECT ROUND(100 * (1 -
(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')
), 2) AS bp_hit_pct;
SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_pages_total','Innodb_buffer_pool_pages_free',
'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_wait_free');"Innodb_buffer_pool_read_requests are logical reads; Innodb_buffer_pool_reads are the ones that had to go to disk. The ratio since startup hides recent behaviour, so also look at the change over a minute of normal load. MariaDB’s guideline is that disk reads should be under 1% of read requests:
sudo mariadb-admin -r -i 60 -c 2 extended-status | grep -E 'Innodb_buffer_pool_read(s|_requests) 'The second block of output shows the increase during that minute. Below 99% under steady load, with Innodb_buffer_pool_pages_free near zero, means the working set does not fit: add memory to the pool or reduce what queries read. A non-zero, rising Innodb_buffer_pool_wait_free means queries waited for a free page: the pool is too small or flushing cannot keep up (check innodb_io_capacity).
A quick health snapshot
| Counter(s) | Healthy | Investigate when |
|---|---|---|
Innodb_buffer_pool_reads vs _read_requests | Under 1% over a minute | Above 1%, free pages near 0 |
Created_tmp_disk_tables vs Created_tmp_tables | A few percent | Large and rising: tune or index |
Threads_created vs Connections | Near 0 | Raise thread_cache_size |
Max_used_connections | Well under max_connections | Within 10 to 20% |
Slow_queries | Flat or slowly rising | Sudden jump after a deploy |
Select_full_join | 0 | Joins without indexes |
Sort_merge_passes | Low | Big sorts spilling to disk |
Innodb_row_lock_waits, Innodb_row_lock_time_avg | Low | Lock contention, long transactions |
Innodb_log_waits | 0 or flat | Increase innodb_log_buffer_size |
SHOW ENGINE INNODB STATUS\G gives the detailed view: the BUFFER POOL AND MEMORY section, pending reads and writes, and the latest deadlock.
mysqltuner as a second opinion
MySQLTuner is a Perl script that reads the same counters and prints suggestions. It is packaged in EPEL 10:
sudo dnf install -y epel-release
sudo dnf install -y mysqltuner
sudo mysqltunerTreat its output as hints, not instructions. It needs at least a day of uptime to be meaningful, it does not know your workload, and its generic advice (for example on per-session buffers) can conflict with the memory budget you worked out above.
Galera-specific considerations
- Keep the nodes alike. Galera runs at the speed of the slowest node: when a node cannot apply write-sets fast enough it triggers flow control and pauses replication for the whole cluster. Deploy the same
zz-tuning.cnfon all three nodes. - Watch flow control.
wsrep_flow_control_pausedis the fraction of time replication was paused since the lastFLUSH STATUS; near 0.0 is healthy, 0.2 or more needs attention.wsrep_local_recv_queue_avgrising on one node points at that node. - Applier threads.
wsrep_slave_threadsdefaults to 1. On multi-core nodes with a write-heavy workload, raising it (for example to 4, the number of vCPUs here) lets write-sets apply in parallel. If you see consistency errors, go back to 1. - Large transactions. A multi-million-row
UPDATEorDELETEbecomes one huge write-set that every node must certify and apply. Batch it (for example 5,000 rows per transaction) or use streaming replication (wsrep_trx_fragment_size) for the occasional big job. - The slow log is per node. Writes arrive on db1 through HAProxy, but reports pointed at db2 or db3 log there. Collect and digest the slow log from every node.
- Durability. See the
innodb_flush_log_at_trx_commitdiscussion in step 3.
sudo mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN
('wsrep_flow_control_paused','wsrep_local_recv_queue_avg','wsrep_local_send_queue_avg',
'wsrep_cert_deps_distance','wsrep_local_state_comment');"Verification checklist
# settings active after the restart
sudo mariadb -e "SELECT @@innodb_buffer_pool_size/1024/1024/1024 AS bp_gb,
@@innodb_log_file_size/1024/1024 AS redo_mb, @@innodb_io_capacity AS io_cap,
@@max_connections AS max_conn, @@tmp_table_size/1024/1024 AS tmp_mb,
@@slow_query_log AS slowlog, @@long_query_time AS lqt, @@slow_query_log_file AS slowfile;"
# no warnings about removed or unknown options
sudo journalctl -u mariadb -b | grep -iE 'warning|error' | tail -20
# the slow log is written and rotates
sudo mariadb -e "SELECT SLEEP(1.5);" && sudo tail -n 3 /var/log/mariadb/mariadb-slow.log
sudo logrotate -d /etc/logrotate.d/mariadb-slow
# memory headroom: no swapping
free -h; vmstat 5 3Then compare a fresh status snapshot with the baseline from step 1 after a day of normal traffic: lower Innodb_buffer_pool_reads per hour, fewer Created_tmp_disk_tables and a shorter slow log.
Troubleshooting
| Symptom | Cause and fix |
|---|---|
| MariaDB does not start after editing the file | A typo or unknown option. Run sudo journalctl -u mariadb -b and look for unknown variable; test with sudo -u mysql /usr/libexec/mariadbd --verbose --help >/dev/null, which parses the config and reports errors. |
Warning 1292 on SET GLOBAL innodb_buffer_pool_size | Above innodb_buffer_pool_size_max (10.11.12 to 10.11.16 default). Set the size in the config file and restart. |
OOM killer stops mariadbd; dmesg shows Out of memory: Killed process ... (mariadbd) | Buffer pool plus per-connection buffers exceed RAM. Lower innodb_buffer_pool_size, revert raised session buffers, check Max_used_connections. |
| Restart takes minutes or systemd times out | Huge buffer pool dump/load or a crash recovery with a big redo log. Wait it out (watch the error log); raise TimeoutSec with systemctl edit mariadb if needed. |
| Slow log stays empty | slow_query_log is OFF, the threshold is too high, or the session was opened before SET GLOBAL long_query_time. Reconnect and check SHOW GLOBAL STATUS LIKE 'Slow_queries'. |
File '/var/log/mariadb/mariadb-slow.log' not found (Errcode: 13) | Permissions or SELinux. Use /var/log/mariadb (owned by mysql, type mysqld_log_t); for another directory run semanage fcontext -a -t mysqld_log_t '/path(/.*)?' and restorecon -Rv /path. |
| Slow log keeps growing after rotation | The server is still writing to the rotated file. The postrotate flush failed; run sudo -u mysql mariadb-admin --local flush-slow-log and read the error. |
ERROR 1040: Too many connections | Raise max_connections (it is dynamic) and find the leak; check SHOW PROCESSLIST for idle Sleep connections and lower wait_timeout if they pile up. |
| Hit ratio low right after a restart | Normal: the pool is warming up. Check Innodb_buffer_pool_load_status and measure again later. |
| Writes stall in bursts | Redo log too small or innodb_io_capacity too low for the write rate. Check checkpoint age (step 3) and dirty pages. |
| Galera: whole cluster slows down during an ALTER TABLE | TOI schema change. Use a quiet window, pt-online-schema-change, or RSU (step 9). |
Galera: wsrep_flow_control_paused high | One node is slower (hardware, disk, competing load) or applier threads are too few. Compare wsrep_local_recv_queue_avg across nodes. |
MySQL 8.4 notes
- Redo log size is set with
innodb_redo_log_capacity(MySQL 8.0.30 and later) instead ofinnodb_log_file_size. - MySQL still has
innodb_buffer_pool_instances, andinnodb_dedicated_server=ONsizes the buffer pool and redo log automatically on a dedicated host. - The query cache was removed in MySQL 8.0.
- Use
EXPLAIN ANALYZE(MySQL) instead ofANALYZE(MariaDB). The Performance Schema is on by default and thesysschema views work out of the box. - The slow log variables are
slow_query_log,slow_query_log_fileandlong_query_time;log_slow_verbositydoes not exist, butlog_slow_extra=ONadds similar fields. The summary tool ismysqldumpslow, andpt-query-digestworks the same way.
Next in the series
Part 7 covers TLS-encrypted client connections and troubleshooting common MariaDB/MySQL errors. Earlier parts: part 5, MariaDB Galera Cluster, part 4, GTID primary/replica replication, part 3, backups and point-in-time recovery, part 2, users, grants and remote access and part 1, install and secure MariaDB/MySQL. For the same exercise on PostgreSQL, see PostgreSQL performance tuning with EXPLAIN ANALYZE, indexes and autovacuum.
Official references: MariaDB: InnoDB buffer pool, MariaDB: InnoDB system variables, MariaDB: InnoDB redo log, MariaDB: slow query log overview, MariaDB: flow control in Galera Cluster and Percona Toolkit: pt-query-digest.