1、InnoDB buffer pool设置 Buffer pool是内存中用来缓存数据和索引的存储区域，其是MySQL性能调优的重要一环。 理想情况下，设置的size越大，则缓存到内存的数据越多，InnoDB就越像是内存数据库。 Buffer pool的底层是一个列表，通过LRU算法进行数据页的换进换出操作。当空间原因导致新页的加入需要换出一页时，InnoDB取出最近最少使用的页并将这个新的数据页加入到列表的中央。从方向上看，列表的头部是最常使用的数据页，而在尾部则是最少使用的数据页。 Buffer pool中3/8的部分是保存最少使用的数据页，而中央部分其实是经常使用和最少使用的结合点。当在最少使用中保存的数据页被访问时，则数据页就会被移动到列表的头部变成最常使用的。 配置大小 InnoDB buffer pool的大小可以在启动时配置，也可以在启动之后配置。 增加和减少buffer pool的大小都是以大块的方式，块的大小由参数innodb_buffer_pool_chunk_size决定，默认为128M。 Innodb_buffer_pool_size的大小可以自行设定，但必须是innodb_buffer_pool_chunk_size 乘以 innodb_buffer_pool_instances的整数倍，如果不是，则buffer pool会被调整成大于设定值且最接近的一个值，如下例： shell> mysqld --innodb_buffer_pool_size=9G --innodb_buffer_pool_instances=16   mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024; +------------------------------------------+ | @@innodb_buffer_pool_size/1024/1024/1024 | +------------------------------------------+ | 10.000000000000 | +------------------------------------------+ Innodb_buffer_pool_chunk_size可以自行设定，且增加和减少都要以M为单位，并只能在启动前修改，修改后的值*innodb_buffer_pool_instances不能大于buffer pool的大小，否则修改无效。 innodb_buffer_pool_instances可以开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。 innodb_buffer_pool_instances 参数显著的影响测试结果，特别是非常高的 I/O 负载时。 实验环境下， innodb_buffer_pool_instances=8 在很小的 buffer_pool 大小时有很大的不同，而使用大的 buffer_pool 时，innodb_buffer_pool_instances=1 的表现最棒。 [mysqld] innodb_buffer_pool_chunk_size=134217728 buffer pool的大小可以动态修改，用set语句直接修改，当语句发起时，会一直等到当前所有的事务结束后才执行，而一旦执行则执行过程中的其他事务如果要访问buffer pool就会等待语句执行完毕。 mysql> SET GLOBAL innodb_buffer_pool_size=402653184; 当执行online的调整大小时，可以通过error log或者innodb_buffer_pool_resize_status查看进度 mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status'; +----------------------------------+----------------------------------+ | Variable_name | Value | +----------------------------------+----------------------------------+ | Innodb_buffer_pool_resize_status | Resizing also other hash tables. | +----------------------------------+----------------------------------+ 2、配置多个buffer pool实例 当buffer pool的大小是GB级别时，将一个buffer pool分割成几个独立的实例能降低多个线程同时读写缓存页的竞争性而提高并发性。通过innodb_buffer_pool_instances参数可以调整实例个数。如果有多个实例，则缓存的数据页会随机放置到任意的实例中，且每个实例都有独立的buffer pool所有的特性。 Innodb_buffer_pool_instances的默认值是1，最大可以调整成64。 3、Making the Buffer Pool Scan Resistant 新读取的数据页被插入到buffer pool的LRU列表的中间位置，默认位置是从尾部开始算起的3/8的位置。当被放入buffer pool的页被第一次访问时就开始往列表的前方移动，而这样列表的后部就是不经常访问的页甚至是从不访问的页。 通过参数innodb_old_blocks_pct可以控制列表中”old”数据页所占的百分比，默认是37%，等同于3/8，取值范围是5~95。 Innodb_old_blocks_time参数默认是1000毫秒，指定了页面读取到buffer pool后但没有移动到经常被访问列表位置的时间窗口。 4、InnoDB buffer pool预存取（read-ahead） Read ahead是异步地预先获取多个数据页到buffer pool的IO操作，这些数据页都是假定会随后被用到的。InnoDB通过两种read-ahead算法提高IO性能： 线性read ahead：预测哪些页会被顺序访问。通过innodb_read_ahead_threshold参数调整顺序数据页的数量。当从一个区中顺序读取的页数量大于等于innodb_read_ahead_threshold时，innodb会触发异步read ahead操作将真个区都读到buffer pool中。该参数的默认值是56，取值范围是0~64。 随机read ahead：通过已经在buffer pool中的数据页来预测哪些页会被随后访问到。如果13个连续的处于相同区的页存在于buffer pool中，则InnoDB会把同一个区的其它页都读取进来。通过设置innodb_random_read_ahead=ON来开启此方式。 通过执行show engine innodb status命令显示的三个参数判断read-ahead算法的有效性： Innodb_buffer_pool_read_ahead Innodb_buffer_pool_read_ahead_evicted Innodb_buffer_pool_read_ahead_rnd 5、InnoDB buffer pool flushing配置 Innodb会在后台将buffer pool中的脏页(已经修改但没有写到数据文件)flush掉。当buffer pool中的脏页所占百分比达到innodb_max_dirty_pages_pct_lvm会触发flush，当所占比例达到innodb_max_dirty_pages_pct时，则innodb会“强烈”的flush。 针对数据修改操作频繁的系统，flush可能会严重滞后导致有大量的buffer pool内存占用，有一些参数专门针对修改繁忙的系统可以调整： Innodb_adaptive_flushing_lwm：为防止redo log被填满，此参数设置一个阈值，如果redo log的容量超过此阈值，则执行adaptive flush操作。 Innodb_max_drity_pages_pct_lwm Innodb_io_capacity_max Innodb_flushing_avg_loops 6、重置buffer pool状态 InnoDB可以通过配置innodb_buffer_pool_dump_at_shutdown参数来确保在mysql正常重启时部分经常使用的数据页能直接加载到buffer pool中，通过批量加载的方式，以节省重启mysql导致的warmup时间（原先在buffer pool中的数据页要从磁盘再次加载到内存中）。 Buffer pool的状态可以在任意时刻被保存，而重置状态也可以恢复任意保存的副本。 在数据库运行期间动态配置buffer pool数据页保留占比的方式是： SET GLOBAL innodb_buffer_pool_dump_pct=40; 而在配置文件中的配置方法为： [mysqld] innodb_buffer_pool_dump_pct=40 配置当服务器关闭时保存buffer pool的当前状态的方法是： SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON; 当服务器开启时重新加载buffer pool的方法是： mysqld --innodb_buffer_pool_load_at_startup=ON; 默认情况下innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup两个配置是开启状态 在关闭MySQL时，会把内存中的热数据保存在磁盘里ib_buffer_pool文件中，位于数据目录下。 数据库运行期间保存和重新加载buffer pool的方法是： SET GLOBAL innodb_buffer_pool_dump_now=ON; SET GLOBAL innodb_buffer_pool_load_now=ON; 查看buffer pool保存和重新加载的进度的方法是： mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'; 监控buffer pool的状态情况 通过show engine innodb status命令可以查看buffer pool的运行情况