Buffer pool
Buffer pool 是主内存的一个区域，用来缓存表和索引数据。
为了提高大批量读的效率，buffer pool 划分为存储多行数据的页。使用较少的数据通过LRU 算法淘汰。
Buffer Pool LRU Algorithm
- 当
InnoDB将一页读到 buffer pool, it initially inserts it at the midpoint (the head of the old sublist). - Accessing a page in the old sublist makes it "young", move it to the head of the new sublist. If the page was read because it was required by a user-initiated operation, the first access occurs immediately and the page is made young. If the page was read due to a read-ahead operation, the first access does not occurs immediately and might not occurs at all before the page is evicted.
默认情况下，查询读取的页面会立刻进入 new sublist。例如mysqldump 操作或者不带 where 的表扫描，会带大量数据进入缓存池，并驱除等量的旧数据。
Monitoring the Buffer Pool Using the InnoDB Standard Monitor
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size 131072
Free buffers 124908
Database pages 5720
Old database pages 2071
Modified db pages 910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 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: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
| Name 名称 | Description 描述 |
|---|---|
| Total memory allocated 已分配的总内存 | The total memory allocated for the buffer pool in bytes. 缓冲池分配的总内存（以字节为单位）。 |
| Dictionary memory allocated 字典内存分配 | The total memory allocated for the InnoDB data dictionary in bytes. 分配给 InnoDB 数据字典的总内存（以字节为单位）。 |
| Buffer pool size 缓冲池大小 | The total size in pages allocated to the buffer pool. 分配给缓冲池的总页数。 |
| Free buffers 空闲缓冲区 | The total size in pages of the buffer pool free list. 缓冲池空闲列表的总页数。 |
| Database pages 数据库页 | The total size in pages of the buffer pool LRU list. 缓冲池 LRU 列表的总页数。 |
| Old database pages 旧数据库页 | The total size in pages of the buffer pool old LRU sublist. 缓冲池旧 LRU 子列表的总页数。 |
| Modified db pages 已修改的数据库页 | The current number of pages modified in the buffer pool. 缓冲池中当前已修改的页数。 |
| Pending reads 待读取 | The number of buffer pool pages waiting to be read into the buffer pool. 缓冲池中等待读入缓冲池的页面数量。 |
| Pending writes LRU 待写入 LRU | The number of old dirty pages within the buffer pool to be written from the bottom of the LRU list. 缓冲池中位于 LRU 列表底部的旧脏页数量。 |
| Pending writes flush list 待处理写入刷新列表 | The number of buffer pool pages to be flushed during checkpointing. 在检查点期间要刷新的缓冲池页数。 |
| Pending writes single page 待写入的单页 | The number of pending independent page writes within the buffer pool. 缓冲池中待独立页写入的数量。 |
| Pages made young 变得年轻的页 | The total number of pages made young in the buffer pool LRU list (moved to the head of sublist of “new” pages). 缓冲池 LRU 列表中变为年轻的页面的总数（移至“新”页面的子列表头部）。 |
| Pages made not young 未变为年轻的页面 | The total number of pages not made young in the buffer pool LRU list (pages that have remained in the “old” sublist without being made young). 缓冲池 LRU 列表中未变为年轻的页面的总数（保持在“旧”子列表中且未变为年轻的页面）。 |
| youngs/s 每秒年轻数 | The per second average of accesses to old pages in the buffer pool LRU list that have resulted in making pages young. See the notes that follow this table for more information. 缓冲池 LRU 列表中导致页面变年轻的旧页面的每秒平均访问次数。有关更多信息，请参阅本表下方的注释。 |
| non-youngs/s 非年轻页数/秒 | The per second average of accesses to old pages in the buffer pool LRU list that have resulted in not making pages young. See the notes that follow this table for more information. 缓冲池 LRU 列表中导致页面未变年轻的旧页面的每秒平均访问次数。有关更多信息，请参阅本表下方的注释。 |
| Pages read 读取的页数 | The total number of pages read from the buffer pool. 从缓冲池中读取的总页数。 |
| Pages created 创建的页面 | The total number of pages created within the buffer pool. 缓冲池中创建的总页数。 |
| Pages written 写入的页数 | The total number of pages written from the buffer pool. 从缓冲池中写入的总页数。 |
| reads/s 每秒读取次数 | The per second average number of buffer pool page reads per second. 每秒平均缓冲池页面读取次数。 |
| creates/s 创建/秒 | The average number of buffer pool pages created per second. 每秒平均创建的缓冲池页面数。 |
| writes/s 写入/秒 | The average number of buffer pool page writes per second. 每秒平均缓冲池页面写入次数。 |
| Buffer pool hit rate 缓冲池命中率 | The buffer pool page hit rate for pages read from the buffer pool vs from disk storage. 从缓冲池读取页面与从磁盘存储读取页面的缓冲池页面命中率。 |
| young-making rate 年轻化率 | The average hit rate at which page accesses have resulted in making pages young. See the notes that follow this table for more information. 页面访问导致页面变新的平均命中率。有关更多信息，请参阅本表后的注释。 |
| not (young-making rate) 不是（变新率） | The average hit rate at which page accesses have not resulted in making pages young. See the notes that follow this table for more information. 页面访问未导致页面变新的平均命中率。有关更多信息，请参阅本表后的注释。 |
| Pages read ahead 预读页面 | The per second average of read ahead operations. 每秒预读操作的平均值。 |
| Pages evicted without access 未访问即被驱逐的页 | The per second average of the pages evicted without being accessed from the buffer pool. 每秒平均被从缓冲池中驱逐且未被访问的页。 |
| Random read ahead 随机预读 | The per second average of random read ahead operations. 每秒随机预读操作的平均值。 |
| LRU len LRU 长度 | The total size in pages of the buffer pool LRU list. 缓冲池 LRU 列表的总页数。 |
| unzip_LRU len unzip_LRU 长度 | The length (in pages) of the buffer pool unzip_LRU list. buffer pool unzip_LRU 列表的长度（以页为单位）。 |
| I/O sum I/O 总和 | The total number of buffer pool LRU list pages accessed. 缓冲池 LRU 列表被访问的总页数。 |
| I/O cur I/O 当前 | The total number of buffer pool LRU list pages accessed in the current interval. 当前间隔内缓冲池 LRU 列表被访问的总页数。 |
| I/O unzip sum I/O 解压缩总和 | The total number of buffer pool unzip_LRU list pages decompressed. 缓冲 unzip_LRU 列表页面的总数。 |
| I/O unzip cur I/O 解压缩当前 | The total number of buffer pool unzip_LRU list pages decompressed in the current interval. |
