슬로우 쿼리 튜닝하기

이전에 개인 노션에 정리한 내용을 옮겼습니다

현재 서비스의 특성상, 고객사 업무 특성에 따라 특정날 인입량이 급증할때가 있다.
인입량이 급증한 날 평상시에는 몰랐고 발견되지 않았던 부분에서 발생한 장애에 대해 원인을 분석하고 해결한 방법을 기록한다.

문제

인입량이 몰리면서 서비스가 급격하게 느려지는 현상이 발생했고, 로그를 파악해보니 커넥션 에러가 계속 발생하고 있었다.
발생하는 서비스 로직을 파악해보고, 현재 디비에서 잡아먹고 있는 프로세스(show full processlist;) 로 확인 결과, 특정 쿼리에서 계속 커넥션을 물고 있었고, 확인해보니 특정 쿼리에서 계속 커넥션을 물고 있었다.

원인

• 발생 쿼리

explain select
  chatroom0_.`id`                as id1_17_,
  ....
  ....
from `chat_room` chatroom0_
where chatroom0_.`customer_id` = ***
      and (chatroom0_.`status` in ('....'))
order by chatroom0_.`create_date` asc
limit 1;

• 해당 메소드

  public Optional<ChatRoom> searchLiveChatRoom(Customer customer) {
          return chatRoomRepository.findFirstByCustomerAndStatusInOrderByCreateDate(customer, liveChatRoomStatus);
      }

위와 같은 로직에서 발생하였고, explain 결과 인덱싱을 타지 않고 full scan으로 돌고있었다.
상담중 방을 뽑아내는 쿼리인데, 실제 brand_id, customer_id, status, create_date 컬럼을 혼합한 인덱스가 있었으나 타지 않고있었다.
→ 쿼리를 치는 실제 로직은 brand 없이 customer를 기준으로 상담방을 뽑아내고 있는데, 실제 걸려있는 인덱싱조건과 맞지않아 커버링인덱스가 되지 않고 fullscan.

해결

인덱싱 조건을 타도록 검색조건에 brand_id를 추가함.

public Optional<ChatRoom> searchLiveChatRoom(Customer customer) {

                if (Objects.nonNull(brand)) {
                // 인입한 고객의 브랜드를 뽑아 브랜드를 조건에 추가한다
                    return chatRoomRepository.findFirstByBrandAndCustomerAndStatusInOrderByCreateDate(brand, customer, liveChatRoomStatus);										
        }

    }

• exlpain 결과 의도된대로 커버링 인덱스가 되었고, 커넥션이 몰려 5s~ 8s이상 걸리던 쿼리가
  0.015~0.017s로 로직 개선전 보다 약 40배 개선된것을 확인했다.

회고

1. 별거 아닌 쿼리라고 생각해도, 늘 쿼리를 확인하고 실행계획을 확인하여 좋은 쿼리인지 확인하자
2. 무작정 인덱스를 거는것이 아니라, 기존에 걸려있던 인덱스를 확인하고 커버링 인덱스 할 수 있는 방향으로 개선해보자.
3. 기존 슬로우 쿼리에 대해 묵인하지말고 스스로 찾아 개선하자.
   (폭탄은 원래 조용한법..)