MyISAM 스토리지 엔진 아키텍처

키 캐시

MyISAM의 키 캐시(Key cache, 키 버퍼)는 InnoDB의 버퍼 풀과 비슷한 역할을 한다.

MyISAM의 키 캐시는 인덱스만을 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다.

키 캐시가 얼마나 효율적으로 작동하는지는 다음 수식으로 간단히 확인할 수 있다.

키 캐시 히트율(Hit rate) = 100 - (key_reads / key_read_requests * 100)
-key reads: 인덱스를 디스크에서 읽어 들인 횟수를 저장하는 상태 변수
-key_read_requests: 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수다.
해당 상태값을 알아보기 위해서 SHOW GLOBAL STATUS LIKE 'key%' 명령을 사용하면 된다.

매뉴얼에서는 일반적으로 키 캐시를 이용한 쿼리의 비율(히트율)을 99% 이상으로 유지하라고 권장한다.

히트율이 99% 미만이라면 키 캐시를 조금 더 크게 설정하는 것이 좋다.

운영체제의 캐시 및 버퍼

MyISAM 테이블의 인덱스는 키 캐시를 이용해 디스크를 검색하지 않고도 충분히 빠르게 검색할 수 있다.

하지만 테이블의 데이터에 대해서는 디스크로부터의 I/O를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능도 MyISAM 스토리지 엔진은 가지고 있지 않다.

그래서 MyISAM 테이블의 데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청될 수밖에 없다.

대부분의 운영체제에는 디스크로부터 읽고 쓰는 파일에 대한 캐시나 버퍼링 메커니즘을 탑재하고 있기 때문에 MySQL 서버가 요청하는 디스크 읽기 작업을 위해 매번 디스크 파일을 읽지는 않는다.

운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본 원칙이다.

따라서 데이터베이스에서 MyISAM 테이블을 주로 사용한다면 운영체제가 사용할 수 있는 캐시 공간을 위해 충분한 메모리를 비워둬야 한다.

MyISAM이 주로 사용되는 MySQL에서 일반적으로 키 캐시는 최대 물리 메모리의 40% 이상을 넘지 않게 설정하고, 나머지 메모리 공간은 운영체제가 자체적인 파일 시스템을 위한 캐시 공간을 마련할 수 있게 해주는 것이 좋다.

데이터 파일과 프라이머리 키(인덱스) 구조

InnoDB 스토리지 엔진을 사용하는 테이블은 프라이머리 키에 의해서 클러스터링되어 저장되는 반면, MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼 활용된다.

즉 레코드는 프라이머리 키 값과 무관하게 INSERT되는 순서대로 데이터 파일에 저장된다.

그리고 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다.

MyISAM 테이블에서 ROWID는 가변 길이와 고정 길이의 두 가지 방법으로 저장될 수 있다.

• 고정 길이 ROWID
  테이블을 생성할 때 MAX_ROWS 옵션을 명시하면 MySQL 서버는 최대로 가질 수 있는 레코드가 한정된 테이블을 생성한다.
  ROWID 값으로 4바이트 정수를 사용한다.
  이때 레코드가 INSERT된 순번이 ROWID로 사용된다.

• 가변 길이 ROWID
  ROWID는 2바이트부터 7바이트까지 가변적인 ROWID를 갖게 된다.
  이 중 첫 번째 바이트는 ROWID의 길이를 저장하는 용도로 사용하고, 나머지 공간은 실제 ROWID를 저장하는 데 사용한다.
  MyISAM 테이블이 가변적인 ROWID를 가지면 데이터 파일에서 레코드의 위치가 ROWID로 사용된다.

MySQL 로그 파일

에러 로그 파일

MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다.

제너럴 쿼리 로그 파일

MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해 볼 때가 있는데, 이때는 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 한 다음, 그 파일을 검토하면 된다.

쿼리 로그 파일에는 시간 단위로 실행됐던 쿼리의 내용이 모두 기록된다.

제너럴 쿼리 로그는 실행되기 전에 MySQL이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 일단 로그 파일에 기록된다.

슬로우 쿼리 로그

서비스 운영중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝을 하는 경우, 어떤 쿼리가 문제의 쿼리인지 판단하기 어렵다.

이런 경우 서비스에서 사용되는 쿼리 중에서 어떤 쿼리가 문제인지를 찬단하는 데 슬로우 쿼리 로그가 상당히 많은 도움이 된다.

슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다.

슬로우 쿼리 로그은 MySQL이 쿼리를 실행한 후, 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부를 판단하기 때문에 반드시 쿼리가 정상적으로 실행이 완료돼야 슬로우 쿼리 로그에 기록될 수 있다.

일반적으로 슬로우 쿼리 또는 제너럴 로그 파일의 내용이 상당히 많아서 직접 쿼리를 하나씩 검토하기에는 시간이 너무 많이 걸리거나 어느 쿼리를 집중적으로 튜닝해야 할지 식별하기 어려울 수 있다.

이런 경우 Percona Toolkit의 pt-query-digest 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다.

로그 파일의 분석이 완료되면 결과가 3개의 그룹으로 나뉘어 저장된다.

• 슬로우 쿼리 통계
  분석 결과의 최상단에 표시되며, 모든 쿼리를 대상으로 슬로우 쿼리 로그의 실행 시간, 잠금 대기 시간 등에 대해 평균 및 최소/최대 값을 표시한다.

• 실행 빈도 및 누적 실행 시간순 랭킹
  각 쿼리별로 응답 시간과 실행 횟수를 보여준다.
  --order-by 옵션으로 정렬 순서를 변경할 수 있다.

• 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보
  Query ID 별 쿼리를 쿼리 랭킹에 표시된 순서대로 자세한 내용을 보여준다.
  랭킹별 쿼리에서는 대상 테이블에 대해 어떤 쿼리인지만을 표시하는데, 실제 상세한 쿼리 내용은 개별 쿼리의 정보를 확인해보면 된다.