MySQL InnoDB 성능 튜닝 기본
InnoDB 를 초기 설정할 때 고려해야할 기본적인 튜닝 파라미터를 소개한다.
위의 InnoDB 아키텍처 그림을 떠올려보면 아래의 5 가지 초기 설정이 왜 중요한지 이해할 수 있을 것이다.
- innodb_buffer_pool_size
- innodb_buffer_pool_instances
- innodb_flush_method
- innodb_log_file_size * innodb_log_files_in_group
- innodb_io_capacity
버퍼 풀의 크기 (innodb_buffer_pool_size)
버퍼 풀은 두 가지 역할을 담당한다.
첫번째는 데이터 파일과 로그 파일이 기록되는 순서를 조정하는 역할이고, 두번째는 디스크 액세스를 줄이기 위한 캐시의 역할이다.
시스템(OS)에서 파일 캐시의 크기가 클수록 성능에 유리하듯이, Database 에서도 마찬가지로 버퍼 풀의 크기가 클수록 성능에 유리하다. 특히 조회 처리를 위한 캐시 효과가 크기 마련인데, 이는 읽으려는 데이터가 메모리에 올라와 있으므로 Disk I/O 를 발생시키지 않기 때문이다.
이론적으로는 다른 버퍼에 할당하는 메모리를 제외하고는 대부분의 메모리를 버퍼 풀에 할당하는 것이 좋다.
버퍼 풀 인스턴스 수 (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_log_file_size * innodb_log_files_in_group)
InnoDB 의 로그 파일은 크기가 고정되어 있는 형태이고, 같은 파일들을 rotation 하며 사용하는 구조이다. (오라클과 동일하다.) 트랜잭션이 데이터를 변경하면 먼저 버퍼 풀의 데이터를 변경하게 되는데 플러쉬가 발생하기 전의 이러한 변경된 데이터를 더티 페이지라고 한다.
더티 페이지를 얼마만큼 허용할 수 있는가는 로그 파일의 크기와 관련이 있다. 로그 파일이 모두 차 있는 상태라면 더 이상 더티 페이지를 허용할 수 없기 때문에 플러쉬를 수행한 후 불필요한 로그 파일 공간을 재 사용해야 한다.
플러시는 I/O 작업을 의미하기 때문에 매우 느리다. 로그 공간을 충분히 확보해 두어야만 느린 플러쉬를 뒤로 미룰 수 있을 것이다.
하지만, 무조건 로그를 크게 한다고 좋은 것은 아니다. 왜냐하면, 리두 로그의 크기가 클수록 복구 시간이 길어질 수 있고, 더티 페이지의 최대 크기라고 할 수 있는 버퍼 풀의 크기보다 리두 로그의 크기를 크게 해봐야 아무 이점이 없기 때문이다.
그리고, 어느 순간에는 쌓이고 쌓인 플러쉬가 한꺼번에 몰려서 발생하게 되는데, 이때는 트랜잭션의 변경 작업이 자칫 플러쉬와 체크포인트 때문에 대기 상태가 될 수도 있다.
I/O 용량 (innodb_io_capacity)
결국 InnoDB 의 변경 작업 성능은 플러쉬 속도, 즉 스토리지의 I/O 속도에 의존적일 수 밖에 없다. 즉, 플러쉬의 속도가 InnoDB의 한계 성능을 결정짓는 가장 중요한 요소 중의 하나이기 때문에, 성능 좋은 디스크를 사용하는 것이 InnoDB 의 변경 작업에 대한 성능을 올리는 최선의 방법 중의 하나이다.
적어도 스토리지의 I/O 성능이 InnoDB 의 데이터 변경 총량을 충분히 커버할 수 있어야 한다. InnoDB 는 디스크의 속도에 따라 자동으로 플러쉬 속도를 조절하지 않는다. 따라서, innodb_io_capacity 값은 현재 사용하고 있는 디스크의 IOPS 와 유사한 값으로 설정하도록 한다.
References
MySQL 5.7 완벽 분석 (오쿠노 미키야)
InnoDB Architecture
MySQL Optimization
