핵심 작동 원리
MySQL의
INFORMATION_SCHEMA.PROCESSLIST 뷰는 현재 실행 중인 모든 클라이언트 연결과 해당 스레드의 실시간 상태를 제공합니다. 이 뷰를 활용하면 완료되지 않은 쿼리도 즉시 식별할 수 있어, 전통적인
slow_query_log가 가지는 지연 문제를 극복할 수 있습니다. 특히
TIME 컬럼(초 단위)을 기준으로 임계값을 설정해 지속 시간이 긴 쿼리를 실시간으로 추적하고, 성능 병목 구간을 초단위로 진단할 수 있습니다.
다음은 프로덕션 환경에 최적화된 모니터링 쿼리입니다:
SELECT
ID AS session_id,
USER AS initiator,
HOST AS client_address,
DB AS target_schema,
COMMAND AS activity_type,
TIME AS runtime_seconds,
STATE AS current_phase,
SUBSTRING(INFO, 1, 300) AS truncated_sql
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE
TIME >= 5
AND COMMAND = 'Query'
AND INFO IS NOT NULL
AND INFO NOT REGEXP '^(SHOW|EXPLAIN|SELECT.*PROCESSLIST)'
ORDER BY runtime_seconds DESC
LIMIT 20;
주요 필드 해설
runtime_seconds: 쿼리가 현재까지 실행된 시간. 성능 기준선 설정의 핵심 지표.
truncated_sql: 실제 실행 중인 SQL 문장. 인덱스 부재, 조인 비효율 등 원인 분석의 출발점.
current_phase: MySQL 내부 처리 단계(Sorting result, Creating sort index, Waiting for handler commit 등). I/O 대기 또는 메모리 부족 여부를 간접적으로 파악할 수 있음.
activity_type: Query는 일반 SQL 실행, Execute는 준비된 문장 실행을 의미하며, 실행 방식에 따라 최적화 전략이 달라질 수 있음.
자동화 감시 구현 사례
1. Bash 기반 주기적 수집 스크립트
#!/bin/bash
LOG_PATH="/var/log/mysql/slow_monitor.log"
DB_USER="monitor"
DB_PASS="s3cur3_p@ss"
CHECK_INTERVAL=25
THRESHOLD_SEC=3
while true; do
timestamp=$(date '+%Y-%m-%d %H:%M:%S')
mysql -u"$DB_USER" -p"$DB_PASS" -Nse "
SELECT CONCAT('$timestamp', '\t',
ID, '\t', USER, '\t', HOST, '\t',
TIME, '\t', SUBSTRING(INFO, 1, 120))
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE TIME >= $THRESHOLD_SEC
AND COMMAND = 'Query'
AND INFO NOT LIKE '%PROCESSLIST%'
AND INFO NOT REGEXP '^(SHOW|EXPLAIN)'
ORDER BY TIME DESC
LIMIT 10
" 2>/dev/null | tee -a "$LOG_PATH"
sleep "$CHECK_INTERVAL"
done
2. NestJS + TypeORM 기반 서비스 컴포넌트
import { Injectable, Logger, OnModuleInit } from '@nestjs/common';
import { InjectConnection } from '@nestjs/typeorm';
import { Connection } from 'typeorm';
import { Cron, CronExpression } from '@nestjs/schedule';
@Injectable()
export class LiveQueryInspector implements OnModuleInit {
private readonly log = new Logger(LiveQueryInspector.name);
private dynamicThreshold = 5; // 기본 임계값 (초)
constructor(@InjectConnection() private readonly db: Connection) {}
onModuleInit() {
this.adjustThresholdByLoad();
}
@Cron(CronExpression.EVERY_20_SECONDS)
async scanActiveQueries() {
try {
const candidates = await this.db.query(
`SELECT ID, USER, HOST, DB, TIME, STATE, INFO
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE TIME >= ?
AND COMMAND = 'Query'
AND INFO IS NOT NULL
AND INFO NOT REGEXP '^(SHOW|EXPLAIN)'`,
[this.dynamicThreshold],
);
if (candidates.length > 0) {
this.log.warn(`⚠️ ${candidates.length}개의 장기 실행 쿼리 감지`);
candidates.forEach((q) => {
this.log.error(
`[SID:${q.ID}] ${q.USER}@${q.HOST} | ${q.TIME}s | ${q.STATE} | ${q.INFO?.substring(0, 80)}...`,
);
});
// 외부 알림 연동 예시 (Slack/Prometheus 등)
this.pushToAlertSystem(candidates);
}
} catch (err) {
this.log.error('쿼리 스캔 실패:', err.message);
}
}
private adjustThresholdByLoad() {
// CPU/메모리 사용률 기반 동적 임계값 계산 로직
const avgLoad = require('os').loadavg()[0];
this.dynamicThreshold = avgLoad > 4 ? 2 : avgLoad > 2 ? 3 : 5;
}
private pushToAlertSystem(queries: any[]) {
// Prometheus metrics 업데이트, Slack 웹훅 전송 등
}
}
운영 환경 적용 가이드
- 권한 설정: 모니터링 계정에는
PROCESS 권한이 반드시 필요합니다.
GRANT PROCESS ON *.* TO 'probe_user'@'localhost'; FLUSH PRIVILEGES;
- 성능 영향 최소화: 쿼리 실행 주기를 20–60초로 유지하고,
LIMIT 절로 결과 수 제한
- 오류 방지 필터링:
INFO NOT REGEXP '^(SHOW|EXPLAIN)'로 자체 모니터링 쿼리 배제
- 확장 분석 통합:
performance_schema.events_statements_current와 조인해 쿼리 메모리/디스크 사용량 연계 분석 가능
진단 시 주의 포인트
상태 값 (STATE) | 의미 | 대응 방안 |
Waiting for table metadata lock | DML 트랜잭션이 오래 실행 중일 때 발생 | INFORMATION_SCHEMA.INNODB_TRX 확인 후 장기 트랜잭션 종료 |
Copying to tmp table | 임시 테이블 생성 → 정렬/그룹화 과정에서 메모리 부족 | tmp_table_size, max_heap_table_size 증가 또는 쿼리 리팩토링 |
Sending data | 결과 집합 전송 중 — 대량 데이터 반환 또는 네트워크 지연 가능성 | 페이지네이션 적용, 불필요한 SELECT * 제거 |
기존 느린 쿼리 로그와의 비교
- 실시간성:
PROCESSLIST는 활성 쿼리만 대상으로 하며, 완료 여부와 무관하게 즉시 반영됩니다.
- 자원 소비: 로그 파일 쓰기 I/O 없이 메모리 기반 조회로, 고부하 시에도 안정적입니다.
- 상황 인식력:
STATE 값을 통해 ‘왜 느린가’에 대한 맥락 정보를 제공합니다.