query_optimization_08.md

쿼리 작성 및 최적화

쿼리 성능 테스트

쿼리 성능에 영향을 미치는 요소

운영체제의 캐시

MySQL 서버는 OS의 파일 시스템 콜을 이용해 데이터 파일을 읽어온다. 그런데 일반적으로 대부분의 OS는 한 번 읽은 데이터는 OS가 관리하는 별도의 캐시 영역에 보관해뒀다가 다시 해당 데이터가 요청되면 디스크를 읽지 않고 캐시의 내용을 바로 MySQL 서버로 반환한다.

InnoDB 스토리지 엔진은 일반적으로 파일 시스템의 캐시나 버퍼를 거치지 않는 Direct I/O를 사용하므로 OS의 캐시가 그다지 큰 영향을 미치지 않지만 MyISAM의 경우 OS 캐시에 대한 의존도가 높아서 OS 캐시에 따라 성능 차이가 큰 편이다.

MySQL 서버의 버퍼 풀(InnoDB 버퍼 풀, MyISAM 키 캐시)

OS 버퍼나 캐시와 마찬가지로 MySQL 서버에서도 데이터 파일 내용을 페이지 단위로 캐시하는 기능을 제공하는데, InnoDB 스토리지 엔진이 관리하는 캐시를 버퍼 풀이라고 하며 MyISAM 스토리지 엔진이 관리하는 캐시는 키 캐시라고 한다.

InnoDB 버퍼 풀은 인덱스 페이지는 물론 데이터 페이지까지 캐시하며, 쓰기 작업을 위한 버퍼링 작업까지 함께 처리한다. 그와 달리 MyISAM의 키 캐시는 주로 읽기를 위한 캐시 역할을 수행하며, 제한적으로 인덱스 변경만을 위한 버퍼 역할을 수행한다. 따라서 MyISAM 스토리지 엔진에서는 인덱스를 제외한 테이블 데이터는 모두 OS 캐시에 의존할 수 밖에 없다.

MySQL 서버가 한번 시작되면 InnoDB 버퍼 풀과 MyISAM 키 캐시의 내용을 강제로 퍼지할 수 있는 방법이 없다. 초기화하려면 MySQL 서버를 재시작해야 한다. 특히 InnoDB 버퍼 풀은 MySQL 서버가 종료될 때 자동으로 덤프됐다가 다시 시작할 때 자동으로 적재된다. 그래서 InnoDB 버퍼 풀이 자동으로 덤프되고 적재되지 않게 innodb_buffer_pool_load_at_startup 시스템 변수를 OFF로 설정할 수 있다. 서버가 종료될 때 버퍼 풀의 내용을 덤프하지 않고자 하면 innodb_buffer_pool_dump_at_shutdown 시스템 변수를 OFF로 설정하면 된다. 8.0 버전에서 기본 값은 모두 ON이다.

독립된 MySQL 서버

MySQL 서버가 기동 중인 장비에 웹 서버나 다른 배치 프로그램이 실행되고 있다면 쿼리의 성능이 영향받을 수 있다. 마찬가지로 MySQL 서버뿐 아니라 테스트 쿼리를 실행하는 클라이언트 프로그램이나 네트워크의 영향 요소도 고려해야 한다.

쿼리 테스트 횟수

실제 쿼리의 성능 테스트를 MySQL 서버가 워밍업된 상태(캐시나 버퍼가 준비)에서 진행할지 아니면 콜드 상태(캐시나 버퍼가 모두 초기화된 상태)에서 진행할지도 고려해야 한다. 보통 일반적인 쿼리 성능 테스트는 워밍업된 상태를 가정하고 테스트하는 편이다.

OS의 캐시나 MySQL의 버퍼 풀, 키 캐시는 그 크기가 제한적이라서 쿼리에서 필요로 하는 데이터나 인덱스 페이지보다 크기가 작으면 플러시 작업과 캐시 작업이 반복해서 발생하므로 쿼리를 한 번 실행해서 나온 결과를 그대로 신뢰해서는 안 된다. 테스트 하려는 쿼리를 번갈아 가면서 6~7번 정도 실행한 후, 처음 한두 번의 결과는 버리고 나머지 결과의 평균값을 기준으로 비교하는 것이 좋다. 처음에는 OS 캐시나 MySQL의 버퍼 풀과 키 캐시가 준비되지 않을 때가 많아서 대체로 많은 시간이 소요되는 편이라 편차가 클 수 있다.

이 같은 사항을 고려해 쿼리의 성능을 비교하는 것은 절대적인 성능이 아니다. 실제 서비스용 MySQL 서버에서는 현재 테스트 중인 쿼리만 실행되는 것이 아니라 동시에 4~50개의 쿼리가 실행중인 상태일 것이다. 각 쿼리가 자원을 점유하기 위한 경합 등이 발생하므로 항상 테스트보다는 느린 처리 성능을 보이는 것이 일반적이다.

Reference

위 글은 책 RealMySQL 8.0 2권을 구입하여 읽고 정리한 내용입니다.

• 도서 홈페이지 https://wikibook.co.kr/realmysql802/