MySQL/MariaDB 성능 최적화 실전 가이드
데이터베이스는 대부분의 웹 서비스에서 성능 병목이 되기 쉬운 구간입니다. MySQL/MariaDB의 기본 설정을 서버 환경에 맞게 최적화하면 쿼리 응답 속도와 동시 처리 능력을 크게 향상시킬 수 있습니다.
현재 상태 진단
-- 시스템 변수 확인
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'query_cache%';
-- 상태 변수로 성능 지표 확인
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
-- 버퍼 풀 히트율 계산 (90% 이상이 이상적)
-- Hit Rate = (read_requests - reads) / read_requests * 100
my.cnf 핵심 설정
# /etc/my.cnf 또는 /etc/mysql/my.cnf
[mysqld]
# ==================== InnoDB 설정 ====================
# 전체 RAM의 70~80% 할당 (가장 중요한 설정)
# 16GB RAM 서버: 12G 권장
innodb_buffer_pool_size = 12G
# 버퍼 풀 인스턴스 (buffer_pool_size / 1GB 단위)
innodb_buffer_pool_instances = 12
# 로그 파일 크기 (버퍼 풀의 25%)
innodb_log_file_size = 3G
innodb_log_buffer_size = 256M
# 플러시 방식 (SSD: O_DIRECT 권장)
innodb_flush_method = O_DIRECT
# 더블 라이트 버퍼 (SSD에서는 비활성화 가능)
innodb_doublewrite = 1
# I/O 용량 설정 (SSD: 높게 설정)
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# ==================== 연결 설정 ====================
max_connections = 300
max_connect_errors = 10000
# 스레드 캐시
thread_cache_size = 32
thread_stack = 256K
# ==================== 쿼리 캐시 ====================
# MySQL 8.0에서 제거됨, 5.7 이하에서만
query_cache_type = 0
query_cache_size = 0
# ==================== 슬로우 쿼리 로그 ====================
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
인덱스 최적화
-- 인덱스 사용 현황 확인
SELECT table_name, index_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'mydb'
ORDER BY cardinality DESC;
-- 실행 계획 분석 (EXPLAIN)
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE created_at > '2025-01-01';
-- 복합 인덱스 생성
ALTER TABLE orders ADD INDEX idx_user_date (user_id, created_at);
-- 인덱스 재구성
OPTIMIZE TABLE orders;
-- 미사용 인덱스 찾기 (performance_schema)
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
AND count_star = 0
AND object_schema = 'mydb';
슬로우 쿼리 분석
# mysqldumpslow로 슬로우 쿼리 분석
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 실행 시간 기준 상위 10개
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log | head -50
# pt-query-digest (Percona Toolkit) 활용
pt-query-digest /var/log/mysql/slow.log > /tmp/query-digest.txt
연결 풀링 최적화
# wait_timeout: 비활성 연결 유지 시간 (초)
wait_timeout = 600
interactive_timeout = 600
# 연결당 버퍼 (max_connections와 곱해서 메모리 사용량 계산)
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
유용한 진단 쿼리
-- 현재 실행 중인 쿼리 확인
SHOW FULL PROCESSLIST;
-- 잠금 대기 중인 트랜잭션
SELECT r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
-- 테이블별 크기 확인
SELECT table_name,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
ROUND(data_length / 1024 / 1024, 2) AS data_mb,
ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY total_mb DESC;
정기 유지보수
# 모든 테이블 분석 (통계 갱신)
mysqlcheck --all-databases --analyze -u root -p
# 테이블 최적화 (데이터 단편화 해소)
mysqlcheck --all-databases --optimize -u root -p
# 특정 DB만 최적화
mysqlcheck --optimize --database mydb -u root -p
데이터베이스 튜닝은 한 번에 많은 변경을 하기보다 하나씩 변경하며 성능 지표를 모니터링하는 것이 중요합니다. innodb_buffer_pool_size를 먼저 조정하고, 슬로우 쿼리 로그를 분석하여 인덱스를 최적화하는 순서로 진행하세요.
댓글남기기