Mysql全局优化

Mysql系统参数优化与 8.0版本新特性

Mysql 除了对sql 进行优化, 还有对Mysql的系统参数进行优化. 下图描述了 sql优化 , 系统 ,直至硬件的优化效果

从上图可以看出 对于sql 索引的优化效果最佳. 同时, 优化成本也是最佳.
具体sql 索引优化 见 : Mysql索引优化

下面为 Mysql的部分配置参数 及其优化思路.

#最大连接请求数
max_connections = 3000

连接 创建和 销毁都需要系统资源(如,内存,文件句柄) .ps: 常说的QPS 就是指每秒请求数,也就是说支持的并发数;
一个连接最少占用内存 为 256k 最大为 64M .如果一个请求数据为64M ,那么就会申请临时空间, 将数据放到硬盘开辟的临时空间中.
如果3000个用户同时连接mysql, 最小需要的内存大小为 3000 × 256k = 750M, 最大需要 3000 × 64M =192G.
当 innodb_buffer_pool_size 为 40GB ,给操作系统分配的空间为4G , 而 分配给连接使用的最大内存 不到20G . 当连接过多时, 使用的内存超过20G, 将会导致磁盘的SWAP(硬盘的物理内存不足时, 将硬盘内存部分空间释放,提供给当前程序使用) ,就会影响磁盘性能. 也就是说 连接数高,不一定会带来吞吐量的提升,反而会因为占用过多的系统资源,而导致效率降低;

#允许用户连接最大数量
max_user_connections=2980

剩余连接数 用于DBA进行管理

#Mysql暂存连接数量
back_log =300

当Mysql的连接数达到 max_connections 时, 新的请求会被存在堆栈中, 等待某一连接释放资源. 堆栈数量就是 back_log, 如果等待连接的数量超过 back_log,就会拒接连接.

#当客户端通过jdbc 连接mysql 并操作结束之后,等待300s后断开连接.默认时间为28800(8小时) ,单位秒
wait_timeout =300

#mysql client连接mysql进行操作完毕后，空闲300秒后断开，默认是28800(8个小时),单位秒
interactive_timeout=300

#innodb线程并发数,默认值0(不受限制)
innodb_thread_concurrency=64
#该参数如果要设置,建议设置为服务器CPU核心数相同 或者 CPU核心数的2倍. 如果值太大,可能会导致锁竞争激烈,影响性能

#innodb存储引擎buffer pool缓存大小，一般为物理内存的60%-70%
innodb_buffer_pool_size=40G

#行锁锁定时间，默认50s
innodb_lock_wait_timeout=10

#redo log 的写入策略，它有三种可能取值
innodb_flush_log_at_trx_commit =1

#bin log 写入磁盘机制
sync_binlog =1

#在 第一次connection 需要使用这个缓存空间 buffer  时, 会一次性分配.该参数不是越大越好.如果设置过大,会可能消耗掉系统的内存资源;
sort_buffer_size=4M

#表关联缓存大小 ,该参数是每个连接独享.
join_buffer_size=4M