DevOps 도구 및 베스트 프랙티스 마스터 - DevOps 지식 허브 DevOps 도구 및 베스트 프랙티스 마스터 - DevOps 지식 허브

MySQL 성능 모니터링: SHOW STATUS와 SHOW PROCESSLIST 활용하기

두 가지 필수 명령어인 SHOW STATUS와 SHOW PROCESSLIST를 사용하여 실시간 MySQL 성능 모니터링을 마스터하세요. 글로벌 성능 카운터 해석, 활성 연결 식별, 장기 실행 또는 차단 쿼리 발견, 리소스 병목 현상 즉시 진단 방법을 배웁니다. 이 가이드는 스레드 활동, InnoDB 메트릭 분석 및 KILL과 같은 대상 작업 실행을 위한 실용적인 예제를 제공합니다.

MySQL 성능 모니터링: SHOW STATUS와 SHOW PROCESSLIST 활용하기

MySQL 기반 애플리케이션이 느려질 때, 대시보드를 열기 전에 가장 빠르게 실행할 수 있는 내장 검사는 SHOW STATUS와 SHOW PROCESSLIST입니다. 이 명령어들만으로 모든 문제를 설명할 수는 없지만, 서버가 무엇을 해왔는지와 지금 무엇이 실행 중인지라는 두 가지 실용적인 질문에 답해줍니다.

SHOW STATUS로 실시간 시스템 상태 이해하기

SHOW STATUS 명령어는 종종 SHOW GLOBAL STATUS 또는 SHOW SESSION STATUS와 동의어로 사용되며, 마지막 재시작 이후 또는 현재 세션이 시작된 이후의 서버 활동에 대한 풍부한 정보를 제공합니다. 이러한 상태 변수는 카운터 역할을 하며, 연결 시도부터 캐시 효율성 및 잠금 대기까지 모든 것을 추적합니다.

글로벌 vs 세션 상태

이 명령어를 실행할 때 범위를 이해하는 것이 중요합니다:

  • SHOW GLOBAL STATUS: MySQL 서버 인스턴스가 시작된 이후 누적된 카운터를 표시합니다. 이는 전체 서버 상태와 장기적인 추세에 대한 조감도를 제공합니다.
  • SHOW SESSION STATUS: 현재 사용 중인 연결(세션)에만 특화된 카운터를 표시합니다. 이는 특정 트랜잭션의 성능 영향을 격리하는 데 유용합니다.

SHOW GLOBAL STATUS의 주요 성능 지표(KPI)

SHOW GLOBAL STATUS는 수백 개의 변수를 반환하지만, 초기 성능 분류를 위해 몇 가지가 중요합니다. 일반적으로 출력을 grep으로 파이프하거나 WHERE 절을 사용하여 관련성 있게 필터링합니다.

1. 연결 및 스레드 모니터링

이 변수들은 연결 부하를 이해하는 데 도움을 줍니다:

변수 이름 설명
Threads_connected 현재 열려 있는 연결(클라이언트) 수입니다.
Threads_running 현재 쿼리를 실행 중인 활성 스레드 수입니다(일반적으로 낮아야 함).
Max_used_connections 서버 시작 이후 최대 동시 연결 수입니다. max_connections 크기 조정에 유용합니다.

예시: 활성 연결 확인:

SHOW GLOBAL STATUS LIKE 'Threads_%';

2. 쿼리 캐싱 및 효율성

레거시 쿼리 캐시(이전 MySQL 버전에서 사용 가능, 최신 버전에서는 더 이상 사용되지 않음/제거됨)를 사용하는 경우 다음 메트릭이 필수적입니다:

  • Qcache_hits: 캐시에서 쿼리가 제공된 횟수입니다.
  • Qcache_lowmem_prunes: 낮은 메모리로 인해 캐시가 이전 항목을 제거하게 만든 쿼리 수입니다.

3. InnoDB 엔진 메트릭(최신 MySQL에서 가장 중요)

InnoDB 스토리지 엔진을 사용하는 최신 배포의 경우 버퍼 풀 활동을 모니터링하세요:

  • Innodb_buffer_pool_read_requests: 총 읽기 요청 수입니다.
  • Innodb_buffer_pool_reads: 디스크에서의 물리적 읽기 수입니다(물리적 읽기 대 요청 비율이 높으면 버퍼 풀 크기를 늘려야 함을 나타냄).

실용적인 팁: 버퍼 풀 효율성을 빠르게 평가하려면 적중률을 계산하세요: (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

4. 임시 테이블 및 정렬

이는 MySQL이 수행하는 내부 처리량을 나타냅니다:

  • Created_tmp_tables: 생성된 메모리 내 임시 테이블 수입니다.
  • Created_tmp_disk_tables: 디스크에 기록해야 했던 임시 테이블 수입니다(느림).

Created_tmp_disk_tables가 높으면 tmp_table_size 또는 max_heap_table_size를 늘려야 할 수 있습니다.

SHOW PROCESSLIST로 활성 워크로드 진단하기

SHOW STATUS가 무엇이 발생했는지 알려주는 반면, SHOW PROCESSLIST는 지금 무엇이 발생하고 있는지 알려줍니다. 서버 내에서 현재 실행 중인 스레드에 대한 정보를 표시하여 장기 실행 또는 차단된 쿼리를 식별할 수 있습니다.

프로세스 목록 구조

명령어는 여러 열을 출력하며, 각 열은 활성 연결에 대한 컨텍스트를 제공합니다:

열 설명
Id 고유 연결 ID(프로세스 종료에 사용됨).
User 연결된 사용자 계정.
Host 연결이 시작된 호스트.
db 스레드가 현재 사용 중인 데이터베이스.
Command 실행 중인 명령 유형(예: Query, Sleep, Connect).
Time 스레드가 현재 상태에 있는 시간(초).
State 스레드가 수행 중인 특정 작업(예: Sending data, Copying to tmp table).
Info 실행 중인 실제 SQL 문(또는 긴 경우 잘림).

출력 필터링 및 해석

대규모 프로덕션 시스템의 경우 전체 프로세스 목록이 압도적일 수 있습니다. 전체 쿼리 텍스트를 확인하기 위해 FULL 키워드를 사용하고, Time 또는 State 열로 필터링하는 것이 일반적인 관행입니다.

1. 전체 명령 텍스트 보기

느린 쿼리가 의심되는 경우 항상 FULL을 사용하세요. 표준 출력은 종종 Info 필드를 자르기 때문입니다:

SHOW FULL PROCESSLIST;

2. 차단 또는 느린 쿼리 식별

Time 및 Command 열을 모니터링하세요:

  • 높은 Time 값: 장기간 실행되는 쿼리(예: SLA에 따라 10초 이상)는 즉시 조사가 필요합니다. 해당 Info 열을 확인하여 SQL을 확인하세요.
  • Command = 'Sleep': 이러한 연결은 유휴 상태이지만 여전히 리소스를 소비합니다. 과도하게 누적되면 wait_timeout 변수 조정을 고려하세요.
  • Command = 'Query': 이들은 활발히 실행 중인 문입니다. 해당 State에 특히 주의하세요.

3. 잠금 문제 식별

쿼리가 리소스를 기다리며 멈춰 있을 때 State 열이 이를 나타내는 경우가 많습니다:

  • Waiting for table metadata lock
  • Waiting for table lock
  • Waiting for lock

대기 상태의 스레드가 많이 보이면 경합을 의미하며, 일반적으로 다른 사람이 필요한 잠금을 보유한 장기 실행 트랜잭션으로 인해 발생합니다.

조치: 프로세스 종료

성능을 심각하게 저하시키는 통제 불능 쿼리를 식별한 경우, 프로세스 Id 뒤에 KILL 명령어를 사용하여 종료할 수 있습니다:

KILL 12345; -- 12345를 프로세스 목록의 실제 Id로 바꾸세요

경고: KILL은 주의해서 사용하세요. 활성 트랜잭션을 종료하면 트랜잭션이 복잡한 쓰기 작업 중간에 있었을 경우 데이터베이스가 일관성 없는 상태로 남을 수 있습니다. 가능하면 먼저 쿼리를 식별하고 최적화하는 것을 항상 시도하세요.

문제 해결을 위한 상태 및 프로세스 정보 결합

효과적인 MySQL 모니터링은 종종 이 두 명령어 간의 상관 관계를 포함합니다:

  1. 초기 확인: SHOW FULL PROCESSLIST를 실행합니다. 높은 시간 쿼리나 과도한 연결을 기록합니다.
  2. 컨텍스트 확인: SHOW GLOBAL STATUS LIKE 'Threads_connected'를 사용하여 연결 수를 검토합니다. 홍수인지 아니면 단 하나의 나쁜 쿼리인지 확인합니다.
  3. 심층 분석: 특정 쿼리가 느린 경우, 쿼리가 실행되는 동안 Innodb_buffer_pool_reads 또는 임시 테이블 생성 속도를 검토하여 리소스 카운터에 미치는 영향을 분석합니다(기준선 비교 필요).

이러한 동적 출력을 정기적으로 확인함으로써 추측을 넘어 목표 지향적인 솔루션을 적용하여 MySQL 안정성과 속도를 개선할 수 있습니다.

현실적인 분류 루틴

좋은 첫 번째 패스는 1분 미만이 소요됩니다. 프로세스 목록부터 시작하세요:

SHOW FULL PROCESSLIST;

활성 쿼리 더미, 긴 Time 값, 잠금 대기, 많은 유휴 Sleep 연결을 스캔하세요. 단일 느린 보고서 쿼리는 동일한 테이블 잠금을 기다리는 수백 개의 웹 연결과 다르게 처리됩니다.

그런 다음 스레드 카운터를 확인하세요:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections'
);

Threads_connected는 연결된 클라이언트 수를 알려줍니다. Threads_running은 느려짐 동안 일반적으로 더 중요합니다. 활발히 작업 중인 스레드 수를 보여주기 때문입니다. 많은 연결된 수면 클라이언트는 낭비일 수 있지만, 많은 실행 중인 스레드는 서버가 실제 압박을 받고 있음을 의미할 수 있습니다.

다음으로, 워크로드가 디스크 기반 임시 테이블을 생성하는지 확인하세요:

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Created_tmp_tables',
  'Created_tmp_disk_tables',
  'Sort_merge_passes'
);

이러한 카운터는 시작 이후 누적되므로 한 번의 스냅샷은 오해를 불러일으킬 수 있습니다. 사고 중에 몇 분 간격으로 두 번의 스냅샷을 찍으세요. 디스크 임시 테이블이 빠르게 증가하는 경우 GROUP BY, ORDER BY, 큰 조인, 텍스트 열 또는 누락된 인덱스가 있는 쿼리를 검사하세요. tmp_table_size를 높이는 것이 경우에 따라 도움이 될 수 있지만, 더 나은 쿼리나 인덱스가 종종 더 깔끔한 수정입니다.

InnoDB 압력 살펴보기

대부분의 최신 MySQL 배포는 InnoDB를 사용하므로 InnoDB 카운터에 주목할 가치가 있습니다:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_read_requests는 논리적 읽기 요청을 계산합니다. Innodb_buffer_pool_reads는 디스크로 가야 했던 읽기를 계산합니다. 정상 트래픽 중에 물리적 읽기가 빠르게 증가하는 경우, 버퍼 풀이 작업 세트에 비해 너무 작거나, 쿼리가 너무 많은 데이터를 스캔하거나, 배치 작업이 유용한 페이지를 캐시에서 밀어내고 있을 수 있습니다.

잠금 대기는 또 다른 일반적인 고통의 원천입니다:

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_row_lock%';

증가하는 행 잠금 대기가 자동으로 InnoDB가 고장났다는 것을 의미하지는 않습니다. 일반적으로 트랜잭션이 애플리케이션이 예상한 것보다 더 오래 잠금을 보유하고 있음을 의미합니다. 열린 트랜잭션, 느린 업데이트 또는 트랜잭션을 시작하고 외부 서비스를 호출한 다음 훨씬 나중에 커밋하는 코드 경로를 찾으세요.

더 깊은 잠금 및 트랜잭션 세부 정보를 위해 SHOW ENGINE INNODB STATUS\G가 도움이 될 수 있지만, 출력이 밀집되어 있습니다. 프로세스 목록에 잠금 대기가 표시되고 그 뒤에 있는 트랜잭션 패턴을 식별해야 할 때 사용하세요.

KILL의 안전한 사용

KILL은 유용하지만 정리 버튼이 아닙니다. 대규모 트랜잭션을 실행 중인 연결을 종료하면 MySQL이 작업을 롤백해야 할 수 있으며, 롤백에는 시간이 걸릴 수 있습니다. 일부 사고에서는 여전히 올바른 조치이지만, 결정을 신중하게 내리세요.

MySQL 버전과 권한이 지원하는 경우 먼저 쿼리를 종료하는 것이 좋습니다:

KILL QUERY 12345;

이렇게 하면 연결을 유지하면서 현재 문을 중지하려고 시도합니다. 클라이언트가 잘못 작동하거나 연결이 끊어져야 하는 경우 다음을 사용하세요:

KILL CONNECTION 12345;

무엇이든 종료하기 전에 프로세스 목록 행, 사용자, 호스트, 데이터베이스 및 SQL 텍스트를 캡처하세요. 사고 후에 이 세부 정보는 동일한 쿼리가 다시 돌아오기를 기다리는 대신 원인을 수정하는 데 도움이 됩니다.

일반적인 프로세스 목록 상태와 그 의미

Sending data가 항상 MySQL이 네트워크를 통해 행을 보내고 있다는 것을 의미하지는 않습니다. 종종 서버가 행을 읽고, 필터링하고, 정렬하거나 준비하고 있음을 의미합니다. 쿼리가 그곳에서 오랜 시간을 보내면 문에 대해 EXPLAIN을 실행하고 테이블 스캔, 잘못된 조인 순서 또는 누락된 인덱스를 찾으세요.

Copying to tmp table 또는 Creating sort index는 종종 비용이 많이 드는 정렬 또는 그룹화를 가리킵니다. 인덱스가 WHERE 및 ORDER BY 패턴을 지원할 수 있는지 확인하세요. 때로는 쿼리가 제품이 요청한 대로 정확히 수행하고 있지만, 요청 경로 대신 비동기 보고서에 속합니다.

Waiting for table metadata lock은 DDL과 일반 쿼리가 충돌할 때 자주 나타납니다. 겉보기에 간단한 ALTER TABLE이 열린 트랜잭션 뒤에서 대기할 수 있으며, 이후 쿼리는 보류 중인 DDL 뒤에 쌓입니다. 이 경우 모든 대기 쿼리를 종료하는 것보다 가장 오래된 차단자를 종료하는 것이 더 안전할 수 있습니다.

카운터를 유용한 증거로 전환하기

SHOW STATUS 값은 대부분 카운터이므로, 원시 숫자보다 비율이 더 유용합니다. 동일한 변수를 두 번 캡처하세요:

SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

60초 기다린 후 다시 실행하세요. 차이는 그 1분 동안의 비율을 알려줍니다. 이것은 대시보드가 사용하는 것과 동일한 아이디어이지만, 터미널 액세스만 있을 때 수동으로 수행하는 것이 유용합니다.

사고 중에 메모를 남기세요. "Threads_running이 8에서 90으로 증가했고, 프로세스 목록에 orders에 대한 메타데이터 잠금을 기다리는 70개의 쿼리가 표시되었으며, Max_used_connections는 변경되지 않음"은 유용한 진단입니다. "MySQL이 느렸음"은 그렇지 않습니다.

이 명령어만으로 충분하지 않은 경우

SHOW STATUS와 SHOW PROCESSLIST는 첫 대응 도구입니다. 느린 쿼리 로그, Performance Schema, 쿼리 계획 또는 호스트 수준 메트릭을 대체하지 않습니다. 동일한 문제가 반복되면 느린 쿼리 로그를 활성화하거나 검토하고 EXPLAIN으로 최악의 문을 검사하세요.

반복되는 연결 급증의 경우 애플리케이션 풀 설정과 배포 동작을 살펴보세요. max_connections를 높이면 시간을 벌 수 있지만, 서버가 실제로 실행할 수 있는 것보다 더 많은 작업을 수용하게 할 수도 있습니다. 반복되는 잠금 대기의 경우 애플리케이션의 트랜잭션 경계를 검사하세요. 코드가 외부 API를 호출하는 동안 열려 있는 트랜잭션은 관련 없는 요청을 차단하고 MySQL을 실제보다 느리게 보이게 할 수 있습니다.

또한 호스트를 확인하세요. 디스크 대기 시간이 높거나, CPU가 포화 상태이거나, 메모리가 스와핑되거나, 시끄러운 이웃이 리소스를 훔치는 경우 MySQL 카운터는 증상을 보여주지만 전체 원인은 보여주지 않습니다. 좋은 진단은 데이터베이스 명령어와 시스템 메트릭을 결합합니다.

SHOW STATUS는 카운터와 컨텍스트를 제공합니다. SHOW FULL PROCESSLIST는 실시간 워크로드를 제공합니다. 함께 사용하면 연결 압력, 하나의 나쁜 쿼리, 잠금 경합, 디스크 집약적인 임시 작업 및 InnoDB 캐시 압력 간의 차이를 구분하는 데 도움이 됩니다.