MySQL 쿼리 캐시 사용하기

큰 서비스를 운영하는 서버가 아닌데도 CPU 점유율이 높아 확인을 했다.

원인은 MySQL 에서 결과를 가져오는데 1초가 걸리는 쿼리를 자주 액세스하면서 발생하는 부하였다. 서비스 초반에는 데이터의 양이 얼마 없기 때문에 성능저하를 느낄 수 없었지만, Row 의 양이 10만건을 넘어가고 Select 되는 Row 가 3만건정도 되면서 쿼리 실행시간이 1초이상 걸리는 결과를 가져오게 되었다. 이 쿼리를 평균 5초에 한번씩 실행을 하게 되면서 저렴한 VPS 에서는 서버의 부하를 체감할 정도가 되었다.

쿼리를 이리저리 옵티마이징을 해 봐도 쿼리 응답시간이 0.8~1초에서 더 낮아지지 않았기에 왜 MySQL 의 자체쿼리 캐시가 동작하지 않는지 궁금해졌다. 쿼리는 다음과 같다.

SELECT *
    FROM
        my_data_items
    WHERE
        aaa > 0 AND bbb < 1 AND ccc < 5 AND
        ( some_date_at <= UTC_TIMESTAMP() OR some_date_at IS NULL )

    ORDER BY
        aaa DESC, id ASC

이 쿼리에서 DB 의 캐시가 동작하지 않는 이유는 UTC_TIMESTAMP() 였다. 이 함수는 현재의 시간을 알려주므로 매초마다 다른 값을 전달하게 되고, 쿼리문을 캐싱을 하는데 이 함수를 사용하게 되면 매번 시간이 달라지게 되어 캐싱이 될 수 없다. 너무나도 당연한 결과지만 의외로 간과하기 쉽고 헤매기 쉬운 부분이다.

2020-11-11 11:11:11 … 12 … 13 … 과 같이 시간이 흘러가므로,

2020-11-11 11:11:00 이렇게 1분의 인터벌을 주기로 하고, 다음과 같은 쿼리를 작성.

UTC_TIMESTAMP() 에 1분의 인터벌

DATE_SUB(DATE_SUB(UTC_TIMESTAMP(), INTERVAL MOD(MINUTE(UTC_TIMESTAMP()),1) MINUTE), INTERVAL SECOND(UTC_TIMESTAMP()) SECOND)

SELECT *
    FROM
        my_data_items
    WHERE
        aaa > 0 AND bbb < 1 AND ccc < 5 AND
        ( some_date_at <= DATE_SUB(DATE_SUB(UTC_TIMESTAMP(), INTERVAL MOD(MINUTE(UTC_TIMESTAMP()),1) MINUTE), INTERVAL SECOND(UTC_TIMESTAMP()) SECOND) OR some_date_at IS NULL )

    ORDER BY
        aaa DESC, id ASC

이렇게 하고 실행을 했지만, 역시나 쿼리를 캐싱하지 않는다. 확인을 위해서 직접 시각을 넣어본다.

SELECT *
    FROM
        my_data_items
    WHERE
        aaa > 0 AND bbb < 1 AND ccc < 5 AND
        ( some_date_at <= '2020-11-11 11:11:00' OR some_date_at IS NULL )

    ORDER BY
        aaa DESC, id ASC

이렇게 하고나니 캐시가 제대로 동작한다. 값이 텍스트 스트링 값이 아니면 (즉 정적 텍스트 데이터) 가 아니면 캐싱을 하지 않는 듯 싶었다. 그래서 PHP 를 동원해 다음과 같이 만든다.

$date = date("Y-m-d H:i:00", time());

$query = "SELECT *
    FROM
        my_data_items
    WHERE
        aaa > 0 AND bbb < 1 AND ccc < 5 AND
        ( some_date_at <= '{$date}' OR some_date_at IS NULL )

    ORDER BY
        aaa DESC, id ASC
";

이렇게 해서 정적 텍스트 쿼리로 1분의 인터벌로 캐싱을 하도록 구현했다.

그런데 이를 실제 서버에 적용하는 순간, 또 캐시가 동작하지 않는다… 테스트 서버에서는 분명히 동작하는데 적용한 서버에서는 그렇지 않으므로 적용한 서버쪽의 MySQL 서버 설정 문제라 판단하고 MySQL 의 캐시 관련 값을 확인.

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
7 rows in set (0.01 sec)

query_cache_type | OFF 이런… 캐시가 Off 란다.

my.cnf 의 관련 부분을 수정하고 MySQL 서버를 재기동.

//
// Make using mysql query cache.
//
// Need below setting in my.cnf
query_cache_limit       = 1M
query_cache_size        = 16M
query_cache_type        = 1 # 0:off, 1:SELECT SQL_NO_CACHE 제외, 2:SELECT SQL_CACHE 만 캐싱

query_cache_type | ON 을 확인한다.

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
7 rows in set (0.01 sec)

이렇게 하고나니, 실제에서도 1분의 인터벌로 캐시가 잘 적용된다.

가능한 한 옵티마이징을 하고 더이상의 효과를 기대하기 어려울 경우에 필요에 따라 캐시를 사용하는것이 좋겠다. 처음부터 캐시를 사용하게 되면 잘못된 쿼리를 찾기 힘들어지게 되므로 캐시 사용은 주의가 필요하다. 비단 MySQL 뿐만 아니라…

MySQL 8.0 부터는 쿼리 캐시 기능이 없어졌으므로 주의가 필요하다.

MySQL 8.0: Retiring Support for the Query Cache