MySQL 8 sample config (my.cnf example), tuning & discussion.

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 ~ 100k.

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!

Tags: mysql, observability, performance, sysadmins

Access my list of Free Linux command-line monitoring tools.