MYSQL 8.0.33 8 核 64G my.cnf 配置

[Aluhao]

自建 MYSQL, 1 主 3 从，前环境使用的 my.cnf 配置文件，准备把它迁移到 AWS 去，
innodb_io_capacity 是根据当前系统购买配置时给的参数；
innodb_buffer_pool_size 是内存的 75%；
innodb_redo_log_capacity 64G 内存建议 4G
其它有些参数调整不知道是不是合理。

[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
server_id = 100
#bind_address = 127.0.0.1
basedir = /usr/local
datadir = /var/db/mysql
tmpdir = /var/db/mysql_tmpdir
log_error = /var/db/mysql/error.log
replica_load_tmpdir = /var/db/mysql_tmpdir
secure_file_priv = /var/db/mysql_secure
authentication_policy = caching_sha2_password
explicit_defaults_for_timestamp = ON
max_connections = 2000
max_connect_errors = 3000
max_allowed_packet = 512M
gtid_mode = ON
enforce_gtid_consistency = ON
innodb_file_per_table = ON
#innodb_dedicated_server = ON
innodb_sort_buffer_size = 64M
innodb_buffer_pool_size = 48G
innodb_redo_log_capacity = 4G
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_log_buffer_size = 64M
innodb_data_home_dir = /var/db/mysql
innodb_log_group_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_io_capacity = 3000
innodb_io_capacity_max = 6000
innodb_adaptive_hash_index = OFF
sync_binlog = 1
sync_relay_log = 1
tmp_table_size = 32M
max_heap_table_size = 32M
thread_stack = 512K
thread_cache_size = 30
key_buffer_size = 256M
read_buffer_size = 8M
sort_buffer_size = 4M
join_buffer_size = 4M
read_rnd_buffer_size = 4M
binlog_cache_size = 16M
binlog_expire_logs_auto_purge = OFF
binlog_expire_logs_seconds = 3280000000
log_replica_updates = ON
log_bin = master
relay_log = relay
#relay_log_purge = ON
#relay_log_recovery = ON
skip_name_resolve = ON
net_buffer_length = 32k
net_retry_count = 16380
#read_only = ON
#super_read_only = ON
#performance_schema = OFF
event_scheduler = OFF
mysqlx = OFF
mysqlx_port = 33060
mysqlx_socket = /tmp/mysqlx.sock
mysqlx_bind_address = 127.0.0.1,::1
[mysqldump]
max_allowed_packet = 1G
quote_names
quick

上面的配置是已经上线稳定运行 1 年左右，因为要考虑搬迁，新的调整配置如下：

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
server_id = 100
# bind_address = 127.0.0.1
basedir = /usr/local
datadir = /var/db/mysql
tmpdir = /var/db/mysql_tmpdir
log_error = /var/db/mysql/error.log
replica_load_tmpdir = /var/db/mysql_tmpdir
secure_file_priv = /var/db/mysql_secure
authentication_policy = caching_sha2_password
explicit_defaults_for_timestamp = ON

# Performance related settings
max_connections = 888
max_connect_errors = 2000
max_allowed_packet = 1G
innodb_file_per_table = ON
innodb_sort_buffer_size = 64M
innodb_buffer_pool_size = 48G
innodb_redo_log_capacity = 4G
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_log_buffer_size = 64M
innodb_data_home_dir = /var/db/mysql
innodb_log_group_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_io_capacity = 3000
innodb_io_capacity_max = 6000
innodb_adaptive_hash_index = OFF
tmp_table_size = 128M
max_heap_table_size = 128M
# thread_stack = 1M
# thread_cache_size = 30
# sort_buffer_size = 4M
# join_buffer_size = 4M
# read_buffer_size = 8M
# read_rnd_buffer_size = 4M
# net_buffer_length = 32k
net_retry_count = 16380
key_buffer_size = 256M

sync_binlog = 1
sync_relay_log = 1
relay_log = relay
# relay_log_purge = ON
# relay_log_recovery = ON
log_bin = master
log_replica_updates = ON
binlog_cache_size = 16M
binlog_expire_logs_auto_purge = OFF
binlog_expire_logs_seconds = 3280000000
gtid_mode = ON
enforce_gtid_consistency = ON

skip_name_resolve = ON
event_scheduler = OFF
mysqlx = OFF
mysqlx_port = 33060
mysqlx_socket = /tmp/mysqlx.sock
mysqlx_bind_address = 127.0.0.1,::1
# read_only = ON
# super_read_only = ON
# performance_schema = OFF

[mysqldump]
max_allowed_packet = 1G
quote_names
quick

max_connections 根据业务并发情况进行调整，设置过大会提前占用更多内存。

MYSQL 启用大页面支持，huge page 能给 MySQL 带来性能提升。
my.cnf [mysqld] 添加 large_pages = 1
不同的系统也需在相应的设置；

innodb_buffer_pool_instances = 48
计算公式：
mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;

[atonganan]

max_allowed_packet = 512M
不够用把 。

[ab]

搭车问一下，如有重复项，是前置的优先，还是前置的？

[atonganan]

@ab 同一区域的重复项，最后一个生效

[ab]

@atonganan 谢谢

[Aluhao]

@atonganan
max_allowed_packet = 512M
设置过大会不会引发什么问题，我看文档有建议设置成 1G

[SunsetYe]

迁移到 AWS 的话可以考虑直接用 RDS ？

[Aluhao]

@SunsetYe RDS 太贵了

[MoMMM]

请教下新的配置中，注释掉诸如 join_buffer_size 等一系列相关的 buffer_size 的考虑是什么？

[Aluhao]

@MoMMM 缓冲区设置较大可能会触发将这些缓冲区分页到磁盘，会极大地减慢数据库的速度并造成瓶颈。

[Aluhao]

@MoMMM MySQL 的文档警告说：较大的值可能会显着减慢内存分配速度。

[E1n]

max_allowed_packet = 512M 合理吗默认数值是 1G 吧，Mysql InnoDB Cluster 方案要求为 2G ，现在生产是 2G:)