MariaDB / MySQL Tweaks As Default & Option

CyberPanel Project Feature Request
1

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.

2

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
3

Is this setting still valid? What was server configuration

OS -
RAM -
CPU-

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

1 Like
4

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

1 Like
5

Is that still valid?

What’s the impact of this?

In what situation should it be used?

Or which ones should be active?

Is there something conflicting there in the current versions of the cyberpanel?

I like optimizations, but I’m not sure what all these lines do. :slight_smile:

6

Yes

At the time of posting this post CyberPanel uses mariadb10.3 under the hood.

1 Like
7

Hi,

Inside the “/etc/my.cnf” file it says:

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

Where exactly should I copy the settings you have shared?

My VDS Features:
10 CPU
16384 MB RAM
200 GB NVME SSD
CyberPanel version: 2.3.4

What would be the most logical change for my WordPress websites according to these features?
I would be very grateful if you could describe this in more detail.
Also Mysql is consuming too much ram.
There are 4 WordPress websites in VDS. There are no high hits on any of these sites.
Ram usage is always 75%

Ekran Resmi 2023-06-23 10.52.51
Ekran Resmi 2023-06-23 10.52.512904×1432 242 KB

Regards,
Have a nice day…

8

Is it still valid?
Also, can you answer my previous answer?

Regards

9

Hello @bahadiracar

A quick search of the forum results to this How to modify my.cnf file - #4 by shoaibkk

Kindly use the forum search tool to your advantage. Most of your qustions are already answered.

10

The information in this post is not enough for me.
The “/etc/my.cnf” file contains only:

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

There are 3 files in the “/etc/my.cnf.d” folder.
I could not find information about which of these 3 files I need to make changes.

Also, the “/etc/mysql/my.cnf” file at the address you specified is not available on my server. Even the “/etc/mysql/” folder doesn’t exist at all.

I use the forum search tool frequently. However, the steps in some posts do not apply to the latest version of CyberPanel.

11

This shows you did not care to go through the information in the links here MariaDB / MySQL Tweaks As Default & Option - #6 by josephgodwinke. Direct answer would be Configuring MariaDB with Option Files - MariaDB Knowledge Base to get the location of your option file. Its obviously different for most linux distributions.

Before changing anything kindly read more on linux systems.