[튜닝] 유물발굴 (mysql tuning using heavy query)

1. 유물발굴

 

분명 느린쿼리는 아니었다 수행시간도 0.5초 이내였고, 슬로우쿼리 로그체크 시간은

2초라서 평소에는 슬로우쿼리 목록에서 보이지 않았는데,

트래픽이 몰리고 메모리 할당에 지연이 생기면 항상 가장 많이 보이던..

그래서 이번에 수정하자는 주위의견이 있어 검토에 들어갔다.

삽엽충 정도로 생각했는데, 이게 full processlist 로 확인해보니 티라노 사우르스다.

쿼리 라인이 300 정도 되고 끝도 없는 join 파티에 group by, file sort, temporary

부하에 대한 고려가 전혀없이 한 번에 모든 것을 가져가기 위해 만든 쿼리같아 보였다.

물론 그냥 덮어놓고 싶었지만 뭐 일단 열었으니 발골해서 붓칠이라도 해보기로 했다.

 

2. 개선전 PLAN 확인

 

 

이 쿼리가 OLAP라면 수행시간이 0.5초 내외고 그닥문제 되지 않을 것이다.

하지만 이 쿼리는 OLTP이고 분당 200회이상 호출하는 쿼리이다.

풀스캔의 row수가 너무 크고, 파일소트도 문제지만 임시테이블을 너무많이 사용한다.

메모리 할당 과 해제시 CPU의 자원소모가 심할 것으로 보인다.

 

 

3. 튜닝

1) 풀스캔(ALL) 제거

--> 최대한 제거할수 있는 만큼 풀스캔을 줄이기 위해 인덱스 추가

 

2) 쿼리개선

--> mysql 5.1 버전에서 볼 수 있는 쿼리형식을 5.7에 개선된 형태에 맞게 수정함

 

3) 정렬인덱스(group by) 추가

--> 드라이빙 테이블을 적절하게 활용하지 못하고 file sort를 쓰는 것을 확인하여

      정렬인덱스를 추가하여 인덱싱 가능하게 개선

 

4) function 개선

--> funciton 안에 now()을 쓰는등 비효율적으로 작성된 function을 제거하고

      subquery로 변경하여 메모리 캐싱을 쓸 수 있게 변경함

 

4. 개선후 PLAN 확인

 

 

쿼리수행시간은 0.5초에서 0.06초로 줄였다.

남아 있는 풀스캔은 테이블 데이터 건수가 얼마 안되서 옵티마이저가 풀스캔이 더 효율적이라고 판단한 것이고, filesort와 임시테이블 사용도 줄였다.

조회하는 rows도 30만건에서 710건으로 많이 줄어든 것을 볼 수 있다.

 

 

하나 더 개선하고 싶은 게 있었는데 인덱스 스캔을 하지만 6640 rows를 조회하는 테이블, 인덱스스캔이지만 그리 효율적이지는 않다.

테이블 스키마를 보고 데이터 분석을 해보니 테이블 설계한 것과 실제 쌓인 데이터가 뭔가 맞지가 않았다.

복합PK로 묶고 데이터의 정합성을 맞춘다면

비효율적인 인덱싱도 개선이 가능할 것으로 보이는데, 혼자 처리할 수 있는 일이 아니니 이건 추후에 진행하기로 했다.

 

 

5. 자빅스 모니터링 확인

 

 

 

메모리의 할당/해제와 풀스캔을 줄이니, CPU의 자원사용이 한결 여유로워진 것을 볼 수 있다.

실제 서버상에서 top으로 확인해보면 cpu jumping 이 반영전에 150~300 이었다면

현재는 30~90사이로 많이 안정되었다.

부하에 좀 더 견딜수 있는 단단한 DB가 되어가고 있다.

 

끝~