MySQL 실시간 성능 감시: PROCESSLIST 기반 느린 쿼리 탐지 및 자동 경보 시스템

핵심 작동 원리

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 lockDML 트랜잭션이 오래 실행 중일 때 발생INFORMATION_SCHEMA.INNODB_TRX 확인 후 장기 트랜잭션 종료
Copying to tmp table임시 테이블 생성 → 정렬/그룹화 과정에서 메모리 부족tmp_table_size, max_heap_table_size 증가 또는 쿼리 리팩토링
Sending data결과 집합 전송 중 — 대량 데이터 반환 또는 네트워크 지연 가능성페이지네이션 적용, 불필요한 SELECT * 제거

기존 느린 쿼리 로그와의 비교

  • 실시간성: PROCESSLIST는 활성 쿼리만 대상으로 하며, 완료 여부와 무관하게 즉시 반영됩니다.
  • 자원 소비: 로그 파일 쓰기 I/O 없이 메모리 기반 조회로, 고부하 시에도 안정적입니다.
  • 상황 인식력: STATE 값을 통해 ‘왜 느린가’에 대한 맥락 정보를 제공합니다.

태그: MySQL processlist performance-monitoring nestjs typeorm

10월 22일 10:55에 게시됨