Introduction

This document provides the recommended MariaDB settings for optimal performance. It is intended for system administrators and database engineers setting up new installations.

Overview Table of Recommended Settings

MySQL / MariaDB Configuration Summary

[mysqld]
innodb_buffer_pool_size = 5G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_io_capacity = 2000
innodb_io_capacity_max = 5000
max_connections = 500
tmp_table_size = 64M
max_heap_table_size = 64M
join_buffer_size = 8M
query_cache_type = 1
query_cache_size = 64M

InnoDB Buffer Pool Size (innodb_buffer_pool_size)

Purpose

Determines how much memory InnoDB can use for caching data and indexes.

Recommended Value

• 50-75% of total RAM (for dedicated MySQL servers).

• Example for an 8GB RAM system:

  innodb_buffer_pool_size = 5368709120; -- 5GB

Impact

• Higher values improve performance for read-heavy workloads.

• Too low values can lead to excessive disk reads, slowing down queries.

Check System

To check how the database server behaves with the buffer, you can read the current setting with this command:

SHOW ENGINE INNODB STATUS;

This will print out the current InnoDB status.

What is interesting for us is the part about the BUFFER POOL AND MEMORY.

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4294967296
Dictionary memory allocated 29275032
Buffer pool size   257536
Free buffers       183261
Database pages     74275
Old database pages 27397
Modified db pages  5447
Percent of dirty pages(LRU & free pages): 2.115
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 1067518, not young 889010
0.00 youngs/s, 0.00 non-youngs/s
Pages read 70243, created 6819, written 22388
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 74275, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

It will show what is the current value of the buffer size Buffer pool size.

How many bytes are still available Free buffers.

And how many queries are handled over the buffer Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000.

The pool hit rate should be 1000/1000 if not the pool size is too short.

InnoDB Log File Size (innodb_log_file_size)

Purpose

Controls the size of InnoDB redo logs.

Recommended Value

• 256MB to 2GB, depending on transaction volume.

• Example:

  innodb_log_file_size = 1073741824; -- 1GB

Impact

• A larger size improves transaction commit speed but increases recovery time after crashes.

InnoDB Flush Log at Transaction Commit (innodb_flush_log_at_trx_commit)

Purpose

• Controls when logs are written to disk.

Recommended Value

• 1 = Safest, but slowest.

• 2 = Balanced (flushes logs to OS, reducing disk writes).

• Example:

  innodb_flush_log_at_trx_commit = 2;

Impact

• 1 ensures durability but adds disk overhead.

• 2 improves performance without major data loss risks.

InnoDB I/O Capacity (innodb_io_capacity)

Purpose

• Determines how many IOPS InnoDB can perform for background tasks (flushing dirty pages, index merges, etc.).

Recommended Value

• HDD: 200-400

• SSD: 1000-2000

• Example:

  innodb_io_capacity = 2000;
  innodb_io_capacity_max = 5000;

Impact

• Low values cause slow disk writes, leading to performance spikes.

• High values improve write performance and prevent bottlenecks.

Max Connections (max_connections)

Purpose

• Defines the maximum number of concurrent connections MySQL can handle.

Recommended Value

• 350-500 for most workloads.

• Example:

  max_connections = 500;

Impact

• Too low can cause "Too many connections" errors.

• Too high wastes memory if unused.

Temporary Table & Sorting Buffers

Purpose

• Defines memory allocation for temporary tables and sorting operations.

Recommended Value

• tmp_table_size & max_heap_table_size: 64MB

• join_buffer_size: 8MB (or more if complex joins are common).

• Example:

  tmp_table_size = 67108864; -- 64MB
  max_heap_table_size = 67108864; -- 64MB

Impact

• Low values cause queries to use disk instead of memory, slowing execution.

• Higher values allow larger temporary tables in RAM, improving performance.

Query Cache (query_cache_type & query_cache_size)

Purpose

• Stores query results in memory to speed up repeated SELECT queries.

• Only relevant for MariaDB, as MySQL 5.7+ removed query cache.

Recommended Value

• query_cache_type: 1 (Enabled for MariaDB, OFF for MySQL 5.7+)

• query_cache_size: 64MB (Can be adjusted based on workload)

• Example:

  query_cache_type = 1;
  query_cache_size = 67108864; -- 64MB

Impact

• Enabling it improves performance for read-heavy workloads.

• Disabling it prevents cache fragmentation and lock contention in high-concurrency environments.

• Too large a cache may cause overhead due to query cache invalidation.