11.8 쿼리 작성 및 최적화 - 쿼리 성능 테스트

작성된 쿼리가 얼마나 효율적이고 더 개선할 부분이 있는지 확인하려면 아래의 과정을 거친다.

쿼리를 직접 실행해보면서 눈으로 성을 체크할 때 여러 방해 요소가 있는데, 이러한 방해 요소를 간과하고 쿼리의 성능을 판단하는 것은 매우 위험하다.

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

직접 작성한 쿼리를 실행해 보고 성능을 판단할 때 가장 큰 변수는 서버가 가지고 있는 여러 종류의 버퍼나 캐시이다.

MySQL 서버는 운영체제의 파일 시스템 관련 기능(시스템 콜 등)을 이용해 데이터 파일을 읽어온다.

일반적으로 대부분 운영체제는 한 번 읽어은 데이터를 관리하는 별도 캐시 영역에 보관했다가 다시 해당 데이터가 요청되면 디스크를 읽지 않고 캐시의 내용을 바로 MySQL 서버로 전달한다.
InnoDB 스토리지 엔진은 일반적으로 파일 시스템의 캐시나 버퍼를 거치지 않는 Direct I/O를 사용하므로 운영체제의 캐시가 큰 영향을 미치지는 않는다.
MyISAM 스토리지 엔진은 운영체제의 캐시 의존도가 높기 때문에 캐시에 따라 성능 차이가 크다.

운영체제가 관리하는 캐시나 버퍼는 공용 공간으로 MySQL 서버와 같은 응용 프로그램이 종료된 후에도 남아있을 수 있다. 따라서 운영체제가 가지고 있는 캐시나 버퍼가 전혀 없는 상태에서 쿼리의 성능을 테스트하려면 캐시 삭제 명령을 실행하고 테스트 하는 것이 좋다.

1
2
3
4
5
6
## linux 기준!
## 캐스나 버퍼의 내용을 디스크와 동기화
sync

## 운영체제의 포함된 캐시 내용을 초기화한다.
echo 3 > /proc/sys/vm/drop_caches

운영 체제의 버퍼나 캐시와 마찬가지로 MySQL 서버에서도 InnoDB 버퍼풀, MyISAM 키 캐시 등으로 데이터 파일의 내용을 페이지(또는 블록) 단위로 캐시하는 기능을 제공한다.

InnoDB 버퍼풀은 인덱스 페이지와 데이터 페이지까지 캐시하며, 쓰기 작업을 위한 버퍼링 작업까지 처리한다.
MyISAM 키 캐시는 읽기를 위한 캐시 역할을 수행하며, 제한적으로 인덱스 변경만을 위한 버퍼 역할을 수행한다.
- 인덱스를 제외한 테이블 데이터는 모두 운영체제의 캐시에 의존한다.

MySQL 서버가 한번 시작되면 버퍼풀과 키 캐시를 강제로 퍼지할 수 있는 방법이 없으므로 초기화 하려면 MySQL 서버를 재시작 해야한다.

InnoDB 버펄풀은 서버가 종료될 때 자동으로 덤프됐다가 사작될 때 자동으로 적재하므로 InnoDB 버퍼풀이 자동으로 처리도지 않게 innodb_buffer_pool_load_at_startup 시스템 변수를 OFF로 설정한 후 재시작 해야 한다.

MySQL 서버가 기동 중인 장비에 웹 서버나 다른 배치용 프로그램이 실행되고 있다면 테스트하려는 쿼리의 성능이 영향을 받는다.

실제 쿼리의 성능 테스트를 MySQL 서버의 상태가 워밍업된 상태에서 진행할지 아니면 콜드 상태에서 진행할지도 고려햐야 한다.

운영체제의 캐시나 버퍼풀, 키 캐시는 크기가 제한적이므로 쿼리에서 필요로 하는 데이터나 인덱스 페이지보다 크기가 작으면 플러시 작업과 캐시 작업이 반복해서 발생하므로 쿼리를 한 번 실행해서 나온 결과를 그대로 신뢰해서는 안된다.

쿼리를 번갈아가면서 6~7회 실행한 후, 처음 한두 번의 결과는 버리고 나머지 결과의 평균값을 기준으로 비교하는 것이 좋다.
- 버퍼풀과 키캐시가 준비되지 않을 때가 많아 대체로 많은 시간이 걸려 편차가 클 수 있기 때문이다.

이 같은 사항을 고려해 쿼리의 성능을 비교하는 것은 결국 상대적인 비교이므로 쿼리가 어떤 서버에서도 그 시간 내에 처리된다고 보장할 수는 없다.

실제 서비스용 MySQL 서버에서는 동시에 여러개의 쿼리가 실행 중인 상태이다.
- 쿼리가 자원을 점유하기 위한 경함 등이 발생하므로 항상 테스트보다는 느린 처리 성능을 보이는 것이 일반적이다.