MySQL OCP888题解004-innodb buffer pool(缓冲池)的作用

1、原题

1.1、英文原题

Which statement best describes the purpose of the InnoDB buffer pool?
A. It is amount of buffers available during a transaction.
B. It caches only the indexes for InnoDB tables.
C. It caches data and indexes for InnoDB tables.
D. It holds changes made during a transaction before they are written to the log.
E. It is a pool of memory for SQL query sort operations from within the InnoDB engine.

1.2、中文翻译

哪个语句最能描述InnoDB缓冲池的用途？
A、 它是事务期间可用的缓冲区数量。
B、 它只缓存InnoDB表的索引。
C、 它缓存InnoDB表的数据和索引。
D、 它保存事务期间所做的更改，然后再将其写入日志。
E、 它是一个内存池，用于InnoDB引擎内的SQL查询排序操作。

1.3、答案

C

2、题目解析

2.1、题干解析

本题主要考察对InnoDB缓冲池的理解

2.2、选项解析

• InnoDB的缓冲池是全局的，不是每个事务一个缓冲池，所以选项A错误。
• InnoDB的缓冲池缓冲了表的数据和索引，所以选项B错误，选项C正确。
• InnoDB的事务日志写入的是Redo Log的Buffer，而不是Buffer Pool，所以选项D错误。
• InnoDB的SQL查询排序用的是针对每个线程建立的Sort Buffer，而不是Buffer Pool，所以选项E错误。

3、知识点

3.1、InnoDB Buffer Pool(缓冲池)

3.1.1、基本概念

缓冲池是主内存中的一个区域，InnoDB在访问表和索引数据时对其进行缓存。缓冲池允许经常使用的数据直接从内存中访问，从而加快处理速度。在专用服务器上，通常有高达80%的物理内存被分配给缓冲池。

官方参考文档

3.1.2、缓冲池的LRU算法

缓冲池以列表的形式管理，使用LRU算法的一个变种。当需要在缓冲池中添加新的页面时，最近使用最少的页面被驱逐，新的页面被添加到列表的中间。这个中点插入策略将列表视为两个子列表。

• 在头部，是最近访问过的新（“年轻”）页面的子列表
• 在尾部，是最近被访问次数较少的旧页面的子列表，包含不经常使用的页面，是被驱逐的对象。缓冲池的3/8被用于旧子列表。

当InnoDB将一个页面读入缓冲池时，它最初将其插入到中点（旧子列表的头部）。通常这是由用户发起的操作（比如SQL查询）所需要的，或是InnoDB自动执行的读前操作的一部分。、
在旧的子列表中访问一个页面会使它变得 “年轻”，把它移到新的子列表的头部。随着数据库的运行，缓冲池中未被访问的页面通过向列表尾部移动而 “老化”。最终，一个未使用的页面到达旧子列表的尾部并被驱逐。如下图所示：

3.1.3、缓冲池大小的配置

1. 缓冲池尽量大：理想的情况是，你把缓冲池的大小设置为尽可能大的数值，为服务器上的其他进程留出足够的内存来运行而不需要过多的分页。缓冲池越大，InnoDB就越像一个内存数据库，从磁盘上读取一次数据，然后在随后的读取中从内存中访问数据。
2. 当增加或减少innodb_buffer_pool_size时，操作是以块(chunk)为单位进行的。chunk的大小是由innodb_buffer_pool_chunk_size配置选项定义的，它的默认值是128M。缓冲池的大小必须总是是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍，如果你设置的不是整数倍，则缓冲池大小会自动调整为大于你的设置而等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍的数值。

3.1.4、多实例缓冲池

当InnoDB缓冲池很大时，许多数据请求可以通过从内存检索来满足。你可能会遇到多个线程试图同时访问缓冲池的瓶颈问题。InnoDB可以启用多个缓冲池来减少这种争夺。每个存储在缓冲池中或从缓冲池中读取的页面都被随机分配到一个缓冲池中，使用散列函数。每个缓冲池管理自己的空闲列表、刷新列表、LRU和所有其他连接到缓冲池的数据结构，并由自己的缓冲池突变保护。
要启用多个缓冲池实例，可以将innodb_buffer_pool_instances配置选项设置为大于1（默认）到64（最大）的值。这个选项只有在你把innodb_buffer_pool_size设置为1GB或更大时才会生效。如果innodb_buffer_pool_size不到1GB，对innodb_buffer_pool_instance设置不会生效，只会是1。

官方参考文档

3.1.5、脏页及FLUSH

如果要修改的数据存在于缓冲池内，则InnoDB可能将直接修改缓冲池内的数据页，这样就会产生缓冲池的数据页和磁盘不一致的情况，这种缓冲池中和磁盘不一致的数据页成为脏页。也就是说，脏页是指那些已经被修改但还没有写入磁盘上的数据文件的页面。可以通过FLUSH的方式，将脏页写入磁盘。

• innodb_page_cleaners变量：在MySQL 5.7中，缓冲池的冲洗是由page cleaner threads执行的。page cleaner threads的数量由innodb_page_cleaners变量控制，默认值为4。 但是，如果线程数量超过缓冲池实例的数量，innodb_page_cleaners会自动设置为与innodb_buffer_pool_instances相同的值。
• innodb_max_dirty_pages_pct变量：如果缓冲池中的脏页百分比达到innodb_max_dirty_pages_pct的阈值，InnoDB会主动地刷新缓冲池的页面。 该变量默认值为75。
• innodb_max_dirty_pages_pct_lwm变量：当脏页的百分比达到innodb_max_dirty_pages_pct_lwm变量所定义的低水位值时，缓冲池的冲洗就开始了。默认的低水位值是0，它禁用了这种早期冲刷行为。 innodb_max_dirty_pages_pct_lwm变量设置的目的是控制缓冲池中脏页的百分比，防止脏页的数量达到innodb_max_dirty_pages_pct变量定义的阈值。当配置innodb_max_dirty_pages_pct_lwm时，该值应该总是低于innodb_max_dirty_pages_pct值。
• innodb_flush_neighbors变量：定义了从缓冲池冲刷一个页面是否也会冲刷同一范围内的其他脏页。
  • 设置为0将禁用innodb_flush_neighbors。同一范围内的脏页不会被刷新。
  • 默认设置为1，会刷新同一范围内连续的脏页。
  • 如果设置为2，则会刷新同一范围内的脏页。
  • 当表数据存储在传统的HDD存储设备上时，与在不同时间刷新单个页面相比，在一次操作中刷新相邻的页面可以减少I/O开销（主要是磁盘搜索操作）。对于存储在SSD上的表数据，寻道时间不是一个重要因素，你可以禁用这个设置来分散写操作。
• innodb_lru_scan_depth变量：指定了每个缓冲池实例，页面清洁线程在缓冲池LRU列表中扫描多远，以寻找要刷新的脏页。这是一个后台操作，由一个页面清理线程每秒执行一次。

官方参考文档

3.1.6、保存和恢复缓冲池状态

缓冲池如果缓存的数据比较多，一旦重启，就面临一个数据预热到缓冲池的问题，在这个预热过程中，由于大量数据不在缓冲池内，可能导致性能问题。
为了解决这个问题，InnoDB允许在服务器关闭时为每个缓冲池保存一定比例的最近使用的页面，并在服务器启动时恢复这些页面。保存的最近使用的页面的百分比由innodb_buffer_pool_dump_pct配置选项定义。

官方参考文档

3.1.7、通过SHOW ENGINE INNODB STATUS监控缓冲池使用

InnoDB标准监控器的输出，可以通过SHOW ENGINE INNODB STATUS来访问，它提供了关于缓冲池运行的指标。缓冲池指标位于InnoDB标准监控器输出的BUFFER POOL AND MEMORY部分。输出中提供的每秒平均数是基于上次打印InnoDB标准监控器输出以来的时间。

----------------------
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]

针对以上输出的输出项，说明如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BjuwzutF-1674887992320)(https://img-oddrock520.oss-cn-shanghai.aliyuncs.com/202301281424424.png)]

官方参考文档

3.1.8、通过INFORMATION_SCHEMA.INNODB_BUFFER*表监控缓冲池

INFORMATION_SCHEMA中的缓冲池表提供了缓冲池的状态信息和池内页面的元数据，缓冲池表包括下面列出的这些：

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';
+-----------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) |
+-----------------------------------------------+
| INNODB_BUFFER_PAGE_LRU                        |
| INNODB_BUFFER_PAGE                            |
| INNODB_BUFFER_POOL_STATS                      |
+-----------------------------------------------+

这三张表的说明如下：

注意：查询INNODB_BUFFER_PAGE或INNODB_BUFFER_PAGE_LRU等缓冲池表会影响性能。不要在生产系统上查询这些表，除非你意识到对性能的影响，并且确定它是可以接受的。为了避免影响生产系统的性能，重现你想调查的问题，并在测试实例上查询缓冲池的统计数据。

• 以下查询提供包含用户数据的页面的近似计数：这个查询通过计算TABLE_NAME值为NOT NULL且NOT LIKE’%INNODB_SYS_TABLES%'的页面：

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE        WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE '%INNODB_SYS_TABLES%';
+----------+
| COUNT(*) |
+----------+
|     1285 |
+----------+

• 以下查询返回包含用户数据的页面的大致数量，缓冲池页面的总数量，以及包含用户数据的页面的大致百分比。

mysql> SELECT        (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE        WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)        ) AS user_pages,        (        SELECT COUNT(*)        FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE        ) AS total_pages,        (        SELECT ROUND((user_pages/total_pages) * 100)        ) AS user_page_percentage;
+------------+-------------+----------------------+
| user_pages | total_pages | user_page_percentage |
+------------+-------------+----------------------+
|         17 |       98292 |                    0 |
+------------+-------------+----------------------+

• 以下查询确定有哪些用户定义的表在缓冲池中：

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE        WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME,
'/') > 0 OR INSTR(TABLE_NAME, '.') > 0)        AND TABLE_NAME NOT LIKE '`mysql`.`%';
+----------------------------------------+
| TABLE_NAME                             |
+----------------------------------------+
| `sakila`.`FTS_0000000000000163_CONFIG` |
+----------------------------------------+

• 关于索引页的信息，可以使用索引的名称查询INDEX_NAME列。例如，下面的查询返回在employees.sals表上定义的emp_no索引的页数和总数据量。

mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,        ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)        AS 'Total Data (MB)'        FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE        WHERE INDEX_NAME='emp_no' AND TABLE_NAME = '`sakila`.`payment`';
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| NULL       |     0 |            NULL |
+------------+-------+-----------------+

• INNODB_BUFFER_POOL_STATS表提供类似于SHOW ENGINE INNODB STATUS和InnoDB缓冲池状态变量的信息。

mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 32764
                    FREE_BUFFERS: 31698
                  DATABASE_PAGES: 1065
              OLD_DATABASE_PAGES: 392
         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: 234
            NUMBER_PAGES_CREATED: 831
            NUMBER_PAGES_WRITTEN: 5977
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 146947
                        HIT_RATE: 0
    	           ......

• 关于SHOW ENGINE INNODB STATUS输出的更多信息，参看官方链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-standard-monitor.html
• 对于状态变量的描述，参看官方链接：https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

官方参考文档

3.2、MySQL服务器状态变量Com_xxx和Qcache_xxx

MySQL服务器维护许多状态变量，提供有关其操作的信息。你可以通过使用SHOW [GLOBAL | SESSION] STATUS语句来查看这些变量及其值。可选的GLOBAL关键字汇总了所有连接的值，而SESSION显示了当前连接的值。

• Com_xxx语句计数器变量表示每个xxx语句被执行的次数。每种类型的语句都有一个状态变量。例如，Com_delete和Com_update分别计算DELETE和UPDATE语句。Com_delete_multi和Com_update_multi类似，但适用于使用多表语法的DELETE和UPDATE语句。
• 如果一个查询结果从查询缓存中返回，服务器会增加Qcache_hits状态变量，而不是Com_select。

官方参考文档

4、实验

4.1、实验1

4.1.1、实验目的

验证缓冲池的大小必须总是是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍，如果你设置的不是整数倍，则缓冲池大小会自动调整为大于你的设置而等于innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍的数值。

4.1.2、实验前准备

已安装并运行的MySQL5.7实例

4.1.3、实验步骤

1、先查看innodb_buffer_pool_chunk_size和innodb_buffer_pool_instances这两个参数，发现值分为别134217728（即128MB）和1：

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_chunk_size';
+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| innodb_buffer_pool_chunk_size | 134217728 |
+-------------------------------+-----------+
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+

2、将缓冲池大小设置为402653184，也就是innodb_buffer_pool_chunk_size和innodb_buffer_pool_instances乘积的3倍，然后查看设置结果，发现设置成成功，缓冲池大小就是402653184

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 402653184 |
+-------------------------+-----------+

3、再次将缓冲池大小改为150000000，这次不是整数倍，然后查看设置结果，发现实际的缓冲池大小就是268435456，也就是innodb_buffer_pool_chunk_size和innodb_buffer_pool_instances乘积的2倍，因为150000000大于他们的1倍，又不到2倍，所以被修正为2倍。这说明了innodb_buffer_pool_size必须是innodb_buffer_pool_chunk_size和innodb_buffer_pool_instances乘积的整数倍。

mysql> SET GLOBAL innodb_buffer_pool_size=150000000;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+

4、状态Innodb_buffer_pool_resize_status会报告缓冲池的调整进度，如下例所示：

mysql> show status like 'Innodb_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name                    | Value                                              |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 221126 15:54:22. |

5、错误日志也会记录缓冲池的调整进度，如下例所示：

$ more /home/mysql/localhost.localdomain.err
......
2022-11-26T07:54:22.526588Z 6 [Note] InnoDB: Requested to resize buffer pool. (new size: 268435456 bytes)
2022-11-26T07:54:22.526651Z 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 268435456 (unit=134217728).
2022-11-26T07:54:22.526662Z 0 [Note] InnoDB: Disabling adaptive hash index.
2022-11-26T07:54:22.526914Z 0 [Note] InnoDB: disabled adaptive hash index.
2022-11-26T07:54:22.526920Z 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2022-11-26T07:54:22.526923Z 0 [Note] InnoDB: Latching whole of buffer pool.
2022-11-26T07:54:22.526934Z 0 [Note] InnoDB: buffer pool 0 : resizing with chunks 1 to 2.
2022-11-26T07:54:22.534460Z 0 [Note] InnoDB: buffer pool 0 : 1 chunks (8192 blocks) were added.
2022-11-26T07:54:22.534516Z 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 268435456.
2022-11-26T07:54:22.534524Z 0 [Note] InnoDB: Re-enabled adaptive hash index.
2022-11-26T07:54:22.534536Z 0 [Note] InnoDB: Completed resizing buffer pool at 221126 15:54:22.
......

4.1.4、实验结论

缓冲池的大小必须总是是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的整数倍。

5、总结

InnoDB缓冲池缓冲的是表的数据和索引。InnoDB会尽量使用缓存中的数据以提高速度，无论是修改还是查询。如果是修改，就可能产生脏页，即内存数据和磁盘数据不一致，内存数据更新，这时要通过FLUSH将脏页刷写到磁盘。InnoDB Buffer Pool支持多实例。