Question MariaDB 10.6 — MySQL Tuner Questions!

[Gene Steinberg]

Server operating system version

AlmaLinux 8.7

Plesk version and microupdate number

Version 18.0.50 Update #2

I have a relatively freshly-minted server running AlmaLinux 8.7, with 64GB RAM and a pair of terabyte SSDs.

After updating to MariaDB 10.6.x, I configured the my.cnf file to represent a prior server with a similar setup:

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

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

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
# skip-name-resolve=1
query_cache_size=0
query_cache_type=0
local-infile=0
innodb_buffer_pool_size=1024M
query_cache_size=64M
performance_schema=ON
tmp_table_size=96M
max_heap_table_size=96M
unix_socket=OFF
table_definition_cache=2400
key_buffer_size=1024M
innodb_buffer_pool_size=3G
innodb_log_file_size=750M
max_connections=300

!includedir /etc/mysql/releem.conf.d

Several days have passed, and I have run the MySQL Tuner.

I'd like some guidance about what to change/add/remove form my.cnf based on these suggestions:

We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See MySQL: Welcome
(specially the conclusions at the bottom of the page).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
skip-name-resolve=1
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 64M)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 96M)
max_heap_table_size (> 96M)
table_definition_cache (2400) > 3113 or -1 (autosizing if supported)
key_buffer_size (~ 195M)
innodb_buffer_pool_size (>= 3.3G) if possible.

NOTE: skip-name-resolve=1 produces a database error in our backup logs, which is why it is disabled.

Thanks for any further assistance. I am obviously name a database guru.

 

[Gene Steinberg]

Nobody?

 

[Dave W]

Not really related to Plesk?

 

[Gene Steinberg]

Well the database software is part of a Plesk install after all.

 

[Dave W]

Yes, but database tuning is more related to a database forum than a hosting control panel forum. You would get better information from Mariadb forums.

 

[Gene Steinberg]

Feel free to recommend one. I see a few.

 

[Peter Debik]

Database Administrators Stack Exchange

Q&A for database professionals who wish to improve their database skills and learn from others in the community

dba.stackexchange.com

 

[zed2007]

In that case one more question about databases. How would you recommend optimizing/configuring the databases for the fastest Mysqldump during backups in Plesk?

 

[trialotto]

In that case one more question about databases. How would you recommend optimizing/configuring the databases for the fastest Mysqldump during backups in Plesk?

@zed2007

To be honest, unless you have a monster of a database (which is quite unlikely with a Plesk instance), the default "configuration" is sufficient and optimization is not at all necessary ......

....... so, no worries!

Kind regards....

PS If you do have a monster of a database, then you should also ask the question whether Plesk or Plesk managed databases are right for the purpose.

 

[trialotto]

I have a relatively freshly-minted server running AlmaLinux 8.7, with 64GB RAM and a pair of terabyte SSDs.

After updating to MariaDB 10.6.x, I configured the my.cnf file to represent a prior server with a similar setup:

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

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

[mysqld]
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = 127.0.0.1
# skip-name-resolve=1
query_cache_size=0
query_cache_type=0
local-infile=0
innodb_buffer_pool_size=1024M
query_cache_size=64M
performance_schema=ON
tmp_table_size=96M
max_heap_table_size=96M
unix_socket=OFF
table_definition_cache=2400
key_buffer_size=1024M
innodb_buffer_pool_size=3G
innodb_log_file_size=750M
max_connections=300

!includedir /etc/mysql/releem.conf.d

Several days have passed, and I have run the MySQL Tuner.

I'd like some guidance about what to change/add/remove form my.cnf based on these suggestions:

We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See MySQL: Welcome
(specially the conclusions at the bottom of the page).
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
skip-name-resolve=1
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 64M)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 96M)
max_heap_table_size (> 96M)
table_definition_cache (2400) > 3113 or -1 (autosizing if supported)
key_buffer_size (~ 195M)
innodb_buffer_pool_size (>= 3.3G) if possible.

NOTE: skip-name-resolve=1 produces a database error in our backup logs, which is why it is disabled.

Thanks for any further assistance. I am obviously name a database guru.

@Gene Steinberg

I agree with @Dave W - this is not the place for DBA optimization.

Nevertheless, when having a quick look at your modifications to the MySQL config, I can only ask myself and you : what (the he*&) typ of databases are you running ...... and when are you killing your server with overloads and overusage of resources?!?

MySQL Tuner is a nice tool, but it will give you indications (not rock-solid recommendations) that will change from day to day.

Only a well-seasoned DBA admin can make cheese out of ...... well, MySQL.

In essence, MySQL configuration, tuning and optimization is complex : each change of an individual variable will have a butterfly effect ...... and often requires changes to the entire config (and each and every change can be relaxed or be restricted, depending on the situation of the day or hour).

As a general rule of thumb, the best recommendation is : stick to the default config, as provided with Plesk.

After reverting to the default config, run MySQL Tuner (and only the latest variant).

Based upon the MySQL Tuner recommendations, start making very small changes to the config and only to the most simple config variables, like variables as query_cache_limit and query_cache_size, ONLY after having read (and full understood) the manuals and documentation for MySQL configuration.

Why am I emphasizing to change only the simplest of simple things?

Well, your current configuration has some issues (query_cache_size variable is assigned a value twice) or some obsolete things (unix_socket variable).

In essence, reverting to Plesk default config is the better option ....... one can start again, with a clean slate ...... and if and only if it is necessary, one can tweak the config in order to improve the MySQL performance sligthly (without "blowing up" the MySQL server and the server itself).

Again, MySQL Tuner is handy, but not very useful - documentation like MySQL Database gives you a much better idea of what is happening and what should not be happening on the level of the MySQL server. MariaDB also has some documentation, but that is not very user-friendly in comparison to the MySQL docs.

I hope the above helps a bit....

Kind regards...

 

[zed2007]

@trialotto , Thank you for Answer.
But still. On some domains I had problem with backup. I added the following lines to /etc/my.cnf
[mysqldump]
skip-lock-tables
single-transaction
And it helped. What do you think abut it?

 

[trialotto]

@trialotto , Thank you for Answer.
But still. On some domains I had problem with backup. I added the following lines to /etc/my.cnf
[mysqldump]
skip-lock-tables
single-transaction
And it helped. What do you think abut it?

@zed2007

Again, you should stick to what is known AFTER you have fully understood what is happening under the bonnet.

A simple command like grep -Ri /etc/mysql would have given you insight that the [mysqldump] directive is part of /etc/mysql/conf.d/mysqldump.cnf .

In essence, you should stick as much as possible to the original config.

In this case, this would be translated to :

1 - change into the directory of /etc/mysql/conf.d

2 - create a backup of the original config : cp mysqldump.cnf mysqldump.cnf.old

3 - modify ONLY the value of the config variable max_allowed_packet to 64M (default : 16M)

and that should be give an improvement already.


Please do not mess about with any database server if you do not know what you are doing ........ any database corruption can make the full server inoperable (and not restorable) and each and every (small or large) change in database config with increase the chance that the database gets corrupted.


It is a simple fact that databases - certainly on the Plesk level and/or when using Plesk - are not the root cause of most problems.

In most cases, performance should be gained at the "front side" : at the Apache level, the Nginx (reverse proxy) level, the WordPress level etc.

And yes, I am specifically leaving out "at the PHP level" - PHP (even though not the most efficient programming language) is not often the culprit, in almost every case it is how the programmer has implemented PHP ........ WordPress are a good example of that : often causing issues, as result of badly coded plugins - not related to PHP, but to the (bad) implementation of PHP (by a programmer).


Again, it has to be emphasized that you should stick to the default MySQL config as much as possible!


Kind regards....

 

[zed2007]

@trialotto , Thanks for the advice. Yes, you are right. In 95 % of all my cases everything wrks for me with the default config.