这是一个创建于 376 天前的主题,其中的信息可能已经有所发展或是发生改变。
自建 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
|
|
1
atonganan 2023-11-13 13:55:22 +08:00
max_allowed_packet = 512M 不够用把 。
|
|
|
2
ab 2023-11-13 14:13:11 +08:00
搭车问一下,如有重复项,是前置的优先,还是前置的?
|
|
|
4
ab 2023-11-13 14:30:12 +08:00
|
|
|
5
Aluhao 2023-11-13 16:16:31 +08:00
@ atonganan max_allowed_packet = 512M 设置过大会不会引发什么问题,我看文档有建议设置成 1G
|
|
|
6
SunsetYe 2023-11-14 16:51:14 +08:00
迁移到 AWS 的话可以考虑直接用 RDS ?
|
|
|
7
Aluhao 2023-11-14 20:26:58 +08:00
|
|
|
8
MoMMM 2023-11-15 11:20:26 +08:00
请教下新的配置中,注释掉诸如 join_buffer_size 等一系列相关的 buffer_size 的考虑是什么?
|
|
|
9
Aluhao 2023-11-16 10:10:47 +08:00 1
@ MoMMM 缓冲区设置较大可能会触发将这些缓冲区分页到磁盘,会极大地减慢数据库的速度并造成瓶颈。
|
|
|
10
Aluhao 2023-11-16 10:12:07 +08:00
@ MoMMM MySQL 的文档警告说:较大的值可能会显着减慢内存分配速度。
|
|
|
11
E1n 337 天前
max_allowed_packet = 512M 合理吗默认数值是 1G 吧,Mysql InnoDB Cluster 方案要求为 2G ,现在生产是 2G:)
|