본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.
4.3 MyISAM 스토리지 엔진 아키텍처
4.3.1 키 캐시
InnoDB의 버퍼 풀과 비슷한 역할을 하는 것이 MyISAM의 키 캐시로, 인덱스만을 대상으로 작동하며 인덱스의 디스크 쓰기 작업에 대해서만 버퍼링 역할을 한다는 점에서 차이가 있다. 키 캐시의가 얼마나 효율적으로 작동하는지는 아래 수식을 통해 간단히 확인할 수 있다.
키 캐시 히트율(Hit rate) = 100 - (Key_reads / Key_read_requests * 100)
Key_reads는 인덱스를 디스크에서 읽어 들인 횟수를 저장하는 상태 변수이며, Key_read_requests는 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수이다. 이 상태 값들은 SHOW GLOBAL STATUS 명령으로 확인할 수 있다.
메뉴얼에서는 일반적으로 키 캐시를 이용한 쿼리의 비율(Hit rate)을 99% 이상을 유지하도록 권장하며, 99% 미만이라면 키 캐시를 조금 더 크게 설정하는 것이 좋다. 하지만 32비트 운영체제에서는 하나의 키 캐시에 4GB 이상의 메모리 공간을 설정할 수 없다. 64비트 운영체제에서는 OS_PER_PROCESS_LIMIT 값에 설정된 크기만큼의 메모리를 할당할 수 있다. 제한 값 이상의 키 캐시를 할당하고 싶다면 기본(Default) 키 캐시 이외에 별도의 명명된 키 캐시 공간을 설정해야 한다. 기본(Default) 키 캐시 공간을 설정하는 파라미터는 key_buffer_size다.
그러나 기본 키 캐시 이외의 명명된 키 캐시 영역은 아무런 설정을 하지 않으면 메모리 할당만 해두고 사용하지 않게 되므로 어떤 인덱스를 캐시할지 MySQL(MyISAM 스토리지 엔진)에 알려줘야 한다.
4.3.2 운영체제의 캐시 및 버퍼
MyISAM 테이블의 인덱스는 키 캐시를 이용해 디스크를 검색하지 않고도 충분히 빠르게 검색할 수 있으나 테이블의 데이터에 대해서는 디스크로부터 I/O를 해결해 줄만한 캐시, 버퍼링 기능이 존재하지 않아 항상 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청될 수밖에 없다. 물론 운영체제에 디스크로부터 읽고 쓰는 파일에 대한 캐시, 버퍼링 메커니즘이 존재하므로 매번 디스크의 파일을 읽지는 않는다. 운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본 원칙이다.
4.3.3 데이터 파일과 프라이머리 키(인덱스) 구조
InnoDB 스토리지 엔진을 사용하는 테이블은 프라이머리 키에 의해서 클러스터링되어 저장되는 반면, MyISAM 테이블은 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼 활용되어 레코드가 프라이머리 키 값과 무관하게 INSERT되는 순서대로 데이터 파일에 저장된다. 또한 MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, 프라이머리 키와 세컨더리 인덱스 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다. ROWID는 가변 길이와 고정 길이 두 가지 방법으로 저장될 수 있다.
4.4 MySQL 로그 파일
4.4.1 에러 로그 파일
MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다. 에러 로그 파일의 위치는 MySQL 설정 파일(my.cnf)에서 lof_error라는 이름의 파라미터로 정의된 경로에 생성된다. MySQL 설정 파일에 별도로 정의되지 않은 경우 데이터 디렉토리에 .err라는 확장자가 붙은 파일로 생성된다. 가장 자주 보게될 메시지들은 아래와 같다.
- MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
- 마지막으로 종료할 때 비정상으로 종료된 경우 나타나는 InnoDB 트랜잭션 복구 메시지
- 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
- 비정상적으로 종료된 커넥션 메시지
- InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지
- MySQL의 종료 메시지
4.4.2 제네럴 쿼리 로그 파일 (제네럴 로그 파일, General log)
MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해볼 때가 있는데, 이때는 쿼리 로그를 활성화해서 쿼리 로그 파일을 기록하게 한 다음 그 파일을 검토하면 된다. 쿼리 로그 파일은 시간 단위로 실행됐던 쿼리의 내용이 모두 기록되며, 제네럴 쿼리 로그는 MySQL이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 로그 파일에 기록된다.
4.4.3 슬로우 쿼리 로그
MySQL 서버의 쿼리 튜닝은 서비스가 적용되기 전에 전체적으로 튜닝하는 경우와 서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝으로 나눌 수 있다. 전자의 경우 검토해야 할 대상 쿼리가 전부라서 모두 튜닝하면 되지만, 후자의 경우 어떤 쿼리가 문제인 쿼리인지 판단하기가 상당히 어려운데, 이때 슬로우 쿼리 로그가 상당히 많은 도움이 된다.
슬로우 쿼리 로그 파일에는 시스템 변수에 설정한 시간 이상의 시간이 소요된 커리가 모두 기록된다. 슬로우 쿼리 로그는 MySQL이 쿼리를 실행한 후, 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부를 판단하기 때문에 정상적으로 실행이 완료돼야 슬로우 쿼리 로그에 기록될 수 있다.
일반적으로 슬로우 쿼리 또는 제네럴 로그 파일의 내용이 상당히 많아 직접 쿼리를 하나씩 검토하기에는 어려움이 있는데, Percona에서 개발한 Percona Toolkit의 pt-query-digest 스크립트를 이용하면 쉽게 빈도 처리나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있다. 로그 파일 분석이 완료되면 아래ㄱ와 같이 3개의 그룹으로 나뉘어 결과가 저장된다.
- 슬로우 쿼리 통계
- 실행 빈도 및 누적 실행 시간순 랭킹
- 쿼리별 실행 횟수 및 누적 실행 시간
