mysql5.7参数调优
1 innodb_buffer_pool_instances作用域为全局integer类型默认值为8当innodb_buffer_pool_size 1GB时为1最小值1最大值64innoDB buffer_pool缓存表和索引的内存区域划分的数量当系统的buffer_pool达到几G的时候分成多个可以通过减少线程读写缓存页的内容。从buffer_pool中读或存储的每个缓存页按hash随机分配instances。每个buffer_pool都有自身的free lists, flush lists, LRUs以及所有其它连接的数据结构并被自身的mutex保护。2 配置InnoDB Buffer Pool Size在线或者重启均可修改InnoDB缓冲池大小调整innodb_buffer_pool_size,参数时通过chunk生效chunk大小由innodb_buffer_pool_chunk_size 定义默认128M。缓冲池必须等于或者是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.的倍数。如果不是缓冲池大小会自动调整到相等或倍数。比如innodb_buffer_pool_size 设为8G, innodb_buffer_pool_instances 为16innodb_buffer_pool_chunk_size 设为128M乘积2G。如果把innodb_buffer_pool_size设为9G, 不是乘积2G的倍数这个参数就会自动调整到10G, 满足倍数要求。shell mysqld --innodb_buffer_pool_size9G --innodb_buffer_pool_instances16mysql SELECT innodb_buffer_pool_size/1024/1024/1024;------------------------------------------|10.000000000000 |------------------------------------------可在线修改无需重启。同时要修改MySQL配置文件防止重启后恢复到原来的值。mysql SET GLOBAL innodb_buffer_pool_size402653184;活动事务和调用innodb api的操作结束后参数调整才能开始开始后新的活动事务和innodb操作会等待。mysql SHOW STATUS WHERE Variable_nameInnoDB_buffer_pool_resize_status;可以通过这个命令查看调整参数的进展。修改记录也会体现到mysql的log_error日志中去3 配置InnoDB Buffer Pool Chunk Sizeinnodb_buffer_pool_chunk_size 最小1M只能在启动时指定或者修改配置文件重启生效。如果新设置的innodb_buffer_pool_chunk_size 乘以innodb_buffer_pool_instances 大于缓冲池那么这个参数会变为innodb_buffer_pool_size /innodb_buffer_pool_instances。4 max_execution_time作为hint,仅对select语句生效下例为1000毫秒也就是1秒超时。如果为0则不生效。SELECT /* MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...如果是多个select 比如union的语句则对整个语句生效且必须写在第一个select 之后不能用于存储过程。作为参数可以是全局或session级生效。可在线修改无需重启。同时要修改/etc/my.cnf防止重启后恢复到原来的值。SET GLOBAL max_execution_time600000;5 innodb_print_all_deadlocks死锁打印到错误日志里默认是关闭的6sort_buffer_size每个会话执行排序操作所分配的内存大小。想要增大max_sort_length参数需要增大sort_buffer_size参数。如果在SHOW GLOBAL STATUS输出结果中看到每秒输出的Sort_merge_passes状态参数很大可以考虑增大sort_buffer_size这个值来提高ORDER BY 和 GROUP BY的处理速度。建议设置为1~4MB。当个别会话需要执行大的排序操作时在会话级别增大这个参数。
