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 = 0innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G
innodb_thread_concurrency = 7innodb_read_io_threads = 64
innodb_write_io_threads = 64key_buffer_size = 32M
low_priority_updates = 1
concurrent_insert = 2max_connections = 1000
back_log = 512
thread_cache_size = 100
thread_stack = 192Kinteractive_timeout = 180
wait_timeout = 180max_execution_time = 30000
innodb_sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 3M
read_rnd_buffer_size = 4M
sort_buffer_size = 4Mtable_definition_cache = 40000
table_open_cache = 40000
open_files_limit = 60000max_heap_table_size = 128M
tmp_table_size = 128Mft_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
Categories
Subscribe Now
10,000 successful online businessmen like to have our content directly delivered to their inbox. Subscribe to our newsletter!Archive Calendar
Sat | Sun | Mon | Tue | Wed | Thu | Fri |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 | 31 |
Recent Articles
-
Posted on : Jul 25
-
Posted on : Jul 07
-
Posted on : Apr 07
-
Posted on : Mar 19
Optimized my.cnf configuration for MySQL 8 (on cPanel/WHM servers)
Tags
- layer 7
- tweak
- kill
- process
- sql
- Knowledge
- vpn
- seo vpn
- wireguard
- webmail
- ddos mitigation
- attack
- ddos
- DMARC
- server load
- Development
- nginx
- php-fpm
- cheap vpn
- Hosting Security
- xampp
- Plesk
- cpulimit
- VPS Hosting
- smtp
- smtp relay
- exim
- Comparison
- cpu
- WHM
- mariadb
- encryption
- sysstat
- optimize
- Link Building
- apache
- centos
- Small Business
- VPS
- Error
- SSD Hosting
- Networking
- optimization
- DNS
- mysql
- ubuntu
- Linux