MySQL → mysqld_exporter → Prometheus → Grafana

-- 현재 커넥션 상태
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW VARIABLES LIKE 'max_connections';

# PromQL - 커넥션 사용률
mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100

-- 쿼리 타입별 통계
SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW GLOBAL STATUS LIKE 'Com_delete';

# PromQL - QPS
rate(mysql_global_status_queries[5m])

# Read/Write 비율
sum(rate(mysql_global_status_commands_total{command=~"select|show"}[5m])) /
sum(rate(mysql_global_status_commands_total{command=~"insert|update|delete"}[5m]))

-- Buffer Pool 상태
SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

# PromQL - Buffer Pool Hit Ratio
(mysql_global_status_innodb_buffer_pool_read_requests -
 mysql_global_status_innodb_buffer_pool_reads) /
mysql_global_status_innodb_buffer_pool_read_requests * 100

SHOW GLOBAL STATUS LIKE 'Innodb_rows%';

# PromQL - 초당 Row 작업량
rate(mysql_global_status_innodb_rows_read[5m])
rate(mysql_global_status_innodb_rows_inserted[5m])

SHOW GLOBAL STATUS LIKE 'Innodb_data%';
SHOW GLOBAL STATUS LIKE 'Innodb_os_log%';

# PromQL - IOPS
rate(mysql_global_status_innodb_data_reads[5m]) +
rate(mysql_global_status_innodb_data_writes[5m])

-- 락 대기 상황
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 데드락 정보
SHOW ENGINE INNODB STATUS\G -- LATEST DETECTED DEADLOCK 섹션 확인

# PromQL - 락 대기 발생률
rate(mysql_global_status_innodb_row_lock_waits[5m])

# 평균 락 대기 시간
mysql_global_status_innodb_row_lock_time / mysql_global_status_innodb_row_lock_waits

-- Replica에서 확인
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master 확인

# PromQL - 복제 지연
mysql_slave_status_seconds_behind_master

SHOW GLOBAL STATUS LIKE 'Slow_queries';

# PromQL - 슬로우 쿼리 발생률
rate(mysql_global_status_slow_queries[5m])

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

# PromQL - 디스크 임시 테이블 비율
mysql_global_status_created_tmp_disk_tables /
mysql_global_status_created_tmp_tables * 100

-- 읽기 부하 시 확인할 지표
SHOW GLOBAL STATUS LIKE 'Handler_read%';

-- 쓰기 부하 시 확인할 지표
SHOW ENGINE INNODB STATUS\G  -- LOG 섹션 확인
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';

# PromQL - Log Waits 발생률
rate(mysql_global_status_innodb_log_waits[5m])

-- 락 경합 확인 (MySQL 8.0+)
SELECT * FROM performance_schema.data_locks WHERE LOCK_STATUS = 'WAITING';

-- 현재 실행 중인 쿼리
SELECT
    id,
    user,
    host,
    db,
    time,
    state,
    LEFT(info, 100) as query
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;

-- 쿼리 실행 시간 분포 (Performance Schema)
SELECT
    schema_name,
    digest_text,
    count_star as exec_count,
    avg_timer_wait / 1000000000 as avg_latency_ms,
    sum_rows_examined / count_star as avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- 알고리즘 명시적 지정
ALTER TABLE large_table ADD COLUMN new_col VARCHAR(100), ALGORITHM=INSTANT;
ALTER TABLE large_table ADD INDEX idx_col(column), ALGORITHM=INPLACE, LOCK=NONE;

-- MySQL 8.0.12+ INSTANT 지원 (테이블 끝에 추가 시)
ALTER TABLE large_table ADD COLUMN new_col VARCHAR(100);  -- INSTANT

-- 기본값 있는 NOT NULL (INPLACE)
ALTER TABLE large_table ADD COLUMN new_col VARCHAR(100) NOT NULL DEFAULT 'value';

-- 특정 위치에 추가 (COPY - 느림!)
ALTER TABLE large_table ADD COLUMN new_col VARCHAR(100) AFTER existing_col;

-- Online DDL로 인덱스 생성 (INPLACE)
ALTER TABLE large_table ADD INDEX idx_col(column), ALGORITHM=INPLACE, LOCK=NONE;

-- 또는
CREATE INDEX idx_col ON large_table(column) LOCK=NONE;

-- DDL 진행 상황 확인 (MySQL 8.0+ Performance Schema)
SELECT
    EVENT_NAME,
    WORK_COMPLETED,
    WORK_ESTIMATED,
    ROUND(WORK_COMPLETED / WORK_ESTIMATED * 100, 2) as progress_pct
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter%';

-- 또는 SHOW PROCESSLIST에서 확인
SHOW PROCESSLIST\G
-- State: "altering table", "copy to tmp table" 등 확인

pt-online-schema-change \
  --alter "ADD COLUMN new_col VARCHAR(100)" \
  --execute \
  D=mydb,t=large_table

-- pt-osc 진행 상황 확인
SHOW PROCESSLIST;
-- "_large_table_new" 테이블에 INSERT 진행 확인

-- 트리거 확인
SHOW TRIGGERS LIKE 'large_table'\G

# prometheus-rules.yml 추가
groups:
  - name: mysql-ddl
    rules:
      - alert: MySQLLongRunningDDL
        expr: |
          mysql_info_schema_processlist_seconds{command="Query",state=~".*alter.*|.*copy.*"} > 300
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "DDL 작업 5분 이상 진행 중"

      - alert: MySQLHighMetadataLockWait
        expr: |
          increase(mysql_global_status_table_locks_waited[5m]) > 100
        labels:
          severity: warning
        annotations:
          summary: "메타데이터 락 대기 급증"

-- 1. 테이블 크기 확인
SELECT
    table_name,
    ROUND(data_length / 1024 / 1024, 2) as data_mb,
    ROUND(index_length / 1024 / 1024, 2) as index_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_name = 'large_table';

-- 2. 메타데이터 락 대기 중인 세션 확인 (MySQL 8.0+)
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'large_table';

-- 3. 락 타임아웃 설정
SET SESSION lock_wait_timeout = 5;

-- 4. DDL 전 장시간 트랜잭션 확인
SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as duration_sec,
    trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

┌─────────────────────────────────────────────────────────┐
│  Overview: QPS, 커넥션, Buffer Pool Hit Ratio           │
├─────────────────────────────────────────────────────────┤
│  Connections          │  Query Rate (CRUD)             │
├───────────────────────┼────────────────────────────────┤
│  Buffer Pool Usage    │  InnoDB Row Operations         │
├───────────────────────┼────────────────────────────────┤
│  InnoDB I/O          │  Lock Waits / Deadlocks        │
├─────────────────────────────────────────────────────────┤
│  Replication Status   │  Slow Queries                  │
└─────────────────────────────────────────────────────────┘

# docker-compose.yml
services:
  mysqld_exporter:
    image: prom/mysqld-exporter
    environment:
      DATA_SOURCE_NAME: "exporter:password@(mysql:3306)/"
    ports:
      - "9104:9104"
    command:
      - "--collect.info_schema.innodb_metrics"
      - "--collect.info_schema.processlist"
      - "--collect.info_schema.tables"
      - "--collect.slave_status"

CREATE USER 'exporter'@'%' IDENTIFIED BY 'password';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
FLUSH PRIVILEGES;

# prometheus-rules.yml
groups:
  - name: mysql
    rules:
      - alert: MySQLHighConnections
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MySQL 커넥션 사용률 80% 초과"

      - alert: MySQLBufferPoolLow
        expr: |
          (mysql_global_status_innodb_buffer_pool_read_requests -
           mysql_global_status_innodb_buffer_pool_reads) /
          mysql_global_status_innodb_buffer_pool_read_requests < 0.95
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "MySQL Buffer Pool Hit Ratio 95% 미만"

      - alert: MySQLReplicationLag
        expr: mysql_slave_status_seconds_behind_master > 30
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MySQL 복제 지연 30초 초과"

      - alert: MySQLDeadlock
        expr: increase(mysql_global_status_innodb_deadlocks[5m]) > 0
        labels:
          severity: warning
        annotations:
          summary: "MySQL 데드락 발생"