mysql 최적화 하기

MySql 최적화, Basic Settings

 

• skip-external-locking 항목이 있는데, 이는 MySQL 4.0 이후에는, 모든 시스템에서 외부 잠금을 비활성화 하는 것이 디폴트로 외부 잠금을 사용하지 말라고 권고 되고 있습니다.

 

• skip-name-resolve
  Mysql 서버가 외부로부터 접속 요청을 받으면 인증을 위해 ip 주소를 호스트 네임으로 변경하면서 불필요한 부하가 발생할 수 있으므로
  skip-name-resolve를 설정하면 접속 시 IP 기반으로 접속을 하게 되어 hostname lookup 과정 생략되어 좀 더 빠르게 접속 가능하다고 합니다.

+ 추가 설정

 

skip-host-cache : 호스트 이름 캐시를 비 활성화 시킬 수가 있다
skip-locked : 쿼리를 실행하며, lock 이 걸린 부분이 있다면, SKIP 하고 다음 row를 읽어들인다.

 

 

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 를 넘어서는 경우) 자동으로 테이블을 메모리에서 디스크 내 형식으로 변환

 

 

 

InnoDB 세팅

 

• default_storage_engine = InnoDB
  기본 데이타베이스 엔진으로 InnoDB를 사용한다는 것 표시

 

• innodb_buffer_pool_size
  . 운영중인 시스템의 DB 크기 이상을 할당
  (저의 경우 DB 크기가 970MB였기 때문에 1GB를 설정
  . 시스템 메모리의 65%~75% 권장, 시스템 메모리 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은 안정성 중심

-> 참고 자료

http://minsql.com/mysql/innodb_flush_log_at_trx_commit-%EA%B0%9C%EB%85%90%EB%8F%84%EC%99%80-%ED%8A%9C%EB%8B%9D-%ED%8F%AC%EC%9D%B8%ED%8A%B8/

 

 

• innodb_flush_method
  . O_DIRECT – 데이터 읽기/쓰기에 OS 캐시를 사용하지 않다 바로 MySql/MariaDB에서 가져 오겠다는 설정
  쓰기 성능은 나빠질 수 있지만 더블 버퍼링을 막아 메모리를 효율적으로 사용하겠다는 것
  . O_DSYNC – 데이터 읽기/쓰기에 OS 캐시를 사용
  속도는 더 빠르지만 대기 시간, 충돌로 데이타가 일관적이지 않을 수 있다고 함

 

• innodb_io_capacity
  . InnoDB 변경 성능은 플러쉬 속도, 즉 스토리지 I/O 속도에 의존하므로 빠른 스토리지 사용 필요
  . 현재 사용하고 있는 디스크의 IOPS와 유사한 값 설정
  . SSD와 같이 속도가 빠른 스토리지는 값을 올리고, 일반 HDD라면 값을 내린다.

 

 

MyISAM

 

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

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

 

• myisam_sort_buffer_size
  . rlqhs rkqtdms 512K

 

 

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 개

 

Query Cache Configuration

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

• query_cache_limit
  . 이 변수 값 보다 큰 값은 캐싱이 안됨

 

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

 

• query_cache_type
  . 쿼리캐시 사용하지 말라는 권고

 

 

로그 세팅

• binlog_cache_size
  . 이 값은 버퍼 명령문에 할당되어, 명령문이 이 값보다 크면 쓰레드는 트랜젝션을 저장하기 위해 임시 파일을 사용

 

• binlog_cache_use 상태 변수는 명령문을 저장하기 위한 용도로 이 버퍼(또는 임시 파일)를 사용한 트랜젝션 숫자를 의미하며, binlog_cache_disk_use 상태 변수는 이 임시 파일을 실제로 사용한 트랜젝션의 숫자를 표시
  . 이 두 가지 변수를 이용해 임시 파일 사용을 피하기 위한 binlog_cache_size를 튜닝하는 데 사용

 

• general_log / slow_query_log
  . 로그 활성화 시 1, 비활성 시 0 사용

 

• long_query_time
  . 이 변수 값보다 쿼리 처리가 길게 걸리면 에러 로그에 기록

 

 

 

출처 

https://happist.com/577204/db-%ED%8A%9C%EB%8B%9D%EC%9C%BC%EB%A1%9C-mysql-%EC%B5%9C%EC%A0%81%ED%99%94