Optimized MariaDB/MySQL my.cnf Configuration Guide for 4GB RAM Production Servers

Introduction

This guide provides an optimized my.cnf configuration example for MariaDB/MySQL running on a 4GB RAM cloud server in a production environment. The configuration aims to balance memory usage, connection performance, and stability for small to medium web applications. Note: database tuning must be adjusted based on actual workload, data volume, and hardware. Do not copy blindly.

Core Configuration Breakdown

The configuration is divided into key sections with explanations for critical parameters.

Basic Paths and Connection Settings

First, set the following directories according to your actual installation paths. Do not copy the placeholder paths directly.

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

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user        = mysql  # or mariadb, depending on the system user created during installation
basedir     = /usr/local/mysql  # Replace with your MySQL installation directory
datadir     = /var/lib/mysql    # Replace with your data directory
log_error   = /var/log/mysql/error.log  # Replace with your error log path
pid-file    = /var/run/mysqld/mysqld.pid # Replace with your PID file path

Memory and Connection Optimization (for 4GB RAM)

This is the core tuning section for a 4GB host, controlling memory allocation and connection handling.

back_log = 384
max_connections = 300  # Reduced from 500 for 4GB memory for stability
thread_cache_size = 128  # Reduced from 256
table_open_cache = 1024
table_definition_cache = 1024

# Key memory buffer settings
key_buffer_size = 256M  # For MyISAM tables. If using mostly InnoDB, set to 64M-128M
innodb_buffer_pool_size = 1536M  # Crucial InnoDB cache, set to ~50-70% of RAM (1.5GB here)
innodb_log_file_size = 256M  # Increased from 32M, ~25% of buffer pool size
innodb_log_buffer_size = 16M

# Per-connection/thread buffers (Caution: total = per-conn memory * max_connections)
sort_buffer_size = 2M  # Increased from 256K
read_buffer_size = 1M  # Increased from 256K
read_rnd_buffer_size = 1M  # Increased from 512K
join_buffer_size = 2M  # Increased from 128K
tmp_table_size = 64M   # Reduced from 256M, works with max_heap_table_size
max_heap_table_size = 64M

# Query Cache (deprecated in MySQL 5.7+, disabled by default in MariaDB 10.1.7+)
# query_cache_type = 0
# query_cache_size = 0

InnoDB Storage Engine Configuration

If using InnoDB as the primary storage engine, these settings are critical.

default_storage_engine = InnoDB
innodb_file_per_table = ON
innodb_flush_log_at_trx_commit = 1  # Default, ensures ACID compliance, safest
innodb_lock_wait_timeout = 50
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_buffer_pool_instances = 2  # For a 1.5GB pool size, set to 2 or 4

Binary Logging and Replication

Used for data recovery and master-slave replication.

server-id   = 1
log_bin = /var/log/mysql/mysql-bin.log  # Specify the full path
expire_logs_days = 7
binlog_format = ROW  # Recommended for better data consistency

Other Optimizations and Security Settings

skip_name_resolve = ON  # Disables DNS resolution, speeds up connections
max_allowed_packet = 64M
wait_timeout = 600  # Non-interactive connection timeout (seconds)
interactive_timeout = 1800  # Interactive connection timeout (seconds)
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow.log

Configuration Verification and Activation

1. Validate Configuration Syntax: Use mysqld --verbose --help | grep -A 1 -B 1 "Default options" to see config file order, then mysqld --defaults-file=/path/to/my.cnf --validate-config to check syntax (MariaDB/MySQL 5.7+).

2. Restart Database Service: After modifying the config, restart the MariaDB/MySQL service.

3. Monitor and Adjust: After activation, monitor database status using commands like SHOW GLOBAL STATUS; and SHOW ENGINE INNODB STATUSG. Fine-tune parameters based on actual metrics (connections, cache hit rate, slow queries).

Important Notes

• Total Memory Control: The sum of all memory-related parameters (especially global and per-connection buffers) must be less than physical RAM. Leave sufficient space (typically ~1GB) for the OS and other applications.
• Version Differences: Parameters and defaults may vary between MariaDB/MySQL versions. Consult the official documentation.
• Backup Original Config: Always backup the original my.cnf file before making changes.