14.4.3.1 The InnoDB Buffer Pool 14.4.3.2 Configuring Multiple Buffer Pool Instances 14.4.3.3 Making the Buffer Pool Scan Resistant 14.4.3.4 Configuring InnoDB Buffer Pool Prefetching (Read-Ahead) 14.4.3.5 Configuring InnoDB Buffer Pool Flushing 14.4.3.6 Fine-tuning InnoDB Buffer Pool Flushing 14.4.3.7 Saving and Restoring the Buffer Pool State 14.4.3.8 Monitoring the Buffer Pool Using the InnoDB Standard Monitor 14.4.3.1 The InnoDB Buffer Pool InnoDB 保持一个存储区域被称为buffer pool 用它cache 数据和索引 在内存里。 了解InnoDB buffer pool的工作, 并利用它来保持频繁访问的数据在内存里, 是MySQL 优化的一个重要方面 你可以配置InnoDB buffer pool的多个方面来改善性能: 1.理想情况下, 你可以设置buffer pool的大小足够大,留给足够的空间给其他的进程来运行不需要过度的分 页。 buffer pool越大, InnoDB 看起来更像一个内存数据库, 从磁盘读取一次,从内存访问数据在随后的读取中。 Buffer pool size 是通过innodb_buffer_pool_size 选项配置 2.64位系统有更大的内存大小,你可以把buffer pool 分成多个部分,以尽量减少并发操作之间的内存结构的 争用。 3. 你可以把频繁访问的数据放在内存里 4.你可以控制何时和如何InnoDB 执行预读请求来获取pages 到buffer pool 异步的进行, 预计很快需要这些pages. 5. 你可以控制当后台进程刷新脏pages 发生,是否Innodb基于负载调整刷新速度。 6.你可以配置InnoDB 来保存当前buffer pool 状态来避免一个长时间的热身阶段 在服务器重启后。 InnoDB Buffer Pool LRU Algorithm InnoDB 管理buffer pool作为一个列表, 使用变化的LRU算法。 当room 需要增加新的页到pool, InnoDB 驱逐最少使用的pages 增加新的page 到列表的中间。 这个中间点插入策略对待列表作为两个子列表 在头部,一个新的pages 列表,是被经常访问的, 在尾部, 一个老的pages 子列表最很少访问的 这个算法 保持pages 是被大量的使用用于查询在新的sublist 老的sublist 包含很少使用的页, 这些页面会被驱逐 LRU 算法操作默认如下: 1.3/8 的buffer pool 是投入到了old sublist 2.列表的中间点 是新的子列表的尾部遇到了 老的子列表的头部 3. 当InnoDB 读取 一个page 到buffer pool, 它初始插入到中间点( 老列表的头部). 一个页面可以被读取是因为它是需要的对于用户指定的操作 比如一个SQL查询,或者 预读操作的一部分 4. 访问一个old 子列表里的page 让这个page 变得年轻, 移动到buffer pool的头部(new sublist 的头部) 如果页面被读进因为它是需要的,第一次访问立即需要,page变的年轻。 如果page 由于预读被读取,第一次访问不发生 5. 在数据库操作时, buffer pool中的pages 是没有被访问 "age" 移动到列表的尾端。 pages 在新的和old 子列表页 其它pages 变为新的。 pages 在old sublist 也会变老当pages 被插入到中间段。 最终,一个page 仍旧不被使用足够长的时候会移动到 old sublist的尾部,被驱逐。 默认的,被查询读取的pages 立即移动到new sublist, 这意味着它们仍旧会停留在buffer pool很长一段时间 。 一个表扫描( 比如 执行一个mysqldump 操作或者一个SELECT 语句没有带WHERE条件) 可能会带来大量的数据到buffer pool,驱逐同等数量的老的数据, 即使新的数据不会再被使用。 类似的, 通过预读加载的pages,只被访问一次移动到 new list的头部。 这些情况可以把平凡使用的页面移动到old sublist，在那里它们收到驱逐。 InnoDB 标准监控输出包含几个字段在BUFFER POOL和MEMORY 章节 ,有关 buffer pool的操作 LRU算法。 InnoDB Buffer Pool 配置选项: InnoDB Buffer Pool Configuration Options 配置选项: 几个配置选项影响InnoDB buffer pool的不同方面: innodb_buffer_pool_size 指定buffer pool的大小, 如果buffer pool 是很小的,你有足够的内存, 让Buffer pool足够大能改善性能通过降低磁盘的I/O innodb_buffer_pool_instances 将缓冲池发成到用户指定的单独的区域的数量, 每个都有它自己的LRU list 和相关的数据结构， 减少竞争在并发的内存读取和写操作。 这个选项只有当 innodb_buffer_pool_size 设置1GB以上才有效。 你指定的总的大小是被划分到素有的buffer pools.为了最好的效率, 指定一个innodb_buffer_pool_instances and innodb_buffer_pool_size 的组合 每个buffer pool instance 至少1GB mysql> show variables like '%innodb_buffer_pool_instances%'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | innodb_buffer_pool_instances | 8 | +------------------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like '%innodb_buffer_pool_size%'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 8589934592 | +-------------------------+------------+ 1 row in set (0.00 sec) innodb_old_blocks_pct 指定buffer pool 大概的比例,InnoDB 用于old block sublist. 值的范围是5到95,默认是只37(也就是3/8) mysql> show variables like '%innodb_old_blocks_pct%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_old_blocks_pct | 37 | +-----------------------+-------+ 1 row in set (0.00 sec) innodb_old_blocks_time 指定多久(单位毫秒） 一个page 被插入到old sublist 必须呆在那里 在它第一次访问后 在它可以被移动到new sublist. 如果值是0，一个page 被插入到old sublist 立即移动到一个new sublist（在首次访问后）， 不管访问发生后插入多么快 mysql> show variables like '%innodb_old_blocks_time%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_old_blocks_time | 1000 | +------------------------+-------+ 1 row in set (0.00 sec) 如果值大于0, pages停留在old sublist 知道一个访问发生至少很多毫秒在首次访问后。 比如 值为1000 会导致pages 停留在old sublist 1秒钟 在首次访问 在它们成为合格的移动到new sublist 设置innodb_old_blocks_time 大于0 防止一次性表扫描充满new sublist 用用于扫描的页。 在pages里的行读进来被访问很多次 快速替代, 但是页不会被使用。如果innodb_old_blocks_time 是这位一个值大于0 那么时间来处理page, page 停留在old sublist 老化到list 的尾部 被快速淘汰。 这样, 页只被使用一次扫描 不损坏大量的使用的页面 在new sublist innodb_old_blocks_time 可以设置运行时间,因此你可以临时改变它当处理扫描和dump这些操作: SET GLOBAL innodb_old_blocks_time = 1000; ... perform queries that scan tables ... SET GLOBAL innodb_old_blocks_time = 0; 这个侧路不应用如果你打算 "warm up" buffer pool 通过使用表的内存填充。 比如,基准测试经常执行一个表或者Index scan 在启动的时候, 因为 数据通常在buffer pool 在正常使用一段时间后,在那种情况下, 设置 innodb_old_blocks_time为0, 至少在热身阶段完成 innodb_read_ahead_threshold 控制线性预读,InnoDB 使用预取页面到buffer pool innodb_random_read_ahead 将随机的预读即使用于获取页面到buffer pool. 随机预读是一种即使预测页什么时候可能需要基于页面已经在buffer pool里, 忽略页面被读取的顺序。innodb_random_read_ahead 默认被关闭 innodb_adaptive_flushing 指定是否动态跳转刷新脏页的速录在buffer pool 中基于工作负载。 动态调整刷新速率 是为了避免突发的I/O活动,默认启用 innodb_adaptive_flushing_lwm redo log 容量的 low water mark 比例 在自适应刷新被启用: