[MySQL] Information Schema - InnoDB 버퍼풀 테이블

InnoDB 버퍼풀 테이블

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

 

■ Table Overview

+ INNODB_BUFFER_PAGE : InnoDB 버퍼풀의 페이지에 대한 정보를 보관합니다.

+ INNODB_BUFFER_PAGE_LRU : InnoDB 버퍼풀의 페이지에 대한 정보, 특히 모두 사용중인 버퍼 풀에서 제거할 페이지를 결정하는 LRU 목록에서 순서가 지정되는 방식에 대한 정보를 보유합니다. INNODB_BUFFER_PAGE_LRU 테이블에는 INNODB_BUFFER_PAGE 테이블과 동일한 열이 있지만, INNODB_BUFFER_PAGE_LRU 테이블에는 BLOCK_ID 대신 LRU_POSITION 열이 있습니다.

+ INNODB_BUFFER_POOL_STATS : 버퍼풀 상태 정보를 제공합니다. 많은 동일한 정보가 SHOW ENGINE INNODB STATUS 출력에 의해 제공되거나 InnoDB 버퍼풀 서버상태 변수를 사용하여 얻을 있습니다.

 

조심할 사항.

INNODB_BUFFER_PAGE 또는 INNODB_BUFFER_PAGE_LRU 테이블을 쿼리하면 성능에 영향을 있습니다. 성능 영향을 인식하고 수용 가능한 것으로 결정하지 않는 프로덕션 시스템에서 이러한 테이블을 조회하면 안됩니다. 프로덕션 시스템의 성능에 영향을 주지 않으려면 조사하려는 문제를 재현하고 테스트 인스턴스에서 버퍼풀 통계를 조회합니다.

 

예제 1 : 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 값을 쿼리하여 식별할 있습니다. 예를 들어 다음 쿼리는 시스템 데이터가 포함된 페이지에서 8개의 고유한 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         |
+-------------------+

 

예제 2 : INNODB_BUFFER_PAGE 테이블에서 사용자 데이터 쿼리

쿼리는 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(*) |
+----------+
|     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` |
+-------------------------+

 

예제 3 : INNODB_BUFFER_PAGE 테이블에서 인덱스 데이터 쿼리

인덱스 페이지에 대한 자세한 내용은 인덱스 이름을 사용하여 INDEX_NAME컬럼을 조회합니다. 예를 들어, 다음 쿼리는 employee.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 |
+------------+-------+-----------------+

 

쿼리는 employee.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 |
+------------+-------+-----------------+

 

예제 4 :  INNODB_BUFFER_PAGE_LRU 테이블에서 LRU_POSITION 데이터 쿼리

INNODB_BUFFER_PAGE_LRU 테이블은 InnoDB 버퍼 풀의 페이지에 대한 정보, 특히 버퍼풀이 가득 차면 버퍼풀에서 제거할 페이지를 결정하는 순서가 지정됩니다. 페이지에 대한 정의는 INNODB_BUFFER_PAGE 경우와 동일하지만이 테이블에 BLOCK_ID컬럼 대신 LRU_POSITION컬럼이 있습니다.

 

쿼리는 employee.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 |
+---------------------+

 

예제 5 : 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 버퍼풀 상태변수 출력은 동일한 데이터 세트를 기반으로하기 때문에 다음과 같습니다.

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

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

 

Designed by JB FACTORY