MySQL 설정 튜닝 — 핵심 시스템 변수와 my.cnf 최적화

MySQL을 설치하고 기본 설정 그대로 운영하고 있다면 — 서버 메모리의 절반 이상을 낭비하고 있을 가능성이 높습니다.

innodb_buffer_pool_size의 기본값이 128MB인데, 요즘 서버 메모리가 최소 8GB는 되는 걸 생각하면 턱없이 작습니다. 이 글에서는 실무에서 반드시 알아야 할 MySQL 시스템 변수들을 카테고리별로 정리하고, 환경별 my.cnf 설정 예시까지 다룹니다.

개념 정의

MySQL 설정 튜닝 이란 MySQL 서버의 시스템 변수(System Variables)를 서버 환경과 워크로드에 맞게 조정하여 최적의 성능을 이끌어내는 작업입니다.

설정은 크게 두 가지로 나뉩니다.

• 정적 변수(Static): 서버를 재시작해야 반영되는 설정 (예: innodb_log_file_size)
• ** 동적 변수(Dynamic)**: 서버 실행 중에 SET GLOBAL로 변경 가능한 설정 (예: max_connections)

중요한 건, 튜닝은 "큰 값 = 좋은 성능"이 아니라는 점입니다. 각 변수가 어떤 리소스를 소비하는지 이해하고, 서버 사양과 워크로드에 맞는 균형점을 찾아야 합니다.

my.cnf 구조와 설정 방법

설정 파일 위치

MySQL은 시작할 때 여러 경로에서 설정 파일을 순서대로 읽습니다. /etc/my.cnf → /etc/mysql/my.cnf → ~/.my.cnf 순서이고, 나중에 읽은 파일이 이전 설정을 덮어씁니다. 운영 환경에서는 보통 /etc/my.cnf에 설정을 모아둡니다.

[mysqld] 섹션

서버 관련 설정은 모두 [mysqld] 섹션에 작성합니다.

[mysqld]
# 서버 설정은 여기에
innodb_buffer_pool_size = 4G
max_connections = 300

[client]
# 클라이언트 도구(mysql CLI 등) 설정
default-character-set = utf8mb4

동적 변경 vs 영구 설정

운영 중에 설정을 바꿔야 할 때, 두 가지 방법이 있습니다.

-- 즉시 반영 (서버 재시작하면 원복)
SET GLOBAL max_connections = 500;

-- 즉시 반영 + my.cnf에도 영구 저장 (MySQL 8.0+)
SET PERSIST max_connections = 500;

-- 현재 값 확인
SHOW VARIABLES LIKE 'max_connections';

-- 여러 변수를 패턴으로 조회
SHOW VARIABLES LIKE 'innodb_buffer%';

SET PERSIST는 MySQL 8.0에서 추가된 기능으로, mysqld-auto.cnf 파일에 자동 저장됩니다. 재시작 후에도 설정이 유지되어서 편리합니다.

SET GLOBAL로만 변경하면 서버를 재시작할 때 원래 값으로 돌아갑니다. 운영 환경에서 변경한 설정을 반드시 my.cnf에도 반영해두는 습관이 중요합니다.

메모리 관련 핵심 변수

innodb_buffer_pool_size

MySQL 튜닝에서 가장 중요한 변수를 하나만 꼽으라면, 단연 이것입니다.

Buffer Pool은 InnoDB가 데이터 페이지와 인덱스 페이지를 메모리에 캐싱하는 공간입니다. 이 영역이 클수록 디스크 I/O가 줄어들고, 쿼리 응답 속도가 빨라집니다.

# 전용 DB 서버: 총 메모리의 70~80%
innodb_buffer_pool_size = 24G   # 32GB 서버 기준

# 다른 프로세스와 공유하는 서버: 50~60%
innodb_buffer_pool_size = 8G    # 16GB 서버, 앱 서버와 동거

** 기본값이 128MB**인 이유는 MySQL이 다양한 환경(노트북, 공유 서버, 전용 서버)에서 돌아가기 때문입니다. 전용 DB 서버라면 이 값을 올리는 것만으로도 체감 성능이 크게 올라갑니다.

Buffer Pool 히트율을 확인하는 방법도 알아두면 좋습니다.

-- Buffer Pool 히트율 확인 (99% 이상이 정상)
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- 히트율 계산
-- 히트율 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) × 100

히트율이 99% 미만이라면 Buffer Pool이 부족하다는 신호입니다.

innodb_buffer_pool_instances 도 함께 설정하면 좋습니다. Buffer Pool이 크면 단일 뮤텍스가 병목이 될 수 있는데, 여러 인스턴스로 나누면 경합이 줄어듭니다. 보통 인스턴스당 1GB 이상이 되도록 설정합니다 (16GB라면 instances = 8~16).

서버 재시작 시 Buffer Pool이 비어서 느려지는 문제는 innodb_buffer_pool_dump_at_shutdown = ON과 innodb_buffer_pool_load_at_startup = ON으로 해결할 수 있습니다. MySQL 8.0에서는 기본 활성화되어 있습니다.

커넥션별 메모리 버퍼

Buffer Pool은 전체에서 공유하는 글로벌 메모리지만, 커넥션별로 할당되는 메모리도 있습니다.

# 커넥션별 할당 버퍼 (세션 단위)
sort_buffer_size = 256K      # ORDER BY 처리용
join_buffer_size = 256K      # JOIN 처리용 (인덱스 없는 경우)
read_buffer_size = 128K      # 순차 읽기용
read_rnd_buffer_size = 256K  # 정렬 후 랜덤 읽기용
thread_stack = 256K          # 스레드 스택

핵심은 총 메모리 사용량 계산 입니다. 총 세션 메모리 ≈ 커넥션별 메모리 × max_connections이므로, 커넥션별 약 2MB를 사용하고 max_connections = 500이면 세션 메모리만 1GB 입니다.

커넥션별 버퍼를 무작정 크게 잡으면 커넥션 수가 늘어날 때 메모리가 폭발합니다. 기본값 근처에서 시작하고, 특정 쿼리에서 문제가 생길 때만 SET SESSION sort_buffer_size = 4M 같이 세션 단위로 올리는 게 안전합니다.

커넥션 관련 변수

max_connections

MySQL이 동시에 허용하는 최대 커넥션 수입니다.

# 기본값: 151
max_connections = 300

적정값을 산정하는 방법은 다음과 같습니다.

• 현재 최대 동시 커넥션 확인: SHOW STATUS LIKE 'Max_used_connections';
• 일반적으로 최대 사용량의 1.5~2배로 설정
• 커넥션 풀(HikariCP 등)을 사용한다면, 풀 크기의 합 + 여유분 으로 계산

-- 현재 커넥션 상태 확인
SHOW STATUS LIKE 'Threads_connected';    -- 현재 연결 수
SHOW STATUS LIKE 'Max_used_connections'; -- 지금까지 최대 연결 수
SHOW STATUS LIKE 'Threads_running';      -- 실제 쿼리 실행 중인 수

Too many connections 에러가 났다고 max_connections만 올리는 건 위험합니다. 커넥션이 많다는 건 쿼리가 느려서 커넥션이 안 반환되는 것 일 수 있습니다. 슬로우 쿼리부터 확인하는 게 먼저입니다.

HikariCP와의 관계도 중요합니다. max_connections ≥ (앱 서버 수 × HikariCP pool size) + 관리용 여유분으로 산정합니다. 예를 들어 앱 서버 3대, 각 maximumPoolSize = 10이면 최소 30 + 여유분이 필요합니다.

wait_timeout / interactive_timeout

유휴 커넥션을 자동으로 끊는 설정입니다.

# 비대화형 커넥션 타임아웃 (기본: 28800초 = 8시간)
wait_timeout = 600          # 10분

# 대화형 커넥션 타임아웃 (mysql CLI 등)
interactive_timeout = 1800  # 30분

기본값 8시간은 너무 깁니다. 커넥션 풀을 사용하는 환경에서는 10~30분 정도로 줄여도 문제없습니다. 단, wait_timeout을 HikariCP의 maxLifetime보다 더 길게 설정해야 합니다. MySQL이 먼저 끊으면 "Connection closed" 에러가 발생합니다.

InnoDB 로그 관련

innodb_log_file_size

InnoDB의 Redo Log 파일 크기입니다. 트랜잭션 변경 사항을 기록하는 Write-Ahead Log(WAL)로, 크래시 복구에 핵심적인 역할을 합니다.

# MySQL 8.0.30 이전
innodb_log_file_size = 1G
innodb_log_files_in_group = 2   # Redo Log 파일 수 (기본: 2)
# 총 Redo Log 크기 = 1G × 2 = 2G

# MySQL 8.0.30+
innodb_redo_log_capacity = 2G   # 통합 설정 (위 두 변수 대체)

Log가 가득 차면 InnoDB는 체크포인트를 강제로 수행하여 dirty page를 디스크에 플러시합니다. 이 동안 쓰기가 멈추기 때문에 성능이 급격히 떨어집니다. 적정 크기의 기준은 1~2시간 분량의 쓰기 로그를 담을 수 있는 크기 입니다. SHOW ENGINE INNODB STATUS에서 Log sequence number의 변화량으로 확인할 수 있습니다.

innodb_flush_log_at_trx_commit

이 변수는 데이터 안전성 vs 쓰기 성능 의 트레이드오프를 결정합니다.

# 기본 (안전 최우선)
innodb_flush_log_at_trx_commit = 1

# 성능 최적화 (대부분의 웹 서비스에서 충분)
innodb_flush_log_at_trx_commit = 2

금융, 결제 등 데이터 유실이 절대 안 되는 시스템은 반드시 1을 유지해야 합니다. 일반 웹 서비스에서 2로 변경하면 쓰기 성능이 극적으로 향상됩니다. 레플리케이션 환경이라면 슬레이브에서 복구할 수 있으므로 2를 선택하는 경우가 많습니다.

쿼리 실행 관련

tmp_table_size / max_heap_table_size

쿼리 실행 중 임시 테이블을 메모리에 생성할 수 있는 최대 크기입니다.

# 두 값 중 작은 쪽이 적용됨 — 반드시 같이 맞춰야 함
tmp_table_size = 64M
max_heap_table_size = 64M

임시 테이블이 이 크기를 초과하면 디스크로 변환되어 성능이 떨어집니다.

-- 디스크 임시 테이블 사용 빈도 확인
SHOW STATUS LIKE 'Created_tmp_disk_tables';
SHOW STATUS LIKE 'Created_tmp_tables';

-- 디스크 비율이 높으면 크기를 늘리거나, 쿼리 자체를 최적화

임시 테이블이 디스크로 넘어가는 원인이 항상 크기 부족은 아닙니다. TEXT, BLOB 컬럼이 포함된 쿼리는 무조건 디스크 임시 테이블을 사용합니다. 변수를 올리기 전에 쿼리 자체를 점검하는 게 먼저입니다.

slow_query_log / long_query_time

느린 쿼리를 자동으로 기록하는 설정입니다. 튜닝의 출발점이라 할 수 있습니다.

# 슬로우 쿼리 로그 활성화
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log

# 기준 시간 (기본: 10초, 실무에서는 1초 이하 권장)
long_query_time = 1

# 인덱스를 사용하지 않는 쿼리도 기록
log_queries_not_using_indexes = ON

# 위 설정이 너무 많은 로그를 남길 때 제한
log_throttle_queries_not_using_indexes = 60  # 분당 최대 60개

-- 운영 중 동적으로 활성화
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;  -- 0.5초 이상 쿼리 기록

슬로우 쿼리 로그를 분석할 때는 Percona Toolkit의 pt-query-digest /var/log/mysql/slow.log 명령을 사용하면 효율적입니다.

환경별 추천 설정 예시

개발 환경 (4GB RAM)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
max_connections = 50
wait_timeout = 300
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2    # 개발이라 성능 우선
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = ON
long_query_time = 0.5
log_queries_not_using_indexes = ON

운영 환경 (32GB RAM, 전용 DB 서버)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-name-resolve                      # DNS 조회 비활성화

# 메모리
innodb_buffer_pool_size = 24G          # 총 메모리의 75%
innodb_buffer_pool_instances = 16
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
sort_buffer_size = 256K
join_buffer_size = 256K

# 커넥션
max_connections = 500
wait_timeout = 600
interactive_timeout = 1800
thread_cache_size = 64

# InnoDB
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1    # 운영은 안전 우선
innodb_flush_method = O_DIRECT         # OS 캐시 바이패스
innodb_io_capacity = 2000              # SSD 기준
innodb_io_capacity_max = 4000

# 쿼리
tmp_table_size = 64M
max_heap_table_size = 64M
slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 60

# 바이너리 로그 (레플리케이션용)
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
sync_binlog = 1

몇 가지 보충 설명을 덧붙이자면 다음과 같습니다.

• skip-name-resolve: DNS 역방향 조회를 건너뛰어 접속이 빨라집니다. 대신 권한 설정에서 호스트명 대신 IP를 사용해야 합니다.
• innodb_flush_method = O_DIRECT: OS 파일 캐시를 건너뛰어 이중 캐싱(Buffer Pool + OS cache)을 방지합니다.
• innodb_io_capacity: 디스크 타입에 따라 조정합니다. HDD는 200400, SSD는 20004000, NVMe는 10000 이상도 가능합니다.

정리

• innodb_buffer_pool_size가 가장 중요합니다. 전용 DB 서버라면 메모리의 70~80%를 할당하고, 히트율 99% 이상을 유지하는지 모니터링합니다.
• max_connections은 계산으로 정합니다. 앱 서버 수 × 풀 크기 + 여유분으로 산정하고, 커넥션별 메모리 소비량도 함께 고려합니다.
• innodb_flush_log_at_trx_commit은 워크로드에 따라 선택합니다. 금융 시스템은 1, 일반 웹 서비스는 2가 합리적인 선택입니다.
• slow_query_log는 항상 켜둡니다. 문제가 생긴 후에 켜는 게 아니라, 처음부터 켜놓고 분석하는 게 중요합니다.
• ** 설정 변경 후에는 반드시 모니터링합니다.** SHOW STATUS, SHOW ENGINE INNODB STATUS, Performance Schema로 변경 효과를 검증합니다.

트래픽 패턴이 바뀌면 설정도 같이 바뀌어야 합니다. 중요한 건 각 변수의 역할을 이해하고, 변경 시 어떤 트레이드오프가 있는지 판단할 수 있는 능력입니다.