Performance Schema

performance_schema란?

• MySQL서버 실행을 모니터링하는 기능의 스키마
• 주로 성능 데이터에 중점을 두고 있으며, 이는 메타 데이터 검사에 사용되는 Information_schema와 차이점이 있음
• 성능 스키마는 MySQL서버의 이벤트를 모니터링 진행
• 성능 스키마 구성은 SQL문을 통해 performance_schema데이터베이스의 테이블을 업데이트하여 동적으로 수정이 가능하며, 디스크 스토리지를 사용하지 않는 인 메모리 테이블

Performance Schema 주요 테이블

use performance_schema;
show tables;

!테이블 주요 내용에 관한건 추후 블로그 포스팅 진행

setup 테이블

• 데이터 수집 및 저장과 관련된 설정 정보 저장

instance 테이블

• 데이터를 수집하는 대상인 실체환된 객체들, 인스턴스들에 대한 정보를 제공

connection 테이블

• mysql에서 생성된 커넥션들에 대한 통계 및 속성 정보 제공

variable 테이블

• mysql서버의 시스템 변수 및 사용자 정의 함수와 상태 변수들에 대한 정보 제공

event 테이블

• 스레드에서 실행된 쿼리 처리와 관련된 이벤트

summary 테이블

• 수집한 이벤트들을 특정 기준별로 집계한 후 요약한 정보 제공

lock 테이블

• mysql에서 발생한 잠금과 관련된 정보 제공

replication 테이블

• 복제 관련 설정 정보

clone 테이블

• clone플러그인을 통해 수행되는 복제 작업에 대한 정보 제공

Performance Schema 관련 주요 명령어

1) 실행 중인 트랜잭션 및 쿼리 정보 검색

SELECT A.PROCESSLIST_ID 
     , C.THREAD_ID 
     , CONCAT(A.PROCESSLIST_USER,'@',A.PROCESSLIST_HOST) AS DB_ACCOUNT
     , C.EVENT_NAME 
     , C.SQL_TEXT 
     , sys.format_time(C.TIMER_WAIT) AS DURATION
     , DATE_SUB(NOW(), INTERVAL (
     							  SELECT VARIABLE_VALUE 
     							    FROM performance_schema.global_status SQ 
     							   WHERE SQ.VARIABLE_NAME ='UPTIME'
     							) -C.TIMER_START *10e-13 SECOND ) AS START_TIME
     , DATE_SUB(NOW(), INTERVAL (
     							  SELECT VARIABLE_VALUE 
     							    FROM performance_schema.global_status SQ 
     							   WHERE SQ.VARIABLE_NAME ='UPTIME'
     							) -C.TIMER_END *10e-13 SECOND ) AS END_TIME						
  FROM performance_schema.threads A
 INNER JOIN performance_schema.events_transactions_current B 
 		 ON B.THREAD_ID=A.THREAD_ID 
 INNER JOIN performance_schema.events_statements_history C
         ON C.NESTING_EVENT_ID = B.EVENT_ID 
 WHERE B.STATE = 'ACTIVE'
   AND C.MYSQL_ERRNO = 0
 ORDER BY A.PROCESSLIST_ID , B.TIMER_START

• MySQL의 performance_schema 스키마를 사용하여 MySQL서버 실행을 모니터링하는 기능

조회 컬럼

• A.PROCESSLIST_ID : 스레드의 프로세스 목록ID 표시
• C.THREAD_ID : 스레드의 고유 식별자인 스레드ID 표시
• CONCAT(A.PROCESSLIST_USER,'@',A.PROCESSLIST_HOST) AS DB_ACCOUNT : 사용자 이름 + 호스트 주소 조합
• C.EVENT_NAME : 이벤트 이름 표시
• C.SQL_TEXT : SQL 구문 텍스트를 표시
• sys.format_time(C.TIMER_WAIT) AS DURATION : 대기시간 표시
• DATE_SUB(NOW(), INTERVAL ...) AS START_TIME" 및 "DATE_SUB(NOW(), INTERVAL ...) AS END_TIME : 트랜잭션 시작시간과 종료시간

참조 테이블

• performance_schema.threads A : 스레드 기본 정보 제공 테이블
• performance_schema.events_transactions_current B : 현재 실행중인 트랜잭션 정보 저장 테이블(트랜잭션 상태 및 스레드 ID제공)
• performance_schema.events_statements_history C : SQL문 실행 이력 정보 저장 테이블

조건절

• B.STATE = 'ACTIVE' : 실행중(ACTIVE)인 트랜잭션만 선택
• C.MYSQL_ERRNO = 0 : 오류번호가 0번, 즉 성공적으로 실행된 SQL쿼리문만을 의미

결과

2) 메모리 확인

SELECT A.*
  FROM sys.memory_global_total A;

• 해당 쿼리는 서버 내의 총 메모리 사용량이 요약
  

3) 테이블 행 수, 입출력 작업 횟수

SELECT T.TABLE_SCHEMA
     , T.TABLE_NAME
     , T.TABLE_ROWS 
     , TIO.COUNT_READ
     , TIO.COUNT_WRITE
  FROM information_schema.TABLES T 
  JOIN performance_schema.table_io_waits_summary_by_table TIO
    ON TIO.OBJECT_SCHEMA = T.TABLE_SCHEMA AND TIO.OBJECT_NAME = T.TABLE_NAME 
 WHERE T.TABLE_SCHEMA NOT IN ('mysql','performance_schema','sys')
   AND TIO.COUNT_WRITE = 0
 ORDER BY T.TABLE_SCHEMA 
        , T.TABLE_NAME;

조회컬럼

• T.TABLE_SCHEMA : 테이블의 스키마(데이터베이스) 표시
• T.TABLE_NAME : 테이블 이름 표시
• T.TABLE_ROWS : 테이블의 행 수 표시
• TIO.COUNT_READ : 읽기 작업 횟수 표시
• TIO.COUNT_WRITE : 쓰기 작업 횟수 표시

참조 테이블

• information_schema.TABLES T : 데이터베이스 스키마 및 테이블 정보 저장 테이블
• performance_schema.table_io_waits_summary_by_table TIO : 테이블 별 입출력 대기정보 저장

조건절

• 데이터베이스가 'mysql', 'performance_schema', 'sys'가 아닌 경우를 필터링하여, 시스템 스키마는 제외

결과

참조

https://neocan.tistory.com/398
https://myinfrabox.tistory.com/194
https://hoing.io/archives/3811