MySQL/MariaDB 아키텍처 – 쿼리 캐시(Query Cache)

MySQL/MariaDB 아키텍처 – 쿼리 캐시(Query Cache)

 

• Version : Mariadb 5.5.4.2-WinX64

 

MySQL / MariaDB 쿼리 캐시(Query Cache)는 타 DBMS에는 없는 기능을 적절히 설정만 잘한다면 상당한 성능 향상 효과를 얻을 수 있다. 쿼리 캐시는 SQL문장을 캐시하는 것이 아니라 쿼리의 결과를 메모리에 캐시해 두는 기능이다. 쿼리 캐시의 구조는 간단한 키와 값의 쌍으로 관리되는 맵과 같은 데이터 구조로 구현돼 있다.

 

쿼리 캐시에서 데이터를 내보내기 전에 다음과 같은 과정이 필요하다.

 

쿼리 캐시는 MySQL의 어떠한 처리보다 앞 단에 위치하며 캐시된 결과를 찾기 위해 쿼리 문장을 분석해서 복잡한 비교 과정을 거치는 것이 아니기 때문에 아주 간단하고 빠르게 진행 된다. 비교 방식은 요청 쿼리가 동일한지(띄어쓰기, 대소문자 포함 완전히 동일해야 한다.) 여부를 비교한다.

 

프리페어 스테이트먼트(바인드 변수가 사용된 쿼리)의 경우에는 쿼리 문장 자체에 변수가 사용되기 때문에 쿼리 문장 자체로 쿼리 캐시를 찾을 수가 없다. 쿼리 결과가 캐시에 저장된 이후 데이터가 변경되면 쿼리 캐시의 데이터는 의미가 없기 때문에 데이터를 제거해야 한다. 쿼리 캐시 데이터 제거는 테이블 단위로 처리되기 때문에 테이블의 크기가 큰 경우 아무리 메모리라고 해도 상당한 시간이 소모될 수 있다. 쿼리 캐시는 절대 여러 스레드에서 동시에 변경할 수 없기 때문에 다른 스레드는 쿼리 캐시 삭제 작업이 완료될 때까지 기다려야 한다. 따라서 너무 큰 쿼리 캐시 설정 자체가 부하를 유발할 수 있다.

 

일부 쿼리의 큰 결과는 쿼리 캐시를 모두 독점할 수도 있기 때문에 이러한 현상을 예방하고자 특정한 크기 미만의 쿼리 결과만 캐시하도록 설정할 수 있다. 설정 파라미터는 query_cache_limit 이며 설정에 크기 미만의 쿼리 결과만 캐시한다. 일반적으로 1~2M 미만으로 설정한다. 현재 쿼리 리미트는 1048576byte인 것을 확인할 수 있다. 서버에서 쿼리 캐시를 사용하지 않을 경우 query_cache_size를 0으로 설정하여 쿼리 캐시로 인한 오버헤드를 방지할 수 있다.

show variables like 'query_%';

 

 

[쿼리 캐시를 사용하지 못하는 경우]

• 임시 테이블에 대한 쿼리
• 사용자 변수의 사용(프리페어 스테이트먼트와 동일하게 작용)
• 칼럼 기반의 권한 설정
• LOCK IN SHARE MODE 힌트
• FOR UPDATE 힌트
• UDF(User Define Function)사용
• 독립적인 SELECT문장이 아닌 일부분의 서브 쿼리
• 스토어드 루틴(Procedure, Function, Trigger)에서 사용된 쿼리
• SQL_NO_CACHE 힌트

 

 

MySQL 서버에서 실행되는 작업은 대부분 MySQL 서버의 상태 변수에 누적되어 기록되기 때문에 SHOW GLOBAL STATUS 명령을 사용하여 쿼리 캐시가 얼마나 사용되었고 MySQL 서버에서 SELECT 쿼리가 얼마나 실행되었는지 확인해 볼 수 있다. Qcache_hits 정보가 쿼리 캐시로 처리된 SELECT 쿼리의 수 이다.

show global status like 'qcache%'

 

 

Com_select정보는 쿼리 캐시에서 결과를 찾지 못하여 MySQL 서버가 쿼리를 실행한 횟수를 의미한다. Com_Select 와 Qcache_hits 를 합하면 서버로 요청된 모든 SELECT 쿼리의 횟수가 된다.

show global status like 'Com_select'

 

 

쿼리 캐시 히트율(%) = Qcache_hits / (Qcache_hits + Com_select) * 100

 

 

 

[참고자료]

• RealMySQL / 위키북스
• The MySQL Query Cache : https://dev.mysql.com/doc/refman/5.1/en/query-cache.html

 

 

2015-07-21 / 강성욱 / http://sqlmvp.kr

 

 

MySQL, MariaDB, 쿼리 캐시, Query Cache, MySQL 튜닝, DBA, 데이터베이스