MySQL 8.0 InnoDB Production Performance Tuning Configuration

Supanote Core Team @supanote · 1 min read

plain (conf) 13 hours ago · 35 lines · 6 views

# MySQL 8.0 / Percona Server Production InnoDB Tuning
# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# Basic Network & Connection Settings
bind-address            = 127.0.0.1
max_connections         = 500
max_connect_errors      = 10000
wait_timeout            = 600
interactive_timeout     = 600

# InnoDB Buffer Pool Tuning (Allocate 60-75% of dedicated RAM)
innodb_buffer_pool_size         = 4G
innodb_buffer_pool_instances     = 4
innodb_redo_log_capacity        = 1G
innodb_log_buffer_size          = 64M

# I/O & Concurrency
innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
innodb_io_capacity              = 2000
innodb_io_capacity_max          = 4000
innodb_read_io_threads          = 4
innodb_write_io_threads         = 4

# Slow Query Log
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/mysql-slow.log
long_query_time                 = 0.5
log_queries_not_using_indexes   = 0

# Security
local_infile                    = 0
skip_symbolic_links             = 1

1# MySQL 8.0 / Percona Server Production InnoDB Tuning

2# /etc/mysql/mysql.conf.d/mysqld.cnf

4[mysqld]

5# Basic Network & Connection Settings

6bind-address = 127.0.0.1

7max_connections = 500

8max_connect_errors = 10000

9wait_timeout = 600

10interactive_timeout = 600

12# InnoDB Buffer Pool Tuning (Allocate 60-75% of dedicated RAM)

13innodb_buffer_pool_size = 4G

14innodb_buffer_pool_instances = 4

15innodb_redo_log_capacity = 1G

16innodb_log_buffer_size = 64M

18# I/O & Concurrency

19innodb_flush_method = O_DIRECT

20innodb_flush_log_at_trx_commit = 2

21innodb_io_capacity = 2000

22innodb_io_capacity_max = 4000

23innodb_read_io_threads = 4

24innodb_write_io_threads = 4

26# Slow Query Log

27slow_query_log = 1

28slow_query_log_file = /var/log/mysql/mysql-slow.log

29long_query_time = 0.5

30log_queries_not_using_indexes = 0

32# Security

33local_infile = 0

34skip_symbolic_links = 1

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note