kth 개발자 블로그

개발자가 행복한 회사 kth

 

MySQL에서의 Query Cache. 잘써도 독일 수 있다!!

August 2, 2011

by kth 공통플랫폼팀 성동찬

안녕하세요. 오랜만에 블로그에 올려봅니다.
날씨는 후덥지근하고, 비는 죙일 내려서 축축하고 생활이 질펀한 이 즈음..
시원한 회사 에어컨을 쐬며, 이리저리 테스트를 해보며 알아가는 좋은 환경(?)인 것 같네요^^
아무튼!! 오늘 제가 주제로 삼고 싶은 사항은 바로 “Query Cache“라는 놈입니다.

Overview

Query Cache란 한마디로 줄여서 말하자면.. SQL실행 결과 값을 메모리에 임시 저장해 놓는 놈을 말합니다.  실행이 오래 걸리는 결과값을 메모리에 임시 저장해놓고 다음 동일 SQL 호출 시 만들어진 결과를 보여주면 되기 때문에.. 메모리에 캐싱된 결과값 재 실행 속도는 말그대로 메모리 퍼포먼스와 동일하다고 볼 수 있습니다. 참으로 효율적이고, 분명 서버의 성능을 최대로 올려줄 만한 놈으로 보입니다만.. 이것이 최적일까요?
한번 심층 분석(?) 해보도록 해보죠.

Query Cache?

 설명드리기에 앞서서 먼저 MySQL의 기본적인 아키텍처에 대해서 소개가 필요할 것 같네요.(특히 Query Cache)

빨간 색으로 강조 표시를 해 놓은 부분이 Query Cache영역입니다.

일반적으로 Query Cache없이 DB가 구동되면
[Query Parsing] → [Optimization] → [Execution]  등 일련의 과정을 거칩니다.

그러나 Query Cache가 있고 그안에 이전에 실행했던 SQL결과가 존재한다면 위 과정 없이 바로 Cache에서 결과를 보내줍니다. 동일한 결과 재 응답 시 DB 입장에서는 이만한 강력한 무기가 없을 것입니다.

기존 MySQL과 연동되는 NOSQL Plugin의 성능 또한 직접 DB 스토리지 엔진과 통신하는 데에서 나옵니다. 물론 Query Cache와 동일하지는 않지만, 데이터 조회를 위해 필요한 단계를 거치지 않고 직접 실행함으로써 강력한 퍼포먼스를 발휘하는 것이죠. ( HandlerSocket, MySQL+memcached 모두 중간의 단계를 거치지 않고 스토리지 엔진과 직접 통신하는 방식이더군요. )

Query 결과가 메모리에 캐싱되는 여부는 Query_Cache_Type값 설정(ON/OFF/DEMAND)에 따라 조정 됩니다. (하단 참고)
http://dev.mysql.com/doc/refman/5.5/en/query-cache-in-select.html
1) Query_Cache_Type = ON 
   –  예외사항을 제외한 모든 SQL결과를 메모리에 캐싱하겠다.
2) Query_Cache_Type = OFF
   – Query Cache를 사용하지 않겠다.
3) Query_Cache_Type = DEMAND
   – 사용자 요청시(SQL_CACHE, SQL_NO_CACHE)에만 Query Cache를 사용하겠다.

중요한 것은 Query Cache 안에 있는 결과셋이 메모리 캐싱되지 않는 상황과 제거되는 상황입니다.

다음과 같은 Function이 호출되면 관련 SQL은 메모리에 캐싱되지 않는다고 합니다.
1) CONNECTION_ID, CURDATE,  NOW, RAND, UUID, FOUND_ROWS 외 다수
2) User Definded Function(UDF)을 사용하는 경우
3) 아래와 같은 타입의 SQL호출 시
   

위 경우는 어디까지나 가장 빈번하게 사용될만한 상황만 언급한 것이고, 실제로는 엄~청 많습니다.
자세한 내용은 MySQL 메뉴얼을 참조하세요~ 하단 클릭
http://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html

또한 메모리에 지금 막 캐싱된 데이터라고 하더라도, 캐싱된 데이터가 참조하는 테이블에 변경 작업 발생 시 제거 됩니다.
하단 테스트에서 확인해볼 수 있으니 관련 내용을 말씀드릴께요.

자~ 이제 Query Cache와 관련된 테스트 진행 후 결과를 말씀드리겠습니다.^^

Query Cache Test Result

환경 : HP G7(8core, 24G메모리)/Raid-10/2G Data/1000만 건 데이터/Sysbench 툴 활용
Query_Cache_Type을 ON/DEMAND/OFF등 세 가지로 설정 후 테스트 진행

<결과>
1) Query_Cache_Type이 ON인 경우 READ-ONLY에서 상당히 저조한 성능을 보입니다.
2) Query_Cache_Type이 OFF인 경우 READ-ONLY에서 정상적인 패턴을 보입니다.
3) Query_Cache_Type이 DEMAND인 경우 READ-ONLY에서 20-Thread 이상에서는 급격한 성능 저하를 보입니다.
4) 세가지 경우 모두 READ/WRITE 성능이 일관된 모습을 보입니다.

1), 3)번 분석이 필요하겠네요.
두 경우 모두 “Waiting for query cache lock” 상태로 인해 DB 퍼포먼스가 상당히 떨어진 상태입니다.
즉, 모든 SQL이 Query Cache라는 놈을 사용하기 위해서 실행이 일시 중지되어 있다는 것을 의미하죠.
특히나 3)번의 경우 일정 Thread 이상에서 “Waiting for query cache lock”가 발생하였는데, 이 경우 MySQL BUG인지는 조금더 확인이 필요합니다.
(결과가 이상해서 재 테스트를 해보았으나, 동일한 결과가 나왔습니다.)

이 테스트 후 조금더 재미난 테스트를 해보았습니다. 바로 Query Cache가 언제 소멸되는지..
문서 상으로는 테이블에 반영이 발생하면, 관련 캐시들이 소멸된다고 하는데, 정말 그런지..
결론부터 말씀드리면.. Query Cache와 전~혀 관련이 없는 데이터라고 하더라도, 관련 테이블 변경 시점에 정말로 사라집니다. -_-;;
다음 결과를 보시죠^^

전혀 관련이 없는 데이터 변경 후 동일 SQL 재실행 시도해보니, 마치 처음 실행된 것 처럼 시간이 소요됨을 확인할 수 있겠죠.^^

위 결과에서 앞서 시행했던 테스트에서 REAE/WRITE 성능에서는 큰 차이가 없었던 이유를 유추할 수 있겠습니다. 매번 Query Cache 값들이 소멸되기 때문에, Caching할 공간을 할당받기 위해 대기할 이유가 없는 것이죠^^ 

Conclusion

Query Cache를 잘 쓰면 약이 되지만, 잘 써도 약이 될 수 없는 상황이 있습니다. 특히나 OLTP(online transaction processing)환경에서는 더욱더 그렇습니다. 순간 순간 테이블에 반영이 끊임없이 발생한다는 말은 Query Cache 또한 꾸준히 소멸된다는 말입니다. 특히나 데이터 변경이 전혀 없다고 하더라도, Select 쿼리가 고정적이지 않다면, Query Cache 영역을 할당받기 위해 매번 대기상태에 빠지겠죠.

MySQL을 사용하는데 Query Cache 사이즈를 얼마를 줘야하냐고 제게 물으시면, 그냥 “사용하시지 마세요”라고 말씀드리고 싶네요. 아니면 “꽤 크게” 할당하여 관련된 Lock을 최대한 줄일 수 있는 방안을 고려하시는게 낫겠죠. Query Cache도 좋지만, 그것보다는 Application레벨에서 별도의 Server Cache(Memcache/EHCache)를 두어서 구현하시기 바랍니다. 정말 원하는 데이터만 WAS레벨에서 캐싱하는 것이 훨씬 낫다고 생각되니까요..^^

MySQL은 분명 OLTP에 적합한 DBMS이고, 그러한 데이터 관리에 최적화되어 있습니다. 물론 Query Cache를 잘 쓰면 되지 않냐고 반문하실 수 있겠지만, DW환경이 아닌 OLPT환경에서 위에서 언급한 환경(SQL/)을 조성할 수 있을 지 의문이 드네요..^^ (물론 가능하다는 쓰는 것이 훨씬 낫겠죠..ㅎㅎ)

더 좋은 이야기로 찾아뵙겠습니다.^^
(정리되지 않은 내용들이 계속해서 쌓여가는 느낌이네요. 얼른 부지런하게 블로그에서 풀도록 노력할께요^^)

About the author

sdchan1 현재 KTH에서 DBA로 활동하고 있으며, 과거 5년 정도 개발 업무를 수행했습니다. RDBMS 뿐만 아니라 NOSQL 및 Cache 에도 관심을 가지고 있으며, 이를 이용하여 절대 깨어지지 않는 高高高 가용성 시스템을 만드는 것을 목표로 하고 있습니다. "L"OCK n "L"OCK baby~! 트위터 : @gywndi

작성일 : 2011/08/02 | 태그: , , ,
글 분류 Tech Note | 작성자 성동찬 ( sdchan1 )

| Trackback URL


"MySQL에서의 Query Cache. 잘써도 독일 수 있다!!" 의 관련글 1 개

  1. MySQL에서의 Query Cache. 이렇게 써보면 어떨까? | 파란 개발자 블로그 wrote:

    [...] MySQL에서의 Query Cache. 잘써도 독일 수 있다!! [...]

 
Powered by Wordpress and MySQL. Theme by Shlomi Noach, openark.org