튜닝을 위한 팁(InnoDB)

1.데이터 파일, REDO로그 파일을 별도의 디스크에 보존
innodb_data_file_path, innodb_log_group_home_dir

파일을 물리적으로 다른 디스크에 배치함으로 I/O처리를 분산 시킬 수 있다.

2.데이터 파일은 autoextend로 하지 않기
innodb_data_file_path

자동확장을 수행하지않게 함으로 I/O처리를 줄인다.

3. 데이터파일은 2개 이상으로 한다.
innodb_data_file_path

테이블 영역을 구성하는 데이터 파일이 1개면 성능이 나오지 않는다. 데이터 파일을 여러개로 하자.

4. 캐쉬
innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size

기본값은 보통은 작다. 이것들은 공유메모리이다.
innodb_buffer_pool_size는 레코드나 인덱스의 캐쉬등에 사용되므로 가능한한 크게 설정한다.
innodb_additional_mem_pool_size는 데이터 사전등의 보존을 위해서 사용되지만 20MB정도로 할당한다.

5. 한개의 트랜잭션의 크기를 고려해서 REDO로그 파일의 크기나 버퍼의 크기를 결정한다.
innodb_log_buffer_size, innodb_log_file_size, innodb_log_files_in_group

너무 작으면 디스크에 써넣는 횟수가 많아지게 된다. 또 innodb_log_file_size * innodb_log_files_in_group * 70%를 넘는 정도의 써넣기가 buffer pool에서 이루어지면 체크포인트가 수행되어져 버린다. innodb_buffer_pool_size이외에도 REDO로그를 고려해야한다.
기본값에서는 보통 작다.

6. innodb_doublewrite를 무효로 한다.

장애에 잘 견디기 위해서 2개의 장소에 데이터를 써넣기를 하고 있다. ( 기본값은 innodb_doublewrite = ON ) 이것을 무효로 한다. ( --skip-innodb-doublewrite )

7. innodb_max_dirty_pages_pct

InnoDB의 buffer pool중에 innodb_max_dirty_pages_pct(%)가 dirty한 부분인 경우, 체크포인트가 수행된다. buffer pool을 크게 할 수 없는 경우 , 이 값을 크게 해서 조금이나마 체크포인트를 지연시키는 방법도 있다.
그러나 이 값을 변경하는 것보다 innodb_buffer_pool_size의 값이나 innodb_log_file_size * innodb_log_files_in_group의 값을 늘리는 게 효과적이다.

8. innodb_thread_concurrency
InnoDB의 처리를 수행하는 스레드 수를 지정한다. 너무 많이 늘려놓으면 반대로 스레드 사이에 기다리는 시간이 늘어나게 된다. 이것은 운영체제의 SMP나 스레드의 구현에도 영향받게되므로 어떤 운영체제에서는 잘 돌아갔던 설정값이더라도 다른 운영체제에서는 성능이 나오지않는 경우도 있을 수 있다. 4,8,16 정도의 값으로 시도해본다.

9. flush
innodb_flush_method, innodb_flush_log_at_trx_commit

flush방법, 타이밍을 지정한다. 대부분의 경우 innodb_flush_method를 O_DSYNC로 변경한다. 또 innodb_flush_log_at_trx_commit은 기본(COMMIT할 때 flush하기 )값으로 운용하는게 많다.

InnoDB 모니터 3

SHOW ENGINE INNODB MUTEX의 출력항목

SHOW ENGINE INNODB MUTEX으로 표시되는 항목은 다음과 같다.

mysql>SHOW ENGINE INNODB MUTEX\G

***************** 1. row *************************

Type: InnoDB

Name: &purge_sys->mutex:trx0purge.c

Satus: count=2, spin_wait=0, spin_rounds=0, os_waits=0, os_yields=0, os_wait_times=0

Type는 항상 InnoDB가 된다.

Name은 소스 파일명과 mutex의 이름이다.

Status는 mutex의 상태를 나타낸다. 자세하게는 다음과 같다.

• count mutex가 몇번 요청되었나
• spin_waits spinlock이 몇번 실행되었나
• spin_rounds spinlock의 회전(round)수(spin_rounds/ sin_waits=평균회전수)
• os_waits spinlock이 동작하지 않았을 경우의 OS대기회수
• os_yields mutex가 timeslice와 yield를 포기한 횟수
• os_wait_times os대기시간(msec.)

timed_mutexes 서버변수(표준은 0)를 1로 하면 os_wait_times는 OS의 wait 시간의 합계(msec.)가 된다.

InnoDB 모니터 2

SHOW ENGINE INNODB STATUS와 innodb_monitor출력항목

SHOW ENGINE INNODB STATUS하고 innodb_monitor의 출력에 포함되는 항목(섹션)은 다음과 같다.

• SEMAPHORE mutex의 spin wait수, round수, OS의 wait수등의 통계정보
• LATEST FOREIGN KEY ERROR 최후의 Foreign Key에 관한 에러
• LATEST DETECTED DEADLOCK 최후의 deadlock의 에러
• TRANSACTIONS lock대기의 정보
• FILE I/O I/O스레드정보
• INSERT BUFFER AND ADAPTIVE HASH INDEX Insert Buffer와 Adaptive Hash Index의 상황
• LOG 로그상황
• BUFFER POOL AND MEMORY Buffer pool의 사용상황, 페이지의 이용상황
• ROW OPERATIONS 메인스레드의 상황

InnoDB 모니터(SHOW ENGINE INNODB STATUS)

InnoDB에서는 내부상태를 출력하는 InnoDB모니터 기능을 준비하고 있어 성능등의 조정에 도움이 된다.

다음과 같은 방법으로 InnoDB 모니터가 가능하다.

>InnoDB 모니터를 개시한다.

CREATE TABLE innodb_monitor( a INT) ENGINE=INNODB;

CREATE TABLE innodb_monitor를 실행하면 에러 로그파일에 15초단위로 status가 출력된다.

>InnoDB 모니터를 중지한다.

DROP TABLE innodb_monitor;

출력을 멈추게 하기위해서는 DROP TABLE innodb_monitor를 실행한다.

>광범위 정보를 모니터한다.

SHOW ENGINE INNODB STATUS;

SHOW INNODB STATUS;

SHOW ENGINE INNODB STATUS에서는 InnoDB정보가 다른 SHOW문과 마찬가지로 표준출력으로 표시된다.

SHOW INNODB STATUS는 SHOW ENGINE INNODB STATUS로 개정되었다.

>MUTEX통계를 표시한다.

SHOW ENGINE INNODB MUTEX;

SHOW ENGINE INNODB MUTEX는 MUTEX정보(상호 배제)를 표시한다.