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

Published October 15, 2022

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 GB database server has very different resource boundaries from one inside a 16 GB 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();

mariadbd --version

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'
);

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"

Then inspect effective startup options:

mariadbd --print-defaults
# or
my_print_defaults mysqld

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

Use [mariadbd] for modern deployments when the server reads that group. If the existing installation uses another server option group, follow the existing configuration layout.

For Docker, keep configuration outside the container image:

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

services:
  mariadb:
    image: mariadb:11.8
    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

For Docker:

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

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

For Kubernetes:

kubectl get pod <pod> -o yaml
# look for resources.limits.memory

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 GB global cache is one thing. A 1 GB 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.

Check actual database size

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

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%';

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:

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';

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 starting heuristics, not hard requirements. Larger redo reduces checkpoint pressure but can increase crash recovery work.

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

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;

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

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

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

Monitor with iostat and InnoDB status

iostat -xz 1

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';

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

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

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

thread_cache_size (default 256): measure Threads_created / Connections. If thread creation is high relative to connection churn, a larger cache can help. Ignored when the thread pool is active.

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: Automatic value is MIN(900, 50 + max_connections / 5). For max_connections=300 → back_log ≈ 110. Rarely needs manual tuning.

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

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.

ulimit -n
systemctl show mariadb --property=LimitNOFILE

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.

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:

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 16 GB Docker container with mem_limit: 16g must be sized against 16 GiB — not the host's 128 GiB.

services:
  mariadb:
    image: mariadb:11.8
    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):

sort_buffer_size      = 2M
join_buffer_size      = 256K
read_buffer_size      = 128K
read_rnd_buffer_size  = 256K
# Sum ≈ 2.625M × 225 ≈ 591M ≈ 0.56 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. Target smoothing window = 30 minutes:

Compare with 28 GiB buffer pool. This may reveal that the workload needs architectural attention — if the database genuinely generates 12 MiB/s continuously, understand why before merely allocating more disk.

Step 5 — I/O capacity

fio shows ~40,000 sustainable IOPS at 16 KiB mixed random, but latency acceptable only up to ~25,000 IOPS. Start innodb_io_capacity = 4000, test under load, increase to 6000 when dirty pages grow, set innodb_io_capacity_max = 12000 as ceiling. Not derived from max fio IOPS.

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
→ open_files_limit = 65535 (if OS/container allows)

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 · 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 = 128

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

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)

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 GB 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();
mariadbd --print-defaults
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');

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}'

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_gb
FROM information_schema.tables WHERE engine='InnoDB';

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

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';

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

iostat -xz 1

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';

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

# 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';

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

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.