InnoDB Buffer Pool for Mysql 5.7

缓存池

内存池用page来分割，pages会连接成链表，方便管理。
内存池最小单位是page，一个page默认是16KB. 一个page可以有一行以上数据，如果只有一行，而且没有填满page，innodb会用指针类型的数据结构填满page。可以压缩数据让page包含更多行数据，对于Blob或者text类型的大column, 使用压缩技术innodb会单独存储这一列的数据，以便减少不必要的查询开销。
一般缓存池最多会占80%的物理内存，InnoDB使用变异的LRU算法，维持缓存中数据的热度。
select * from information_schema.innodb_buffer_page 可以查看所有缓冲池的元信息，这个查询可能影响性能。

change buffer

change buffer 是一个特殊的数据结构，用于缓存那些辅助索引页的修改，而这些页又不在buffer pool中。由于辅助索引非唯一，insert,delete,update更改辅助索引页并不是按序进行的。所以需要先把他们缓存起来，当辅助索引读入buffer的时候，一并merge到buffer,这样减少了随机访问磁盘I/O.
change buffer 包含insert buffer, delete buffer, purge buffer
change buffer 既是缓存，又是tablespace的一部分。

show variables like innodb_change_buffering 这个变量可以设置inserts, deletes, purges,all,none.

none	Do not buffer any operations.
inserts	Buffer insert operations.
deletes	Buffer delete marking operations; strictly speaking, the writes that mark           index records for later deletion during a purge operation.
changes	Buffer inserts and delete-marking operations.
purges	Buffer the physical deletion operations that happen in the background.
all	The default. Buffer inserts, delete-marking operations, and purges.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
- 下面这个状态可以看到具体change buffer操作次数，insert表示insert buffer, delete mark表示delete buffer, delete表示purge buffer;
    ```sql
  show engine innodb status;
  -------------------------------------
  INSERT BUFFER AND ADAPTIVE HASH INDEX
  -------------------------------------
  Ibuf: size 1, free list len 0, seg size 2, 0 merges
  merged operations:
   insert 0, delete mark 0, delete 0
  discarded operations:
   insert 0, delete mark 0, delete 0
  Hash table size 34673, node heap has 0 buffer(s)
  Hash table size 34673, node heap has 0 buffer(s)
  Hash table size 34673, node heap has 0 buffer(s)
  Hash table size 34673, node heap has 0 buffer(s)
  Hash table size 34673, node heap has 1 buffer(s)
  Hash table size 34673, node heap has 0 buffer(s)
  Hash table size 34673, node heap has 0 buffer(s)
  Hash table size 34673, node heap has 0 buffer(s)
  0.00 hash searches/s, 0.00 non-hash searches/s

查看IBUF_INDEX和IBUF_BITMAP占全部page的比例

SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
(SELECT ((change_buffer_pages/total_pages)*100))
AS change_buffer_page_percentage;

change_buffer_pages | total_pages | change_buffer_page_percentage|
--- | --- | ---
21 | 8191 | 0.2564 |