MySQL 성능 관련 Config 설정

---  MEMORY  ----

 * innodb buffer pool size를 크게 가져갈수록 디스크 IO를 줄일수 있음
 * 일반적으로 OS의 70&~80% 사용

 

---  CPU  ----

 * 하이퍼 스레딩 활성화
 * 일반적으로 CPU보다는 memory 및 Disk IO를 올리는것이 성능 향상에 효과적
 * MySQL 버전에 따른 코어수 제한 
   - MySQL 5.1 : ~ 4코어 
   - MySQL 5.5 : ~ 16코어 
   - MySQL 5.6 : ~ 36 쓰레드(Core)
   - MySQL 5.7 : ~ 64 쓰레드(32 Core-HT)
   - MySQL 8.0 : ~ 100 쓰레드(48 Core-HT)

 

---  Disk  ----

 * SSD, NVMe 사용시 innodb_page_size=4K, innodb_flush_neighbors=0 로 사용을 추천
 * innodb datadir, tmp 파일 및 undo 로그 모두 Ramdom IO가 발생함으로 SSD로 할당
 * 로그(빈로그 등등)의 경우 Sequnential IO가 발생함으로 디스크로 할당
 * innodb_io_capacity
   - innodb가 사용 가능한 I/O 대역폭
   - 너무 큰 값을 사용한다고 해서 많은 이점이 있지는 않음( 20000 이상은 권장되지 않음 )
   - 쓰기 작업이 많은 시스템은 높은 값이 유리 ( Ex LogDB )
   - 쓰기 작업이 적은 시스템은 낮은 값이 유리
 * innodb_io_capacity_max
   - innodb가 사용 가능한 I/O 최대 대역폭 
   - 일반적으로 innodb_io_capacity 값의 두배로 설정
 

---  OS  ---- 

 * MySQL은 Linux 에 최적화 되어 있다. 
 * MySQL 이외에 OS에서 설정을 해줘야되는 부분
   - ulimit -n, 파일 수 제한 (connections, open tables, ...)
   - ulimit -u, 스레드 수 제한 (connections, InnoDB background threads, event scheduler, ....)
 * NUMA 기반 서버의 경우 innodb_numa_interleave를 1로 설정 
   - mysql과 os의 numa 설정이 다른경우, 노드간 메모리 사용 불균형으로 swap이 발생하여 성능 이슈가 발생할 가능성이 있음.
 * InnoDB를 사용하는 경우 OS의 파일시스템 캐시를 사용하지 않는다.( 더블 버퍼링을 막아 메모리를 효율적으로 사용 )
   - set inndbo_flush_method=O_DIRECT
   - innodb pool이 훨씬 고도화된 형태로 동작

---  ETC   ---

 * innodb_log_file_size(redo log 사이즈)
   - 크게 가져 갈수록 체크포인트 동작이 덜 필요하여 성능에 좋으나, 너무 크게 설정하는 경우 DB 복구 시간이 느려짐.
   - 운영서버 최소 512MB 이상 권장.   
 * innodb_log_files_in_group(리두 로그 파일 갯수)
   - Default는 2이지만 일반적으로 3으로 운영
 * skip-name-resolve
   - client에서 server로 접속시 dns lookup 과정을 생략
   - dns 서버가 느리거나 장애 발생시 DB 접속문제가 발생하는 것을 해결할 수 있음.
 * innodb_write_io_threads / innodb_read_io_threads
   - SHOW ENGINE INNODB STATUS 에서 지연된 쓰기/읽기 요청이 64개 이상인 경우 값을 올려주는것이 좋음.
 * innodb_log_file_size
   - 로그 파일 크기
   - innodb_buffer_pool_size의 25% 정도로 유지
   - 이 값이 클수록 버퍼 풀에서 체크포인트 발생 빈도가 낮음.
   - 이 값이 클수록 크래시 발생시 복구가 그만큼 느려짐.

 

---  ACID 유지  ---   

 * innodb_flush_log_at_trx_commit = 1 
   - commit마다 리두 로그에 기록하고 디스크에 flush
   - ACID가 보장이 되지만, 디스크 I/O가 높음
   - 성능 위주 DB로 설정하고 싶은 경우 0
 * sync_binlog = 1
   - 트랜잭션이 커밋되기 전에 이진 로그를 디스크에 동기화
   - 정전 및 운영 체제 충돌에도 ACID가 보장되지만, 디스크 I/O가 높음
   - 성능 위주 DB로 설정하고 싶은 경우 0
 * innodb_doublewrite = 1
   - innoDB의 페이지를 쓰기 전에 버퍼 풀에서 플러시된 페이지를 중복해서 저장
   - MySQL Crash 복구 중에 이중으로 쓰여진 버퍼에서 페이지 복사본을 찾아 복구 진행
   - 데이터 무결성을 유지할 수 있으나, 이중으로 데이터를 기록하여 I/O가 높음. (이중으로 기록 하지만 2배의 I/O가 발생하지는 않음)
   - 성능 위주 DB로 설정하고 싶은 경우 0   

 

---  Buffer per Client Connections  ---

 * connection(세션)당 할당되는 버퍼들 
   - read_buffer_size : Sequnential scan (full table scan)을 사용할 때 사용하는 버퍼 
   - read_rnd_buffer_size : 정렬 작업후, 정렬된 순서대로 데이터를 다시 읽어 들일 때 사용하는 버퍼 
   - join_buffer_size : 인덱스를 사용하지 않는 조인에 사용되는 버퍼, Session level에서 join 단위로 생성
   - sort_buffer_size : 인덱스를 사용하지 않는 정렬에 사용하는 버퍼 
   - binlog_cache_size (if binary logginigs is enabled)
 * connection이 많을 경우 이 버퍼들이 메모리를 점유하기 때문에 크게 세팅하면 안된다.
   - 만약 크게 필요한 경우가 있다면 필요한 세션에서만 크게 잡아서 사용한다. 

 

참고

https://iamwhat.tistory.com/entry/MySQL-%ED%8F%AC%ED%8D%BC%EB%A8%BC%EC%8A%A4-%ED%8A%9C%EB%8B%9D