[참고 사이트] 

- [MYSQL] 느리다면 튜닝하자 두번째

- MySQL InnoDB 성능 튜닝 기본

- MariaDB(MySQL) 최적셋팅값

 

MySQL의 튜닝은 MySQL의 데이터베이스 시스템 관련 파라메터들에 대한 튜닝과 각각의 스토리지 엔진 관련 튜닝으로 나뉘어진다.

MySQL의 시스템 관련 튜닝은 MySQL의 설정 파일인 my.cnf(윈도우의 경우 my.ini)파일을 수정하게 되며 MySQL 커넥션에 관한 부분과 메모리에 관한 부분으로 나눌 수 있다.

 

1. MySQL 연결(Connection) 튜닝

실질적으로 MySQL이 가장 많이 사용되는 분야를 꼽는다면 역시 인터넷 분야라고 할 수 있다. 포탈사이트나 게임사이트 등 매우 많은 부하가 발생하는 사이트에서 가장 문제가 되는 것은 MySQL의 커넥션에 관련된 문제이다.

 

(1) Connection 설정 보기​

SHOW STATUS LIKE '%connect%';   SHOW STATUS LIKE '%client%';   SHOW STATUS LIKE '%thread%';   SHOW VARIABLES LIKE '%wait_timeout%';   SHOW VARIABLES LIKE '%thread_cache_size%';   SHOW VARIABLES LIKE '%max_connections%';

 

① STATUS

- Aborted_clients : 클라이언트 프로그램이 비 정상적으로 종료된 수

- Aborted_connects : MySQL 서버에 접속이 실패된 수

- Connections : MySQL 서버에 대한 연결 시도 횟수

- Max_used_connections : 최대로 동시에 접속한 수

- Threads_cached : Thread Cache의 Thread 수

- Threads_connected : 현재 연결된 Thread 수

- Threads_created : 접속을 위해 생성된 Thread 수

- Threads_running : Sleeping 되어 있지 않은 Thread 수

 

② VARIABLES

- wait_timeout : 종료전까지 요청이 없이 기다리는 시간( TCP/IP 연결, Shell 상의 접속이 아닌 경우)

- thread_cache_size : thread 재사용을 위한 Thread Cache 수로써, Cache에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.

- max_connections : 최대 동시 접속 가능 수

 

(2) 튜닝

Cache Miss Rate(%) = Threads_created / Connections * 100

Connection Miss Rate(%) = Aborted_connects / Connections * 100

Connection Usage(%) = Threads_connected / max_connections * 100

 

- Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.

 

- DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게(10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.

 

- Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 

일반적으로 threads_connected 가 Peak-time시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.

 

- MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다. 

skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.

 

2. MySQL InnoDB 튜닝

 

(1) 소개

Transaction-safe 한 Storage Engine으로 2005년 Oracle이 Innobase를 인수한 후 Oracle의 일부가 되었으며, MySQL 5.5 이후 기본적으로 사용되고 있다. 

MyISAM 보다 많은 기능을 제공한다.(MVCC 지원, ACID Transaction 처리, FK 지원, row-level lock 등)

또한 Undo, Tablespace 등 Oracle의 개념을 많이 수용하고 있다.

사용 정책의 경우 듀얼 라이선스 정책을 취하고 있는데 GNU GPL이면서도 상업적인 용도로 판매 가능하다.

 

(2) 사용 이유

- 대용량의 데이터를 컨트롤하는 경우

- 트랜잭션 관리가 필요한 경우

- 복구가 필요할 경우

- 정렬등의 구문이 들어가는 경우

- INSERT, UPDATE, DELETE 등이 빈번하게 발생하는 경우

- 높은 퍼포먼스가 필요한 대용량 사이트에 적합

 

(3) 장점

- 데이터 무결성이 보장

- 동시성 제어가 가능

- 제약조건, 외래 키 생성이 가능

- Row-level Lock(행 단위 Lock)을 사용하기 때문에 변경 작업(INSERT, UPDATE, DELETE)에 대한 속도가 빠름

- 트랜잭션을 지원해 transaction-safe 테이블 관리

- MyISAM과 비슷하지만 ORACLE처럼 많은 기능을 지원(Commit, Rollback, 장애 복구, row-level locking, 외래 키 등 다양한 기능을 지원)

 

(4) 단점

- 복구 방법 어려움

- Dead lock 발생 가능성 있음

- 많은 기능을 제공하다보니 데이터 모델 디자인에는 많은 시간이 필요

- 시스템 자원을 많이 차지함

- Full-text 인덱싱이 불가능

 

(5) 튜닝

① innodb_additional_mem_pool_size

데이터 디렉토리 정보와 다른 내부 데이터 구조를 저장하기 위해 사용하는 메모리 풀의 크기(바이트 단위). 여러분이 어플리케이션에서 테이블을 많이 가질수록, 여기에 할당해야 하는 메모리가 많이 필요하게 된다. 만일 InnoDB가 이 풀에 있는 메모리를 다 사용하게 되면, 이 엔진은 OS가 사용하는 메모리를 할당하기 시작하고, MySQL 에러 로그에 경고 메시지를 작성한다. 

 

8M~16M 권장한다. 기본 값은 1MB이다.

 

② innodb_buffer_pool_size

버퍼 풀은 두 가지 역할을 담당한다. 첫번째는 데이터 파일과 로그 파일이 기록되는 순서를 조정하는 역할이고, 두번째는 디스크 액세스를 줄이기 위한 캐시의 역할이다.

시스템(OS)에서 파일 캐시의 크기가 클수록 성능에 유리하듯이, Database 에서도 마찬가지로 버퍼 풀의 크기가 클수록 성능에 유리하다. 특히 조회 처리를 위한 캐시 효과가 크기 마련인데, 이는 읽으려는 데이터가 메모리에 올라와 있으므로 Disk I/O 를 발생시키지 않기 때문이다.

이론적으로는 다른 버퍼에 할당하는 메모리를 제외하고는 대부분의 메모리를 버퍼 풀에 할당하는 것이 좋다.

 

보통 시스템 메모리의 65%~75% 권장한다.(Ex. 서버 전체 메모리가 16G이면 12G로 설정)

 

현재 설정된 innodb_buffer_pool_size이 충분한지는 아래와 같이 information_schema.TABLES 테이블에서 현재 사용량을 조회하여 확인할 수 있다.

SELECT `engine`, COUNT(*) AS table_count, CONCAT(ROUND(SUM(table_rows) / 1000000, 2), 'M') AS table_rows, CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024), 2),'G') AS total_data, CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2), 'G') AS total_index, CONCAT(ROUND(SUM(data_length + index_length) / (1024 * 1024 * 1024), 2), 'G') AS total_size, ROUND(SUM(index_length) / SUM(data_length), 2) AS index_frac FROM information_schema.TABLES GROUP BY `engine` ORDER BY SUM(data_length + index_length) DESC;

 

③ innodb_buffer_pool_instances

MySQL 5.5 부터 버퍼 풀의 인스턴스 수를 설정할 수 있는데, 인스턴스 수를 늘리면 트랜잭션 간의 Lock 경합을 줄일 수 있다. 멀티 스레드 구조인 MySQL 에서는 스레드 간 버퍼 풀 조작에서 Exclusive Lock 처리가 필요한데, 이 때 버퍼 풀 접근을 위해 뮤텍스를 사용하고 동시 다발적으로 접근 시 뮤텍스에 대한 경합이 발생한다.

인스턴스 수를 늘릴수록 많은 수의 스레드가 동시에 버퍼 풀에 접근하더라도 Lock 경합을 피할 수 있다. 

 

CPU 코어 수가 많은 시스템일수록 인스턴스 수를 늘릴 수 있다고 보면 된다. 인스턴스 수의 기본 값은 8 이다.

 

④ innodb_flush_method

버퍼 풀에서 이미 데이터에 대한 캐시 역할을 하기 때문에, 데이터 파일에 대한 I/O를 수행할 때 파일 시스템 캐시를 중복으로 이용할 필요가 없다. 즉, 시스템에서 제공하는 파일 캐시를 이용하는 대신 InnoDB 에서 제공하는 버퍼 풀을 이용하여 데이터를 캐싱한다.

따라서, 파일 캐시를 이용하지 않고 직접 쓰도록 하기 위해 데이터 파일을 Open할 때 O_DIRECT 모드를 설정하는데, 이는 innodb_flush_method 파라미터를 통해 설정할 수 있다.

파일 캐시를 사용하지 않는 것이 좋은 또 한가지 이유는 시스템 파일 캐시의 공격적인 메모리 사용에 있다. O_DIRECT 모드를 사용하지 않으면 시스템이 여유 메모리를 파일 캐시에 공격적으로 할당하여 메모리를 쉽게 고갈시킬 수 있다.

 

innodb_flush_method = O_DIRECT로 설정한다.

 

⑤ innodb_log_file_size * innodb_log_files_in_group

InnoDB의 로그 파일은 크기가 고정되어 있는 형태이고, 같은 파일들을 rotation 하며 사용하는 구조이다.(오라클과 동일하다.) 트랜잭션이 데이터를 변경하면 먼저 버퍼 풀의 데이터를 변경하게 되는데 플러쉬가 발생하기 전의 이러한 변경된 데이터를 더티 페이지라고 한다.

더티 페이지를 얼마만큼 허용할 수 있는가는 로그 파일의 크기와 관련이 있다. 로그 파일이 모두 차 있는 상태라면 더 이상 더티 페이지를 허용할 수 없기 때문에 플러쉬를 수행한 후 불필요한 로그 파일 공간을 재 사용해야 한다.

플러시는 I/O 작업을 의미하기 때문에 매우 느리다. 로그 공간을 충분히 확보해 두어야만 느린 플러쉬를 뒤로 미룰 수 있을 것이다.

하지만, 무조건 로그를 크게 한다고 좋은 것은 아니다. 왜냐하면, REDO 로그의 크기가 클수록 복구 시간이 길어질 수 있고, 더티 페이지의 최대 크기라고 할 수 있는 버퍼 풀의 크기보다 REDO 로그의 크기를 크게 해봐야 아무 이점이 없기 때문이다.

그리고, 어느 순간에는 쌓이고 쌓인 플러쉬가 한꺼번에 몰려서 발생하게 되는데, 이때는 트랜잭션의 변경 작업이 자칫 플러쉬와 체크포인트 때문에 대기 상태가 될 수도 있다.

 

innodb_log_files_in_group : 로그(redo) 파일 갯수. 기본값(2) 유지.

innodb_log_file_size : 로그(redo) 파일 크기. innodb_buffer_pool_size/innodb_log_files_in_group를 적정 값으로 본다.

 

⑥ innodb_log_buffer_size

로그 파일을 디스크에 쓰기 위해 사용하는 버퍼의 크기 (바이트). 로그 버퍼가 크면 트랜잭션을 실행하기 전에 로그를 디스크에 쓰지 않고서도 대형 트랜잭션을 처리할 수가 있게 된다. 따라서, 만일 여러분이 대형 트랜젝션을 가지고 있다면, 로그 버퍼를 크게 해서 디스크 I/O를 절감하도록 한다.

 

사용 가능한 크기는 1MB에서 8MB이다. 기본 값은 1MB이다.

 

⑦ innodb_flush_log_at_trx_commit

INSERT, UPDATE, DELETE 쿼리로 변동되는 MySQL의 데이터베이스 상태는 일시적으로 메모리(innodb_buffer_pool)에 보관된다. 메모리에 보관된 데이터는 정전이나 운영체제 장애가 발생할 경우 유실될 우려가 있다. flush 행위가 발생해야만 비로소 디스크에 영구적으로 저장된다. MySQL은 기본 설정 상태에서 매 트랜잭션 커밋 시점마다 flush를 발생시켜 데이터베이스의 원자성을 보장한다. 반면 잦은 flush는 결국 I/O 블로킹으로 이어져 응답 속도를 늦추는 결과를 초래한다. 극단적인 퍼포먼스가 필요할 경우 이 값을 수정하여 응답 속도를 4배 이상 향상시킬 수 있다.

 

– 0 : 각 트랜잭션 시 아무런 기록을 하지 않고 로그 버퍼는 로그 파일에 기록되어 1초마다 디스크로 플러시 

– 1 : 트랜잭션을 수행할 때마다 버퍼가 로그 파일에 기록되고 로그 파일은 다시 디스크로 플러시

– 2 : 데이터베이스가 각 트랜잭션을 완료 시 로그 파일에 기록되고 로그 파일은 1초마다 디스크로 플러시

 

가장 성능이 좋은 것은 0을 선택하는 것이고, 1을 선택하면 가장 높은 신뢰성을 보여 준다고 한다.

 

⑧ innodb_io_capacity

결국 InnoDB 의 변경 작업 성능은 플러쉬 속도, 즉 스토리지의 I/O 속도에 의존적일 수 밖에 없다. 즉, 플러쉬의 속도가 InnoDB의 한계 성능을 결정짓는 가장 중요한 요소 중의 하나이기 때문에, 성능 좋은 디스크를 사용하는 것이 InnoDB 의 변경 작업에 대한 성능을 올리는 최선의 방법 중의 하나이다.

적어도 스토리지의 I/O 성능이 InnoDB 의 데이터 변경 총량을 충분히 커버할 수 있어야 한다. InnoDB 는 디스크의 속도에 따라 자동으로 플러쉬 속도를 조절하지 않는다.

 

따라서, innodb_io_capacity 값은 현재 사용하고 있는 디스크의 IOPS와 유사한 값으로 설정하도록 한다.

사용 가능한 값은 100~2**64(플랫폼 비트)-1이다. 기본 값은 200이다.

일반적으로 7200 RPMs 하드 드라이브인 경우 100으로 설정

RAID 구성이나 SSD 사용 등에 따라서 더 높은 값으로 설정

Raid1 : 200. Raid10 : 200 x write arrays. SSD : 5000. FusionIO : 20000

 

⑨ innodb_lock_wait_timeout

InnoDB 트랜잭션의 타임아웃은 롤백이 진행되기 전에 락을 대기하는 시간이다. InnoDB는 자동으로 자신의 락 테이블에 있는 트랜잭션 데드락(deadlock)를 검사하고 트랜잭션을 롤백한다. InnoDB는 LOCK TABLES 명령문을 사용해서 락 세트를 알려준다. 

 

기본 값은 50초이다.

 

⑩ innodb_write_io_threads

InnoDB에서 쓰기 작업에 대한 I/O 스레드 수입니다. 

 

사용 가능한 값은 1~64이다. 기본 값은 4이다.

 

⑪ innodb_read_io_threads

InnoDB에서 읽기 작업에 대한 I/O 스레드 수입니다.

 

사용 가능한 값은 1~64이다. 기본 값은 4이다.

 

⑫ innodb_thread_concurrency

​InnoDB는 이 변수가 주는 한계와 동등하거나 작게 InnoDB 내부에 OS 쓰레드의 숫자를 유지하고자 한다. 만일 성능상의 문제가 있다면, 그리고 SHOW ENGINE INNODB STATUS 가 세마포어를 기다리는 많은 수의 쓰레드를 내 보낸다면, 쓰레드 “thrashing”을 가지고 있는 것이며, 이 변수를 보다 작게 또는 보다 크게 설정해 보아야 한다. 만일 여러분이 사용하는 컴퓨터가 많은 수의 CPU와 디스크를 가지고 있는 것이라면, 컴퓨터의 자원을 보다 많이 사용할 수 있도록 이 값을 높게 설정하도록 한다. 

 

권장하는 값은 여러분이 사용하는 시스템의 프로세스와 디스크의 전체 합이다.

이 변수의 범위는 0에서 100까지이다. 

MySQL 5.0.19 이전에는 20보다 크거나 같게 되면 무한 일관성(infinite concurrency)으로 해석이 되었다. 

5.0.19 버전 이후에는, 0을 무한으로 해석한다. 무한이라는 의미는 일관성 검사가 비활성화 되고 뮤텍스(mutex)를 획득하고 풀기 위한 오버헤드를 제거된다는 것을 의미한다.

 

(6) InnoDB 관련 메모리별 my.cnf 설정 예

64bit 8GB	64bit 16GB	64bit 32GB
innodb_additional_mem_pool_size = 8M  innodb_buffer_pool_size = 5G innodb_buffer_pool_instances = 4  innodb_flush_log_at_trx_commit = 1  innodb_flush_method=O_DIRECT  innodb_log_files_in_group = 2  innodb_log_file_size = 256M  innodb_log_buffer_size = 8M  innodb_lock_wait_timeout = 50  innodb_thread_concurrency = 8 innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_io_capacity = 200	innodb_additional_mem_pool_size = 8M  innodb_buffer_pool_size = 12G innodb_buffer_pool_instances = 8  innodb_flush_log_at_trx_commit = 1  innodb_flush_method=O_DIRECT  innodb_log_files_in_group = 2  innodb_log_file_size = 256M  innodb_log_buffer_size = 8M  innodb_lock_wait_timeout = 50  innodb_thread_concurrency = 12 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_io_capacity = 200	innodb_additional_mem_pool_size = 8M  innodb_buffer_pool_size = 24G innodb_buffer_pool_instances = 16  innodb_flush_log_at_trx_commit = 1  innodb_flush_method=O_DIRECT  innodb_log_files_in_group = 2  innodb_log_file_size = 256M  innodb_log_buffer_size = 8M  innodb_lock_wait_timeout = 50  innodb_thread_concurrency = 16 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_io_capacity = 200