MySQL 성능 모니터링: SHOW STATUS 및 SHOW PROCESSLIST 사용

성능 병목 현상을 진단하고 MySQL 데이터베이스의 상태를 이해하는 것은 모든 관리자 또는 개발자에게 기본적인 기술입니다. 느린 쿼리, 연결 폭주 또는 예기치 않은 리소스 사용량은 애플리케이션 성능에 심각한 영향을 미칠 수 있습니다. 다행히 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. 전체 명령 텍스트 보기

표준 출력은 종종 Info 필드를 잘라내므로 느린 쿼리가 의심되는 경우 항상 FULL을 사용하십시오.

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 모니터링에는 종종 이 두 명령 간의 상관 관계가 포함됩니다.

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

이러한 동적 출력을 정기적으로 확인하면 추측을 넘어 MySQL 안정성과 속도를 개선하기 위한 대상 솔루션을 적용할 수 있습니다.

결론

SHOW STATUS 및 SHOW PROCESSLIST 명령은 실시간 MySQL 진단을 위한 최전선 도구입니다. SHOW STATUS는 서버 구성을 조정하는 데 필요한 기록 컨텍스트와 카운터 메트릭을 제공하며, SHOW PROCESSLIST는 현재 긴급 상황이나 오래 실행되는 실행을 처리하는 데 필요한 즉각적인 스냅샷을 제공합니다. 이러한 명령을 정기적으로 사용하면 고성능 데이터베이스 환경을 유지하는 데 필수적입니다.