본문 바로가기
Research Notes (개인)

MySQL my.cnf 운영/로컬 설정 가이드 (대량 적재 중 binlog 오류로 ABORT_SERVER 다운 포함)

이 문서는 로컬(Local) 과 운영(Production) 환경에서의 MySQL 설정(my.cnf)을 분리해 관리하는 방법과, 이번에 발생한 아래 장애를 어떻게 이해/예방할지 정리한 플레이북이다.

OperationalError (1598): Binary logging not possible ... during sync stage of the commit ... binlog_error_action='ABORT_SERVER'. Server is being stopped.
-> 대량 적재 중 커밋 단계에서 binlog fsync/sync 실패 -> 정책상 mysqld가 서버를 중지(ABORT_SERVER)

1) 장애 요약: 왜 대량 적재 중 MySQL이 다운됐나?

어떤 일이 일어났나

  • ingest 스크립트가 conn.commit() 하는 순간,
  • MySQL이 binlog를 디스크에 동기화(sync/fsync) 하는 단계에서 오류 발생
  • binlog_error_action=ABORT_SERVER라서 MySQL은 즉시 서버 종료를 선택
  • 클라이언트(PyMySQL)는 1598 에러를 받음

왜 이런 오류가 발생하나 (대부분 "스토리지/파일시스템" 문제)

binlog sync 실패는 보통 아래 중 하나로 발생한다.

  • 디스크 용량 부족 / inode 부족
  • 파일시스템 read-only 전환
  • I/O 에러(EIO) / 스토리지 일시 장애
  • 컨테이너(overlayfs)/볼륨 이슈
  • binlog 경로/권한 문제

대량 적재는 쓰기량과 커밋 빈도를 폭증시키기 때문에, 스토리지의 작은 흔들림도 바로 장애로 연결될 수 있다.

핵심 교훈

  • 로컬: 대부분 binlog/복제/CDC가 필요 없으니 binlog를 끄는 것이 정답에 가깝다
  • 운영: 복제/CDC/PITR이 필요하면 강내구성 유지 + 적재 방식(배치/스테이징/로드 윈도우)으로 해결해야 한다

2) 로컬(Local) 프로파일 - "빠르고 안정적인 대량 적재" 목표

사용 상황

  • Docker 로컬 MySQL
  • 대량 데이터 ingest를 반복 실행
  • binlog/복제/CDC 불필요 (재생성 가능 데이터)

원칙

  • binlog를 아예 꺼서 1598/ABORT_SERVER 루트 제거
  • redo fsync 압력을 낮춰 ingest 속도/안정성 향상
  • 로컬 메모리/도커 메모리 제한을 고려해 buffer pool 과다 할당 금지

my.local.cnf (권장)

[mysqld]
# --- Basics ---
server_id=1
port=3306
skip_name_resolve=ON
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

# --- Local ingest speed (내구성보다 속도/안정) ---
# commit마다 redo를 디스크까지 fsync 하지 않음 (속도↑)
innodb_flush_log_at_trx_commit=2

# 로컬에서는 보통 binlog가 불필요 -> 완전히 끔 (ABORT_SERVER 트리거 제거)
skip_log_bin

# --- Memory / IO (로컬 RAM / Docker 제한에 맞춰 조정) ---
# 도커/로컬에서 12G 고정은 위험. 보수적으로 시작 후 올려라.
innodb_buffer_pool_size=4G
innodb_redo_log_capacity=1G

innodb_flush_neighbors=0
innodb_io_capacity=1000
innodb_io_capacity_max=2000

# --- Connections ---
max_connections=100
max_allowed_packet=256M

# --- Logging: 대량 적재 시 불필요한 디스크 쓰기 줄이기 ---
slow_query_log=OFF
log_queries_not_using_indexes=OFF

로컬 주의사항

  • Docker Desktop(mac/Windows)은 디스크 I/O가 병목이 되기 쉬움 -> buffer_pool 과다 할당 금지
  • innodb_flush_method=O_DIRECT는 Linux+SSD에서는 유효한 경우가 많지만, 로컬 도커 환경에서는 이득이 없거나 문제를 만들 수 있어 로컬은 기본값 유지를 권장
  • 적재 중에는 slow log를 끄는 게 보통 더 빠름(필요할 때만 ON)

3) 운영(Production) 프로파일 - "안전/내구성/복제" 목표

사용 상황

  • 서비스 트래픽 처리
  • replication/CDC/PITR 요구
  • 데이터 유실 최소화(RPO 최소)

원칙

  • 강내구성 기본값 유지
  • binlog 오류 시 fail-fast(ABORT_SERVER) 정책은 운영에서는 "안전장치"일 수 있음
  • 대량 적재는 설정 약화보다 적재 방식(스테이징+배치 MERGE) 로 해결

my.prod.cnf (기본 안전 세팅)

[mysqld]
# --- Basics / Safety ---
server_id=1
port=3306
skip_name_resolve=ON
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

# --- Durability (강내구성) ---
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# --- InnoDB Memory / IO (인스턴스 스펙에 맞게 튜닝) ---
# 예시: 전용 DB 서버에서 RAM의 60~75% 수준을 buffer pool로 (절대 복붙 금지)
innodb_buffer_pool_size=12G
innodb_redo_log_capacity=2G

innodb_flush_neighbors=0
innodb_io_capacity=2000
innodb_io_capacity_max=4000

# Linux+SSD에서 흔히 권장. 환경에 따라 검증 필요.
innodb_flush_method=O_DIRECT

# --- Connections / Limits ---
max_connections=250
wait_timeout=60
interactive_timeout=600
max_allowed_packet=256M

# --- Observability ---
slow_query_log=ON
long_query_time=0.5
log_queries_not_using_indexes=OFF
log_error=/var/lib/mysql/error.log

# --- Binlog (Replication/CDC) ---
binlog_format=ROW
binlog_row_image=MINIMAL

# MySQL 8: expire_logs_days 대신 seconds 권장
binlog_expire_logs_seconds=604800

4) 운영에서 "대량 적재 모드" (권장: 일시 적용 + 원복)

운영에서도 대량 적재가 빈번할 수 있다. 이 경우 핵심은:

  • 평상시 운영 설정은 강내구성 유지
  • 대량 적재 "윈도우" 동안만 일시 완화(절충) + 배치 반영으로 부하를 통제
  • 작업 후 즉시 원복

옵션 A (권장 절충): binlog 유지 + sync 압력만 낮추기

# 대량 적재 윈도우 동안만
innodb_flush_log_at_trx_commit=2
sync_binlog=100

옵션 B (다운만은 피하겠다): IGNORE_ERROR (주의)

# binlog에 문제가 생겨도 서버를 죽이지 않음
# 다만 replication/CDC/PITR 무결성이 깨질 수 있어 운영에서는 신중히 사용
binlog_error_action=IGNORE_ERROR

운영에서 ABORT_SERVER는 "원인 제거(스토리지 안정화)"가 최우선이다.
단순히 IGNORE로 바꿔 "안 죽게" 만드는 건 replication/CDC를 조용히 깨뜨릴 수 있다.

5) ABORT_SERVER 다운을 "진짜로" 막는 방법

A) 근본 원인(스토리지)을 먼저 잡아야 한다

이번 1598/ABORT_SERVER는 설정만의 문제가 아니라, 대부분 아래 요인에서 시작된다.

  • 디스크 용량/인오드 부족
  • read-only 전환
  • I/O 에러/스토리지 불안정
  • 도커 볼륨/호스트 디스크 상태

운영 체크리스트

  • 디스크 사용량/인오드 모니터링
  • filesystem read-only 이벤트 알림
  • MySQL datadir/binlog 디렉토리의 안정적 스토리지 보장
  • 컨테이너 환경이면 볼륨/호스트 디스크 여유 확보

B) 커밋/flush 빈도를 낮춰라 (설정보다 "적재 패턴"이 1순위)

강내구성(1+1)을 유지하더라도, 아래만 해도 안정성이 크게 올라간다.

  • 배치 커밋: 1행 1커밋 금지, 5천~2만 행 단위 커밋
  • 스테이징 + LOAD DATA + MERGE
    • stage에는 인덱스 최소로 LOAD DATA로 빠르게 적재
    • main 반영은 batch MERGE로 "조금씩" 반영(부하를 throttle)

6) 로컬/운영 설정 분리 운영 방식(권장)

권장 구조

  • my.base.cnf : 공통(캐릭셋, sql_mode 등)
  • my.local.cnf : 로컬(ingest 최적, binlog off)
  • my.prod.cnf : 운영(강내구성, binlog on)

Docker Compose에서 적용 예

  • 로컬:
    • ./mysql/conf.d에 my.local.cnf 마운트
  • 운영:
    • (자가호스팅) my.prod.cnf 적용
    • (Aurora/RDS) my.cnf 대신 Parameter Group으로 동일 개념을 관리

7) 빠른 결론(실전 요약)

로컬에서 대량 적재가 느리거나 다운난다

  • skip_log_bin + innodb_flush_log_at_trx_commit=2
  • buffer_pool은 로컬 RAM/도커 제한에 맞게(과다 할당 금지)
  • slow log는 적재 중 OFF
  • 가능하면 LOAD DATA + stage + merge로 ingest 경로 자체를 벌크로 전환

운영에서 대량 적재가 빈번하다

  • 운영 기본은 강내구성(1+1) 유지
  • 적재 윈도우 동안만 절충(2 + sync_binlog=100) + 배치 반영
  • ABORT_SERVER는 "원인 제거(스토리지 안정화)"가 최우선
  • 가장 안정적인 구조는 stage로 bulk load 후 main 반영을 배치로 throttle 하는 방식