CentrioHost Blog

Here is the optimized my.cnf Configuration for MySQL 8 (on cPanel/WHM Servers) by Niaz Mohammad who is a PHP web developer. It is quite good and if amended correctly it brings some amazing results. The settings provided below are a starting point for a 4GB - 8GB RAM server with 4-8 CPU cores. If you have less or more resources available you should adjust accordingly to save CPU, RAM and disk I/O usage. You can adjust and tweak these values on system by using database diagnostics tools like: https://github.com/major/MySQLTuner-perl or, https://launchpad.net/mysql-tuning-primer

# For advice on how to change settings please contact Centriohost Limited
# https://www.centriohost.com/contact-us/

# This Tuned mySQL Config is for MySQL 8 servers only of Centriohost Cloud Services

[client]
default-character-set=utf8

[mysql]
port = 3306
socket = /var/lib/mysql/mysql.sock
default-character-set=utf8

[mysqld]
innodb_use_native_aio = 0
disable-log-bin = 1
default-authentication-plugin = mysql_native_password
bind_address = 127.0.0.1
datadir = /var/lib/mysql
max_allowed_packet = 256M
max_connect_errors = 1000000
pid_file = /var/lib/mysql/mysql.pid
port = 3306
skip_external_locking
socket = /var/lib/mysql/mysql.sock
tmpdir = /tmp
user = mysql
event_scheduler = off
collation-server = utf8_unicode_ci
character-set-server = utf8
sql_mode=""

default_storage_engine = InnoDB
innodb_buffer_pool_instances = 8 # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size = 7G # Use up to 70-80% of RAM
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_stats_on_metadata = 0

innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G
innodb_thread_concurrency = 7

innodb_read_io_threads = 64
innodb_write_io_threads = 64

key_buffer_size = 32M

low_priority_updates = 1
concurrent_insert = 2

max_connections = 1000

back_log = 512
thread_cache_size = 100
thread_stack = 192K

interactive_timeout = 180
wait_timeout = 180

max_execution_time = 30000

innodb_sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 3M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M

table_definition_cache = 40000
table_open_cache = 40000
open_files_limit = 60000

max_heap_table_size = 128M
tmp_table_size = 128M

ft_min_word_len = 3

log_error = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes = 1
long_query_time = 5
slow_query_log = 0
slow_query_log_file = /var/lib/mysql/mysql_slow.log

[mysqldump]
quick
quote_names
max_allowed_packet = 512M