MySQL 이노디비 튜닝 포인트 관련된것
● innodb_buffer_pool_chunk_size
기본값은 128M 이며 버풀 사이즈를 결정하는데 역활을 한다.
코어수가 적다면 이 값을 늘려서 버퍼풀의 크기를 늘려 줘야 한다는 논리가 발생 한다.
예를 들면 4G 인경우 코어가 1개 이면 - 인스턴스는 2개로 설정이 된다.
이것을 512M로 잡으면 버퍼풀의 크기는 1G가 된다. - 1G로 잡으면 버퍼풀의 크기는 2G
● innodb_buffer_pool_size
- 디스크 액세스를 줄이기 위한 캐쉬 역활. 로그파일 기록 순서 조정 역활
- 비어 있는 메모리의 60% 정도를 할당 하는것을 권장 한다.
- 메모리가 크게 할당 되어 있다면 인메모리 처럼 이노디비가 작동 한다.
- 색인 처리가 잘 되었는데 슬로우 쿼리가 있다면 이 버퍼풀 크기를 확인해 보는게 좋다.
- 참고할것은
innodb_buffer_pool_size = innodb-buffer-pool-instances * innodb_buffer_pool_chunk_size
이와 같지 않게 구성한다면 버퍼 풀 크기는 자동으로 innodb-buffer-pool-instances * innodb_buffer_pool_chunk_size와
같거 조정이 되거나 시스템에 의해 자동 적용 된다.
즉, 코어수가 적다면 인스턴수 지정 수치가 작아지므로 청크사이즈 값을 늘려 한다는 것이 된다.
● innodb_log_file_size
- 버퍼풀의 25%가 적당 하다. 즉, 버퍼풀의 사이즈가 2G 이면 이건 512 정도가 적당 하다는 것이다.
- 여기서 무조건 25% 설정이 아니라. 256M 이상 크게 되면 충돌 발생시 복구 하는데 오래걸린다고 최대값을 256M로 하는것을 권장 한다.
- 최신의 것을 확인해볼 필요가 있음. 용량이 컷던것 같던데...
- mysqld를 완전히 종료 한 후에는 ib_logfile이 불필요합니다.
● innodb_flush_method
IO 성능 저하를 감안하면까지 O_DIRECT를 사용하는 가장 큰 이유는 더블버퍼링을 막아 메모리를 효율적으로 쓰기 위함이다. 쓰기 속도가 느려진다.
● innodb_io_capacity
기본값은 200인데 이것은 1.5K 디시크나 SSD를 말하는것이다. 7200rpm의 디스크는 100으로 맞추는게 맞다.
● innodb_online_alter_log_max_size
이 변수는 online ddl 작업중에 사용되는 임시 로그파일들의 최대 크기를 지정하는 것으로 이 로그파일에는 online ddl 중에 dml 조작의 데이터가 저장된다. 임시 로그파일은 필요에 따라 innodb_sort_buffer_size 값으로 필요에 따라 최대 innodb_online_alter_log_max_size 만큼 확장한다. 만약 임시 로그파일이 최대 크기를 초과한 경우 alter는 중단되고 커밋되지 않는 dml 작업은 모두 롤백된다. 최대 크기의 값을 늘리면 online ddl 동안에 더 많은 dml을 처리할 수 있지만 online ddl 은 그 만큼 늦어지게 될 것이다.
● innodb_buffer_pool_instances
- 권장설정은 코어수 * 2 설정 하지 않으면 기본값은 8 이다. 즉 적은 코어이면 반드시 설정 해야 한다.
- 수치가 클 수록 트랜젝션간의 락 경쟁을 줄일 수 있다.
● innodb_page_cleaners
기본값은 4이고 최대값은 64 이지만 innodb_buffer_pool_instances 값보다는 크게 지정하지 못하고 이 값과 같게 된다. 리얼타임으로 변경이 안되며 my.cnf 에 설정된 값에 따라 서버 시작 할때 적용이 된다.
● innodb_page_size=16k
이것은 성능에 영향을 미치지 않는다고 한다. 기본 16K 이면 충분
● 추가적인 튜닝 포인트
group_concat_max_len=1024000
● 소트 버퍼 (기본=256k)
sort_buffer_size=4024K
● innodb_log_files_in_group
트랜잭션 로그 파일 개수로 3개로 설정합니다.
● innodb_flush_log_at_trx_commit
서비스 정책에 따라 다르게 설정하겠지만, 저는 일반적으로 2값으로 세팅합니다.
- 0: 초당 1회씩 트랜잭션 로그 파일(innodb_log_file)에 기록
- 1: 트랜잭션 커밋 시 로그 파일과 데이터 파일에 기록
- 2: 트랜잭션 커밋 시 로그 파일에만 기록, 매초 데이터 파일에 기록
이중으로 쓰기 버퍼를 사용하는지 여부를 설정하는 변수로 활성화 시 innodb_doublewrite 공간에 기록 후 데이터 저장합니다. 저는 활성화합니다.
● sync_binlog
트랜잭션 커밋 시 바이너리 로그에 기록할 것인지에 관한 설정이며, 저는 비활성 처리합니다.
● innodb_doublewrite
■ 참고사이트
- AWS 라이트세일 서버리스 DBMS - MySQL8 > AWS.클라우드
- http://cloudrain21.com/mysql-innodb-basic-performance-tunning
- [MySQL] 이노디비(InnoDB) 메모리 사용 환경설정 > 리눅스서버 - 세션, 글로벌 메모리 사용량