[mysql]innodb 메모리별 설정값 예시


[mysqld]

innodb_sort_buffer_size=64m

innodb_buffer_pool_size=8G

innodb_buffer_pool_instances=32

innodb_write_io_threads=8

innodb_read_io_threads=8



query_cache_type=1

query_cache_size=512M





default_storage_engine = InnoDB
기본 데이타베이스 엔진으로 InnoDB를 사용한다는 것 표시

 

innodb_buffer_pool_size
운영중인 시스템의 DB 크기 이상을 할당.

시스템 메모리의 65%~75% 권장, 시스템 메모리 8GB RAM라면 일반적으로 5~6GB 정도 할당.

buffer pool이 너무 작으면 페이지가 buffer pool에서 플러시 되어 잠시 후 다시 필요하게 되므로 과도한 I/O 가 발생할 수 있으며, 너무 큰 경우 메모리 경쟁으로 스와핑이 발생할 수 있음.

 

innodb_log_file_size
데이타베이스 충돌 발생 시 다시 실행하거나 이전으로 되돌릴 때 사용하는 메모리.

지나치게 크면 복구 시간이 길어지면서 비효율적이 될 수 있음.

위에서 설정한 innodb_buffer_pool_size의 25% 정도 할당.

 

innodb_buffer_pool_instances
인스턴스 수를 늘리면 트랜잭션 간 Lock 경합을 줄일 수 있음.

기본값은 8.

메모리가 많은 시스템에서는 buffer pool을 여러 개 buffer pool instance로 나누어 동시성을 향상 시키는 것이 가능.

 

innodb_flush_log_at_trx_commit
0은 성능 중심, 1은 안정성 중심.

 

innodb_flush_method
O_DIRECT
데이터 읽기/쓰기에 OS 캐시를 사용하지 않다 바로 MySql/MariaDB에서 가져 오겠다는 설정.
쓰기 성능은 나빠질 수 있지만 더블 버퍼링을 막아 메모리를 효율적으로 사용하겠다는 것.

O_DSYNC
데이터 읽기/쓰기에 OS 캐시를 사용.
속도는 더 빠르지만 대기 시간, 충돌로 데이타가 일관적이지 않을 수 있다고 함.

 

innodb_io_capacity
InnoDB 변경 성능은 플러쉬 속도, 즉 스토리지 I/O 속도에 의존하므로 빠른 스토리지 사용 필요

현재 사용하고 있는 디스크의 IOPS와 유사한 값 설정

SSD와 같이 속도가 빠른 스토리지는 값을 올리고, 일반 HDD라면 값을 내린다.


추천 셋팅값

innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_stats_on_metadata = 0
innodb_io_capacity = 15000html


InnoDB 관련 메모리별 my.cnf설정 예시

8기가innodb_additional_mem_pool_size = 8M
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4
innodb_flush_log_at_trx_commit = 1
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 8
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_io_capacity = 200
16기가innodb_additional_mem_pool_size = 8M
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 1
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 12
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 200
32기가innodb_additional_mem_pool_size = 8M
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 16
innodb_flush_log_at_trx_commit = 1
innodb_flush_method=O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_io_capacity = 200
0
0
이 글을 페이스북으로 퍼가기 이 글을 트위터로 퍼가기 이 글을 카카오스토리로 퍼가기 이 글을 밴드로 퍼가기

Database

번호 제목 글쓴이 날짜 조회수
5 [MySQL] REGEXP_REPLACE 한글, 영문, 특수문자, 숫자 제거 관리자 01-19 139
4 [mysql]innodb 메모리별 설정값 예시 관리자 10-13 312
3 MySQL 중복 레코드 관리 방법 관리자 09-10 222
2 sleep session 정리하기 관리자 07-31 251
1 MYSQL 8 에서 PASSWORD 함수 사용하기 관리자 04-18 556