Configuration Guide

Base Configuration Components

  • Connection and Network
  • Buffer and Memory Management
  • InnoDB Storage Engine
  • Logging and Monitoring
  • Security Settings

RAM-Based Configurations

2GB RAM Server

[mysqld]
# Connection Settings
max_connections = 100
max_allowed_packet = 128M
wait_timeout = 600
interactive_timeout = 600

# Buffer and Memory
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
join_buffer_size = 1M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
tmp_table_size = 64M
max_heap_table_size = 64M

# InnoDB Settings
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 200
innodb_io_capacity_max = 400

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_error = /var/log/mysql/error.log

# Security
secure_file_priv = NULL
validate_password.policy = STRONG
validate_password.length = 12

8GB RAM Server

[mysqld]
# Connection Settings
max_connections = 300
max_allowed_packet = 256M
wait_timeout = 600
interactive_timeout = 600

# Buffer and Memory
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 5
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
tmp_table_size = 256M
max_heap_table_size = 256M

# InnoDB Settings
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_error = /var/log/mysql/error.log

# Security
secure_file_priv = NULL
validate_password.policy = STRONG
validate_password.length = 12

16GB RAM Server

[mysqld]
# Connection Settings
max_connections = 500
max_allowed_packet = 512M
wait_timeout = 600
interactive_timeout = 600

# Buffer and Memory
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
join_buffer_size = 8M
sort_buffer_size = 8M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
tmp_table_size = 512M
max_heap_table_size = 512M

# InnoDB Settings
innodb_log_file_size = 2G
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_error = /var/log/mysql/error.log

# Security
secure_file_priv = NULL
validate_password.policy = STRONG
validate_password.length = 12

Log Bin Trust

log_bin_trust_function_creators = 1

Performance Notes

  1. innodb_buffer_pool_size: 70-80% of available RAM
  2. innodb_buffer_pool_instances: 1 per GB of buffer pool
  3. innodb_flush_log_at_trx_commit = 1: Full ACID compliance
  4. innodb_io_capacity: Based on storage IOPS capability

Implementation Steps

  1. Backup current configuration
  2. Apply new settings in /etc/mysql/mysql.conf.d/mysqld.cnf
  3. Validate config: mysqld --validate-config
  4. Restart MySQL: sudo systemctl restart mysql
  5. Monitor performance using: 
    SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS\G
Edit this page on GitHub
Table of Contents