MySQL: 메모리 파라미터

※ MySQL: Memory Parameter.

※ Version: MySQL 8.4.

 

안녕하세요. 듀스트림입니다.

 

오늘 포스팅은 MySQL의 메모리 파라미터에 대한 내용입니다.

MySQL :: MySQL 8.4 Reference Manual :: 17 The InnoDB Storage Engine

---

설정 확인 방법

-- 서버 전체 메모리 관련 설정 확인
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%cache%';
SHOW VARIABLES LIKE '%memory%';

-- 글로벌 설정
SELECT @@GLOBAL.innodb_buffer_pool_size,
       @@GLOBAL.innodb_log_buffer_size,
       @@GLOBAL.key_buffer_size,
       @@GLOBAL.tmp_table_size,
       @@GLOBAL.max_heap_table_size;

-- 세션별 설정
SELECT @@SESSION.sort_buffer_size,
       @@SESSION.read_buffer_size,
       @@SESSION.join_buffer_size;

 

+ 메모리 사용량 추적

SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS db_size_mb
FROM information_schema.tables
WHERE engine='InnoDB';

 

++ InnoDB 상태 확인

SHOW ENGINE INNODB STATUS\G

---

1. InnoDB(서버, mysqld) 메모리

• innodb_buffer_pool_size
  
  • 기본값: 128MB.
  • 권장 크기는 시스템 메모리의 50–75%. 동적으로 조정 가능.
  • 설명: InnoDB 테이블/인덱스 캐시의 핵심 메모리 공간. 크기가 클수록 디스크 I/O 감소.
  • 튜닝 가이드:
    
    • 전용 DB 서버면 RAM 50–75%로 시작 후, 버퍼 풀 히트율과 I/O(특히 Innodb_buffer_pool_reads)를 보며 조정.
    • 온라인 리사이즈 시 chunks × instances 배수 제약(아래 두 변수) 준수.

MySQL :: MySQL 8.4 Reference Manual :: 17.8.3.1 Configuring InnoDB Buffer Pool Size

 

• innodb_buffer_pool_instances
  
  • 기본값:
    • 버퍼 풀 ≤ 1GiB면 1.
    • 1GiB 초과면 “(버퍼풀/청크)/2”와 “CPU 논리 프로세서/4” 중 작은 값(1–64)을 적용.
  • 각 인스턴스는 ≥1GiB 권장.
  • 설명: 버퍼 풀을 여러 파티션으로 나눠 동시성 향상. 1GiB 미만이면 효과 적음.
  • 튜닝 가이드: 대용량 버퍼 풀(수 GB~수십 GB)에서 8–16부터 시작, 경합(뮤텍스 대기)과 페이지 클리너 스레드 수(아래)를 보며 조정.

 

• innodb_buffer_pool_chunk_size
  
  • 기본값: 128MB.
  • 설명: 버퍼 풀 리사이즈 작업 단위를 정의. size = chunk_size × instances × N 배수 조건.
  • 튜닝 가이드:
    • 일반적으로 기본값 유지.
    • 리사이즈 단위가 너무 커서 조정 불편할 때만 변경.

 

• innodb_log_buffer_size
  
  • 기본값: 64MB.
  • 설명: 트랜잭션 중 생성되는 로그 레코드를 메모리에 버퍼링. 큰 LOB/대량 변경 시 디스크 플러시 빈도 감소.
  • 튜닝 가이드: 커밋 간에 자주 플러시가 발생하거나 LOB 업데이트가 많다면 128–512MB로 점진 확대 후 체크.

 

• innodb_redo_log_capacity
  
  • 기본값: 1GB. (innodb_dedicated_server 사용 시 CPU 수 기반으로 최대 16GB까지 자동 산정)
  • 설명:
    • InnoDB redo 로그 총 용량(이전의 파일+개수 조합 대체).
    • 커질수록 체크포인트 압박↓, 대량 변경 안정성↑.
  • 튜닝 가이드: 대용량 배치·DDL·플리카 초기 동기화가 많다면 4–16GB 고려. (체크포인트/플러시 지표를 보며 조정)

 

• innodb_dedicated_server
  
  • 기본값: OFF.
  • ON으로 설정하면, 서버 메모리/CPU를 감지해 버퍼 풀과 redo 용량을 자동 산정.
  • 설명: MySQL 서버에 자동 메모리 사이징을 적용.
  • 튜닝 가이드: 전용 호스트면 ON으로 시작 → 워크로드에 맞춰 세부 변수 수동 미세조정.

 

• innodb_page_cleaners
  
  • 기본값: 페이지 클리너 스레드는 버퍼풀 인스턴스 수와 동일.

 

• innodb_max_dirty_pages_pct(_lwm)
  
  • 기본값: LWM 기본 10%, 상한 90%.
  • 설명: 버퍼 풀 더티 페이지 플러시 제어.
  • 튜닝 가이드:
    • 쓰기 작업이 많을 시 LWM을 5–10%로 유지.
    • 상한 80–90%. 디스크 쓰기 병목이면 I/O 옵션과 함께 조정.

---

2. 내부 임시 테이블(Temp Table) 메모리

• internal_tmp_mem_storage_engine
  
  • 기본값: TempTable(내부 임시 테이블용).
  • 설명: 내부 임시 테이블은 우선 메모리를 사용하고 한도 초과 시 디스크(InnoDB)로 스필.

 

• tmp_table_size / max_heap_table_size
  
  • 기본값: 각 16MB. (두 값 중 더 낮은 값이 메모리 임시 테이블 최대 크기를 제한)
  • 설명: 내부(TempTable)·사용자 MEMORY 테이블의 메모리 한도. (초과하면 디스크 테이블로 전환되어 느려짐)
  • 튜닝 가이드: 정렬/집계가 큰 쿼리가 많고 스필이 잦으면 둘 다 동일하게 64–256MB 범위로 올려 테스트. (너무 크게 잡으면 동시성에서 메모리 폭증 위험)

 

• temptable_max_ram
  
  • 기본값:
    
    • MySQL 8.0 계열: 1 GB 또는 전체 메모리의 3% 중 작은 값 (최소 1MB, 최대 4GB).
    • MySQL 8.4: 기본 계산식이 약간 변경되어, 메모리 32GB 이상이면 더 큰 기본값을 잡도록 개선됨.
  • 설명:
    
    • TempTable 스토리지 엔진(내부 임시 테이블용)이 메모리에 사용할 수 있는 총 용량 한도.
    • 이 값을 초과하는 데이터는 temptable_max_mmap(활성 시) 또는 디스크(InnoDB 임시 테이블)로 넘어감.
  • 튜닝 가이드:
    
    • 시스템 메모리가 충분하다면 기본보다 크게 잡아 **임시 쿼리(정렬, GROUP BY, DISTINCT 등)**에서 디스크 스필을 줄임.
    • 단, 이 값은 글로벌 상한이므로 동시 다중 세션이 큰 TempTable을 만들 경우 메모리 사용량이 폭증할 수 있
    • 음 → Created_tmp_disk_tables 지표를 보고 점진적으로 조정.

 

• temptable_max_mmap
  
  • 기본값:
    
    • MySQL 8.0 계열: 1 GB.
    • MySQL 8.4: 기본값이 변경되었으며, temptable_use_mmap 옵션이 제거되면서 mmap 사용 여부는 이 값 > 0 여부로만 제어됨.
  • 설명:
    
    • TempTable 엔진이 메모리 한도(…_max_ram)를 초과했을 때 추가 데이터를 mmap 파일로 저장할 수 있는 상한.
    • mmap은 디스크 파일을 가상 메모리에 매핑하는 방식이라 메모리처럼 접근하지만 결국은 디스크 I/O 발생.
  • 튜닝 가이드:
    
    • 임시 디렉터리 공간이 여유롭고, 디스크 기반 스필을 허용하려면 기본값 유지.
    • 임시 디렉터리 공간이 부족하거나 스왑·OOM이 우려되면 temptable_max_mmap=0으로 설정해 mmap을 비활성화.

 

• temptable_use_mmap
  
  • 기본값:
    
    • MySQL 8.0에서 존재했으며 기본은 ON.
    • MySQL 8.4에서는 제거(Deprecated). (temptable_max_mmap=0을 설정하는 것이 사실상 동일한 동작)
  • 설명:
    
    • TempTable이 mmap 기반 스토리지를 사용할지 여부를 단순히 켜고 끄는 스위치 역할.
  • 튜닝 가이드:
    
    • 8.4 이상에서는 이 변수를 사용하지 않고, temptable_max_mmap 값으로 제어해야 함.
    • 기존 환경에서 업그레이드 시 temptable_use_mmap=OFF를 쓰던 경우, 업그레이드 후에는 temptable_max_mmap=0으로 대체 설정해야 동일 동작을 보장.

---

3. 바이너리 로그·패킷

이 파라미터들은 복제/클러스터에도 영향이 있습니다.

 

• binlog_cache_size / binlog_stmt_cache_size
  
  • 기본값: 일반적으로 32KB.
  • 설명:
    
    • binlog_cache_size: 트랜잭션에서 실행된 DML(SQL 문장)을 커밋 전까지 메모리에 캐시.
    • binlog_stmt_cache_size: 비트랜잭션 문장(DDL, AUTOCOMMIT 등)을 캐시.
    • 캐시 크기를 초과하면 디스크의 임시 파일을 사용 → 성능 저하.
  • 튜닝 가이드:
    
    • 상태 변수 Binlog_cache_use, Binlog_cache_disk_use, Binlog_stmt_cache_use, …_disk_use를 확인.
    • …_disk_use > 0이 의미 있게 나타나면 캐시를 64–512KB 정도로 점진 확대.
    • 단, 세션마다 할당되는 값이므로 연결 수가 많으면 전체 메모리 사용량에 유의.

 

• max_binlog_cache_size
  
  • 기본값: 4GB. (이를 초과하는 트랜잭션은 오류 발생)
  • 설명:
    
    • 하나의 트랜잭션이 사용할 수 있는 binlog 캐시의 최대 크기. 대용량 트랜잭션 방지를 위한 안전장치 역할.
  • 튜닝 가이드:
    
    • 대용량 트랜잭션이 불가피하다면 INSERT … SELECT나 BLOB 처리 시 이 값을 늘릴 수 있음.
    • 하지만 너무 크게 잡으면 단일 세션이 GB 단위 메모리를 점유 → OOM 위험.
    • 권장 방식은 애플리케이션에서 트랜잭션을 쪼개 처리.

 

• max_allowed_packet
  
  • 기본값: 서버 기준 64MB (최대 1GB).
  • 설명:
    
    • 클라이언트 ↔ 서버 간 전송 가능한 단일 패킷의 최대 크기.
    • 대형 BLOB, JSON 문서, 복제 이벤트 크기에 직접 영향.
    • 값이 부족하면 에러 1236(Replication Error) 발생 가능.
  • 튜닝 가이드:
    
    • 대용량 데이터를 다루는 경우 128MB~1GB까지 상향 조정.
    • 반드시 소스 서버, 리플리카 서버, 클라이언트 값이 동일해야 함.

---

4. Group Replication (InnoDB Cluster) 메모리

• group_replication_message_cache_size
  
  • 기본값: 1GB (+ 내부 오버헤드 50MB 추가).
  • 설명:
    
    • 그룹 내에서 커밋/전파 중인 트랜잭션 메시지를 임시로 저장하는 캐시.
    • 네트워크 지연·큰 트랜잭션이 몰릴 경우 사용량 급증.
  • 튜닝 가이드:
    
    • performance_schema.replication_group_communication_* 테이블에서 사용량 모니터링.
    • 필요 시 2–4GB까지 확대.
    • 메모리가 부족하다면 대형 트랜잭션을 애플리케이션에서 분할하는 편이 더 안전.

 

• group_replication_communication_max_message_size
  
  • 기본값: 10MB.
  • 설명:
    
    • 그룹 통신 계층(XCom)이 단일 메시지로 처리할 수 있는 최대 크기.
    • 이 값이 작으면 대형 트랜잭션이 여러 메시지로 분할되어 성능 저하 가능.
  • 튜닝 가이드:
    
    • 대형 트랜잭션을 허용해야 한다면 group_replication_transaction_size_limit와 함께 상향.
    • 단, 너무 크게 잡으면 네트워크 MTU·전송 지연에 악영향.

 

• group_replication_transaction_size_limit
  
  • 기본값: 150,000,000 bytes (≈143MB).
  • 설명:
    
    • 그룹이 합의·전송 가능한 단일 트랜잭션 최대 크기.
    • 너무 크면 합의 지연·재전송 비용 증가.
  • 튜닝 가이드:
    
    • 정말 필요한 경우만 소폭 증설.
    • 가능하다면 애플리케이션에서 트랜잭션을 쪼개는 것이 권장.

 

• replica_pending_jobs_size_max
  
  • 기본값: 128MB (기본 max_allowed_packet 64MB의 2배).
  • 설명:
    
    • 멀티스레드 리플리케이션(MTS)에서 적용 대기 중인 이벤트 큐의 총 메모리 상한.
  • 튜닝 가이드:
    
    • 대형 이벤트·병렬 적용이 많을 때는 256–1024MB로 확장 가능.
    • 큐 가득참으로 인한 슬레이브 지연을 모니터링하며 점진 조정.

---

+ max_connections (서버 동시 접속 수)

• 기본값: 151.
• 설명:
  
  • mysqld가 동시에 허용하는 클라이언트 연결 수의 상한.
  • 내부 스레드와 예약 연결(super_read_only 세션용) 등을 감안하여 실제 허용 수는 max_connections보다 약간 적게 동작.
  • 연결 수만큼 **세션 버퍼(sort_buffer_size, join_buffer_size, read_buffer_size, 등)**가 할당되므로, 과도한 값은 메모리 폭증으로 이어질 수 있음.
  • 연결이 상한에 도달하면 신규 접속은 "Too many connections" 에러 발생.
• 튜닝 가이드:
  
  • 워크로드 특성 확인
    
    • OLTP(짧은 트랜잭션)라면 수천 개 커넥션까지 필요할 수 있음.
    • OLAP(긴 쿼리 위주)라면 상대적으로 낮아도 충분.
  • 메모리 영향 고려
    
    • max_connections × 세션 버퍼(예: sort/join/read) = 잠재 메모리 사용량.
    • 따라서 max_connections를 늘릴수록 세션 메모리 파라미터를 더 보수적으로 잡아야 안전.
  • 실제 접속 패턴 기반 설정
    
    • SHOW STATUS LIKE 'Threads_connected';
    • SHOW STATUS LIKE 'Max_used_connections'; 로 최고 동시접속 수를 확인.
    • 피크 시점 + 20~30% 여유를 두고 설정하는 것이 실무 권장.
  • 연결 관리
    
    • 커넥션 풀(HikariCP, ProxySQL, MySQL Router 등)을 적극 활용해 불필요한 연결 폭증을 막는 것이 가장 효과적.
    • 임시로 높은 값을 주는 것보다 안정적인 풀링 구성이 중요.

---

++ MySQL Router 사용 시

• max_total_connections
  
  • 기본값: 512.
  • 설명:
    
    • Router가 동시에 허용할 수 있는 최대 연결 수. 연결이 많을수록 Router 프로세스 메모리 사용량도 증가.
  • 튜닝 가이드:
    
    • 피크 동접 수와 Router 인스턴스 수를 고려해 분산.
    • OS 레벨 ulimit -n(파일 디스크립터 수)도 반드시 함께 조정.

---

운영 팁

• 버퍼 풀 사이징:
  • 전용 호스트면 50–75%부터 시작.
  • 8.4의 자동화(innodb_dedicated_server)를 활용해 초기값을 잡고 미세조정.
• 임시 테이블 스필:
  • tmp_table_size/max_heap_table_size를 동일값으로 올려 스필을 줄이되, 동시성 메모리 폭증 주의.
• 바이너리 로그 캐시:
  • Binlog_cache_use/…_disk_use 상태변수로 “디스크 사용 0에 근접”하도록 최소한만 상향.
• GR 대형 트랜잭션:
  • 가능하면 트랜잭션을 쪼개는 것이 캐시/메시지 사이즈 증설보다 안전.
  • 그래도 필요하면 message_cache_size·communication_max_message_size·transaction_size_limit을 함께 조정.

---

오늘은 여기까지~