튜닝을 위한 팁(전체)

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등이 사용되고 있다.

MySQL - 모니터링8

• NOOP : 단순한 FIFO(First In/First Out) 이다. I/O스케줄러의 큐에 순서대로 I/O 요청이 저장되어 처리된다.
  
• Deadline: I/O 요청의 순서를 바꿈으로 I/O 퍼포먼스 향상을 목표로 한다. READ와 WRITE용으로 각각 큐를 가지고 있어 큐는 두개의 리스트로 관리된다. 큐 시간으로 소트된 것과 섹터 개시위치로 소트된 것이다. FIFO 리스트에 있는 최초의 요청이 READ로는 500msec, WRITE로는 5sec이상 기다리게 되면 그 큐를 우선적으로 처리한다.
  
• Anticipatory: READ 요청의 처리 마지막에 다음의 요청이 지금과 가까운 위치에서의 처리를 수행할 것인가를 확인한다. ( 지금 처리한 근처로의 액세스가 있는 것을 기대해서) 만약 가깝다면 곧 그 요청을 dispatch한다. 디스크 헤드가 한개밖에 없는 것을 전제로 하고 있다.
  
• CFQ(Completely Fair Queuing): 모든 I/O요청이 공평하게 처리될 것을 목적으로 하고 있다. 프로세스 단위, 프로세스 그룹 단위, 유저 단위로 처리가 공평하게 이루어지도록 한다. 요청의 발신지 모두에 대해서 각각 고유의 큐를 준비한다 이 큐를 라운드로빈으로 처리한다.
  

MySQL - 모니터링7

Linux커널의 I/O스케줄러 변경

커널2.6에서는 다음처럼 I/O스케줄러를 변경할 수 있다.

#I/O스케줄러의 변경

root@shell# echo "anticipatory" > /sys/block/sda/queue/scheduler

하드디스크단위로 지정한다.

하드디스크의 인식명에 따라서 위 예의 sda부분이 바뀌게 된다.

또 boot할 때 커널의 파라미터에 디폴트 I/O스케줄러를 부여하는 것이 가능하다.

elevator=deadline

/sys/block/디스크명/queue/iosched/ 아래의 파일로 I/O스케줄러의 파라미터를 변경할 수 있다.

I/O스케줄러에는 다음과 같은 것이 있다. 커널의 config시에 디폴트를 지정할 수 있다.

• Anticipatory
• Deadline
• CTQ
• NOOP

커널의 config의 변수, CONFIG_DEFAULT_IOSCHED이던지 make config의 메뉴의

「Block layer」→ 「IO Schedulers」→ 「Default I/O scheduler」에 값을 부여한다.

MySQL - 모니터링6

운영체제 툴

MySQL본체이외에도 운영체제의 상태를 알고 싶을 때가 있을 것이다.

여기에서는 Linux를 기준으로 몇가지 툴과 수법을 소개해보자.

iostat

iostat는 디스크 드라이브 단위로 I/O통계정보를 얻을 수 있는 명령어이다. 구문은 다음과 같다.

iostat [옵션] [간격 [횟수] ]

간격에 초수를 지정하면 지정 초수마다 횟수에 지정된 횟수만큼 iostat를 실행하고 끝낸다.

이 때 최초의 레포트는 지금까지의 누적정보를 내고, 두번째부터는 전 레포트 보고시점부터의 통계를 나타낸다. 횟수를 지정하지 않으면 영원히 레포트를 출력한다 .

vmstat

vmstat는 VM의 통계정보를 얻을 수 있는 명령어이다. 구문은 다음과 같다.

vmstat [옵션] [간격 [횟수] ]

간격에 초수를 지정하면 초수마다 횟수에 지정된 횟수만큼 vmstat를 실행하고 끝낸다.

횟수를 지정하지 않으면 영원히 레포트를 출력한다

Linux프로파일러( oprofile)

어플리케이션 어느 부분이 어느 정도의 처리를 하고 있는지등의 정보를 얻을 수 있다.

oprofile에서 프로파일링을 하기 위해서는 커널이 oprofile을 지원하고 있을 필요가 있다.

커널의 config변수는 다음과 같다.

CONFIG_PROFILING 　「y」

CONFIG_OPROFILE 「y」또는 「m」

커널의 config의 menu에서는 다음 항목을 유효로 해둔다.

Instrumentation Support -> Profiling support를 「y」

OProfile system profiling을 「y」또는 「m」

실제 조작은 다음과 같다.

oprofile실행예

opcontrol --shutdown <-- oprofile데몬 정지

opcontrol --reset <-- oprofile의 과거 데이터를 삭제

opcontrol --start <-- oprofile 데몬 기동

조사하고 싶은 명령어를 실행

opcontrol --shutdown <-- oprofile데몬 정지

opcontrol --save=test01 <-- 방금 취득한 데이터를 test01라는 이름으로 백업

opcontrol -1 session:test01 <-- test01의 레포트를 표시

oprofile의 데이터는 /var/lib/oprofile/에 기록된다.