[MySQL] Information Schema - InnoDB 버퍼풀 테이블
- Databases/MySQL
- 2020. 12. 27.
■ 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 |
+---------------------------------------+-------------+
'Databases > MySQL' 카테고리의 다른 글
| [MySQL] 서버 Parameter 설정 (0) | 2021.01.10 |
|---|---|
| [MySQL] Information Schema - InnoDB 메트릭 테이블 (0) | 2021.01.07 |
| [MySQL] Information Schema - InnoDB 시스템 테이블 (0) | 2020.12.25 |
| [MySQL] Information Schema - InnoDB : 2번째 (0) | 2020.12.22 |
| [MySQL] Information Schema - InnoDB : 1번째 (0) | 2020.12.22 |