MariaDB 온라인쇼핑몰 성능이슈 해결과정 2편

MariaDB 온라인 쇼핑몰 성능이슈 해결과정 2편

4일차: 네이버 배너광고(2차 프로모션)

16시~17시까지 네이버 배너광고를 진행한다고 한다. 광고비만 해도 5천만원 정도라고 하는데 많은 트랜잭션이 들어올 것 같다.

Connection Pool 조정결과(3일차 적용)

응답시간/SQL시간 분포를 비교해 보자. (18시 Pool 조정) Connection Pool을 조정하기 전에는 응답시간과 SQL응답시간의 Gap이 크다. Gap time은 getConnection 대기 시간이였다. 18시 이후에는 응답시간/SQL시간이 거의 동일하며 트랜잭션도 많아졌다. Connection수가 조정되며 많은 트랜잭션이 유입되며 응답시간이 튀는 현상이 관찰된다.

이런 경우 SQL 튜닝을 통해 SQL시간을 줄여야 한다. 바로 오늘 오후에 배너광고인데 이제부터 SQL을 튜닝을 시작한다. 좀 더 일찍 DB에 접속권한을 받았다면 좋았겠지만 요즘은 보안이 강화되서 쉽지 않다.

쿼리 캐시를 이용한 튜닝

SQL응답시간이 높은 어플리케이션을 분석한결과 SQL응답시간이 6.3초 이며 SQL이 1,096회 수행된다. (평균SQL응답시간: 0.006초) 이런 경우 SQL은 튜닝포인트가 없다.

변수까지 동일한 SQL이 100회이상 반복호출 되는 것도 있지만 프로그램은 수정할 수는 없다고 한다.

그런데 트랜잭션이 많이 발생할는 SQL평균시간이 6ms에서 12ms 로만 느려져도 전체 SQL응답시간은 6.3초에서 12.6초로 느려진다.

갑자기 쿼리 캐시 튜닝방법이 생각났다. 회사 선배가 Oracle로 구축된 DW시스템에서 사용하는 것을 1번 본적이 있다. Oracle에서는 RESULT CACHE 라고 불리고 MS-SQL에서는 지원되지 않는 기능이다. 간단히 말해 SQL실행결과를 쿼리 캐시에 담고 조건을 만족하면 SQL이 수행되지 않고 쿼리캐쉬에서 결과를 가져와 튜닝하는 방법이다. 관련 프로그램 10종의 SQL에 힌트를 적용하고 모니터링 하였다.

[Note]

쿼리 캐시 SELECT 쿼리에 의해 만들어진 결과를 재사용하기 위해 선택적으로 쿼리 캐쉬에 저장한다. SELECT 쿼리 결과를 쿼리 캐시에 담아 둘지 여부를 쿼리에서 선택할 수 있으며, 이때 사용하는 힌트가 SQL_CACHE / SQL_NO_CACHE, query_cache_type이라는 시스템 변수의 설정 에 의해기본적으로 쿼리 캐시에 저장여부가 결정된다. query_cache_type을 DEMAND로 설정 query_cache_size설정 (시스템상황에 맞게 조정) 각 쿼리에 SQL_CACHE힌트를 사용한다. 예) SELECT /*! SQL_CACHE */ ….

쿼리 캐시를 이용한 튜닝결과

배너광고 30분전(15시30분) 겨우 튜닝사항을 운영시스템에 적용할 수 있었다.

모니터링 결과 초당 SELECT문장이 1,600~2000회 수행될 때 쿼리캐시 Hit가 600~700번 정도 발생하였다. 16시부터 응답시간이 줄어든 것을 볼 수 있었다. 튜닝결과는 만족스럽다.

네이버 광고 이벤트(2차 프로모션) 후 고객이 ITO사업팀에게 튜닝결과에 만족했다는 전화가 왔다.

아직 3차 프로모션도 남아있고 시스템 성능이 일정하지 않고 불안정한 상황이다.

(3편에 계속~)