[MySQL] 성능 테스트 전 체크리스트

이 글을 시작으로 간단한 쿼리 개선 중 알게 된 지식들과 그 과정을 적어보고자 한다.

먼저 쿼리 개선을 위해서는 현재 내가 작성한 쿼리의 성능을 테스트 해봐야 한다.

MySQL서버에서 직접 테스트를 진행했을 때 첫 쿼리 실행 시간과 다음 쿼리 실행시간의 차이가 매우 크고 그 이후의 쿼리 실행시간들도 제각각이라는 것을 확인했다. 이에 성능 테스트의 일관성을 유지하기 위해 실제 쿼리가 실행될 때 거치는 과정들을 학습하면서 변수들을 최소화하고자 했다.

 

---

 

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

 

1️⃣ 운영체제의 캐시

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

 

InnoDB 스토리지 엔진은 자체적인 버퍼 풀을 가지고 있기 때문에 일반적으로 파일 시스템의 캐시나 버퍼를 거치지 않는 Direct I/O를 사용하고 운영체제의 캐시가 큰 영향을 미치지 않는다. 하지만 MyISAM 스토리지 엔진은 운영체제의 캐시에 대한 의존도가 높기 때문에 운영체제의 캐시에 따라 성능 차이가 큰 편이다.

 

운영체제에 포함된 캐시나 버퍼는 프로그램 단위로 관리되기 때문에 MySQL 서버가 종료되면 해당 프로그램을 위한 캐시는 자동적으로 해제된다.

운영체제가 가지고 있는 캐시나 버퍼가 전혀 없는 상태에서 쿼리의 성능을 테스트하려면 MySQL 서버를 재시작하거나, 다음과 같이 캐시 삭제 명령을 실행하고 테스트하는 것이 좋다.

## 캐시나 버퍼의 내용을 디스크와 동기화한다.
shell > sync

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

 

 

2️⃣ MySQL 서버의 버퍼 풀(InnoDB 버퍼 풀과 MyISAM의 키 캐시)

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

InnoDB 스토리지 엔진이 관리하는 캐시를 버퍼 풀이라고 하며, MyISAM 스토리지 엔진이 관리하는 캐시는 키 캐시라고 한다. InnoDB의 버퍼 풀은 인덱스 페이지는 물론이고 데이터 페이지까지 캐시하며, 쓰기 작업을 위한 버퍼링 작업까지 겸해서 처리한다. 이와 달리 MyISAM의 키 캐시는 인덱스 데이터에 대해서만 캐시 기능을 제공한다. 또한 MyISAM의 키 캐시는 주로 읽기를 위한 캐시 역할을 수행하며, 제한적으로 인덱스 변경만을 위한 버퍼 역할을 수행한다. 결국 MyISAM 스토리지 엔진에서는 인덱스를 제외한 테이블 데이터는 모두 운영체제의 캐시에 의존할 수 밖에 없다.

 

MySQL이 한번 가동된 상태에서는 InnoDB의 버퍼 풀과 MyISAM의 키 캐시 크기를 변경하거나 내용을 강제로 삭제하는 명령이 없다. MySQL 서버에 포함된 키 캐시나 버퍼 풀을 초기화하기 위해서는 MySQL 서버를 재시작해야 한다.

 

쿼리 캐시를 사용하지 못하도록 힌트를 사용한 쿼리라 하더라도 처음 실행했을 때와 두 번째 실행했을 때 상당한 성능 차이가 발생할 수도 있다. 이는 버퍼 풀이나 키 캐시에 의한 성능 차이다.  실서비스의 성능을 측정할때는 쿼리는 버퍼 풀이나 키 캐시가 준비된 상태에서 실행되기 때문에 처음 실행했을 때의 결과는 버리고 여러 번 테스트해서 그 결과를 기준으로 판단하는 것이 좋다. 하지만 이는 엄밀히 말하자면 캐시 조회 속도, 즉 메모리 속도를 측정하는 것이기 때문에 쿼리 성능 측정을 위해서는 디스크 I/O로만 처리되는 최초 실행 쿼리 속도를 측정해야한다. 또는 시나리오 테스트를 통해 버퍼 풀에 새로운 데이터가 계속 적재 하여 캐시 히트를 방지해야 성능 측정이 가능하다. 

 

 

3️⃣ MySQL 쿼리 캐시(Deprecated)

 

쿼리 캐시는 이전에 실행했던 SQL 문장과 그 결과를 임시로 저장해두는 메모리 공간을 의미한다. 만약 어떤 쿼리의 결과가 이미 쿼리 캐시에 있었다면 그 쿼리는 전체 실행 과정을 건너뛰기 때문에 실제 부하와 관계없이 아주 빠르게 처리될 것이다. 쿼리 캐시에서 결과를 가져온 경우에는 대부분 0.00초 내에 쿼리 결과가 반환된다. 쿼리 캐시에 저장된 데이터를 비우려면 `RESET QUERY CACHE` 명령을 이용하면 되는데, 쿼리를 테스트할 때마다 쿼리 캐시를 비우기란 번거로운 일일 것이다. 그래서 SELECT 쿼리에 SQL_NO_CACHE 힌트를 추가해서 쿼리의 성능을 테스트하는 것이 좋다.

하지만 현재 아래와 같은 이유로 쿼리 캐시는 Deprecate되었다.

 

 

 

MySQL 엔진인 InnoDB와 쿼리 캐시 무효화 사이의 오버헤드가 너무 크다.결정적으로 테이블에 단 한번의 DML에도 참조된 모든 영역에 무효화가 일어나는데, 이 때 모든 캐시에서 참조를 찾는다.

여러 ORM에서 준수한 성능의 쿼리를 제공한다. 물론 각각 차이는 있겠지만, 사용되고 있는 ORM들에서 쿼리에 대한 캐싱을 제공하고 캐싱에 대한 관리 또한 DB보단 부담이 덜하다는 의견입니다. 또한 별도의 캐싱을 위한 DB들(Redis 등) 또한 제공되고 있기 때문에 쿼리 캐시를 개선하기보다 MySQL 자체의 성능 향상에 집중하겠다는 MySQL 서버 팀의 글이 있었습니다.

 

---

 

☑️ 정리

 

실제 쿼리의 성능 테스트를 MySQL 서버의 상태가 워밍업된 상태(위에서 언급한 캐시나 버퍼가 필요한 데이터로 준비된 상태)에서 진행할지 아니면 콜드 상태(캐시나 버퍼가 모두 초기화된 상태)에서 진행할지 고려해야 한다.

일반적으로 쿼리의 성능 테스트는 콜드 상태가 아닌 워밍업된 상태를 가정하고 테스트하는 편이다. 어느 정도 사용량이 있는 서비스라면 콜드 상태에서 워밍업 상태로 전환되는 데 그다지 많은 시간이 걸리지 않기 때문에 실제 서비스 환경의 쿼리는 대부분 콜드 상태보다는 워밍업 상태에서 실행된다고 볼 수 있다.

 

 

 

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