17.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables

17.15.5 InnoDB INFORMATION_SCHEMA Buffer Pool Tables

InnoDB``INFORMATION_SCHEMA buffer pool 테이블은 buffer pool 상태 정보와 InnoDB buffer pool 내 페이지에 대한 메타데이터를 제공합니다.

InnoDB``INFORMATION_SCHEMA buffer pool 테이블에는 다음과 같은 것들이 포함됩니다:

1mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_BUFFER%';
2+-----------------------------------------------+
3| Tables_in_INFORMATION_SCHEMA (INNODB_BUFFER%) |
4+-----------------------------------------------+
5| INNODB_BUFFER_PAGE_LRU                        |
6| INNODB_BUFFER_PAGE                            |
7| INNODB_BUFFER_POOL_STATS                      |
8+-----------------------------------------------+

Table Overview

• INNODB_BUFFER_PAGE: InnoDB buffer pool 내 각 페이지에 대한 정보를 저장합니다.

• INNODB_BUFFER_PAGE_LRU: InnoDB buffer pool 내 페이지들에 대한 정보를 저장하며, 특히 buffer pool이 가득 찼을 때 어떤 페이지를 buffer pool에서 제거할지를 결정하는 LRU 리스트 상에서의 정렬 순서에 대한 정보를 저장합니다. INNODB_BUFFER_PAGE_LRU 테이블은 INNODB_BUFFER_PAGE 테이블과 동일한 컬럼을 가지지만, INNODB_BUFFER_PAGE_LRU 테이블은 BLOCK_ID 컬럼 대신 LRU_POSITION 컬럼을 가집니다.

• INNODB_BUFFER_POOL_STATS: buffer pool 상태 정보를 제공합니다. 동일한 정보의 상당 부분은 SHOW ENGINE INNODB STATUS 출력으로 제공되거나, InnoDB buffer pool 서버 상태 변수를 사용하여 얻을 수 있습니다.

주의

INNODB_BUFFER_PAGE 또는 INNODB_BUFFER_PAGE_LRU 테이블을 조회하는 것은 성능에 영향을 줄 수 있습니다. 성능 영향에 대해 잘 알고 있고 그 영향이 허용 가능하다고 판단한 경우가 아니라면, 프로덕션 시스템에서 이 테이블들을 조회하지 마십시오.

프로덕션 시스템의 성능에 영향을 주지 않기 위해서는, 조사하려는 이슈를 테스트 인스턴스에서 재현한 후 해당 테스트 인스턴스에서 buffer pool 통계를 조회하십시오.

Example 17.6 INNODB_BUFFER_PAGE Table에서 System Data 조회

이 쿼리는 시스템 데이터를 포함하는 페이지의 대략적인 개수를 제공합니다. 여기서는 TABLE_NAME 값이 NULL 이거나, 테이블 이름에 슬래시 / 또는 마침표 . 가 포함된 페이지를 제외하는데, 이는 사용자 정의 테이블을 나타냅니다.

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

이 쿼리는 시스템 데이터를 포함하는 페이지의 대략적인 개수와 전체 buffer pool 페이지 수, 그리고 시스템 데이터를 포함하는 페이지의 대략적인 비율을 반환합니다.

1mysql> SELECT
2       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
3       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0)
4       ) AS system_pages,
5       (
6       SELECT COUNT(*)
7       FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
8       ) AS total_pages,
9       (
10       SELECT ROUND((system_pages/total_pages) * 100)
11       ) AS system_page_percentage;
12+--------------+-------------+------------------------+
13| system_pages | total_pages | system_page_percentage |
14+--------------+-------------+------------------------+
15|          295 |        8192 |                      4 |
16+--------------+-------------+------------------------+

buffer pool 내 시스템 데이터의 유형은 PAGE_TYPE 값을 조회하여 결정할 수 있습니다. 예를 들어, 다음 쿼리는 시스템 데이터를 포함하는 페이지들 사이에서 서로 다른 8개의 PAGE_TYPE 값을 반환합니다:

1mysql> SELECT DISTINCT PAGE_TYPE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
2       WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
3+-------------------+
4| PAGE_TYPE         |
5+-------------------+
6| SYSTEM            |
7| IBUF_BITMAP       |
8| UNKNOWN           |
9| FILE_SPACE_HEADER |
10| INODE             |
11| UNDO_LOG          |
12| ALLOCATED         |
13+-------------------+

Example 17.7 INNODB_BUFFER_PAGE Table에서 User Data 조회

이 쿼리는 사용자 데이터를 포함하는 페이지의 대략적인 개수를 제공합니다. 여기서는 TABLE_NAME 값이 NOT NULL 이고 NOT LIKE '%INNODB_TABLES%' 인 페이지의 개수를 셉니다.

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

이 쿼리는 사용자 데이터를 포함하는 페이지의 대략적인 개수와 전체 buffer pool 페이지 수, 그리고 사용자 데이터를 포함하는 페이지의 대략적인 비율을 반환합니다.

1mysql> SELECT
2       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
3       WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
4       ) AS user_pages,
5       (
6       SELECT COUNT(*)
7       FROM information_schema.INNODB_BUFFER_PAGE
8       ) AS total_pages,
9       (
10       SELECT ROUND((user_pages/total_pages) * 100)
11       ) AS user_page_percentage;
12+------------+-------------+----------------------+
13| user_pages | total_pages | user_page_percentage |
14+------------+-------------+----------------------+
15|       7897 |        8192 |                   96 |
16+------------+-------------+----------------------+

이 쿼리는 buffer pool에 페이지를 가지고 있는 사용자 정의 테이블을 식별합니다:

1mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
2       WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)
3       AND TABLE_NAME NOT LIKE '`mysql`.`innodb_%';
4+-------------------------+
5| TABLE_NAME              |
6+-------------------------+
7| `employees`.`salaries`  |
8| `employees`.`employees` |
9+-------------------------+

Example 17.8 INNODB_BUFFER_PAGE Table에서 Index Data 조회

인덱스 페이지에 대한 정보는 인덱스의 이름을 사용하여 INDEX_NAME 컬럼을 조회함으로써 확인할 수 있습니다. 예를 들어, 다음 쿼리는 employees.salaries 테이블에 정의된 emp_no 인덱스에 대한 페이지 수와 해당 페이지들의 전체 데이터 크기를 반환합니다:

1mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
2ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
3AS 'Total Data (MB)'
4FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
5WHERE INDEX_NAME='emp_no' AND TABLE_NAME = '`employees`.`salaries`';
6+------------+-------+-----------------+
7| INDEX_NAME | Pages | Total Data (MB) |
8+------------+-------+-----------------+
9| emp_no     |  1609 |              25 |
10+------------+-------+-----------------+

이 쿼리는 employees.salaries 테이블에 정의된 모든 인덱스에 대해, 각각의 페이지 수와 전체 데이터 크기를 반환합니다:

1mysql> SELECT INDEX_NAME, COUNT(*) AS Pages,
2       ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024)
3       AS 'Total Data (MB)'
4       FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
5       WHERE TABLE_NAME = '`employees`.`salaries`'
6       GROUP BY INDEX_NAME;
7+------------+-------+-----------------+
8| INDEX_NAME | Pages | Total Data (MB) |
9+------------+-------+-----------------+
10| emp_no     |  1608 |              25 |
11| PRIMARY    |  6086 |              95 |
12+------------+-------+-----------------+

Example 17.9 INNODB_BUFFER_PAGE_LRU Table의 LRU_POSITION Data 조회

INNODB_BUFFER_PAGE_LRU 테이블은 InnoDB buffer pool 내 페이지들에 대한 정보를 저장하며, 특히 buffer pool이 가득 찼을 때 어떤 페이지를 buffer pool에서 제거할지를 결정하는 순서에 대한 정보를 저장합니다. 이 페이지에 대한 정의는 INNODB_BUFFER_PAGE와 동일하지만, 이 테이블은 BLOCK_ID 컬럼 대신 LRU_POSITION 컬럼을 가집니다.

다음 쿼리는 LRU 리스트에서 특정 위치보다 앞쪽에 있는, employees.employees 테이블의 페이지가 차지하고 있는 position 수를 셉니다.

1mysql> SELECT COUNT(LRU_POSITION) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU
2       WHERE TABLE_NAME='`employees`.`employees`' AND LRU_POSITION < 3072;
3+---------------------+
4| COUNT(LRU_POSITION) |
5+---------------------+
6|                 548 |
7+---------------------+

Example 17.10 INNODB_BUFFER_POOL_STATS Table 조회

INNODB_BUFFER_POOL_STATS 테이블은 SHOW ENGINE INNODB STATUS 및 InnoDB buffer pool 상태 변수와 유사한 정보를 제공합니다.

1mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS \G
2*************************** 1. row ***************************
3                         POOL_ID: 0
4                       POOL_SIZE: 8192
5                    FREE_BUFFERS: 1
6                  DATABASE_PAGES: 8173
7              OLD_DATABASE_PAGES: 3014
8         MODIFIED_DATABASE_PAGES: 0
9              PENDING_DECOMPRESS: 0
10                   PENDING_READS: 0
11               PENDING_FLUSH_LRU: 0
12              PENDING_FLUSH_LIST: 0
13                PAGES_MADE_YOUNG: 15907
14            PAGES_NOT_MADE_YOUNG: 3803101
15           PAGES_MADE_YOUNG_RATE: 0
16       PAGES_MADE_NOT_YOUNG_RATE: 0
17               NUMBER_PAGES_READ: 3270
18            NUMBER_PAGES_CREATED: 13176
19            NUMBER_PAGES_WRITTEN: 15109
20                 PAGES_READ_RATE: 0
21               PAGES_CREATE_RATE: 0
22              PAGES_WRITTEN_RATE: 0
23                NUMBER_PAGES_GET: 33069332
24                        HIT_RATE: 0
25    YOUNG_MAKE_PER_THOUSAND_GETS: 0
26NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
27         NUMBER_PAGES_READ_AHEAD: 2713
28       NUMBER_READ_AHEAD_EVICTED: 0
29                 READ_AHEAD_RATE: 0
30         READ_AHEAD_EVICTED_RATE: 0
31                    LRU_IO_TOTAL: 0
32                  LRU_IO_CURRENT: 0
33                UNCOMPRESS_TOTAL: 0
34              UNCOMPRESS_CURRENT: 0

비교를 위해, 동일한 데이터 세트를 기반으로 한 SHOW ENGINE INNODB STATUS 출력과 InnoDB buffer pool 상태 변수 출력이 아래에 나와 있습니다.

SHOW ENGINE INNODB STATUS 출력에 대한 자세한 내용은 Section 17.17.3, “InnoDB Standard Monitor and Lock Monitor Output”를 참조하십시오.

1mysql> SHOW ENGINE INNODB STATUS\G
2...
3----------------------
4BUFFER POOL AND MEMORY
5----------------------
6Total large memory allocated 137428992
7Dictionary memory allocated 579084
8Buffer pool size   8192
9Free buffers       1
10Database pages     8173
11Old database pages 3014
12Modified db pages  0
13Pending reads 0
14Pending writes: LRU 0, flush list 0, single page 0
15Pages made young 15907, not young 3803101
160.00 youngs/s, 0.00 non-youngs/s
17Pages read 3270, created 13176, written 15109
180.00 reads/s, 0.00 creates/s, 0.00 writes/s
19No buffer pool page gets since the last printout
20Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
21LRU len: 8173, unzip_LRU len: 0
22I/O sum[0]:cur[0], unzip sum[0]:cur[0]
23...

상태 변수 설명은 Section 7.1.10, “Server Status Variables”를 참조하십시오.

1mysql> SHOW STATUS LIKE 'Innodb_buffer%';
2+---------------------------------------+-------------+
3| Variable_name                         | Value       |
4+---------------------------------------+-------------+
5| Innodb_buffer_pool_dump_status        | not started |
6| Innodb_buffer_pool_load_status        | not started |
7| Innodb_buffer_pool_resize_status      | not started |
8| Innodb_buffer_pool_pages_data         | 8173        |
9| Innodb_buffer_pool_bytes_data         | 133906432   |
10| Innodb_buffer_pool_pages_dirty        | 0           |
11| Innodb_buffer_pool_bytes_dirty        | 0           |
12| Innodb_buffer_pool_pages_flushed      | 15109       |
13| Innodb_buffer_pool_pages_free         | 1           |
14| Innodb_buffer_pool_pages_misc         | 18          |
15| Innodb_buffer_pool_pages_total        | 8192        |
16| Innodb_buffer_pool_read_ahead_rnd     | 0           |
17| Innodb_buffer_pool_read_ahead         | 2713        |
18| Innodb_buffer_pool_read_ahead_evicted | 0           |
19| Innodb_buffer_pool_read_requests      | 33069332    |
20| Innodb_buffer_pool_reads              | 558         |
21| Innodb_buffer_pool_wait_free          | 0           |
22| Innodb_buffer_pool_write_requests     | 11985961    |
23+---------------------------------------+-------------+