Shuo
Shuo I'm a DBA(Database Administrator), we can share and discuss MySQL, MongoDB, Redis and other databases here, also including learning Python, Shell, Golang together.

InnoDB缓冲池中的内容

InnoDB缓冲池中的内容

缓冲池信息相关的表

查看缓冲池中的信息,主要是通过information_schema库下的三张表进行查看:
+ INNODB_BUFFER_PAGE:INNODB缓冲池中page的情况
+ INNODB_BUFFER_PAGE_LRU:INNODB缓冲池中page的LRU(Least Recently Used)情况
+ INNODB_BUFFER_POOL_STATS:INNODB缓冲池状态总览

1
2
3
4
5
6
7
8
9
root:information_schema> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';
+-----------------------------------------------+
| Tables_in_information_schema (INNODB_BUFFER%) |
+-----------------------------------------------+
| INNODB_BUFFER_PAGE                            |
| INNODB_BUFFER_PAGE_LRU                        |
| INNODB_BUFFER_POOL_STATS                      |
+-----------------------------------------------+

详情查看:[InnoDB INFORMATION_SCHEMA Buffer Pool Tables]
注意事项:

查询 INNODB_BUFFER_PAGE 或者 INNODB_BUFFER_PAGE_LRU 会影响数据库的性能!

(https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-buffer-pool-tables.html)

从INNODB_BUFFER_PAGE表查询系统数据

系统数据记为MySQL用以存储数据库实例相关状态或者配置的数据,这部分数据同样会部分缓存在缓冲池中。

1
2
3
4
5
6
7
8
root:information_schema> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE        WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
+----------+
| COUNT(*) |
+----------+
|    29573 |
+----------+
1 row in set (0.25 sec)

系统数据在缓冲池中的分布

查看系统数据在整个缓冲池中的分布情况:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 |
+--------------+-------------+------------------------+
|        29573 |       32768 |                     90 |
+--------------+-------------+------------------------+

缓存中系统数据的属性

PAGE_TYPE字段可以查看系统数据的属性:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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            |
| INODE             |
| IBUF_INDEX        |
| TRX_SYSTEM        |
| RSEG_ARRAY        |
| UNDO_LOG          |
| FILE_SPACE_HEADER |
| IBUF_BITMAP       |
| LOB_FIRST         |
| LOB_DATA          |
| LOB_INDEX         |
| UNKNOWN           |
| INDEX             |
+-------------------+
13 rows in set (0.37 sec)

从INNODB_BUFFER_PAGE表查询用户数据

1
2
3
4
5
6
7
8
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE '%INNODB_TABLES%';
+----------+
| COUNT(*) |
+----------+
|     3195 |
+----------+

用户数据在缓冲池中的分布情况

同理,查看用户数据在缓冲池中的分布情况:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 |
+------------+-------------+----------------------+
|       3195 |       32768 |                   10 |
+------------+-------------+----------------------+

查看用户数据对应的表

1
2
3
4
5
6
7
8
9
10
11
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           |
+----------------------+
| `wstestdb`.`sbtest2` |
| `wstestdb`.`sbtest1` |
+----------------------+
2 rows in set (0.15 sec)

从INNODB_BUFFER_PAGE表查看索引数据

当然,也可以使用该表查看索引被缓存的情况情况。索引缓存情况可以对比另一篇文章查看InnoDB索引在缓存中的情况

1
2
3
4
5
6
7
8
9
10
11
12
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='k_2' AND TABLE_NAME = '`wstestdb`.`sbtest2`';
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| k_2        |   167 |               3 |
+------------+-------+-----------------+
1 row in set (0.19 sec)

查看某个表的所有索引缓存情况:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 = '`wstestdb`.`sbtest2`'
       GROUP BY INDEX_NAME;
+------------+-------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+------------+-------+-----------------+
| PRIMARY    |  1767 |              28 |
| k_2        |   167 |               3 |
+------------+-------+-----------------+
2 rows in set (0.21 sec)

查询LRU_POSITION信息

LRU可以反映表中热数据的情况,可以参考这个状态,判断是否会影响sql的查询,或者是否需要刷数据,使数据变“热”。

1
2
3
4
5
6
7
8
9
root:information_schema> select table_name,INDEX_NAME,min(LRU_POSITION),max(LRU_POSITION)  from   INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU where table_name='`wstestdb`.`sbtest2`' group by INDEX_NAME;
+----------------------+------------+-------------------+-------------------+
| table_name           | INDEX_NAME | min(LRU_POSITION) | max(LRU_POSITION) |
+----------------------+------------+-------------------+-------------------+
| `wstestdb`.`sbtest2` | PRIMARY    |               485 |              4273 |
| `wstestdb`.`sbtest2` | k_2        |               527 |              2910 |
+----------------------+------------+-------------------+-------------------+
2 rows in set (0.03 sec)

查询INNODB_BUFFER_POOL_STATS表

查询INNODB_BUFFER_POOL_STATS表和直接执行show engine innodb status \G查看Innodb_buffer的状态的结果相似:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
root:information_schema> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 32768
                    FREE_BUFFERS: 27417
                  DATABASE_PAGES: 5169
              OLD_DATABASE_PAGES: 1888
         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: 3590
            NUMBER_PAGES_CREATED: 1579
            NUMBER_PAGES_WRITTEN: 68456
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 8.973448067156127
                NUMBER_PAGES_GET: 25963207
                        HIT_RATE: 1000
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 0
       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
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
mysql> SHOW ENGINE INNODB STATUS \G
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 549453824
Dictionary memory allocated 494195
Buffer pool size   32768
Free buffers       27417
Database pages     5169
Old database pages 1888
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3590, created 1579, written 68456
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/sLRU len: 5169, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

root:information_schema> SHOW STATUS LIKE 'Innodb_buffer%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 190911  6:50:47 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 5169                                             |
| Innodb_buffer_pool_bytes_data         | 84688896                                         |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 68446                                            |
| Innodb_buffer_pool_pages_free         | 27417                                 -           |
| Innodb_buffer_pool_pages_misc         | 182                                              |
| Innodb_buffer_pool_pages_total        | 32768                                            |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 25963233                                         |
| Innodb_buffer_pool_reads              | 3384                                             |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 4119405                                          |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.01 sec)

comments powered by Disqus