20. 缓冲池
缓冲池介绍
- 每次读写数据都是通过Buffer Pool；
- 当Buffer Pool中没有用户所需要的数据时，才去硬盘中获取；
- 通过innodb_buffer_pool_size进行设置总容量；
- 该值设置的越大越好；
- innodb_buffer_pool_instances 设置为多少个缓冲池；
- 总容量 还是 innodb_buffer_pool_size
- 设置 多个instance 可将热点打散，提高并发性能（建议设置成CPU个数值，设置大了也没什么伤害）
- Buffer Pool也是以页（page）为单位的，且大小和 innodb_page_size 一致；
Buffer Pool 性能测试
18G的测试数据,80M条记录；
- 当 Buffer Pool 使用的内存超过数据库的大小 时，比如20G（库中所有数据都在内存中），此时的性能有了很大的提升；
- 该图测试的是 TPS （每秒事物数），sysbench中一个事物由18条SQL语句组成，即这里的QPS为4.5W
- 内存减少 10% ，性能下降 60%
Buffer Pool的管理
Buffer Pool 的组成
- Free List
- Buffer Pool刚启动时，有一个个16K的空白页，这些页就存放（链表串联）在Free List中
- LRU List
- 当读取一个数据页的时候，就从Free List中取出一个页，存入数据，并将该页放入到LRU List中
- Flush List
- 当LRU List中的页第一次被修改了，就将该页的指针（page number）放入了Flush List（只要修改过，就放入，不管修改几次）
- Flush List中包含脏页（数据经过修改，但是未刷入磁盘的页）
- Flush List中存放的不是一个页，而是页的指针（page number）
查看Buffer Pool的状态
- 使用命令 show engine innodb status\G 配合 pager less
root@mysqldb 14:32: [(none)]> show engine innodb status\G -- 省略其他输出 ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 0 Dictionary memory allocated 508567 Buffer pool size 32765 -- 该Buffer Pool中有多少个页 Free buffers 31361 -- 该Buffer Pool中有多少个空白页（Free List），线上可能看到为0 Database pages 1399 -- 该Buffer Pool中使用了多少页（LRU List） Old database pages 536 -- old pages（见下） Modified db pages 0 -- 脏页 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s -- young表示old-->new的状态 Pages read 1251, created 148, written 313 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1399, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -- 省略其他输出
注意：
Free buffers + Database pages < Buffer pool size
- 使用元数据表 information_schema.INNODB_BUFFER_POOL_STATS
root@mysqldb 14:38: [information_schema]> select * from information_schema.INNODB_BUFFER_POOL_STATS\G *************************** 1. row *************************** POOL_ID: 0 POOL_SIZE: 32765 -- 该Buffer Pool中有多少个页 FREE_BUFFERS: 31361 -- 该Buffer Pool中有多少个空白页（Free List），线上可能看到为0 DATABASE_PAGES: 1399 -- 该Buffer Pool中使用了多少页（LRU List） OLD_DATABASE_PAGES: 536 -- old pages （见下） MODIFIED_DATABASE_PAGES: 0 -- 脏页 PENDING_DECOMPRESS: 0 PENDING_READS: 0 PENDING_FLUSH_LRU: 0 PENDING_FLUSH_LIST: 0 PAGES_MADE_YOUNG: 0 PAGES_NOT_MADE_YOUNG: 0 PAGES_MADE_YOUNG_RATE: 0 PAGES_MADE_NOT_YOUNG_RATE: 0 NUMBER_PAGES_READ: 1251 NUMBER_PAGES_CREATED: 148 NUMBER_PAGES_WRITTEN: 313 PAGES_READ_RATE: 0 PAGES_CREATE_RATE: 0 PAGES_WRITTEN_RATE: 0 NUMBER_PAGES_GET: 20177 HIT_RATE: 1000 YOUNG_MAKE_PER_THOUSAND_GETS: 0 NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0 NUMBER_PAGES_READ_AHEAD: 0 NUMBER_READ_AHEAD_EVICTED: 0 READ_AHEAD_RATE: 0 READ_AHEAD_EVICTED_RATE: 0 LRU_IO_TOTAL: 0 LRU_IO_CURRENT: 0 UNCOMPRESS_TOTAL: 0 UNCOMPRESS_CURRENT: 0 1 row in set (0.00 sec) root@mysqldb 14:42: [information_schema]> select * from information_schema.INNODB_BUFFER_PAGE_LRU limit 1\G *************************** 1. row *************************** POOL_ID: 0 LRU_POSITION: 0 SPACE: 0 -- space id 表空间号 PAGE_NUMBER: 7 -- 对应的页号 PAGE_TYPE: SYSTEM FLUSH_TYPE: 1 FIX_COUNT: 0 IS_HASHED: NO NEWEST_MODIFICATION: 5487632721 -- 该页最近一次（最新）被修改的LSN值 OLDEST_MODIFICATION: 0 -- 该页在Buffer Pool中第一次被修改的LSN值，FLushList是根据该值进行排序的 -- 该值越小，表示该页应该最先被刷新 ACCESS_TIME: 14321 TABLE_NAME: NULL INDEX_NAME: NULL NUMBER_RECORDS: 0 DATA_SIZE: 0 COMPRESSED_SIZE: 0 COMPRESSED: NO IO_FIX: IO_NONE IS_OLD: YES FREE_PAGE_CLOCK: 0 1 row in set (0.00 sec)
Buffer Pool 在线调整
set global innodb_buffer_pool_size=2*1024*1024*1024; --修改buffer poolMySQL 5.7之前的版本，修改该值，需要重启
LRU List 的管理
- 使用mid point的LRU算法
- 当该页被第一次读取时，先将该页放在mid point的位置（因为无法保证一定是活跃）；
- 当被读到第二次的，才将该页放到new page的首部；
- innodb_old_blocks_pct参数控制mid point的位置，默认时37，即3/8的位置
root@mysqldb 02:42: [(none)]> show variables like "%innodb_old_blocks_pct%"; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_old_blocks_pct | 37 | +-----------------------+-------+ 1 row in set (0.02 sec)
- 当Free List中没有空余的页时，就需要将 old page 中最后的页（被淘汰的页）取出，给新的查询所使用
- 如果被淘汰的页是脏页（page number在Flush List中），则需要先刷回磁盘后，再给新的查询使用
- 避免扫描语句污染LRU
当使用 select * from tablename; 时，该语句会读取某个页很多次（即该页可能被读取了两次以上， 读取一条记录，就需要读一次页 ）
-
- innodb_old_blocks_time
root@mysqldb 02:48: [(none)]> select @@innodb_old_blocks_time; +--------------------------+ | @@innodb_old_blocks_time | +--------------------------+ | 1000 | -- 设置为1s +--------------------------+ 1 row in set (0.00 sec)
- innodb_old_blocks_time
- 当该页被 第一次 读取时，将该页放在 mid point 位置，但是随后无论你读多少次 ，我在这 innodb_old_blocks_time 的时间内都不管（ 都视作只读取了一次 ），等这个时间过去了（时间到），如果该页还是被读取了，我才把这个页放到 new page 的首部。
- 通常 select * 扫描操作不会高于1秒，一个页很快就被扫完了。
Buffer Pool 的预热
- 在 MySQL 5.6 以后，可以在停机的时候dump出buffer pool的数据（space，page number），然后在启动的时候Load进buffer pool
- 该功能可以让MySQL启动时自动预热，无需人工干预。
root@mysqldb 02:51: [(none)]> show variables like '%innodb_buffer_pool%'; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_chunk_size | 134217728 | | innodb_buffer_pool_dump_at_shutdown | ON | -- 在停机时dump出buffer pool中的（space，page） | innodb_buffer_pool_dump_now | OFF | -- set一下，表示现在就从buffer pool中dump | innodb_buffer_pool_dump_pct | 40 | -- dump的百分比，是每个buffer pool文件，而不是整体 | innodb_buffer_pool_filename | ib_buffer_pool | -- dump出的文件的名字 | innodb_buffer_pool_in_core_file | ON | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | ON | -- 启动时加载dump的文件，恢复到buffer pool中 | innodb_buffer_pool_load_now | OFF | -- set一下，表示现在加载dump的文件 | innodb_buffer_pool_size | 536870912 | +-------------------------------------+----------------+ 11 rows in set (0.00 sec) root@ubuntu:/data/mysql# head ib_buffer_pool -- dump出来的文件 4294967294,17 4294967294,42 4294967294,67 4294967294,99 4294967294,76 4294967294,41 4294967294,120 4294967294,43 4294967294,418 4294967294,51 root@ubuntu:/data/mysql# wc -l ib_buffer_pool -- dump前 554 ib_buffer_pool root@mysqldb 03:20: [(none)]> set global innodb_buffer_pool_dump_now=1; Query OK, 0 rows affected (0.00 sec) root@mysqldb 03:21: [(none)]> show status like 'Innodb_buffer_pool_dump_status'; +--------------------------------+--------------------------------------------------+ | Variable_name | Value | +--------------------------------+--------------------------------------------------+ | Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 240420 3:21:43 | -- 已完成 +--------------------------------+--------------------------------------------------+ 1 row in set (0.00 sec) root@ubuntu:/data/mysql# wc -l ib_buffer_pool -- dump后 550 ib_buffer_pool root@mysqldb 03:24: [(none)]> set global innodb_buffer_pool_load_now = 1; Query OK, 0 rows affected (0.00 sec) root@mysqldb 03:24: [(none)]> show status like 'Innodb_buffer_pool_load_status'; +--------------------------------+--------------------------------------------------+ | Variable_name | Value | +--------------------------------+--------------------------------------------------+ | Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 240420 3:24:46 | -- load完成 +--------------------------------+--------------------------------------------------+ 1 row in set (0.00 sec)
- dump的越多，启动的越慢
- 频繁的手工dump（ set innodb_buffer_pool_dump_now = 1 ），会导致Buffer Pool中的数据越来越少，是因为设置了 innodb_buffer_pool_dump_pct
- 如果做了高可用，可以定期dump，然后将该dump的文件传送到slave上，然后直接load（ set innodb_buffer_pool_load_now = 1 ）（slave上的（Space，Page）和Master上的 大致相同 ）
- load now 和 dump now 都是 异步 在后台加载的，返回的速度很
- innodb_buffer_pool_dump_pct
- 该百分比（N<100）不是你当前buffer pool的总的数据（总页数）的N%，而是你每个buffer pool实例中最近使用的页的N%
