Understanding the Query Cache
흰콧수염
webmaster@10mbps.com
Last translated on Sep 08. 2004.
5.11 The MySQL Query Cache
MySQL Version 4.0.1 이상부터, qeury cache가 도입되었다. qeury cache가 이용될 경우 클라이언트로부터 입력된 SELECT 쿼리를 캐쉬에 저장한다. 이후 정확히 같은 쿼리가 입력되었을 때, 해당 쿼리를 MySQL 서버에서 실행하지 않고 캐쉬로부터 가져와 클라이언트에게 전달한다.
query cache는 테이블의 내용이 자주 변하지 않고 정확히 같은 쿼리를 반복적으로 사용할 경우 큰 효과를 보여준다.
주의 :
1. query cache는 오래된 data를 클라이언트에게 전달하지 않는다. 테이블의 내용이 변경될 경우, 캐쉬에 저장된 관련 쿼리의 내용을 모두 초기화시킨다.
2. 하나의 서버에 여러 mysqld 데몬을 운영하는 경우에는 동작하지 않는다.
5.11.1 How the Query Cache Operates
모든 query는 클라이언트에게 전달하기 전에 서로 비교하는 과정을 거친다. 따라서 query cache는 아래의 두 query문은 서로 다르게 인식한다.
SELECT * FROM tbl_name;
Select * from tbl_name;
query는 동일하게 인식되기 위해서는 정확히 일치해야 한다. 또한 같은 query문이라도 몇몇 상황에서는 다르게 받아들여진다. 다른 database, 다른 protocol 버전, 다른 문자 셑을 가질 경우 각각 서로 다른 query문으로 간주되어 따로 따로 cache 된다.
query cache로부터 결과값이 클라이언트에게 전달되면, 서버는 Qcache_hits 값을 자동 증가시킨다.
특정 테이블의 내용이 변경되면, 해당 테이블로부터 쿼리 cache된 모든 내용은 삭제된다. 테이블의 내용은 INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, 또는 DROP DATABASE와 같은 쿼리로 인하여 변경된다.
query cache는 SELECT SQL_CALC_FOUND_ROWS ... 와 SELECT FOUND_ROWS() 타입의 쿼리에도 적용된다. FOUND_ROWS()는 선행 query가 cache에 저장되어 있다하더라도 record의 개수 또한 cache에 저장되어 있으므로 정확한 값을 반환한다.
아래의 함수를 포함한 query문은 cache를 이용하지 않는다 :
|
BENCHMARK() |
CONNECTION_ID() |
CURDATE() |
|
CURRENT_DATE() |
CURRENT_TIME() |
CURRENT_TIMESTAMP() |
|
CURTIME() |
DATABASE() |
ENCRYPT() with one parameter |
|
FOUND_ROWS() |
GET_LOCK() |
LAST_INSERT_ID() |
|
LOAD_FILE() |
MASTER_POS_WAIT() |
NOW() |
|
RAND() |
RELEASE_LOCK() |
SYSDATE() |
|
UNIX_TIMESTAMP() with no parameters |
USER() |
|
또 query가 아래와 같은 조건일 경우에도 cached 되지 않는다 :
❚ 사용자 정의 함수(UDF)가 포함된 경우.
❚ 사용자 변수(user variables)가 포함된 경우.
❚ mysql의 system database에 있는 테이블을 조회하는 경우.
❚ 아래의 형태를 가진 경우 :
SELECT ... IN SHARE MODE
SELECT ... INTO OUTFILE ...
SELECT ... INTO DUMPFILE ...
SELECT * FROM ... WHERE autoincrement_col IS NULL
❚ TEMPORARY tables을 사용하는 경우.
❚ table를 사용하지 않는 경우.
❚ user가 관련 table에 column 레벨의 권한만을 가진 경우.
❚ query가 cache로부터 값을 가져오기 전에 MySQL은 user가 관련 database와 table에 SELECT 권한을 가지고 있는 지 체크한다. 권한이 없을 경우 cached 된 값을 반환하지 않는다.
5.11.2 Query Cache SELECT Options
SELECT 구문에 정의할 수 있는 두 개의 cache 관련 옵션이 있다.
SQL_CACHE
query_cache_type이 ON 이거나 DEMAND 인 경우, 결과 값을 cache로부터 반환시킨다.
SQL_NO_CACHE
쿼리 결과를 cache 시키지 않는다.
예 :
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
5.11.3 Query Cache Configuration
have_query_cache DB 환경 변수는 query cache를 이용할 수 있는 지 여부를 확인할 수 있다.
mysql> SHOW VARIABLES LIKE 'have_query_cache';
|
Variable_name |
Value |
|
have_query_cache |
YES |
query cache를 이용하기 위한 옵션으로 몇 가지가 있다. 이 옵션들은 /etc/my.cnf 에 정의하거나 mysqld 구동 스크립트에 정의 할 수 있다. query cache 관련 변수는 query_cache_ 로 시작한다.
query cache의 크기를 지정하기 위해서는 query_cache_size를 이용한다. 0으로 설정하면 query cache는 더 이상 사용되지 않는다. 기본값은 0이다.
query cache가 enable 된 경우, query_cache_type 변수는 SELECT 방법에 영향을 준다.
0 또는 OFF 인 경우 cache시키거나 혹은 cached 된 결과를 가져올 수 없다.
1 또는 ON 인 경우 SELECT SQL_NO_CACHE 인 구문을 제외하고 cache 기능을 이용한다.
2 또는 DEMAND 인 경우 SELECT SQL_CACHE 구문만 cache 기능을 이용한다.
클라이언트는 query cache 기능을 이용하고 싶지 않을 경우 아래와 같이 할 수 있다:
mysql> SET SESSION query_cache_type = OFF;
하나의 query 결과를 caching 하기 위한 최대 값은 query_cache_limit 변수로 수정할 수 있으며, 기본값은 1MB를 가진다.
5.11.4 Query Cache Status and Maintenance
query cache 기능이 존재하는 알고 싶을 경우 아래의 구문을 사용한다.
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
좀 더 효율적으로 메모리를 사용하기 위해 FLUSH QUERY CACHE 구문으로 조각모음을 할 수 있다. 그렇다고하여 cache로부터 query된 내용을 삭제하지는 않는다.
query cache로부터 query 결과를 삭제하기 위해서는 RESET QUERY CACHE 또는 FLUSH TABLES 구문을 사용한다.
query cache 성능을 모니터링하기 위해서는 SHOW STATUS를 이용한다.
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name | Value |
+-------------------------+--------+
| Qcache_free_blocks | 36 |
| Qcache_free_memory | 138488 |
| Qcache_hits | 79570 |
| Qcache_inserts | 27087 |
| Qcache_lowmem_prunes | 3114 |
| Qcache_not_cached | 22989 |
| Qcache_queries_in_cache | 415 |
| Qcache_total_blocks | 912 |
+-------------------------+--------+
SELECT query 전체 구문은 :
Com_select
+ Qcache_hits
+ queries with errors found by parser
Com_select 값은 :
Qcache_inserts
+ Qcache_not_cached
+ queries with errors found during columns/rights check
와 같다.
query cache는 가변 길이의 block을 이용한다. 따라서 Qcache_total_blocks 와 Qcache_free_blocks는 분산된 memory 조각을 가르킨다. FLUSH QUERY CACHE을 하고 난 뒤에는 하나의 free block만 남게된다.
모든 캐쉬에 저장된 query는 최소 2 block를 가진다. query에 이용된 모든 테이블은 1 block을 갖는다. 그러나 같은 테이블을 2 또는 그 이상의 query에서 조회한다면 1 block을 할당해야한다.