MySQL Query Cache은 무조건 좋을까? (Feat. query cache lock)

MySQL에서는 한 SELECT 쿼리의 결과를 캐싱해주는 Query Cache 라는 최적화 기능을 제공해주고 있다.

(하지만, 5.7.20 버전부터 deprecate되었고, 8.0 버전에서부터는 제거되었다고 한다. - 참고 글)

그렇다면, Query Cache은 항상 사용하면 좋은 것일까? (모든 캐싱이 그러하듯, 당연히 아닐 듯 하다.) 실습과 함께 한번 확인해보자.

실습 준비

MySQL 5.7.20 Docker Container 환경에서 실습하였습니다.

참고로 query_cache_type 의 default 값은 OFF이다. 이 값은 MySQL을 구동한 이후에는 변경할 수 없으므로, 아래와 같이 설정파일을 변경한 후, 한번 재기동해주어야 한다.

[mysqld]
query_cache_type = 2 # or DEMAND

MySQL 서버에 접속해서 Query Cache가 잘 설정되어 있는지 확인해보자.

mysql> SHOW VARIABLES LIKE 'have_query_cache';  
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size';  
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 1048576 |
+------------------+---------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_type';  
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| query_cache_type | DEMAND |
+------------------+--------+
1 row in set (0.00 sec)

Query Cache의 효과를 더 눈에 띄게 확인하기 위해서, Employees Sample Database 를 이용해 다량의 데이터들을 생성하였다.

mysql> use employees;  
mysql> show tables;  
+----------------------+
| Tables_in_employees  |
+----------------------+
| current_dept_emp     |
| departments          |
| dept_emp             |
| dept_emp_latest_date |
| dept_manager         |
| employees            |
| salaries             |
| titles               |
+----------------------+

장점

MySQL Query Cache 설정이 켜져있고 Query Cache를 사용하는 쿼리라면, MySQL는 요청으로 들어온 쿼리에 대해서 먼저 Query Cache를 조회해본다. 해당 쿼리가 Query Cache에 존재하는 경우라면, 기존처럼 아래 3단계를 거치지않고,

Parsing
Optimizing
Executing

바로 캐시에 담겨있는 데이터를 반환하게 된다.

따라서 쿼리의 비용이 크고, 반복적으로 호출되는 쿼리일수록 리소스 면에서 큰 이점을 얻을 수 있다.

이 때 캐시의 키가 되는 SELECT 쿼리는 byte 값까지 동일해야 동일한 키로 간주된다. 즉 대소문자까지 완벽히 동일해야한다.

-- Query Cache 미사용
mysql> SELECT count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;  
+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.45 sec)

mysql> SELECT count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;  
+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.43 sec)


-- Query Cache 사용
mysql> SELECT SQL_CACHE count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;  
+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set, 1 warning (0.42 sec)

mysql> SELECT SQL_CACHE count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;  
+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set, 1 warning (0.01 sec)

-- SELECT의 's' 소문자
mysql> sELECT SQL_CACHE count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;  
+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set, 1 warning (0.43 sec)

단점

Query Cache를 사용할 때의 가장 주의할 점은, 만약 대상 테이블에 대한 변경(ex. INSERT, UPDATE, DELETE)이 있었다면 묻고 따지지도 않고 기존의 캐시를 제거한다는 점이다. (처음에는 테이블 단위인 점이 의아했지만, 조금더 생각해보니 이렇게 말고는 딱히 좋은 방법은 없었을 듯하다.)

mysql> SELECT SQL_CACHE count(*) from salaries WHERE salary BETWEEN 60000 AND 70000;  
+----------+
| count(*) |
+----------+
|   588270 |
+----------+
1 row in set, 1 warning (00 sec)

-- 업데이트
mysql> UPDATE salaries SET salary = salary + 1 WHERE salary BETWEEN 70000 AND 75000;  
Query OK, 230785 rows affected (2.07 sec)  
Rows matched: 230785  Changed: 230785  Warnings: 0

mysql> SELECT SQL_CACHE count(*) from salaries WHERE salary BETWEEN 60000 AND 70000;  
+----------+
| count(*) |
+----------+
|   588270 |
+----------+
1 row in set, 1 warning (0.49 sec)

Query Cache는 여러 세션(쓰레드)들이 공유하는 자원이기 때문에, 동기화를 위한 lock이 필요하다. (이를 "query cache lock" 라고 부른다.) 즉, 테이블의 변경으로 인해 캐시를 제거하는 시점에, 다른 쓰레드에서는 이제 더이상 유효하지 않은 데이터를 가져가지 못하도록 lock을 걸게 된다. 이 lock이 풀릴때까지 Query Cache에 접근하는 쓰레드들은 "Waiting for query cache lock" 상태에서 대기하게 된다.

따라서 테이블의 변경이 잦을수록, Query Cache를 사용하는 SELECT 쿼리가 많을수록 이 lock을 waiting하는 시간은 많은 비중을 차지하게 된다.

query cache lock이 미치는 영향을 살펴보기 위해, MySQL에서 제공하는 mysqlslap 툴을 이용하여 다량의 쿼리를 동시에 요청해보았다.

`SELECT` 쿼리만 있는 경우

먼저 SELECT 쿼리만 다량으로 호출되는 상황을 가정하였다. (50개 클라이언트에서 동시에 실행, 10번씩 테스트)

# Query Cache 미사용
$ mysqlslap -u jupiny -p --concurrency=50 --iterations=10 --delimiter=";" --create-schema="employees" --query="SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000;" --verbose
Enter password:  
Benchmark  
    Average number of seconds to run all queries: 6.887 seconds
    Minimum number of seconds to run all queries: 6.364 seconds
    Maximum number of seconds to run all queries: 7.263 seconds
    Number of clients running queries: 50
    Average number of queries per client: 1

# Query Cache 사용 (처음에 Query Cache를 한번 flush한 후 실행)
$ mysqlslap -u jupiny -p --concurrency=50 --iterations=10 --delimiter=";" --create-schema="employees" --query="SELECT SQL_CACHE count(*) from salaries WHERE salary BETWEEN 60000 AND 70000;" --verbose
Benchmark  
    Average number of seconds to run all queries: 0.722 seconds
    Minimum number of seconds to run all queries: 0.012 seconds
    Maximum number of seconds to run all queries: 7.089 seconds
    Number of clients running queries: 50
    Average number of queries per client: 1

평균 실행시간을 보면 6.887s -> 0.722s, 약 90% 줄어들었엄을 볼 수 있다. (사실 처음에 캐싱이 안된 요청을 제외하면, 실행시간은 더욱 크게 줄어들었다.)

`SELECT` 쿼리 중간에 `UPDATE`가 있는 경우

극단적이긴하지만, SELECT, UPDATE가 1번씩 번갈아가며 호출되는 상황을 가정하였다. (50개 클라이언트에서 동시에 실행, 10번씩 테스트)

# Query Cache 미사용
$ mysqlslap -u jupiny -p --concurrency=50 --iterations=10 --delimiter=";" --create-schema="employees" --query="SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000; UPDATE salaries SET salary = salary + 1 WHERE emp_no=10001 AND from_date='1986-06-26';" --verbose
Enter password:  
Benchmark  
    Average number of seconds to run all queries: 6.579 seconds
    Minimum number of seconds to run all queries: 5.573 seconds
    Maximum number of seconds to run all queries: 7.441 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2

# Query Cache 사용 (처음에 Query Cache를 한번 flush한 후 실행)
$ mysqlslap -u jupiny -p --concurrency=50 --iterations=10 --delimiter=";" --create-schema="employees" --query="SELECT SQL_CACHE count(*) from salaries WHERE salary BETWEEN 60000 AND 70000; UPDATE salaries SET salary = salary + 1 WHERE emp_no=10001 AND from_date='1986-06-26';" --verbose
Enter password:  
Benchmark  
    Average number of seconds to run all queries: 6.807 seconds
    Minimum number of seconds to run all queries: 6.151 seconds
    Maximum number of seconds to run all queries: 7.910 seconds
    Number of clients running queries: 50
    Average number of queries per client: 2

많은 SELECT 쿼리들이 query cache lock을 대기하는 상황으로 인해 실행시간이 크게 늘어나는 상황을 연출하고 싶었지만, 정확하게 연출하기는 힘들었다.(더 좋은 방법을 고민해봐야겠다.) 어쨌든, Query Cache를 사용했을 때 평균 실행시간이 6.579s -> 6.807s, 약 3.5% 더 증가하였다.

결론

The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries.

사실 공식문서에 나와있는 이 한마디가 언제 Query Cache를 사용해야하는지를 모두 설명하고 있다고 생각한다.

테이블의 읽기 대비 변경 횟수가 얼마나 많은가?
동일한 쿼리가 얼마나 자주 호출되는가?

서비스 환경에서 이 두 가지를 고려해서 Query Cache의 적용여부를 신중하게 결정해야할 것이다.

실습 준비

장점

단점

SELECT 쿼리만 있는 경우

SELECT 쿼리 중간에 UPDATE가 있는 경우

결론

참고

`SELECT` 쿼리만 있는 경우

`SELECT` 쿼리 중간에 `UPDATE`가 있는 경우