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

​

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 = 15000

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

참고
https://m.blog.naver.com/islove8587/221977641268

[MYSQL] 시스템 관련 튜닝