MYSQL 8.0.33 8 核 64G my.cnf 配置

自建 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 条附言 · 2023-11-13 18:13:10 +08:00

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

第 2 条附言 · 2023-11-13 18:14:04 +08:00

[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

第 3 条附言 · 2023-11-13 18:15:51 +08:00

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

第 4 条附言 · 2023-11-13 20:38:28 +08:00

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

第 5 条附言 · 2023-11-14 20:27:41 +08:00

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

11 条回复 • 2023-12-22 21:31:22 +08:00