MySQL :: MySQL 8.4 參考手冊 :: 17.15.5 InnoDB INFORMATION_SCHEMA 緩衝池表格

版本 8.4

8.0 日語

MySQL 8.4 參考手冊 / ... / InnoDB INFORMATION_SCHEMA 緩衝池表格

17.15.5 InnoDB INFORMATION_SCHEMA 緩衝池表格

InnoDB INFORMATION_SCHEMA 緩衝池表格提供關於 InnoDB 緩衝池中頁面的緩衝池狀態資訊與中繼資料。

InnoDB 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 緩衝池中每個頁面的相關資訊。
INNODB_BUFFER_PAGE_LRU：保留 InnoDB 緩衝池中頁面的相關資訊，特別是它們在 LRU 清單中的排序方式，該清單決定在緩衝池已滿時要從緩衝池中移除哪些頁面。INNODB_BUFFER_PAGE_LRU 表格與 INNODB_BUFFER_PAGE 表格具有相同的欄位，但 INNODB_BUFFER_PAGE_LRU 表格具有 LRU_POSITION 欄位，而不是 BLOCK_ID 欄位。
INNODB_BUFFER_POOL_STATS：提供緩衝池狀態資訊。大部分相同的資訊由 SHOW ENGINE INNODB STATUS 輸出提供，或者可以使用 InnoDB 緩衝池伺服器狀態變數取得。

警告

查詢 INNODB_BUFFER_PAGE 或 INNODB_BUFFER_PAGE_LRU 表格可能會影響效能。除非您知道效能影響且已確定可接受，否則請勿在生產系統上查詢這些表格。為了避免影響生產系統的效能，請重現您想要調查的問題，並在測試執行個體上查詢緩衝池統計資訊。

範例 17.6 查詢 INNODB_BUFFER_PAGE 表格中的系統資料

此查詢會排除 TABLE_NAME 值為 NULL 或表格名稱中包含斜線 / 或句點 . 的頁面，這些頁面表示使用者定義的表格，以此提供包含系統資料的頁面的概略計數。

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
+----------+
| COUNT(*) |
+----------+
|     1516 |
+----------+

此查詢會傳回包含系統資料的頁面概略數量、緩衝池頁面總數，以及包含系統資料的頁面概略百分比。

mysql> SELECT
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0)
       ) AS system_pages,
       (
       SELECT COUNT(*)
       FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       ) AS total_pages,
       (
       SELECT ROUND((system_pages/total_pages) * 100)
       ) AS system_page_percentage;
+--------------+-------------+------------------------+
| system_pages | total_pages | system_page_percentage |
+--------------+-------------+------------------------+
|          295 |        8192 |                      4 |
+--------------+-------------+------------------------+

緩衝池中系統資料的類型可以透過查詢 PAGE_TYPE 值來判斷。例如，下列查詢會傳回包含系統資料的頁面中的八個不同的 PAGE_TYPE 值

mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
+-------------------+
| PAGE_TYPE         |
+-------------------+
| SYSTEM            |
| IBUF_BITMAP       |
| UNKNOWN           |
| FILE_SPACE_HEADER |
| INODE             |
| UNDO_LOG          |
| ALLOCATED         |
+-------------------+

範例 17.7 查詢 INNODB_BUFFER_PAGE 表格中的使用者資料

此查詢透過計算 TABLE_NAME 值為 NOT NULL 且 NOT LIKE '%INNODB_TABLES%' 的頁面，提供包含使用者資料的頁面的概略計數。

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

此查詢會傳回包含使用者資料的頁面概略數量、緩衝池頁面總數，以及包含使用者資料的頁面概略百分比。

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 |
+------------+-------------+----------------------+
|       7897 |        8192 |                   96 |
+------------+-------------+----------------------+

此查詢會識別緩衝池中具有頁面的使用者定義表格

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`.`innodb_%';
+-------------------------+
| TABLE_NAME              |
+-------------------------+
| `employees`.`salaries`  |
| `employees`.`employees` |
+-------------------------+

範例 17.8 查詢 INNODB_BUFFER_PAGE 表格中的索引資料

如需索引頁面的相關資訊，請使用索引名稱查詢 INDEX_NAME 欄位。例如，下列查詢會傳回定義在 employees.salaries 表格上的 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 = '`employees`.`salaries`';
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| emp_no     |  1609 |              25 |
+------------+-------+-----------------+

此查詢會傳回在 employees.salaries 資料表上定義的所有索引的頁數和頁面總資料大小。

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 TABLE_NAME = '`employees`.`salaries`'
       GROUP BY INDEX_NAME;
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| emp_no     |  1608 |              25 |
| PRIMARY    |  6086 |              95 |
+------------+-------+-----------------+

範例 17.9：查詢 INNODB_BUFFER_PAGE_LRU 表格中的 LRU_POSITION 資料

INNODB_BUFFER_PAGE_LRU 表格會保存 InnoDB 緩衝池中頁面的相關資訊，特別是它們的排序方式，這決定了緩衝池滿時要從緩衝池中逐出的頁面。此頁面的定義與 INNODB_BUFFER_PAGE 的定義相同，但此表格具有 LRU_POSITION 欄位，而不是 BLOCK_ID 欄位。

此查詢會計算 employees.employees 資料表的頁面在 LRU 清單中特定位置所佔據的位置數量。

mysql> SELECT COUNT(LRU_POSITION) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU
       WHERE TABLE_NAME='`employees`.`employees`' AND LRU_POSITION < 3072;
+---------------------+
| COUNT(LRU_POSITION) |
+---------------------+
|                 548 |
+---------------------+

範例 17.10：查詢 INNODB_BUFFER_POOL_STATS 表格

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: 8192
                    FREE_BUFFERS: 1
                  DATABASE_PAGES: 8173
              OLD_DATABASE_PAGES: 3014
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 0
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 15907
            PAGES_NOT_MADE_YOUNG: 3803101
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 3270
            NUMBER_PAGES_CREATED: 13176
            NUMBER_PAGES_WRITTEN: 15109
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 33069332
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 2713
       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

為了進行比較，以下顯示了基於相同資料集的 SHOW ENGINE INNODB STATUS 輸出和 InnoDB 緩衝池狀態變數輸出。

有關 SHOW ENGINE INNODB STATUS 輸出的更多資訊，請參閱第 17.17.3 節，「InnoDB 標準監視器和鎖定監視器輸出」。

mysql> SHOW ENGINE INNODB STATUS\G
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 579084
Buffer pool size   8192
Free buffers       1
Database pages     8173
Old database pages 3014
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 15907, not young 3803101
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3270, created 13176, written 15109
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8173, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
...

有關狀態變數描述，請參閱第 7.1.10 節，「伺服器狀態變數」。

mysql> SHOW STATUS LIKE 'Innodb_buffer%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status        | not started |
| Innodb_buffer_pool_load_status        | not started |
| Innodb_buffer_pool_resize_status      | not started |
| Innodb_buffer_pool_pages_data         | 8173        |
| Innodb_buffer_pool_bytes_data         | 133906432   |
| Innodb_buffer_pool_pages_dirty        | 0           |
| Innodb_buffer_pool_bytes_dirty        | 0           |
| Innodb_buffer_pool_pages_flushed      | 15109       |
| Innodb_buffer_pool_pages_free         | 1           |
| Innodb_buffer_pool_pages_misc         | 18          |
| Innodb_buffer_pool_pages_total        | 8192        |
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 2713        |
| Innodb_buffer_pool_read_ahead_evicted | 0           |
| Innodb_buffer_pool_read_requests      | 33069332    |
| Innodb_buffer_pool_reads              | 558         |
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 11985961    |
+---------------------------------------+-------------+