mysql5.7性能提升一百倍调优宝典( 六 ) _mysql5.7

这个我们是在全真生产环境、全数据量下用测试工具去对4k,8k,16k三种场景压测得到的吞吐量即tps来做观察的，我这边可以给出一个推荐值，以单表超1000w条数据基于中台1.1的数据库结果（每个表都超1000w），我们在设置该值为：8K时，它的吞吐达到最优。

如果不配的后果：

32位下默认为8192
64位下默认为16384

配置实例：

innodb_page_size = 8192

23)innodb_buffer_pool_size
推荐设置：

72G

作用：
这个值和innodb_buffer_pool_instances相辅相成。在32位机器下，innodb_buffer_pool_instances一般为1，在64位机器上，这个值为8-64.
pool_instances其实为cpu核数，它的作用是：
1）对于缓冲池在数千兆字节范围内的系统，通过减少争用不同线程对缓存页面进行读写的争用，将缓冲池划分为多个单独的实例可以提高并发性。
2）使用散列函数将存储在缓冲池中或从缓冲池读取的每个页面随机分配给其中一个缓冲池实例。每个缓冲池管理自己的空闲列表，刷新列表，LRU和连接到缓冲池的所有其他数据结构，并受其自己的缓冲池互斥量保护。
innodb_buffer_pool_size的设置需要为pool_instance的整数倍。
网上很多说innodb_buffer_pool_size为系统的70%，这是错的！因为你真的设了70%你的swap空间会被挤压，你不要忘了你还有os，上面还可能有监控agent端。一旦swap空间被挤压后你的mysql反面严重拖慢读写。
此处强烈建议设成内存的20%-65%间（独立的mysql服务器），为什么有一个20%呢？对于<4gb的mysql用服务器来说按照20%系统内存来设置。由于我们是128gb的内存，此处我建议使用72G,如果内存超过128gb，一般我们会把pool instance设成16个，每个开启10g左右的buffer_pool_size，对于256gb内存的服务器来说我们可以这样设。
如果不配的后果：

默认为64

配置实例：

innodb_buffer_pool_size = 72G

24)innodb_buffer_pool_instances = 8
这个参数同innodb_buffer_pool_size一起讲解了。
25)innodb_buffer_pool_load_at_startup
推荐设置：

0

作用：
这两个参数几乎没人用一般dba也不曾听说过，它是什么意思呢？Mysql在第一次（重启）时，它的buffer_pool_size中是空的，随着mysql运行时间1-2小时后，它的buffer_pool_size里开始被塞入东西，它分为old block与new block，而此时mysql性能开始一点点读写效率上去了，那是因为在buffer_pool_size没有放入东西时，mysql很多读写发生在硬盘上，从硬盘到内存的加载过程是一个比较漫长和耗时的过程，因此我们往往会设一个startup=1以加快这个“预热”过程，它与参数shutdown配合使用，即相当于把上次使用的innot_db_buffer_pool里的东西在启动时先做一次加载，以加快mysql的性能。它会在innodb的数据目录中生成一个文件：ib_buffer_pool 。高度注意：加入了startup和shutdown=1时，mysql的启动过程会比较慢，如果你上次的dump出的buffer_pool里的东西有50多g那么mysql启动时的加载过程会变得比较慢。这个值很多人使用默认的0（不开启），它的影响就是你在mysql重启后，一开始你的系统读写性能不如在你系统运行了2-4小时（视db读写而定）反而它的读写性能变好了。不设使用默认值（0）。
如果不配的后果：

不配的话系统默认为0

配置实例：

innodb_buffer_pool_load_at_startup = 0

26)innodb_buffer_pool_dump_at_shutdown
同上面的startup参数以及解说
27)innodb_lru_scan_depth
推荐设置：

2000

作用：

innodb_io_capactiy 在sas 15000转的下配置800就可以了，在ssd下面配置2000以上。
可使用默认配置。即不设。

如果不配的后果：

默认为200，db吞吐量上不去。

配置实例：

innodb_lru_scan_depth = 2000

28)innodb_lock_wait_timeout
推荐设置：

60

作用：
我们一般会碰到，mysql innodb_lock_wait_timeout这个错，这个错是慢sql导致，它代表的是慢sql的事务锁超过了mysql锁超时的设置了。默认这个值为：50s，这个值是可以动态改变的，我不建议去改这个值，因为一个sql能达50s这得多夸张？
动态改变命令如下：
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
SET GLOBAL innodb_lock_wait_timeout=500;
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';