MySQL 8 sample config (my.cnf example) and tuning.
A while back, I wrote the article Tuning MySQL my.cnf? Avoid this common pitfall! With the release of MySQL 8 I wanted to paste some of a my.cnf config file for discussion, suggestions and questions.
With the launch of the blog’s tech forums, I’m hoping that the performance tuning tips that emerge will be from all of us who manage MySQL databases. Read the first recommendation in the comments section at the end of this article.
Sample MySQL 8 Config
This my.cnf config is from a dedicated MySQL 8 server that was recently split from a web server. It averages a modest 9k QPS (queries per second), however around 5 to 10 times a year the QPS will spend a few days averaging ~ 40k QPS.
Some notable config areas are:
— max_conections
and thread_cache_size
. max_conections
is set for the 5 to 10 times per year peak-connections, while thread_cache_size
is set somewhere in between normal traffic and those peak days.
— innodb_buffer_pool_instances
is set to 48 because innodb_dedicated_server
automatically sets innodb_buffer_pool_size
to 48 GB. See the below chart:
Source: https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html
— read_buffer_size
, join_buffer_size
, sort_buffer_size
and read_rnd_buffer_size
, please read here.
— performance-schema
, read Performance Schema Benchmarks: OLTP RW.
— disable-log-bin
, read How Binary Logs Affect MySQL 8.0 Performance.
— Have questions or suggestions? Add your comment at the end of this article.
Paste of my.cnf:
[mysqld] disable-log-bin = 1 skip-name-resolve = 1 performance-schema = 0 local-infile = 0 mysqlx = 0 bind-address = [IPs removed] default-storage-engine = InnoDB open_files_limit = 200000 max_allowed_packet = 256M sql_mode = "STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION" innodb_dedicated_server = 1 innodb_buffer_pool_instances = 48 innodb_log_buffer_size = 64M innodb_read_io_threads = 12 innodb_write_io_threads = 12 innodb_stats_on_metadata = 0 innodb_file_per_table = 1 max_connections = 500 thread_cache_size = 128 table_definition_cache = 65536 table_open_cache = 65536 wait_timeout = 10 connect_timeout = 5 interactive_timeout = 30 tmp_table_size = 128M max_heap_table_size = 128M read_buffer_size = 256K join_buffer_size = 1M sort_buffer_size = 512K read_rnd_buffer_size = 512K slow-query-log = 1 long_query_time = 2 slow_query_log_file = /var/log/mysql_slow_query.log log-error = /var/log/mysql/db.[removed].com.err
MySQL server ‘status’
*for the above MySQL server.
mysql> status -------------- Server version: 8.0.30 MySQL Community Server - GPL Uptime: 37 days 5 hours 39 min 39 sec Threads: 8 Questions: 31068214993 Slow queries: 0 Opens: 36331 Flush tables: 1 Open tables: 36024 Queries per second avg: 9656.974
Other MySQL performance related reading/guides:
— Could not increase number of max_open_files to more than… (Solution).
— MySQL Performance Tuning: Tips, Scripts and Tools.
— MySQL Performance: Stop hoarding. Drop unused MySQL databases.
— “MySQL server has gone away” error – Solution(s).
— Tuning MySQL my.cnf? Avoid this common pitfall!
— Linux server performance: Is disk I/O slowing your application?
See below for MySQL 8 tuning discussion. My.cnf config questions are welcome!
First recommendation is not to depend on
innodb_dedicated_server = 1
.When
innodb_dedicated_server
is enabled,InnoDB
automatically configures the following variables:innodb_buffer_pool_size
innodb_redo_log_capacity
or, before MySQL 8.0.30,innodb_log_file_size
andinnodb_log_files_in_group
.(
innodb_log_file_size
andinnodb_log_files_in_group
are deprecated in MySQL 8.0.30. These variables are superseded by theinnodb_redo_log_capacity
variable.)innodb_flush_method
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 enablinginnodb_dedicated_server
if you run MySQL Server in a Docker container or dedicated VM that only runs MySQL.That said, with a
innodb_dedicated_server
enabled, you won’t gain any performance by usinginnodb_dedicated_server
if you instead just set the automatically configured 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.
Suggestion for MySQL 8 enable slow query log and fix those queries. Also choose InnoDB Over MyISAM as MyISAM is old news.