MySQL5.6 InnoDB INFORMATION_SCHEMA 버퍼풀 테이블

14.14.5 InnoDB INFORMATION_SCHEMA 버퍼풀 테이블

InnoDB INFORMATION_SCHEMA 버퍼 풀 테이블은 버퍼 풀의 상태 정보 및 InnoDB 버퍼 풀의 페이지에 대한 메타 데이터를 제공합니다. 이 테이블은 MySQL 5.6.2,에서 도입되고 나중에 MySQL 5.5 (MySQL 5.5.28) 및 MySQL 5.1 (MySQL 5.1.66)으로 백 포트되었습니다.

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                      |
+-----------------------------------------------+
3 rows in set (0.00 sec)

테이블의 개요

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 테이블의 쿼리는 상당한 성능 오버 헤드가 도입 될 수 있습니다. 쿼리에 의해 발생할 수있는 성능에 미치는 영향을 인식하고 또한 그것이 허용 가능하다고 판단하지 않는 한, 이러한 테이블을 운영 시스템에서는 쿼리하지 마십시오. 성능에 미치는 영향을 방지하기 위해 조사하려고하고있는 문제를 테스트 인스턴스로 재현 테스트 인스턴스에서 쿼리를 실행하십시오.

예 14.17 INNODB_BUFFER_PAGE 테이블의 시스템 데이터 쿼리

이 쿼리는 TABLE_NAME 값이 NULL 인지, 혹은 그 테이블 이름에 슬래시 " / "또는 마침표" . "를 포함 (이것은 사용자 정의 테이블을 나타냅니다) 페이지를 제외하여 시스템 데이터를 포함 페이지의 근사치를 제공합니다.

SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 
WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0);
+----------+
| COUNT(*) |
+----------+
|     1320 |
+----------+
1 row in set (0.12 sec)

이 쿼리는 시스템 데이터를 포함하는 페이지의 개수 버퍼 풀 페이지 수 및 시스템 데이터가 포함 된 페이지의 대략적인 비율 (%)을 반환합니다.

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 |
+--------------+-------------+------------------------+
|         1320 |        8192 |                     16 |
+--------------+-------------+------------------------+
1 row in set (0.15 sec)

버퍼 풀의 시스템 데이터 유형은 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       |
| UNDO_LOG          |
| UNKNOWN           |
| FILE_SPACE_HEADER |
| INODE             |
| ALLOCATED         |
| TRX_SYSTEM        |
+-------------------+
8 rows in set (0.05 sec)

예 14.18 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(*) |
+----------+
|     6872 |
+----------+
1 row in set (0.06 sec)

이 쿼리는 사용자 데이터를 포함하는 페이지의 개수 버퍼 풀 페이지 수 및 사용자 데이터가 포함 된 페이지의 대략적인 비율 (%)을 반환합니다.

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 |
+------------+-------------+----------------------+
|       6872 |        8192 |                   84 |
+------------+-------------+----------------------+
1 row in set (0.08 sec)

이 쿼리는 버퍼 풀의 페이지를 포함하는 사용자 정의 테이블을 식별합니다.

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` |
+-------------------------+
2 rows in set (0.09 sec)

예 14.19 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     |  1756 |              27 |
+------------+-------+-----------------+
1 row in set (0.07 sec)

이 쿼리는 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     |  1756 |              27 |
| PRIMARY    |  4838 |              76 |
+------------+-------+-----------------+
2 rows in set (0.12 sec)

예 14.20 INNODB_BUFFER_PAGE_LRU 테이블의 LRU_POSITION 데이터 쿼리

INNODB_BUFFER_PAGE_LRU 테이블은 InnoDB 버퍼 풀의 페이지에 대한 정보, 특히 꽉 찬 경우 버퍼 풀에서 어떤 페이지를 삭제할지 여부를 결정하는 각 페이지의 순서를 유지하고 있습니다. 이 페이지의 정의는이 테이블에 BLOCK_ID 컬럼 대신 LRU_POSITION 열이있는 것을 제외하고 INNODB_BUFFER_PAGE 의 경우와 동일합니다.

이 쿼리는 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) |
+---------------------+
|                 275 |
+---------------------+
1 row in set (0.04 sec)

예 14.21 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 : 1024
                   DATABASE_PAGES : 7029
               OLD_DATABASE_PAGES : 2574
          MODIFIED_DATABASE_PAGES : 0
               PENDING_DECOMPRESS : 0
                    PENDING_READS : 0
                PENDING_FLUSH_LRU : 0
               PENDING_FLUSH_LIST : 0
                 PAGES_MADE_YOUNG : 173
             PAGES_NOT_MADE_YOUNG : 3721891
            PAGES_MADE_YOUNG_RATE : 0
        PAGES_MADE_NOT_YOUNG_RATE : 0
                NUMBER_PAGES_READ : 1075
             NUMBER_PAGES_CREATED : 12594
             NUMBER_PAGES_WRITTEN : 13525
                  PAGES_READ_RATE : 0
                PAGES_CREATE_RATE : 0
               PAGES_WRITTEN_RATE : 0
                 NUMBER_PAGES_GET : 27873240
                         HIT_RATE : 0
     YOUNG_MAKE_PER_THOUSAND_GETS : 0
 NOT_YOUNG_MAKE_PER_THOUSAND_GETS : 0
          NUMBER_PAGES_READ_AHEAD : 576
        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)

비교를 위해 동일한 데이터 세트에 근거한 SHOW ENGINE INNODB STATUS 출력 및 InnoDB 버퍼 풀의 상태 변수의 출력을 보여줍니다.

SHOW ENGINE INNODB STATUS 출력의 자세한 내용은 섹션 14.15.3 "InnoDB 표준 모니터 및 잠금 모니터 출력" 을 참조하십시오.

  mysql> SHOW ENGINE INNODB STATUS \ G
 ...
 ----------------------
 BUFFER POOL AND MEMORY
 ----------------------
 Total memory allocated 137363456; in additional pool allocated 0
 Dictionary memory allocated 99725
 Buffer pool size 8192
 Free buffers 1024
 Database pages 7029
 Old database pages 2574
 Modified db pages 0
 Pending reads 0
 Pending writes : LRU 0, flush list 0, single page 0
 Pages made young 173 not young 3721891
 0.00 youngs / s, 0.00 non-youngs / s
 Pages read 1075, created 12594, written 13525
 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 : 7029, unzip_LRU len : 0
 I / O sum [0] : cur [0], unzip sum [0] : cur [0]
 ...

상태 변수에 대한 설명은 섹션 5.1.6 "서버 상태 변수" 를 참조하십시오.

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_pages_data         | 7029        |
| Innodb_buffer_pool_bytes_data         | 115163136   |
| Innodb_buffer_pool_pages_dirty        | 0           |
| Innodb_buffer_pool_bytes_dirty        | 0           |
| Innodb_buffer_pool_pages_flushed      | 13525       |
| Innodb_buffer_pool_pages_free         | 1024        |
| Innodb_buffer_pool_pages_misc         | 139         |
| Innodb_buffer_pool_pages_total        | 8192        |
| Innodb_buffer_pool_read_ahead_rnd     | 0           |
| Innodb_buffer_pool_read_ahead         | 576         |
| Innodb_buffer_pool_read_ahead_evicted | 0           |
| Innodb_buffer_pool_read_requests      | 27873240    |
| Innodb_buffer_pool_reads              | 500         |
| Innodb_buffer_pool_wait_free          | 0           |
| Innodb_buffer_pool_write_requests     | 11966441    |
+---------------------------------------+-------------+
17 rows in set (0.00 sec)