튜닝을 위한 팁(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하기 )값으로 운용하는게 많다.

튜닝을 위한 팁(MyISAM)

1.캐쉬

key_buffer_size, preload_buffer_size등의 캐쉬를 수정해 본다. key_buffer_size는 되도록이면 많이 할당한다. 이것은 공유메모리이다. 메모리가 충분히 있다면 myisam_use_mmap를 사용해서 모든 MyISAM테이블을 메모리에 매핑하는 것도 검토해 본다.

2.데이터 테이블, 인덱스 파일을 별도의 디스크에 보존한다.

I/O의 분산처리에 도움이 된다. CREATE TABLE문장에 DATA DICTIONARY, INDEX DICTIONARY를 지정하면 MYD파일, MYI파일의 보존 디렉토리를 변경할 수 있다.

3.써넣기하지 않는 테이블은 압축

써넣기가 없는 테이블은 "myisampack -v 테이블명", "myisamchk -rq 테이블"를 실행해서 테이블을 압축한다. 압축하면 파일사이즈도 작게되고 그로인해 디스크로부터 읽어들이는 양도 줄어 들게 되므로 경우에 따라서는 속도향상도 기대된다.

튜닝을 위한 팁(전체)

1. 쿼리와 테이블 구조
slow query log에 기록되는 쿼리를 수정한다. 또 인덱스도 적절하게 작성한다.
JOIN 이나 GROUP BY 등으로 임시 테이블을 사용하는 빈도를 낮춘다.

2. 쿼리 캐쉬 사용
같은 SELECT문이 실행된다면 효과가 기대된다.

3. 바이너리 로그 파일등은 별도의 디스크에 보존
I/O를 분산처리한다.

4. prealloc영역
한개의 스레드로 많은 쿼리를 처리해야하는 경우 사용할 수 있는 변수이다.
쿼리 해석등에 사용하는 메모리는 그 때마다 할당되고 해제되지만 prealloc변수는 미리 할당한 영역을 해제하지 않고 그 다음 처리에도 사용한다. prealloc변수에는 다음과 같은 것이 있다.
query_prealloc_size
transaction_prealloc_size

5. 메모리 처리량에 주의
sort_buffer_size , join_buffer_size, read_buffer_size, read_rnd_buffer_size 를 늘리면
쿼리의 처리가 빨라질 수 있지만 너무 많이 늘리게 되면 메모리가 부족하게 되므로 조심해야한다. ( 이것들은 스레드단위로 메모리가 할당된다. )

6. 스레드 캐쉬, Listen
접속이나 절단이 많은 경우는 thread_cache_size를 늘려서 재이용할 수 있는 스레드 수를 확보한다. MySQL은 접속이 있을 때마다 자식 스레드를 작성하고 절단할 때에 그 자식 스레드를 파기한다. 이것을 몇개정도는 파기하지 않고 새로운 접속에 준비에 둘까하는 것을 지정하는 것이 thread_cache_size이다. 또, back_log로 Listen의 수를 늘려두면 접속반응이 좋아진다.

7. 테이블 핸들러의 확보
동시접속수가 많은 경우, 한번에 사용할 수 있는 테이블 핸들러의 최대수를 table_cache 지정으로 늘린다. 모든 클라이언트가 사용할 정도의 테이블 수만큼 확보해두면 낫지않을지 싶다. 이것이 적을 경우 테이블 핸들러의 close, open이 증가한다. MySQL설정예에서는 1024, 2048등이 사용되고 있다.