MariaDB / MySQL Tweaks As Default & Option

I recently had a support ticket where my web site had gone down, the admin panel was inaccessible, and all manner of other things were broken (FTP). Many services in a cyberpanel install depend on MySQL.

The default MySQL config file (/etc/my.cnf) is bare-bones. There are really no options in it. This lead to my innodb-powered sites to suffer near 100% CPU usage on a high load site. I came up with a fix for it that served as good tweaks for both myisam and innodb. Here’s what I came up with:

[mysqld]
user= mysql
pid-file= /var/lib/mysql/mysql.pid
socket= /var/lib/mysql/mysql.sock
port= 3306
datadir= /var/lib/mysql

open_files_limit=50000
max_allowed_packet=268435456

skip-name-resolve=1
innodb_buffer_pool_size=1G

innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_old_blocks_time=1000
innodb_open_files=5000

key_buffer_size=16M
read_buffer_size=256K
read_rnd_buffer_size=256K
query_cache_size=256M
query_cache_limit=5M
join_buffer_size=4M
sort_buffer_size=4M
max_heap_table_size=64M
tmp_table_size=64M
table_open_cache=4500
table_definition_cache=4000
thread_cache_size=50

This caused problems though. I believe the reason my services went down was the line: skip-name-resolve=1

After removing that line, everything seems to be working fine now. So I make this suggestion, that the default my.cnf for a clean cyberpanel install be the following:

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe

[mysqld]
user= mysql
pid-file= /var/lib/mysql/mysql.pid
socket= /var/lib/mysql/mysql.sock
port= 3306
datadir= /var/lib/mysql

open_files_limit=50000
max_allowed_packet=268435456

innodb_buffer_pool_size=1G

innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_old_blocks_time=1000
innodb_open_files=5000

key_buffer_size=16M
read_buffer_size=256K
read_rnd_buffer_size=256K
query_cache_size=256M
query_cache_limit=5M
join_buffer_size=4M
sort_buffer_size=4M
max_heap_table_size=64M
tmp_table_size=64M
table_open_cache=4500
table_definition_cache=4000
thread_cache_size=50

[mysqld1]
user= mysql
pid-file= /var/lib/mysql1/mysql.pid
socket= /var/lib/mysql1/mysql.sock
port= 3307
datadir= /var/lib/mysql1

open_files_limit=50000
max_allowed_packet=268435456

innodb_buffer_pool_size=1G

innodb_log_file_size=256M
innodb_flush_method=O_DIRECT
innodb_io_capacity=1000
innodb_old_blocks_time=1000
innodb_open_files=5000

key_buffer_size=16M
read_buffer_size=256K
read_rnd_buffer_size=256K
query_cache_size=256M
query_cache_limit=5M
join_buffer_size=4M
sort_buffer_size=4M
max_heap_table_size=64M
tmp_table_size=64M
table_open_cache=4500
table_definition_cache=4000
thread_cache_size=50

That way, everyone starts out with a good myisam and innodb setup. I took that config from someone who runs a large shared hosting setup. Then, in the future we could have a tweak page similar to the ones for php and the web server.

TensorTom, thanks for these settings!

I just used some of them, first of all increased innodb_log_file_size to 1G.
AND removed multi_instance which isn’t necessary I guess (mysqld_multi - MariaDB Knowledge Base).

Default settings would be difficult to decide I suppose due to variety of hosts confs.

1 Like

Is this setting still valid? What was server configuration

OS -
RAM -
CPU-

What parameter needs to be changed if RAM is 32GB and 8vCPU?

2 Likes

One more point – [mysqld_multi] ?? Why did you use it?
Any benefits on CyberPanel?

1 Like