InnoDB Performance tuning tips

from http://www.database.sarang.net/?inc=read&aid=16451&criteria=mysql&subcrit=&id=&limit=20&keyword=tip&page=1

InnoDB는 MyISAM과 공유하는 메모리도 있지만 대개 별도의 Buffer pool을 가지고 있습니다. 즉, MyISAM의 경우 record_buffer나 key_buffer를 사용하지만 InnoDB는 innodb_additional_mem_pool_size나 innodb_buffer_pool_size 같은 별도의 메모리를 사용합니다. 따라서 InnoDB전용 DB를 구성한다면 key_buffer나 record_buffer 에 너무 많은 메모리를 할당해서는 안되겠습니다.

중요한 메모리 관련 파라미터를 몇가지 설명하겠습니다.

1. innodb_buffer_pool_size : MyISAM의 경우 Buffer pool이 없습니다. 따라서 OS자체의 buffer cache를 사용하며 OS의 flush 메카니즘에 따라서 디스크에 쓰게 됩니다.

MyISAM의 경우 Key_buffer 나 record_buffer, sort_buffer외에도 OS의 버퍼캐시를 위해 충분한 메모리를 남겨 놓아야하겠습니다. OS의 버퍼캐쉬는 OS가 자동으로 사이즈를 동적으로 할당하며 free 명령어로 상태를 확인할 수 있습니다.

그러나 InnoDB는 오라클처럼 별도의 데이타를 위한 읽기 쓰기 버퍼 메모리를 가지고 있으며 그것이 innodb_buffer_pool_size입니다. innodb_buffer_pool_size는 색인과 데이타에 대한 Cache 데이타를 가지고 있습니다. 그래서 MyISAM처럼 Key_buffer가 필요 없는겁니다.

보통 우리가 SQL쿼리로 질의를 수행하면 DBMS는 먼저 이 buffer_pool에서 데이타를 검색하고 없을 경우 디스크에서 읽어서 buffer_pool에 올려놓으며 이것을 \물리적인 읽기\ 라고 합니다. 반면에 이미 buffer_pool에 데이타가 존재하여 디스크를 직접 읽지 않아도 되는 경우를 \논리적 읽기\라고 합니다.

당연히 논리적 읽기가 더 빠르므로 buffer_pool의 사이즈를 크게 설정할 수록 유리합니다. 그러나 무한정 크기를 늘릴 경우 스와핑을 유발할 수 있으므로 자신의 현재 메모리에서 계산하여야 합니다. 적당한 사이즈는 자신의 메모리의 50%-80%입니다.

참고로 x86머신에서는 이 innodb_buffer_pool_size 를 2G이상 설정할 수없으니 참고 바랍니다.

2. innodb_additional_mem_pool_size: 이 메모리는 \데이타 딕셔너리 정보\나 \내부의 데이타 구조\ 에 대한 정보를 담는 메모리입니다.

보통 2M정도가 적당하지만 아주 많은 테이블을 사용한다면 늘려주어야합니다.

이 메모리 공간이 부족하면 InnoDB는 OS의 메모리를 할당해서 사용하기 시작하며 error log에 warning 메시지를 남기게 됩니다.

3. innodb_flush_log_at_trx_commit : 이 파라미터는 insert 나 update 등 데이타 삽입과 밀접한 관계가 있습니다.

즉, commit 을 하였을 경우 그 즉시 commit된 데이타를 log file 에 기록할 것인지 아닌지를 설정하는 옵션입니다. 즉시 로그 파일에 기록할 경우 급작스런 정전 같은 경우 데이타 손실을 막을 수 있지만 매번 로그를 기록해야 하므로 속도가 상당히 저하됩니다.

만약 약간의 데이타 손실을 감내할 수 있다면 \0\으로 설정하시기 바랍니다.

\0\으로 설정할 경우 매 트랜잭션 마다 데이타를 로그에 기록하지 않으므로 I/O부하를 줄일 수 있으며 여러 트랜잭션을 모아서 한번의 디스크 I/O로 기록하므로 I/O횟수 자체도 줄어듭니다.

실재로 테스트 결과 이 옵션의 설정에 따라 극적으로 insert 성능이 달라지는 것을 목격하였습니다.

디폴트 설정은 \0\즉 off 상태입니다. (commit즉시 로그에 기록하지 않음)

4. innodb_log_file_size: 이 파일은 트랜잭션을 기록하는 로그 파일의 사이즈를 결정하는 옵션입니다. InnoDB는 트랜잭션을 지원하므로 이런 트랜잭션 안정성을 위한 로그 파일이 존재하여야 합니다.

처음에 데이타와 색인을 위한 버퍼풀 메모리(innodb_buffer_pool_size)를 언급하였습니다. 이러한 버퍼풀은 성능을 위한 것임에 틀림없지만 단점은 급작스런 정전 같은 경우에 버퍼풀에 있는 데이타가 손실된다는 것입니다. (주로 쓰기 버퍼) 그래서 이러한 손실을 방지하고자 log file을 두게 되며, 트랜잭션 commit이 될 때마다 로그에 꼼꼼히 기록해놓고 추후 시스템 다운이나 정전 같은 상황이 발생할 경우 이 로그 파일을 근거로 자동 복구를 하게 됩니다.

이 파일의 사이즈를 얼마로 설정하느냐도 아주 중요한 문제입니다. 이 파일은 트랜잭션을 기록하는 파일이므로 이 파일이 가득차게 되면 버퍼풀에 있는 데이타를 디스크에 쓰도록 지시하고 로그를 초기화하고 다시 써나가야 합니다.

즉 로그 파일은 무한정 커지는 것이 아니라 일정한 크기와 갯수를 가지고 순환식으로 처리 되므로 로그가 초기화되는시점에는 buffer_pool의 데이타를 디스크에 기록하여야합니다. 뒤집어 말하면 이 로그 사이즈가 작으면 작을수록 buffer_pool의 내용을 자주 디스크에 기록하여야 한다는 의미입니다. (checkpoint)

따라서 이 로그 파일의 사이즈를 buffer_pool의 15% 정도로 설정하는 것이 바람직하며 경우에 따라서는 buffer_pool 사이즈 정도로 커질 수도 있습니다.

이 로그 파일의 최대 사이즈는 모두 합쳐서 <4GB보다 작아야 한다는 제한이 있으며, 사이즈가 클 경우 반대 급부로 시스템 다운 후 재복구에 많은시간이 소요된다는 단점이 있습니다.

5. innodb_log_buffer_size : 이것은 위에서 말한 로그 파일을 기록하기 위한 버퍼 사이즈입니다. 커다란 트랜잭션을 처리할 경우 크게 잡아주는 것이 좋지만 트랜잭션이작거나 거의 없다면 크게 잡는 것은 낭비입니다.

보통 1M-8M 사이로 설정하시기 바랍니다.

몇가지 옵션들이 더 있지만 성능에 가장 극적인 영향을 주는 것들만 언급했습니다.

모니터링

MySQL 3.23.52 그리고 4.0.3 버젼 부터 InnoDB의 내부 상태를 모니터링 할 수 있도록 "SHOW INNODB STATUS" 명령어를 제공합니다.

현재 버젼은 위의 명령어를 사용할 수 없으므로

CREATE TABLE innodb_monitor(a int) type = innodb;

라는 명령어를 내리면 <hostname>.err 파일에 일정간격으로 내부 정보를 기록합니다.

다시 끄려면 DROP TABLE innodb_monitor; 명령어를 내려주셔야 합니다.

create table innodb_monitor(a int) type=innodb; 라는 명령어는 그냥 MySQL엔진에게 상태정보를 로그에 기록하라는 의미이지 실재로 테이블은 의미가 없습니다.

위의 테이블을 생성하라는 명령어를 입력하면 MySQL 엔진은 그냥 명령어로 인식합니다.

모니터링을 언급하는 이유는 튜닝을 한번하는것으로 끝나는것이 아니라 지속적으로 모니터링 하여 조정하여야 한다는 점을 강조하기 위해서 입니다.

분명히 튜닝은 정답이 없습니다. 따라서 내부 작동 원리를 정확히 이해하고 튜닝을 했을 때 효과를 볼 수 있습니다.