# MySQL/MariaDB Optimized Configuration for High Performance
# Server: 4 CPU Cores, 16GB RAM
# Target: Handle thousands to millions of transactions
#
# INSTALLATION:
# 1. Buat direktori log: sudo mkdir -p /var/log/mysql
# 2. Set permission: sudo chown -R mysql:mysql /var/log/mysql
# 3. Backup: sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf /etc/mysql/mariadb.conf.d/50-server.cnf.backup
# 4. Copy: sudo cp optimized-mysql.cnf /etc/mysql/mariadb.conf.d/50-server.cnf
# 5. Restart: sudo systemctl restart mariadb

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
# ==================== BASIC SETTINGS ====================
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
user = mysql

# Directories - PASTIKAN SUDAH DIBUAT:
# sudo mkdir -p /var/log/mysql
# sudo chown -R mysql:mysql /var/log/mysql
datadir = /var/lib/mysql
tmpdir = /tmp

# Character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# ==================== INNODB SETTINGS ====================
default_storage_engine = InnoDB
innodb_buffer_pool_size = 8G        # 50% of RAM (8GB for 16GB RAM)
innodb_buffer_pool_instances = 4      # One per CPU core
innodb_log_file_size = 1G             # 1GB log file
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 2    # 0=disk async, 1=safe, 2=fast
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 0         # 0 = unlimited

# ==================== CONNECTION SETTINGS ====================
max_connections = 300                 # Increased for high traffic
max_connect_errors = 10000           # Prevent host blocking
max_allowed_packet = 64M             # Large packet support
wait_timeout = 300                   # 5 minutes
interactive_timeout = 300
connect_timeout = 30

# ==================== CACHE SETTINGS ====================
# query_cache deprecated di MariaDB 10.1+,gunakan handler cache lain
table_open_cache = 4000
table_definition_cache = 2000
thread_cache_size = 32
key_buffer_size = 128M               # For MyISAM (if used)
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 4M

# ==================== TEMP & LOG ====================
tmp_table_size = 256M
max_heap_table_size = 256M
# Error log - PASTIKAN /var/log/mysql SUDAH ADA
log_error = /var/log/mysql/error.log

# Slow query log (optional, bisa dinyalakan kalau perlu)
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 2

# ==================== NETWORK ====================
skip-name-resolve                     # Skip DNS lookup
bind-address = 0.0.0.0

# ==================== BINARY LOG (Optional - disable kalau tidak perlu replication) ====================
# Kalau mau disable binary log:
# skip-log-bin
# log_bin = /var/log/mysql/mysql-bin.log
# expire_logs_days = 7
# max_binlog_size = 100M
# binlog_format = ROW
# binlog_row_image = MINIMAL

[mysqldump]
quick
max_allowed_packet = 256M

[mysql]
no-auto-rehash