일반적인 MySQL 성능 병목 현상과 해결 방법

MySQL이 느려질 때, 첫 번째 증상은 거의 "데이터베이스가 느리다"가 아닙니다. 일반적으로 체크아웃 페이지가 멈추거나, 대기열이 비워지지 않거나, 대시보드가 타임아웃되거나, 이전에 80ms 만에 완료되던 요청이 갑자기 3초가 걸리는 API가 나타납니다.

시간을 낭비하는 가장 빠른 방법은 대기가 어디서 발생하는지 알기 전에 임의의 설정을 조정하는 것입니다. 먼저 명확한 질문을 던지세요: MySQL이 쿼리 작업, 잠금, 메모리, 디스크, CPU, 네트워크 또는 너무 많은 연결 중 무엇을 기다리고 있습니까? 해결 방법은 답에 따라 달라집니다.

1. 느린 쿼리

느린 쿼리는 틀림없이 가장 일반적인 성능 병목 현상입니다. 비효율적인 쿼리 설계, 누락된 인덱스 또는 대규모 테이블 스캔과 같은 다양한 요인으로 인해 발생할 수 있습니다. 이러한 쿼리를 식별하는 것이 해결의 첫 번째 단계입니다.

느린 쿼리 식별

MySQL 느린 쿼리 로그는 지정된 임계값보다 오래 걸리는 쿼리를 식별하는 데 매우 유용한 도구입니다. my.cnf(또는 my.ini) 구성 파일에서 이 로그를 활성화하고 구성할 수 있습니다.

my.cnf 구성 예시:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

이 예시에서:

slow_query_log = 1: 느린 쿼리 로그를 활성화합니다.
slow_query_log_file: 로그 파일의 경로를 지정합니다.
long_query_time = 2: 임계값을 2초로 설정합니다. 이보다 오래 걸리는 쿼리가 기록됩니다.
log_queries_not_using_indexes = 1: 인덱스를 사용하지 않는 쿼리를 기록합니다. 이러한 쿼리는 최적화의 주요 대상이 되는 경우가 많습니다.

로그를 활성화한 후에는 내용을 분석할 수 있습니다. mysqldumpslow와 같은 도구는 로그 파일을 요약하고 정렬하여 가장 문제가 되는 쿼리를 더 쉽게 찾을 수 있도록 도와줍니다.

느린 쿼리 최적화

느린 쿼리가 식별되면 여러 전략을 사용할 수 있습니다:

인덱싱: WHERE, JOIN, ORDER BY 및 GROUP BY 절에 사용되는 열에 적절한 인덱스가 생성되었는지 확인하세요. EXPLAIN을 사용하여 쿼리 실행 계획을 분석하고 누락된 인덱스를 식별하세요.
- 예시: 쿼리가 대규모 orders 테이블에서 user_id로 자주 필터링하는 경우 orders(user_id)에 대한 인덱스가 성능을 획기적으로 향상시킬 수 있습니다.
```
CREATE INDEX idx_user_id ON orders (user_id);
```
쿼리 재작성: 때로는 더 나은 효율성을 위해 쿼리를 다시 작성할 수 있습니다. 여기에는 조인 단순화, SELECT * 방지, 서브쿼리 신중한 사용 등이 포함될 수 있습니다.
- 예시: 상관 서브쿼리를 JOIN으로 대체하면 더 나은 성능을 제공할 수 있습니다.
데이터베이스 스키마 설계: 정규화 문제 또는 (신중하게) 비정규화 기회에 대한 데이터베이스 스키마를 검토하는 것도 도움이 될 수 있습니다.

2. 비효율적인 인덱싱

인덱싱이 쿼리 성능의 핵심이지만, 잘못 설계되거나 과도한 인덱스도 병목 현상이 될 수 있습니다. 인덱스는 디스크 공간을 소비하고 쓰기 작업(INSERT, UPDATE, DELETE)에 오버헤드를 추가합니다.

인덱싱 문제 식별

EXPLAIN 계획 분석: 인덱싱 변경을 수행하기 전후에 항상 EXPLAIN을 사용하세요. 대규모 테이블에서 전체 테이블 스캔(type: ALL) 또는 반환된 행보다 훨씬 많은 검사된 행을 찾으세요.
```
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
```
사용되지 않는 인덱스: MySQL 5.6+에는 인덱스 사용을 추적하는 기능이 있습니다. performance_schema.table_io_waits_summary_by_index_usage를 확인하여 전혀 또는 거의 사용되지 않는 인덱스를 식별할 수 있습니다.
중복 인덱스: 동일한 열을 포함하거나 다른 인덱스의 접두사인 인덱스는 중복될 수 있습니다.

인덱싱 모범 사례

선택적 인덱싱: 쿼리 패턴을 기반으로 실제로 필요한 곳에만 인덱스를 생성하세요.
복합 인덱스: 여러 열을 필터링하는 쿼리의 경우 복합 인덱스를 고려하세요. 복합 인덱스에서 열의 순서가 중요합니다.
커버링 인덱스: 쿼리에 필요한 모든 열이 인덱스의 일부인 커버링 인덱스를 목표로 하세요. 이를 통해 MySQL은 테이블에 액세스하지 않고 인덱스에서 직접 데이터를 검색할 수 있습니다.
정기적인 검토: 스키마 변경이나 애플리케이션 사용 패턴 변경 후에 특히 정기적으로 인덱스를 검토하세요.

3. 버퍼 풀 및 메모리 구성

InnoDB 버퍼 풀은 InnoDB가 데이터 및 인덱스 페이지를 캐시하는 중요한 메모리 영역입니다. 버퍼 풀 크기가 충분하지 않으면 과도한 디스크 I/O가 발생하여 작업 속도가 크게 느려질 수 있습니다.

InnoDB 버퍼 풀 조정

innodb_buffer_pool_size 매개변수는 InnoDB 성능을 위한 가장 중요한 설정 중 하나입니다.

권장 사항: 전용 데이터베이스 서버의 경우 innodb_buffer_pool_size를 사용 가능한 RAM의 50-75%로 설정하는 것이 일반적인 시작점입니다. 일부 시스템은 더 높게 설정할 수 있지만 운영 체제가 스와핑되지 않고 연결 메모리가 제어되는 경우에만 가능합니다.

my.cnf 구성 예시:

[mysqld]
innodb_buffer_pool_size = 8G

이렇게 하면 버퍼 풀이 8GB로 설정됩니다.

모니터링: 버퍼 풀 읽기 패턴을 관찰하세요. 매우 높은 적중률은 대부분의 읽기가 메모리에서 처리된다는 것을 의미하지만 모든 쿼리가 정상임을 증명하지는 않습니다. 다음을 사용하여 모니터링할 수 있습니다:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

적중률은 (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests로 계산할 수 있습니다.

기타 메모리 설정

innodb_log_file_size: 쓰기 성능 및 복구 시간에 영향을 미칩니다. 파일이 클수록 쓰기 처리량이 향상될 수 있지만 충돌 후 복구 시간이 늘어납니다.
innodb_flush_log_at_trx_commit: 내구성과 성능 간의 균형을 제어합니다. 1(기본값)로 설정하면 완전한 ACID 준수를 보장하지만 더 느릴 수 있습니다. 0 또는 2로 설정하면 일부 내구성 보장을 희생하면서 성능을 향상시킬 수 있습니다.

4. 잠금 문제 및 동시성

잠금은 데이터 일관성에 필수적이지만 제대로 관리되지 않으면 병목 현상이 될 수 있습니다. 과도한 잠금은 쿼리 경합, 시간 초과 및 교착 상태로 이어질 수 있습니다.

잠금 문제 식별

SHOW ENGINE INNODB STATUS: 이 명령은 활성 트랜잭션, 보유 잠금 및 잠금 대기를 포함하여 InnoDB의 내부 상태에 대한 자세한 정보를 제공합니다.
Performance Schema 잠금 테이블: MySQL 8.0에서는 data_locks 및 data_lock_waits와 같은 Performance Schema 테이블을 사용하세요. 이전 버전에서는 information_schema 테이블을 통해 잠금 정보를 노출했습니다.
모니터링 도구: 성능 모니터링 도구는 높은 잠금 대기 시간이나 교착 상태를 강조 표시할 수 있습니다.

잠금 문제 해결

잠금을 유발하는 쿼리 최적화: 더 짧고 효율적인 쿼리는 잠금이 유지되는 시간을 줄입니다.
트랜잭션 관리: 트랜잭션을 가능한 한 짧게 유지하세요. 광범위한 잠금이 필요한 트랜잭션 내에서 장기 실행 작업을 피하세요.
잠금 세분성: InnoDB는 대부분의 작업에 행 수준 잠금을 사용하며, 이는 일반적으로 동시성에 좋습니다. 그러나 쿼리가 테이블 잠금으로 확대될 수 있는 방법(예: 온라인 DDL 없는 ALTER TABLE)을 이해하는 것이 중요합니다.
교착 상태 감지 및 해결: MySQL에는 교착 상태 감지기가 있습니다. 교착 상태가 감지되면 InnoDB는 일반적으로 관련 트랜잭션 중 하나를 롤백하여 다른 트랜잭션이 진행되도록 합니다. SHOW ENGINE INNODB STATUS의 교착 상태 정보를 분석하여 원인을 파악하고 애플리케이션 로직 또는 쿼리 순서를 조정하세요.

5. 리소스 경합 (CPU, 디스크, 네트워크)

최적화된 쿼리와 적절한 구성에도 불구하고 불충분한 하드웨어 리소스 또는 이러한 리소스에 대한 경합은 성능을 제한할 수 있습니다.

리소스 병목 현상 식별

CPU 사용량: mysqld 프로세스의 높은 CPU 사용량은 비효율적인 쿼리, 과도한 정렬 또는 처리 능력 부족을 나타낼 수 있습니다.
디스크 I/O: 특히 버퍼 풀 적중률이 낮은 경우 높은 디스크 읽기/쓰기 활동은 디스크 I/O가 병목 현상임을 나타냅니다. Linux 시스템에서 높은 iowait 시간을 찾으세요.
네트워크 처리량: 대규모 결과 집합이 전송되거나 많은 클라이언트 연결이 있는 경우 과도한 네트워크 트래픽이 발생할 수 있습니다.

리소스 병목 현상 해결

하드웨어 업그레이드: 때로는 가장 간단한 해결책은 CPU, RAM 또는 더 빠른 스토리지를 추가하는 것입니다. 워크로드가 합리적이라는 것을 확인한 후에만 수정 사항으로 처리하세요. 하드웨어는 잘못된 쿼리를 숨길 수 있지만 거의 사라지게 하지는 않습니다.
쿼리 최적화: 처리 및 전송되는 데이터 양을 줄여 간접적으로 CPU, 디스크 및 네트워크 부하를 줄입니다.
연결 풀링: 애플리케이션에서 연결 풀링을 구현하여 새 연결 설정 오버헤드를 줄이고 활성 연결 수를 효과적으로 관리하세요.
읽기 복제본: 읽기 집약적인 워크로드의 경우 읽기 복제본을 설정하여 기본 서버에서 읽기 부하를 분산하는 것을 고려하세요.

압박 속에서 작동하는 분류 흐름

인시던트가 활성화된 경우 전체 튜닝 프로젝트부터 시작하지 마세요. 먼저 빠른 그림을 얻으세요.

활성 쿼리 확인:

SHOW FULL PROCESSLIST;

동일한 쿼리에 멈춰 있는 많은 세션이 보이면 캡처하세요. 잠금을 기다리는 많은 세션이 보이면 무작위로 종료하지 말고 먼저 차단 트랜잭션을 식별하세요.

InnoDB 상태 확인:

SHOW ENGINE INNODB STATUS\G

교착 상태, 잠금 대기, 체크포인트 압력 및 장기 실행 트랜잭션을 찾으세요. 한 시간 동안 열려 있는 트랜잭션은 제거 작업을 지연시키고 관련 없는 쿼리를 더 느리게 만들 수 있습니다.

서버가 포화 상태인지 확인:

top
vmstat 1
iostat -xz 1
ss -s

I/O가 낮은 높은 CPU는 일반적으로 값비싼 쿼리 실행, 정렬, 구문 분석 또는 과도한 동시성을 가리킵니다. 높은 iowait는 스토리지를 가리킵니다. 스왑 활동은 위험 신호입니다. 스왑 압력 하의 MySQL은 종종 예측할 수 없게 동작합니다.

그런 다음 지난 몇 분 동안의 느린 쿼리 로그를 확인하세요. 전체 기간 최악의 쿼리만 확인하지 마세요. 오늘의 인시던트를 유발한 쿼리는 새롭거나, 배포와 관련되거나, 피크 시간에만 나타나는 트래픽 패턴과 관련될 수 있습니다.

연결 폭풍

일반적인 MySQL 병목 현상은 하나의 잘못된 쿼리가 아니라 소량의 작업을 수행하는 너무 많은 애플리케이션 연결입니다. 모든 웹 작업자가 자체 연결을 열고 앱이 갑자기 확장되면 MySQL은 세션을 예약하고 연결당 메모리를 할당하는 데 너무 많은 시간을 소비할 수 있습니다.

증상은 다음과 같습니다:

Threads_connected가 급격히 증가합니다.
Threads_running이 높게 유지됩니다.
Too many connections와 같은 애플리케이션 오류.
명백한 느린 쿼리 없이 CPU가 상승합니다.

유용한 확인:

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

해결 방법은 종종 애플리케이션 계층에 있습니다: 연결 풀링을 사용하고, 적절한 풀 제한을 설정하고, 시간 초과를 명시적으로 만드세요. max_connections를 높이면 시간을 벌 수 있지만 각 연결이 조인, 정렬 및 임시 테이블에 메모리를 사용하는 경우 서버가 더 심하게 다운될 수 있습니다.

임시 테이블 및 정렬

GROUP BY, ORDER BY, DISTINCT 또는 대규모 조인이 있는 쿼리는 임시 테이블을 만들 수 있습니다. 일부 임시 테이블은 메모리에 유지됩니다. 더 큰 테이블은 디스크로 넘어갑니다. 디스크 임시 테이블이 자동으로 재앙은 아니지만 갑작스러운 증가는 종종 지연 시간 급증을 설명합니다.

확인:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

그런 다음 쿼리 계획을 검사하세요. EXPLAIN에 Using temporary 및 Using filesort가 표시되면 인덱스가 필터와 순서를 함께 지원할 수 있는지 물어보세요. 예:

SELECT customer_id, created_at, total
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

(status, created_at)에 대한 인덱스는 필터링 및 정렬 작업을 모두 줄일 수 있습니다. tmp_table_size를 늘리면 일부 경우에 도움이 될 수 있지만 세션별 위험이 있습니다. 많은 세션이 동시에 대규모 임시 테이블을 할당하면 메모리가 빠르게 사라집니다.

성능 증상으로서의 복제 지연

읽기가 복제본으로 가는 경우 복제 지연은 기본 서버가 정상이더라도 데이터베이스 성능 문제처럼 보일 수 있습니다. 사용자는 페이지를 새로 고치고 자신의 변경 사항을 보지 못합니다. 백그라운드 작업은 오래된 행을 읽습니다. 보고서가 일치하지 않습니다.

MySQL 버전에 적합한 도구를 사용하여 복제본 상태를 확인하세요:

SHOW REPLICA STATUS\G

이전 버전에서는 다음을 사용합니다:

SHOW SLAVE STATUS\G

지연은 복제본의 느린 SQL, 기본 서버의 대규모 트랜잭션, 불충분한 복제본 하드웨어, 행별 유지 관리 작업 또는 네트워크 문제로 인해 발생할 수 있습니다. 해결 방법은 쿼리 튜닝, 대규모 쓰기를 더 작은 청크로 분할, 복제본 리소스 개선 또는 새 읽기가 라우팅되는 위치 변경일 수 있습니다.

먼저 변경할 사항

작업을 줄이는 수정을 선호하세요:

입증된 핫 쿼리에 대한 인덱스를 추가하거나 조정합니다.
더 적은 행을 읽도록 쿼리를 다시 작성합니다.
잠금을 보유하는 트랜잭션을 단축합니다.
MySQL이 넘치지 않도록 연결 풀 크기를 제한합니다.
무거운 보고서를 기본 서버에서 멀리 이동합니다.

용량만 늘리는 수정에는 더 신중하세요:

max_connections를 높입니다.
정렬 및 조인 버퍼를 전역적으로 늘립니다.
임시 테이블 제한을 늘립니다.
문제를 일으키는 쿼리를 수정하지 않고 복제본을 추가합니다.

용량 변경은 제 역할이 있지만 증거에 따라야 합니다. 좋은 MySQL 문제 해결 세션은 더 적은 양의 데이터베이스 작업을 남기고, 동일한 낭비 작업을 수행하는 더 큰 서버만 남기지 않습니다.