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
log_bin = OFF

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.

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.