MariaDB Configuration File Parameter Guide and Example for a 4GB Server

MariaDB Configuration File Parameter Guide

This guide explains common core parameters in MariaDB/MySQL configuration files. Note that specific values should be adjusted based on server hardware, data volume, and workload.

General & Connection Parameters

• key_buffer_size: Caches indexes for the MyISAM storage engine. If primarily using InnoDB, set to a small value like 64MB.
• max_allowed_packet: Maximum packet size allowed for server-client communication. Too small may cause operation failures; too large poses risks. Typically set to 16M or higher.
• table_open_cache: Number of file descriptors for open tables. Too small causes frequent file open/close; too large may exhaust system descriptors. Adjust based on concurrent connections.
• max_connections: Maximum concurrent connections. Too high may cause memory exhaustion. Small/medium sites: 200-500; large/high-concurrency apps may need more.
• max_connect_errors: Maximum connection errors allowed. Increase to prevent false client blocking due to network fluctuations.
• open_files_limit: Maximum files the process can open. Must exceed table_open_cache and respect system limits.
• thread_cache_size: Number of idle threads cached. Reduces thread creation/destruction overhead.

Query & Sort Buffers

• sort_buffer_size: Buffer for ORDER BY/GROUP BY on non-indexed columns. Per-connection. Large values (e.g., several MB) consume memory; 256K-1M is usually sufficient. Adding indexes is the best solution.
• read_buffer_size: Buffer for sequential scans (e.g., full table scans). Per-connection. 128K-256K is often enough.
• read_rnd_buffer_size: Buffer for random reads (e.g., reading rows after sorting by non-indexed columns). Per-connection. Typically 256K-512K.
• query_cache_size: Note: Query cache is deprecated from MariaDB 10.1.7 / MySQL 5.7.20 and removed in later versions. For older versions, enable (query_cache_type=1) for read-heavy workloads, but disable (query_cache_type=0, query_cache_size=0) for write-heavy scenarios to avoid performance overhead from cache invalidation.
• tmp_table_size: Maximum size for in-memory temporary tables. Larger tables convert to disk-based MyISAM. Set with max_heap_table_size.

InnoDB Storage Engine Core Parameters

• innodb_buffer_pool_size: Most important parameter. Caches InnoDB table data and indexes. On dedicated DB servers, set to 60%-80% of available RAM. For a 4GB server, at least 1G-2G.
• innodb_file_per_table: When set to 1 (default), each InnoDB table uses a separate .ibd file. Recommended for easier management, backup, and space reclamation.
• innodb_flush_log_at_trx_commit: Controls transaction log durability.
  • 1 (default): Write and flush to disk on every commit. Safest, lower performance.
  • 2: Write on commit, flush to disk once per second. May lose ~1 second of data on OS crash.
  • 0: Write and flush once per second. Highest performance, may lose up to 1 second of data on crash.

  Balance based on data safety and performance needs.

• innodb_log_file_size: Size of a single redo log file. Larger files reduce checkpointing and improve write performance but increase crash recovery time. Typically ~25% of innodb_buffer_pool_size.
• innodb_log_buffer_size: Redo log buffer size. Increase (e.g., 16M-64M) for heavy update workloads to reduce disk I/O.
• innodb_lock_wait_timeout: InnoDB row lock wait timeout (seconds). Transactions exceeding this are rolled back. Default 50 seconds; adjust per application.

Binary Logging & Replication

• log-bin: Enables binary logging for replication and point-in-time recovery.
• binlog_format: Binary log format: STATEMENT (SQL-based), ROW (row-based), MIXED (mixed). MIXED is a common choice.
• server-id: Unique ID required for each server in a replication topology.
• expire_logs_days: Automatically purge binary logs older than this many days to prevent disk filling. Recommended 7-14 days.

Example Configuration for a 4GB RAM Server

Below is a reference configuration snippet for a 4GB RAM MariaDB server primarily using InnoDB. Adjust paths and values as needed.

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user        = mariadb
basedir     = /usr/local/mariadb
datadir     = /data/mysql
log_error   = /data/mysql/mariadb.err
pid-file    = /data/mysql/mariadb.pid
skip-external-locking

# General
key_buffer_size         = 64M
max_allowed_packet      = 16M
table_open_cache        = 512
sort_buffer_size        = 1M
read_buffer_size        = 256K
read_rnd_buffer_size    = 512K
thread_cache_size       = 32
tmp_table_size          = 64M
max_heap_table_size     = 64M

# Connections & Limits
max_connections         = 300
max_connect_errors      = 10000
open_files_limit        = 65535

# Binary Logging
log-bin                 = mysql-bin
binlog_format           = mixed
server-id               = 1
expire_logs_days        = 10

# InnoDB
default_storage_engine          = InnoDB
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 2G
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_flush_log_at_trx_commit  = 2
innodb_lock_wait_timeout        = 30

Important: After modifying the configuration file (usually my.cnf or my.ini), restart the MariaDB/MySQL service for changes to take effect. Test significant parameter changes (especially innodb_buffer_pool_size) in a staging environment first.