CentrioHost Blog

Stories and News from IT Industry, Reviews & Tips | Technology Blog


Optimized my.cnf configuration for MySQL 8 (on cPanel/WHM servers)

  • Category : cPanel Tutorials
  • Posted on : Mar 19, 2021
  • Views : 5,664
  • By : CentrioHost

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

Subscribe Now

10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!

Archive Calendar

SatSunMonTueWedThuFri
 1234
567891011
12131415161718
19202122232425
262728293031 

Over 20000 Satisfied Customers!

  • web hosting reviewer
    Valerie Quinn
    CTO, Acteon Group

    Centriohost staff were fantastic, I had a concern with a domain and they got back to me very quickly and they helped me to resolve the issue! ~ . . . Read more

  • Joomla hosting reviewer
    Collin Bryan
    Photographer, Allister Freeman

    I'm using centrio for my portfolio since 2006. The transition was seamless, the support was immediate, and everything works perfectly. ~ . . . Read more

  • dedicated server reviewer
    Harry Collett
    Actor, A&J Artists

    Very easy to understand & use even though I am not very technologically minded. No complications whatsoever & I wouldn't hesitate to recommend it to all. ~ . . . Read more

  • vps web hosting reviewer
    Porfirio Santos
    Technician, Diageo PLC

    Centrio support team have been amazingly responsive and helpful to any of my queries, thank you so much to the Centriohost have been amazingly responsive and helpful to any of my queries ๐Ÿ‘๐Ÿ‘๐Ÿ‘ ~ . . . Read more

  • wordpress hosting plans reviewer
    Catherine Auer
    Doctor, SmartClinics

    Anytime I've had a problem I can't solve, I've found Centriohost to be diligent and persistent. They simply won't let an issue go until the client is happy. ~ . . . Read more

  • reseller hosting reviewer
    Effectivo Social
    Freelancer, Fiverr

    Recommend their shared hosting for all my SME web design clients. Their cloud or VME offerings are too great to deal with. Pricing is perfect and suitable for all users (อ โ‰– อœส–อ โ‰–) ๐Ÿ‘Œ ~ . . . Read more

Top