MySQL & Maria
Query Cache
- -
반응형
- Query Cache에 결과가 없을때
[Query Parsing] → [Optimization] → [Execution] 등 일련의 과정을 거칩니다.
- Query Cache에 존재
Query Cache가 활성화되었으면, Query Cache안에 이전에 실행했던 Query가 존재한다면 위 과정 없이 바로 Query Cache에서 결과를 보내줍니다.
- Query Cache 제약사항
* SELECT SQL_NO_CACHE ...
* SELECT ... INTO OUTFILE ...
* SELECT ... INTO DUMPFILE ...
* SELECT ... FOR UPDATE
* SELECT * FROM ... WHERE autoincrement_column IS NULL
* SELECT ... LOCK IN SHARE MODE
* TEMPORARY 테이블을 사용합니다.
* It uses no tables at all
* 경고를 생성합니다.
* 사용자는 쿼리의 모든 테이블에 대해 열 수준 권한이 있습니다.
* INFORMATION_SCHEMA, mysql 또는 performance_schema 데이터베이스의 테이블에 액세스합니다.
* 사용자 또는 지역 변수를 사용합니다.
* 저장된 기능을 사용합니다.
* 사용자 정의 함수를 사용합니다.
* SERIALIZABLE 격리 수준의 트랜잭션 내부에 있습니다.
* 동일한 테이블이 INSERT, UPDATE 또는 DELETE를 사용하여 쿼리 캐시 무효화를 실행 한 후 트랜잭션 내의 테이블을 쿼리합니다.
* Mysql 8.0 에서는 해당 기능 제거됨
쿼리 캐시는 SELECT 쿼리의 결과를 저장하므로 향후 동일한 쿼리가 수신 될 경우 결과를 빠르게 반환 할 수 있습니다.
이것은 읽기가 많고 쓰기가 적은 환경 (대부분의 웹 사이트 등)에서 매우 유용합니다. 멀티 코어 시스템에서 처리량이 많은 환경에서는 확장이 잘되지 않으므로 기본적으로 비활성화되어 있습니다.
1.사용할 수 있는지 확인
-캐시가 사용 가능한지 확인하려면 query_cache_type 서버 변수를 확인하십시오.
마리아DB 버전에서는 기본적으로 10.1.6까지 활성화되지만, 마리아DB 10.1.7부터 비활성화되며, 필요한 경우 query_cache_type을 1로 설정하여 활성화한다.
-query_cache_size = Qcache를 위한 전용 메모리
-query_cache_limit = 쿼리당 가질 수 잇는 최대 캐시 크기다.
*성능 문제를 피하려면 쿼리 캐시를 256M 이상으로 보유하지 않도록해야된다.
#Query Cache Default
MariaDB [employees_second]> 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 | 1048576 |
| query_cache_strip_comments | OFF |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
#Query Cache 활성화
set global query_cache_type=2;
MariaDB [(none)]> 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 | 1048576 |
| query_cache_strip_comments | OFF |
| query_cache_type | DEMAND |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
2.Query Cache 없이 Select
해당 Select 문의 시간이 0.89 / 0.63으로 [Query Parsing] → [Optimization] → [Execution] 등 일련의 과정을 거치고 있다.
#Query Cache 미사용
MariaDB [employees_second]> SELECT count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
| 588322 |
+----------+
1 row in set (0.89 sec)
MariaDB [employees_second]> SELECT count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
| 588322 |
+----------+
1 row in set (0.63 sec)3.Query Cache 사용
첫번째 실행 하였을때 Query Cache에 담기고 이후 두번째 실행했을때 Query를 불러온다.
MariaDB [employees_second]> SELECT SQL_CACHE count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
| 588322 |
+----------+
1 row in set (0.63 sec)
MariaDB [employees_second]> SELECT SQL_CACHE count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
| 588322 |
+----------+
1 row in set (0.00 sec)
4. 제약 사항 Test
1) Query 문장 다를때
MariaDB [employees_second]> SELECT SQL_CACHE count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
| 588322 |
+----------+
1 row in set (0.63 sec)
MariaDB [employees_second]> sELECT SQL_CACHE count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
| 588322 |
+----------+
1 row in set (0.59 sec)
--> 이렇게 하나라도 틀리면 Query Cache를 탈수없다.2) 해당 Table에 Update 발생 시
MariaDB [employees_second]> SELECT SQL_CACHE count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
| 588322 |
+----------+
1 row in set (0.00 sec)
MariaDB [employees_second]> UPDATE salaries SET salary = salary + 1 WHERE salary BETWEEN 70000 AND 75000;
MariaDB [employees_second]> SELECT SQL_CACHE count(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
| 588270 |
+----------+
1 row in set (0.64 sec)
5. 대량의 Select 실행 TEST
대량의 Select 실행 먼저 SELECT 쿼리만 다량으로 호출되는 상황을 가정하였다. (50개 클라이언트에서 동시에 실행, 10번씩 테스트)
1.Query Cache 미사용
mysqlslap -u root -p --concurrency=50 --iterations=10 --delimiter=";" --create-schema="employees_second" --query="SELECT count(*) from salaries WHERE salary BETWEEN 60000 AND 70000;" --verbose
Enter password:
Benchmark
Average number of seconds to run all queries: 16.468 seconds ## 평균 16초
Minimum number of seconds to run all queries: 15.806 seconds
Maximum number of seconds to run all queries: 18.880 seconds
Number of clients running queries: 50
Average number of queries per client: 1
2.Query Cache 사용 (처음에 Query Cache를 한번 flush한 후 실행)
mysqlslap -u root -p --concurrency=50 --iterations=10 --delimiter=";" --create-schema="employees_second" --query="SELECT SQL_CACHE count(*) from salaries WHERE salary BETWEEN 60000 AND 70000;" --verbose
Benchmark
Average number of seconds to run all queries: 1.598 seconds ## 1초만에 끝난걸 볼 수 있다.
Minimum number of seconds to run all queries: 0.016 seconds
Maximum number of seconds to run all queries: 15.818 seconds
Number of clients running queries: 50
Average number of queries per client: 1
3.Select 중간에 Update가 껴있을 경우
mysqlslap -u root -p --concurrency=50 --iterations=10 --delimiter=";" --create-schema="employees_second" --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
Benchmark
Average number of seconds to run all queries: 16.515 seconds ## 일반 Query Cache 안할때보다 더걸림
Minimum number of seconds to run all queries: 16.236 seconds
Maximum number of seconds to run all queries: 16.706 seconds
Number of clients running queries: 50
Average number of queries per client: 2
6. 쿼리 캐시가 잘 사용되고 있는지 모니터링
MariaDB [(none)]> show global status like 'Qc%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 11696 |
| Qcache_free_memory | 35546000 |
| Qcache_hits | 1452464 |
| Qcache_inserts | 184542558 |
| Qcache_lowmem_prunes | 117092 |
| Qcache_not_cached | 537324710 |
| Qcache_queries_in_cache | 14765 |
| Qcache_total_blocks | 41366 |
+-------------------------+-----------+
-Qcache_free_memory = 쿼리를 저장하기 위해 사용 가능한 메모리
-Qcache_hits = 캐시를 히트한 수
-Qcache_inserts = 캐시로 들어간 쿼리의 수. 확실한 성능향상을 위해서는 Qcache_hits가 Qcache_insert 보다 높아야한다.
-Qcache_lowmem_prunes = 캐시에 충분한 공간이 없어, 가장 오래된 요청을 새로운 요청으로 교체한
-Qcache_not_cached = 캐시되지 않은 쿼리를 나타낸다.
* 쿼리 캐시를 사용해야 하는지 아닌지 알시위해 염두해야될 사항
1.Qcache_hits값이 Qcache_inserts 보다 작으면 대부분 상황에서 쿼리 캐시를 비활성화 해야된다. 하지만 쿼리 캐시의 100퍼센트를 히트하는 Query가 있다면 비활성화해서는 안된다.
2.Qcache_not_cached 값이 Qcache_hits 값과 Qcache_inserts 값의 합보다 작으면 쿼리 캐시 크기를 늘려보라.
3.Qcache_lowmem_prunes 값이 증가하면 Qcache의 메모리를 추가하라
반응형
'MySQL & Maria' 카테고리의 다른 글
| Slow Query 분석 Website (0) | 2021.08.24 |
|---|---|
| [MySQL] RENAME DATABASE (0) | 2021.08.10 |
| MySQLTunner.pl - MySQL 성능 진단 툴 (0) | 2021.08.05 |
| Maria DB Binary Install (0) | 2021.08.04 |
| MySQL 과도한 CPU 사용 Thread 확인 ( Pidstat ) (6) | 2021.08.03 |
Contents
소중한 공감 감사합니다