MySQL5.7.31性能优化记录

一、配置优化

1、innodb_buffer_pool_instances

范围是 1 (the default) up to 64 (the maximum).

可以将 innodb_buffer_pool_instances 的个数设置为 buffer pool size 的 十分之一&＃xff0c; 比如 innodb_buffer_pool_size 是 30g &＃xff0c;那 innodb_buffer_pool_instances 就设置为 3&＃xff1b;

2、innodb_buffer_pool_size 

因为有额外内存的使用&＃xff0c;如果指定 innodb_buffer_pool_size 为 12g 实际占用内存可能是 14g &＃43;

二、事务日志配置

innodb_log_file_size

默认值 48MB
设置的太小&＃xff1a;比如用默认值48MB&＃xff0c;当一个日志文件写满后&＃xff0c;innodb会自动切换到另外一个日志文件&＃xff0c;而且会触发数据库的检查点&＃xff08;Checkpoint&＃xff09;&＃xff0c;这会导致innodb缓存脏页的小批量刷新&＃xff0c;会明显降低innodb的性能。

设置的太大&＃xff1a;设置很大以后减少了checkpoint&＃xff0c;并且由于redo log是顺序I/O&＃xff0c;大大提高了I/O性能。但是如果数据库意外出现了问题&＃xff0c;比如意外宕机&＃xff0c;那么需要重放日志并且恢复已经提交的事务&＃xff0c;如果日志很大&＃xff0c;那么将会导致恢复时间很长。甚至到我们不能接受的程度。

总结&＃xff1a; innodb_log_file_size设置得太小无法释放数据库性能&＃xff0c;设置得太大&＃xff0c;会增加宕机后日志重放恢复的时间。

innodb_log_files_in_group

重做日志组中的日志数量&＃xff0c;默认值是2 &＃xff0c;一般用默认值也可以。

innodb_log_buffer_size

将日志写入磁盘日志文件前的缓冲大小&＃xff0c;默认值 8MB&＃xff0c;一般用默认值也可以。

三、读写线程增加

innodb_write_io_threads
innodb_read_io_threads
innodb_thread_concurrency

四、示例

[mysql]
# 设置mysql客户端默认字符集
default-character-set&＃61;utf8
[mysqld]bind-address &＃61; 193.193.193.41skip-name-resolve
#设置3306端口
port &＃61; 3306
# 设置mysql的安装目录
basedir&＃61;/opt/soft/mysql57
# 设置mysql数据库的数据的存放目录
datadir&＃61;/opt/soft/mysql57/data
# 允许最大连接数
max_connections&＃61;1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server&＃61;utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine&＃61;INNODB
lower_case_table_names&＃61;1
max_allowed_packet&＃61;16M# 优化配置参数&＃xff0c;实际物理内存&＃xff1a;8G
innodb_buffer_pool_size&＃61;4096M
innodb_log_file_size &＃61; 512M
innodb_flush_log_at_trx_commit &＃61; 2# 禁用ssl
skip-ssl# 慢日志
slow_query_log&＃61;ON
slow_query_log_file&＃61;/var/log/mysql/slow_query2.log
long_query_time&＃61;0.2 #单位秒

注意&＃xff1a;如果配置重启mysql后慢sql不生效&＃xff0c;也可以临时命令启用如下&＃xff1a;

mysql> set global slow_query_log&＃61;1;

# 查看设置的参数是否生效
mysql> show variables like &＃39;%query%&＃39;;