MySQL 쿼리 캐시

쿼리 캐시의 연산 방식

쿼리 캐시SELECT 옵션

쿼리 캐시 구성

쿼리 캐시 상태 및 관리

쿼리 캐시는 SELECT 명령문 텍스트를 클라이언트에 보내는 결과와 함께 저장한다. 만약에 동일한 명령문이 나중에 전달되면, 서버는 그 명령문을 다시 분석 (parsing)하고 실행하는 대신에 쿼리 캐시에서 그 결과 값을 추출한다.

쿼리 캐시는 자주 변경되지 않는 테이블이 있고 서버가 동일한 쿼리를 많이 받는 환경에서 매우 유용하게 사용된다. 이것은 데이터 베이스 내용을 기반으로 많은 수의 동적 페이지를 만들어내는 대부분의 웹 서버에서 활용할 수가 있다.

Note: 쿼리 캐시는 오래된 데이터를 리턴 하지는 않는다. 테이블이 수정되면, 쿼리 캐시에 있는 모든 연관 엔트리는 플러시 된다.

Note: 쿼리 캐시는 동일한 MyISAM 테이블을 업데이트하는 여러 개의 mysqld 서버를 가지고 있는 환경에서는 사용할 수 없다.

Note: 쿼리 캐시는 서버-측면의 프리페어드 (prepared)명령문에서는 사용할 수 없다. Section 22.2.4, “C API 프리페어드 (Prepared) 명령문”을 참조.

서버 스타트업 시점에 쿼리 캐시를 비활성화 시키기 위해서는, query_cache_size 시스템 변수를 0으로 설정한다. 소스 코드를 MySQL을 설치한다면, --without-query-cache 옵션을 사용해서 configure를 실행하면 쿼리 캐시 기능을 전부 제외 시킬 수가 있다.

쿼리 캐시의 연산 방식

이 섹션에서는 쿼리 캐시를 구동 시키는 방법을 설명하기로 한다.

입력되는 쿼리는 분석을 하기 전에 쿼리 캐시에 있는 것과 비교를 하며, 따라서 아래의 두 쿼리는 쿼리 캐시에 의해 서로 다른 것으로 간주된다:

SELECT * FROM tbl_name

Select * from tbl_name

쿼리가 서로 동일한 것으로 취급되기 위해서는 정확히 똑 같아야 한다 (바이트 비교). 또한, 쿼리 스트링이 같다고 하더라도 다른 이유로 인해 서로 다른 것으로 취급될 수도 있다. 서로 다른 데이터 베이스, 프로토콜 버전, 또는 디폴트 문자 셋을 사용하는 쿼리들은 서로 다른 것으로 간주되고 별도로 캐시에 저장된다..

쿼리 결과를 쿼리 캐시에서 가져오기 전에, MySQL은 사용자가 모든 데이터 베이스 및 호출된 테이블에 대해서 SELECT 권한을 가지고 있는지 검사한다, 만약에 권한이 없다면, 캐시 결과는 사용되지 않는다.

쿼리 결과를 쿼리 캐시에서 가져오게 되면, 서버는 Qcache_hits 상태 변수를 증가 시킨다 (Com_select를 증가 시키지 않음).

테이블이 변경된다면, 그 테이블을 사용하는 모든 캐시된 쿼리들은 사용할 수 없고 캐시에서 제거된다. 여기에는 변경된 테이블을 매핑하는 MERGE 테이블도 포함된다. INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, 또는 DROP DATABASE와 같은 여러 가지 명령문으로 인해 테이블은 변경될 수 있다.

변경된 트랜젝션 InnoDB 테이블은 COMMIT 가 실행될 때에는 사용되지 못한다.

InnoDB 테이블을 사용할 때에도 트랜젝션 내에서 쿼리 캐시는 구동이 되고, 테이블 버전 번호를 사용해서 테이블 컨텐츠가 여전히 존재하는지를 검사한다.

MySQL 5.0에서는, 뷰가 만든 쿼리도 캐시에 저장된다.

MySQL 5.0 이전에는, 코멘트로 시작되는 쿼리는 캐시에 저장할 수는 있지만 캐시에서 가져올 수는 없었다. 이 문제는 MySQL 5.0에서 해결을 했다.

쿼리 캐시는 SELECT SQL_CALC_FOUND_ROWS ... 그리고 SELECT FOUND_ROWS() 타입의 쿼리에 대해서 동작을 한다. FOUND_ROWS()는 비록 발견된 열의 숫자도 함께 캐시에 저장하기 때문에 이전 쿼리를 캐시로부터 가져왔다고 하더라도 올바른 값을 리턴해 준다.

만약에 아래의 테이블에 있는 함수 중에 하나라도 쿼리가 가지고 있게 되면 캐시를 할 수가 없다:

또한, 아래의 조건에서는 쿼리가 캐시 되지 않는다

• 쿼리가 사용자 정의 함수 (UDFs)를 참조함.
• 쿼리가 사용자 변수를 참조함.
• 쿼리가 mysql 시스템 데이터 베이스에 있는 테이블을 참조함.
• 쿼리가 아래의 형태 중에 하나일 경우:
  

  SELECT ... IN SHARE MODE
  
  SELECT ... FOR UPDATE
  
  SELECT ... INTO OUTFILE ...
  
  SELECT ... INTO DUMPFILE ...
  
  SELECT * FROM ... WHERE autoincrement_col IS NULL
  
  
  
  

  마지막 형태는 쿼리가 마지막 ID 값을 얻기 위해 ODBC를 사용하고 있기 때문에 캐시 되지 못한다.
• 플레이스 홀더 (placeholder)가 사용되었다고 하더라도, 쿼리가 프리페어드 명령문 형태로 입력되는 경우. 예를 들면, 여기에서 사용되는 쿼리는 캐시되지 않는다:
  

  char *my_sql_stmt = "SELECT a, b FROM table_c";
  
  /* ... */
  
  mysql_stmt_prepare(stmt, my_sql_stmt, strlen(my_sql_stmt));
  
  
  
  

• 쿼리가 TEMPORARY 테이블을 사용한다.
• 어떠한 테이블도 쿼리가 사용하지 않는다.
• 포함된 모든 테이블에 대해서 사용자가 컬럼-레벨 권한을 가지고 있다.

쿼리 캐시SELECT 옵션

SELECT 명령문 안에서 두 개의 쿼리 캐시-관련 옵션을 지정할 수도 있다:

• SQL_CACHE
  쿼리 결과 값은 query_cache_type 시스템 변수 값이 ON 또는 DEMAND라면 캐시 된다.
• SQL_NO_CACHE
  쿼리 결과는 캐시 되지 않는다.

예제:

SELECT SQL_CACHE id, name FROM customer;

SELECT SQL_NO_CACHE id, name FROM customer;

쿼리 캐시 구성

have_query_cache 서버 시스템 변수는 쿼리 캐시가 사용 가능한지를 나타낸다:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| have_query_cache | YES   |

+------------------+-------+

표준 MySQL 바이너리를 사용하면, 이 값은 항상 YES인데, 쿼리 캐시가 비활성화 되었을 경우에도 마찬가지다.

다른 여러 가지 시스템 변수가 쿼리 캐시 동작을 제어 한다. 옵션 파일 또는 명령어 라인에서 이 변수들을 설정할 수 있다. mysqld가 시작될 때 이 변수들을 옵션 파일 또는 명령어 라인에서 설정할 수 있다. 쿼리 캐시 시스템 변수 모두는 query_cache_를 가지고 시작된다.

쿼리 캐시의 크기를 설정하기 위해서는, query_cache_size 시스템 변수를 설정하면 된다. 이것을 0으로 설정하면 쿼리 캐시는 비활성화가 된다. 디폴트는 0 이다.

여러분이 query_cache_size를 0이 아닌 다른 값으로 설정할 때에는, 쿼리 캐시는 최소한 40KB 크기를 가져야 한다는 것을 알기 바란다. (정확한 크기는 시스템 구조에 달려 있다.) 만약에 이 값을 너무 작게 설정한다면, 아래와 같은 경고문이 나오게 된다:

mysql> SET GLOBAL query_cache_size = 40000;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> SHOW WARNINGS\G

*************************** 1. row ***************************

  Level: Warning

   Code: 1282

Message: query cache failed to set size 39936; new query cache size is 0

 

mysql> SET GLOBAL query_cache_size = 41984;

Query OK, 0 rows affected (0.00 sec)

 

mysql> SHOW VARIABLES LIKE 'query_cache_size';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| query_cache_size | 41984 |

+------------------+-------+

만약에 쿼리 캐시의 크기가 0 보다 크면, query_cache_type 변수가 쿼리 캐시 동작에 영향을 주게 된다. 이 변수는 아래의 값을 가지고 설정할 수 있다:

• 0 또는OFF는 캐싱 또는 캐시 결과를 추출하지 못하게 한다.
• 1 또는 ON은 SELECT SQL_NO_CACHE로 시작되는 명령문을 제외한 나머지를 캐싱한다.
• 2 또는 DEMAND는 SELECT SQL_CACHE로 시작되는 명령문만을 캐싱 하도록 만든다.

GLOBAL query_cache_type 값을 설정하면, 데이터가 변경된 후에 접속이 되는 모든 클라이언트에 대한 쿼리 캐시 동작을 결정하게 된다. 개별적인 클라이언트는 SESSION query_cache_type 값을 사용해서 자신의 접속에 대한 캐시 동작을 제어할 수가 있다. 예를 들면, 클라이언트는 아래와 같이 자신의 쿼리를 위한 쿼리 캐시 사용을 비활성화 시킬 수가 있다:

mysql> SET SESSION query_cache_type = OFF;

캐시될 수 있는 개별 쿼리 캐시의 최대 크기를 제어하기 위해서는, query_cache_limit 시스템 변수를 설정한다. 디폴트는 1MB이다.

쿼리가 캐시 되는 경우, 그 결과 값 (클라이언트에 전달되는 데이터)은 결과를 추출하는 동안 쿼리 캐시에 저장된다. 따라서 일반적으로 데이터는 하나의 큰 단위로 취급되지 않는다. 쿼리 캐시는 필요에 따라 이 데이터를 저장하기 위한 블록을 할당하기 때문에, 하나의 블록이 가득 차게 되면, 다른 새로운 블록이 할당된다. 메모리 할당 동작은 비용이 비싸기 때문에, 쿼리 캐시는 query_cache_min_res_unit 시스템 변수가 주는 최소의 크기를 가지고 블록을 할당한다. 쿼리가 실행될 때, 최종 결과 블록은 실제 데이터 크기로 조정되고 사용하지 않는 나머지 메모리를 돌려 준다. 여러분의 서버가 실행하는 쿼리의 형태에 따라서 query_cache_min_res_unit 값을 튜닝하는 것이 도움이 될 것이다:

• query_cache_min_res_unit의 디폴트는 4KB이다. 이 크기는 대부분의 경우에 적당하다.
• 작은 결과 값을 가지는 쿼리를 많이 가지고 있다면, 디폴트 블록 크기로 인해 많은 메모리 단편화 (fragmentation)가 발생할 수도 있다. 메모리 조각은 메모리 부족을 일으켜서 쿼리 캐시가 캐시에서 쿼리를 삭제하게끔 만든다. 이와 같은 경우에는, query_cache_min_res_unit 값을 하향 조정한다. 프리 블록 (free block)의 숫자와 삭제된 쿼리의 숫자는 Qcache_free_blocks와 Qcache_lowmem_prunes 상태 변수를 가지고 얻을 수 있다.
• 만약에 쿼리 대부분이 큰 결과 값 (Qcache_total_blocks와 Qcache_queries_in_cache 상태 변수를 검사)을 가지고 있다면, query_cache_min_res_unit 값을 증가 시켜서 성능을 개선한다. 하지만, 너무 크게 하지는 않도록 주의 한다.

쿼리 캐시 상태 및 관리

여러분은 아래의 명령문을 사용해서 MySQL 서버에 쿼리 캐시가 존재하는지를 알아볼 수 있다:

mysql> SHOW VARIABLES LIKE 'have_query_cache';

+------------------+-------+

| Variable_name    | Value |

+------------------+-------+

| have_query_cache | YES   |

+------------------+-------+

FLUSH QUERY CACHE 명령문을 사용하면 보다 효율적으로 메모리를 관리하도록 쿼리 캐시 단편화 (defragment)를 해소할 수가 있다. 이 명령문은 캐시에서 어떠한 쿼리도 제거하지 않는다.

RESET QUERY CACHE 명령문은 쿼리 캐시에서 모든 쿼리 결과를 제거한다. FLUSH TABLES 명령문도 동일한 일을 한다.

쿼리 캐시의 성능을 모니터 하기 위해서는, 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 쿼리의 전체 숫자는 아래의 공식으로 얻을 수 있다:

 Com_select

+ Qcache_hits

+ queries with errors found by parser

Com_select 값은 아래의 공식으로 얻어진다:

 Qcache_inserts

+ Qcache_not_cached

+ queries with errors found during the column-privileges check

쿼리 캐시는 변수-길이 블록을 사용하기 때문에, Qcache_total_blocks 와 Qcache_free_blocks 는 쿼리 캐시 조각을 가리키게 된다. FLUSH QUERY CACHE를 실행하면 하나의 프리 블록만 남게 된다.

캐시된 모든 쿼리는 최소한 두 개의 블록을 요구한다. (쿼리 텍스트를 위해서 하나가 필요하고, 쿼리 결과 값을 위해서는 한 개 이상이 필요함). 또한, 쿼리가 사용하는 모든 테이블은 하나의 블록을 요구한다. 하지만, 만약에 두 개 이상의 쿼리가 동일한 테이블을 사용한다면, 하나의 테이블 블록만 할당하게 된다.

Qcache_lowmem_prunes 상태 변수가 제공하는 정보를 사용하면 쿼리 캐시 크기를 튜닝할 수가 있다. 이것은 캐시에서 삭제된 쿼리 숫자를 계산한다.