InnoDB buffer pool配置【Mysql InnoD

缓冲池的配置

可以从缓冲池几个不同方面的配置来提升性能。

• 应该尽可能把缓冲池设置得大一些，但要保证服务器上的其他进程分配足够的内存，否则容易出现太多的换页空间。缓冲池设置越大，InnoDB就越像in-memory数据库，一次磁盘读取，多次内存访问。

缓冲池参数：
innodb_buffer_pool_size参数用于设置缓冲池的大小，其值必须为chunk size的整数倍。
innodb_buffer_pool_chunk_size参数用于设置chunk size，默认128M。

mysql> show variables like '%buffer_pool%size';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_size       | 134217728 |
+-------------------------------+-----------+
2 rows in set (0.00 sec)

mysql> set global innodb_buffer_pool_size=134217728+1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%buffer_pool%size';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_size       | 268435456 |
+-------------------------------+-----------+
2 rows in set (0.00 sec)

以上示例中可以看出，设置值可以不为chunk size的整数倍，但实际生效的值会自动调整为chunk szie的整数倍。

• 在64位系统上，可以将缓冲池划分成多个部分来减少并发操作带来的内存结构的争用。

  多缓冲池实例配置：

  对于缓冲池大小上GB级别的系统，把缓冲池划分成多个独立的实例可以提升并发性能，因为减少了不同线程对数据缓存页的读写争用。
  innodb_buffer_pool_instances系统变量设置缓存实例的个数，默认为1，最多可设为64，修改需要重启数据库
  innodb_buffer_pool_size系统变量必须要大于等于1G，上面的多实例配置才会生效。

• 可以改变预读的方式，线性预读或随机预读

• 可以控制缓冲池后台刷新到磁盘的频率以及刷新速率是否基于负载动态调整

  缓冲池刷脏：

  InnoDB会执行一些后台任务，脏页刷新任务是其中之一。当缓冲池中脏页（改变过的数据页）所占百分比达到innodb_max_dirty_pages_pct_lwm系统变量值定义的低水位线（low wark mark简称LWM），InnoDB就会开始刷新脏页到磁盘。另外，如果有大量密集的DML操作，导致脏页的产生速度比刷新磁盘的速度更快，当脏页的百分比持续增涨达到innodb_max_dirty_pages_pct系统变量的值时，InnoDB会为更加激烈地进行刷脏操作，占用大量IO。

  InnoDB可以使用算法来动态调整刷脏速率，基于redo log产生日志的速度和当前的刷脏速率来调整刷脏速率。InnoDB采用循环的方式使用redo log文件，当一个redo log文件写满需要切换时，被覆盖重用的log文件里的条目对应的脏数据必须确保已被写入了磁盘，此时会尽可能快地将脏数据写入磁盘，这个过程叫sharp checkpoint，若是写密集的应用任务，如果刷脏的速度跟不上，被重用的redo log里脏页过多，sharp checkpoint容易占用过多IO（类似于脏页达到innodb_max_dirty_pages_pct时的激烈刷脏）。为了减少这种情况的发生，InnoDB采用了一种启发式算法来动态调整刷脏速率，根据缓冲池中脏页的数量变化和redo生成的速率，来决定每秒刷新脏页的数量。这种自适应算法能够很好地处理负载的突然增加，使IO性能更稳定。控制这个功能是否开启的系统变量为innodb_adaptive_flushing，默认为开启。

• 微调InnoDB缓冲池刷新

  • innodb_flush_neighbors

    控制刷新一个脏页时是否刷新同一个区（extent）的其他脏页。这个功能对于传统HDD存储设备来说，可以减少磁盘寻道消耗的时间，从而减少IO消耗。但对于SSD磁盘，寻道速度非常快，大约是HDD的50~100倍，因此不能从这个功能上明显受益，建议关闭此功能，以减少没必要的开销。

  • innodb_lru_scan_depth

    规定page cleaner线程扫描每个缓冲池实例LRU列表来刷新脏页的深度.

  • 可以配置InnoDB保留缓冲池状态，以避免重启Mysql后数据预热期太长。