백엔드 서버 개발자가 알아야할 MySQL 설정/지식

서론 

개발자로서 경력이 쌓여가면서 SQL 쿼리를 공부해서 MySQL은 사용할 수 있고 실행 계획을 고려해서 최적화도 하고 JPA, JDBC 설정을 통해서 하긴하는데 결국 인프라적으로 커넥션풀 설정이나 timeout 관련되어 어떻게 해야하는지에 대한 부족함이 늘 있었습니다. 그래서 이번에 한 번 정리해보았습니다.

Spring에서 MySQL 스레드풀을 최적화하는 이유

일반적인 문제점

• "Connection is not available, request timed out" → 커넥션 풀 부족
• "Too many connections" → DB가 감당할 수 없는 과도한 커넥션 발생
• DB 부하 증가 → CPU 사용률 증가 및 서비스 응답 시간 지연
• 스레드 블로킹 발생 → 커넥션 풀에서 오래 기다리며 병목 발생

HikariCP 설정 최적화 (Spring Boot 기본 커넥션 풀)

Spring Boot에서는 기본적으로 HikariCP를 사용하며, MySQL과의 연결을 최적화하려면 적절한 커넥션 풀 설정이 필요함.

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?serverTimezone=UTC&useSSL=false
    username: user
    password: password
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
      maximum-pool-size: 30  # (1) 커넥션 풀 최대 크기
      minimum-idle: 10       # (2) 최소 유휴 커넥션 개수
      idle-timeout: 30000    # (3) 커넥션이 유휴 상태일 때 유지 시간 (30초)
      max-lifetime: 600000   # (4) 커넥션 최대 수명 (10분)
      connection-timeout: 2000 # (5) 커넥션 요청 대기 시간 (2초)
      validation-timeout: 5000  # (6) 커넥션 유효성 체크
      keepalive-time: 300000  # (7) 커넥션이 닫히지 않도록 유지 (5분마다 ping)
      auto-commit: false # (8) AutoCommit 비활성화
      leak-detection-threshold: 15000 # (8) 15초 이상 실행되는 SQL은 누수로 감지

 

각 설정값의 역할

설정	설명	최적화 포인트
maximum-pool-size	커넥션 풀에서 사용할 최대 커넥션 개수	너무 작으면 병목, 너무 크면 DB 부담 동시 요청 수에 맞게 설정 (보통 CPU 코어 수 * 2)
minimum-idle	풀에서 유지할 최소 유휴 커넥션 개수	트래픽이 급격히 증가하는 경우 대비
idle-timeout	커넥션이 유휴 상태일 때 닫기까지 대기하는 시간	너무 길면 리소스 낭비, 너무 짧으면 자주 재연결 오래된 커넥션 낭비 방지
max-lifetime	커넥션이 강제로 종료되기 전까지 유지되는 시간	MySQL wait_timeout보다 짧게 설정 느린 응답 대비
connection-timeout	커넥션을 얻을 때까지 대기하는 최대 시간	너무 길면 서비스 응답 지연 장시간 유지되는 Connection 방지
validation-timeout	Connection 유효성 검사 시간	느린 응답 대비
keepalive-time	Connection을 강제로 유지	장시간 유지되는 Connection 방지
auto-commit: false	AutoCommit 비활성화	명시적인 트랜잭션 관리 가능
leak-detection-threshold	쿼리 실행이 너무 오래 걸리면 로그 기록	커넥션 누수 방지

 

MySQL 설정 최적화 (my.cnf 또는 my.ini 조정)

MySQL 자체의 스레드 풀 및 연결 관련 설정을 최적화하면 스레드 생성 비용을 줄이고, 동시 접속 요청을 효율적으로 처리 가능함.

MySQL 설정 예제 (/etc/mysql/my.cnf 또는 my.ini)

[mysqld]
max_connections = 500  # (1) MySQL 최대 동시 접속 수 증가
thread_cache_size = 100  # (2) 스레드 캐싱을 통해 생성 비용 감소
innodb_thread_concurrency = 16  # (3) InnoDB 엔진에서 병렬 실행 최적화
innodb_read_io_threads = 8  # (4) 읽기 I/O 병렬 처리 증가
innodb_write_io_threads = 8  # (5) 쓰기 I/O 병렬 처리 증가
innodb_flush_log_at_trx_commit = 2  # (6) 트랜잭션 커밋 최적화 (안전하게 조정)

참고) MySQL 8.0에서는 MySQL 쿼리 캐시 기능 삭로 사라진 설정 (데이터 정합성 이슈 때문에)

query_cache_size = 64M 
query_cache_type = 1

🔥 중요 설정 최적화

설정 값	설명	최적화 포인트
max_connections	MySQL이 허용하는 최대 동시 연결	HikariCP maximum-pool-size의 20배 정도로 설정
thread_cache_size	스레드 재사용 가능하게 설정	요청마다 새로운 스레드를 생성하는 오버헤드 방지
innodb_thread_concurrency	InnoDB 동시 실행 스레드 제한	CPU 코어 수에 맞춰 조정 (16~32 추천)
innodb_read_io_threads / write_io_threads	MySQL 읽기/쓰기 성능 향상	I/O 병목 해결

JDBC 최적화 (Spring + HikariCP)

HikariCP는 기본적으로 빠르지만, JDBC 레벨에서 추가 최적화가 필요함.

JDBC Connection의 성능 최적화

try (Connection connection = dataSource.getConnection();
     PreparedStatement ps = connection.prepareStatement(SQL)) {
    ps.setFetchSize(500); // (1) 한 번에 500개씩 가져오기 (Batch Fetch)
    ps.setQueryTimeout(2); // (2) 2초 이상 걸리는 쿼리 자동 취소
    ResultSet rs = ps.executeQuery();
    
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
}

🔥 JDBC 최적화 포인트

설정	설명	적용 이유
setFetchSize(500)	한 번에 가져올 데이터 수	네트워크 I/O 감소
setQueryTimeout(2)	2초 이상 걸리는 쿼리 취소	DB Timeout 방지
PreparedStatement	미리 컴파일된 SQL 사용	Query Performance 향상

 

분산 환경에서 HikariCP 최적화

대규모 트래픽을 처리하는 분산 환경에서는 HikariCP를 단순히 로컬 설정만으로 최적화하는 것이 불가능.
아래와 같은 분산 환경 최적화 전략이 필요함.

1. Connection Pool을 분산 환경에 맞게 조정

• 동일한 maximum-pool-size 설정은 위험
  • 각 노드가 50개의 Connection을 유지하면 100개 노드에서 5000개 Connection을 사용할 수 있음
  • 해결책: 노드별 트래픽에 따라 maximum-pool-size 동적 조정

2. Read/Write 분리 (Master-Slave DB)

• 쓰기 (INSERT, UPDATE, DELETE) → Master DB
• 읽기 (SELECT) → Replica DB (Slave)

spring:
  datasource:
    url: jdbc:mysql://master-db:3306/mydb
  datasource-slave:
    url: jdbc:mysql://slave-db:3306/mydb

3. Connection Pool 공유 (Cloud 환경)

• Kubernetes 환경에서는 Connection Pool을 Pod 간 공유하는 것이 성능에 유리.
• AWS RDS Proxy, PgBouncer, ProxySQL 같은 Connection Pooler 사용.

Connection Pool 모니터링 및 성능 튜닝

HikariCP 상태 모니터링

HikariDataSource dataSource = (HikariDataSource) applicationContext.getBean(DataSource.class);

System.out.println("활성 커넥션: " + dataSource.getHikariPoolMXBean().getActiveConnections());
System.out.println("대기 중인 커넥션 요청: " + dataSource.getHikariPoolMXBean().getThreadsAwaitingConnection());

분석 포인트

• getActiveConnections() → 현재 사용 중인 커넥션 개수
• getThreadsAwaitingConnection() → 너무 크면 maximum-pool-size를 늘릴 필요 있음
• getTotalConnections() → 전체 커넥션 개수 확인

 

HikariCP의 내부 구조와 동작 원리

HikariCP는 Spring Boot 기본 Connection Pool이며, 다른 Pool 대비 성능

HikariCP가 빠른 이유

1. Connection 객체 재사용
   • MySQL 연결을 새로 생성하는 대신, 기존 Connection을 재사용하여 불필요한 네트워크 비용을 줄임.
2. ConcurrentBag 알고리즘
   • 스레드 간 동기화 부담 없이 Connection을 공유하는 데이터 구조 사용.
3. Optimized Query Execution
   • Statement Caching(SQL 쿼리 재사용) & Lazy Query Execution(필요할 때 실행) 최적화.
4. Timeout & Leak Detection
   • 오래 걸리는 쿼리를 감지하고 Dead Connection 자동 감지 및 제거.

 

참고

• HikariCP를 이해하면 풀 사이즈 설정이 보인다
• HikariCP Down the Rabbit Hole (역사 보기)