MySQL 주요 성능 파라미터

MySQL 주요 성능 파라미터 튜닝

innodb_buffer_pool_size

• 데이터와 인덱스를 캐시하기 위해 사용하는 메모리 버퍼크기.
  -- Paging을 위해 LRU 알고리즘 사용
• 전체 RAM의 50% – 70%.
• 전체 데이터베이스 크기보다 크게 설정할 필요는 없음.

innodb_log_file_size

• transaction redo log 를 저장하는 로그 파일 사이즈
• 일반적으로 128M – 2G 사이.
• 최대 한 시간 정도의 로그를 보관할 수 있는 Size.

innodb_flush_log_at_trx_commit

• commit 될 때 log buffer를 flush하고 disk 연산이 flush 되는 시점을 설정하는 파라미터
  

• "1"(5.7에서는 기본값) 1~4단계 모두 처리. 설정 내구성 좋음.
  쓰기 속도보다 데이터의 중요도가 높을 때 설정

• "0" 트랜잭션 commit되면 1~2단계까지 처리.
  mysqld 프로세스가 죽으면 마지막 1초간 트랜잭션 유실될 수 있음

• "2" 트랜잭션 commit 되면 1~3단계까지 처리하고 (메모리 영역만).
  서버가 죽으면 마지막 1초간 트랜잭션 유실될 수 있음(MySQL 장애시 유실 없음)

1초에 한번 씩 3~4단계를 자동으로 수행함 (OS buffer Cache, InnoDB log에 데이터 쓰기)

• mysqld 프로세스가 죽으면 마지막 1초간 트랜잭션 유실될 수 있음

innodb_flush_method

• InnoDB가 데이터 파일을 어떤 방식으로 읽고 쓰기를 할것인지 선택
• O_DIRECT로 설정하면 이중 버퍼링(메모리상에서의 중복 저장)으로 인한 성능 저하를 피할 수 있음.
  -- UNIX&Linux 시스템에서는 모든 데이터 읽기/쓰기에 OS 캐시를 사용
  -- 버퍼풀(Buffer Pool)메모리 관리 영역을 가지고 있기 때문에 동일한 데이터를 버퍼풀과 OS캐시에 중복으로 저장해 메모리를 낭비할 필요가 없다.
• O_DIRECT를 쓰더라도 데이터파일에 대해서만 OS캐시를 사용 하지 않는다. Redo로그에서도 OS캐시를 쓰지 않겠다면 ALL_O_DIRECT로 설정이 필요하다.

innodb_file_per_table

• "ON"모든 테이블에 대해 독립적인 InnoDB 테이블 공간이 생성.

innodb_stats_on_metadata

• "OFF" InnoDB 통계의 불필요한 업데이트를 방지하고 읽기 속도를 향상.

innodb_buffer_pool_instances

• 버퍼 풀 크기가 1G 미만인 경우를 제외하고 default "8"로 설정.
• innodb_buffer_pool_size 병렬로 수행할 수 있는 threads 수
• core 수 2배 지정
• instance 수 늘리면 트랜잭션 간 Locks 경합 단축

query_cache_type 및 query_cache_size

• "0" 쿼리 캐시가 완전히 비활성화.
• Query Cache를 사용 활성화 고려 사항.
  -- 테이블의 읽기 대비 변경 횟수가 얼마나 많은가?
  -- 동일한 쿼리가 얼마나 자주 호출되는가?
  이 두 가지를 고려해서 Query Cache의 적용여부 결정.

Reference
https://www.percona.com/blog/mysql-101-parameters-to-tune-for-mysql-performance/