Konfigurasi MySQL yang tepat adalah fondasi dari performa database yang optimal. Setelah mengaudit puluhan production servers, saya telah mengidentifikasi pola-pola konfigurasi yang berhasil dan gagal. Artikel ini berbagi my.cnf optimal untuk berbagai skenario production.

Analisis Kebutuhan Sebelum Konfigurasi

1. Identifikasi Workload Type

-- Analisis current queries
SELECT 
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT/1000000000 as avg_latency_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC
LIMIT 10;

Workload Categories:
– OLTP: Transaksional, banyak write, high concurrency
– OLAP: Analytical, complex queries, full table scans
– Mixed: Kombinasi OLTP dan OLAP

2. Hardware Assessment

# Cek resources
cat /proc/cpuinfo | grep processor | wc -l  # CPU cores
free -h  # RAM
df -h  # Disk space
cat /sys/class/block/sda/queue/rotational  # 0=SSD, 1=HDD

Base Configuration untuk Semua Production Servers

1. Basic Settings

[mysqld]
# Server identity
server-id = 1
bind-address = 0.0.0.0
port = 3306

# Directories
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake

# Timezone
default-time-zone = '+07:00'

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

# Binary log untuk replication dan point-in-time recovery
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
max_binlog_size = 1G
sync_binlog = 1

2. Security Settings

[mysqld]
# Local infile disabled
local_infile = 0

# Symbolic links
symbolic-links = 0

# Skip networking jika hanya local
# skip-networking

# Require secure transport
require_secure_transport = ON

# SSL Configuration
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

Memory Configuration

1. Buffer Pool Sizing

[mysqld]
# Formula: 70-80% dari total RAM untuk InnoDB buffer pool
# Contoh untuk server dengan 16GB RAM:
innodb_buffer_pool_size = 12G

# Contoh untuk server dengan 32GB RAM:
# innodb_buffer_pool_size = 24G

# Contoh untuk server dengan 64GB RAM:
# innodb_buffer_pool_size = 48G
# innodb_buffer_pool_instances = 4

# Chunk size untuk online resize
innodb_buffer_pool_chunk_size = 128M

2. Connection Memory

[mysqld]
# Connection settings
max_connections = 200
max_user_connections = 50

# Per-connection buffers
join_buffer_size = 256K
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K

# Thread cache
thread_cache_size = 50
thread_pool_size = 16  # Untuk MariaDB

3. Additional Memory Settings

[mysqld]
# Table cache
table_open_cache = 4000
table_definition_cache = 2000
table_open_cache_instances = 16

# Query cache (MySQL 5.7, removed di 8.0)
query_cache_type = 0
query_cache_size = 0

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

InnoDB Configuration

1. Core InnoDB Settings

[mysqld]
# Storage engine
default_storage_engine = InnoDB

# File format dan row format
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_default_row_format = DYNAMIC

# Page size
innodb_page_size = 16384

# Buffer pool
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1

2. InnoDB I/O Settings

[mysqld]
# Flush behavior
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_doublewrite = 1

# I/O capacity
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# Page cleaners
innodb_page_cleaners = 4

# Dirty pages
innodb_max_dirty_pages_pct = 75
innodb_max_dirty_pages_pct_lwm = 10

3. InnoDB Transaction Settings

[mysqld]
# Transaction log
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M

# Transactions
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = 0
innodb_autoinc_lock_mode = 2

# Deadlock detection
innodb_deadlock_detect = ON
innodb_print_all_deadlocks = 1

Thread dan Concurrency Configuration

1. Thread Settings

[mysqld]
# Concurrency
innodb_thread_concurrency = 0  # 0 = unlimited
innodb_concurrency_tickets = 5000
innodb_commit_concurrency = 0
innodb_replication_delay = 0

# Adaptive flushing
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 10

# Adaptive hash index
innodb_adaptive_hash_index = ON
innodb_adaptive_hash_index_parts = 8

2. Purge dan History

[mysqld]
# Purge settings
innodb_purge_threads = 4
innodb_purge_batch_size = 300

# Change buffer
innodb_change_buffering = all
innodb_change_buffer_max_size = 25

# Read ahead
innodb_read_ahead_threshold = 56
innodb_random_read_ahead = OFF

Replication Configuration (Jika Menggunakan)

1. Master Configuration

[mysqld]
# Server ID
server-id = 1

# Binary log
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
max_binlog_size = 1G
sync_binlog = 1

# GTID (recommended)
gtid_mode = ON
enforce_gtid_consistency = ON

# Replication
log_slave_updates = ON
binlog_gtid_simple_recovery = ON
binlog_expire_logs_seconds = 604800

2. Slave Configuration

[mysqld]
server-id = 2
log_bin = mysql-bin
relay_log = mysql-relay-bin
read_only = 1

# Replication performance
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
slave_preserve_commit_order = ON

Monitoring dan Logging Configuration

1. Performance Schema

[mysqld]
# Enable performance schema
performance_schema = ON

# Enable specific instruments
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
performance-schema-instrument='memory/%=COUNTED'

2. Error dan Slow Query Logging

[mysqld]
# Error logging
log_error_verbosity = 3

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# General log (jika debugging, disable di production)
# general_log = 0

Configuration untuk Berbagai Workloads

1. High Concurrency OLTP

[mysqld]
max_connections = 500
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 16
innodb_thread_concurrency = 0
innodb_lock_wait_timeout = 30
innodb_deadlock_detect = ON
thread_cache_size = 100
table_open_cache = 8000

2. Read-Heavy Analytics

[mysqld]
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 8
innodb_read_io_threads = 8
innodb_write_io_threads = 4
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
query_cache_type = 0

3. Write-Heavy Logging

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 16G
innodb_log_file_size = 2G
innodb_log_buffer_size = 128M
innodb_write_io_threads = 8
innodb_read_io_threads = 2

Applying Configuration

1. Testing Configuration

# Test configuration
sudo mysqld --verbose --help | grep -A 1 "Default options"

# Atau
sudo mysqld --defaults-file=/etc/mysql/my.cnf --validate-config

2. Restart dengan New Config

# Restart MySQL
sudo systemctl restart mysql

# Verifikasi variables
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -e "SHOW VARIABLES LIKE 'max_connections';"

3. Monitoring After Changes

-- Check status
SHOW GLOBAL STATUS LIKE 'Uptime';
SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- Monitor buffer pool
SHOW ENGINE INNODB STATUS\G

Kesimpulan

Konfigurasi MySQL yang optimal memerlukan:
– Understanding workload characteristics
– Proper memory allocation
– I/O optimization
– Security hardening
– Monitoring setup

Dengan konfigurasi ini, Anda memiliki fondasi yang solid untuk performa production-grade.

Cara Optimize MySQL Server Config untuk Production Environment