MYSQL 매뉴얼중 Query cache에 대한 내용만을 Study 하기 위해 비공식적으로 

번역한 것을 도움이 될것 같아 올립니다. Tip&Tech 부분이 가장 적당할 것 같아

Tip&Tech에 올립니다. 저자권에 문제가 되는 경우는 바로 삭제 하겠습니다.


정리하면서 수정해서 다시 올립니다. 허접으로 번역해서 도움이 될지 모르겠습니다.


6.9 MySQL Query Cache

==============================

MYSQL 4.0.1 버전부터 Query Cache 개념이 도입되었다.

Query Cache가 사용되면 SELECT Query문과 SELECT 결과를 캐싱처리하고

나중에 동일한 Query 요청이 들어오면 Query문을 실행하지 않고

Query Cache에서 결과를 전달하는 식으로 동작 한다.


Query Cache는 동적인 내용을 많이 사용하는 웹서버 등에서 데이터 변경이 빈번하지

않는 테이블을 대상으로 동일한 Query를 중복해서 사용하는 경우 

매우 유용하다.


아래는 Query Cache의 성능에 대한 일부 자료 이다.

( OS: Linux Alpha  CPU: 2x500 MHZ  RAM: 2GB  Query Cache:64MB 환경의 

      서버에서 MySQL benchmark 에서 생성되었음)

  

  . 실행한 모든 쿼리문들이 단순하지만 (ex 한 레코드만 있는 테이블에서 레코드를 선택하는 쿼리)

    쿼리문이 모두 달라 캐시가 될 수 없는 경우는 Query Cache를 하기 위한 오버헤드가 13%

    증가 했다. 이 경우는 최악의 시나리오로 볼 수 있다. 일반적인 경우 현업에서 Query문은 훨씬 

    복잡하기 때문에 오버헤드는 현저히 줄어든다.

  . 레코드가 하나 있는 테이블의 레코드가 캐시된 후 검색은 238% 빨라졌다. 이는 Query가 캐시된 후

    예상할 수 있는 최소한의 성능향상에 근접한 결과라고 볼 수 있다.

  . Query Cache를 사용하지 않고자 하는 경우 Query_Cache_size=0으로 my.cnf 파일에 설정하면

    된다. Query Cache를 사용하지 않으면 눈에 띄는 오버헤드는 없다.

    ( Query Cache는 컴파일시에 --without-Query-Cache 옵션을 사용해서 제외할 수도 있다.)

    


6.9.1 Query Cache 동작 방식

==============================


동일한 Query문이란?


  MYSQL 서버에 Query문 처리 요청이 들어 오면 Query Cache된 Query문과 같은 Query문 인지 

  비교하는 작업이 먼저 이루어 지는데 이때 Query문의 모든 문자가 일치해야 동일 

  Query문으로 간주 된다.

  따라서 SELECT * FROM TABLE 과 Select * from table 은 다른 쿼리로 간주한다.


  Query문이 일치하더라도 각각 다른 Query문으로 간주되어 따로 캐시 처리 되는 경우도 있는데

  database,프로토콜 버젼,디폴트 character set이 다른 경우이다.

  이때는 각각 다른 Query로 간주되어 각각 캐시 처리 된다.

  


Query Cache의 제외 대상 


   특정 SELECT문은 Query Cache 대상이 되지 않는 경우도 있는데 

      SELECT CALC_ROWS ... 유형의 쿼리와  

      SELECT FOUND_ROWS () ... 유형의 쿼리는 

   Query Cache의 대상이 되지 않는다.


   아래의 함수를 포함하고 있는 SELECT Query문은 캐시 되지 않는다.


   Function 

   -----------------------

   User-Defined Functions        

   CONNECTION_ID       

   FOUND_ROWS

   GET_LOCK  

   RELEASE_LOCK  

   LOAD_FILE  

   MASTER_POS_WAIT  

   NOW  

   SYSDATE  

   CURRENT_TIMESTAMP  

   CURDATE  

   CURRENT_DATE  

   CURTIME  

   CURRENT_TIME  

   DATABASE  

   ENCRYPT (with one parameter)  

   LAST_INSERT_ID  

   RAND  

   UNIX_TIMESTAMP (without parameters)  

   USER  

   BENCHMARK  

   

   Query문에 사용자 변수가 있는 경우와

   SELECT ... IN SHARE MODE 유형의 쿼리

   SELECT * FROM AUTOINCREMENT_FIELD IS NULL(마지막 INSERT된 ID를 가져오기 위해)

   유형의 쿼리도 캐시되지 않는다.

 

   참고로 이전의 Query문의 결과가 캐시에서 가져온 경우라도 FOUND_ROWS()는 정확한 값을 리턴한다.


   또 SELECT Query가 테이블을 대상으로 하지 않는 경우, 

   임시 테이블을 사용하는 경우,

   접속 클라이언트가 Query문 대상의 테이블들중 한 테이블에 대해 컬럼 권한(Column Privilege)을 

   가지고 있는 경우에도 쿼리가 캐시되지 않는다.


Query Cache에 저장된 캐시의 삭제


    테이블이 변경되는 경우(INSERT, UPDATE, DELETE, TRUNCATE, ALTER, DROP TABLE|DATABASE)는 

    해당 테이블(MRG_MyISAM table를 통한 경우도)을 참조한 모든 캐시 데이터가 삭제 되고,

    InnoDb 테이블에서  트랜잭션이 이루어지는 단계에서는 테이블이 변경되는 경우는 

    COMMIT이 실행될때 캐시 데이터가 삭제 된다.


Query Cache 적용시

    Query Cache를 적용시에는 접속클라이언트가 적용할 캐시에 관계된 모든 데이터베이스들과

    테이블들에 대해 SELECT 권한이 있을때만 적용 된다.


6.9.2 Query Cache 환경설정

==============================

Query Cache를 사용하기 위해서는 환경파일의 mysqld 부분이나 mysqld를 커맨트라인에서 실행하는 경우

모두 추가해야 할 사항이 있다.


   . query_qache_limit  결과가 이 값보다 큰 경우 결과를 캐시하지 않는다. ( default 1M)

   . query_qache_size   캐시 하기 위해 메모리상에 확보해야할 메모리 크기

                        이 값이 0이면 Query Cache가 사용되지 않는다.   ( default 0)

   . query_qache_type   숫자형식이며 다음값중 하나가 사용됨

                        0 :  (OFF, 캐시하지 않으며 캐시에서 결과를 가져오지도 않음)

                        1 :  (ON, SELECT SQL_NO_Cache ... 를 제외한 모든 Query 

                                  결과를 캐시함)

                        2 :  (DEMAND, SELECT SQL_Cache ... 쿼리들만 캐시 한다.)

                        


런타임에 thread(connection)에서 동적으로 Query Cache의 기본 동작 방식을 변경할 수도 있는데

문법은 다음과 같다.


Query_Cache_TYPE = OFF | ON | DEMAND 

Query_Cache_TYPE = 0 | 1 | 2 


      (   0 or OFF  : 캐시하지 않으며 캐시에서 결과를 가져오지도 않음

          1 or ON   : SELECT SQL_NO_Cache ... 를 제외한 모든 Query 결과를 캐시함

          2 or DEMAND : SELECT SQL_Cache ... 쿼리들만 캐시 한다.)

          


6.9.3 SELECT 문에서 Query Cache 옵션들

========================================


SELECT문에 Query Cache와 관련되서 두가지 옵션이 첨가 될 수 있다.


SQL_CACHE : Query_Cache_TYPE이 DEMAND인 경우는 Query를 캐시 한다.

            Query_Cache_TYPE이 ON인 경우 default

            Query_Cache_TYPE이 OFF인 경우 의미 없음


 ex> SELECT SQL_CACHE .....

 

SQL_NO_CACHE : Query문이 캐시에 저장되지 못하도록 함.


 ex> SELECT SQL_NO_CACHE ...

 


6.9.4 Query Cache 상태와 유지관리

========================================

FLUSH QUERY CACHE

  FLUSH QUERY CACHE는 Query Cache의 메모리를 조각모음해서 메모리 사용 효율을

  높일때 사용 한다.


FLUSH TABLES

  FLUSH TABLES는 Query Cache를 비운다.


RESET QUERY CACHE

  RESET QUERY CACHE는 Query Cache의 캐시된 모든 쿼리결과를 제거 한다.



Query Cache 지원 여부 확인

  현재 사용하고 있는 MYSQL Version에서 Query Cache가 있는지 여부를 다음과 같이 

  확인 할 수 있다.



  mysql> SHOW VARIABLES LIKE 'have_Query_Cache';

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

  | Variable_name    | Value |

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

  | have_Query_Cache | YES   |

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

  1 row in set (0.00 sec)


Query Cache 성능 모니터

    

 SHOW STATUS 문을 통해 Query Cache의 성능을 모니터 할 수 있다.


 변수                        설명

 ------------------------    ------------------------------------

 QCache_queries_in_Cache    캐시에 등록된 쿼리 갯수. 

 QCache_inserts             캐시에 추가된 쿼리 갯수. 

 QCache_hits                캐시 Hit(적용된) 갯수. 

 QCache_lowmem_prunes       메모리 부족으로 캐시에서 삭제된 쿼리 갯수. 

 QCache_not_Cached          캐시 불가능한 쿼리 갯수(캐시 불가능 하거나 Query_Cache_TYPE에 의해). 

 QCache_free_memory         Query Cache의 비할당 메모리양. 

 QCache_free_blocks         Query Cache의 비할당 메모리 블럭의 갯수. 

 QCache_total_blocks        Query Cache의 총 메모리 블럭 갯수. 


 총 쿼리 수 = QCache_inserts + QCache_hits + QCache_not_Cached


 Query Cache는 동적인 크기의 블럭을 사용하기 때문에  QCache_total_blocks 과

 QCache_free_blocks은 Query Cache의 메모리의 조각화 현상을 보여 준다.

 FLUSH Query Cache 후에는 QCache_free_blocks의 값이 1이 된다.


 NOTE: 각 Query는 최소한 두 블럭(Query문과 Query결과값)을 사용한다. 

       또, Query에 사용된 각 테이블은 한 블럭을 사용하지만, 여러 Query에서 

       같은 테이블이 사용되는 경우에는 한 블럭만 할당 된다.

      

 QCache_lowmem_prunes 값을 참조해서 Query Cache의 크기를 튜닝할 수 있다.

 이 값은 새로운 쿼리를 캐시하기 위해 캐시에서 삭제된 쿼리 갯수인데 Query Cache는

 least recently used(LRU : 최근에 가장 사용빈도가 적음) 전략을 통해 Cache에서

 제거할 쿼리를 결정 한다.

by 차까꿍 2015. 1. 2. 22:36