MariaDB / MySQL Tweaks As Default & Option — CyberPanel - WebHosting Control Panel for OpenLiteSpeed
CyberLoader

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.

Comments

  • 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 (https://mariadb.com/kb/en/mysqld_multi/).

    Default settings would be difficult to decide I suppose due to variety of hosts confs.
  • Is this setting still valid? What was server configuration

    OS -
    RAM -
    CPU-

    What parameter needs to be changed if RAM is 32GB and 8vCPU?
  • One more point -- [mysqld_multi] ?? Why did you use it?
    Any benefits on CyberPanel?
Sign In or Register to comment.
CyberPanel Discord

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!