# Host35 : cPanel shared >1200 cpanel acct and ~1600 db # MariaDB 10.6 / 128GB RAM / 48 threads CPU / HDD Storage
# Conservative settings — prioritizes stability over throughput to avoid observed DB OOMs (limit memory usage) and HDD iowait spikes (limit multi-threading and concurrent I/O)
# Before restarting:
# sudo -u mysql mysqld --validate-config --defaults-file=/etc/my.cnf

[client-server]
!includedir /etc/my.cnf.d


[mysqld]
disable-log-bin = 1
performance-schema = 0
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

## Changed Applied by BladimirF - July 09, 2024
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-name-resolve=0
unix_socket=OFF

########### Added ##############

# -- Per-Connection Buffers --
# These are allocated per-thread, so we keep them modest.
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

# -- Global Buffers & Limits --
max_allowed_packet = 256M
max_connections = 200
thread_cache_size = 128

# Max size for internal in-memory temp tables
tmp_table_size = 128M

# User-created MEMORY tables
max_heap_table_size = 128M

# Connections and timeouts
max_user_connections = 40
wait_timeout = 300
interactive_timeout = 300
lock_wait_timeout = 100
max_connect_errors = 100
max_statement_time = 60

# -- MyISAM Settings --
key_buffer_size = 1024M

# -- Table Caches --
# verify: systemctl show mariadb.service -p LimitNOFILE
open_files_limit=80000
#table_open_cache = 40000
table_definition_cache = 40000
# Default
table_open_cache_instances = 16

# -- InnoDB Settings --
innodb_buffer_pool_size = 32G

# Large log file for write performance
innodb_log_file_size = 2G
innodb_log_buffer_size = 128M
innodb_file_per_table = 1

# -- Based on disk type HDD/SSD
innodb_flush_method = O_DIRECT

# -- HDD I/O Tuning --
# Baseline for HDD
innodb_io_capacity = 400
# Burst capacity
innodb_io_capacity_max = 800
# Recommended for HDD
innodb_flush_neighbors = 1

# -- Durability vs Performance --
# Good balance for performance, slight risk on OS crash
# innodb_flush_log_at_trx_commit = 2

# -- SQL Mode --
# Default cPanel mode is good for compatibility
sql_mode = "IGNORE_SPACE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"