[MySql] 쿼리 캐시
쿼리 캐시
쿼리 캐시는 MySql 에만 존재하는 기능 중 하나로써, 적절한 설정을 하면 상당한 성능 향상 효과를 얻을 수 있다.
SQL 문장을 캐시하는 것이 아니라 쿼리의 결과를 메모리에 캐시해 두는 기능이다.
구조는 간단히 맵(Map)과 같이 키와 값의 구조로 구현되어 있다. 여기서 키는 쿼리 문장, 값은 쿼리의 실행 결과를 나타낸다.
데이터베이스에서 쿼리를 처리할 때는 상당히 많은 부분의 처리 절차가 있다. 이를 전부 무시하고 동일한 쿼리 문장이 요청 됐다고해서 그냥 캐시된 결과를 보내서는 안된다.
처리 절차
요청된 쿼리 문장이 쿼리 캐시에 존재하는가?
쿼리 문장 비교대상에는 공백, 탭과 같은 문자도 모두 포함되며, 대소문자까지
완전히 동일해야 같은 쿼리로 인식한다. 동일한 쿼리라도 대소문자, 공백, 탭이 하나라도 다르면 쿼리 캐시를 공유하지 못한다.따라서
동일 작업을 하는 쿼리는 동일 문자열로 관리하는 것이 좋다.
해당 사용자가 그 결과를 볼 수 있는 권한을 가지고 있는가?
트랜잭션 내에서 실행된 쿼리인 경우, 그 결과가 가시 범위 내의 트랜잭션에서 만들어진 결과인가? (InnoDB인 경우)
- InnoDB에서는 트랜잭션 격리 수준을 준수하기 위해 각 트랜잭션은 자신의 ID보다 ID 값이 큰 트랜잭션에서 변경한 작업이나 쿼리 결과를 참조할 수 없다.
쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
CURRENT_DATE, SYSDATE, RAND 등과 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?
호출될 때마다 결과 값이 달라지는 내장 함수뿐 아니라 NOT DETERMINISTIC으로 정의된 스토어드 함수 등은 사용하지 않는 편이 쿼리 캐시의 효율을 높이는데 도움이 된다.
PrepareStatement의 경우 변수가 결과에 영향을 미치지 않는가?
PrepareStatement의 경우 쿼리 문장 자체에 변수가 사용되기 때문에 쿼리 문장 자체로 쿼리 캐시를 찾을 수 없다. 소스코드에서 PrepareStatement를 사용했다 하더라도 실제 MySql 서버에서는 PrepareStatement 형태로 실행되지 않는다.
실제로 사용하려면 소스코드에서 DB Connection을 생성할 때 특별한 옵션을 사용해야 하는데 이를
Server-Side PrepareStatement라고 한다.
캐시가 만들어진 후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
쿼리 캐시를 무효화하는 작업은 레코드 단위가 아닌 테이블 단위로 처리된다.
만약 쿼리 캐시를 1GB로 설정하고, 하나의 테이블로부터 조회된 데이터로 쿼리 캐시를 꽉 채웠다고 가정해보자. 그런데 해당 테이블에 새로운 레코드 1건이 INSERT되면 MySql 서버는 쿼리 캐시에 채워져 있는 1GB의 내용을 모두 제거해야 한다.
게다가
쿼리 캐시는 절대 여러 스레드에서 동시에 변경할 수 없다.따라서 다른 스레드는 쿼리 캐시 삭제 작업이 완료될 때까지 기다려야한다.많은 사용자가 쿼리 캐시를 위한 메모리 공간은 무조건 크게 설정하면 좋다고 생각하지만, 이러한 이유로 적절한 크기 이상으로 설정할 경우 캐시 자체가 부하의 원인이 될 가능성이 있다.
32MB ~ 64MB가 권장 메모리다.
쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
쿼리 캐시의 전체 크기를 권장 사항 최대크기인 64MB로 설정했는데, 만약 어떤 쿼리 하나가 63MB 크기의 쿼리 결과를 만들어내면 하나의 쿼리 때문에 쿼리 캐시를 다 소모해 버릴 수 있다.
이러한 현상을 예방하기 위해
특정한 크기 미만의 쿼리 결과만 캐시하도록 설정하는 시스템 파라미터가 있다.query_cache_limit으로, 값은 1~2MB 미만으로 설정한다.
쿼리가 결과를 만들어내는 데 많은 시간과 자원이 필요하지만,
만들어진 결과의 크기가 작을수록쿼리 캐시를 더효율적으로 사용할 수 있다.따라서 GROUP BY, DISTINCT, COUNT() 같은 집합 함수의 결과가 쿼리 캐시를 사용하기에 적합하다.
- 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용됐는가?
쿼리 캐시를 사용하지 못하게 하는 요소
- 임시 테이블(Temporary table)에 대한 쿼리
- 사용자 변수의 사용 : 쿼리에 사용자 변수를 사용하면 PrepareStatement와 동일한 효과가 발생해 쿼리 캐시를 사용할 수 없다.
- 컬림 기반의 권한 설정
- Lock In Share Mode 힌트 : SELECT 문장의 끝에 붙여서 조회하는 레코드에 공유 잠금(읽기 락)을 설정하는 쿼리
- For Update 힌트 : SELECT 문장의 끝에 붙여서 조회하는 레코드에 배타적 잠금을 설정하는 쿼리
- UDF(User Defined Function)의 사용
- 독립적인 SELECT 문장이 아닌 일부분의 서브 쿼리
- 스토어드 루틴(Procedure, Function, Trigger)에서 사용된 쿼리
- SQL_NO_CACHE 힌트 : SELECT 문장에서 SELECT 키워드 위에 붙이는 힌트로써, 이 힌트가 사용되면 캐시를 사용하지 않는다. 주로
쿼리의 성능을 시험하기 위해 사용한다.
MySql 서버에서 실행되는 작업은 대부분 MySql 서버의 상태 변수에 누적되어 기록되기 때문에 SHOW GLOBAL STATUS 명령을 이용해 쿼리 캐시가 얼마나 사용됐고, MySql 서버에서 SELECT 쿼리가 얼마나 실행됐는지 등에 대한 정보를 확인할 수 있다.
Qcache_hits : 쿼리 캐시로 처리된 SELECT 쿼리의 수
Com_select : 쿼리 캐시에서 결과를 찾지 못해서 MySql 서버가 쿼리를 실행한 횟수
쿼리 캐시 히트율(%) = Qcache_hits / (Qcache_hits + Com_select) * 100MySql 서버에서 쿼리 캐시를 사용하지 않기로 했다면 설정 파일의 설정 파라미터를 변경하면 된다.
query_cache_size = 0
query_cache_type = 0