MySQL의 성능 개선 테크닉

쿼리 개선하기

느린 쿼리 밝혀내기

• 슬로우 쿼리 로그

• SHOW FULL PROCESSLIST 등 다양한 방법으로 확인 가능

• I/O 튜닝 방법
• 쿼리를 실행시키지 않기
• I/O를 시키지 않기 (테이블 스캔 → 인덱스 검색)
• 필요한 레코드만 I/O 시키기 (테이블 스캔 → 인덱스 검색)
많은 I/O가 필요한 경우 순차적인 I/O에 귀착시키기
일치하는 수백 개의 레코드를 개별적으로 랜덤 액세스해 나가는 것이 아니라 인덱스를 스캔하는 것만으로 대상 레코드를 검색한 후에 되돌려 주어야 할 레코드만을 랜덤 액세스하는 것이 효과적
• 정렬을 시키지 않기
• 병렬 I/O를 시키기(주로 배치 처리)

정렬의 실행 효율에 주의하기

• 인덱스의 내용을 올바로 사용하지 않은 경우 (버그)

• 두 경우 모두 FORCE INDEX로 인덱스를 강제함으로써 올바르게 인덱스를 사용하게 할 수 있음

실행 빈도가 많은 쿼리 밝혀내기

• 이런 종류의 문제를 분석하는 데 가장 쉽고 효과적이라고 말할 수 있는 것
tcp-dump : 패킷 캡쳐를 하는 범용 도구
고부하 환경에서 사용하면 몇 초만에 100MB 수준으로 파일 사이즈가 되기에 클라이언트 IP 주소 등 다양한 조건으로 필터링하여 전체 통계 경향에 영향을 주지 않는 범위에서 캡쳐 크기를 작게 하는 것이 유효함
• mk-query-digest : 슬로우 쿼리로그와 tcpdump 출력 결과 등을 분석하여 각 쿼리 유형마다 총 실행 시간을 계산하여 쿼리가 많은 순으로 나열하는 도구 (Maatkit 라는 유틸리티 도구에 포함되어 있음. MySQL 커맨드라인 기반 유틸리티 도구들에서 가장 뛰어난 것 중 하나)

느린 트랜잭션 개선하기

• 어떤 트랜잭션이 레코드를 30초 동안 잠그고 있을 경우, 동일한 레코드를 잠그려고 하는 다른 요청은 최대 30초 동안은 기다려야 함

• 이러한 문제를 해결하기 위해서는 잠금을 유지하고 있던 트랜잭션이 어떤 처리를 하고 있었는지를 파악하는 것이 중요한 과정임

• 필자가 개발한 MySlowTranCapture와 같은 툴을 이용해 느린 트랜잭션 파악이 가능함

경쟁에서의 배려

• 잠금 경쟁의 과제

• 웹 서비스에서는 사용자 수가 많은 반면 데이터베이스 서버의 수는 한정되어 있기 때문에 동일 테이블에 대해서 다수의 클라이언트로부터 일제히 액세스가 발생하는 경향이 있음 → 잠금 경합 문제되기 쉬움