MySQL 8.0参数最佳实践

MySQL8.0.34版本my.cnf最佳实践参考实例

[client]
port                                    = 3307
socket                                  = /data/mysql/3307/run/mysql.sock
default-character-set                   = utf8mb4

[mysql]          
port                                    = 3307
socket                                  = /data/mysql/3307/run/mysql.sock
default-character-set                   = utf8mb4
prompt                                  = \\u@\\h:\\d \\r:\\m:\\s >

[mysqld]
server-id                               = 64112
port                                    = 3307
mysqlx_port                             = 33070
admin_address                           = 127.0.0.1
admin_port                              = 9999
default_storage_engine                  = InnoDB
character_set_server                    = utf8mb4

transaction_isolation                   = READ-COMMITTED
max_allowed_packet                      = 1G
max_connections                         = 1000
max_connect_errors                      = 1000000

# 机械硬盘保持默认值200即可，SSD磁盘建议调高该值（比如2000）
innodb_io_capacity                      = 2000

datadir                                 = /data/mysql/3307/data
log_bin                                 = /data/mysql/3307/binlogs/mysql-bin
socket                                  = /data/mysql/3307/run/mysql.sock
mysqlx_socket                           = /data/mysql/3307/run/mysqlx.sock 
pid-file                                = /data/mysql/3307/run/mysql.pid
log_error                               = /data/mysql/3307/logs/error.log
slow_query_log_file                     = /data/mysql/3307/logs/slow.log
tmpdir                                  = /data/mysql/3307/tmp

# 建议调整为操作系统内存的50%到60%之间
innodb_buffer_pool_size                 = 32G


# 缓存binlog event的内存，当有大事物，大的DML操作，binlog_cache_size不足以容纳所有的binlog event时，便转而使用临时文件来缓存binlog event，这样性能会下降（采用临时文件缓存binlog event 肯定比采用内存缓存binlog event慢）
# 从Binlog_cache_use和Binlog_cache_disk_use可以看出是否使用了binlog cache或binlog 临时文件用于保存binlog event。
binlog_cache_size                       = 2M

# 用于普通索引扫，范围扫，和全表扫的表连接缓冲区的大小，在MySQL 8.0.18及更高版本中，此变量还控制用于哈希联接hash join的内存量。
# 默认256KB，当无法添加索引时，增加join_buffer_size的值以获得更快的完全联接。为两个表之间的每个完整联接分配一个联接缓冲区。
# 当使用块嵌套循环（Block Nested-Loop）时，更大的联接缓冲区是有益的，因为涉及到第一张表的所有关联行的所有关联列被存储在join buffer中（会减少磁盘IO）。
# 当使用Batched Key Access BKA时，也是将外部表的相关列存入join buffer中，减少随机IO，join buffer越大，越能提高性能。
join_buffer_size                        = 8M

# 默认256K，如果内存充足，增大该值会加速排序的速度，提高查询性能。connection级参数，在每个connection需要buffer的时候，一次性分配的内存，并不是越大越好，过大的设置+高并发可能会耗尽系统内存资源。
sort_buffer_size                        = 2M

read_buffer_size                        = 2M
read_rnd_buffer_size                    = 2M



# 临时表内存大小，默认16兆，执行计划中 Using temporary 出现表明用到临时表，当临时表内存不够用的时候，会使用磁盘，性能下降，典型场景:group by.  
# 当Created_tmp_disk_tables 数值增加，说明tmp的大小不够用，进而判断是否需要增加tmp_table_size的大小。
# Created_tmp_disk_tables/Created_tmp_tables 大于一定比例（5% or 10%）,尝试调大tmp_table_size
tmp_table_size                          = 64M


# 从8.0.27版本后引入该参数，适用于创建或重新生成辅助索引的在线DDL操作，默认1兆，加大该值有理由提高在线DDL的执行时间。
innodb_ddl_buffer_size                  = 1G
 

# 从8.0.30版本引入该参数，产生不可见主键简称GIPK，在建表时没有指定主键的情况（备库库会出现全表扫描回放问题，带来非常大的主备延迟），MySQL会帮助我们创建名为 my_row_id 主键，建议主从库都设置。
sql_generate_invisible_primary_key      = ON


# 从8.0.30引入该参数，该参数为MySQL redo的大小，如果显示定义了innodb_log_file_size 和innodb_log_files_in_group 那么，innodb_redo_log_capacity= innodb_log_files_in_group * innodb_log_file_size，否则保持默认值100MB
innodb_redo_log_capacity                = 6G



innodb_data_file_path                   = ibdata1:500M;ibdata2:500M:autoextend
innodb_temp_data_file_path              = ibtmp1:500M;ibtmp2:500M:autoextend:max:5120M
innodb_flush_method                     = O_DIRECT
default-time-zone                       = '+8:00'
log_bin_trust_function_creators         = 1
innodb_adaptive_hash_index              = 0
log_timestamps                          = SYSTEM

gtid_mode                               = ON
enforce_gtid_consistency                = ON

lower_case_table_names                  = 1
report_host                             = '9.23.64.112'
relay-log                               = /data/mysql/3307/binlogs/relay-log
relay-log-index                         = /data/mysql/3307/binlogs/relay-log.index

binlog_expire_logs_seconds              = 2592000
slow_query_log                          = 1
long_query_time                         = 1

my.cnf使用示例

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &