MariaDB Performance Tuning

How to size the InnoDB buffer pool, choose a durability mode, scale IO capacity to the underlying storage, decide between one-thread-per-connection and the thread pool, and avoid the per-connection-buffer multiplication trap. Use this skill on any "the database is slow" or "production my.cnf" task, before changing innodb_flush_log_at_trx_commit, and before copying a MySQL 8 config to MariaDB.

Quick Reference

• innodb_buffer_pool_size : set to 60-80% of RAM on a dedicated DB host. KB caps the recommendation at 80% and warns "not too large, because this can cause swapping, which more than undoes the benefits".
• innodb_flush_log_at_trx_commit=1 is the only full-ACID setting (default). 2 survives process crash but loses up to one second on OS crash. 0 loses up to one second on any crash. 3 exists for legacy 5.5 group-commit emulation.
• innodb_buffer_pool_instances is REMOVED from MariaDB 10.6.0. NEVER write it into a 10.6+ my.cnf : the server logs an unknown-variable warning. Buffer pool runs as a single instance.
• innodb_buffer_pool_chunk_size is deprecated and ignored from 10.11.12, 11.4.6, 11.8.2. From those versions on, buffer-pool resize is contiguous in 1 MB steps up to innodb_buffer_pool_size_max.
• innodb_io_capacity=200 (default) targets HDD. SSD baseline is 2000, NVMe 5000-10000. innodb_io_capacity_max defaults to 2000 ; set it to ~2x innodb_io_capacity. Leaving 200 on NVMe wastes ~95% of the device.
• innodb_flush_method=O_DIRECT (default on Unix since 10.6) avoids OS-cache double-buffering on the data files. The variable itself is deprecated from 11.0 and replaced by four boolean dynamic variables.
• Query cache : query_cache_type=OFF is the default since 10.1.7 (KB confirms "disabled by default"). Variable is NOT removed in 11.x ; it is still present and tunable, just OFF. NEVER turn it ON on a write-heavy workload : try_lock contention serializes invalidations.
• Per-connection buffers MULTIPLY by max_connections : sort_buffer_size (default 2 MiB), read_buffer_size (128 KiB), read_rnd_buffer_size (256 KiB), join_buffer_size (256 KiB), thread_stack (~292 KiB). Setting sort_buffer_size=256M with max_connections=1000 reserves up to 256 GB of address space.
• Thread pool : thread_handling=pool-of-threads. Default OFF on Unix, ON on Windows. thread_pool_size defaults to CPU-core count. KB warns "the max value is 100000. However, it is not a good idea to set it that high."
• sync_binlog=1 is durable but slow. sync_binlog=0 defers to OS flush cadence ; binlog can lag the data file on crash. 1 is the only correct value for financial systems with binlog replication.
• ALWAYS scale innodb_log_file_size up on write-heavy systems. A common starting point is ~25% of buffer-pool size, capped by recovery-time tolerance (larger log = slower crash recovery).

Decision Tree : "Tune a fresh production instance"