mysql缓冲池_Mysql数据库InnoDB缓冲池（BufferPool）

1.InnoDB缓冲池简介

InnoDB在内存中维护一个叫做缓冲池的存储区域来缓存数据和索引。了解InnoDB缓冲区如何工作&＃xff0c;如何利用它来在内存中保存被频繁访问的数据&＃xff0c;对Mysql调优至关重要。可以对InnoDB缓冲的各方面进行配置来改善Mysql的性能。

1)理想来讲&＃xff0c;可以将缓冲池设置为实际需要的值&＃xff0c;为服务器上运行的其他进程保留足够内存以防发生过度也交换。缓冲池越大&＃xff0c;InnoDB就越像一个内存库&＃xff0c;从磁盘上读取一次数据&＃xff0c;后续只从内存中读取数据。

2)大内存64位系统&＃xff0c;可以把缓冲池分为多个部分&＃xff0c;以便最小化并发操作时对内存结构的冲突。

3)尽管会突然增加类似备份或报表类的活动&＃xff0c;但依然可以将频繁访问的数据保存在内存中。

4)预期不久将需要某些数据页&＃xff0c;则可以控制InnoDB将数据页预先异步读入缓冲池的时间和方式。

5)可以控制后台进程何时将脏页刷出到磁盘&＃xff0c;以及InnoDB是否基于工作负载动态调整刷出脏页的频率。

6)可以对InnoDB缓冲池刷新各方面行为进行微调一提高性能。

7)可以配置InnoDB保留当前的缓冲池状态&＃xff0c;以避免服务器重启后发生过长的温启动时间。也可以在在服务器运行时保存当前的缓冲池状态。

2.Innodb缓冲池LRU算法

Innodb将缓冲池作为链表并通过最近最少使用(LRU)的变种算法进行管理。当需要为缓冲池增加新页时&＃xff0c;InnoDB对最近最少使用的页进行驱逐&＃xff0c;并将新页增加到链表的中部。这种“中间点插入策略”将链表看做两个子链表&＃xff1a;

1)链表头部&＃xff0c;为最近被存取过的“新”(或“年轻”)页的子链表。

2)链表尾部&＃xff0c;为最近较少被存取的“旧”页的子链表。

这个算法把经常被查询使用的页保持在新的子链表中。旧的子链表则包含较少用到的页&＃xff1b;这些页时将来被驱逐的候选页。LRU算法默认操作如下&＃xff1a;

1)缓冲池的3/8用作旧的子链表。

2)链表中点是新的子链表尾部与旧的子链表头部衔接的边界。

3)当InnoDB往缓冲池读入一个页时&＃xff0c;会将该页插入到链表的中点(旧的子链表的头部)。当用户类似查询的某个特定操作需要数据页或InnoDB进行预读操作时&＃xff0c;InnoDB会将其读入缓冲池。

4)存取就的子链表中的页会使其变的“年轻”&＃xff0c;因为会将其移向缓冲池的头部(新的子链表的头部)。如果因为需要而将一个页读入缓冲池&＃xff0c;则立即发生首次读取并使其变得年轻。如果因为预读而将一个页读入缓冲池&＃xff0c;

则并不立即发生首次存取(也许在该页被驱逐前根本就不会被读取)。

5)随着数据库操作的进行&＃xff0c;缓冲池中未被存取的页通过移向链表尾部而变老。其他也变新的同事&＃xff0c;新旧子链表中的页变老。随着页被插入链表中点&＃xff0c;旧子链表中的页也会变老。最终&＃xff0c;长时间未被使用的页会到达旧子链表尾部而被驱逐。

默认的&＃xff0c;查询读取的页会立即移到新子链表&＃xff0c;这意味着它们将会更长时间留在缓冲池中。表扫描(像mysqldump操作或没有where子句的select语句引导致的)能将大量数据带入缓冲池&＃xff0c;并驱逐同等数量的旧数据&＃xff0c;即使新数据还从未用过。类似的&＃xff0c;被后台进程预读加载的页&＃xff0c;接着仅被存取一次就移到新链表的头部。这些情况会频繁的将被用过的页推到就子链表&＃xff0c;从而将被驱逐。

InnoDB标准监视输出在属于缓冲池LRU算法的BUFFER POOL AND MEMORY部分包含几个域。

3.InnoDB缓冲池配置选项

有几个配置选项会影响InnoDB缓冲池的不通方面。

1)innodb_buffer_pool_size&＃xff1a;确定缓冲池的大小。如果缓冲池很小且系统有足够的内存&＃xff0c;设置较大的缓冲池能通过减少查询访问InnoDB表需要的磁盘I/O来改善性能。innodb_buffer_pool_size为动态选项&＃xff0c;其允许对缓冲池大小进行配置而无需重启服务器。

2)innodb_buffer_pool_chunk_size&＃xff1a;定义InnoDB缓冲池改变大小时的块(chunk)大小。

3)innodb_buffer_pool_instances&＃xff1a;将缓冲池分为用户指定数目的独立区&＃xff0c;每个有自己的LRU链表和相关的数据结构&＃xff0c;以减少并发内存读写期间的冲突。该选项仅当将innodb_buffer_pool_size设置为1GB及更高值时才会起作用。指定的缓冲池的总大小会被分到所有的缓冲池实例。为了达到最好性能&＃xff0c;指定innodb_buffer_pool_instances和innodb_buffer_pool_size选项的组合值&＃xff0c;以便每个缓冲池实例至少达到1GB。

4)innodb_old_blocks_pct&＃xff1a;为用作旧子链表的缓冲池指定一个近似比例。可以指定的范围为5~95。默认值为37(即&＃xff0c;缓冲池的3/8)。

5)innodb_old_blocks_time&＃xff1a;指定首次访问后插入旧子链表的页&＃xff0c;至少在旧子链表停留多少毫秒才能被移入新子链表。如果该值为0&＃xff0c;旧子链表中的页被首次访问后立即被移到新子链表&＃xff0c;无论存取发生时该页在旧子链表中已停留多久。如果该值大于0&＃xff0c;则首次访问后页必须停留在旧子链表中至少指定的毫秒数后&＃xff0c;再次发生访问时才能移出旧子链表。例如&＃xff1a;指定该值为1000将导致页首次访问后停留在旧子链表1秒&＃xff0c;才可以被移到新子链表。将innodb_old_blocks_time设置为大于0的值将防止一次表扫描所用的页塞满新子链表。一次扫描读进页中的数据行被连续不断的访问&＃xff0c;但以后不再被用。如果innodb_old_blocks_time设置为大于

处理该页所用时间的值&＃xff0c;则该页一直停留在“旧”子链表中&＃xff0c;并很快老化到旧子链表尾部和被驱逐。通过这种方式&＃xff0c;用于一次性扫描的页并不会影响新子链表中频繁使用的页。innodb_old_blocks_time可以动态设置&＃xff0c;因此&＃xff0c;可以在执行表扫描和导出时临时改变该选项的值。

SET GLOBAL innodb_old_blocks_time &＃61; 1000;

... 执行扫描表的查询 ...

SET GLOBAL innodb_old_blocks_time &＃61; 0;

如果打算通过用一张表的数据填充缓冲池来进行“热身”&＃xff0c;则该策略并不适合。例如:基准测试经常在服务器启动时执行一个表或索引扫描&＃xff0c;因为一段时间日常使用后&＃xff0c;这些数据通常将在缓冲池内。这种情况下&＃xff0c;让innodb_old_blocks_time保持为0&＃xff0c;至少热身阶段完成前保持为0。

6)innodb_read_ahead_threshold&＃xff1a;控制InnoDB将页预读进缓冲池的线性预读的灵敏度。

7)innodb_random_read_ahead&＃xff1a;开启将页预读进缓冲池的随机预读技术。随机预读是不管页的读取顺序&＃xff0c;基于已在缓冲池中的页来判断其他页不久将被用到的一项技术。innodb_random_read_ahead默认为禁用。

8)innodb_adaptive_flushing&＃xff1a;确定是否基于工作负载动态调整缓冲池中脏页的刷出率。动态调整页刷出率为了避免突然爆发的I/O活动。该选项默认为开启。

9)innodb_adaptive_flushing_lwm&＃xff1a;表示启用自适应刷出时重做日志容量百分比的低水位线。

10)innodb_flush_neighbors&＃xff1a;确定从缓冲池刷出页时是否也刷出同一个范围内的其他脏页。

11)innodb_flushing_avg_loops&＃xff1a;InnoDB保持先前计算的刷出状态快照的迭代次数&＃xff0c;用于控制针对负载变化自适应刷出的反应速度。

12)innodb_lru_scan_depth&＃xff1a;影响缓冲池刷出操作的算法和试探法的一个参数。主要用于性能专家对I/O密集的负载进行调试。该参数指定&＃xff0c;每个缓冲池实例&＃xff0c;page_cleaner线程寻找刷出的脏页时要对缓冲池LRU链表进行多深的扫描。

13)innodb_max_dirty_pages_pct&＃xff1a;InnoDB尽力从缓冲中刷出数据以便脏页的百分比不超过该值。指定一个0~99的整数值。默认值为75。

14)innodb_max_dirty_pages_pct_lwm&＃xff1a;开启预刷出控制脏页率时表示脏页百分比的低水位线。默认值0彻底禁用预刷出行为。

15)innodb_buffer_pool_filename&＃xff1a;指定一个用于保存innodb_buffer_pool_dump_at_shutdown或innodb_buffer_pool_dump_now产生的表空间ID和页ID列表的文件名。

16)innodb_buffer_pool_dump_at_shutdown&＃xff1a;为了缩短下次重启时温启动过程的耗时&＃xff0c;指定mysql服务器关闭时是否记录缓冲池中缓冲的页。

17)innodb_buffer_pool_load_at_startup&＃xff1a;指定在mysql服务器启动时通过加载较早时间缓冲的相同页来自动进行缓冲池热身。该选项典型与innodb_buffer_pool_dump_at_shutdown一起使用。

18)innodb_buffer_pool_dump_now&＃xff1a;立即记录缓冲池中缓冲的页。

19)innodb_buffer_pool_load_now&＃xff1a;通过加载一套数据页来立刻进行缓冲池热身&＃xff0c;而非等待服务器重启。用于基准测试期间将缓冲内存带回一个已知状态&＃xff0c;或运行报表查询或维护后将mysql服务器恢复成其通常的工作负载。该选项典型与innodb_buffer_pool_dump_now一起使用。

20)innodb_buffer_pool_dump_pct&＃xff1a;指定每个缓冲池中读出和导出的最近被使用页的百分比。其值范围为1~100。

21)innodb_buffer_pool_load_abort&＃xff1a;中断innodb_buffer_pool_load_at_startup或innodb_buffer_pool_load_now触发的缓冲池内容恢复进程。