MySQL slow query 완벽 진단 및 최적화 가이드

🔍 검색 키워드: PostgreSQL VACUUM, 데드 튜플 정리, PostgreSQL 성능 저하, autovacuum, PostgreSQL 디스크 사용량, VACUUM ANALYZE PostgreSQL은 MVCC(Multi-Version Concurrency Control) 방식으로 동작하기 때문에, 데이터를 삭제해도 물리적으로 즉시 제거되지 않습니다. 시간이 지나면서 불필요한 "죽은" 데이터(데드 튜플)가 누적되어 디스크 용량을 낭비하고 쿼리 성능을 저하시킵니다. 이 글에서는 VACUUM의 동작 원리와 최적화 방법을 설명합니다. 증상: PostgreSQL의 숨은 성능 저하 PostgreSQL 서버를 운영하다 보면 나타나는 문제들: # 문제 1: 디스크 사용량은 계속 증가하지만 DELETE했는데도? SELECT pg_size_pretty(pg_total_relation_size('users')); -- 5GB # 문제 2: 같은 쿼리인데 시간이 점점 느려짐 EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'completed'; -- Seq Scan on orders (cost=0.00..50000.00 rows=100000) # 문제 3: 특정 테이블에서만 SELECT 느림 SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders'; -- 결과: n_live_tup=100000, n_dead_tup=500000 (데드 튜플이 많음!) 원인 분석: MVCC와 데드 튜플 PostgreSQL의 MVCC 동작 방식: 1. 트랜잭션 A가 행을 삭제하면, 실제로는 그 행을 "삭제됨" 표시 2. 트랜잭션 B가 여전히 이전 버전을 참조 중이면, 그 행은 메모리에 남아야 함 3. 모든 트랜잭션이 새 버전만 참조하게 되면, 그때 "데드 튜플" 처리 가능 데드 튜플이 문제인 이유: - 디스크 용량 낭비: 삭제한 데이터도 차지 - 인덱스 비대화: 데드 튜플도 인덱스 스캔에 포함 - 쿼리 성능 저하: Seq Scan 비용 증가 원인 추적: 데드 튜플 상태 확인 데드 튜플 누적 상태를 확인합니다: SELECT schemaname, relname, n_live_tup, n_dead_tup, ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10; 결과 해석: - n_live_tup: 활성 행 수 - n_dead_tup: 데드 튜플 수 - dead_ratio: 데드 튜플 비율. 10% 이상이면 VACUUM 필요 해결방법 1: 자동 VACUUM (autovacuum) 설정 최적화 PostgreSQL은 기본적으로 autovacuum 데몬이 자동으로 VACUUM을 실행합니다. 하지만 기본 설정은 보수적이므로, 대용량 데이터베이스에서는 수동 튜닝이 필요합니다. postgresql.conf 설정 확인: SHOW autovacuum; SHOW autovacuum_naptime; SHOW autovacuum_vacuum_threshold; 권장 설정 (운영 환경): postgresql.conf에 추가: autovacuum = on autovacuum_naptime = '10s' # 10초마다 체크 (기본 1분) autovacuum_vacuum_threshold = 50 # 50개 행 변경 시 VACUUM (기본 50) autovacuum_analyze_threshold = 50 # 50개 행 변경 시 ANALYZE (기본 50) # 테이블별 추가 튜닝 autovacuum_vacuum_scale_factor = 0.05 # 테이블의 5% 변경 시 autovacuum_vacuum_cost_limit = 1000 # I/O 부하 제한 (기본 -1) 재시작: sudo systemctl restart postgresql 해결방법 2: 수동 VACUUM 실행 긴급 상황에서는 수동으로 VACUUM을 실행합니다: # 기본 VACUUM (먹다 남은 공간 재사용) VACUUM; # ANALYZE 포함 (통계 업데이트) VACUUM ANALYZE; # 특정 테이블만 처리 VACUUM ANALYZE orders; # FULL (여유 공간을 OS로 반환, 시간 오래 걸림) VACUUM FULL; 주의: VACUUM FULL은 테이블을 잠금(LOCK)으로 처리하므로, 운영 중에는 피해야 합니다. 해결방법 3: 추가 최적화 1. REINDEX로 인덱스 재구성 데드 튜플로 비대화된 인덱스를 재구성: REINDEX TABLE orders; 2. pg_repack 확장 모듈 (다운타임 없음) CREATE EXTENSION pg_repack; SELECT pg_repack.repack_table('orders'); pg_repack은 FULL VACUUM과 달리 테이블 잠금 없이 재구성합니다. 운영 중 사용 가능. 3. 정기적인 VACUUM ANALYZE 스케줄 크론 작업으로 자동 실행: # 매일 새벽 2시에 VACUUM ANALYZE 실행 0 2 * * * psql -d mydb -c "VACUUM ANALYZE;" 성능 회복 비교 경미한 데드 튜플: VACUUM ANALYZE → 통계 업데이트, 쿼리 최적화 → ~초 단위 중간 정도 비대화: REINDEX TABLE → 인덱스 재구성 → ~분 단위 심각한 비대화: pg_repack (비운영) 또는 VACUUM FULL → 공간 회복 → ~분~시간 단위 모니터링 체크리스트 데드 튜플 비율: pg_stat_user_tables에서 dead_ratio → 10% 이상이면 VACUUM 자동 VACUUM 실행 여부: pg_stat_user_tables의 last_vacuum → 안 실행되면 autovacuum_naptime 단축 인덱스 비대화: pg_indexes_size(table) → 인덱스가 테이블보다 크면 REINDEX 디스크 사용량: pg_size_pretty(pg_database_size(...)) → 지속 증가면 VACUUM FULL 또는 pg_repack PostgreSQL의 성능 저하는 대부분 데드 튜플 누적으로 인한 것입니다. 정기적인 VACUUM과 ANALYZE로 데이터베이스를 "청소"하면, 쿼리 성능과 디스크 효율을 동시에 개선할 수 있습니다.