MYSQL 엔진 성능 최적화

쿼리 최적화를 시도하는 동안, WHERE 절 조건을 아무리 줄여도 2.4초라는 속도가 변하지 않았습니다.

 

최적화 방법을 지속적으로 검색하던 중,

 

MySQL 엔진의 성능 자체가 낮은 설정이 최적화에 영향을 줄 수 있다는 생각이 들었습니다.

 

이에 관련된 성능 최적화가 잘 정리된 글을 참고하여

 

설정을 조정한 결과, 최적화가 성공적으로 이루어졌습니다.

 

정리된글을 언제든 볼수 있도록 포스팅 하고 싶어 그대로 복사 하였다.

MySql  성능 최적화 방안 (옵션 설정값 설명 포함)

 

1. 기본 설정

 

• Basic Settings에서 생각해 볼 것은 tmpdir을 어디에 둘 것인지 여부입니다.
  기본으로는 하드디스크인 /tmp에 설정되어 있습니다.
  
  여러 번 시행 착오 끝에 리눅스 tmpfs 파일시스템은 일부 임시 파일생성을 허용하지 않기 때문에 에러가 발생하다는 사실을 확인하고 임시 파일 폴더를 메모리에 올리는 것을 포기 했습니다.
  제가 받은 에러 메세지는 리눅스 커널에서 MariaDB가 tmpfs 파일시스템에서 일부 임시 파일 생성하는 것을 허용하지 않아 에러가 발생, mariadbd: O_TMPFILE is not supported on /dev/shm (disabling future attempts).
  .
• skip-external-locking 항목이 있는데, 이는 MySQL 4.0 이후에는, 모든 시스템에서 외부 잠금을 비활성화 하는 것이 디폴트로 외부 잠금을 사용하지 말라고 권고 되고 있습니다.
  .
• skip-name-resolve
  Mysql 서버가 외부로부터 접속 요청을 받으면 인증을 위해 ip 주소를 호스트 네임으로 변경하면서 불필요한 부하가 발생할 수 있으므로
  skip-name-resolve를 설정하면 접속 시 IP 기반으로 접속을 하게 되어 hostname lookup 과정 생략되어 좀 더 빠르게 접속 가능하다고 합니다.

 

# * Basic Settings #

user = mysql

pid-file = /run/mysqld/mysqld.pid

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

lc-messages-dir = /usr/share/mysql

lc-messages = en_US skip-external-locking skip-name-resolve

bind-address = 127.0.0.1

2. Fine Tuning

• max_connection : 최대 동시 접속자 수,
  늘어나면 날수록 메모리가 고갈되고 스케줄링 오버헤드도 증가
  이전 최대 접속자 수의 2배 정도 잡는다.
  
  
• connect_timeout : mysqld 서버가 패킷과 연결하기 위해서 대기하는 시간
  기본값은 10초
  
  
• wait_timeout : 서버가 데이타 패킷과 연결된 후 연결을 유지하는 시간
  . 기본값은 28800초(8시간)
  . DB 서버 접속이 많다면 wait_timeout을 최대한 적게 (20~30 정도를 추천) 설정하여 불필요한 연결을 빨리 정리 필요.
  그러나 Connection Miss Rate(%)가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 가져야 함.
  
  
• . 그렇지 않다면 DB 연결 후 끊고 다시 연결하는 것은 속도를 지연 시키는 요인이기 때문에 연결 시간을 길게 유지하는 것이 성능 향상에 도움이 된다는 의견이 있음
  
  
• max_allowed_packet : 허용 패킷 크기
  . 기본값 16MB이며 최대값은 1GB,
  . MySQL 서버가 잘못된, 너무 큰 패킷을 제어하는 데는 도움이 되지만
  규모 이상으로 큰 패킷을 수신하면 문제가 있다고 판단해 연결을 끊어 버리기 때문에 이를 피하려면 값을 새로 설정하고 mysql을 다시 시작해야 함
  
  
• thread_cache_size :
  . 기본값 8
  . Cache Miss Rate(%)가 높다면 기본값보다 높게 잡는다
  
  
• sort_buffer_size
  . 리눅스에는 256K 또는 2MB라는 임계점이 존재하는데
  이 이상의 값은 메모리 할당이 크게 느려질 수 있으므로 이보다 낮은 값을 사용하는 것으로 고려
  
  
• join_buffer_size
  . MySqlTunner에서는 최소 1MB이상으로 제안
  
  
• tmp_table_size
  . group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용하는 메모리 크기
  
  
• max_heap_table_size
  . 내부 메모리 임시 테이블이 너무 커지면(tmp_table_size와 max_heap_table_size 를 넘어서는 경우) 자동으로 테이블을 메모리에서 디스크 내 형식으로 변환

 

# DB 크기가 1G정도로 사이즈를 기준임.

max_connection             = 50
connect_timeout            = 20
wait_timeout               = 7200
max_allowed_packet         = 32M 
thread_stack               = 192K
thread_cache_size          = 128 
sort_buffer_size           = 2M
join_buffer_size           = 2M  
bulk_insert_buffer_size    = 16M
tmp_table_size             = 256M  
max_heap_table_size        = 256M

 

 

3. InnoDB 세팅

 

[그림-1] InnoDB 구조

 

• default_storage_engine = InnoDB
  기본 데이타베이스 엔진으로 InnoDB를 사용한다는 것 표시
  
  
• innodb_buffer_pool_size
  . 운영중인 시스템의 DB 크기 이상을 할당
  (저의 경우 DB 크기가 970MB였기 때문에 1GB를 설정
  . 시스템 메모리의 65%~80% 권장, 시스템 메모리 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

 

4. MyISAM

가능하면 InnoDB를 사용하고 MyISAM을 사용하지 않는 방법을 찾는 게 좋다고 권고 받지만 때로는 MyISAM를 사용할 수 밖에 없는 경우도 있다고 합니다.

• key_buffer_size
  . 인덱스를 메모리에 저장하는 버퍼의 크기로 총 메모리의 25% 정도로 설정
  . 기본 값은 256MB
  . 그렇지만 InnoDB 중심으로 운영한다면 이 값을 매우 낮게 유지 가능. 가이드에서는 64K까지 낮출 수 있다
  
  
• myisam_sort_buffer_size
  . rlqhs rkqtdms 512K

 

key_buffer_size            = 36M
myisam_sort_buffer_size    = 512K  # default 2M
concurrent_insert          = 2
read_buffer_size           = 512K
read_rnd_buffer_size       = 512K

 

5. Table 세팅

 

• table_definition_cache
  . 테이블 오픈 속도를 향상 시키기 위한 캐시 수
  
  
• table_open_cache
  . 각 쓰레드별 오픈할 테이블 수
  . 기본 값은 2000
  . max_connection * N개가 되어야 함
  여기서 N은 실행하는 쿼리에서 조인 당 최대 테이블 수
  . MySql에서 show global status like ‘%table_open_cache%’ ; 명령 결과에서 miss가 있다면 늘려 봄
  
  
• open_files_limit
  . table_open_cache 값의 2배 또는 3배
  . file-max 값은 리눅스에서 한 번에 운용할 수 있는 파일 수를 의미하며,
  보통 4MB 메모리 당 256개의 파일을 운용할 수 있다고 한다.
  대략 1G -> 65536개, 2G -> 131072 개

 

table_definition_cache          = 2000 
table_open_cache                = 15000
open_files_limit                = 7260000

 

 

 

6. Query Cache Configuration

MySqlTunner에서는 쿼리 캐시를 사용하지 말라고 권고하고 있네요

• query_cache_limit
  . 이 변수 값 보다 큰 값은 캐싱이 안됨
  
  
• query_cache_size
  . 쿼리 결과를 캐싱하기 위해서 할당된 메모리 크기
  . query_cache_size가 너무 크다면 갑자기 엄청난 쓰기 작업이 발생 시 서버는 바로 쿼리 작업을 하는 대신 cache를 찾아 작동하는데 집중해 오히려 속도가 느려짐
  . 시스템에서 사용하지 말라고 권고
  
  
• query_cache_type
  . 쿼리캐시 사용하지 말라는 권고

 

query_cache_limit        = 2M   
query_cache_size         = 0    
query_cache_type         = OFF 
binlog_cache_size        = 16M

MYSQL 8.0버전 이후로는 쿼리 캐시 라는것이 삭제 되었다.
하지만 관련된 비슷한 기능이 있을것이라고 판단된다.

 

*출저: https://0inch.tistory.com/entry/MySql-%EC%97%94%EC%A7%84-%EC%84%B1%EB%8A%A5-%EC%B5%9C%EC%A0%81%ED%99%94-%EB%B0%A9%EC%95%88