MySQL 8.x服务器参数调优

mysql 8.0.x 服务器调优

服务器主要参数

以8核16G为优化参考。

参数	默认值	建议值	备注
innodb_read_io_threads	4	8	全局只读参数
innodb_buffer_pool_size	134217728	2147483648	全局参数。缓存innodb表的索引，数据，插入数据时的缓冲。默认值是128M，建议调大到物理内存的40% ~ 70%，建议值：2G
innodb_buffer_pool_chunk_size	134217728		默认值：128M
innodb_log_buffer_size	16777216	67108864	全局参数。InnoDB存储引擎的事务日志所使用的缓冲区。默认值为：16M，建议值：64M
innodb_flush_log_at_trx_commit	1	2	全局参数。事务刷新机制。性能：1<2<0，事务安全：1>2>0
sort_buffer_size	262144	1048576	每个需要排序的线程分配该大小的一个缓冲区。默认值为256K，建议值：1M
join_buffer_size	262144	1048576	每个需要联表查询的线程分配该大小的一个缓冲区。默认值为256K，建议值：1M
read_buffer_size	262144	1048576	每个线程连续扫描时为扫描的每个表分配的缓冲区的大小，默认值为256K，建议值：1M
thread_cache_size	9	16	线程池缓存线程数量的大小。通过show global status like '%threads%';
back_log	151	151	用于控制MySQL监听TCP端口时设置的积压请求 栈大小。
sync_binlog	1	0	全局参数。https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html

代码

set PERSIST innodb_buffer_pool_size = 2147483648;
set PERSIST sort_buffer_size = 4194304;
set PERSIST join_buffer_size = 4194304;
set PERSIST read_buffer_size = 4194304;
set PERSIST innodb_flush_log_at_trx_commit = 2;
SET PERSIST sync_binlog = 0;
set PERSIST binlog_cache_size=1048576;
set PERSIST binlog_stmt_cache_size = 1048576;

多线程优化

my.cnf

[mysqld]
innodb_write_io_threads=16
innodb_parallel_read_threads=16
innodb_purge_threads=16
innodb_read_io_threads=16
innodb_ddl_threads=16
innodb_log_file_size=512M
innodb_buffer_pool_instances=16
innodb_buffer_pool_size=2147483648
innodb_flush_log_at_trx_commit=2
mysqlx_min_worker_threads=8
thread_cache_size=64
sort_buffer_size=8388608
join_buffer_size=8388608
read_buffer_size=8388608
read_rnd_buffer_size=1M
sync_binlog=0
binlog_cache_size=2M
binlog_stmt_cache_size=2M
innodb_log_buffer_size=67108864
tmp_table_size=512M
max_heap_table_size=512M
back_log=512
transaction_isolation=READ-COMMITTED
max_connections=256

注：

• 以上的16是CPU核心数
• transaction_isolation需结合应用场景设置，OLAP场景多时：READ-COMMITTED，OLTP场景多时：REPEATABLE-READ。
• back_log需少于等于：cat /proc/sys/net/ipv4/tcp_max_syn_backlog

启动时：

nohup ./bin/mysqld --defaults-extra-file=./my.cnf --user=mysql --basedir=./ --datadir=./data/  &

统计

通过

show global status

可以查看相关的状态统计：如：

show global status like 'bin%';

其他：

show status like '%threads%';
SHOW ENGINE INNODB STATUS;

参数大全：

https://dev.mysql.com/doc/refman/8.4/en/dynamic-system-variables.html
https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_sync_binlog

explain的执行计划

type的性能排序

system,const,eq_ref,ref,fulltext,ref_or_null,index_merge,unique_subquery,index_subquery,range,index,All