[MySQL][InnoDB] InnoDB 스키마 정보 테이블

앞축에 관련된 InnoDB INFORMATION_SCHEMA 테이블

압축에 관한 InnoDB INFORMATION_SCHEMA 테이블에는 압축이 전체적으로 얼마나 작동하는지에 대한 아주 유용한 정보를 제공하는 두개의 테이블이 있습니다.

+ INNODB_CMP INNODB_CMP_RESET 압축 조작 압축 수행에 소요된 시간에 대한 정보를 제공합니다.

+ INNODB_CMPMEM INNODB_CMPMEM_RESET 메모리가 압축을 위해 할당되는 방법에 대한 정보를 제공합니다.

 

INNODB_CMP INNODB_CMP_RESET

INNODB_CMP INNODB_CMP_RESET 테이블은 압축 테이블 관련 작업에 대한 상태 정보를 제공합니다. PAGE_SIZE열은 압축된 페이지 크기를 나타냅니다.

테이블의 내용은 동일하지만 INNODB_CMP_RESET에서 읽으면 압축 압축 해제 조작에 대한 통계가 재설정됩니다. 예를 들어 60 분마다 INNODB_CMP_RESET 출력을 아카이브하면 시간별 통계가 표시됩니다. INNODB_CMP 출력을 모니터링하면 (INNODB_CMP_RESET 읽지 않아야 ) InnoDB 시작된 이후 누적 통계가 표시됩니다.

 

INNODB_CMPMEM INNODB_CMPMEM_RESET

INNODB_CMPMEM INNODB_CMPMEM_RESET 테이블은 버퍼풀에 있는 압축 페이지에 대한 상태 정보를 제공합니다. INNODB_CMP INNODB_CMP_RESET 테이블은 압축에 대한 유용한 통계를 제공합니다.

 

내부 세부 사항

InnoDB 버디 할당자 시스템을 사용하여 1KB에서 16KB까지 다양한 크기의 페이지에 할당 메모리를 관리합니다. 여기에 설명 테이블의 행은 단일 페이지 크기에 해당합니다.

버디 할당자 시스템 : 리눅스 OS에서 페이지를 관리할때 사용하는 방법. 페이지 프레임을 연속적으로 할당할 필요가 있을 사용.

 

INNODB_CMPMEM INNODB_CMPMEM_RESET 테이블의 내용은 동일하지만 INNODB_CMPMEM_RESET에서 읽으면 재배치 작업에

통계가 재설정됩니다. 예를 들어 60분마다 INNODB_CMPMEM_RESET 출력을 아카이브하면 시간별 통계가 표시됩니다. INNODB_CMPMEM_RESET 읽지 않고 대신 INNODB_CMPMEM 출력을 모니터링하면 InnoDB 시작된 이후 누적 통계가 표시됩니다.

 

 

□ 압축 정보 스키마 테이블 사용

예제)14.1 Using the Compression Information Schema Tables

다음은 압축된 테이블을 포함하는 데이터베이스의 샘플 출력입니다.다음 포스팅의 "InnoDB Table Page 압축”, 그리고 INNODB_CMP, INNODB_CMP_PER_INDEX, and INNODB_CMPMEM 테이블들을 참고합니다.

다음 표는 적은 워크로드에서 INFORMATION_SCHEMA.INNODB_CMP 내용을 보여줍니다. 버퍼 풀에 포함된 유일한 압축 페이지 크기는 8K입니다. COMPRESS_TIME UNCOMPRESS_TIME 열이 0이므로 통계가 재설정된 이후로 페이지를 압축하거나 압축 해제하는데 1 미만이 소요되었습니다.

 

page size compress ops compress ops ok compress time uncompress ops uncompress time
1024 0 0 0 0 0
2048 0 0 0 0 0
4096 0 0 0 0 0
8192 1048 921 0 61 0
16384 0 0 0 0 0

 

 

INNODB_CMPMEM 따르면, 버퍼 풀에는 6169개의 ​​압축된 8KB 페이지가 있습니다. 할당된 다른 블록 크기는 64 바이트뿐입니다. INNODB_CMPMEM에서 가장 작은 PAGE_SIZE 압축되지 않은 페이지가 버퍼 풀에 존재하지 않는 압축된 페이지의 블록 디스크립터에 사용됩니다. 이러한 페이지가 5910 있음을 있습니다. 간접적으로, 259 (6169-5910) 압축 페이지도 압축되지 않은 형태로 버퍼풀에 존재한다는 것을 있습니다.

 

다음 표는 적은 워크로드에서 INFORMATION_SCHEMA.INNODB_CMPMEM 내용을 보여줍니다. 압축된 페이지에 대한 메모리 할당자의 조각화로 인해 일부 메모리를 사용할 없습니다 : SUM (PAGE_SIZE * PAGES_FREE)=6784. 이는 버디 할당 시스템을 사용하여 기본 버퍼 풀에서 할당된 16K 블록부터 시작하여 블록을 분할하여 작은 메모리 할당 요청을 수행하기 때문입니다. 일부 할당된 블록이 인접한 자유블록을 형성하기 위해 재배치(복사) 되었기 때문에 조각화는 수준이 낮습니다. SUM (PAGE_SIZE * RELOCATION_OPS) 바이트 복사에 1 미만이 소요되었습니다 (SUM (RELOCATION_TIME) = 0).

page size pages used pages free relocation ops relocation time
64 5910 0 2436 0
128 0 1 0 0
256 0 0 0 0
512 0 1 0 0
1024 0 0 0 0
2048 0 1 0 0
4096 0 1 0 0
8192 6169 0 5 0
16384 0 0 0 0

 

 

■ InnoDB INFORMATION_SCHEMA Transaction Locking 정보

개의 InnoDB INFORMATION_SCHEMA 테이블을 사용하면 트랜잭션을 모니터링하고 잠재적 잠금 문제를 진단 있습니다.

+ INNODB_TRX : 트랜잭션 상태 ( : 실행 또는 잠금 대기 여부), 트랜잭션 시작시기 트랜잭션이 실행중인 특정 SQL 문을 포함하여 InnoDB 내에서 현재 실행중인 모든 트랜잭션에 대한 정보를 제공합니다.

+ INNODB_LOCKS : 다른 트랜잭션이 잠금을 해제하기를 기다리는 InnoDB 트랜잭션 (INNODB_TRX.TRX_STATE LOCK WAIT ) 정확히 하나의 차단 잠금 요청에 의해 차단됩니다. 해당 차단 잠금 요청은 호환되지 않는 모드에서 다른 트랜잭션이 보유한 또는 테이블 잠금에 대한 것입니다. 트랜잭션을 차단하는 잠금은 항상 요청된 잠금 모드(읽기 / 쓰기 / 공유 / 독점) 호환되지 않는 모드로 유지됩니다. 차단된 트랜잭션은 다른 트랜잭션이 커밋되거나 롤백 될때까지 진행할 없으므로 요청된 잠금이 해제됩니다. 차단된 모든 트랜잭션에 대해 INNODB_LOCKS에는 트랜잭션이 요청하고 대기중인 잠금을 설명하는 하나의 행이 포함됩니다. INNODB_LOCKS에는 잠금을 보유한 트랜잭션 상태 (INNODB_TRX.TRX_STATE RUNNING, LOCK WAIT, ROLLING BACK 또는 COMMITTING) 관계없이 트랜잭션마다 다른 트랜잭션을 차단하는 행마다 하나의 행이 있습니다.

+ INNODB_LOCK_WAITS : 테이블은 주어진 잠금을 기다리는 트랜잭션 또는 주어진 트랜잭션을 기다리는 잠금을 나타냅니다. 테이블에는 차단 트랜잭션에 대해 하나 이상의 행이 포함되어 있으며, 요청한 잠금 해당 요청을 차단하는 잠금을 나타냅니다. REQUESTED_LOCK_ID 값은 트랜잭션이 요청한 잠금을 나타내고 BLOCKING_LOCK_ID 값은 번째 트랜잭션이 진행되지 못하게하는 잠금 (다른 트랜잭션에 의해 보류 ) 나타냅니다. 주어진 차단된 트랜잭션에 대해 INNODB_LOCK_WAITS 모든 행은 REQUESTED_LOCK_ID 대해 동일한 값을 가지며 BLOCKING_LOCK_ID 대해 다른 값을 갖습니다.

 

□ InnoDB Transaction Locking 정보

차단 트랜잭션 식별

어떤 트랜잭션이 다른 트랜잭션을 차단하는지 식별하는 것이 도움이되는 경우가 있습니다. InnoDB 트랜잭션 데이터 잠금에 대한 정보가 포함 테이블을 사용하면 다른 트랜잭션을 기다리는 트랜잭션과 요청중인 리소스를 확인할 있습니다.

 

개의 세션이 동시에 실행되고 있다고 가정합니다. 세션은 MySQL 스레드에 해당하며 한번에 하나씩 트랜잭션을 실행합니다. 이러한 세션이 다음 명령문을 발행했지만 아직 트랜잭션을 커미트하지 않은 시스템 상태를 체크합니다.

+ Session A:
BEGIN;
SELECT a FROM t FOR UPDATE;
SELECT SLEEP(100);

+ Session B:
SELECT b FROM t FOR UPDATE;

+ Session C:
SELECT c FROM t FOR UPDATE;

 

시나리오에서는 다음 쿼리를 사용하여 대기중인 트랜잭션과 차단중인 트랜잭션을 식별합니다.

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

 

또는 간단하게 sys 스키마 innodb_lock_waits보기를 사용합니다.

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

 

차단 쿼리에 대해 NULL 값이 보고되면 발급 세션이 유휴 상태가 차단 쿼리 식별을 참고하니다.

waiting trx id waiting thread waiting query blocking trx id blocking thread blocking query
A4 6 SELECT b FROM t FOR UPDATE A3 5 SELECT SLEEP(100)
A5 7 SELECT c FROM t FOR UPDATE A3 5 SELECT SLEEP(100)
A5 7 SELECT c FROM t FOR UPDATE A4 6 SELECT b FROM t FOR UPDATE

 

 

다음과 같이 위의 표에서 "대기 쿼리"또는 "차단 쿼리"열로 세션을 식별 있습니다.

+ 세션 B (trx id A4, 스레드 6) Session C (trx id A5, 스레드 7) 모두 세션 A (trx id A3, 스레드 5) 기다리고 있습니다.

+ 세션 C 세션 A 세션 B 기다리고 있습니다.

 

INNODB_TRX, INNODB_LOCKS INNODB_LOCK_WAITS 테이블에서 기본 데이터를 있습니다.

다음 표는 INFORMATION_SCHEMA.INNODB_TRX 일부 샘플 내용을 보여줍니다

trx id trx state trx started trx requested
lock id
trx wait
started
trx weight trx mysql thread id trx query
A3 RUN­NING 2008-01-15 16:44:54 NULL NULL 2 5 SELECT SLEEP(100)
A4 LOCK WAIT 2008-01-15 16:45:09 A4:1:3:2 2008-01-15
16:45:09
2 6 SELECT b
FROM t FOR UPDATE
A5 LOCK WAIT 2008-01-15 16:45:14 A5:1:3:2 2008-01-15
16:45:14
2 7 SELECT c
FROM t FOR UPDATE

 

 

다음 표는 INFORMATION_SCHEMA.INNODB_LOCKS 일부 샘플 내용을 보여줍니다.

lock id lock trx id lock mode lock type lock table lock index lock data
A3:1:3:2 A3 X RECORD test.t PRIMARY 0x0200
A4:1:3:2 A4 X RECORD test.t PRIMARY 0x0200
A5:1:3:2 A5 X RECORD test.t PRIMARY 0x0200

 

 

다음 표는 INFORMATION_SCHEMA.INNODB_LOCK_WAITS 일부 샘플 내용을 보여줍니다.

requesting trx id requested lock id blocking trx id blocking lock id
A4 A4:1:3:2 A3 A3:1:3:2
A5 A5:1:3:2 A3 A3:1:3:2
A5 A5:1:3:2 A4 A4:1:3:2

 

 

 

발급 세션이 유휴 상태가 차단 쿼리 식별

차단 트랜잭션을 식별 쿼리를 발행한 세션이 유휴 상태인 경우 차단 쿼리에 대해 NULL값이 보고됩니다. 경우 다음 단계를 사용하여 차단 쿼리를 결정합니다.

1. 차단 트랜잭션의 프로세스 목록 ID 식별합니다. sys.innodb_lock_waits 테이블에서 차단 트랜잭션의 프로세스 목록 ID blocking_pid 값입니다.

 

2. blocking_pid 사용하여 MySQL 성능 스키마 스레드 테이블을 쿼리하여 차단 트랜잭션의 THREAD_ID 결정합니다. 예를 들어 blocking_pid 6이면이 쿼리를 실행합니다.

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

 

3. THREAD_ID 사용하여 성능 스키마 events_statements_current 테이블을 쿼리하여 스레드가 실행 마지막 쿼리를 결정합니다. 예를 들어, THREAD_ID 28 경우이 쿼리를 실행합니다.

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current 
WHERE THREAD_ID = 28\G

 

4. 스레드에 의해 실행 마지막 쿼리가 잠금이 유지되는 이유를 결정하기에 충분한 정보가 아닌 경우 성능 스키마 events_statements_history 테이블을 쿼리하여 스레드에 의해 실행 마지막 10 명령문을 있습니다.

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history 
WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

 

▶ InnoDB 트랜잭션과 MySQL 세션의 상관 관계

때로는 내부 InnoDB 잠금 정보를 MySQL 유지 관리하는 세션 수준 정보와 연관시키는 것이 유용합니다. 예를 들어, 주어진 InnoDB 트랜잭션 ID 대해 잠금을 보유하고 있고 따라서 다른 트랜잭션을 차단할 수있는 해당 MySQL 세션 ID 세션 이름을 알고 싶습니다.

 

INFORMATION_SCHEMA 테이블의 다음 출력은 다소 로드작업이 있는 시스템의 샘플입니다. 보다시피, 개의 트랜잭션이 실행되고 있습니다.

 

다음 INNODB_LOCKS INNODB_LOCK_WAITS 테이블은 다음을 보여줍니다.

+ Transaction 77F (executing an INSERT) is waiting for transactions 77E, 77D, and 77B to commit.

+ Transaction 77E (executing an INSERT) is waiting for transactions 77D and 77B to commit.

+ Transaction 77D (executing an INSERT) is waiting for transaction 77B to commit.

+ Transaction 77B (executing an INSERT) is waiting for transaction 77A to commit.

+ Transaction 77A is running, currently executing SELECT.

+ Transaction E56 (executing an INSERT) is waiting for transaction E55 to commit.

+ Transaction E55 (executing an INSERT) is waiting for transaction 19C to commit.

+ Transaction 19C is running, currently executing an INSERT.

 

노트

INFORMATION_SCHEMA PROCESSLIST INNODB_TRX 테이블에 표시된 쿼리간에 불일치가 있을 있습니다.

 

다음 표는 작업량이 많은 시스템에 대한 INFORMATION_SCHEMA.PROCESSLIST 내용을 보여줍니다.

ID USER HOST DB COMMAND TIME STATE INFO
384 root localhost test Query 10 update INSERT INTO t2 VALUES …
257 root localhost test Query 3 update INSERT INTO t2 VALUES …
130 root localhost test Query 0 update INSERT INTO t2 VALUES …
61 root localhost test Query 1 update INSERT INTO t2 VALUES …
8 root localhost test Query 1 update INSERT INTO t2 VALUES …
4 root localhost test Query 0 preparing SELECT * FROM PROCESSLIST
2 root localhost test Sleep 566
NULL

 

 

다음 표는 작업량이 많은 시스템에 대한 INFORMATION_SCHEMA.INNODB_TRX 내용을 보여줍니다.

trx id trx state trx started trx requested
lock id
trx wait started trx weight trx mysql
thread id
trx query
77F LOCK WAIT 2008-01-15 13:10:16 77F 2008-01-15 13:10:16 1 876 INSERT INTO t09 (D, B, C) VALUES …
77E LOCK WAIT 2008-01-15 13:10:16 77E 2008-01-15 13:10:16 1 875 INSERT INTO t09 (D, B, C) VALUES …
77D LOCK WAIT 2008-01-15 13:10:16 77D 2008-01-15 13:10:16 1 874 INSERT INTO t09 (D, B, C) VALUES …
77B LOCK WAIT 2008-01-15 13:10:16 77B:733:12:1 2008-01-15 13:10:16 4 873 INSERT INTO t09 (D, B, C) VALUES …
77A RUN­NING 2008-01-15 13:10:16 NULL NULL 4 872 SELECT b, c FROM t09 WHERE …
E56 LOCK WAIT 2008-01-15 13:10:06 E56:743:6:2 2008-01-15 13:10:06 5 384 INSERT INTO t2 VALUES …
E55 LOCK WAIT 2008-01-15 13:10:06 E55:743:38:2 2008-01-15 13:10:13 965 257 INSERT INTO t2 VALUES …
19C RUN­NING 2008-01-15 13:09:10 NULL NULL 2900 130 INSERT INTO t2 VALUES …
E15 RUN­NING 2008-01-15 13:08:59 NULL NULL 5395 61 INSERT INTO t2 VALUES …
51D RUN­NING 2008-01-15 13:08:47 NULL NULL 9807 8 INSERT INTO t2 VALUES …

 

 

다음 표는 작업량이 많은 시스템에 대한 INFORMATION_SCHEMA.INNODB_LOCK_WAITS 내용을 보여줍니다.

requesting trx id requested lock id blocking trx id blocking lock id
77F 77F:806 77E 77E:806
77F 77F:806 77D 77D:806
77F 77F:806 77B 77B:806
77E 77E:806 77D 77D:806
77E 77E:806 77B 77B:806
77D 77D:806 77B 77B:806
77B 77B:733:12:1 77A 77A:733:12:1
E56 E56:743:6:2 E55 E55:743:6:2
E55 E55:743:38:2 19C 19C:743:38:2

 

 

다음 표는 작업량이 많은 시스템의 INFORMATION_SCHEMA.INNODB_LOCKS 내용을 보여줍니다.

lock id lock trx id lock mode lock type lock table lock index lock data
77F:806 77F AUTO_INC TABLE test.t09 NULL NULL
77E:806 77E AUTO_INC TABLE test.t09 NULL NULL
77D:806 77D AUTO_INC TABLE test.t09 NULL NULL
77B:806 77B AUTO_INC TABLE test.t09 NULL NULL
77B:733:12:1 77B X RECORD test.t09 PRIMARY supremum pseudo-record
77A:733:12:1 77A X RECORD test.t09 PRIMARY supremum pseudo-record
E56:743:6:2 E56 S RECORD test.t2 PRIMARY 0, 0
E55:743:6:2 E55 X RECORD test.t2 PRIMARY 0, 0
E55:743:38:2 E55 S RECORD test.t2 PRIMARY 1922, 1922
19C:743:38:2 19C X RECORD test.t2 PRIMARY 1922, 1922

 

 

 

InnoDB Lock Lock-Wait 정보

트랜잭션이 테이블의 행을 업데이트하거나 SELECT FOR UPDATE 잠그면 InnoDB 해당 행에 대한 잠금 목록 또는 큐를 설정합니다. 마찬가지로 InnoDB 테이블 수준 잠금을 위해 테이블의 잠금 목록을 유지 관리합니다. 호환되지 않는 모드에서 두번째 트랜잭션이 행을 업데이트하거나 이전 트랜잭션에 의해 이미 잠겨있는 테이블을 잠그려면 InnoDB 해당 큐에 행에 대한 잠금 요청을 추가합니다. 트랜잭션이 잠금을 획득하려면 해당 또는 테이블의 잠금 큐에 이전에 입력된 호환되지 않는 모든 잠금 요청을 제거해야합니다 ( 잠금을 보유하거나 요청하는 트랜잭션이 커미트 또는 롤백 발생 ).

 

트랜잭션에는 다른 행이나 테이블에 대한 잠금 요청이 얼마든지있을 있습니다. 주어진 시간에, 트랜잭션은 다른 트랜잭션에 의해 보유된 잠금을 요청할 있으며, 경우 다른 트랜잭션에 의해 차단됩니다. 요청 트랜잭션은 차단 잠금을 보유한 트랜잭션이 커밋 또는 롤백 때까지 기다려야합니다. 트랜잭션이 잠금을 대기하지 않으면 RUNNING 상태입니다. 트랜잭션이 잠금 대기중인 경우 LOCK WAIT 상태입니다. INFORMATION_SCHEMA INNODB_TRX 테이블은 트랜잭션 상태 값을 나타냅니다.

 

INNODB_LOCKS 테이블은 LOCK WAIT 트랜잭션에 대해 하나 이상의 행을 보유하며 진행을 방해하는 잠금 요청을 나타냅니다. 테이블에는 주어진 또는 테이블에 대해 보류중인 잠금 큐의 잠금을 설명하는 하나의 행도 포함됩니다. INNODB_LOCK_WAITS 테이블은 트랜잭션이 이미 보유한 잠금이 다른 트랜잭션이 요청한 잠금을 차단하고 있음을 보여줍니다.

 

InnoDB 트랜잭션 잠금 정보의 지속성 일관성

트랜잭션 잠금 테이블 (INNODB_TRX, INNODB_LOCKS INNODB_LOCK_WAITS) 의해 노출 데이터는 빠르게 변화하는 데이터를 엿볼 있습니다. 이것은 응용 프로그램 시작 업데이트가 발생할때만 데이터가 변경되는 사용자 테이블과는 다릅니다. 기본 데이터는 내부 시스템 관리 데이터이며 매우 빠르게 변경 있습니다.

 

성능상의 이유로 트랜잭션 테이블과 잠금 테이블간의 잘못된 연결 조인 가능성을 최소화하기 위해 InnoDB 테이블에서 SELECT 실행될 때마다 필요한 트랜잭션 잠금 정보를 중간 버퍼에 수집합니다. 버퍼는 마지막으로 버퍼를 읽은 이후 0.1 이상 경과 경우에만 새로 고쳐집니다. 개의 테이블을 채우는 필요한 데이터는 원자적으로 일관되게 페치되고 글로벌 내부 버퍼에 저장되어 특정 시점의 "스냅 " 형성합니다. 여러 테이블 액세스가 0.1 내에 발생하는 경우(MySQL 이러한 테이블 간의 조인을 처리 거의 확실하게), 동일한 스냅샷이 쿼리를 충족시키는 사용됩니다.

 

세개의 테이블에 대한 데이터가 동일한 스냅샷에서 가져오기 때문에 단일 테이블에서 이러한 테이블을 결합하면 올바른 결과가 리턴됩니다. 이러한 테이블의 모든 쿼리에서 버퍼가 새로 고쳐지지 않으므로 10 내에 이러한 테이블에 대해 별도의 쿼리를 발행하면 결과는 쿼리마다 동일합니다. 반면, 데이터가 다른 스냅 샷에서 나오기 때문에 10분의 1 이상 간격으로 발행된 동일하거나 다른 테이블에 대한 개의 개별 쿼리는 다른 결과를 있습니다.

 

트랜잭션 잠금 데이터가 수집되는 동안 InnoDB 일시적으로 중단되어야 하므로 이러한 테이블에 대한 너무 빈번한 쿼리는 다른 사용자가 보는 것처럼 성능에 부정적인 영향을 있습니다.

 

테이블에는 민감한 정보 (적어도 INNODB_LOCKS.LOCK_DATA INNODB_TRX.TRX_QUERY) 포함되어 있으므로 보안상의 이유로 PROCESS 권한이있는 사용자만 선택할 있습니다.

 

앞에서 설명한 것처럼 트랜잭션 잠금 테이블 (INNODB_TRX, INNODB_LOCKS INNODB_LOCK_WAITS) 채우는 데이터는 자동으로 가져와져서 특정 시점 스냅샷을 제공하는 중간 버퍼에 저장됩니다. 동일한 스냅 샷에서 쿼리 테이블 모두의 데이터가 일관됩니다. 그러나 기본 데이터가 너무 빠르게 변경되어, 유사하게 빠르게 변경되는 다른 데이터에서 비슷한 모양이 동기화되지 않을 있습니다. 따라서 InnoDB 트랜잭션의 데이터와 잠금 테이블을 PROCESSLIST 테이블의 데이터와 비교할 때는 주의해야합니다. PROCESSLIST 테이블의 데이터는 잠금 트랜잭션에 대한 데이터와 동일한 스냅 샷에서 가져 오지 않습니다. 예를 들어 INNODB_TRX PROCESSLIST 참여하는 단일 SELECT 발행하더라도 해당 테이블의 내용은 일반적으로 일관되지 않습니다. INNODB_TRX PROCESSLIST 없는 행을 참조하거나 INNODB_TRX.TRX_QUERY 표시된 트랜잭션의 현재 실행중인 SQL 쿼리를 PROCESSLIST.INFO 행과 참조 있습니다.

 

■ InnoDB INFORMATION_SCHEMA 시스템 테이블

InnoDB INFORMATION_SCHEMA 시스템 테이블을 사용하여 InnoDB 관리하는 스키마 객체에 대한 메타 데이터를 추출 있습니다. 정보는 InnoDB 내부 시스템 테이블 (InnoDB 데이터 사전이라고도 )에서 제공되며 일반 InnoDB 테이블처럼 직접 쿼리 ​​없습니다. 관련 명령어를 이용하여 InnoDB상태를 가져오는데 가령 SHOW ENGINE INNODB STATUS 문의 출력을 파싱하는 이러한 유형의 정보를 얻었습니다. InnoDB INFORMATION_SCHEMA 테이블 인터페이스를 사용하면 SQL 사용하여 데이터를 쿼리 ​​있습니다.

 

해당 내부 시스템 테이블이 없는 INNODB_SYS_TABLESTATS 제외하고 InnoDB INFORMATION_SCHEMA 시스템 테이블은 메모리에 캐시 메타 데이터가 아닌 내부 InnoDB 시스템 테이블에서 직접 읽은 데이터로 채워집니다.

 

InnoDB INFORMATION_SCHEMA 시스템 테이블에는 아래에 나열된 테이블이 포함됩니다.

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_SYS%';
+--------------------------------------------+
| Tables_in_information_schema (INNODB_SYS%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES                       |
| INNODB_SYS_TABLESTATS                      |
| INNODB_SYS_FOREIGN                         |
| INNODB_SYS_COLUMNS                         |
| INNODB_SYS_INDEXES                         |
| INNODB_SYS_FIELDS                          |
| INNODB_SYS_TABLESPACES                     |
| INNODB_SYS_FOREIGN_COLS                    |
| INNODB_SYS_TABLES                          |
+--------------------------------------------+

 

테이블 이름은 제공되는 데이터 유형을 나타냅니다. 테이블 이름을 통하여 어떤 데이터를 제공하는지 유추해 있습니다. 예를 들면 COLUMNS 끝난다면 컬럼에 관련된 데이터를 제공하는 테이블로 예상할 있습니다. 

+ INNODB_SYS_TABLES InnoDB 데이터 사전의 SYS_TABLES 테이블에있는 정보와 동일한 InnoDB 테이블에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_COLUMNS InnoDB 데이터 사전의 SYS_COLUMNS 테이블에있는 정보와 동일한 InnoDB 테이블 열에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_INDEXES InnoDB 데이터 사전의 SYS_INDEXES 테이블에있는 정보와 동일한 InnoDB 인덱스에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_FIELDS InnoDB 데이터 딕셔너리의 SYS_FIELDS 테이블에있는 정보와 동일한 InnoDB 인덱스의 (필드) 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_TABLESTATS 메모리 데이터 구조에서 파생 InnoDB 테이블에 대한 저수준 상태 정보보기를 제공합니다. 해당 내부 InnoDB 시스템 테이블이 없습니다.

+ INNODB_SYS_DATAFILES InnoDB 데이터 딕셔너리의 SYS_DATAFILES 테이블에있는 정보와 동등한 InnoDB 테이블당 파일 일반 테이블 스페이스에 대한 데이터 파일 경로 정보를 제공합니다.

+ INNODB_SYS_TABLESPACES InnoDB 데이터 사전의 SYS_TABLESPACES 테이블에 있는 정보와 동등한 InnoDB 테이블당 파일 일반 테이블 스페이스에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_FOREIGN InnoDB 데이터 사전의 SYS_FOREIGN 테이블에 있는 정보와 동등한 InnoDB 테이블에 정의된 외래 키에 대한 메타 데이터를 제공합니다.

+ INNODB_SYS_FOREIGN_COLS InnoDB 데이터 사전의 SYS_FOREIGN_COLS 테이블에있는 정보와 동일한 InnoDB 테이블에 정의된 외래키 컬럼에 대한 메타 데이터를 제공합니다.

 

InnoDB INFORMATION_SCHEMA 시스템 테이블은 TABLE_ID, INDEX_ID SPACE 같은 필드를 통해 함께 결합될 있으므로 연구하거나 모니터링하려는 객체에 대해 사용 가능한 모든 데이터를 쉽게 검색 있습니다.

 

▶ Example 14.2 InnoDB INFORMATION_SCHEMA 시스템 테이블

예제는 단일 인덱스 (i1) 함께 간단한 테이블 (t1) 사용하여 InnoDB INFORMATION_SCHEMA 시스템 테이블에서 발견된 메타 데이터 유형을 보여줍니다.

1. Create a test database and table t1:

 

 

2. 테이블 t1 작성한 INNODB_SYS_TABLES 조회하여 test/t1(스키마이름/테이블이름) 메타 데이터를 찾습니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 71
         NAME: test/t1
         FLAG: 1
       N_COLS: 6
        SPACE: 57
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact
ZIP_PAGE_SIZE: 0
...

테이블 t1 TABLE_ID 71입니다. FLAG 필드는 테이블 형식 스토리지 특성에 대한 비트 레벨 정보를 제공합니다. 6개의 열이 있으며 이중 3개는 InnoD(DB_ROW_ID, DB_TRX_ID DB_ROLL_PTR) 의해 생성된 숨겨진 열입니다. 테이블의 SPACE ID 57입니다 (값이 0이면 테이블이 시스템 테이블 스페이스에 있음을 나타냄). FILE_FORMAT Antelope이며 ROW_FORMAT 소형입니다. ZIP_PAGE_SIZE 압축행 형식의 테이블에만 적용됩니다.

 

3. INNODB_SYS_TABLES TABLE_ID 정보를 사용하여 INNODB_SYS_COLUMNS 테이블을 조회하여 테이블 컬럼에 대한 정보를 얻으십시오.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS where TABLE_ID = 71 \G
*************************** 1. row ***************************
TABLE_ID:z71
    NAME: col1
     POS: 0
   MTYPE: 6
  PRTYPE: 1027
     LEN: 4
*************************** 2. row ***************************
TABLE_ID: 71
    NAME: col2
     POS: 1
   MTYPE: 2
  PRTYPE: 524542
     LEN: 10
*************************** 3. row ***************************
TABLE_ID: 71
    NAME: col3
     POS: 2
   MTYPE: 1
  PRTYPE: 524303
     LEN: 10

INNODB_SYS_COLUMNS TABLE_ID NAME 외에도 열의 서수 위치 (POS) (0부터 시작하여 순차적으로 증가), MTYPE 또는 "기본 유형"(6 = INT, 2 = CHAR, 1 = VARCHAR), PRTYPE 또는 "정확한 유형"(MySQL 데이터 유형, 문자 세트 코드 가능성을 나타내는 비트가있는 2 ) 길이 (LEN) 제공합니다.

 

4. INNODB_SYS_TABLES TABLE_ID 정보를 다시 사용하여 INNODB_SYS_INDEXES 테이블 t1 연관된 인덱스에 대한 정보를 조회합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = 71 \G
*************************** 1. row ***************************
       INDEX_ID: 111
           NAME: GEN_CLUST_INDEX
       TABLE_ID: 71
           TYPE: 1
       N_FIELDS: 0
        PAGE_NO: 3
          SPACE: 57
MERGE_THRESHOLD: 50
*************************** 2. row ***************************
       INDEX_ID: 112
           NAME: i1
       TABLE_ID: 71
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 57
MERGE_THRESHOLD: 50

 

INNODB_SYS_INDEXES 개의 인덱스에 대한 데이터를 리턴합니다. 번째 인덱스는 GEN_CLUST_INDEX이며, 테이블에 사용자 정의 클러스터형 인덱스가없는 경우 InnoDB 생성한 클러스터형 인덱스입니다. 번째 인덱스(i1) 사용자 정의 보조 인덱스입니다.

 

INDEX_ID 인스턴스의 모든 데이터베이스에서 고유한 인덱스의 식별자입니다. TABLE_ID 인덱스가 연결된 테이블을 식별합니다. 인덱스 TYPE값은 인덱스 유형을 나타냅니다(1=클러스터형 인덱스, 0​​=보조 인덱스). N_FILEDS값은 색인을 구성하는 필드 수입니다. PAGE_NO 인덱스 B-트리의 루트 페이지 번호이고 SPACE 인덱스가 있는 테이블 스페이스의 ID입니다. 0 아닌 값은 인덱스가 시스템 테이블 스페이스에 상주하지 않음을 나타냅니다.

MERGE_THRESHOLD 인덱스 페이지의 데이터 양에 대한 백분율 임계 값을 정의합니다. 행이 삭제되거나 업데이트 작업으로 행이 단축될 인덱스 페이지의 데이터 양이 (기본값은 50 %)아래로 떨어지면 InnoDB 인덱스 페이지를 인접 인덱스 페이지와 병합하려고 시도합니다.

 

5. INNODB_SYS_INDEXES에서 INDEX_ID 정보를 사용하여 인덱스 i1 필드에 대한 정보는 INNODB_SYS_FIELDS 조회합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS where INDEX_ID = 112 \G
*************************** 1. row ***************************
INDEX_ID: 112
    NAME: col1
     POS: 0

INNODB_SYS_FIELDS 색인화된 필드의 이름과 색인 서수 위치를 제공합니다. 인덱스(i1) 여러 필드에 정의된 경우 INNODB_SYS_FIELDS 인덱스된 필드에 대한 메타 데이터를 제공합니다.

 

6. INNODB_SYS_TABLES SPACE 정보를 사용하여 테이블의 테이블 공간에 대한 정보는 INNODB_SYS_TABLESPACES 테이블을 조회합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE SPACE=57 \G
*************************** 1. row ***************************
        SPACE: 57
         NAME: test/t1
         FLAG: 0
  FILE_FORMAT: Antelope
   ROW_FORMAT: Compact or Redundant
    PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0

INNODB_SYS_TABLESPACES 테이블 스페이스의 SPACE ID 연관된 테이블의 NAME 외에도 테이블 스페이스 형식 스토리지 특성에 대한 비트 레벨 정보 테이블 스페이스 FLAG 데이터를 제공합니다. FILE_FORMAT, ROW_FORMAT, PAGE_SIZE 테이블 스페이스 기타 여러 테이블 스페이스 메타 데이터 항목도 제공됩니다.

 

7. INNODB_SYS_TABLES SPACE 정보를 다시 사용하여 INNODB_SYS_DATAFILES 테이블 스페이스 데이터 파일의 위치를 ​​조회합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE SPACE = 57 \G
*************************** 1. row ***************************
SPACE: 57
 PATH: ./test/t1.ibd

데이터 파일은 테스트 디렉토리의 MySQL데이터 디렉토리에 있습니다. CREATE TABLE문의 DATA DIRECTORY절을 사용하여 MySQL데이터 디렉토리 외부 위치에 테이블당 파일 공간이 작성되면, 테이블 공간 PATH 완전한 디렉토리 경로가 됩니다.

 

8. 마지막 단계로, 테이블 t1 (TABLE_ID = 71) 행을 삽입하고 INNODB_SYS_TABLESTATS 테이블의 데이터를 확인합니다. 테이블의 데이터는 MySQL 옵티마이 저가 InnoDB 테이블을 쿼리 사용할 인덱스를 계산하는데 사용됩니다. 정보는 메모리내 데이터 구조에서 파생됩니다. 해당 내부 InnoDB 시스템 테이블이 없습니다.

mysql> INSERT INTO t1 VALUES(5, 'abc', 'def');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS where TABLE_ID = 71 \G
*************************** 1. row ***************************
         TABLE_ID: 71
             NAME: test/t1
STATS_INITIALIZED: Initialized
         NUM_ROWS: 1
 CLUST_INDEX_SIZE: 1
 OTHER_INDEX_SIZE: 0
 MODIFIED_COUNTER: 1
          AUTOINC: 0
        REF_COUNT: 1

 

STATS_INITIALIZED 필드는 테이블에 대한 통계가 수집되었는지 여부를 나타냅니다. NUM_ROWS 테이블의 현재 예상행 수입니다. CLUST_INDEX_SIZE OTHER_INDEX_SIZE 필드는 각각 테이블의 클러스터된 인덱스 보조 인덱스를 저장하는 디스크의 페이지 수를 보고합니다. MODIFIED_COUNTER 값은 외래 키에서 DML 작업 계단식 작업으로 수정된 수를 나타냅니다. AUTOINC값은 자동 증가 기반 작업에 대해 다음에 발행되는 숫자입니다. 테이블 t1 자동 증분 열이 정의되어 있지 않으므로 값은 0입니다. REF_COUNT 값은 카운터입니다. 카운터가 0 도달하면 테이블 캐시에서 테이블 메타 데이터를 제거 있음을 나타냅니다.

 

▶ Example 14.3 외래키 INFORMATION_SCHEMA 시스템 테이블

INNODB_SYS_FOREIGN INNODB_SYS_FOREIGN_COLS 테이블은 외래키 관계에 대한 데이터를 제공합니다. 예제는 외부키 관계가있는 상위 테이블 하위 테이블을 사용하여 INNODB_SYS_FOREIGN INNODB_SYS_FOREIGN_COLS 테이블에있는 데이터를 보여줍니다.

1. 부모 테이블과 자식 테이블을 사용하여 테스트 데이터베이스를 만듭니다.

mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE parent (id INT NOT NULL,
       PRIMARY KEY (id)) ENGINE=INNODB;
mysql> CREATE TABLE child (id INT, parent_id INT,
       INDEX par_ind (parent_id),
       CONSTRAINT fk1
       FOREIGN KEY (parent_id) REFERENCES parent(id)
       ON DELETE CASCADE) ENGINE=INNODB;

 

 

2. 상위 하위 테이블이 작성된 INNODB_SYS_FOREIGN 조회하고 test/child test/parent 외래키 관계에 대한 외래키 데이터를 찾습니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G
*************************** 1. row ***************************
      ID: test/fk1
FOR_NAME: test/child
REF_NAME: test/parent
  N_COLS: 1
    TYPE: 1

메타 데이터에는 외래키 ID(fk1) 포함되며 키는 자식 테이블에 정의된 CONSTRAINT 이름으로 지정됩니다. FOR_NAME 외래키가 정의된 자식 테이블의 이름입니다. REF_NAME 상위 테이블 ("참조 "테이블) 이름입니다. N_COLS 외래키 인덱스의 컬럼수 입니다. TYPE 외래 컬럼에 대한 추가 정보를 제공하는 비트 플래그를 나타내는 숫자 값입니다. 경우 TYPE 값은 1이며 외래 키에 ON DELETE CASCADE 옵션이 지정되었음을 나타냅니다. TYPE 값에 대한 자세한 정보는 INNODB_SYS_FOREIGN 테이블 정의를 참조하십시오.

 

3. 외래 ID 사용하여 INNODB_SYS_FOREIGN_COLS 쿼리하여 외래키 컬럼에 대한 데이터를 봅니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS WHERE ID = 'test/fk1' \G
*************************** 1. row ***************************
          ID: test/fk1
FOR_COL_NAME: parent_id
REF_COL_NAME: id
         POS: 0

FOR_COL_NAME 자식 테이블의 외래키 컬럼 이름이고 REF_COL_NAME 부모 테이블의 참조 컬럼 이름입니다. POS 값은 외래 인덱스 내에서 필드의 서수 위치이며 0부터 시작합니다.

 

▶ Example 14.4 InnoDB INFORMATION_SCHEMA 시스템 테이블 조인

예제는 세개의 InnoDB INFORMATION_SCHEMA 시스템 테이블(INNODB_SYS_TABLES, INNODB_SYS_TABLESPACES INNODB_SYS_TABLESTATS) 결합하여 직원 샘플 데이터베이스의 테이블에 대한 파일 형식, 형식, 페이지 크기 인덱스 크기 정보를 수집하는 방법을 보여줍니다.

 

The following table name aliases are used to shorten the query string:

다음 테이블 이름 별명은 쿼리 문자열을 단축하는 사용됩니다.

+ INFORMATION_SCHEMA.INNODB_SYS_TABLES: a

+ INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES: b

+ INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS: c

 

IF() 제어 흐름 함수는 압축된 테이블을 설명하는 사용됩니다. 테이블이 압축되면 인덱스 크기는 PAGE_SIZE 아닌 ZIP_PAGE_SIZE 사용하여 계산됩니다. 바이트 단위로 보고되는 CLUST_INDEX_SIZE OTHER_INDEX_SIZE 1024 * 1024 나누어 인덱스 크기(MB) 제공합니다. MB 값은 ROUND() 함수를 사용하여 10진수 공백으로 반올림됩니다.

mysql> SELECT a.NAME, a.FILE_FORMAT, a.ROW_FORMAT,
        @page_size :=
         IF(a.ROW_FORMAT='Compressed',
          b.ZIP_PAGE_SIZE, b.PAGE_SIZE)
          AS page_size,
         ROUND((@page_size * c.CLUST_INDEX_SIZE)
          /(1024*1024)) AS pk_mb,
         ROUND((@page_size * c.OTHER_INDEX_SIZE)
          /(1024*1024)) AS secidx_mb
       FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES a
       INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES b on a.NAME = b.NAME
       INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS c on b.NAME = c.NAME
       WHERE a.NAME LIKE 'employees/%'
       ORDER BY a.NAME DESC;
+------------------------+-------------+------------+-----------+-------+-----------+
| NAME                   | FILE_FORMAT | ROW_FORMAT | page_size | pk_mb | secidx_mb |
+------------------------+-------------+------------+-----------+-------+-----------+
| employees/titles       | Antelope    | Compact    |     16384 |    20 |        11 |
| employees/salaries     | Antelope    | Compact    |     16384 |    91 |        33 |
| employees/employees    | Antelope    | Compact    |     16384 |    15 |         0 |
| employees/dept_manager | Antelope    | Compact    |     16384 |     0 |         0 |
| employees/dept_emp     | Antelope    | Compact    |     16384 |    12 |        10 |
| employees/departments  | Antelope    | Compact    |     16384 |     0 |         0 |
+------------------------+-------------+------------+-----------+-------+-----------+

 

■ InnoDB INFORMATION_SCHEMA 버퍼풀 테이블

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

테이블 개요

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

+ INNODB_BUFFER_PAGE_LRU : InnoDB 버퍼 풀의 페이지에 대한 정보, 특히 버퍼 풀이 가득 찼을 버퍼 풀에서 제거 페이지를 결정하는 LRU 목록에서 페이지 순서에 대한 정보를 보유합니다. INNODB_BUFFER_PAGE_LRU 테이블에 BLOCK_ID 컬럼 대신 LRU_POSITION 컬럼이 있다는 점을 제외하고 INNODB_BUFFER_PAGE_LRU 테이블에는 INNODB_BUFFER_PAGE 테이블과 동일한 컬럼이 있습니다.

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

+ INNODB_BUFFER_PAGE 또는 INNODB_BUFFER_PAGE_LRU 테이블을 쿼리하면 성능에 영향을 있습니다. 성능 영향을 인식하고 허용 가능한 것으로 판별되지 않은 경우 프로덕션 시스템에서 테이블을 조회시 굉장히 조심해야 합니다. 프로덕션 시스템의 성능에 영향을주지 않으려면 테스트 인스턴스에서 버퍼 통계를 조사하고 쿼리하려는 문제를 재현하여 어떤문제가 발생되는지 판별하고 수행해야 합니다.

 

• Example 14.6 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         |
+-------------------+

 

• Example 14.7 INNODB_BUFFER_PAGE 테이블에서 사용자 데이터 조회

쿼리는 TABLE_NAME 값이 NULL 아니고 '%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` |
+-------------------------+

 

• Example 14.8 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 |
+------------+-------+-----------------+

 

• Example 14.9 INNODB_BUFFER_PAGE_LRU 테이블에서 LRU_POSITION 데이터 쿼리

INNODB_BUFFER_PAGE_LRU 테이블은 InnoDB 버퍼 풀의 페이지에 대한 정보, 특히 페이지가 가득 찼을 버퍼 풀에서 제거할 페이지를 결정하는 순서에 따라 정보를 보유합니다. 페이지의 정의는 INNODB_BUFFER_PAGE 동일하지만 테이블에 BLOCK_ID 대신 LRU_POSITION 열이 있습니다.

 

쿼리는 LRU 목록의 특정 위치에서 employee.employees 테이블의 페이지가 차지하는 위치 수를 계산합니다.

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

 

• Example 14.10 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    |
+---------------------------------------+-------------+

 

 

■ InnoDB INFORMATION_SCHEMA 메트릭 테이블

INNODB_METRICS 테이블은 InnoDB 성능 리소스 관련 카운터에 대한 정보를 제공합니다.

INNODB_METRICS 테이블 열은 다음과 같습니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 46273
      MAX_COUNT: 46273
      MIN_COUNT: NULL
      AVG_COUNT: 492.2659574468085
    COUNT_RESET: 46273
MAX_COUNT_RESET: 46273
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2014-11-28 16:07:53
  TIME_DISABLED: NULL
   TIME_ELAPSED: 94
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: status_counter
        COMMENT: Number of rows inserted

 

카운터 활성화, 비활성화 재설정

다음 변수를 사용하여 카운터를 활성화, 비활성화 재설정 있습니다.

+ innodb_monitor_enable: 카운터 사용.

SET GLOBAL innodb_monitor_enable = [counter-name|module_name|pattern|all];

+ innodb_monitor_disable: 카운터 비활성화.

SET GLOBAL innodb_monitor_disable = [counter-name|module_name|pattern|all];

+ innodb_monitor_reset: 카운터값 0으로 초기화

SET GLOBAL innodb_monitor_reset = [counter-name|module_name|pattern|all];

+ innodb_monitor_reset_all: 모든 카운터 값을 재설정합니다. innodb_monitor_reset_all 사용하기 전에 카운터를 비활성화해야합니다.

SET GLOBAL innodb_monitor_reset_all = [counter-name|module_name|pattern|all];

카운터 카운터 모듈은 시작시 MySQL 서버 구성 파일을 사용하여 활성화할 있습니다. 예를 들어, metadata_table_handles_opened metadata_table_handles_closed 카운터 모듈을 활성화하려면 MySQL 서버 구성 파일의 [mysqld] 섹션에 다음 줄을 입력합니다.

[mysqld]
innodb_monitor_enable = module_recovery,metadata_table_handles_opened,metadata_table_handles_closed

 

구성 파일에서 여러 카운터 또는 모듈을 활성화 , 위에 표시된대로 innodb_monitor_enable 변수 뒤에 쉼표로 구분된 카운터 모듈 이름을 지정하세요. 구성 파일에는 innodb_monitor_enable 변수만 사용할 있습니다. innodb_monitor_disable innodb_monitor_reset 변수는 명령 행에서만 지원됩니다.

 

노트

카운터는 어느 정도의 런타임 오버 헤드를 추가하므로 프로덕션 서버에서 보수적으로 카운터를 사용하여 특정 문제를 진단하거나 특정 기능을 모니터링합니다. 카운터를 보다 광범위하게 사용하려면 테스트 또는 개발 서버가 권장됩니다.

 

카운터

사용 가능한 카운터 목록은 변경 있습니다. MySQL 서버 버전에서 사용 가능한 카운터에 대해서는 INFORMATION_SCHEMA.INNODB_METRICS 테이블을 쿼리합니다.

 

기본적으로 활성화된 카운터는 SHOW ENGINE INNODB STATUS 출력에 표시된 카운터에 해당합니다. SHOW ENGINE INNODB STATUS 출력에 표시된 카운터는 항상 시스템 레벨에서 사용 가능하지만 INNODB_METRICS 테이블에 대해 사용 불가능하게 있습니다. 카운터 상태가 지속되지 않습니다. 달리 구성하지 않으면 서버를 다시 시작할 카운터가 기본 활성화 또는 비활성화 상태로 돌아갑니다.

 

카운터 추가 또는 제거에 영향을받는 프로그램을 실행하는 경우 릴리스 정보를 검토하고 INNODB_METRICS 테이블을 쿼리하여 업그레이드 프로세스의 일부로 해당 변경 사항을 식별하는 것이 좋습니다.

mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS ORDER BY NAME;
+------------------------------------------+---------------------+----------+
| name                                     | subsystem           | status   |
+------------------------------------------+---------------------+----------+
| adaptive_hash_pages_added                | adaptive_hash_index | disabled |
| adaptive_hash_pages_removed              | adaptive_hash_index | disabled |
| adaptive_hash_rows_added                 | adaptive_hash_index | disabled |
| adaptive_hash_rows_deleted_no_hash_entry | adaptive_hash_index | disabled |
| adaptive_hash_rows_removed               | adaptive_hash_index | disabled |
| adaptive_hash_rows_updated               | adaptive_hash_index | disabled |
| adaptive_hash_searches                   | adaptive_hash_index | enabled  |
| adaptive_hash_searches_btree             | adaptive_hash_index | enabled  |
| buffer_data_reads                        | buffer              | enabled  |
| buffer_data_written                      | buffer              | enabled  |
| buffer_flush_adaptive                    | buffer              | disabled |
| buffer_flush_adaptive_avg_pass           | buffer              | disabled |
| buffer_flush_adaptive_avg_time_est       | buffer              | disabled |
| buffer_flush_adaptive_avg_time_slot      | buffer              | disabled |
| buffer_flush_adaptive_avg_time_thread    | buffer              | disabled |
| buffer_flush_adaptive_pages              | buffer              | disabled |
| buffer_flush_adaptive_total_pages        | buffer              | disabled |
| buffer_flush_avg_page_rate               | buffer              | disabled |
| buffer_flush_avg_pass                    | buffer              | disabled |
| buffer_flush_avg_time                    | buffer              | disabled |
| buffer_flush_background                  | buffer              | disabled |
| buffer_flush_background_pages            | buffer              | disabled |
| buffer_flush_background_total_pages      | buffer              | disabled |
| buffer_flush_batches                     | buffer              | disabled |
| buffer_flush_batch_num_scan              | buffer              | disabled |
| buffer_flush_batch_pages                 | buffer              | disabled |
| buffer_flush_batch_scanned               | buffer              | disabled |
| buffer_flush_batch_scanned_per_call      | buffer              | disabled |
| buffer_flush_batch_total_pages           | buffer              | disabled |
| buffer_flush_lsn_avg_rate                | buffer              | disabled |
| buffer_flush_neighbor                    | buffer              | disabled |
| buffer_flush_neighbor_pages              | buffer              | disabled |
| buffer_flush_neighbor_total_pages        | buffer              | disabled |
| buffer_flush_n_to_flush_by_age           | buffer              | disabled |
| buffer_flush_n_to_flush_requested        | buffer              | disabled |
| buffer_flush_pct_for_dirty               | buffer              | disabled |
| buffer_flush_pct_for_lsn                 | buffer              | disabled |
| buffer_flush_sync                        | buffer              | disabled |
| buffer_flush_sync_pages                  | buffer              | disabled |
| buffer_flush_sync_total_pages            | buffer              | disabled |
| buffer_flush_sync_waits                  | buffer              | disabled |
| buffer_LRU_batches_evict                 | buffer              | disabled |
| buffer_LRU_batches_flush                 | buffer              | disabled |
| buffer_LRU_batch_evict_pages             | buffer              | disabled |
| buffer_LRU_batch_evict_total_pages       | buffer              | disabled |
| buffer_LRU_batch_flush_avg_pass          | buffer              | disabled |
| buffer_LRU_batch_flush_avg_time_est      | buffer              | disabled |
| buffer_LRU_batch_flush_avg_time_slot     | buffer              | disabled |
| buffer_LRU_batch_flush_avg_time_thread   | buffer              | disabled |
| buffer_LRU_batch_flush_pages             | buffer              | disabled |
| buffer_LRU_batch_flush_total_pages       | buffer              | disabled |
| buffer_LRU_batch_num_scan                | buffer              | disabled |
| buffer_LRU_batch_scanned                 | buffer              | disabled |
| buffer_LRU_batch_scanned_per_call        | buffer              | disabled |
| buffer_LRU_get_free_loops                | buffer              | disabled |
| buffer_LRU_get_free_search               | Buffer              | disabled |
| buffer_LRU_get_free_waits                | buffer              | disabled |
| buffer_LRU_search_num_scan               | buffer              | disabled |
| buffer_LRU_search_scanned                | buffer              | disabled |
| buffer_LRU_search_scanned_per_call       | buffer              | disabled |
| buffer_LRU_single_flush_failure_count    | Buffer              | disabled |
| buffer_LRU_single_flush_num_scan         | buffer              | disabled |
| buffer_LRU_single_flush_scanned          | buffer              | disabled |
| buffer_LRU_single_flush_scanned_per_call | buffer              | disabled |
| buffer_LRU_unzip_search_num_scan         | buffer              | disabled |
| buffer_LRU_unzip_search_scanned          | buffer              | disabled |
| buffer_LRU_unzip_search_scanned_per_call | buffer              | disabled |
| buffer_pages_created                     | buffer              | enabled  |
| buffer_pages_read                        | buffer              | enabled  |
| buffer_pages_written                     | buffer              | enabled  |
| buffer_page_read_blob                    | buffer_page_io      | disabled |
| buffer_page_read_fsp_hdr                 | buffer_page_io      | disabled |
| buffer_page_read_ibuf_bitmap             | buffer_page_io      | disabled |
| buffer_page_read_ibuf_free_list          | buffer_page_io      | disabled |
| buffer_page_read_index_ibuf_leaf         | buffer_page_io      | disabled |
| buffer_page_read_index_ibuf_non_leaf     | buffer_page_io      | disabled |
| buffer_page_read_index_inode             | buffer_page_io      | disabled |
| buffer_page_read_index_leaf              | buffer_page_io      | disabled |
| buffer_page_read_index_non_leaf          | buffer_page_io      | disabled |
| buffer_page_read_other                   | buffer_page_io      | disabled |
| buffer_page_read_system_page             | buffer_page_io      | disabled |
| buffer_page_read_trx_system              | buffer_page_io      | disabled |
| buffer_page_read_undo_log                | buffer_page_io      | disabled |
| buffer_page_read_xdes                    | buffer_page_io      | disabled |
| buffer_page_read_zblob                   | buffer_page_io      | disabled |
| buffer_page_read_zblob2                  | buffer_page_io      | disabled |
| buffer_page_written_blob                 | buffer_page_io      | disabled |
| buffer_page_written_fsp_hdr              | buffer_page_io      | disabled |
| buffer_page_written_ibuf_bitmap          | buffer_page_io      | disabled |
| buffer_page_written_ibuf_free_list       | buffer_page_io      | disabled |
| buffer_page_written_index_ibuf_leaf      | buffer_page_io      | disabled |
| buffer_page_written_index_ibuf_non_leaf  | buffer_page_io      | disabled |
| buffer_page_written_index_inode          | buffer_page_io      | disabled |
| buffer_page_written_index_leaf           | buffer_page_io      | disabled |
| buffer_page_written_index_non_leaf       | buffer_page_io      | disabled |
| buffer_page_written_other                | buffer_page_io      | disabled |
| buffer_page_written_system_page          | buffer_page_io      | disabled |
| buffer_page_written_trx_system           | buffer_page_io      | disabled |
| buffer_page_written_undo_log             | buffer_page_io      | disabled |
| buffer_page_written_xdes                 | buffer_page_io      | disabled |
| buffer_page_written_zblob                | buffer_page_io      | disabled |
| buffer_page_written_zblob2               | buffer_page_io      | disabled |
| buffer_pool_bytes_data                   | buffer              | enabled  |
| buffer_pool_bytes_dirty                  | buffer              | enabled  |
| buffer_pool_pages_data                   | buffer              | enabled  |
| buffer_pool_pages_dirty                  | buffer              | enabled  |
| buffer_pool_pages_free                   | buffer              | enabled  |
| buffer_pool_pages_misc                   | buffer              | enabled  |
| buffer_pool_pages_total                  | buffer              | enabled  |
| buffer_pool_reads                        | buffer              | enabled  |
| buffer_pool_read_ahead                   | buffer              | enabled  |
| buffer_pool_read_ahead_evicted           | buffer              | enabled  |
| buffer_pool_read_requests                | buffer              | enabled  |
| buffer_pool_size                         | server              | enabled  |
| buffer_pool_wait_free                    | buffer              | enabled  |
| buffer_pool_write_requests               | buffer              | enabled  |
| compression_pad_decrements               | compression         | disabled |
| compression_pad_increments               | compression         | disabled |
| compress_pages_compressed                | compression         | disabled |
| compress_pages_decompressed              | compression         | disabled |
| ddl_background_drop_indexes              | ddl                 | disabled 
| ddl_background_drop_tables               | ddl                 | disabled |
| ddl_log_file_alter_table                 | ddl                 | disabled |
| ddl_online_create_index                  | ddl                 | disabled 
| ddl_pending_alter_table                  | ddl                 | disabled |
| ddl_sort_file_alter_table                | ddl                 | disabled |
| dml_deletes                              | dml                 | enabled  |
| dml_inserts                              | dml                 | enabled  |
| dml_reads                                | dml                 | disabled |
| dml_updates                              | dml                 | enabled  
| file_num_open_files                      | file_system         | enabled  |
| ibuf_merges                              | change_buffer       | enabled  |
| ibuf_merges_delete                       | change_buffer       | enabled  |
| ibuf_merges_delete_mark                  | change_buffer       | enabled  |
| ibuf_merges_discard_delete               | change_buffer       | enabled  |
| ibuf_merges_discard_delete_mark          | change_buffer       | enabled  |
| ibuf_merges_discard_insert               | change_buffer       | enabled  |
| ibuf_merges_insert                       | change_buffer       | enabled  |
| ibuf_size                                | change_buffer       | enabled  |
| icp_attempts                             | icp                 | disabled |
| icp_match                                | icp                 | disabled |
| icp_no_match                             | icp                 | disabled |
| icp_out_of_range                         | icp                 | disabled 
| index_page_discards                      | index               | disabled |
| index_page_merge_attempts                | index               | disabled |
| index_page_merge_successful              | index               | disabled |
| index_page_reorg_attempts                | index               | disabled |
| index_page_reorg_successful              | index               | disabled |
| index_page_splits                        | index               | disabled |
| innodb_activity_count                    | server              | enabled  |
| innodb_background_drop_table_usec        | server              | disabled |
| innodb_checkpoint_usec                   | server              | disabled |
| innodb_dblwr_pages_written               | server              | enabled  |
| innodb_dblwr_writes                      | server              | enabled  |
| innodb_dict_lru_count                    | server              | disabled |
| innodb_dict_lru_usec                     | server              | disabled |
| innodb_ibuf_merge_usec                   | server              | disabled |
| innodb_log_flush_usec                    | server              | disabled |
| innodb_master_active_loops               | server              | disabled |
| innodb_master_idle_loops                 | server              | disabled |
| innodb_master_purge_usec                 | server              | disabled |
| innodb_master_thread_sleeps              | server              | disabled |
| innodb_mem_validate_usec                 | server              | disabled |
| innodb_page_size                         | server              | enabled  |
| innodb_rwlock_sx_os_waits                | server              | enabled  |
| innodb_rwlock_sx_spin_rounds             | server              | enabled  |
| innodb_rwlock_sx_spin_waits              | server              | enabled  |
| innodb_rwlock_s_os_waits                 | server              | enabled  |
| innodb_rwlock_s_spin_rounds              | server              | enabled  |
| innodb_rwlock_s_spin_waits               | server              | enabled  |
| innodb_rwlock_x_os_waits                 | server              | enabled  |
| innodb_rwlock_x_spin_rounds              | server              | enabled  |
| innodb_rwlock_x_spin_waits               | server              | enabled  |
| lock_deadlocks                           | lock                | enabled  |
| lock_rec_locks                           | lock                | disabled |
| lock_rec_lock_created                    | lock                | disabled |
| lock_rec_lock_removed                    | lock                | disabled |
| lock_rec_lock_requests                   | lock                | disabled |
| lock_rec_lock_waits                      | lock                | disabled |
| lock_row_lock_current_waits              | lock                | enabled  |
| lock_row_lock_time                       | lock                | enabled  |
| lock_row_lock_time_avg                   | lock                | enabled  |
| lock_row_lock_time_max                   | lock                | enabled  |
| lock_row_lock_waits                      | lock                | enabled  |
| lock_table_locks                         | lock                | disabled |
| lock_table_lock_created                  | lock                | disabled |
| lock_table_lock_removed                  | lock                | disabled |
| lock_table_lock_waits                    | lock                | disabled |
| lock_timeouts                            | lock                | enabled  |
| log_checkpoints                          | recovery            | disabled |
| log_lsn_buf_pool_oldest                  | recovery            | disabled |
| log_lsn_checkpoint_age                   | recovery            | disabled |
| log_lsn_current                          | recovery            | disabled |
| log_lsn_last_checkpoint                  | recovery            | disabled |
| log_lsn_last_flush                       | recovery            | disabled |
| log_max_modified_age_async               | recovery            | disabled |
| log_max_modified_age_sync                | recovery            | disabled |
| log_num_log_io                           | recovery            | disabled |
| log_padded                               | recovery            | enabled  |
| log_pending_checkpoint_writes            | recovery            | disabled |
| log_pending_log_flushes                  | recovery            | disabled |
| log_waits                                | recovery            | enabled  |
| log_writes                               | recovery            | enabled  |
| log_write_requests                       | recovery            | enabled  |
| metadata_table_handles_closed            | metadata            | disabled |
| metadata_table_handles_opened            | metadata            | disabled |
| metadata_table_reference_count           | metadata            | disabled |
| os_data_fsyncs                           | os                  | enabled  |
| os_data_reads                            | os                  | enabled  |
| os_data_writes                           | os                  | enabled  |
| os_log_bytes_written                     | os                  | enabled  |
| os_log_fsyncs                            | os                  | enabled  |
| os_log_pending_fsyncs                    | os                  | enabled  |
| os_log_pending_writes                    | os                  | enabled  |
| os_pending_reads                         | os                  | disabled |
| os_pending_writes                        | os                  | disabled |
| purge_del_mark_records                   | purge               | disabled |
| purge_dml_delay_usec                     | purge               | disabled |
| purge_invoked                            | purge               | disabled |
| purge_resume_count                       | purge               | disabled |
| purge_stop_count                         | purge               | disabled |
| purge_undo_log_pages                     | purge               | disabled |
| purge_upd_exist_or_extern_records        | purge               | disabled |
| trx_active_transactions                  | transaction         | disabled |
| trx_commits_insert_update                | transaction         | disabled |
| trx_nl_ro_commits                        | transaction         | disabled |
| trx_rollbacks                            | transaction         | disabled |
| trx_rollbacks_savepoint                  | transaction         | disabled |
| trx_rollback_active                      | transaction         | disabled |
| trx_ro_commits                           | transaction         | disabled |
| trx_rseg_current_size                    | transaction         | disabled |
| trx_rseg_history_len                     | transaction         | enabled  |
| trx_rw_commits                           | transaction         | disabled |
| trx_undo_slots_cached                    | transaction         | disabled |
| trx_undo_slots_used                      | transaction         | disabled |
+------------------------------------------+---------------------+----------+
235 rows in set (0.01 sec)

 

 

 

카운터 모듈

카운터는 특정 모듈과 연결됩니다. 모듈 이름을 사용하여 특정 하위 시스템에 대한 모든 카운터를 활성화, 비활성화 또는 재설정 있습니다. 예를 들어, module_dml 사용하여 dml하위 시스템과 관련된 모든 카운터를 활성화합니다.

mysql> SET GLOBAL innodb_monitor_enable = module_dml;
mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS
       WHERE subsystem ='dml';
+-------------+-----------+---------+
| name        | subsystem | status  |
+-------------+-----------+---------+
| dml_reads   | dml       | enabled |
| dml_inserts | dml       | enabled |
| dml_deletes | dml       | enabled |
| dml_updates | dml       | enabled |
+-------------+-----------+---------+

 

모듈 이름은 innodb_monitor_enable 관련 변수와 함께 사용할 있습니다.

모듈 이름과 해당 SUBSYSTEM 이름이 아래에 나열되어 있습니다.

+ module_adaptive_hash (subsystem = adaptive_hash_index)

+ module_buffer (subsystem = buffer)

+ module_buffer_page (subsystem = buffer_page_io)

+ module_compress (subsystem = compression)

+ module_ddl (subsystem = ddl)

+ module_dml (subsystem = dml)

+ module_file (subsystem = file_system)

+ module_ibuf_system (subsystem = change_buffer)

+ module_icp (subsystem = icp)

+ module_index (subsystem = index)

+ module_innodb (subsystem = innodb)

+ module_lock (subsystem = lock)

+ module_log (subsystem = recovery)

+ module_metadata (subsystem = metadata)

+ module_os (subsystem = os)

+ module_purge (subsystem = purge)

+ module_trx (subsystem = transaction)

 

• Example 14.11 INNODB_METRICS 테이블 카운터 작업

예는 카운터를 활성화, 비활성화 재설정하고 INNODB_METRICS 테이블에서 카운터 데이터를 쿼리하는 방법을 보여줍니다.

1. 간단한 InnoDB 테이블을 만듭니다 :

mysql> USE test;
Database changed

mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

 

2. dml_inserts 카운터를 활성화합니다.

dml_inserts 카운터에 대한 설명은 INNODB_METRICS 테이블의 COMMENT 컬럼에서 찾을 있습니다.

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts";
+-------------+-------------------------+
| NAME        | COMMENT                 |
+-------------+-------------------------+
| dml_inserts | Number of rows inserted |
+-------------+-------------------------+

 

3. dml_inserts 카운터 데이터에 대해 INNODB_METRICS 테이블을 조회합니다. DML 작업이 수행되지 않았으므로 카운터 값은 0 또는 NULL입니다. TIME_ENABLED TIME_ELAPSED 값은 카운터가 마지막으로 활성화된 시간과 이후 초가 경과했는지를 나타냅니다.

mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts" \G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 0
      MAX_COUNT: 0
      MIN_COUNT: NULL
      AVG_COUNT: 0
    COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2014-12-04 14:18:28
  TIME_DISABLED: NULL
   TIME_ELAPSED: 28
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: status_counter
        COMMENT: Number of rows inserted

 

4. 3 개의 데이터 행을 테이블에 삽입합니다.

mysql> INSERT INTO t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 values(3);
Query OK, 1 row affected (0.00 sec)

 

5. dml_inserts 카운터 데이터에 대해 INNODB_METRICS 테이블을 다시 조회합니다. COUNT, MAX_COUNT, AVG_COUNT COUNT_RESET 포함하여 여러 카운터 값이 증가했습니다. 값에 대한 설명은 INNODB_METRICS 테이블 정의를 참조합니다.

mysql>  SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 3
      MAX_COUNT: 3
      MIN_COUNT: NULL
      AVG_COUNT: 0.046153846153846156
    COUNT_RESET: 3
MAX_COUNT_RESET: 3
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2014-12-04 14:18:28
  TIME_DISABLED: NULL
   TIME_ELAPSED: 65
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: status_counter
        COMMENT: Number of rows inserted

 

6. dml_inserts 카운터를 재설정하고 dml_inserts 카운터 데이터에 대해 INNODB_METRICS 테이블을 다시 쿼리합니다 COUNT_RESET MAX_RESET 같이 이전에 보고된 %_RESET 값은 0으로 다시 설정됩니다. 카운터가 활성화 시간부터 누적 적으로 데이터를 수집하는 COUNT, MAX_COUNT AVG_COUNT 같은 값은 재설정의 영향을받지 않습니다.

mysql> SET GLOBAL innodb_monitor_reset = dml_inserts;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 3
      MAX_COUNT: 3
      MIN_COUNT: NULL
      AVG_COUNT: 0.03529411764705882
    COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: 0
   TIME_ENABLED: 2014-12-04 14:18:28
  TIME_DISABLED: NULL
   TIME_ELAPSED: 85
     TIME_RESET: 2014-12-04 14:19:44
         STATUS: enabled
           TYPE: status_counter
        COMMENT: Number of rows inserted

 

7. 모든 카운터 값을 재설정하려면 먼저 카운터를 비활성화해야합니다. 카운터를 비활성화하면 STATUS 값이 비활성화로 설정됩니다.

mysql> SET GLOBAL innodb_monitor_disable = dml_inserts;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 3
      MAX_COUNT: 3
      MIN_COUNT: NULL
      AVG_COUNT: 0.030612244897959183
    COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: 0
   TIME_ENABLED: 2014-12-04 14:18:28
  TIME_DISABLED: 2014-12-04 14:20:06
   TIME_ELAPSED: 98
     TIME_RESET: NULL
         STATUS: disabled
           TYPE: status_counter
        COMMENT: Number of rows inserted

 

노트

카운터 모듈 이름에 와일드 카드 일치가 지원됩니다. 예를 들어, 전체 dml_inserts 카운터 이름을 지정하는 대신 dml_i% 지정할 있습니다. 와일드 카드 일치를 사용하여 여러 카운터 또는 모듈을 번에 활성화, 비활성화 또는 재설정 수도 있습니다. 예를 들어, dml_ 시작하는 모든 카운터를 활성화, 비활성화 또는 재설정하려면 dml_% 지정합니다.

 

8. 카운터가 비활성화 innodb_monitor_reset_all 옵션을 사용하여 모든 카운터 값을 재설정 있습니다. 모든 값은 0 또는 NULL 설정됩니다.

mysql> SET GLOBAL innodb_monitor_reset_all = dml_inserts;
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME="dml_inserts"\G
*************************** 1. row ***************************
           NAME: dml_inserts
      SUBSYSTEM: dml
          COUNT: 0
      MAX_COUNT: NULL
      MIN_COUNT: NULL
      AVG_COUNT: NULL
    COUNT_RESET: 0
MAX_COUNT_RESET: NULL
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
   TIME_ENABLED: NULL
  TIME_DISABLED: NULL
   TIME_ELAPSED: NULL
     TIME_RESET: NULL
         STATUS: disabled
           TYPE: status_counter
        COMMENT: Number of rows inserted

 

■ InnoDB INFORMATION_SCHEMA 임시 테이블에 관련된  정보테이블

INNODB_TEMP_TABLE_INFO InnoDB 인스턴스에서 활성화 사용자 작성 InnoDB 임시 테이블에 대한 정보를 제공합니다. 옵티마이저가 사용하는 내부 InnoDB 임시 테이블에 대한 정보는 제공하지 않습니다.

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_TEMP%';
+---------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_TEMP%) |
+---------------------------------------------+
| INNODB_TEMP_TABLE_INFO                      |
+---------------------------------------------+

 

• Example 14.12 INNODB_TEMP_TABLE_INFO

예는 INNODB_TEMP_TABLE_INFO 테이블의 특성을 보여줍니다.

1. 간단하게 InnoDB 임시테이블을 만듭니다.:

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

 

2. INNODB_TEMP_TABLE_INFO 조회하여 임시 테이블 메타 데이터를 확인합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 194
                NAME: #sql7a79_1_0
              N_COLS: 4
               SPACE: 182
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE

TABLE_ID 임시 테이블의 고유 식별자입니다. NAME 열에는 임시 테이블의 시스템 생성 이름이 표시되며 앞에 "#sql" 붙습니다. InnoDB 항상 3개의 숨겨진 테이블 (DB_ROW_ID, DB_TRX_ID DB_ROLL_PTR) 생성하므로 (N_COLS) 1 아니라 4입니다. PER_TABLE_TABLESPACE IS_COMPRESSED 압축 임시 테이블에 대해 TRUE를보고합니다. 그렇지 않으면이 필드는 FALSE를보고합니다.

 

3. 압축 임시 테이블 만들기

mysql> CREATE TEMPORARY TABLE t2 (c1 INT) ROW_FORMAT=COMPRESSED ENGINE=INNODB;

 

4. INNODB_TEMP_TABLE_INFO 테이블을 다시 쿼리.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 195
                NAME: #sql7a79_1_1
              N_COLS: 4
               SPACE: 183
PER_TABLE_TABLESPACE: TRUE
       IS_COMPRESSED: TRUE
*************************** 2. row ***************************
            TABLE_ID: 194
                NAME: #sql7a79_1_0
              N_COLS: 4
               SPACE: 182
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE

PER_TABLE_TABLESPACE IS_COMPRESSED 압축된 임시 테이블에 대해 TRUE 보고합니다. 압축 임시 테이블은 별도의 테이블당 파일 테이블 스페이스에 작성되므로 압축 임시 테이블의 SPACE ID 다릅니다. 압축되지 않은 임시 테이블은 공유 임시 테이블 스페이스 (ibtmp1) 작성되며 동일한 SPACE ID 보고합니다.

 

5. MySQL재시작과 INNODB_TEMP_TABLE_INFO 조회

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
Empty set (0.00 sec)

INNODB_TEMP_TABLE_INFO 서버가 종료될 해당 데이터가 디스크에 유지되지 않기 때문에 세트가 리턴됩니다.

 

6. 새로운 임시 테이블을 만듭니다.

mysql> CREATE TEMPORARY TABLE t1 (c1 INT PRIMARY KEY) ENGINE=INNODB;

 

7. INNODB_TEMP_TABLE_INFO 조회하여 임시 테이블 메타 데이터를 확인합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*************************** 1. row ***************************
            TABLE_ID: 196
                NAME: #sql7b0e_1_0
              N_COLS: 4
               SPACE: 184
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE

SPACE ID 서버가 시작될때 동적으로 생성되므로 다를 있습니다.

 

■ INFORMATION_SCHEMA.FILES에서 InnoDB 테이블 스페이스 메타 데이터 검색

INFORMATION_SCHEMA.FILES 테이블은 테이블 파일 테이블 스페이스, 일반 테이블 스페이스, 시스템 테이블 스페이스, 임시 테이블 테이블 스페이스 언두 테이블 스페이스 (있는 경우) 포함하여 모든 InnoDB 테이블 스페이스 유형에 대한 메타 데이터를 제공합니다. 그리고 InnoDB 사용 예제를 제공합니다.

INNODB_SYS_TABLESPACES INNODB_SYS_DATAFILES 테이블은 InnoDB 테이블 스페이스에 대한 메타 데이터도 제공하지만 데이터는 테이블 파일 일반 테이블 스페이스로 제한됩니다.

 

쿼리는 InnoDB 테이블 스페이스와 관련된 INFORMATION_SCHEMA.FILES 테이블의 필드에서 InnoDB 시스템 테이블 스페이스에 대한 메타 데이터를 검색합니다. InnoDB 관련이 없는 INFORMATION_SCHEMA.FILES 필드는 항상 NULL 반환하며 쿼리에서 제외됩니다.

mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS,
       TOTAL_EXTENTS,  EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE
       FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME LIKE 'innodb_system' \G
*************************** 1. row ***************************
        FILE_ID: 0
      FILE_NAME: ./ibdata1
      FILE_TYPE: TABLESPACE
TABLESPACE_NAME: innodb_system
   FREE_EXTENTS: 0
  TOTAL_EXTENTS: 12
    EXTENT_SIZE: 1048576
   INITIAL_SIZE: 12582912
   MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 67108864
      DATA_FREE: 4194304
         ENGINE: NORMAL

 

쿼리는 테이블당 InnoDB 파일 일반 테이블 스페이스에 대한 FILE_ID (공간 ID 동일) FILE_NAME (경로 정보 포함) 검색합니다. 테이블당 파일 일반 테이블 스페이스의 파일 확장자는 .ibd입니다.

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%.ibd%' ORDER BY FILE_ID;
    +---------+---------------------------------------+
    | FILE_ID | FILE_NAME                             |
    +---------+---------------------------------------+
    |       2 | ./mysql/plugin.ibd                    |
    |       3 | ./mysql/servers.ibd                   |
    |       4 | ./mysql/help_topic.ibd                |
    |       5 | ./mysql/help_category.ibd             |
    |       6 | ./mysql/help_relation.ibd             |
    |       7 | ./mysql/help_keyword.ibd              |
    |       8 | ./mysql/time_zone_name.ibd            |
    |       9 | ./mysql/time_zone.ibd                 |
    |      10 | ./mysql/time_zone_transition.ibd      |
    |      11 | ./mysql/time_zone_transition_type.ibd |
    |      12 | ./mysql/time_zone_leap_second.ibd     |
    |      13 | ./mysql/innodb_table_stats.ibd        |
    |      14 | ./mysql/innodb_index_stats.ibd        |
    |      15 | ./mysql/slave_relay_log_info.ibd      |
    |      16 | ./mysql/slave_master_info.ibd         |
    |      17 | ./mysql/slave_worker_info.ibd         |
    |      18 | ./mysql/gtid_executed.ibd             |
    |      19 | ./mysql/server_cost.ibd               |
    |      20 | ./mysql/engine_cost.ibd               |
    |      21 | ./sys/sys_config.ibd                  |
    |      23 | ./test/t1.ibd                         |
    |      26 | /home/user/test/test/t2.ibd           |
    +---------+---------------------------------------+

 

쿼리는 InnoDB 임시 테이블 스페이스에 대한 FILE_ID FILE_NAME 검색합니다. 임시 테이블 스페이스 파일 이름 앞에는 ibtmp 있습니다.

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%ibtmp%';
+---------+-----------+
| FILE_ID | FILE_NAME |
+---------+-----------+
|      22 | ./ibtmp1  |
+---------+-----------+

 

마찬가지로 InnoDB 언두 테이블 스페이스 파일 이름 앞에는 언두(실행취소) 있습니다. 다음 쿼리는 별도의 언두(Undo) 테이블 스페이스가 구성된 경우 InnoDB 언두(Undo) 테이블 스페이스에 대해 FILE_ID FILE_NAME 반환합니다.

mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES
       WHERE FILE_NAME LIKE '%undo%';

 

도움이 되셨다면 광고클릭 한번 부탁드립니다.※

'Databases > MySQL' 카테고리의 다른 글

[MySQL][InnoDB] Deadlock  (0) 2020.07.07
[MySQL][InnoDB] InnoDB에서 다른 SQL 문으로 설정된 잠금  (0) 2020.07.02
[MySQL][InnoDB] Online DDL  (0) 2020.06.28
[MySQL][InnoDB] 행(Row)형식  (0) 2020.06.22
[MySQL][InnoDB] 페이지 압축  (2) 2020.06.17

Designed by JB FACTORY