모니터링과 성능 튜닝 — Performance Schema와 sys 스키마

-- Performance Schema 활성화 확인 (기본 ON)
SHOW VARIABLES LIKE 'performance_schema';

수집하는 정보:
- 쿼리 실행 통계 (실행 횟수, 소요 시간, 행 수)
- 대기 이벤트 (락 대기, I/O 대기, 뮤텍스 대기)
- 메모리 사용량
- 스레드 상태
- 파일 I/O 통계
- 테이블 I/O 통계

-- 가장 느린 쿼리 TOP 10
SELECT
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
    ROUND(SUM_TIMER_WAIT / 1e9, 2) AS total_ms,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- 실행 횟수가 많은 쿼리 TOP 10
SELECT
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1e9, 2) AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY COUNT_STAR DESC
LIMIT 10;

-- 가장 많이 대기한 이벤트
SELECT
    EVENT_NAME,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e9, 2) AS total_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 가장 많이 접근되는 테이블
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY COUNT_READ + COUNT_WRITE DESC
LIMIT 10;

-- 컴포넌트별 메모리 사용량
SELECT
    EVENT_NAME,
    CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS current_mb
FROM performance_schema.memory_summary_global_by_event_name
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 10;

-- 가장 비용이 높은 쿼리
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- 풀 테이블 스캔이 발생하는 쿼리
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- 임시 테이블을 사용하는 쿼리
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;

-- 사용되지 않는 인덱스
SELECT * FROM sys.schema_unused_indexes;

-- 중복 인덱스
SELECT * FROM sys.schema_redundant_indexes;

-- 테이블별 통계
SELECT * FROM sys.schema_table_statistics;

-- 사용자별 연결 통계
SELECT * FROM sys.user_summary;

-- 현재 실행 중인 쿼리
SELECT * FROM sys.processlist WHERE command != 'Sleep';

-- I/O 가장 많은 파일
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 10;

-- 메모리 사용량 요약
SELECT * FROM sys.memory_global_total;

-- 서버 시작 이후 한 번도 사용되지 않은 인덱스
SELECT
    object_schema AS db_name,
    object_name AS table_name,
    index_name
FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema');

-- 다른 인덱스에 포함되는 중복 인덱스
SELECT * FROM sys.schema_redundant_indexes
WHERE table_schema NOT IN ('mysql', 'sys');

예: INDEX(a)와 INDEX(a, b)가 있으면 INDEX(a)는 중복

SHOW ENGINE INNODB STATUS\G

Buffer pool hit rate: 999 / 1000  ← 히트율 99.9%
Modified db pages: 1234           ← Dirty Page 수

Number of rows inserted 12345, updated 6789, deleted 1234, read 99999

-- 뮤텍스 대기가 많으면 내부 경합 문제
Mutex spin waits 12345, rounds 67890

-- 연결 관련
SHOW STATUS LIKE 'Threads%';
-- Threads_connected: 현재 연결 수
-- Threads_running: 현재 실행 중인 스레드 수
-- Threads_created: 생성된 총 스레드 수

-- Buffer Pool 관련
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- Innodb_buffer_pool_read_requests: 논리적 읽기 요청 수
-- Innodb_buffer_pool_reads: 디스크에서 읽은 횟수 (미스)

-- 임시 테이블 관련
SHOW STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables: 메모리 임시 테이블 수
-- Created_tmp_disk_tables: 디스크 임시 테이블 수 (높으면 문제)

-- 전용 DB 서버: 전체 메모리의 50~80%
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;  -- 8GB

-- 여러 인스턴스로 분리 (경합 감소)
-- innodb_buffer_pool_instances = 8  (my.cnf에서 설정)

-- 정렬 버퍼 (세션별)
SET GLOBAL sort_buffer_size = 4 * 1024 * 1024;  -- 4MB (기본 256KB)

-- 조인 버퍼 (세션별, 풀 스캔 조인 시 사용)
SET GLOBAL join_buffer_size = 4 * 1024 * 1024;  -- 4MB (기본 256KB)

총 메모리 = innodb_buffer_pool_size
           + max_connections × (sort_buffer_size + join_buffer_size + ...)
           + 기타 전역 버퍼

-- Redo Log 크기 (MySQL 8.0.30+에서 동적 조절)
SET GLOBAL innodb_redo_log_capacity = 2 * 1024 * 1024 * 1024;  -- 2GB

-- Redo Log 플러시 빈도
-- 1: 매 커밋마다 (안전, 기본)
-- 2: 매 초마다 OS 버퍼에 (약간 위험)
-- 0: 매 초마다 (위험)
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- 최대 연결 수
SHOW VARIABLES LIKE 'max_connections';  -- 기본 151

-- 현재 연결 수와 최대 동시 연결 수 확인
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

max_connections을 무작정 올리면 안 됩니다.

각 연결이 사용하는 메모리:
- 스레드 스택: ~256KB
- sort_buffer, join_buffer: 쿼리 시 할당
- net_buffer: ~16KB

500 연결 × 1MB(추정) = 500MB 추가 메모리

대신:
1. 애플리케이션 커넥션 풀 크기를 적절히 설정 (보통 10~30)
2. 서버 수 × 풀 크기가 max_connections 이내인지 확인
3. Threads_running이 CPU 코어 수의 2~3배를 넘지 않도록 관리

# mysqld_exporter 설치 (Prometheus 메트릭 수집)
# MySQL 메트릭을 Prometheus 형식으로 노출
mysqld_exporter --config.my-cnf=".my.cnf"