MySQL Query Cache 사용법

MySQL에서는 반복되는 쿼리를 효율적으로 처리하기 위한 캐쉬가 존재한다.

바로 query cache인데 이는 까다로운 조건에 의해 동작하고 pruning 과정에서 meta 정보의 lock이 발생할 수 있어서 조심해서 사용해야 한다.

일단 동작하는 조건은 아래와 같다.

query cache에는 SQL문과 result set이 저장된다. 바로 이 cache에 저장되어 있는 SQL 문과 완전 동일(띄어쓰기까지..)하고 result set이 같은 쿼리가 수행될 때 query cache가 동작하고 result set을 바로 반환한다.

보통 개발자들은 위와 같은 조건을 간과하고 무작정 같은 쿼리를 수행하면 query cache를 쓰는 것으로 알고 사용한다.

그렇게 아무렇게나 쓰면 waiting query cache lock이라는 상태를 가진 프로세스로 인해 MySQL 서버가 hang이 걸릴 것이다...(오랜시간 고쳐지지 않고 있는 버그..)

이러한 상태에 빠지는 것을 방지하기 위해서는 꼭 query cache를 사용해야 하는 쿼리에만 사용하도록 옵션을 주어 사용할 수 있다.

 

query cache는 query_cache_type이라는 옵션을 통해 세 가지 타입을 제공한다.

OFF (0) - Query cache를 사용하지 않는다. ON (1) - Query cache를 사용한다. (SQL_NO_CACHE 힌트를 사용하는 쿼리는 query cache를 사용하지 않는다.) DEMAND (2) - 선택한 쿼리만 query cache를 사용한다. (SQL_CACHE 힌트를 사용하는 쿼리는 query cache를 사용한다.)

 

위와 같은 설정은 my.cnf에서 설정 가능하고 세션 상에서도 설정이 가능하다.

# 세션에서 dynamic하게 설정하는 방법  mysql> set global query_cache_type = 1;   # my.cnf에 설정 (재시작 시 적용) [mysqld] query_cache_type = 1;

설정은 숫자로 설정을 하거나 alias를 통해 설정이 가능하다.

 

query cache를 OFF할 경우에도 query_cache_size만큼 메모리를 할당하므로 해당 설정값을 0으로 설정하는 것을 권고한다.

query cache를 사용할 경우에는 query_cache_size를 1024의 배수로 설정해야 한다. 다른 값으로 설정할 경우 반올림하여 적용된다.

구조상 최소한 40KB 이상으로 설정해야 하며 적은 값으로 설정할 경우 warning이 발생한다.

해당 값은 아래와 같이 설정한다.

# 세션에서 dynamic하게 설정하는 방법 mysql> set global query_cache_size = 16*1024*1024;   # my.cnf에 설정 (재시작 시 적용) [mysqld] query_cache_size = 16M;