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

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:

This will print out the current InnoDB status.

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

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:

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:

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:

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:

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:

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:

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.