회사 업무 경험담이라서 자세한 상황, 테이블이 어떻고 어떤 기능인지에 대해서는 언급을 하지 않습니다.

쿼리 개선 경험담

회사에서 예전에 개발했던 코드를 개선을 하면서 API 성능에 가장 큰 영향을 주는 쿼리도 개선했습니다.

굉장히 흔한 이야기나, 당연해서 다른 블로그에서 흔하게 이렇게 해라, 이거 하지 마라라고 하는 것들 이라도 어떤 방법으로 로그 집계 테이블의 성능을 개선했는지를 공유합니다.

쿼리 개선 배경

처음 API 개발할 때 거의 모든 조회 쿼리에 인덱스를 사용하는 것을 확인했지만 AWS RDS의 파라미터를 수정하고 파일에서 뽑은 1초 이상 걸린 쿼리를 바탕으로 슬로우 쿼리 리스트 만들어 보니 꽤 많은 쿼리들이 있었습니다.

슬로우 쿼리에 포함된 쿼리 그 대부분이 GB 단위의 로그 테이블을 집계하는 쿼리들이었습니다.

여기서 제가 했던 실수 3가지를 찾았습니다.

  1. 1~2주 사이의 기간으로 조회 할 것이라 예상했으나 1달까지의 조회가 가장 많음

  2. FULL OUTER JOIN을 한다고 너무 많은 서브 쿼리를 만듬

  3. 테이블 자체 크기가 너무 큼

학생 시절에 DB를 공부하고 프로젝트를 하며 쿼리를 짤 때는 천만건 이상의 데이터에 대해 고민할 필요는 물론이고 쿼리의 성능에 대해 고민할 일이 없었지만 현업에 와서 만난 실제 사례의 문제였습니다.

대다수의 문제들

평소 Mysql의 서브쿼리가 특히 느리다 들었는데 슬로우 쿼리에 성능에 영향을 끼치던 것들은 인덱스와 서브 쿼리였습니다.

해결책

위의 1~3번의 문제가 전부 복합적이라서 쿼리 마다 해결책이 다르긴 했지만 공통적으로 다음의 방법에 따라 쿼리를 수정했습니다.

  1. 기존에 있던 집계 테이블을 최대한 활용함
  2. 불필요한 서브쿼리 제거, 수정
  3. 1달 조회를 기준으로 테스트
  4. USE INDEX로 1달 조회에 가장 빠른 인덱스를 선택함
  5. 쿼리를 개선하는 것으로 해결이 안 되면 집계 테이블을 만듬

기존에 쿼리를 잘 못 짠 문제는 원인은 찾기 쉽지만 고치는게 오래 걸렸고 잘못된 인덱스를 타거나 쿼리 개선으로 해결이 안 되는 경우는 고치기는 쉬워도 원인을 찾기가 힘들었습니다.

복잡한 서브쿼리 문제

Mysql에는 Full Outer Join 같은게 없는데 Id와 날짜별로 집계된 수치가 없어도 0이라도 나오게 해달라는 요구가 있어서 날짜들을 저장하는 Calender 테이블을 만들고 이용해서 비슷한 기능을 만들었습니다. 그 과정에서 쿼리가 너무 복잡해지고 불필요한 서브쿼리가 생겼습니다.

먼저 서브쿼리 각각을 검토했습니다.

집계 테이블로 대체가 가능하다면 대체하고 아니라면 explain으로 인덱스를 확인해서 서브 쿼리들가 더 빨라질 수 있도록 했습니다.

그렇게 서브 쿼리가 정리하고 보니 라인 수도 줄었고 성능도 좋아졌습니다.

엉뚱한 인덱스를 사용하는 문제

쿼리를 빠르게 하고 싶어도 explain으로 본 type이 ref, eq-ref 처럼 좋은 타입이고 조건문도 이상한게 없어 보이는데 시간이 오래 걸리는 것들이 있었습니다.

explain으로 확인 했을 때 possiable_key에 나오는 여러 인덱스 중 이 인덱스를 사용해서 조회 하겠지? 라는 예상과 다른 인덱스를 사용하는 서브 쿼리가 있었습니다.

그래서 mysql에 USE INDEX로 키에 대한 힌트를 주고 제가 예상했던 인덱스와 DB가 선택한 인덱스를 비교해 봤습니다.

인덱스 종류 조회 범위 걸린 시간
DB 선택 1주 1초 이내
예상했던 인덱스 1주 1초 이내
DB 선택 1달 1초 이상 2초 이하
예상했던 인덱스 1달 1초 이내
DB 선택 2달 1초 이상 2초 이하
예상했던 인덱스 2달 3초 이상

제가 예상했던 인덱스는 1달 정도 조회에서는 더 빠른 성능을 보였지만 조회기간이 늘수록 안 좋은 성능 보였습니다. 그래서 DB가 예상과 다른 인덱스를 사용했습니다.

앞서 문제점에서 말했듯 주로 1달까지의 기간으로 조회 합니다. 그래서 USE INDEX로 힌트를 줘서 원래 예상했던 인덱스를 사용하게 쿼리를 수정했습니다.

어떻게 해도 해결이 안 되는 것들

복잡한 서브쿼리도 없고 인덱스도 모두 의도한 것만 사용하는데 느린다고 느껴진 쿼리들이 있었습니다. 쿼리를 실행할 때 집계하는 row가 너무 많은게 문제였습니다.

그래서 집계 테이블과 집계를 위한 인덱스를 만들고 사수님과 함께 트리거의 검증과 이게 필요한 집계 테이블인지를 확인하는 작업을 했습니다.

복잡한 트리거는 DB 전체 성능에 영향을 주기에 신중하게 테스트 했습니다.

트리거의 몇 배의 쿼리를 짜서 데이터를 넣고 지우고 집계 테이블로 줄어든 비율, 집계의 총 합계, 예외 처리, 수정된 쿼리의 결과 등을 확인했습니다.

테스트 과정은 개발 환경에 test DB를 만들고 그곳에서 집계 테이블을 테스트 한 후 개발 서버에 적용하고 개발 서버에서 확인이 완료 되면 운영 서버에서 적용하면서 트리거 테스트를 했습니다.

전체적인 과정은 다음과 같았습니다.

  1. 이슈 생성
    • 기존 API 문제점, 히스토리, 할 일을 정리
  2. 쿼리 작업
    • 테이블 생성
    • 트리거 생성
    • 상황별 확인
  3. 테스트
    • 트리거 동작 확인
    • 집계로 인해 줄어드는 데이터 수치 확인
    • 집계 테이블로 수정된 쿼리, 기존 쿼리 수치, 성능 확인
  4. 적용
    • test DB에서 테스트
    • 개발 서버 적용
    • 운영 서버 적용
    • 운영 서버 트리거가 잘 되는지 확인

집계 테이블을 이용하니 확실히 성능이 향상되었지만 다른 고민이 생겼습니다.

과제

다행히도 이번에 만든 트리거는 로그 테이블의 insert에만 작동 하지만 트리거에 트리거를 걸거나 복잡한 트리거를 만드는 일은 성능에 큰 영향을 끼침니다.

이런 이슈가 있을 때 마다 집계 테이블을 만드는게 해결 방법은 아닌 것 같아서 몇 가지 고민을 해봤습니다.

  1. 로그 테이블의 아카이브 정책을 만들면?
    • 좋은 방법이지만 이전 데이터의 롤백 방법이나 아카이브 정책에 대한 논의가 필요합니다.
  2. 테이블 분할
    • 위와 마찬가지로 롤백에 대한 고민이 필요하고 오로라 DB에서도 가능한지 찾아봐야 됬습니다.
  3. LIMIT, OFFSET
    • 사수님이 말하기를 이미 계시판등의 개발에서 이런 이슈가 오랫 동안 고민되었고 LIMIT과 OFFSET으로 많이 해결하고 있다고 하셨습니다.
  4. 그냥 트리거를 제대로 쓰면 문제 없다.
    • 운영 DB의 성능은 생각보다 좋습니다. 트리거를 이상하게 쓰면 안 좋지만 잘 쓰면 상관 없습니다.

후일담: 끝인 줄 알았나?

이렇게 쿼리 최적화가 끝나는줄 알았으나… 언제나 그렇듯 몇 가지 버그가 나와버렸습니다.

배포한지 꽤 지나서대시보드의 수치가 조금 이상하단 말을 들었습니다. 쿼리 최적화를 하면서 복잡한 쿼리를 기존에 있던 집계 테이블로 수정했습니다. 근데 문제는 그 집계 테이블은 다른 화면을 위해 임시로 만든 테이블이라 일부 컬럼을 제외하고는 쓰면 안 된다고 하더군요.

언젠가 한 번 그런 설명을 듣긴 했는데… 너무 예전 일이라 잊고 있었습니다. 설상가상으로 이런 주의사항을 언급하는 사내 문서도 없고 ERD도 업데이트가 필요한 상황입니다.

새로운 헬게이트가 열렸군요.

문서화와 기존 문서의 업데이트는 꾸준히 해야됩니다. ㅠㅠ