MariaDB Performance Tuning for Production: Memory, InnoDB, I/O, and Docker

50 min read

Published June 29, 2026 · Major revision (MariaDB 10.11 / 11.4 / 11.8 LTS)

Measure → Calculate → Configure → Load test → Observe → Tune again. Every important variable: what it does, the default, what goes wrong, and how to calculate the right value.

MariaDB performance tuning is often reduced to numbers copied from someone else's my.cnf:

innodb_buffer_pool_size = 16G
max_connections = 500
tmp_table_size = 1G
sort_buffer_size = 8M

It looks reasonable. It may even work. And it can still be completely wrong.

A MariaDB instance on a dedicated 128 GiB database server has very different resource boundaries from one inside a 16 GiB Docker container on a host that also runs Redis, Nginx, application workers, and backup jobs. Good tuning is about the relationship between infrastructure, workload, MariaDB internals, OS limits, and application architecture — not memorizing recommended values.

For every important setting in this guide you get four things:

1. What it does — in plain language
2. Default value — upstream MariaDB default if you omit it
3. What goes wrong — if you leave the default or set it wrong
4. How to calculate — step-by-step with real numbers

Part 1 — Versions, setup, and configuration layout

For production, use an actively maintained LTS branch rather than an old non-LTS release just because an older tuning guide happens to use it.

MariaDB 10.11, 11.4, and 11.8 are the relevant Community Server LTS lines discussed here. MariaDB 11.4 was explicitly introduced as an LTS release; MariaDB 11.8 is the newer Community LTS line. MariaDB's current release information shows active 2026 maintenance releases for 10.11, 11.4, and 11.8.

MariaDB Community Server 10.6 reached end of life on July 6, 2026. If you are starting a new Community deployment now, 10.6 should not be your baseline. Older versions are mentioned only when a variable changed, was deprecated, or was removed.

Always check the exact version first

SELECT VERSION();

+---------------------------+
| VERSION()                 |
+---------------------------+
| 11.8.2-MariaDB-ubu2404-log|
+---------------------------+

mariadbd --version

mariadbd  Ver 11.8.2-MariaDB for Linux on x86_64 (MariaDB Server)

Do not assume that MariaDB 11.x means every 11.x release behaves exactly the same. Some variables changed during maintenance releases too.

MariaDB exposes system-variable metadata through information_schema.SYSTEM_VARIABLES, which is extremely useful when you need the actual default, current value, scope, and origin:

SELECT
    VARIABLE_NAME,
    GLOBAL_VALUE,
    DEFAULT_VALUE,
    VARIABLE_SCOPE,
    GLOBAL_VALUE_ORIGIN
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME IN (
    'innodb_buffer_pool_size',
    'innodb_log_file_size',
    'innodb_log_buffer_size',
    'innodb_io_capacity',
    'max_connections',
    'tmp_table_size',
    'max_heap_table_size',
    'sort_buffer_size',
    'join_buffer_size'
);

VARIABLE_NAME             GLOBAL_VALUE  DEFAULT_VALUE  VARIABLE_SCOPE  GLOBAL_VALUE_ORIGIN
INNODB_BUFFER_POOL_SIZE   30064771072   134217728      GLOBAL          CONFIG
INNODB_LOG_FILE_SIZE      17179869184   100663296      GLOBAL          CONFIG
INNODB_IO_CAPACITY        6000          200            GLOBAL          CONFIG
MAX_CONNECTIONS           225           151            GLOBAL          CONFIG
TMP_TABLE_SIZE            134217728     16777216       SESSION         CONFIG

Important distinction: MariaDB compiled/server default is not necessarily the same as the default configuration supplied by Debian/RHEL/Docker image. An OS package or image can ship a .cnf file that overrides the upstream server default.

Find the configuration MariaDB actually reads

Do not assume the main file is always /etc/my.cnf. Depending on installation and distribution, common locations include:

/etc/my.cnf
/etc/mysql/my.cnf
/etc/mysql/mariadb.conf.d/
/etc/mysql/conf.d/
/etc/my.cnf.d/

Check the actual search path:

mariadbd --help --verbose 2>/dev/null | grep -A20 "Default options are read from"

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-11.8 mariadb mariadb-11.8 mariadbd mariadbd-11.8 client-server

Then inspect effective startup options:

mariadbd --print-defaults

mariadbd would have been started with the following arguments:
--user=mysql --datadir=/var/lib/mysql --innodb-buffer-pool-size=28G --innodb-log-file-size=16G --max-connections=225

The exact file hierarchy matters because later option files can override values loaded earlier.

Recommended configuration layout

For a package-based Linux installation, avoid editing vendor files whenever possible. Create your own file:

• Debian/Ubuntu: /etc/mysql/mariadb.conf.d/99-production-tuning.cnf
• RHEL/Rocky/AlmaLinux: /etc/my.cnf.d/99-production-tuning.cnf

The copy-ready snippets in this article target /etc/mysql/mariadb.conf.d/99-production-tuning.cnf and use [mariadbd]. Confirm that the directory is included and the group is listed by mariadbd --help --verbose. On RHEL-family systems, translate the path to the included /etc/my.cnf.d/ directory; do not create a Debian path that the server never reads.

For Docker, keep configuration outside the container image:

mariadb/
├── conf.d/
│   └── 99-production-tuning.cnf
└── compose.yaml

services:
  mariadb:
    image: mariadb:11.4.3-noble
    volumes:
      - ./conf.d:/etc/mysql/conf.d:ro
      - mariadb-data:/var/lib/mysql

Do not SSH into a running container and edit /etc/mysql/... manually — that change disappears when the container is replaced. A production configuration should be version controlled, reproducible, and mounted or baked intentionally.

Part 2 — Resource boundaries and memory budget

Before calculating any MariaDB parameter, determine the resource boundary. There are three different questions:

1. How much RAM does the physical host have?
2. How much RAM is available to the VM?
3. How much RAM is actually available to the MariaDB process/container?

They are not necessarily the same.

Measure RAM on Linux

free -h
cat /proc/meminfo

               total        used        free      shared  buff/cache   available
Mem:            62Gi        18Gi       6.0Gi       1.0Gi        38Gi        43Gi
Swap:          4.0Gi          0B       4.0Gi
MemTotal:       65996564 kB
MemAvailable:   45112064 kB

For Docker:

docker inspect <container> --format '{{.HostConfig.Memory}}'

17179869184

If the result is non-zero, it represents the container's memory limit in bytes.

For Kubernetes:

kubectl get pod <pod> -o jsonpath='{.spec.containers[?(@.name=="mariadb")].resources.limits.memory}{"\n"}'

16Gi

The database must be sized against the resource boundary that the MariaDB process actually has.

Build a memory budget

This is fundamentally better than 64 × 0.80 = 51.2 GiB because the second calculation ignores the rest of the machine.

Global vs per-connection memory

MariaDB memory is not one big pool. Think about it in two categories:

• Global allocations: InnoDB buffer pool, Performance Schema, other global caches
• Dynamic/per-session allocations: sort buffers, join buffers, read buffers, temporary tables, network buffers, transaction-related memory

A 1 GiB global cache is one thing. A 1 GiB limit that can potentially be reached by hundreds of sessions is something else entirely. MariaDB's own documentation warns about the cumulative effect of per-thread buffers at high concurrency.

Where B = buffer pool, C = expected peak concurrent sessions, P = estimated per-session working memory, T = temporary-table allowance, G = other global MariaDB memory, R = safety reserve, M = available MariaDB memory. This is a capacity-planning model, not an exact MariaDB allocator equation.

Part 3 — InnoDB buffer pool

The InnoDB buffer pool is normally the largest memory allocation in an InnoDB-heavy workload. It caches table data, indexes, dirty pages, and frequently accessed pages. The objective is to keep the active working set in memory and reduce physical reads.

[mariadbd]
innodb_buffer_pool_size = 34G

Check actual database size

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

+--------------------------+
| innodb_data_and_index_gib |
+--------------------------+
|                    28.40 |
+--------------------------+

If the result is 28.4 GiB and the buffer pool is 34 GiB, the entire current logical data/index footprint can potentially fit. That is useful information, but not enough — the database may grow, there may be fragmentation, and the working set may differ from total size.

Measure buffer pool effectiveness

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Variable_name                       Value
Innodb_buffer_pool_read_requests    100000000
Innodb_buffer_pool_reads            100000

Useful counters: Innodb_buffer_pool_read_requests and Innodb_buffer_pool_reads.

Do not treat one ratio as a universal target. A workload doing large sequential scans behaves very differently from a small OLTP workload. The right question: does increasing the buffer pool materially reduce physical reads and improve application latency?

Deprecated: innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size

innodb_buffer_pool_instances was deprecated in MariaDB 10.5.1 and removed in 10.6. Modern MariaDB runs the buffer pool as a single instance — do not configure it on MariaDB 10.6+.

innodb_buffer_pool_chunk_size became autosized from MariaDB 10.8. On current LTS releases (10.11.12+, 11.4.6+, 11.8.2+), the variable is deprecated/ignored. Do not configure it unless you have a very specific legacy requirement.

Buffer pool warmup

After restart, the buffer pool is cold and queries hit storage until frequently used pages return to RAM. MariaDB supports:

# /etc/mysql/mariadb.conf.d/99-production-tuning.cnf
[mariadbd]
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

Both are enabled by default in current documentation. For a large production database, this is operationally useful because a restart does not necessarily mean starting with a completely cold cache.

Part 4 — Redo log sizing

The redo log should not be sized from RAM alone. First measure how much redo the workload actually generates.

SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

-- 14:00:00
Innodb_os_log_written    500000000000
-- 14:10:00
Innodb_os_log_written    507549747200

Take the value, wait for a representative period (e.g. 10 minutes), then execute again.

Use peak redo rate, not average. A production database may generate 10 MiB/s normally and 100 MiB/s during peak traffic. Size based on the busiest realistic window.

MariaDB has historically recommended two useful starting points: combined redo capacity ≈ 25–50% of buffer pool, or redo capacity ≈ one hour of peak redo generation. These are cross-checks, not simultaneous requirements: at 12 MiB/s, one hour would be 43,200 MiB = 42.19 GiB, while 25–50% of a 28 GiB pool is 7–14 GiB. This worked system chooses 16 GiB because its explicit smoothing objective is about 20 minutes (22.8 minutes actual), then accepts the modest 57% buffer-pool ratio and lower recovery exposure instead of blindly using the one-hour value.

[mariadbd]
innodb_log_file_size = 16G

[mariadbd]
innodb_log_buffer_size = 64M

Deprecated: innodb_log_files_in_group. Older MariaDB used innodb_log_file_size × innodb_log_files_in_group (e.g. 4G × 2 = 8G). MariaDB 10.5 moved to a single redo log file; innodb_log_files_in_group was deprecated and removed in 10.6. Do not use it on modern LTS releases.

Part 5 — Durability and flush settings

[mariadbd]
innodb_flush_log_at_trx_commit = 1

[mariadbd]
sync_binlog = 1

innodb_flush_method: Historically Linux guides recommended O_DIRECT. MariaDB changed the Unix default to O_DIRECT starting with 10.6. MariaDB 11.0 deprecated innodb_flush_method in favor of granular variables (innodb_data_file_write_through, innodb_data_file_buffering, innodb_log_file_write_through, innodb_log_file_buffering). Do not blindly add O_DIRECT to a MariaDB 11.x configuration.

innodb_file_per_table defaults to ON — keep it. The explicit variable was deprecated in MariaDB 11.0.1 because the desired behavior is already the default.

innodb_doublewrite defaults to ON — protects against partial page writes. Deprecated as a tuning switch in MariaDB 11.0.1. Do not disable it because "NVMe is fast."

innodb_strict_mode defaults to ON — keep it. Not primarily a performance variable; it prevents invalid InnoDB definitions from being silently accepted.

Part 6 — Storage measurement with fio

Seeing "NVMe" and immediately writing innodb_io_capacity = 10000 is not a calculation. First determine where the MariaDB data directory lives:

SELECT @@datadir;

+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+

findmnt -T /var/lib/mysql
df -hT /var/lib/mysql
lsblk -d -o NAME,TRAN,ROTA,TYPE,SIZE,MODEL

TARGET SOURCE    FSTYPE OPTIONS
/      /dev/nvme0n1p2 ext4 rw,relatime
Filesystem       Type  Size Used Avail Use% Mounted on
/dev/nvme0n1p2   ext4  450G 210G  218G  50% /
NAME    TRAN ROTA TYPE  SIZE MODEL
nvme0n1 nvme    0 disk  477G SAMSUNG MZVL2512HCJQ

Run fio on a separate test file — never on MariaDB data files

Do not run fio --filename=/var/lib/mysql/ibdata1 or point benchmarks at .ibd files.

sudo mkdir -p /var/lib/mysql/fio-test

sudo fio \
  --name=mariadb-storage \
  --filename=/var/lib/mysql/fio-test/testfile \
  --size=8G \
  --rw=randrw \
  --rwmixread=70 \
  --bs=16k \
  --ioengine=libaio \
  --direct=1 \
  --iodepth=32 \
  --numjobs=1 \
  --runtime=60 \
  --time_based \
  --group_reporting

read: IOPS=15.4k, BW=241MiB/s, clat (usec): avg=1120, 99.00th=2474
write: IOPS=6598, BW=103MiB/s, clat (usec): avg=2210, 99.00th=4883
Run status group 0 (all jobs):
  READ: bw=241MiB/s (253MB/s), io=14.1GiB, run=60001msec
  WRITE: bw=103MiB/s (108MB/s), io=6.04GiB, run=60001msec

The preceding mkdir is normally silent: success is exit status 0 with no stderr. It does not justify fabricated terminal output.

Why 16 KiB? Traditional InnoDB uses 16 KiB pages by default, so --bs=16k approximates InnoDB-oriented storage testing. Real MariaDB I/O also includes sequential access, redo writes, fsync, background flushing, temporary files, and metadata — fio measures the storage subsystem, not MariaDB exactly.

Important fio parameters

--rw=randrw          Random read/write workload
--rwmixread=70       70% read, 30% write (adjust to your app: 90=read-heavy, 30=write-heavy)
--bs=16k             I/O size matching InnoDB page size
--iodepth=32         Outstanding I/O operations (queue depth)
--direct=1           Bypass OS page cache for the benchmark
--runtime=60         Run duration (use longer for serious benchmarking)

Do not look only at IOPS. 20,000 IOPS @ 0.5 ms is very different from 20,000 IOPS @ 20 ms. Also consider latency, bandwidth, queue depth, read/write mix, and tail latency.

Test different queue depths

for depth in 1 4 16 32 64; do
    fio \
      --name=mariadb-q${depth} \
      --filename=/var/lib/mysql/fio-test/testfile \
      --size=8G \
      --rw=randrw \
      --rwmixread=70 \
      --bs=16k \
      --ioengine=libaio \
      --direct=1 \
      --iodepth="$depth" \
      --numjobs=1 \
      --runtime=60 \
      --time_based \
      --group_reporting
done

mariadb-q1:  IOPS=3000,  clat avg=0.30ms
mariadb-q16: IOPS=18000, clat avg=0.90ms
mariadb-q32: IOPS=22000, clat avg=1.70ms
mariadb-q64: IOPS=23000, clat avg=4.80ms

Monitor with iostat and InnoDB status

iostat -xz 1

Device         r/s    w/s  r_await  w_await aqu-sz  %util
nvme0n1     3100.0 1850.0     0.42     1.73   4.51  68.2

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_flushed';

Innodb_buffer_pool_pages_dirty     18420
Innodb_buffer_pool_wait_free      0
Innodb_buffer_pool_pages_flushed  9274412

Is InnoDB keeping up with dirty-page flushing? If dirty pages keep growing while storage has headroom, background flushing may need attention. If storage latency is already high, increasing flushing can make the problem worse.

Part 7 — InnoDB I/O tuning

[mariadbd]
innodb_io_capacity = 6000

[mariadbd]
innodb_io_capacity_max = 12000

innodb_read_io_threads / innodb_write_io_threads: Default 4 each. Do not use CPU cores = I/O threads as a formula. Storage and workload matter more. Benchmark higher values only if I/O submission concurrency appears limiting.

innodb_flush_neighbors: On SSD/NVMe, innodb_flush_neighbors = 0 is commonly sensible — neighbor flushing optimizes for spinning disks. Validate on your exact version and storage.

innodb_lru_scan_depth: Default 1536 since MariaDB 10.5.7. No good universal formula. If flushing is healthy, leave it alone. If flushing is unhealthy, measure first — do not increase simply because the server uses NVMe.

Part 8 — Temporary tables and per-connection memory

[mariadbd]
tmp_table_size = 128M
max_heap_table_size = 128M

[mariadbd]
sort_buffer_size = 2M

[mariadbd]
join_buffer_size = 256K

read_buffer_size (default 128 KiB) and read_rnd_buffer_size (default 256 KiB) follow the same principle — per-session buffers multiply by concurrency. max_allowed_packet (default 16 MiB) and net_buffer_length (default 16 KiB) matter for large BLOB/text transfers. binlog_cache_size (default 32 KiB) — measure Binlog_cache_disk_use before increasing globally.

Part 9 — Connections and threading

[mariadbd]
max_connections = 225

[mariadbd]
max_user_connections = 210

thread_cache_size (default 256 on the LTS versions covered here): calculate the interval ratio ΔThreads_created / ΔConnections, not lifetime counters viewed once. With 18 new threads for 12,000 connections, the interval creation ratio is 0.15%; a cache of 64 is already adequate for the worked workload. A larger value is not automatically better, and the setting is ignored when the thread pool is active.

[mariadbd]
thread_cache_size = 64

Apply: use only with one-thread-per-connection, persist the measured value, then compare counter deltas over the same workload interval. Do not infer that 64 must exceed concurrent connections: it caches idle worker threads, not active sessions.

thread_handling: Linux default is one-thread-per-connection. The MariaDB thread pool (available without commercial restriction) can reduce scheduling pressure at very high connection counts — benchmark first, do not enable just because "high concurrency = thread pool".

back_log: when left unset, MariaDB derives MIN(900, 50 + max_connections / 5) using integer arithmetic. For max_connections=225, the automatic value is 95; for 300 it is 110. Keep the automatic value unless bursts overflow the listen queue and the OS somaxconn is also sufficient. The worked configuration therefore does not set back_log=128.

Part 10 — Table cache, file descriptors, and OS limits

[mariadbd]
table_open_cache = 4000
table_definition_cache = 3000

table_definition_cache (default 400): caches table definition metadata. Increase when many distinct tables are accessed. Default was raised in newer MariaDB versions — verify on your installation.

[mariadbd]
open_files_limit = 65535

ulimit -n
systemctl show mariadb --property=LimitNOFILE

65535
LimitNOFILE=65535

SELECT @@open_files_limit;

+--------------------+
| @@open_files_limit |
+--------------------+
|              65535 |
+--------------------+

systemd TasksMax and container ulimits matter at high connection counts. MariaDB cannot exceed the operating system's hard limit.

Part 11 — Timeouts, logging, and query optimization

Timeouts must be designed as a chain — application timeout, connection pool timeout, MariaDB wait_timeout, and max_statement_time should be coherent, not contradictory.

[mariadbd]
wait_timeout = 600
interactive_timeout = 600

connect_timeout (default 10s), net_read_timeout (default 30s), net_write_timeout (default 60s): align with application and load balancer timeouts.

max_statement_time (default 0 = unlimited): set a sane limit for runaway queries on supported versions.

Slow query logging:

# /etc/mysql/mariadb.conf.d/99-production-tuning.cnf
[mariadbd]
slow_query_log = ON
long_query_time = 1

MariaDB 10.11 introduced log_slow_query_time as an alias for long_query_time.

Query optimization before memory optimization. The query cache was removed in MariaDB 10.1.5 — do not configure it. Performance Schema (enabled by default in modern MariaDB) helps identify expensive statements. Fix indexes and query plans before globally increasing per-session buffers.

Binary log format: ROW is the modern default and recommended for replication. Transaction isolation: understand your application's requirements — do not change globally without analysis. innodb_lock_wait_timeout (default 50s): tune based on application deadlock tolerance.

tmpdir: ensure adequate filesystem capacity for temporary files. Docker storage limits and volume sizing matter — a full tmpdir causes query failures unrelated to memory tuning.

Part 12 — Docker, Kubernetes, and container limits

Container memory limits are hard boundaries. MariaDB inside a Docker container with mem_limit: 16g has a 16 GiB boundary and must be sized against it — not the host's 128 GiB.

Note: On Docker Compose (non-Swarm), mem_limit still works. For Swarm stacks, use deploy.resources.limits.memory instead.

services:
  mariadb:
    image: mariadb:11.4.3-noble
    mem_limit: 16g
    cpus: 4
    volumes:
      - ./conf.d:/etc/mysql/conf.d:ro
      - mariadb-data:/var/lib/mysql
    ulimits:
      nofile:
        soft: 65535
        hard: 65535

For Kubernetes, respect resources.limits.memory and resources.limits.cpu. MariaDB concurrency must be validated against container CPU limits. Storage class and volume performance determine I/O capacity — benchmark fio from inside the pod's volume mount when possible.

CPU limits can cause throttling that looks like database slowness. If pidstat shows MariaDB waiting on CPU while queries are "slow," check cgroup CPU limits before tuning InnoDB.

Part 13 — Worked example: 48 GiB shared VM

Step 1 — MariaDB memory budget

Step 2 — Connection memory budget

Peak measured application connections = 180. Safety margin 25% → 180 × 1.25 = 225 → max_connections = 225.

Per-session buffers (conservative):

# /etc/mysql/mariadb.conf.d/99-production-tuning.cnf
[mariadbd]
sort_buffer_size      = 2M
join_buffer_size      = 256K
read_buffer_size      = 128K
read_rnd_buffer_size  = 256K
# Binary-unit planning: 2.625 MiB × 225 = 590.625 MiB ≈ 591 MiB = 0.58 GiB

Reserve ~2 GiB for session/query/temporary activity.

Step 3 — Buffer pool

36 GiB budget
− 2 GiB connection/query
− 2 GiB global/internals
− 3 GiB operational safety
= 29 GiB → round to innodb_buffer_pool_size = 28G

We did not use 48 × 70%. We calculated it.

Step 4 — Redo

Peak production-like testing shows redo generation = 12 MiB/s. Use a desired smoothing window around 20 minutes and select a practical 16 GiB file:

innodb_log_file_size = 16G is 57% of the 28 GiB buffer pool, slightly above the 25–50% secondary heuristic. The measured workload is the primary evidence: 16 GiB gives about 22.8 minutes at the observed peak rate. Confirm crash-recovery objectives and free disk space before rollout.

Step 5 — I/O capacity

fio shows ~22,000 IOPS at queue depth 32 with 1.7 ms average latency; queue depth 64 adds only ~1,000 IOPS while latency rises to 4.8 ms. Start innodb_io_capacity = 4000, test under load, increase to 6000 when dirty pages grow, and set innodb_io_capacity_max = 12000 as the urgent ceiling. These values are deliberately below the fio saturation edge.

Step 6 — Table cache and file descriptors

2,800 tables → table_open_cache = 4000, table_definition_cache = 3000.

MAX(225 × 5, 225 + 4000 × 2) = MAX(1125, 8225) = 8225
→ common planning estimate = 8225; MariaDB may request more for internal needs
→ configure 65535 as margin only when systemd/container grants it; verify live

Part 14 — Example production my.cnf

Based on the 48 GiB example above. Assumptions: MariaDB 11.8 · 48 GiB RAM · 8 vCPU · NVMe · Shared VM · 28 GiB buffer pool · 16 GiB redo (22.8 minutes at 12 MiB/s) · 225 max connections · 6,000 I/O capacity.

[mariadbd]

# ============================================================
# INNODB MEMORY
# ============================================================
# Calculated from MariaDB memory budget after reserving
# connections, temporary work, internal allocations, headroom.
innodb_buffer_pool_size = 28G

# ============================================================
# BUFFER POOL WARMUP
# ============================================================
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

# ============================================================
# INNODB REDO
# ============================================================
innodb_log_file_size = 16G
innodb_log_buffer_size = 64M

# ============================================================
# DURABILITY
# ============================================================
innodb_flush_log_at_trx_commit = 1

# ============================================================
# INNODB I/O
# ============================================================
innodb_io_capacity = 6000
innodb_io_capacity_max = 12000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_flush_neighbors = 0

# ============================================================
# TEMPORARY TABLES
# ============================================================
tmp_table_size = 128M
max_heap_table_size = 128M

# ============================================================
# PER-CONNECTION MEMORY
# ============================================================
sort_buffer_size = 2M
join_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
binlog_cache_size = 256K
net_buffer_length = 16K
max_allowed_packet = 64M

# ============================================================
# CONNECTIONS
# ============================================================
max_connections = 225
max_user_connections = 210
thread_cache_size = 64
# back_log intentionally omitted: automatic value is 95 for max_connections=225

# ============================================================
# TABLE CACHE
# ============================================================
table_open_cache = 4000
table_definition_cache = 3000

# ============================================================
# FILE DESCRIPTORS
# ============================================================
open_files_limit = 65535

# ============================================================
# CONNECTION TIMEOUTS
# ============================================================
wait_timeout = 600
interactive_timeout = 600
connect_timeout = 10
net_read_timeout = 30
net_write_timeout = 60

# ============================================================
# SLOW QUERY LOG
# ============================================================
slow_query_log = ON
long_query_time = 1

# ============================================================
# TRANSACTIONAL SAFETY
# ============================================================
innodb_strict_mode = ON

# Replication primary (uncomment if this node is a replication source):
# log_bin = ON
# sync_binlog = 1
# gtid_strict_mode = ON

# Query guard (optional):
# max_statement_time = 300

docker compose restart mariadb
docker compose exec mariadb mariadb -uroot -p"$ROOT" -e "
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'max_connections';"

 Container site-mariadb-1  Restarting
 Container site-mariadb-1  Started
Variable_name                  Value
innodb_buffer_pool_size        30064771072
innodb_log_file_size           17179869184
max_connections                225

Intentionally absent:

• innodb_buffer_pool_instances — removed in MariaDB 10.6
• innodb_log_files_in_group — removed in MariaDB 10.6 (single redo file since 10.5)
• innodb_flush_method = O_DIRECT — Unix default since 10.6; deprecated in 11.0

A good production my.cnf should not contain every variable you have ever heard of. It should express intentional deviations from MariaDB defaults, with comments explaining why each value exists.

Part 15 — Reference tables

Version changes — quick lookup

Version    │ Key changes relevant to tuning
───────────┼──────────────────────────────────────────────────────────────────
10.5       │ Single buffer-pool instance (instances deprecated)
           │ Single redo log file architecture begins
           │ Background I/O mechanism changes
10.6       │ Removed: innodb_buffer_pool_instances, innodb_log_files_in_group,
           │         innodb_page_cleaners
           │ Unix innodb_flush_method default = O_DIRECT
10.8       │ innodb_buffer_pool_chunk_size autosized
10.9       │ innodb_log_file_size dynamically resizable
10.11 LTS  │ Slow-query alias log_slow_query_time; active maintenance 2026
11.0       │ Deprecated: innodb_flush_method, innodb_file_per_table,
           │         innodb_doublewrite (granular I/O vars introduced)
11.4 LTS   │ Cost-based optimizer model for SSD characteristics; test plans after upgrade
11.8 LTS   │ Current Community LTS baseline; active maintenance 2026

Upstream defaults — quick lookup

Variable                          │ Default
──────────────────────────────────┼────────────────────────────
innodb_buffer_pool_size           │ 128 MiB
innodb_log_file_size              │ 96 MiB (10.5+); 48 MiB (10.4-era)
innodb_log_buffer_size            │ 16 MiB
innodb_flush_log_at_trx_commit    │ 1
innodb_io_capacity                │ 200
innodb_io_capacity_max            │ max(2000, 2 × io_capacity)
innodb_read_io_threads            │ 4
innodb_write_io_threads           │ 4
tmp_table_size                    │ 16 MiB
max_heap_table_size               │ 16 MiB
sort_buffer_size                  │ 2 MiB
join_buffer_size                  │ 256 KiB
read_buffer_size                  │ 128 KiB
read_rnd_buffer_size              │ 256 KiB
net_buffer_length                 │ 16 KiB
max_allowed_packet                │ 16 MiB
max_connections                   │ 151
thread_cache_size                 │ 256
table_open_cache                  │ 2000
table_definition_cache            │ 400
wait_timeout                      │ 28800 s
interactive_timeout               │ 28800 s
connect_timeout                   │ 10 s
net_read_timeout                  │ 30 s
net_write_timeout                 │ 60 s
long_query_time                   │ 10 s
max_statement_time                │ 0 (unlimited)

Verify actual values on your installation via information_schema.SYSTEM_VARIABLES — OS packages and Docker images may override upstream defaults.

Planning formulas

Category         │ Formula
─────────────────┼────────────────────────────────────────────────────────────
Memory budget    │ MariaDB Budget = RAM − Other Services − OS − Headroom
Buffer pool      │ Budget − Connection/Query − Temporary − Global/Internal
Physical reads   │ (buffer_pool_reads / read_requests) × 100
Redo capacity    │ Peak Redo Rate × Smoothing Window
                 │ Secondary: 25–50% of buffer pool
I/O capacity     │ Start conservative; iterate with fio + iostat + dirty pages
                 │ Never = max fio IOPS
Connections      │ Instances × Pool Size + Admin + Headroom
                 │ Validate: Max_used_connections × 1.25–1.5
Temp tables      │ MIN(tmp_table_size, max_heap_table_size)
File descriptors │ MAX(max_connections × 5, max_connections + table_open_cache × 2)
                 │ Effective value is capped by systemd/container hard limit

Post-change monitoring

Area             │ Commands / tools
─────────────────┼────────────────────────────────────────────────────────────
Connections      │ SHOW GLOBAL STATUS LIKE 'Threads%';
                 │ SHOW GLOBAL STATUS LIKE 'Max_used_connections';
Temporary tables │ SHOW GLOBAL STATUS LIKE 'Created_tmp%';
Sorting          │ SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';
Table cache      │ SHOW GLOBAL STATUS LIKE 'Opened_tables';
                 │ SHOW GLOBAL STATUS LIKE 'Open_tables';
Buffer pool      │ SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
Redo             │ SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';
Binary log       │ SHOW GLOBAL STATUS LIKE 'Binlog_cache%';
OS               │ iostat -xz 1 · free -h · vmstat 1
                 │ pidstat -p $(pidof mariadbd) 1

If vmstat shows swap activity (si/so) under database load, do not immediately increase the buffer pool — the memory budget may be too aggressive.

Tuning checklist

Before tuning
  [ ] MariaDB version identified (10.11 / 11.4 / 11.8 LTS)
  [ ] Option-file path identified (mariadbd --print-defaults)
  [ ] VM/container memory and CPU limits identified
  [ ] Datadir and storage device identified
  [ ] Other services on host identified

Memory
  [ ] MariaDB memory budget calculated
  [ ] Buffer pool sized from budget (not RAM × 0.8)
  [ ] Connection memory estimated
  [ ] Temporary-memory budget considered
  [ ] Safety headroom reserved

I/O
  [ ] Storage tested with fio (separate test file, not ibdata1)
  [ ] Multiple queue depths tested; latency measured
  [ ] iostat monitored under load
  [ ] InnoDB dirty-page behavior measured
  [ ] io_capacity tuned iteratively (not set to max fio IOPS)

Connections
  [ ] Application pool size known
  [ ] Peak connections measured (Max_used_connections)
  [ ] max_connections calculated from app architecture
  [ ] max_user_connections considered

File descriptors
  [ ] table_open_cache sized and verified
  [ ] open_files_limit calculated
  [ ] systemd LimitNOFILE and container ulimits checked

Queries
  [ ] Slow query log enabled
  [ ] EXPLAIN used on slow queries
  [ ] Missing indexes investigated before raising buffers

Version compatibility
  [ ] Deprecated variables removed (buffer_pool_instances, log_files_in_group)
  [ ] Removed variables not present in my.cnf
  [ ] Major-version release notes reviewed

Anti-patterns to avoid

Anti-pattern                              │ Why it fails
──────────────────────────────────────────┼────────────────────────────────────
"Use 80% of RAM for buffer pool"          │ Ignores other services and per-connection memory
"Use 1000 connections for a powerful server"│ Capacity ≠ safe concurrency
"NVMe means innodb_io_capacity = 20000"   │ Must measure; fio max ≠ sustainable background I/O
"More sort_buffer is faster"              │ Fix indexes first; per-session buffers multiply
"1 GiB tmp_table_size for WordPress"      │ Exposes dangerous memory ceiling under concurrency
"Copy entire 2018 my.cnf"                 │ Ignores version changes and removed variables
"Query cache makes SELECT faster"         │ Removed in MariaDB 10.1.5
"innodb_io_capacity = fio max IOPS"       │ Causes pathological flushing and latency

Part 16 — Practical walkthrough

Use this sequence when tuning a production or staging MariaDB instance. Do not change every variable at once.

Step 1 — Identify version and effective configuration

SELECT VERSION();

SELECT VARIABLE_NAME, GLOBAL_VALUE, DEFAULT_VALUE, GLOBAL_VALUE_ORIGIN
FROM information_schema.SYSTEM_VARIABLES
WHERE VARIABLE_NAME IN ('innodb_buffer_pool_size','max_connections','innodb_io_capacity');

VERSION()
11.8.2-MariaDB-ubu2404-log
VARIABLE_NAME             GLOBAL_VALUE  DEFAULT_VALUE  GLOBAL_VALUE_ORIGIN
INNODB_BUFFER_POOL_SIZE   30064771072   134217728      CONFIG
MAX_CONNECTIONS           225           151            CONFIG
INNODB_IO_CAPACITY        6000          200            CONFIG

mariadbd --print-defaults

mariadbd would have been started with the following arguments:
--innodb-buffer-pool-size=28G --innodb-log-file-size=16G --innodb-io-capacity=6000 --max-connections=225

Step 2 — Determine resource boundary

free -h
docker inspect mariadb --format '{{.HostConfig.Memory}} {{.HostConfig.NanoCpus}}'
# or kubectl get pod mariadb-0 -o jsonpath='{.spec.containers[0].resources}'

Mem: 62Gi 18Gi 6.0Gi 1.0Gi 38Gi 43Gi
51539607552 8000000000

Write down: total RAM available to MariaDB, CPU limit, and what else runs on the host.

Step 3 — Build memory budget and size buffer pool

# MariaDB budget = available RAM − other services − OS − headroom
# Buffer pool = budget − connection/query − temporary − internal headroom

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

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

innodb_gib
28.40
Innodb_buffer_pool_read_requests  100000000
Innodb_buffer_pool_reads          100000

Step 4 — Measure redo generation

# Snapshot Innodb_os_log_written, wait 10+ minutes during peak traffic, snapshot again
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

14:00:00  Innodb_os_log_written  500000000000
14:10:00  Innodb_os_log_written  507549747200

Calculate peak redo rate. Size innodb_log_file_size from peak rate × smoothing window, cross-check against 25–50% of buffer pool.

Step 5 — Benchmark storage with fio

sudo mkdir -p /var/lib/mysql/fio-test
sudo fio --name=test --filename=/var/lib/mysql/fio-test/testfile \
  --size=8G --rw=randrw --rwmixread=70 --bs=16k --ioengine=libaio \
  --direct=1 --iodepth=32 --runtime=60 --time_based --group_reporting

read: IOPS=15.4k, BW=241MiB/s, clat avg=1.12ms, p99=2.47ms
write: IOPS=6598, BW=103MiB/s, clat avg=2.21ms, p99=4.88ms

iostat -xz 1

Device       r/s    w/s  r_await  w_await aqu-sz %util
nvme0n1   3100.0 1850.0     0.42     1.73   4.51  68.2

Record sustainable IOPS and latency — not just peak IOPS.

Step 6 — Calculate max_connections from application

# App instances × pool size + admin + headroom
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';

Max_used_connections  180
Threads_connected     142
Threads_running       18

Step 7 — Write version-controlled 99-production-tuning.cnf

# /etc/mysql/mariadb.conf.d/99-production-tuning.cnf
[mariadbd]
# Example comment style:
# 2026-08-28 — Peak measured redo: 12 MiB/s; smoothing window ~20 min
# Validated under staging workload.
innodb_log_file_size = 16G

Mount via Docker Compose or place in mariadb.conf.d/. Restart MariaDB.

Step 8 — Load test and observe

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Innodb_buffer_pool_read_requests   100000000
Innodb_buffer_pool_reads           100000
Created_tmp_tables                 420000
Created_tmp_disk_tables            21000
Innodb_buffer_pool_pages_dirty     18420

iostat -xz 1
vmstat 1
pidstat -p $(pidof mariadbd) 1

nvme0n1: r_await=0.42ms w_await=1.73ms aqu-sz=4.51 %util=68.2
vmstat:  r=3 b=1 swpd=0 si=0 so=0 us=42 sy=8 id=43 wa=7
pidstat: %usr=286.0 %system=34.0 %CPU=320.0 CPU=3 Command=mariadbd

Change one category at a time. Compare against baseline. Keep or rollback.

Step 9 — Iterate io_capacity

Start conservative (e.g. 4000). Under production-like load, if dirty pages grow continuously and storage latency is acceptable, increase incrementally. Stop when dirty pages stabilize.

Part 17 — Tuning process and rollout

A tuning change is not finished when MariaDB starts successfully. Test with workload that resembles production — sysbench, application replay, API load test. Important metrics: QPS/TPS, p50/p95/p99 latency, CPU, RAM, swap, IOPS, I/O latency, Threads_running, buffer-pool reads, temporary disk tables, redo generation.

A configuration that improves average throughput but makes p99 terrible may be a regression.

Production rollout sequence:

Current production
      ↓
Create version-controlled candidate (Git + PR)
      ↓
Staging + production-like load test
      ↓
Canary / small deployment
      ↓
Observe (Part 15 monitoring table)
      ↓
Full rollout

For MariaDB version upgrades, a staged replica-based approach gives a safer rollback path. Review release notes for deprecated/removed variables before promoting.

Document why each value exists in version control:

# innodb_buffer_pool_size = 28G
# because: MariaDB memory budget is 36G, 8G reserved for
# connections, temporary work, internal memory, and safety.

Part 18 — Wrapping up

MariaDB tuning is not a collection of magic numbers. It is resource engineering.

RAM        → Memory budget     → Buffer pool + dynamic memory
Storage    → fio + iostat      → InnoDB flushing → io_capacity
Application→ Connection pool   → max_connections
Queries    → EXPLAIN + status  → Per-query buffers
Version    → Supported vars    → Current defaults → No deprecated options

The correct answer to "What is the best MariaDB configuration?" is almost never a configuration file. The better answer: first measure the environment, then calculate the configuration.

A production-ready MariaDB deployment should have enough memory to keep its working set hot, enough redo capacity to absorb the write workload, enough I/O capacity to keep dirty pages under control, enough connection capacity to satisfy the application, and enough operating-system headroom to remain stable during peaks. It should also remain understandable six months later.

The configuration should be measured, calculated, version-aware, tested, and documented — not copied, tweaked, and hoped.