Last Updated on 11월 20, 2023 by Jade(정현호)
안녕하세요
이번 글에서는 MySQL 의 Performance Schema(성능 스키마)에 대해서 전반적으로 살펴보도록 하겠습니다.
해당 내용은 MySQL 성능최적화 책 과 MySQL Document 에 대해서 정리한 내용입니다.
Performance Schema 소개
부하가 높은 데이터베이스의 성능을 튜닝하는 일은 반복적으로 수행하게 됩니다.
이러한 데이터베이스를 튜닝과 개선하는 활동을 하면서 여러가지 원인과 이유를 찾는 과정을 거치게 됩니다.
이러한 여러 가지 질문에 대해서 대답을 찾는 여러가지 방법이 있을 것이며, 원인과 이유 등을 찾기 위한 살펴보는 데이터 중에서 MySQL 에서는 여러 중요한 데이터 중에서 Performance Schema 가 있습니다.
SYS스키마와 함께 Performance Schema를 이용해서 MySQL 내부에서 발생 또는 일어나는 일들에 대해서 정보를 찾는 여러가지 방법에 대해서 확인해볼 수 있습니다.
Performance Schema가 어떻게 동작하는지를 이해하기 위해서는 두가지를 알아야 할 내용(개념)이 있습니다.
첫번째는 인스트루먼트(instrument) 입니다.
인스트루먼트는 정보를 얻고자 하는 MySQL 코드의 어떤 부분을 나타내거나 가리킵니다.
예를 들어서 Metadata의 Lock 에 대한 정보를 수집하기 위해서는 wait/lock/metadata/sql/mdl 이라는 인스트루먼트를 활성화해야 정보를 수집 및 확인할 수 있습니다.
두번째 개념은 어떤 코드를 수행하였는지에 대한 정보를 저장하는 테이블인 컨슈머(consumer) 입니다.
쿼리를 실행하게 되면 컨슈머는 총 실행 횟수, 인덱스가 사용되지 않은 횟수, 수행 시간 등과 같은 여러 다양한 정보를 기록하게 됩니다.
컨슈머는 대부분 사람들이 Performance Schema 통해서 조회하고 확인하는 데이터(정보) 입니다.
[performance_schema 데이터 수집 및 저장 정보 제공 프로세스]
애플리케이션에서(또는 사용자)가 MySQL에 연결하여 SQL을 수행 실행할 때 performance_schema는 검사된 각 호출을 두개의 매크로로 캡슐화한 다음 해당 컨슈머 테이블에 결과를 기록하게 됩니다.
인스트루먼트를 활성화하면 추가 코드가 호출되기 때문에 필연적으로 인스트루먼트가 CPU 를 더 사용할 수 있다는 점은 주요한 포인트가 됩니다.
인스트루먼트
performance_schema의 setup_instruments 테이블에는 지원되는 모든 인스트루먼트 목록이 포함되어 있으며, 모든 인스트루먼트 명은 슬래시로 구분해서 구성되어 있습니다.
인스트루먼트 이름이 어떤 형태로 지정되어 있는지는 아래의 예시를 통해서 확인해보도록 하겠습니다.
- statement/sql/select
- wait/synch/mutex/innodb/autoinc_mutex
인스트루먼트 이름의 가장 왼쪽이 인스트루먼트의 종류를 의미합니다. 따라서 위의 예시에서 statement는 인스트루먼트가 sql 문장임을 나태내고 있으며, wait/ 은 대기 관련된 내용을 나타내고 있습니다.
select는 statement 유형인 sql 하위 시스템의 일부입니다.
autoinc_mutex 는 innodb에 속하며, 이는 보다 일반적인 인스트루먼트 클래스인 mutex의 일부입니다.
그리고 인스트루먼트 유형 wait의 보다 일반적인 인스트루먼트 synch의 일부입니다.
보통의 경우는 인스트루먼트 이름(명) 을 통해서 그 자체로 설명되게 됩니다.
앞에서의 wait/synch/mutex/innodb/autoinc_mutex 는 InnoDB가 auto-increment 열에 설정하는 뮤텍스 입니다.
이런 인스트루먼트의 리스트는 performance_schema.setup_instruments 에서 확인할 수 있으며, documentation 컬럼에 기록된 정보를 토대로 해당 인스트루먼트에 대한 내용을 조금 더 명시적으로 확인할 수 있습니다.
다만 모든 인스트루먼트에 대해서 documentation 내용이 있는 것은 아닙니다.
-- setup_instruments의 전체 카운트 mysql> select count(*) from performance_schema.setup_instruments; +----------+ | count(*) | +----------+ | 1220 | +----------+ -- documentation 이 있는 건수 mysql> select count(*) from performance_schema.setup_instruments where documentation is not null; +----------+ | count(*) | +----------+ | 86 | +----------+
조회 시 나오는 일부 결과의 예시입니다.
mysql> select * from performance_schema.setup_instruments
where documentation is not null limit 2\G
*************************** 1. row ***************************
NAME: wait/synch/mutex/pfs/LOCK_pfs_share_list
ENABLED: NO
TIMED: NO
PROPERTIES: singleton
VOLATILITY: 1
DOCUMENTATION: Components can provide their own performance_schema tables.
This lock protects the list of such tables definitions.
*************************** 2. row ***************************
NAME: wait/synch/mutex/sql/LOCK_tls_ctx_options
ENABLED: NO
TIMED: NO
PROPERTIES:
VOLATILITY: 0
DOCUMENTATION: A lock to control all of the --ssl-* CTX
related command line options for client server connection port
설명한 내용처럼 전체 중에서 documentation 에 내용이 있는 비중이 많지는 않습니다. 그래서 특정 인스트루먼트에 대한 내용을 이해하기 위해서는 인스트루먼트 이름이나 또는 MySQL 소스코드나 기타 MySQL 에 대한 경험 또는 지식을 활용해야 합니다.
컨슈머(consumer)
컨슈머(consumer)는 인스트루먼트가 정보를 보내는 대상을 의미 합니다. performance_schema는 인스트루먼트 결과를 많은 테이블에 저장하게 됩니다.
버전 별로 차이가 있지만 MySQL Community 8.0.25 버전에는 performance_schema 에 110개의 테이블이 있습니다.
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.25 | +-----------+ mysql> use performance_schema; mysql> show tables; +------------------------------------------------------+ | Tables_in_performance_schema | +------------------------------------------------------+ | accounts | < ..중략 ..> 110 rows in set (0.01 sec)
테이블이 많기 때문에 테이블마다 저장되어 있고 의도하는 바를 이해하기 위해서는 그룹으로 묶어서 살펴보고 이해하는 것이 도움이 될 수 있습니다.
현재 및 과거 데이터
이벤트는 이름이 다음과 같이 끝나는 테이블에 넣습니다.
*_current : 현재 서버에서 발생하고 있는 이벤트
* _history : 스레드당 최종 10개의 완료된 이벤트
*_history_long : 전역으로 스레드당 최종 10,000개의 완료된 이벤트
* _history 와 *_history_long 테이블의 크기는 MySQL 시스템 변수에서 설정할 수 있습니다.
파라미터는 performance_schema_events_ 로 시작하는 파라미터들 입니다.
mysql> show variables like '%performance_schema_events_%'; +----------------------------------------------------------+-------+ | Variable_name | Value | +----------------------------------------------------------+-------+ | performance_schema_events_stages_history_long_size | 10000 | | performance_schema_events_stages_history_size | 10 | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_statements_history_size | 10 | | performance_schema_events_transactions_history_long_size | 10000 | | performance_schema_events_transactions_history_size | 10 | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | +----------------------------------------------------------+-------+
다음과 같은 지표에 대해서 현재와 과거 데이터를 확인 할 수 있습니다.
events_wait : 뮤텍스 획득과 같은 로우-레벨 서버 대기
events_statements : SQL 문
events_stages : 임시 테이블 생성이나 데이터 전송과 같은 프로파일 정보
events_transactions : 트랜잭션
Summary 테이블과 다이제스트(digest)
Summary(요약) 테이블은 테이블이 제안하는 모든 것에 대한 집계된 정보를 저장합니다.
예를 들어서 memory_summary_by_thread_by_event_name 테이블은 사용자 커텍션 또는 모든 백그라운드 스레드에 대한 MySQL 스레드당 집계된 메모리 사용량을 저장하고 있습니다.
다이제스트는 쿼리에서 변경되는 부분(where 절의 상수값 등)을 제거하고 쿼리를 집계하는 방법입니다.
아래의 예제 쿼리를 통해서 내용을 확인해보도록 하겠습니다.
select user,birth_date from users where user_id=20; select user,birth_date from users where user_id=64; select user,birth_date from users where user_id=5;
위의 쿼리에 대한 다이제스트는 다음과 같이 되게 됩니다.
select user,birth_date from users where user_id=?
이렇게 저장 관리함으로써 performance_schema는 각 쿼리의 다른 부분을 별도로 유지할 필요 없이 다이제스트에 대한 성능 지연과 같은 메트릭을 확인 추적할 수 있습니다.
인스턴스
인스턴스는 MySQL 설치에 사용할 수 있는 객체 인스턴스를 의미합니다.
예를 들어서 file_instances 테이블에는 파일명과 이들 파일에 대한 액세스한 스레드 수를 포함합니다.
Setup(셋업)
Setup(셋업) 테이블은 현재 instrumentation 에 대한 설정 정보를 제공하고, 구성을 변경할 수 있도록 지원하며, performance_schema의 런타임 설정에 사용됩니다.
기타 다른 테이블
테이블 이름에서 네이밍 룰(또는 패턴)을 따르지 않는 테이블이 있습니다. 예를 들어 metadata_locks 테이블은 메타데이터 락에 대한 정보를 제공하고 있습니다.
자원 소비
performance_schema에 의해 수집된 데이터는 메모리에 보관되게 됩니다. 컨슈머의 최대 사이즈를 설정해서 사용하는 메모리량은 제한할 수 있습니다.
performance_schema의 일부 테이블은 자동 크기 조정을 지원하며, MySQL 인스턴스 시작시에는 최소한의 메모리를 할당하고 필요에 따라서 크기를 조정하게 됩니다.
다만, 특정 인스트루먼트는 비활성화 하고 테이블을 truncate한 이후에도 할당된 이 메모리가 해제되지 않는 경우도 있습니다.
모든 인스트루먼트 호출은 performance_schema에 성능 관련 데이터를 저장하기 위해 두개의 매크로를 호출하게 됩니다.
그래서 더 많은 인스트루먼트를 수행할 수록 CPU 사용량이 증가할 수 있게 됩니다.
인스트루먼트 별로 호출되는 빈도수는 차이가 있습니다. 가령 쿼리 구문과 관련된 인스트루먼트는 쿼리 중에 한 번만 호출될 수 있는 반면에 대기에 관련된 인스트루먼트는 상대적으로 훨씬 더 빈번하게 호출될 수 있습니다.
또한 다른 예시로 백만 개의 행을 가진 InnoDB 테이블에 대해서 스캔을 하려면 엔진이 백만 개의 행을 잠금을 설정하고 해제를 해야 합니다. 이러 경우에는 잠금 관련된 인스트루먼트에 의해서 CPU 사용량이 증가할 수 있습니다.
제약 사항
이번에는 performan_schema 에 대한 제약사항에 대해서 살펴보도록 하겠습니다.
• MySQL 컴포넌트에서 지원해야 합니다.
예를 들어서 메모리 인스트루먼트를 사용해서 어떤 MySQL 구성 요소 또는 스레드가 대부분의 메모리를 사용하는지 계산한다고 가정할 때에 가장 많은 메모리를 사용하는 구성 요소가 메모리 인스트루먼트를 지원하는 않는 스토리지 엔진이라고 확인하였습니다.
이럴 경우에는 메모리를 어디에서 사용하였는지 찾을 수가 없습니다.
• 인스트루먼트와 컨슈머가 활성된 이후 데이터 수집됩니다.
모든 인스트루먼트가 비활성화된 상태에서 MySQL 서버를 시작한 다음 메모리 사용량을 측정하기로 결정한 경우 InnoDB 버퍼풀과 같은 전역 메모리 버퍼가 메모리 인스트루먼트를 활성화하기 전에 이미 활동되었기 때문에 할당된 정확한 양을 알 수 없습니다.
• 메모리 해제가 어렵습니다.
MySQL 서버를 시작할 때 컨슈머의 사이즈를 제한하거나 자동 크기 설정으로 둘 수 있습니다. (체크필요)
자동 크기 설정의 경우 시작 시 메모리를 할당하지 않고 인스트루먼트가 활성화된 데이터를 수집될 때만 메모리를 할당하게 됩니다.
그러나 이후에 특정 인스트루먼트 또는 컨슈머를 비활성화하더라도 MySQL 서버를 다시 시작하지 않으면 메모리는 해제되지 않습니다.
sys 스키마
MySQL 5.7.7 버전 부터 sys 스키마 라고 하는 performance_schema 의 수집된 데이터를 DBA와 개발자가 해석하는 데 도움이 되는 개체 집합이 포함되어 있습니다. sys schema 객체는 일반적인 튜닝 및 진단 사용 사례에 사용할 수 있습니다.
- 성능 스키마 데이터를 더 쉽게 이해할 수 있는 형태로 요약된 View
- 성능 스키마 구성 및 진단 보고서 생성과 같은 작업을 수행하는 Stored Procedure
- 성능 스키마 구성을 쿼리하고 형식 지정 서비스를 제공하는 Stored Function
이 처럼 performance_schema를 보다 손쉽게 사용하도록 설계 및 제공되고 있으며, 자체적으로 데이터를 저장하고 있지는 않습니다.
View 형태이기 때문에 sys 스키마에서 원하는 데이터가 없을 경우나 다른 정보를 추가로 확인하고자 할 때는 performance_schema를 직접 참조해야 할 수도 있습니다.
예를 들어서 sys.schema_unused_indexes 뷰에 제공되는 정보 이외에 추가로 다른 정보 등이 필요하다면 아래와 같이 뷰에서 참조하는 performance_schema 테이블이 무엇인지 확인 후에 직접 확인하는 것도 방법이 될 수 있습니다.
mysql> show create table sys.schema_unused_indexes;
sys.schema_unused_indexes 에 대해서는 아래 포스팅을 참조하시면 됩니다.
스레드에 대한 이해
MySQL 서버는 멀티 스레드 방식으로 동작하는 데이터베이스 소프트웨어입니다.
(물론, 다른 RDBMS의 경우 멀티 프로세스 방식도 있습니다. )
그래서 MySQL 각 구성 요소는 스레드를 사용하게 되며, 예를 들어서 메인 스레드나 스토리지 엔진에 의해 생성된 백그라운드 스레드이거나 사용자 연결을 위해 생성된 포그라운드 스레드 등이 있습니다.
각 스레드는 최소 두 개의 고유 식별자가 있습니다. 예를 들어서 리눅스에서는 아래와 같이 ps 명령어를 통해서 운영 체제 스레드ID와 내부적인 MySQL 스레드 ID 그외 기타 정보를 확인할 수 있습니다.
$ ps -eLF UID PID PPID LWP C NLWP SZ RSS PSR STIME TTY TIME CMD root 18367 1 18367 0 1 28764 3296 3 16:36 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data mysql 19030 18367 19030 8 38 614746 417376 3 16:36 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/data mysql 19030 18367 19033 0 38 614746 417376 1 16:36 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/data mysql 19030 18367 19034 0 38 614746 417376 0 16:36 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/data mysql 19030 18367 19035 0 38 614746 417376 0 16:36 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/data mysql 19030 18367 19036 0 38 614746 417376 1 16:36 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/data mysql 19030 18367 19037 0 38 614746 417376 0 16:36 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/data
이 내부적인 MySQL 스레드ID는 대부분 performance_schema 테이블에서 thread_id 로 표현됩니다. 그리고 각 포그라운드 스레드는 show processlist 명령어 또는 information_schema.processlist 뷰의 ID 컬럼값에서 processlist_id 를 확인할 수 있습니다.
단, THREAD_ID 는 PROCESSLIST_ID 와 같지 않습니다.
performance_schema.threads 테이블에는 MySQL에 존재하는 모든 스레드에 대한 정보가 있습니다.
mysql> select name,thread_id,processlist_id,thread_os_id from performance_schema.threads where PROCESSLIST_ID=8; +---------------------------+-----------+----------------+--------------+ | name | thread_id | processlist_id | thread_os_id | +---------------------------+-----------+----------------+--------------+ | thread/sql/one_connection | 48 | 8 | 19499 | +---------------------------+-----------+----------------+--------------+
thread_id 외에도 show processlist(information_schema.processlist)의 에서의 ID와 같은 데이터인 processlist_id 데이터도 확인할 수 있습니다.
mysql> select id,user,command,state from information_schema.processlist; +----+-----------------+---------+------------------------+ | id | user | command | state | +----+-----------------+---------+------------------------+ | 8 | root | Query | executing | +----+-----------------+---------+------------------------+
thread_os_id 컬럼값은 운영 체제 프로세스의 스레드 값과 매칭이 됩니다.
$ ps -eLF | grep 19499 mysql 19030 18367 19499 <..중략..> 00:00:00 /usr/local/mysql..(중략)
performance_schema 전역적으로는 thread_id가 사용되는 반면, processlist_id 는 잠금을 오랫동안 획득하고 있는 연결 스레드를 종료하기 위해서 processlist_id 를 사용합니다.
그래서 performance_schema.threads 테이블은 실행 중인 쿼리에 대한 추가 정보를 확인 하기 위해서 여러 테이블과 조인을 해서 사용하기도 합니다.
performance_schema 설정
performance_schema 자체에 대한 설정과 메모리 사용 및 수집된 데이터에 대한 제한과 관련된 시스템 변수에 대한 활성화 또는 비활성화 하는 것과 같은 부분은 서버 시작시에만 변경할 수 있습니다.
performance_schema의 인스트루먼트 및 컨슈머의 활성화 또는 비활성화는 동적으로 변경할 수 있습니다.
그래서 모든 인스트루먼트를 처음부터 활성화하는 것이 아니라 인스투먼트 별로 시작할 때부터 활성화해서 수집할 내용이 있을 수도 있고, 필요시에 따라서 그때 그때 마다 활성화해서 사용하는 것이 performance_schema에 의한 CPU 및 메모리 리소스 사용을 낭비하지 않는 방법이라고 생각 합니다.
활성화/비활성화
performance_schema 자체를 활성화 또는 비활성화하려면 MySQL 서버의 performance_schema 시스템 변수를 ON(1) 또는 OFF(0) 로 설정하면 됩니다. 해당 시스템 변수는 MySQL 시작(재시작)에만 적용이 가능합니다.
해당 시스템 변수의 기본값은 ON(1) 입니다.
인스트루먼트 활성화 비활성화
인스트루먼트를 활성화하거나 비활성화에 따라서 performance_schema 에서 수집하게 되는 성능 지표에 대해서 선별적 선택을 할 수 있습니다.
인스트루먼트의 상태를 확인하려면 setup_intruments 테이블을 조회합니다.
mysql> select * from performance_schema.setup_instruments
where name='statement/sql/select'\G
*************************** 1. row ***************************
NAME: statement/sql/select
ENABLED: YES
TIMED: YES
PROPERTIES:
VOLATILITY: 0
DOCUMENTATION: NULL
컬럼중에서 ENABLED 컬럼 정보에 따라서 해당 인스트루먼트의 활성화 여부를 확인할 수 있습니다.
위의 예제에서는 해당 인스트루먼트가 활성화되어 있음을 확인할 수 있습니다.
performance_schema 활성화를 한 다음에 기본적으로 ENABLED: YES(활성화) 되어 있는 인스트루먼트는 MySQL 서버 버전에 따라서 차이가 있습니다.
인스트루먼트를 활성화하거나 비활성화를 하는 방법은 3가지가 있습니다.
- setup_intruments 테이블을 변경합니다.(update)
- sys스키마의 ps_setup_intrument 스토어드 프로시저를 사용합니다.
- my.cnf 에서 performance-schema-instrument 파라미터 설정 후 MySQL 서버를 시작합니다.
setup_intruments update
setup_intruments테이블을 update 하는 방법으로 일반적으로 많이 사용하는 방법입니다.
-- 활성화 mysql> update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES' where name='statement/sql/select'; -- 비활성화 mysql> update performance_schema.setup_instruments set ENABLED='NO', TIMED='NO' where name='statement/sql/select';
표준 SQL 업데이트 구문으로 수행하기 때문에 like 로 해서 여러 인스트루먼트를 활성화/비활성화 할 수 있습니다.
-- LIKE 를 사용하여 여러 인스트루먼트를 활성화 mysql> update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES' where name like 'statement/sql/%'; -- LIKE 를 사용하여 여러 인스트루먼트를 비활성화 mysql> update performance_schema.setup_instruments set ENABLED='NO', TIMED='NO' where name like 'statement/sql/%';
여기서 중요한 점은 performance_schema.setup_instruments 테이블을 업데이트하는 방식은 다이나믹한 방식으로 영구적이지 않습니다. MySQL 서버를 재시작시 다시 적용해야 합니다.
-- statement/sql/% 조회 -- ENABLED='NO' 는 0건임 mysql> select count(*) from performance_schema.setup_instruments where name like 'statement/sql/%' and ENABLED='NO'; +----------+ | count(*) | +----------+ | 0 | +----------+ -- LIKE 를 사용하여 여러 인스트루먼트를 비활성화 : YES -> NO mysql> update performance_schema.setup_instruments set ENABLED='NO',TIMED='NO' where name like 'statement/sql/%'; Rows matched: 159 Changed: 159 Warnings: 0 -- statement/sql/% 조회 -- ENABLED='NO' 이 159건으로 확인됨 mysql> select count(*) from performance_schema.setup_instruments where name like 'statement/sql/%' and ENABLED='NO'; +----------+ | count(*) | +----------+ | 159 | +----------+ ===== MySQL 서버 재시작 ===== -- 재시작 후에 다시 조회 mysql> select count(*) from performance_schema.setup_instruments where name like 'statement/sql/%' and ENABLED='NO'; +----------+ | count(*) | +----------+ | 0 | +----------+
프로시저 이용
sys 스키마의 ps_setup_enable_instrument 와 ps_setup_disable_instrument 를 이용해서 인스트루먼트를 활성화 또는 비활성화 할 수 있습니다.
특정 인스트루먼트도 가능하고 여러 인스트루먼트를 동시에 변경하는 것도 가능 합니다.
-- 특정 인스트루먼트에 대해서 활성화
mysql> CALL sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');
+----------------------+
| summary |
+----------------------+
| Enabled 1 instrument |
+----------------------+
-- 특정 인스트루먼트에 대해서 비활성화
mysql> CALL sys.ps_setup_disable_instrument('wait/lock/metadata/sql/mdl');
+-----------------------+
| summary |
+-----------------------+
| Disabled 1 instrument |
+-----------------------+
-- 모든 mutex instruments에 대해서 활성화
mysql> CALL sys.ps_setup_enable_instrument('mutex');
+-------------------------+
| summary |
+-------------------------+
| Enabled 231 instruments |
+-------------------------+
-- 모든 mutex instruments에 대해서 비활성화
CALL sys.ps_setup_disable_instrument('mutex');
+--------------------------+
| summary |
+--------------------------+
| Disabled 231 instruments |
+--------------------------+
이 방법 역시 setup_intruments 테이블을 업데이트해서 변경하는 것과 동일하게 영구적이지 않습니다.
MySQL 서버가 재시작 되면 다시 적용 수행(적용) 해야 합니다.
시스템 변수 설정(my.cnf)
앞에서 살펴본 두 가지 방법 모두 다이나믹 적용 방식이며, 사용 중에 인스트루먼트의 활성화 및 비활성화가 가능한 반면에, 영구적으로 저장되지는 않습니다.(휘발성)
그래서 두 가지 방법 중 하나를 선택하여 MySQL 서버를 사용하는 중에 원하는 인스트루먼트를 활성화/비활성화를 수행 후에는 my.cnf 파일에 동일한 내용의 시스템 변수를 설정하여 재시작시에도 계속 적용되도록 해야 합니다.
서버가 재시작시에도 적용되게 하러면 my.cnf 파일에서 performance-schema-instrument 시스템 변수를 사용합니다.
• 설정 예시)
performance-schema-instrument='인스트루먼트_이름=값'
인스트루먼트_이름 에는 활성화/비활성화 하고자하는 인스트루먼트 이름이 들어가고, 값 에는 ON(1) 또는 OFF(0) 가 입력됩니다.
performance-schema-instrument='wait/synch/mutex/sql/LOCK_status=ON'
여러개의 인스트루먼트를 활성화/비활성화를 해야할 경우 시스템 변수를 여러번 지정해서 사용하시면 됩니다.
performance-schema-instrument='wait/synch/mutex/sql/LOCK_status=ON' performance-schema-instrument='wait/synch/mutex/innodb/autoinc_mutex=ON'
해당 시스템 변수에서 인스트루먼트 지정은 와일드카드도 가능 합니다.
performance-schema-instrument='%mutex%=ON'
MySQL 서버 버전에 따라서 인스투먼트가 기본 값으로 활성화된 종류는 상이하며, 8.0 버전은 5.7 버전에 비해 활성화가 기본값으로 되어있는 숫자와 비중이 더 높습니다.
• 5.7 버전 : 전체 1020개 중에서 기본적으로 활성화 327개, 비율 32%
• 8.0 버전 : 전체 1216개 중에서 기본적으로 활성화 778개, 비율 63%
컨슈머 활성화 비활성화
인스트루먼트와 마찬가지로 동일하게 컨슈머도 다음 방법을 통해서 활성화 또는 비활성화할 수 있습니다.
- performance_schema.setup_consumers 테이블을 update
- sys 스키마의 ps_setup_enable_consumer or ps_setup_disable_consumer 를 사용하여 활성/비활성화
- my.cnf 에 performance-schema-consumer 시스템 변수를 설정
15개의 사용 가능한 컨슈머가 있으며 그 중 일부는 이름을 통해서 해당 내용을 직관적으로 알 수 있지만 일부는 추가로 설명이 찾아볼 필요가 있는 컨슈머도 있습니다.
- events_states_[current|history|history_long] : 다음과 같은 프로파일링 세부정보
creating tmp table, statistics, buffer pool load - events_statement_[current|history|history_long] : sql 문 통계
- events_transaction_[current|history|history_long] : 트랜잭션 통계
- events_waits_[current|history|history_long] : 대기 통계
- global_intrumentation : Global 인스트루먼트를 활성화하거나 비활성화 합니다.
비활성화된 경우 개별 파라미터가 확인되지 않고, 전역 또는 스레드별 데이터가 유지되지 않습니다.
개별 이벤트는 수집되지 않습니다 - thread_intrumentation : 스레드별 인스트루먼트, 전역 인스트루먼트가 활성화된 경우에만 확인됩니다.
비활성화하면 스레드별 또는 개별 이벤트 데이터가 수집되지 않습니다. - statement_digest : sql 문 다이제스트
특정 객체에 대한 모니터링
setup_objects 테이블은 performance_schema가 특정 객체 유형 또는 스키마를 모니터링 여부를 제어할 수 있습니다.
이 테이블의 최대 크기는 기본적으로 100행입니다.
테이블 크기를 변경하려면 서버 시작 시 performance_schema_setup_objects_size 시스템 변수를 수정합니다.
object_type 컬럼은 event, function, procedure, table, trigger 다섯 가지 값 중에 하나의 값을 지정해서 사용합니다.
object_schema와 object_name 에는 특정 이름을 지정할 수도 있고 와일드카드 형태로도 지정할 수 있습니다.
사용 예시로는 다음과 같습니다.
• test 스키마의 trigger 오브젝트에 대해서 performance_schema 를 비활성화
mysql> insert into performance_schema.setup_objects
(object_type,object_schema,object_name,enabled,timed)
values('TRIGGER','test','%','NO','NO');
• test 스키마의 trg_test 라는 트리거에 대해서 활성화
mysql> insert into performance_schema.setup_objects
(object_type,object_schema,object_name,enabled,timed)
values('TRIGGER','test','trg_test','YES','YES');
performance_schema는 특정 개체를 계측해야 할지 결정할 때 먼저 더 구체적인 규칙을 검색한 다음 덜 구체적인 규칙을 선택합니다.
이러한 객체에 대한 별도의 모니터링 설정에 관해서는 my.cnf 에 설정이 없습니다. 즉 MySQL 서버를 다시 시작하면 해당 테이블에 다시 정보를 입력해줘야 합니다.
또는 insert 문장을 SQL 파일로 작성하고 init_file 옵션을 사용해서 시작 시 해당 SQL파일을 실행하는 형태로 설정해서 사용할 수는 있습니다.
스레드 모니터링
setup_threads 테이블에는 모니터링 대상이 되는 백그라운드 스레드 목록이 기본적으로 포함되어 있습니다.
ENABLED 컬럼은 해당 스레드에 대해서 인스트루먼트의 활성화 여부를 의미합니다. HISTORY 컬럼은 계측된 이벤트 정보를 _history 및 _history_long 테이블에도 저장할지 여부를 설정하는 컬럼입니다.
아래 조회한 MySQL은 8.0.25 버전입니다.
mysql> SELECT * FROM performance_schema.setup_threads; +-----------------------------------------------------+---------+---------+------------+------------+---------------+ | NAME | ENABLED | HISTORY | PROPERTIES | VOLATILITY | DOCUMENTATION | +-----------------------------------------------------+---------+---------+------------+------------+---------------+ | thread/performance_schema/setup | YES | YES | singleton | 0 | NULL | | thread/sql/bootstrap | YES | YES | singleton | 0 | NULL | | thread/sql/manager | YES | YES | singleton | 0 | NULL | | thread/sql/main | YES | YES | singleton | 0 | NULL | | thread/sql/one_connection | YES | YES | user | 0 | NULL | | thread/sql/signal_handler | YES | YES | singleton | 0 | NULL | | thread/sql/compress_gtid_table | YES | YES | singleton | 0 | NULL | | thread/sql/parser_service | YES | YES | singleton | 0 | NULL | | thread/sql/admin_interface | YES | YES | user | 0 | NULL | | thread/mysys/thread_timer_notifier | YES | YES | singleton | 0 | NULL | | thread/sql/event_scheduler | YES | YES | singleton | 0 | NULL | | thread/sql/event_worker | YES | YES | | 0 | NULL | | thread/innodb/log_archiver_thread | YES | YES | | 0 | NULL | | thread/innodb/page_archiver_thread | YES | YES | | 0 | NULL | | thread/innodb/buf_dump_thread | YES | YES | | 0 | NULL | | thread/innodb/clone_ddl_thread | YES | YES | | 0 | NULL | | thread/innodb/clone_gtid_thread | YES | YES | | 0 | NULL | | thread/innodb/dict_stats_thread | YES | YES | | 0 | NULL | | thread/innodb/io_handler_thread | YES | YES | | 0 | NULL | | thread/innodb/io_ibuf_thread | YES | YES | | 0 | NULL | | thread/innodb/io_log_thread | YES | YES | | 0 | NULL | | thread/innodb/io_read_thread | YES | YES | | 0 | NULL | | thread/innodb/io_write_thread | YES | YES | | 0 | NULL | | thread/innodb/buf_resize_thread | YES | YES | | 0 | NULL | | thread/innodb/log_writer_thread | YES | YES | | 0 | NULL | | thread/innodb/log_checkpointer_thread | YES | YES | | 0 | NULL | | thread/innodb/log_flusher_thread | YES | YES | | 0 | NULL | | thread/innodb/log_write_notifier_thread | YES | YES | | 0 | NULL | | thread/innodb/log_flush_notifier_thread | YES | YES | | 0 | NULL | | thread/innodb/recv_writer_thread | YES | YES | | 0 | NULL | | thread/innodb/srv_error_monitor_thread | YES | YES | | 0 | NULL | | thread/innodb/srv_lock_timeout_thread | YES | YES | | 0 | NULL | | thread/innodb/srv_master_thread | YES | YES | | 0 | NULL | | thread/innodb/srv_monitor_thread | YES | YES | | 0 | NULL | | thread/innodb/srv_purge_thread | YES | YES | | 0 | NULL | | thread/innodb/srv_worker_thread | YES | YES | | 0 | NULL | | thread/innodb/trx_recovery_rollback_thread | YES | YES | | 0 | NULL | | thread/innodb/page_flush_thread | YES | YES | | 0 | NULL | | thread/innodb/page_flush_coordinator_thread | YES | YES | | 0 | NULL | | thread/innodb/fts_optimize_thread | YES | YES | | 0 | NULL | | thread/innodb/fts_parallel_merge_thread | YES | YES | | 0 | NULL | | thread/innodb/fts_parallel_tokenization_thread | YES | YES | | 0 | NULL | | thread/innodb/srv_ts_alter_encrypt_thread | YES | YES | | 0 | NULL | | thread/innodb/parallel_read_thread | YES | YES | | 0 | NULL | | thread/innodb/meb::redo_log_archive_consumer_thread | YES | YES | | 0 | NULL | | thread/myisam/find_all_keys | YES | YES | | 0 | NULL | | thread/mysqlx/acceptor_network | YES | YES | | 0 | NULL | | thread/mysqlx/worker | YES | YES | user | 0 | NULL | | thread/sql/slave_io | YES | YES | singleton | 0 | NULL | | thread/sql/slave_sql | YES | YES | singleton | 0 | NULL | | thread/sql/slave_worker | YES | YES | singleton | 0 | NULL | | thread/sql/replica_monitor | YES | YES | singleton | 0 | NULL | +-----------------------------------------------------+---------+---------+------------+------------+---------------+ 52 rows in set (0.00 sec)
예를 들어 이벤트 스케줄러에 대한 모니터링 로깅을 비활성화 하려면 다음과 같이 실행합니다.
mysql> update performance_schema.setup_threads set enabled='NO', history='NO' where name='thread/sql/event_scheduler';
performance_schema.setup_objects 테이블에는 사용자 스레드에 대한 설정은 저장하지 않으며, 백그라운드 스레드만 설정합니다.
사용자 스레드에 대한 모니터링 설정을 위한 테이블은 setup_actors 으로 별도로 존재합니다.
메모리 크기 조정
수집된 데이터는 performance_schema 엔진을 사용하는 테이블에 저장을 하게 되며, 이 엔진은 데이터를 메모리에 저장합니다. 일부 performance_schema 테이블은 기본으로 크기 조정이 자동으로 되기도 하며, 일부 테이블은 row의 수가 정해져 있습니다.
my.cnf 에서 시스템 변수 설정을 변경함으로 이러한 옵션은 조정할 수 있습니다.
설정에 사용되는 시스템 변수명은 다음과 같은 패턴을 따릅니다.
performance_schema_(object)_[size|instances|calsses|length|handles]
위에서 object는 하나의 컨슈머 이거나 특정 이벤트명등을 나타냅니다.
SHOW VARIABLES LIKE 'performance_schema_%'; +----------------------------------------------------------+-------+ | Variable_name | Value | +----------------------------------------------------------+-------+ | performance_schema_accounts_size | -1 | | performance_schema_digests_size | 10000 | | performance_schema_error_size | 4890 | | performance_schema_events_stages_history_long_size | 10000 | | performance_schema_events_stages_history_size | 10 | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_statements_history_size | 10 | | performance_schema_events_transactions_history_long_size | 10000 | | performance_schema_events_transactions_history_size | 10 | | performance_schema_events_waits_history_long_size | 10000 | | performance_schema_events_waits_history_size | 10 | | performance_schema_hosts_size | -1 | | performance_schema_max_cond_classes | 100 | | performance_schema_max_cond_instances | -1 | | performance_schema_max_digest_length | 1024 | | performance_schema_max_digest_sample_age | 60 | | performance_schema_max_file_classes | 80 | | performance_schema_max_file_handles | 32768 | | performance_schema_max_file_instances | -1 | | performance_schema_max_index_stat | -1 | | performance_schema_max_memory_classes | 450 | | performance_schema_max_metadata_locks | -1 | | performance_schema_max_mutex_classes | 300 | | performance_schema_max_mutex_instances | -1 | | performance_schema_max_prepared_statements_instances | -1 | | performance_schema_max_program_instances | -1 | | performance_schema_max_rwlock_classes | 60 | | performance_schema_max_rwlock_instances | -1 | | performance_schema_max_socket_classes | 10 | | performance_schema_max_socket_instances | -1 | | performance_schema_max_sql_text_length | 1024 | | performance_schema_max_stage_classes | 175 | | performance_schema_max_statement_classes | 218 | | performance_schema_max_statement_stack | 10 | | performance_schema_max_table_handles | -1 | | performance_schema_max_table_instances | -1 | | performance_schema_max_table_lock_stat | -1 | | performance_schema_max_thread_classes | 100 | | performance_schema_max_thread_instances | -1 | | performance_schema_session_connect_attrs_size | 512 | | performance_schema_setup_actors_size | -1 | | performance_schema_setup_objects_size | -1 | | performance_schema_show_processlist | OFF | | performance_schema_users_size | -1 | +----------------------------------------------------------+-------+
예를 들어서 performance_schema_events_stages_history_size 는 performance_schema_events_stages_history 테이블에 저장할 스레드당 단계 수를 정의하는 것입니다.
SQL 문장 저장 길이
MySQL에서 수행한 SQL을 Digest(요약) 하고, 다이제스트 된 SQL과 일반 SQL모두를 performance_schema 에 저장합니다.
Digest(요약) 를 하고 저장할 때 쿼리 길이에 대한 설정이 있으며 관련 시스템 변수는 max_digest_length 와 performance_schema_max_digest_length 입니다.
두 시스템 변수의 기본값은 1024(바이트)로 수행한 SQL이 이보다 더 길다면 digest 된 쿼리는 잘려서 performance_schema에 저장되게 됩니다.
Digest 쿼리는 events_statements_current, events_statements_history, and events_statements_history_long 등의 테이블에서 DIGEST_TEXT 컬럼에서 확인할 수 있습니다.
그렇게 되면 향후에 분석시에 SQL이 잘린 상태로 보이기 때문에 SQL 플랜 확인이나 분석이 어려울 수 있습니다.
그래서 max_digest_length 와 performance_schema_max_digest_length 시스템 변수는 더 큰 값으로 설정이 권장됩니다.
또 다른 연관 시스템 변수로 performance_schema_max_sql_text_length 이 있습니다.
performance_schema 에서 저장되는 SQL 텍스트의 최대 길이를 지정합니다. 이 시스템 변수의 기본값은 1024입니다.
이 시스템 변수의 크기 설정은 다음 테이블의 컬럼에 영향을 미칩니다.
events_statements_current, events_statements_history, and events_statements_history_long 의 SQL_TEXT 컬럼
events_statements_summary_by_digest 테이블의 QUERY_SAMPLE_TEXT
3개 시스템 변수 모두 값을 줄이면 메모리 사용량이 줄어들지만 끝 부분만 다를 경우 더 많은 문을 구별할 수 없게 됩니다.
값을 늘리면 메모리 사용량이 늘어나지만 더 긴 명령문을 구별할 수 있습니다.
그래서 performance_schema_max_sql_text_length 시스템 변수도 더 큰 값으로 설정이 권장됩니다.
기본값
MySQL 시스템 변수 기본값은 버전이 변경됨에 따라 달리질수 있게 됩니다.
MySQL 5.7 버전 부터 performance_schema 는 기본적으로 활성화 되어 있습니다. 다만 많은 인스트루먼트가 비활성화 되어 있습니다.
Global, 스레드, 구문 및 트랜잭션 인스트루먼트만 기본 활성화되어 있습니다.
MySQL 8.0 에서부터는 추가로 메타데이터 잠금 및 메모리 인스트루먼트가 기본으로 활성화 되어 있습니다.
mysql, information_schema, performance_schema 데이터베이스는 계측(데이터 수집)되지 않습니다.
다른 스키마에서는 모든 객체, 스레드 등이 모니터링 및 수집 적재됩니다.
많은 대부분의 인스턴스, 핸들, 셋업 테이블은 자동으로 크기가 조정됩니다.
_history 테이블의 경우 스레드당 마지막 10개의 이벤트가 저장됩니다.
_history_long 테이블의 경우 스레드당 최신 10,000개의 이벤트가 저장됩니다.
performance_schema 의 활용
앞에서는 performance_schema 사용을 하기 위한 여러 가지 설정하는 내용에 대해서 확인해보았습니다.
이제는 일반적이 문제 상황에서의 performance_schema를 사용 및 활용하는 방법에 대해서 확인 해 보도록 하겠습니다.
SQL문 점검
performance_schema는 SQL문의 성능 점검할 수 있는 다양한 인스트루먼트를 제공 및 지원하고 있으며, 그에 따라서 performance_schema를 사용하면 성능상 문제를 일으킨 쿼리와 이유 등을 쉽게 찾을 수 있습니다.
SQL 구문에 관련된 활성화가 필요한 인스트루먼트는 아래와 같습니다.
- statement/sql : SELECT 또는 CREATE TABLE 과 같은 SQL문
- statement/sp : 스토어드 프로시저
- statement/scheduler : 이벤트 스케줄러
- statement/com : quit,KILL,DROP DATABASE,Binlog Dump 와 같은 명령어, 일부는 mysqld 프로세스 자체에서 호출
- statement/abstract : 네가지 명령 클래스 - clone,Query,new_packet, relay_log
일반 SQL 문
performance_schema에는 events_statements_current, _history , _history_long 테이블이 있습니다.
세 테이블 모두 동일한 구조를 가지고 있으며 매우 많은 컬럼을 포함하고 있습니다.
그래서 많은 컬럼에 대한 내용은 공식 문서에서 설명되고 있으므로 참조 해보시기 바랍니다.
최적화가 필요한 구문을 찾으려면 해당 테이블의 컬럼 중 하나를 선택하고 0과 비교를 하는 방법이 있습니다.
예를 들어 좋은 인덱스를 사용하지 않는 모든 쿼리를 찾으려고 할 때 다음과 같이 조회하여 확인할 수 있습니다.
mysql> select thread_id,sql_text ,rows_sent,rows_examined, created_tmp_tables, no_index_used,no_good_index_used from performance_schema.events_statements_history_long where 1=1 and no_index_used>0 or no_good_index_used>0;
위와 같이 events_statements_[] 테이블에서 제공되는 정보를 통해서 성능상 문제의 SQL을 도출할 수 있습니다.
주요하게 사용될 컬럼 중에서 TIMER_WAIT는 이벤트 경과 시간(기간)를 의미하며, 중요한 점은 단위가 picoseconds 입니다.
SYS 스키마 사용
SYS 스키마는 문제가 있는 구문을 찾는데 사용할 수 있는 뷰들을 제공합니다.
예를 들어 statements_with_errors_or_warnings 은 오류와 경고를 반환한 모드 구문에 대한 정보를 나열합니다.
sys스키마에는 쿼리 텍스트 대신에 다이제스트(DIGEST)가 들어가 있습니다.
mysql> select * from sys.statements_with_errors_or_warnings limit 1\G
*************************** 1. row ***************************
query: SELECT ........
db: NULL
exec_count: 134823
errors: 134823
error_pct: 100.0000
warnings: 0
warning_pct: 0.0000
first_seen: 2023-02-20 01:42:10.119284
last_seen: 2023-03-31 16:35:30.625249
digest: 1asdfasf7344535sdfasfasasdfasfsdfasfasdf <!!---
performance_schema에서 DIGEST을 통해서 DIGEST SQL 텍스트를 확인 할 수 있습니다.
최적화가 필요한 구문을 찾는데 도움이 될 수 있는 SYS스키마의 뷰는 다음과 같이 있습니다.
- statements_with_errors_or_warnings
- statements_with_full_table_scans
- statements_with_runtimes_in_95th_percentile
- statements_with_sorting
- statements_with_temp_tables
스토어드 루틴
performance_schema를 사용해서 스토어드 루틴이 어떻게 실행되었는지에 대한 정보를 검색할 수 있습니다.
예를 들어 IF ... ELSE 흐름 제어 분기가 선택되었는지 또는 에러 핸들러가 호출이 되었는지와 같은 정보를 확인할 수 있습니다.
스토어드 루틴의 계측을 활성화하기 위해서는 'statement/sp/%' 패턴의 인스트루먼트를 활성화해야 합니다.
statement/sp/stmt 인스트루먼트는 루틴 내에서 호출되는 구문을 담당하는 반면, 다른 인스트루먼트는 프로시저, 루프 또는 기타 제어 명령을 시작하거나 나가는 것과 같은 이벤트 추적을 담당합니다.
구문 프로파일링
events_stages_[current|history|history_long] 테이블에는 MySQL이 임시 테이블을 생성하거나 업데이트하거나 잠금을 기다리는 동안 소요 시간과 같은 정보가 포함되어 있습니다.
프로파일링을 활성화 하려면 해당 컨슈머와 'stage/%' 패턴의 인스트루먼트를 활성화해야 합니다.
-- 활성화 UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'stage/%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_stages%'; -- 비활성화 UPDATE performance_schema.setup_instruments SET ENABLED = 'NO', TIMED = 'NO' WHERE NAME LIKE 'stage/%'; UPDATE performance_schema.setup_consumers SET ENABLED = 'NO' WHERE NAME LIKE 'events_stages%';
다만 stage/% 인스트루먼트는 그 수가 많으며 기본으로 활성화 된 인스트루먼트는 일부분이며, 대부분은 기본값이 비활성황 입니다.
그러므로 stage/% 패턴으로 활성화하게 되면 많은 인스트루먼트가 모두 활성화 되기 때문에 그에 따른 CPU , 메모리 리소스 사용에 대한 부분이 고려가 되어야 합니다.
mysql> select ENABLED, count(*) cnt from performance_schema.setup_instruments WHERE NAME LIKE 'stage/%' group by ENABLED; +---------+-----+ | ENABLED | cnt | +---------+-----+ | NO | 108 | | YES | 16 | +---------+-----+
위의 조회결과는 8.0.25 버전 기준 stage/% 인스트루먼트의 활성화 와 비활성화 건수를 확인 해본 결과 입니다.
활성화되면 쿼리 실행의 어느 단계에서 오래 걸렸는지에 대한 정보를 추척 및 확인 할 수 있습니다.
프로파일링은 일반적인 서버 단계에서의 데이터를 계측하고 정보를 사용할 있으며 스토리지 엔진에서 사용한 작업 등은 프로파일링을 지원하지 않습니다.
그에 따라서 state/sql/update 계측에서 수집된 정보는 스토리지 엔진 내부에서 일어나는, 스토리지 엔진에 의해서 성능적 이슈가 일어나는 것을 포함 할 수 있습니다.
또한 엔진 레벨의 잠금 또는 다른 경합 문제 때문에 기다리게 되는 정보도 포함된다는 것을 의미 합니다.
Read 와 Write 사용 패턴 및 성능 점점
performance_schema 구문 계측은 실행된 워크로드가 읽기 작업인지, 쓰기 작업인지, 또는 어떤 유형의 작업이 많고 적었는지를 이해하고 파악하는데 많은 도움과 유용합니다.
events_statements 에 있는 event_name 별로 건수 등을 아래와 같이 조회함으로써 데이터베이스 상에서의 워크로드의 비중을 파악할 수 있습니다.
mysql> select event_name,count(event_name) from performance_schema.events_statements_history_long group by event_name; +-----------------------------+-------------------+ | event_name | count(event_name) | +-----------------------------+-------------------+ | statement/sql/select | 7930 | | statement/sql/alter_table | 166 | | statement/sql/create_table | 83 | | statement/sql/show_tables | 135 | | statement/sql/show_fields | 366 | | statement/sql/update | 507 | | statement/sql/insert | 152 | | statement/sql/delete | 64 | | statement/com/Quit | 94 | | statement/sql/set_option | 288 | | statement/com/Init DB | 83 | | statement/sql/show_keys | 102 | | statement/sql/delete_multi | 28 | | statement/sql/error | 1 | | statement/sql/show_warnings | 1 | +-----------------------------+-------------------+
메타데이터 잠금
메타데이터 잠금은 데이터베이스 객체 정의가 변경되지 않도록 보호하는 데 사용 합니다.
메타데이터 락 계측을 활성화 하려면 wait/lock/metadata/sql/mdl 인스트루먼트를 활성화 해야 합니다.
MySQL 5.7 에서는 별도로 활성화가 필요하며, MySQL 8.0버전에서는 기본값이 활성화 입니다.
select , update 등의 모든 SQL 문에 대해 공유 메타데이터 잠금이 설정 됩니다.
공유 메타데이터 잠금이 요구되는 다른 SQL명령문에는 영향을 미치지 않습니다.
그러나 ALTER TABLE 또는 CREATE INDEX 와 같이 데이터베이스 객체 정의를 변경하는 명령문들은 잠금이 해제 될 때까지 시작하는 것을 막아줍니다.(대기하게 됨)
메타데이터 잠금으로 인한 대기는 현재 메타데이터 잠금을 획득하고 있는 선행 세션의 트랜잭션이 완료 될때 까지 유지 됩니다. 따라서 여러 개의 쿼리문을 하나의 트랜잭션으로 묶어서 사용하는 경우 문제를 찾고 해결하기가 더욱 어려워집니다.
잠금을 기다리는 구문은 일반적으로 명확합니다. DDL 명령문은 암시적으로 트랜잭션을 커밋하므로 그것은 새로운 트랜잭션 구문이 되게 되며 프로세스 목록의 상태에서 "Waiting for a metadata lock" 으로 확인 되게 됩니다.
performance_schema의 metadata_locks 테이블은 현재 서로 다른 스레드에서 설정한 잠금에 대한 정보와 잠금을 기다리고 있는 잠금 요청에 대한 정보를 가지고 있습니다.
metadata_locks 데이터를 통해서 어떤 스레드에 의해서 DDL이 대기하고 있는지 쉽게 식별할 수 있습니다.
이번 포스팅에서는 performance_schema 에 대한 전반적으로 내용을 확인 해보았으며, 여기서 글을 마무리 하겠습니다.
긴글 읽어주셔서 감사합니다.
Reference
Reference Book
• MySQL 성능 최적화
Reference URL
• mysql.com/performance-schema-setup-tables
• mysql.com/sys-schema
• mysql.com/sys-ps-setup-enable-instrument
• mysql.com/performance-schema-setup-objects-table
• mysql.com/performance-schema-stage-tables
• mysql.com/performance-schema-startup-configuration
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
mariadb도 동일하겠죠?
안녕하세요
블로그 약간 문제가 있어서, 코멘트가 늦었습니다.
저는 MySQL 위주로 하고 있고 MariaDB는 간접적으로 하다보니 정확히 동일하다 라고 말하기는 어려울것 같습니다.
MySQL5.7 버전과 유사성을 가진 MariaDB에서는 유사할것으로 예상은 되나 버전의 지날수록 차이가 있을수는 있다고 예상은 합니다.
감사합니다.