ProxySQL - Query Cache

머리말

역사적으로 MySQL 환경에서 캐싱을 사용하는 방법에는 두 가지가 있습니다.

• MySQL 쿼리 캐시 사용: MySQL 서버 자체에 내장되어 있으며 외부 종속성이 없습니다. 해당 테이블이 쓰기를 수신하면 캐시 항목이 무효화되기 때문에 쓰기 집약적인 워크로드에 병목 현상이 발생합니다.
• use external caching : 많은 유연성을 허용하지만 응용 프로그램이 데이터베이스와 캐싱 시스템 모두에 연결해야 하고 업데이트를 유지해야 하기 때문에 많은 응용 프로그램 변경 및 논리가 필요합니다.

외부 캐싱은 매우 효율적이지만 개발 노력이 필요하고 DBA는 데이터 흐름을 제어할 수 없습니다.

유선 캐싱

ProxySQL은 쿼리 캐싱에 새로운 패러다임을 도입했습니다. 구성(자세한 내용은 아래 참조)에 따라 결과 집합은 유선으로 캐시되며 쿼리가 실행되고 결과 집합이 응용 프로그램에 반환됩니다.
응용 프로그램이 동일한 쿼리를 다시 실행하면 포함된 쿼리 캐시에서 결과 집합을 반환합니다.

몇 초 동안 캐시해야 하는 결과 집합을 생성하는 최적이 아닌 SELECT 문으로 인해 발생하는 데이터베이스 로드를 식별하는 것은 매우 일반적인 시나리오입니다.
코드 변경을 구현하는 것은 긴 프로세스가 될 수 있으며(개발자는 새 코드를 작성하고, 빌드하고, 스테이징에서 테스트한 다음 프로덕션에 배포해야 함) 긴급 상황에서 적합한 옵션이 아닌 경우가 많습니다.
데이터베이스 프록시 계층(이 경우 ProxySQL)의 구성은 DBA의 책임이므로 DBA 캐싱을 활성화하기 위해 개발자가 애플리케이션을 변경할 필요가 없습니다.
따라서 이것은 DBA에게 권한을 부여하는 기능입니다.

캐시해야 하는 트래픽 정의

캐싱해야 하는 트래픽을 정의하려면 들어오는 트래픽과 일치 하는 쿼리 규칙 을 정의하고 이에 대한 cache_ttl을 정의해야 합니다.

설명서 에 설명된 대로 들어오는 트래픽에 대한 일치를 정의하는 방법에는 여러 가지가 있습니다.
결과 집합을 캐시하기 위해 해야 할 일은 일치 기준과 시간 제한을 정의하는 것뿐입니다.

캐싱 예시

캐싱을 구성하는 방법을 설명하는 가장 좋은 방법은 예제를 사용하는 것입니다.
매우 작은 테이블을 사용하여 ProxySQL에 대해 sysbench를 실행한다고 가정합니다.

$ sysbench --num-threads=16 --max-requests=0 --max-time=60 --test=oltp 
--mysql-user=msandbox --mysql-password=msandbox --mysql-db=sbtest --mysql-host=127.0.0.1 --mysql-port=6033 
--oltp-table-size=10000 --oltp-read-only=on --db-ps-mode=disable --oltp-skip-trx=on 
--oltp-point-selects=100 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1 
--oltp-distinct-ranges=1 run

결과는 다음과 같습니다.

    read/write requests:                 380952 (6341.71 per sec.)

ProxySQL에서 다음 결과를 볼 수 있습니다.

Admin> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC;
+------------+-----------+-----------+--------------------+-------------------------------------------------------------------+
| count_star | sum_time  | hostgroup | digest             | digest_text                                                       |
+------------+-----------+-----------+--------------------+-------------------------------------------------------------------+
| 366300     | 508306254 | 1         | 0xE8930CB2CC9E68D7 | SELECT c from sbtest where id=?                                   |
| 3663       | 6932505   | 1         | 0xB749413737FAF581 | SELECT DISTINCT c from sbtest where id between ? and ? order by c |
| 3663       | 6607248   | 1         | 0x78881FD58E5437B2 | SELECT c from sbtest where id between ? and ? order by c          |
| 3663       | 5534740   | 1         | 0x547C0EAF9BC36E91 | SELECT SUM(K) from sbtest where id between ? and ?                |
| 3663       | 5506153   | 1         | 0xDC1EE02F8CD8B09B | SELECT c from sbtest where id between ? and ?                     |
| 1          | 2372      | 1         | 0xD575B97BB01C8428 | SHOW TABLE STATUS LIKE ?                                          |
+------------+-----------+-----------+--------------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)

SELECT의심할 여지 없이 대부분의 실행 시간은 여러 번 실행 되는 단일 유형의 에서 발생합니다 .
이를 캐시하여 일치하는 규칙을 생성해 보겠습니다. 이 예에서는 digest일치 기준 cache_ttl으로 2000ms를 사용합니다.

Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply)
VALUES (5,1,'0xE8930CB2CC9E68D7',2000,1);
Query OK, 1 row affected (0.00 sec)

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

테스트 벤치마크를 다시 실행해 보겠습니다.

$ sysbench --num-threads=16 --max-requests=0 --max-time=60 --test=oltp 
--mysql-user=msandbox --mysql-password=msandbox --mysql-db=sbtest --mysql-host=127.0.0.1 --mysql-port=6033 
--oltp-table-size=10000 --oltp-read-only=on --db-ps-mode=disable --oltp-skip-trx=on 
--oltp-point-selects=100 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1 
--oltp-distinct-ranges=1 run

결과는 다음과 같습니다.

    read/write requests:                 1613248 (26873.58 per sec.)

일부 쿼리가 ProxySQL에 의해 캐시되었기 때문에 처리량이 크게 증가했음을 즉시 확인할 수 있습니다.

ProxySQL에서 다음 결과를 볼 수 있습니다 from stats_mysql_query_digest.

Admin> SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+------------+-----------+-----------+--------------------+-------------------------------------------------------------------+
| count_star | sum_time  | hostgroup | digest             | digest_text                                                       |
+------------+-----------+-----------+--------------------+-------------------------------------------------------------------+
| 114715     | 119933775 | 1         | 0xE8930CB2CC9E68D7 | SELECT c from sbtest where id=?                                   |
| 6783       | 8244945   | 1         | 0xB749413737FAF581 | SELECT DISTINCT c from sbtest where id between ? and ? order by c |
| 6800       | 8081234   | 1         | 0x78881FD58E5437B2 | SELECT c from sbtest where id between ? and ? order by c          |
| 6877       | 7923794   | 1         | 0xDC1EE02F8CD8B09B | SELECT c from sbtest where id between ? and ?                     |
| 6840       | 7535059   | 1         | 0x547C0EAF9BC36E91 | SELECT SUM(K) from sbtest where id between ? and ?                |
| 1          | 2199      | 1         | 0xD575B97BB01C8428 | SHOW TABLE STATUS LIKE ?                                          |
| 8729       | 0         | -1        | 0xB749413737FAF581 | SELECT DISTINCT c from sbtest where id between ? and ? order by c |
| 8672       | 0         | -1        | 0x547C0EAF9BC36E91 | SELECT SUM(K) from sbtest where id between ? and ?                |
| 8712       | 0         | -1        | 0x78881FD58E5437B2 | SELECT c from sbtest where id between ? and ? order by c          |
| 8635       | 0         | -1        | 0xDC1EE02F8CD8B09B | SELECT c from sbtest where id between ? and ?                     |
| 1436485    | 0         | -1        | 0xE8930CB2CC9E68D7 | SELECT c from sbtest where id=?                                   |
+------------+-----------+-----------+--------------------+-------------------------------------------------------------------+
11 rows in set (0.00 sec)

참고: 쿼리는 hostgroup=-1백엔드에 도달하지 않고 쿼리 캐시에서 직접 제공되는 트래픽을 나타냅니다.

측정항목

현재 사용 가능한 측정항목 중 일부는 아래 예와 같이 에서 stats_mysql_query_digest보고 되는 측정항목입니다.hostgroup=-1

쿼리 캐시와 관련된 다른 메트릭은 stats 테이블을 통해 사용할 수 있습니다 stats_mysql_global.

Admin> SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';
+--------------------------+----------------+
| Variable_Name            | Variable_Value |
+--------------------------+----------------+
| Query_Cache_Memory_bytes | 54133472       |
| Query_Cache_count_GET    | 1892409        |
| Query_Cache_count_GET_OK | 1699405        |
| Query_Cache_count_SET    | 193004         |
| Query_Cache_bytes_IN     | 24323669       |
| Query_Cache_bytes_OUT    | 135396517      |
| Query_Cache_Purged       | 185323         |
| Query_Cache_Entries      | 7681           |
+--------------------------+----------------+
8 rows in set (0.00 sec)

그들은 다음을 나타냅니다.

• Query_Cache_Memory_bytes: 쿼리 캐시에 저장된 결과 집합의 총 크기입니다. 여기에는 메타데이터가 포함되지 않습니다.
• Query_Cache_count_GET: 쿼리 캐시에 대해 실행된 총 GET 요청 수입니다.
• Query_Cache_count_GET_OK: 쿼리 캐시에 대해 실행된 성공적인 GET 요청 의 총 수 : 결과 집합이 있고 만료되지 않았습니다.
• Query_Cache_count_SET: 쿼리 캐시에 삽입된 결과 집합의 총 수입니다.
• Query_Cache_bytes_IN: 쿼리 캐시에 기록된 데이터의 양
• Query_Cache_bytes_OUT: Query Cache에서 읽은 데이터의 양;
• Query_Cache_Purged: 삭제된 항목 수
• Query_Cache_Entries: 현재 쿼리 캐시에 있는 항목 수입니다.

쿼리 캐시 튜닝

현재로서는 다음 mysql-query_cache_size_MB 변수를 사용하여 쿼리 캐시에서 사용하는 총 메모리 양만 조정할 수 있습니다.

mysql> SHOW VARIABLES LIKE 'mysql-query_cache%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| mysql-query_cache_size_MB | 256   |
+---------------------------+-------+

중요 사항: mysql-query_cache_size_MB 의 현재 구현은  엄격한 제한을 부과하지 않습니다 . 대신 제거 스레드에서 인수로 사용됩니다.

쿼리 캐시에서 사용하는 총 메모리 양을 변경하려면 다음과 같은 명령을 사용할 수 있습니다.

mysql> SET mysql-query_cache_size_MB=128; LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

PROXYSQL FLUSH QUERY CACHE쿼리 캐시를 지우 려면 ProxySQL Admin 인터페이스를 통해 명령 을 실행하여 현재 캐시를 지울 수 있습니다. 이렇게 하면 캐시가 지워지고 관련 메모리가 해제됩니다.

쿼리 캐시와 엄격하게 관련되지는 않지만 동작에 영향을 미치는 변수는 mysql-threshold_resultset_size 입니다.
ProxySQL이 클라이언트로 전송을 시작하기 전에 버퍼링할 최대 결과 집합 크기를 mysql-threshold_resultset_size 에서 정의합니다.
이 변수를 너무 낮게 설정하면 백엔드에서 결과 집합을 검색하는 동안 실패한 쿼리 재시도를 방지할 수 있습니다.
이 변수를 너무 높게 설정하면 ProxySQL이 더 많은 데이터를 버퍼링하려고 시도하므로 잠재적으로 메모리 사용량이 증가합니다.
버퍼링할 수 있는 최대 결과 집합 크기를 mysql-threshold_resultset_size 에서 정의하므로 쿼리 캐시에 저장할 수 있는 최대 결과 집합 크기도 정의합니다.

구현 세부정보

쿼리 캐시의 모든 요소에는 관련된 여러 메타데이터가 있습니다.

• key: 쿼리 캐시 항목을 고유하게 식별합니다. 사용자 이름, 스키마 이름 및 쿼리 자체에서 파생된 해시입니다. 이를 결합하면 사용자가 자신의 결과 집합에만 액세스하고 올바른 스키마에 대해 액세스할 수 있습니다.
• value: 결과 집합;
• length: 결과 집합의 길이;
• expire_ms: 항목이 만료되는 시기를 정의합니다.
• access_ms: 항목에 마지막으로 액세스한 시간을 기록합니다.
• ref_count: 현재 사용 중인 결과 집합을 식별하기 위한 참조 횟수입니다.

전화 받기

호출이 성공할 때마다 GET성능 향상을 위해 참조 포인터를 늘리고 잠금을 해제한 후 데이터 복사본이 만들어집니다. 복사가 완료되면 ref_count가 감소합니다. 이렇게 하면 항목이 아직 사용 중인 동안 쿼리 캐시에서 항목이 삭제되지 않습니다.
통화 에서 GET만료된 항목을 찾으면 해당 항목이 제거 대기열로 이동됩니다.

SET 호출

전화 는 SET절대 실패하지 않습니다. mysql-query_cache_size_MB에 도달하면 호출 SET이 실패하지 않습니다.
동일한 키를 가진 항목이 있으면 삭제 대기열로 이동됩니다.

삭제 스레드 (Purging thread)

쿼리 캐시의 항목 제거는 제거 스레드에 의해 수행됩니다.
이렇게 하면 쿼리 캐시에 액세스하는 MySQL 스레드가 아닌 백그라운드 스레드가 쿼리 캐시 유지 관리를 수행하므로 성능이 향상됩니다. 

이것이 mysql-query_cache_size_MB에 도달 하더라도 SET 호출이 절대 실패하지 않는 이유 입니다.일부 공간을 확보하기 위해 쿼리 캐시에 액세스하는 것은 MySQL 스레드의 책임이 아닙니다. 퍼지 스레드가 대신 처리합니다.

 

제거 스레드는 제거 대기열에서 항목을 제거하는 역할만 하는 것은 아닙니다. 또한 만료된 항목을 찾기 위해 전체 쿼리 캐시를 주기적으로 스캔합니다.
최적화로서 thd 퍼지 스레드는 현재 메모리 사용량이 mysql-query_cache_size_MB 의 3% 미만인 경우 퍼지를 수행하지 않습니다.

제한 사항

현재 쿼리 캐시에는 여러 가지 알려진 제한 사항이 있습니다.
일부는 구현하기 간단하고 다른 것들은 그렇지 않습니다.
정의된 우선 순위 목록이 없습니다. 우선 순위는 사용자 요청에 따라 정의됩니다.

현재 알려진 제한 사항:

• cache_ttl 이외의 쿼리 캐시 무효화를 정의할 수 없습니다.
• mysql-query_cache_size_MB 는 엄격하게 적용되지는 않지만 만료된 항목의 자동 제거를 트리거하는 메트릭으로만 사용됩니다.
• access_ms 기록 되지만 mysql-query_cache_size_MB 달성 시 미사용 메트릭을 만료하기 위한 메트릭으로 사용되지 않습니다. 
• 쿼리 캐시는 준비된 문을 지원하지 않습니다.