개요: 데이타베이스 서버를 설정하거나 튜닝함으로써, 그리고 MySQL을 위해 솔라리스 자체를 최적화 함으로써 솔라리스 플랫폼 상에서 MySQL의 퍼포먼스를 극대화 할 수 있습니다. 이 문서는 튜닝 파라미터 등을 설정하고 여러분의 환경에서 튜닝하는 것을 돕기 위해 작성되었습니다.
소개
MySQL은 세계에서 가장 많이 쓰이는 오픈소스 데이타 베이스 중 하나 입니다. MySQL의 강점은 훌륭한 퍼포먼스와 확장성이고 그러한 강점으로 인해 엔터프라이즈 환경에서 웹사이트나 데이타 웨어 하우스 그리고 각종 데이타 위주의 어플리케이션에 잘 맞습니다.
솔라리스 상의 MySQL에서 퍼포먼스를 극대화 하기 위해서는 데이타 베이스 서버의 설정과 튜닝도 중요하고 또한 MySQL을 위한 솔라리스의 최적화도 중요합니다. 모든 워크로드에 동일하게 적용되는 범용 MySQL 서버 튜닝 파라미터는 존재하지 않습니다;각각의 특수한 워크로드, 하드웨어, 운영체제에 따라서 적합한 파라미터가 결정 됩니다. 이 문서는 이러한 파라미터를 정의하고 튜닝하는 방법에 대해 도움을 주기 위해 쓰여졌습니다.
MySQL은 MyISAM, InnoDB, HEAP, 그리고 Berkeley DB (BDB)를 포함한 몇가지 스토리지 엔진들을 포함하고 있습니다. InnoDB와 BDB 스토리지는 원자적, 일관적, 독립적, 그리고 안정적인 트랜젝션을 커밋, 롤백 그리고 오류 복구와 함께 지원합니다. 그리고 오직 InnoDB만이 열단위의 락을 논-라킹(non-locking) 읽기와 함께 기본으로 제공합니다.
InnoDB 스토리지 엔진은 4가지의 독립된 레벨을 지원 합니다: read uncommitted, read committed, repeatable read, 그리고 직렬화. InnoDB 는 또한 외래 키 무결성이라는 참조 무결성으로 불리는 기능을 지원하고 프라이머리 키를 이용한 빠른 레코드 검색을 지원합니다. 이러한 강력한 함수와 기능 때문에 InnoDB는 종종 엄청난 크기의 로드가 걸리는 시스템에 사용됩니다. 이 문서는 솔라리스10에서 CPU 사용율, 메모리, 디스크 드라이브 리소스등을 효과 적으로 사용할 수 있는 다양한 방법을 다룹니다. 또한 주제들에서는 최적화된 라이브러리를 사용하고 썬 스튜디오 11 소프트웨어 를 사용하여 64비트 MySQL을 컴파일 하며 솔라리스의 UFS 파일 시스템을 튜닝하고 MySQL 서버를 설정하고 InnoDB 스토리지 엔진을 튜닝 하는 것을 다룹니다.
InnoDB 유저 쓰레드
MySQL은 단일 프로세스이고 멀티 쓰레드 어플리케이션 입니다. 하나의 메인 쓰레드는 서버를 컨트롤하기 위한 쓰레드로 모든 다른 MySQL 쓰레드에 비해 가장 우선순위가 높습니다. 메인 쓰레드는 대부분의 시간에 idle한 상태이고 매 300ms(millisecond)마다 깨어나서 버퍼 풀에서 더러운 블럭을 내보내는 류의 특수한 작업이 필요한지 체크하게 됩니다.
메인쓰레드에 덧붙여서 유저쓰레드 셋은 보통의 우선순위를 가지고 쓰레드 풀 안에서 실행되며 클라이언트의 요구를 동시에 처리하게 됩니다. 각 클라이언트 요구에 대해 하나의 쓰레드가 클라이언트의 요구를 처리하고 결과가 준비되는 대로 바로 각 클라이언트에 돌려 줍니다. 그리고 하나의 유저 쓰레드는 콘솔에서의 입력을 기다리고 하나의 유틸리티 쓰레드 그룹은 가장 낮은 우선순위를 가지고 수행되면서 백그라운드 작업을 처리하게 됩니다.
현재 MySQL은 클라이언트 요구를 처리하는 유저 쓰레드의 갯수면에서 확장성이 좋지 않습니다. 퍼포먼스는 어느 정도의 퍼포먼스 한계점까지만 효율적으로 확장됩니다. 그 후에는 유저 연결의 수가 증가하면 할수록 MySQL의 퍼포먼스는 떨어지게 됩니다. 왜냐하면 쓰레드의 동시실행성에 충돌이 생기기 때문입니다. 유저의 연결수가 튜닝이 가능한 어플리케이션에서 개발자는 각 다양한 워크로드에 대하여 가장 최적의 유저 접속수를 결정할 필요가 있습니다.
우리는 SysBench CPU-바운드의 벤치 마크 테스트를 4-way 울트라SPARC IV 프로세서 기반에서 수행하였습니다. (1M-열 데이타는 InnoDB 데이타와 인덱스 캐쉬 버퍼에 채워질 수 있습니다) MySQL 퍼포먼스는 16 유저 연결에서 가장 높았고 32 유저 연결에서 부터 서서히 떨어지기 시작합니다. 그래프를 참조 바랍니다. (주의: 결과는 다양해 질 수 있음.)
 |
표 1: MySQL 5.0.7 SysBench 연결 확장성 테스트 |
이 테스트는 SysBench 워크로드에서의 결과를 보여주고 울트라SPARC IV 기반의 서버에서의 최대 퍼포먼스는 유저의 접속수를 4*CPU수로 설정함으로써 얻어질 수 있음을 보여줍니다. 최대 MySQL 퍼포먼스 확장성은 4개의 CPU에서 거의 선형으로 확장되고 확장성의 비율은 8개의 CPU에서 부터 떨어짐을 알 수 있습니다. 다음 표는 1-24 울트라SPARC IV 프로세서에서의 확장성을 보여줍니다. (주의: 결과는 다양해 질 수 있음.)
 |
표 2: MySQL 5.0.7 SysBench CPU 확장성 테스트 |
유저 접속수가 튜닝이 불가능한 어플리케이션에서 innodb_thread_concurrency 파라미터는 InnoDB가 최대로 가질 수 있는 동시 쓰레드의 갯수를 설정이 가능합니다. show innodb 상태에 있는 큐에 많은 쿼리들이 걸려 있다면 이 값을 증가시킬 필요가 있습니다. 이 값을 1000으로 셋팅한다면 동시성 체크를 비활성화 하게 됩니다. 그러므로 동시에 서버 내에 존재하는 다양한 종류의 태스크를 처리할 많은 쓰레드가 존재하게 될 것입니다. 솔라리스 플랫폼상의 몇몇 워크로드에서 거대한 유저 레벨 락을 본다면 (prstat -mL 을 이용해 LCK 값 출력), 이 값을 감소시킴으로써 CPU 사용율의 효율을 높일 수 있습니다. 수행중인 시스템의 동작에 따라 이 파라미터를 튜닝함으로써 퍼포먼스에 큰 영향을 줄 수 있습니다.
time(2) 시스템 콜을 최소화 하기 위한 최적화된 Time 라이브러리 사용법
솔라리스 플랫폼에서 time(2) 시스템 콜은 커널에 트랩을 수행시켜서 gethrestime()을 호출합니다. 사실 이 작업은 많은 자원을 소비 하는 작업입니다. MySQL이 각 쿼리를 수행할때 time(2) 시스템콜을 시작, 종료시에 수행함으로써 쿼리가 얼마만큼의 시간이 걸렸는지 측정합니다. 몇몇 워크로드상에서 MySQL은 시스템 타임의 30% 이상을 이 time(2) 시스템 콜에 낭비합니다. 다음의 결과를 참고 바랍니다:
# truss -c -p 385
syscall seconds calls errors
read 28.286 450958 3248
write 19.516 231648
open .000 2
close .000 2
time 45.247 848307
lseek .329 6878 6800
alarm .140 2218
fdsync 1.140 5520
fcntl .364 6510
lwp_park 12.288 187383
lwp_unpark 11.134 187381
poll 4.535 67263
sigprocmask 2.072 36030
sigtimedwait .381 2506
yield .741 9829
lwp_kill .201 2512
pread .000 2
pwrite 1.040 5527
-------- ------ ----
sys totals: 127.447 2051007 10048
usr time: 101.288
elapsed: 123.750
|
솔라리스 플랫폼에서는 time(2) 시스템 콜을 최적화 해서 커널에 gethrestime() 을 호출하는 대신 좀 더 빠른 gethrtime(3C) 시스템콜을 구현 할 수 있습니다. 만약 사용자의 워크로드가 time(2) 시스템 콜에 시스템 CPU 자원의 많은 부분을 소비한다면 (truss 출력을 통해 확인 할 수 있음), MySQL DB를 A Performance Optimization for C/C++ Systems That Employ Time-Stamping 에 기술된 최적화 된 시간 라이브러리를 이용해 링크 하는것이 훨씬 좋은 선택입니다. 추가적으로 gethrtime(3C) 을 구현하기 위해 MySQL 소스트리 홈에 있는 configure 파일에 -lfasttime 를 추가시켜 줍니다. 우리는 OSDL Database Test 2 (DBT2) 워크로드 테스트를 수행 시켰고 최적화된 time(2) 를 통해서 8-way 울트라SPARC 시스템에서 약 7% 정도의 성능 향상을 얻을 수 있었습니다.
MySQL에서 솔라리스 mtmalloc 메모리 할당자 사용하기
솔라리스 malloc 루틴은 메모리 풋 프린트 측면에서 매우 잘 동작합니다; 어쨌든 libc 에 있는 싱글-쓰레드 malloc 은 큐에 있는 메모리 할당 요구를 하나씩 단계적으로 처리 함으로써 멀티쓰레드 어플리케이션에서 성능의 저하를 가져 옵니다. 솔라리스는 멀티쓰레드 어플리케이션에서 메모리 할당을 향상 시킬 수 있도록 mtmalloc, libumem, 그리고 hoard 같은 malloc 방법을 구현하고 있습니다. 메모리 할당 패턴이 각각 다른 어플리케이션에서는 어떤 malloc 구현이 가장 최적의 것인지 알아내기 힘듭니다.
MySQL 은 갑작스런 대용량의 스트링을 능동적으로 처리하기 위해 malloc() 과 free() 를 사용합니다. malloc 호출은 HEAP 경쟁을 방지 하기 위해 mysqld 을 블럭 시킵니다. 다른 메모리 할당자의 성능을 검사 했을때 즉 malloc 을 mtmalloc 으로 바꿈으로써 큰 MySQL DB 퍼포먼스를 얻을 수 있었습니다. 우리는 SysBecnh CPU 벤치마크 테스트를 수행했을때 약 65%의 성능향상을 얻었음을 알 수 있습니다. 우리는 8-way 듀얼-코어 울트라SPARC IV 프로세서 기반의 시스템에서 MySQL 5.0.7을 이용하여 이러한 결과를 도출해 냈습니다.
mtmalloc 을 사용하기 위해 솔라리스 플랫폼에 MySQL 시작 스크립트에 LD_PRELOAD 또는 LD_PRELOAD_64 환경 변수를 설정함으로써 mtmalloc 라이브러리를 미리 로딩 할 수 있습니다. 그럼으로써 MySQL DB를 재빌드 할 필요가 없습니다.
만약 32비트 MySQL이라면, LD_PRELOAD 를 다음과 같은 방법으로 세팅하여 mtmalloc 을 미리 로드 시킵니다:
LD_PRELOAD=/usr/lib/libmtmalloc.so (x86)
LD_PRELOAD=/usr/lib/libmtmalloc.so(sparc)
|
만약 64비트 MySQL이라면, LD_PRELOAD_64 를 다음과 같은 방법으로 셋팅합니다:
LD_PRELOAD_64=/usr/lib/amd64/libmtmalloc.so(x64)
LD_PRELOAD_64=/usr/lib/sparcv9/libmtmalloc.so(64-bit sparc)
|
솔라리스에서 64비트 MySQL 사용하기
솔라리스는 32비트 어플리케이션과 완벽 호환되는 64비트 컴퓨팅 환경을 제공합니다. 그러므로 32비트, 64비트의 MySQL 모두 솔라리스 상에서 잘 동작합니다. 32비트 MySQL과 비교하여 64비트 MySQL은 데이타 캐쉬, 코드 캐쉬, 메타 데이타 캐쉬를 위해 좀 더 많은 메모리를 사용함으로써 디스크 I/O 작업을 줄여 줍니다. 추가적으로 64비트 컴퓨팅 환경의 확장된 CPU 동작을 통해서 64비트 MySQL은 32비트의 MySQL보다 빠르게 동작합니다. 다음의 표는 8-way 울트라SPARC IV 기반 서버에서 SysBench 의 CPU-바운드 테스트 결과를 보여줍니다.
 |
표 3: MySQL 4.1.11 CPU-바운드 SysBench 테스트 |
썬 스튜디오 11을 이용하여 MySQL 빌딩하기
썬 스튜디오 11을 이용하여 솔라리스 플랫폼 상의 64비트 MySQL을 빌드하기 위해 다음과 같은 컴파일러 플래그와 옵션을 사용합니다:
솔라리스 x64 플랫폼에서:
CC=cc CFLAGS="-xO3 -mt -fsimple=1 -ftrap=%none -nofstore
-xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron
-xarch=amd64 -xregs=no%frameptr"
CXX=CC CXXFLAGS="-xO3 -mt -fsimple=1 -ftrap=%none -nofstore
-xbuiltin=%all -xlibmil -xlibmopt -xtarget=opteron
-xarch=amd64 -xregs=no%frameptr"
LDFLAGS="-xtarget=opteron -xarch=amd64"
./configure --prefix=/usr/local/mysql
--localstatedir=/usr/local/mysql/data
--libexecdir=/usr/local/mysql/bin --with-extra-charsets=complex
--with-server-suffix=-standard --enable-thread-safe-client
--enable-local-infile --with-named-curses=-lcurses
--with-big-tables --disable-shared --with-readline
--with-archive-storage-engine --with-innodb
|
SPARC 플랫폼용 솔라리스에서:
CC=cc CFLAGS="-mt -fsimple=1 -ftrap=%none
-xbuiltin=%all -xlibmil -xlibmopt -xstrconst -xarch=v9"
CXX=CC CXXFLAGS="-noex -mt -fsimple=1
-ftrap=%none -xbuiltin=%all -xlibmil -xlibmopt
-xarch=v9"
./configure --prefix=/usr/local/mysql
--localstatedir=/usr/local/mysql/data
--libexecdir=/usr/local/mysql/bin
--with-extra-charsets=complex
--with-server-suffix=-standard
--enable-thread-safe-client --enable-local-infile
--with-named-z-libs=no --with-big-tables
--disable-shared --with-readline
--with-archive-storage-engine --with-innodb
|
서로 다른 컴파일러 버젼(썬 스튜디오 10 과 11)에서 빌드 된 MySQL의 성능을 비교하기 위해서 우리는 DBT2 테스트 스위트를 사용했습니다. 이 워크로드는 도매상이 서로 다른 소매상들과 그에 관련된 상점에서의 주문을 처리하는것을 나타 냅니다. 이 태스크는 읽기-전용, 업데이트 위주의 트랜젝션이 혼합되있는 배달 주문, 결제사항 기록, 주문상태 체크, 그리고 소매상의 재고량등으로 이루어져 있습니다. 9가지 테이블에 각각 소매상, 위치, 아이템, 재고량, 고객, 주문, 새로운 주문, 주문-라인과 과거 기록들이 포함되어 있고 소매상의 숫자에 따라 확장되도록 되어 있습니다. (아이템 테이블 제외).
우리는 10개의 소매상 데이타 베이스를 솔라리스10에 4개의 듀얼코어 2200-MHZ AMD 옵테론 기반의 썬 Fire 40z 서버를 이용하여 DBT2 테스트를 수행하였습니다. 이 테스트에서 대부분의 데이타 베이스 쿼리들은innodb 버퍼에 캐쉬 됩니다. 대부분의 CPU 타임은 쿼리를 처리하는데 사용 되었음으로 이 테스트에서 시스템은 CPU 바운드 작업을 했다고 볼 수 있습니다. 테스트 항목은 처리량으로써 분당 새로운 주문 트랜젝션을 의미 합니다. 다음 표의 결과 데이타 대로 MySQL은 썬 스튜디오 11을 이용하여 빌드 됨으로써 썬 스튜디오 10으로 빌드된 MySQL에 비해 13%의 성능 향상이 있었습니다. (주의: 결과는 다양해 질 수 있음.)
 |
표 4: 솔라리스 10에서 MySQL 5.0.15 DBT2 테스트 |
썬 스튜디오 10 대신 썬 스튜디오 11로 빌드한 MySQL 데이타베이스의 성능향상에 덧붙여서 썬 스튜디오 11은 울트라SPARC 기반 시스템에서 멀티코어 그리고 칩 멀티쓰레딩(CMT) 최적화를 제공합니다. 썬 스튜디오 11은 또한 향상된 그래픽 기반 디버거 툴을 제공함으로써 손쉽게 브레이크 포인트를 지정하고 변수 값을 검사하고 콜 스택을 조회하고 MySQL 내의 멀티쓰레드 코드들을 디버그 할 수 있습니다. 썬 스튜디오 11의 향상된 퍼포먼스 분석 툴은 울트라SPARC 기반 시스템에서 어플리케이션의 메모리 참조와 관련된 퍼포먼스 비용을 프로파일링 할 수 있는 기능을 제공합니다. 썬 스튜디오 11에 추가된 새로운 디버거, 퍼포먼스 분석기에 대한 자세한 사항은 다음의 유저 가이드를 통해 얻으 실 수 있습니다.
파일 시스템 퍼포먼스 최적화 하기
파일 시스템 클러스터 사이즈는 시스템 퍼포먼스에 큰 임팩트가 될 수 있습니다. -- 특별히 MySQL이 시스템의 메모리보다 큰 워크로드를 데이타 베이스상에서 수행하고 있을때. 솔라리스 플랫폼에서 UFS 파일 시스템 클러스터 사이즈 (the maxcontig 파라미터) 는 기본적으로 128로 설정되어 있습니다. SPARC 플랫폼상에서 파일 시스템의 블럭사이즈는 8 Kbytes 이고 x86/x64 플랫폼의 솔라리스의 블럭 사이즈는 4 Kbytes입니다. 이것은 전체 파일 시스템을 클러스터의 길이 만큼 미리 읽어 들이고 (128*8 Kbytes 또는 128*4 Kbytes), 랜덤 I/O의 경우에서도 똑같이 동작함으로써 디스크의 퍼포먼스를 현격하게 저하시킵니다.
이 문제를 해결하는 방법은 maxcontig 파라미터의 값을 줄임으로써 디스크 I/O 전송 사이즈를 DB의 블럭 사이즈와 일치 시키는 것입니다. maxcontig 값은 tunefs -a maxcontig# 커맨드를 이용해서 조정할 수 있습니다. 이 방법의 단점은 클라이언트로 부터의 대용량 순차적 I/O 워크로드의 성능에 영향을 줄 수 있다는 것입니다.
또 다른 솔루션으로는 파일시스템을 --forcedirectio 옵션으로 마운트 함으로써 파일시스템 Direct I/O를 활성화 시키는 것입니다. 그럼으로써 Direct I/O는 자동적으로 미리 읽기를 비활성화 시킵니다. 추가적으로 MySQL은 고유의 데이타와 캐쉬 버퍼를 가지고 있으므로 Direct I/O를 사용함으로써 이중으로 버퍼링을 하는데 사용되는 CPU 사이클을 줄일 수 있습니다. 다음의 표는 썬 Fire V65x 서버에서의 SysBench I/O 바운드 테스트 결과 입니다. (100M-열 데이타는 InnoDB 데이타와 인덱스 캐쉬에 들어 갈 수 없습니다). 이 테스트는 기본 maxcontig 파라미터 값을 사용했을때와 maxcontig 를 5로 셋팅했을때(디스크 트랜스퍼 사이즈는 5*4 Kbytes), 그리고 Direct I/O를 사용했을때의 퍼포먼스 차이를 보여줍니다. (주의: 결과는 다양해 질 수 있음.)
 |
표 5: MySQL 4.1.11 I/O-바운드 SysBench 테스트 |
InnoDB 데이타 그리고 인덱스 케쉬 사이즈
MySQL 은 디스크를 직접적으로 엑세스 하지 않습니다; 대신 데이타를 내부 버퍼 캐쉬로 읽어 들이고 블럭을 읽고 쓰고 변경 사항을 디스크에 기록합니다. 만약 서버가 캐쉬내의 데이타를 요구 한다면 데이타는 바로 처리 될 것입니다. 그렇지 않다면 운영체제는 데이타를 디스크에서 읽어 들이도록 요구할 것입니다. 큰 사이즈의 캐쉬 사이즈는 디스크 액세스 횟수를 줄여 줄 것입니다. 기본 값인 8 Mbytes 는 대부분의 워크로드에서 너무 작습니다. 개발자는 iostat -xnt 5 의 출력에서 %b (디스크의 활용율)이 60 퍼센트가 넘어 갈때 혹은, svc_t (응답 시간) 이 35msec이 넘어 갈때 값을 증가시킬것을 권장합니다. 그리고 show innodb status 출력의 FILE IO 파트에서 많은 양의 읽기 작업이 나타남을 확인 할 수 있습니다.
중요한 점은 충분한 RAM 용량이 없이 수행되는 다른 프로세스들이 비용이 많이 드는 페이징을 수행 하지 않도록 innodb_buffer_pool_size 파라미터 값을 너무 높게 잡지 않는 것입니다. 단일 MySQL 프로세스 환경에서는 MySQL 프로레스가 2~3Mbytes의 용량만 차지한다고 했을때 innodb_buffer_pool_size 파라미터를 메모리의 70~80%정도로 높여 주는 것도 가능합니다.
트랜젝션 로그 Flush 모드
InnoDB 는 백그라운드에서 약 1초에 한번씩 로그를 디스크로 기록합니다. 기본적으로 로그는 각각의 트랜잭션이 커밋 될때 마다 일어 납니다. 갑작 스런 MySQL, OS, 혹은 하드웨어의 오류 상황에서 트랜젝션을 안전하게 수행 할 수 있는 방법은 innodb_flush_log_at_trx_commit = 1 모드를 사용하는 것입니다.
많은 작은 용량의 트랜젝션으로 이루어진 워크로드에서 innodb_flush_log_at_trx_commit 파라미터를 다른 값으로 설정함으로써 디스크 쓰기 작업을 줄여줄 수 있습니다.
만약 이 값을 0으로 설정한다면 각각의 트렌잭션보다 로그를 기록하지 않을 것이고 디스크 I/O의 퍼포먼스를 향상시켜 줄 것입니다. 그러나 만약 MySQL에 갑작스럽게 오류가 생긴다면 모든 트랜잭션은 날아가 버릴 것입니다.
만약 이 값을 2으로 설정한다면 로그를 디스크에 기록하는 대신 OS 캐쉬(파일 시스템 캐쉬)에 기록합니다. 이 방법을 통해 디스크 I/O를 줄여 주지만 0으로 설정했을 때보다 약간의 퍼포먼스 저하가 있습니다;대신 트렌젝션의 손실이 이루어지지 않을 것입니다. (비록 OS나 하드웨어의 문제로 손실이 발생할 수 있더라도).
로그 버퍼 사이즈
대용량의 트랜젝션에서 로그 버퍼가 각각의 트랜젝션 커밋마다 기록 되기 전에 innodb_flush_log_at_trx_commit 을 1로 설정했다면 로그는 디스크의 I/O를 줄여 주기 위해 디스크에 로그를 기록 하는 대신 로그 버퍼에 기록 되게 됩니다. 만약 수행중인 상태에서 show innodb status 출력에서 대용량의 I/O 작업을 확인 했다면 innodb_log_buffer_size 파라미터를 좀 더 큰 값으로 설정할 것을 요구합니다. 대용량의 트랜잭션이 없는 대부분의 워크로드에서는 로그 버퍼의 용량을 늘림으로써 쓸때 없는 메모리 낭비를 할 필요가 없습니다. 보통 8~64 Mbytes의 용량이 적당합니다.
체크포인트 작업
InnoDB 에 있는 복구 관리 서브시스템은 백업과 복구를 위해 데이타 베이스 페이지와 트랜잭션을 로그 파일로 기록합니다. 이 시스템은 조금 복잡한 방법으로 구현 되어 있습니다. 계속적으로 수정된 데이타 베이스 페이지들을 작은 일괄작업을 통해서 버퍼 풀에서 부터 꺼내와서 기록합니다. InnoDB는 순환적으로 로그파일을 기록하고 있습니다. 그러므로 로그 파일이 innodb_log_file_size 파라미터에 기록된 제한 크기에 도달했다면 체크포인트 작업이 수행되어서 한번에 수정된 모든 데이타베이스 페이지들을 기록합니다. 이 작업을 통해서 복구 작업시에 수정된 모든 페이지들이 로그파일에 기록됨으로써 완벽하게 복구 작업을 할 수 있도록 보장해 줍니다.
로그 파일의 사이즈는 너무 잦은 체크포인트 작업이 일어 나지 않도록 잘 결정 되어야 합니다. 큰 로그 파일 사이즈는 체크 포인트시에 디스크 I/O 작업을 줄여줄 것입니다. show innodb status 출력의 BUFFER POOL AND MEMORY 파트에서 대용량의 페이지 쓰기가 있음을 확인 했다면 좀 더 큰 값으로 설정하기를 권장합니다. 이 경우 큰 용량의 로그 파일은 시스템 복구시 시간이 좀 더 걸립니다.
쿼리 캐쉬 사이즈
데이타, 인덱스 버퍼 캐쉬 에 덧붙여서 MySQL 4.0.1 혹은 이후 버젼은 쿼리 캐쉬라고 불리는 멋진 기능을 가지고 있습니다. 쿼리 캐쉬란 클라이언트가 서버에게 요청한 SELECT 쿼리 와 동일한 쿼리를 저장하는 것을 의미 합니다. 이것은 반복적이고 파싱하기 힘든 일들을 반복하는 일이 없이 다시 똑같은 쿼리를 처리 하는데 크게 도움을 줍니다. MySQL 은 또한 쿼리 캐쉬에 쿼리의 결과 셋들을 저장함으로써 디스크 혹은 메모리 캐쉬에 의해 발생되는 복잡한 쿼리의 결과 셋을 만드는 오버헤드를 줄여 줍니다. 몇몇 어플리케이션들은 동일한 쿼리를 수행함으로 쿼리 캐시는 응답시간을 크게 줄여 줍니다.
query_cache_size 파라미터는 자주 수행되는 쿼리들의 캐쉬를 수행하는 메모리의 용량을 결정하는데 사용합니다. 그럼으로써 실제 쿼리를 수행 하는 대신 결과를 클라이언트에 전달합니다. query_cache_type 파라미터는 쿼리 캐시를 활성화 혹은 비활성화 하는데 이용 됩니다. 이 두개의 파라미터를 설정하는 것을 결정할때에는 qcache_inserts, qcache_hits, 그리고 qcache_free_memory 를 실행시에 확인 하는 것이 필요 합니다. qcache_inserts 는 쿼리 케쉬에 몇개의 쿼리가 추가 됐는지 알려주고, qcache_hits 는 몇개의 쿼리가 실제 쿼리 수행 없이 쿼리 캐쉬에서 가져와 졌는지 알려주고, qcache_free_memory 는 쿼리 캐쉬가 사용하지 않은 사용 가능한 용량이 얼마정도인지 보여줍니다.
만약 실행시 총 쿼리수에 비교해서 qcache_hits 의 값이 높다면 혹은 qcache_free_memory 의 값이 낮다면 query_cache_size 파라미터 값을 그에 맞춰서 증가시키는 것이 권장됩니다. 그렇지 않다면 query_cache_size 파라미터 값을 감소 시켜서 다른 MySQL 캐쉬 버퍼를 위해 메모리를 절약할 것을 권장합니다. 만약 qcache_hit 가 실행시에 0이라면 query_cache_type 을 0으로 설정하고 동시에 setting query_cache_size 를 0으로 설정함으로써 쿼리 캐쉬를 비활성화 시켰을 것입니다.
query_cache_limit 파라미터는 쿼리 캐쉬에 저장되는 최대 결과 셋의 수를 지정합니다. 실행시에 qcache_insert 에 비해 낮은 비율의 qcache_hits는 query_cache_limit 파라미터값을 너무 낮게 잡음으로써 발생하였을 것입니다. 이 경우에는 query_cache_limit 파라미터 값을 증가 시킴으로써 쿼리 캐쉬에 좀더 많은 결과 셋을 저장할 것을 권장합니다.
결과
추가적인 퍼포먼스 향상과 확장성은 MySQL내의 스토리지 엔진을 튜닝함으로써 썬 시스템에서 돌아가는 특정한 워크로드에 대한 향상을 이끌어 낼 수 있습니다. 퍼포먼스에 영향을 줄 수 있는 많은 변수들과 각 워크로드별로 많은 튜닝이 가능한 파라미터들이 있기 때문에 이 문서에서는 가장 일반적인 가이드 라인과 실용적인 조언을 줄 수 있도록 쓰여 졌습니다. 이 문서에 대한 어떠한 피드백이나 썬 시스템에서의 MySQL 성능 향상에 관한 의견도 환영합니다.
관련 자료
저자에 관하여
Luojia Chen 은 썬의 Market Development Engineering 조직에서 일하고 있고 오픈 소스 팀에 속해 있습니다. 그녀는 현재 썬의 최근 기술에 MySQL을 접목시키는 일을 하고 있습니다. 그녀는
luojia.chen@sun.com 로 연락이 가능합니다.
