MySQL 8 sample config (my.cnf example) and tuning

general
, , ,
1

Originally published at: MySQL 8 sample config (my.cnf example) and tuning.

With the release of MySQL 8 I wanted to paste my tuned my.cnf (MySQL configuration file) for discussion, suggestions, and questions. To get the most out of your MySQL 8 installation, you will need to configure it correctly and tune the settings for your specific use case. In this blog post, we will discuss my…

2

First recommendation is not to depend on innodb_dedicated_server = 1.

When innodb_dedicated_server is enabled, InnoDB automatically configures the following variables:

(innodb_log_file_size and innodb_log_files_in_group are deprecated in MySQL 8.0.30. These variables are superseded by the innodb_redo_log_capacity variable.)

Only enable innodb_dedicated_server if the MySQL instance resides on a dedicated server where it can use all available system resources. For example, consider enabling innodb_dedicated_server if you run MySQL Server in a Docker container or dedicated VM that only runs MySQL.

That said, you won’t gain any performance by using innodb_dedicated_server if you instead just set the MySQL variables optimally. So, this setting is more of a convenience, as it will simply automatically configure the variables to ~ what you should be using anyway.

Add your tips below.

3

Suggestion for MySQL 8 enable slow query log and fix those queries. Also choose InnoDB Over MyISAM as MyISAM is old news.

4

Hello I have a server with mysql 8.034 with RAM 200GB cpu 18 cores i want to tuning my mysql config
I put theses values
############### my tuning ##############
join_buffer_size = 512M
innodb_buffer_pool_size = 160G
innodb_log_file_size = 512M
innodb_log_buffer_size = 512M
interactive_timeout = 200
wait_timeout = 200
skip-name-resolve=ON
do you think that values are correct because i always have full RAM used.

1 Like
5

Hi @Baba_Ndiaye

Welcome to our Linux Community :handshake:t4:

MySQL cannot truly be fined tuned without logging and analyzing at least several hours of runtime stats. (or more for less busy servers)

That said, there are some general mistakes to avoid before tuning.

For example, please read: MySQL Database Performance: Avoid this common mistake

As per the article and MySQL documentation referenced:
your join_buffer is almost certainly too high.
Your wait timeout is far too high. (also see above article)

Also see: MySQL 8 sample config (my.cnf example) and tuning.
If this is a dedicated MySQL server you should use: innodb_dedicated_server to avoid misconfig. (as per the above article)

Hope this is helpful.