Innodb缓冲池

2019独角兽企业重金招聘Python工程师标准>>>

Innodb缓冲池

缓存读和写(insert,update)，可能提高数据库的性能。

使用LRU算法进行pool列表管理，当需要添加新块到pool中时，Innodb驱逐最近最少使用的块，添加新块到列表的middle位置，叫做"midpoint insertion strategy"，维护两个子列表：midpoint之前的子列表存放"new"或"young"块，热点数据；midpoint之后的子列表存放"old"块，最近最少访问的。

缓冲池中缓存的数据页类型有：

索引页，数据页，undo页，插入缓冲，自适应哈希索引，Innodb存储的锁信息，数据字典信息等

查看相关系统变量：

1. root@[(none)] 11:33:30>show variables where Variable_name in ('innodb_buffer_pool_size','innodb_buffer_pool_instances','innodb_old_blocks_pct','innodb_old_blocks_time');
2. +------------------------------+------------+
3. | Variable_name                | Value      |
4. +------------------------------+------------+
5. | innodb_buffer_pool_instances | 4          |
6. | innodb_buffer_pool_size      | 5368709120 |
7. | innodb_old_blocks_pct        | 37         |
8. | innodb_old_blocks_time       | 1000       |
9. +------------------------------+------------+
10. 9 rows in set (0.00 sec)

innodb_buffer_pool_instances：

分开buffer pool到定义数量的独立区域，每个实例有自己的free、flush和LRU列表和相关数据结构，减少并发内存读和写操作的争用，只有当innodb_buffer_pool_size大小大于等于1GB时才有效。使用哈希函数分配每个页到各个buffer pool实例。

innodb_buffer_pool_size：

指定buffer pool的大小，单位字节(bytes)，该内存区域用于Innodb缓存表和索引数据等，设置足够大的值，保持频繁访问数据在内存，可以减少磁盘I/O，在专用的数据库服务器，官方建议设置为物理内存的80%。buffer pool被分成很多页(默认16KB)，可能存储多行数据，使用链表结构，使用LRU算法(修改过)淘汰很少使用的页。从mysql 5.6开始支持dump和restore buffer pool的内容，可以在服务关闭和开启时自动完成，也可以手动执行这些操作。innodb需要额外的内存缓存和控制结构，因此总的分配内存空间比指定的值要大10%左右；初始化buffer pool是需要一段时间的，如在Linux系统，10GB缓冲池，需要6s。

innodb_old_blocks_pct：

指定一个近似的buffer pool的百分比，用于old block子列表，值范围为5-95，默认37

innodb_old_blocks_time：

单位毫秒(ms)，指定块移到new sublist之前，必须在old sublist保留多少时间。默认值为0，当innodb从buffer pool中驱逐1/4插入块页，块插入到old sublist将移到new sublist。设置大于0，防止表扫描操作或dump操作腐败new sublist。

监控buffer pool：

1、通过innodb标准监控

1. root@[(none)] 11:38:48>show engine innodb status\G;

如果youngs/s值很低，当没有大的扫描，表明可能需要减少延时时间(innodb_old_blocks_time)，或者增大old sublist的百分比

当做大的表扫描时，没有看到许多non-youngs/s，或许多youngs/s，需要增大延时时间(innodb_old_blocks_time)。

1. ----------------------
2. BUFFER POOL AND MEMORY
3. ----------------------
4. Total memory allocated 5494538240; in additional pool allocated 0 #buffer pool的大小，单位字节
5. Dictionary memory allocated 68583
6. Buffer pool size   327679 #缓冲池总的页数量，默认页大小为16K
7. Free buffers       326335 #Free 列表的页数量
8. Database pages     1338 #LRU列表中页的数量
9. Old database pages 0  #buffer pool中old sublist的页数量
10. Modified db pages  0  #脏页的数量
11. Pending reads 0
12. Pending writes: LRU 0, flush list 0, single page 0
13. Pages made young 0, not young 0 #多少old页移到new sublist，多少页保留在old sublist
14. 0.00 youngs/s, 0.00 non-youngs/s #每秒访问old页面的次数，youngs/s为old sublist移到new sublist的old页
15. Pages read 1334, created 4, written 16
16. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
17. Buffer pool hit rate 894 / 1000, young-making rate 0 / 1000 not 0 / 1000  #Buffer pool hit rate表示缓冲池的命中率，young-making rate块移到buffer pool头部的比率，这段信息也可能是这样No buffer pool page gets since the last printout
18. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
19. LRU len: 1338, unzip_LRU len: 0 #对非16KB的页，使用unzip_LRU列表进行管理
20. I/O sum[0]:cur[0], unzip sum[0]:cur[0]
21. ----------------------
22. INDIVIDUAL BUFFER POOL INFO
23. ----------------------
24. ---BUFFER POOL 0
25. Buffer pool size   81920
26. Free buffers       81577
27. Database pages     341
28. Old database pages 0
29. Modified db pages  0
30. Pending reads 0
31. Pending writes: LRU 0, flush list 0, single page 0
32. Pages made young 0, not young 0
33. 0.00 youngs/s, 0.00 non-youngs/s
34. Pages read 337, created 4, written 14
35. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
36. No buffer pool page gets since the last printout
37. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
38. LRU len: 341, unzip_LRU len: 0
39. I/O sum[0]:cur[0], unzip sum[0]:cur[0]

2、通过状态变量

1. root@[(none)] 17:28:58>show global status like 'innodb_buff%';
2. +-----------------------------------------+-------------+
3. | Variable_name                           | Value       |
4. +-----------------------------------------+-------------+
5. | Innodb_buffer_pool_dump_status          | not started | #dump innodb buffer pool的操作进度
6. | Innodb_buffer_pool_load_status          | not started | #预热早期dump的innodb buffer pool操作进度
7. | Innodb_buffer_pool_pages_data           | 1001917     | #在buffer pool中缓存的页数量，包括脏页和没有修改的
8. | Innodb_buffer_pool_bytes_data           | 16415408128 | #在buffer pool中缓存的总数据大小(脏页和没有修改的)，单位字节
9. | Innodb_buffer_pool_pages_dirty          | 126483      | #在buffer pool中当前脏页数量
10. | Innodb_buffer_pool_bytes_dirty          | 2072297472  | #在buffer pool中当前总的脏页大小，单位字节
11. | Innodb_buffer_pool_pages_flushed        | 1457174     | #有多少页请求从buffer pool刷新到磁盘
12. | Innodb_buffer_pool_pages_LRU_flushed    | 0           |
13. | Innodb_buffer_pool_pages_free           | 3575        | #在buffer pool中的空闲页数量
14. | Innodb_buffer_pool_pages_made_not_young | 1398376     | #没有发生从old list移到new list的页数量
15. | Innodb_buffer_pool_pages_made_young     | 909318      | #从old sublist移到new sublist的页数量
16. | Innodb_buffer_pool_pages_misc           | 43080       | #buffer pool中多少页是忙的，因为分配管理开销，如行锁或自适应哈希索引，该值能使用以下公式进行计算：Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free - Innodb_buffer_pool_pages_data
17. | Innodb_buffer_pool_pages_old            | 369767      | #old sublist的页数量
18. | Innodb_buffer_pool_pages_total          | 1048572     | #buffer pool总大小，页面数量
19. | Innodb_buffer_pool_read_ahead_rnd       | 0           |
20. | Innodb_buffer_pool_read_ahead           | 0           | #多少页面因预读后台线程读到innodb buffer pool
21. | Innodb_buffer_pool_read_ahead_evicted   | 0           | #预读的页面没有后续的查询访问，被驱逐的页面数量
22. | Innodb_buffer_pool_read_requests        | 548907434   | #逻辑读请求数量
23. | Innodb_buffer_pool_reads                | 1354065     | #多少逻辑读没有在buffer pool中命中，直接从磁盘读取
24. | Innodb_buffer_pool_wait_free            | 0           | #当没有clean页面可用时，等待脏页刷新，该计算为等待的实例
25. | Innodb_buffer_pool_write_requests       | 57861503    | #写到buffer pool的数量
26. +-----------------------------------------+-------------+
27. 21 rows in set (0.00 sec)
30. root@[(none)] 17:29:13>show global status like 'innodb_page%';
31. +----------------------+---------+
32. | Variable_name        | Value   |
33. +----------------------+---------+
34. | Innodb_page_size     | 16384   | #innodb 页尺寸，默认16KB
35. | Innodb_pages_created | 22424   | #在innodb 表操作创建的页数量
36. | Innodb_pages_read    | 1564390 | #在innodb 表操作读的页数量
37. | Innodb_pages_written | 1457609 | #在innodb 表操作写的页数量
38. +----------------------+---------+
39. 4 rows in set (0.00 sec)
41. root@[(none)] 17:29:57>show global status like 'innodb_data%';
42. +----------------------------+-------------+
43. | Variable_name              | Value       |
44. +----------------------------+-------------+
45. | Innodb_data_fsyncs         | 110347      | #到目前为止，fsync()操作数量
46. | Innodb_data_pending_fsyncs | 0           | #当前等待fsync()操作的数量
47. | Innodb_data_pending_reads  | 3           | #当前等待读的数量
48. | Innodb_data_pending_writes | 1           | #当前等待写的数量
49. | Innodb_data_read           | 25754046464 | #从服务启动后，总的读取数据量，单位字节
50. | Innodb_data_reads          | 1554920     | #总的数据读取次数
51. | Innodb_data_writes         | 3138308     | #总的数据写次数
52. | Innodb_data_written        | 52755998208 | #从服务启动后，总的写数据量，单位字节
53. +----------------------------+-------------+
54. 8 rows in set (0.00 sec)

3、通过系统表：需要PROCESS权限

INNODB_BUFFER_POOL_STATS表提供跟show engine innodb status同样buffer pool信息。

1. root@[information_schema] 14:58:28>select COLUMN_NAME from COLUMNS where TABLE_NAME='INNODB_BUFFER_POOL_STATS';
2. +----------------------------------+
3. | COLUMN_NAME                      |
4. +----------------------------------+
5. | POOL_ID                          | #buffer pool实例ID
6. | POOL_SIZE                        | #buffer pool实例的大小，总的可用页数量
7. | FREE_BUFFERS                     | #free页数量
8. | DATABASE_PAGES                   | #包含数据的页，包括脏页和干净的页
9. | OLD_DATABASE_PAGES               | #old sublist的页数量
10. | MODIFIED_DATABASE_PAGES          | #脏页的数量
11. | PENDING_DECOMPRESS               | #等待解压的页数量
12. | PENDING_READS                    | #等待读取的页数量
13. | PENDING_FLUSH_LRU                | #在LRU中等待刷新的页数量
14. | PENDING_FLUSH_LIST               | #在flush list中等待刷新的页数量
15. | PAGES_MADE_YOUNG                 | #从old list移到new list的页数量
16. | PAGES_NOT_MADE_YOUNG             | #因innodb_old_blocks_time的设置导致页没有从old部分移动到new部分的页数量
17. | PAGES_MADE_YOUNG_RATE            | #每秒从old list移到new list的页数量
18. | PAGES_MADE_NOT_YOUNG_RATE        | #每秒没有从old部分移动到new部分的页数量
19. | NUMBER_PAGES_READ                | #读取页的数量
20. | NUMBER_PAGES_CREATED             | #创建页的数量
21. | NUMBER_PAGES_WRITTEN             | #写页的数量
22. | PAGES_READ_RATE                  | #每秒读的页数量
23. | PAGES_CREATE_RATE                | #每秒创建页面数量
24. | PAGES_WRITTEN_RATE               | #每秒写页面数量
25. | NUMBER_PAGES_GET                 | #逻辑读请求的数量
26. | HIT_RATE                         | #buffer pool命中率
27. | YOUNG_MAKE_PER_THOUSAND_GETS     | #每1000个get，多少页从old list移到new list
28. | NOT_YOUNG_MAKE_PER_THOUSAND_GETS | #每1000个get，多少页没有从old list移到new list
29. | NUMBER_PAGES_READ_AHEAD          | #预读页的数量
30. | NUMBER_READ_AHEAD_EVICTED        | #预读后台线程读到innodb buffer pool的页面，随后被驱逐的页数量，没有被查询访问
31. | READ_AHEAD_RATE                  | #每秒预读比率
32. | READ_AHEAD_EVICTED_RATE          | #每秒预读页被驱逐的比率
33. | LRU_IO_TOTAL                     | #总LRU IO个数
34. | LRU_IO_CURRENT                   | #当前时间间隔LRU IO个数
35. | UNCOMPRESS_TOTAL                 | #总解压缩的页数量
36. | UNCOMPRESS_CURRENT               | #当前时间间隔解压缩的页数量
37. +----------------------------------+
38. 32 rows in set (0.01 sec)

查看LRU列表中每个页的具体信息

select TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE from INNODB_BUFFER_PAGE_LRU where space=24;

查看缓存池的运行状态：命中率，old list移到new list的数量

select POOL_ID,HIT_RATE,PAGES_MADE_YOUNG,PAGES_NOT_MADE_YOUNG from INNODB_BUFFER_POOL_STATS;

查看unzip_lru列表中的页：

select TABLE_NAME,SPACE,PAGE_NUMBER,COMPRESSED_SIZE from innodb_buffer_page_lru where COMPRESSED_SIZE<>0;

查看脏页的信息：

select TABLE_NAME,SPACE,PAGE_NUMBER,COMPRESSED_SIZE from innodb_buffer_page_lru where OLDEST_MODIFICATION>0;

参考：

1、<<Mysql技术内幕：Innodb存储引擎>>

2、"buffer pool instance"：http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_buffer_pool

3、"The InnoDB Buffer Pool"：http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html

4、"Choosing innodb_buffer_pool_size"：http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

5、"Innodb Performance Optimization Basics"：http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

6、"InnoDB memory usage"：http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory-usage/

7、"Improved Buffer Pool Scalability"：http://www.percona.com/doc/percona-server/5.6/scalability/innodb_split_buf_pool_mutex.html

8、http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

9、"The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table"：http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool-stats-table.html

10、"5.1.6 Server Status Variables"：http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html

来自为知笔记(Wiz)

转载于:https://my.oschina.net/anthonyyau/blog/300581