Configuring MariaDB for a production server involves fine-tuning various parameters based on the specific requirements and characteristics of your workload. Below are example settings for a basic MariaDB production configuration. Please note that these values are just starting points and may need further adjustment based on your hardware specifications and workload characteristics.
[mysqld]
# Basic Settings
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
# Logging and Errors
log_error = /var/log/mysql/error.log
slow_query_log = 1
long_query_time = 2
log_queries_not_using_indexes = 1
# Security Settings
skip_name_resolve
skip_external_locking
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
default_authentication_plugin = mysql_native_password
# Connection Handling
max_connections = 1000
max_user_connections = 800
wait_timeout = 1800
# InnoDB Settings
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_open_files = 400
# Query Cache (Consider if your workload benefits from it)
query_cache_type = 0
# Performance Schema
performance_schema = ON
# Thread Pool (For MariaDB 10.3 and later)
thread_handling = pool-of-threads
thread_pool_size = 16
# Other Buffer Sizes
key_buffer_size = 256M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
# Table Cache
table_open_cache = 800
# Temporary Tables
tmp_table_size = 64M
max_heap_table_size = 64M
# Binary Log Settings (If using replication)
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
# Backups
# Set up a robust backup strategy using tools like mysqldump or Percona XtraBackup
# Enable the slow query log and log-queries-not-using-indexes for monitoring and optimization.
Please review and adjust these settings based on your specific server resources, workload characteristics, and MariaDB version. Additionally, it’s recommended to use monitoring tools and perform regular performance testing to ensure that your database is operating efficiently under production conditions.
