Performance 스키마와 sys 스키마 3

Performance 스키마와 sys 스키마·2에서 본 Performance 스키마(Performance Schema)의 테이블은 정보가 많지만 그대로 읽기 어렵다. sys 스키마(sys schema)는 그 테이블을 목적별 뷰(view)로 묶어 "가장 오래 걸린 쿼리", "안 쓰는 인덱스", "누가 누구를 막고 있나" 같은 질문에 바로 답하게 해 준다.

sys 스키마

sys 스키마는 Performance 스키마를 해석하도록 돕는 뷰·스토어드 프로시저(stored procedure)·스토어드 함수(stored function) 묶음이다. MySQL 8.4는 데이터 디렉터리(data directory)를 처음 만들 때 sys 스키마를 기본으로 설치한다. 뷰는 대부분 두 벌이다. statement_analysis처럼 이름이 그냥인 뷰는 피코초 같은 시간 값과 바이트 수를 사람이 읽기 쉬운 단위로 바꿔 보여 준다. x$statement_analysis처럼 x$가 붙은 뷰는 가공하지 않은 숫자를 보여 주므로 정렬하거나 다른 도구로 넘길 때 쓴다. sys 스키마는 Performance 스키마의 값을 읽을 뿐이므로 Performance 스키마가 꺼져 있으면 쓸모가 없다.

느린 쿼리 찾기

SELECT query, exec_count, total_latency, avg_latency, rows_examined_avg
FROM sys.statement_analysis
LIMIT 10;

sys.statement_analysis는 다이제스트(digest), 즉 상수를 지운 쿼리 모양별로 실행 횟수·총 시간·평균 시간·검사 행 수·풀 테이블 스캔(full table scan) 횟수·임시 테이블(temporary table) 수를 보여 준다. 기본 정렬이 총 실행 시간 내림차순이라, 맨 위가 서버 시간을 가장 많이 쓴 쿼리 모양이다. 하루 수백만 번 도는 0.05초짜리 쿼리가 10초짜리 한 건보다 더 큰 부담일 수 있는데, 이런 쿼리는 기준 시간(long_query_time)보다 짧아 슬로우 쿼리 로그(slow query log)에 남지 않고 여기서 드러난다(MySQL 로그 파일 참고).

sys.statements_with_full_table_scans는 인덱스 없이 테이블 전체를 읽은 쿼리 모양만 모아 준다. 여기서 찾은 쿼리는 EXPLAIN으로 실행 계획(execution plan)을 확인한다(실행 계획 확인 참고).

인덱스 정리

인덱스는 쓰기마다 함께 고쳐야 하므로, 안 쓰는 인덱스는 저장 공간과 쓰기 비용만 먹는다.

• sys.schema_unused_indexes: 서버가 시작된 뒤로 한 번도 쓰이지 않은 인덱스 목록이다. 통계가 서버 시작 시점부터 쌓인다는 점이 중요하다. 재시작 직후나 월말 배치처럼 가끔 도는 쿼리가 아직 안 돈 시점에는 필요한 인덱스도 여기 나온다. 매뉴얼도 서버가 대표적인 작업량을 충분히 처리한 뒤에 보라고 한다.
• sys.schema_redundant_indexes: 다른 인덱스와 겹쳐 필요 없는 인덱스 목록이다. (user_id) 인덱스는 (user_id, created_at) 인덱스의 맨 앞 칼럼과 같으므로, 좌측 접두어 규칙(leftmost prefix rule)에 따라 긴 쪽이 짧은 쪽의 일을 대신할 수 있다. 뷰가 삭제문(sql_drop_index)까지 만들어 준다.

바로 지우기보다는 ALTER TABLE t ALTER INDEX idx INVISIBLE로 먼저 숨겨 보는 편이 안전하다. 인비저블 인덱스(invisible index)는 옵티마이저(optimizer)가 쓰지 않도록 숨긴 인덱스다. 쓰기 때는 계속 갱신되므로 문제가 생기면 VISIBLE로 바로 되돌릴 수 있어, 큰 테이블에서 인덱스를 지웠다 다시 만드는 것보다 훨씬 싸다.

잠금 대기 찾기

"쿼리가 멈췄다"는 신고의 상당수는 누군가의 잠금을 기다리는 상황이다. 누가 막고 있는지를 두 뷰로 찾는다. 메타데이터 락(metadata lock)은 테이블 구조를 지키는 잠금으로 스토리지 엔진(storage engine)이 아니라 MySQL 서버가 걸고, InnoDB 행 잠금은 트랜잭션(transaction)이 고치는 행에 거는 잠금이다.

뷰	보는 잠금	바탕 테이블
sys.schema_table_lock_waits	메타데이터 락	metadata_locks
sys.innodb_lock_waits	InnoDB 행 잠금	data_locks·data_lock_waits·innodb_trx

• 메타데이터 락 대기: 긴 트랜잭션이 테이블을 쥔 채 ALTER TABLE이 오면 ALTER가 기다린다. 그 잠금 요청이 대기열 앞에 서 있어서 뒤에 온 SELECT도 잠금을 받지 못하고 줄줄이 막힌다(MySQL 엔진의 잠금 참고). schema_table_lock_waits가 기다리는 쪽(waiting_query)과 막는 쪽(blocking_pid)을 보여 준다. 메타데이터 락을 재는 인스트루먼트(instrument), 즉 측정 지점은 8.4에서 기본으로 켜져 있다.
• InnoDB 행 잠금 대기: innodb_lock_waits는 행 잠금을 기다리는 트랜잭션과 막는 트랜잭션, 잠긴 테이블·인덱스, 기다린 시간을 보여 준다(InnoDB 잠금 참고). 막는 세션이 쿼리를 끝내고 트랜잭션만 열어 둔 채 쉬고 있으면 blocking_query가 NULL로 나온다. 커밋(commit)을 잊은 세션일 가능성이 크다.

두 뷰 모두 막는 쪽을 끊는 KILL 문(sql_kill_blocking_query·sql_kill_blocking_connection)을 만들어 준다. 끊으면 그 트랜잭션은 롤백(rollback)되고, 기다리는 ALTER 자신이 막는 쪽으로 보이기도 하므로 끊기 전에 확인한다. 조회 SQL과 절차는 온라인 DDL 4에 정리했다.

ALTER TABLE 진행률 보기

큰 테이블의 ALTER TABLE이 얼마나 남았는지도 Performance 스키마의 단계(stage) 이벤트로 본다. 진행 정보를 주는 stage/innodb/alter% 인스트루먼트는 8.4에서 기본으로 켜져 있지만, 결과를 쌓는 events_stages_% 컨슈머(consumer)는 기본으로 꺼져 있어 setup_consumers에서 먼저 켜야 한다. 그다음 ALTER가 도는 동안 events_stages_current의 WORK_COMPLETED를 WORK_ESTIMATED로 나눈 값을 대략의 진행률로 본다. 켜는 문장과 조회 쿼리, 값을 읽는 법은 온라인 DDL 4에 모아 두었다.

면접 포인트

• "가장 부담이 큰 쿼리를 어떻게 찾나요?" → sys.statement_analysis를 총 시간순으로. 짧지만 자주 도는 쿼리는 슬로우 쿼리 로그보다 여기서 잘 보인다. 찾은 뒤 EXPLAIN.
• "안 쓰는 인덱스는?" → sys.schema_unused_indexes. 서버 시작 이후 통계라 충분히 돈 뒤 보고, 지우기 전에 INVISIBLE로 시험.
• "쿼리가 멈췄어요" → 메타데이터 락이면 schema_table_lock_waits, 행 잠금이면 innodb_lock_waits로 막는 세션을 찾는다. blocking_query가 NULL이면 커밋 안 한 유휴 트랜잭션 의심.