MySQL Performance Best Practice

 

출처 : https://www.youtube.com/watch?v=dF8qmCOSE7g 

 

MySQL 8.0 
- Redo log 디자인을 변경해서 성능향상
- system mutex 를 최대한 분리해서 IO Capacity 를 충분히 이용하도록 변경 
- Information Schema, Performance Schmea 를 조회하는 성능 향상

Memory 
- 작업 데이터셋이 innodb buffer pool 에 적합한지 확인
  사용 가능한 메모리의 70~80%를 innodb buffer pool 에 할당 할 수 있음 
- MySQL 은 Connection 당 메모리 소모, Connection 당 할당되는 메모리는 크게 할당하는것을 권장하지 않는다. 
  max_allowed_packet, thread_stack, net_buffer_length, max_digest_length
- 디스크 접속을 완하하기 위한 Caching 및 Buffer 
  Read log/Join/Sorting Buffer 와 Table Cache
  Replication 관련 max_binlog_cache_size 
- 기타
  - OS, FS cache
  - temporary tables tmp_table_size 
  - Performance Schema
- 모니터링 방법: top 명령어로 확인, 전체 메모리의 80% 이하 

Monitoring MySQL Memory Usage 
- 대부분 메모리 지표는 비활성화 됨 
  select * from performance_schema_setup_instruments where name like '%memory%';
- performance_schema 
  memory_summary_global_by_event_name 
- sys 스키마 
  memory_global_by_current_bytes 
- 상태값 확인
  show global status like 'table_open%';
  show global status like 'binlog%';
  show global status like 'created_tmp%';
  
  select 
    substring_index(event_name, '/', 2) as core_area, 
    format_bytes(sum(current_alloc)) as current_alloc
  from sys.x$memory_global_by_current_bytes
  group by substring_index(event_name, '/', 2)
  order by sum(current_alloc) desc;

Disk 
- SSD, NVMe 권장 
 innodb datadir, tmp 파일, undo 로그 모두 랜덤 IO, 빠른 SSD 권장
 inndb_page_size = 4K (디폴트는 16K, 디스크 page 사이즈와 매칭)
 innodb_flush_neighbors = 0 
- Spinning 디스크는 log (순서 IO)에 여전히 사용할 수 있음 
- RAID 디스크를 사용한다면 성능을 위해 RAID 1+0 권장 
- FBWC/BBWC (Flash/Battery-Backed Write Cache) 권장 
  write 성능 향상 및 crash-safe
- 디스크 유형(rpm 지표) 및 부하에 따라 innodb_io_capactiy 설정 
  디폴트값은 200, 느린 ssd 혹은 일반 하드 디스크에 적합 
  디스크 성능이 좋다면 200보다 더 큰값을 설정

OS 
- 성능만 고려하면 Linux 권장 
- MySQL 8.0 인 경우 최신 커널 권장하지
- ext4, xfs 파일 시스템 추천
- ulimit을 사용하여 file/process limits 확장 
- MySQL 전용 서버인 경우, innodb_numa-interleave = 1 권장 
- Swappiness, 권장 옵션은 (1-6): sysctl - vn.swappinness=1
- InnoDB 데이터 파일은 FS 캐시를 거치지 않음, innodb_flush_method=O_DIRECT 

MySQL Configuration 
- 8.0 설정 파일 : my.cnf, mysqld-auto.cnf (SET PERSIST 변수로 지정한 값을 mysqld 재기동 후에도 유지)
- 설정값 확인 테이블 추가 
  - performance_schema.global_variables
  - performance_schema.session_variables
  - performance_schema.variables_by_thread
  
- 클라이언트 커넥션 관련 설정 
  - max_connections 를 필요 이상으로 크게 설정하지 말아야 할 것 
   - OS limit on file descriptors 
     - Linux/Solaris : UP to 10,000
     - Windows: Up to 16,384
   - 응답 시간의 기대치 및 워크로드 감안
   - 많은 연결 수가 예상되면 버퍼에 따른 메모리 용량을 확복 
   - wait_timeout으로 연결을 닫음 
  - 상태값 확인
    - Connections
    - Max used connections
    - Connection errors max connections 
  - 커넥션별로 할당되는 버퍼 값은 작게 시작 
    - sort_buffer_size
    - binlog_cache_size
    - net_buffer_length ~ max_allowed_packet 
    - read_buffer_size 
    - read_rnd_buffer_size 
    
Reusing Threads
- thread_cache_size : 서버가 스레드를 재사용할 수 있도록 저장한 스레드 개수 
  - 디폴트 값은 자동 설정 : 8 + (max_connections / 100)
  - Thread cache hit rate % = 100 - ((Threads_created / Connections) * 100)
- 연관된 상태 값 확인 
  - Connections
  - Threads_connected
  - Connections / Uptime 
  - Threads_created / Uptime 
  - Uptime_since_flush_status 
  - Threads_cached 
  - Threads_running 
- thread_pool_size 스레드 풀 성능 튜닝 (엔터프라이즈만 사용가능)
  - https://dev.mysql.com/doc/refman/8.0/en/thread-pool-tuning.html

InnoDB Buffer pool 
- innodb_buffer_pool_size 
  - 모든 데이터를 innodb_buffer_pool 에 넣어서 사용하면 이상적이나(이상일 뿐)
  - 테이블 및 인덱스 데이터를 캐시한 InnoDB의 메모리 영역, LRU 알고리즘에 의해 관리 
  - 전용 데이터베이스 서버에서 시스템의 실제 메모리 크기의 70%~80%정도로 설정 할 수 있음 
  - innodb_buffer_pool_size  = N * innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 
- 확인 방법 : SHOW ENGINE INNODB STATUS;
  - Total memory allocated
  - Buffer pool size 
  - Free buffers 
  - Database pages, Old database pages 
  - Modified db pages 
  - Pending reads, Pending writes 
  - Buffer pool hit rate 

Innodb redo log (트랜잭션 로그)
- innodb data file에 발생하는 랜덤 io 를 red log file을 사용해서 순차 io 로 변경해서 처리 해줘서 write 성능 향상 
- innodb_log_file_size 
  - write 성능에 유리하지만 복구 시간에 영향 (크래시 리커버는 redo log 기반)
  - 총 사이즈(~512G) : innodb_log_file_size * innodb_log_files_in_group (기본값 2)
  - 운영 환경에서 최소 512M으로 권장하고, 가능한 충분히 크게 설정할 것 권장 
- redo 사용율 
  - SHOW ENGINE INNODB STATUS 에서 Log sequence number, Last checkpoint at 값을 확인하여 계산 
  - SELECT NAME, COUNT FROM information_schema.INNODB_METRICS WHERE NAME IN ('log_lsn_current', 'log_lsn_last_checkpoint')
  - Used % = (Used log / Total log) * 100 
    = (log_lsn_current - log_lsn_last_checkpoint / (innodb_log_file_size * innodb_log_files_in_group)) * 100
    = (47605855 / 100663296) * 100 = 47.29% 
  - Used % 가 50% 정도는 괜찮지만 80%정도일 경우 위험함 
    redo log 가 부족하게 되면 innodb buffer pool flushing 이 발생하는데 이때 대량의 io가 발생하여 성능 저하가 발생한다. 
- innodb_log_buffer_size = 16MB
  - Redo 로그 I/O를 완화하기 위한 버퍼 영역 
  - 일반적으로 16MB정도면 충분하지만 트랜잭션 사이즈가 크면 commit 전에 disk IO 가 발생되기에, 따라서 크게 조절할 필요가 있음 
 - innodb_flush_log_at_trx_commit = 1
  - InnoDB가 commit한 트랜젝션에 대해서 언제 flush 할 것인지 결정
  - 디폴트값인 1로 권장하지만 다음 상황에서 제외:
    - InnoDB가 commit한 트랜젝션에 대해서 언제 flush 할 것인지 결정
    - Bulk 데이타 로딩할 경우 2로 설정하거나 8.0 버전에서 redo로그를 비활성화
    - 예측하지 못한 peak 부하로 인해 디스크 I/O부하가 심할 경우
    - 데이터 로스가 발생하여도 괜찮을 경우

바이너리 로그 성능만- 
-  binlog_cache_size에 비해 트랜젝션이 너무 크면, 임시 파일에 저장하기에 성능 영향
- 관련된 상태 변수 확인:
  - Binlog_cache_use
  - Binlog_cache_disk_use
  - 캐시 효율성% = (100 - ( Cache Disk Use/Cache Use) * 100)
- 트랜젝션 사이즈를 제어함으로 바이너리 로그 사이즈 제어, 복제 효율성 향상
  - DELETE 대신에 DROP TABLE/PARTITION
  - DELETE FROM … WHERE … LIMIT 1000;
  - binlog_row_image = minimal
- 복제 지연 방지 설정
  - binlog_transaction_dependency_tracking = WRITESET
  - replica_parallel_type = LOGICAL_CLOCK
  - replica-parallel-workers = 16
  - binlog_order_commits=OFF
  - replica_preserve_commit_order = 1(optional)
https://dev.mysql.com/blog-archive/improving-the-parallel-applier-with-writeset-based-dependency-tracking/