쿼리 성능 개선 (MySQL Optimizer 실행 계획 분석)

1. 문제 설명

• Jmeter를 이용하여 예약 가능한 시간 조회 성능을 테스트하였습니다.
  • Jmeter를 통해서 서비스의 트래픽 가용성을 확인하였습니다.

시나리오

하루 이용자 수 9000명

하루 트래픽 180만 

⇒ 2분 동안 300명 사용자가 총 60000의 트래픽 발생시킨다.

• Number of Threads (users) : 300
• Ramp-up period (seconds) : 120초
• Loop Count : 200

• 평균 TPS : 약 240
• 실패율: 0.1%

분석

• DB 서버의 CPU Utilization이 100%에 가깝습니다.
• HikariCP Connection Timeout이 발생하였습니다.
• 전체 트래픽을 처리하는데 4:14초가 소요되었습니다.

예약 시스템 특성상 예약 가능한 시간 조회 트랜잭션이 빈번하게 발생하기 때문에 이러한 문제를 해결하기 위해 쿼리 성능을 향상시켜야 한다는 결론에 도달하였습니다.

MySQL Optimizer 실행 계획을 분석을 기반으로 쿼리 튜닝을 진행함으로써 서비스 성능 개선을 시도하였습니다.

 

2. 성능 개선 전 분석

DesignerRepository.java

@Query("select distinct d from Designer d join fetch d.reservationTimes r "
    + "where d.hairshop.id = :hairshopId and r.date = :date and r.reserved = false")
List<Designer> findDesignerFetchJoinByHairshopIdAndDate(@Param("hairshopId") Long hairshopId,
    @Param("date") LocalDate date);

• 헤어샵에 속한 designer data와 해당 날짜의 예약 안된 reservationTime data를 Fetch Join을 통해 같이 가져오는 쿼리

SQL

select
        designer0_.id as id1_0_0_,
        reservatio1_.id as id1_4_1_,
        designer0_.created_at as created_2_0_0_,
        designer0_.hairshop_id as hairshop8_0_0_,
        designer0_.profile_img as profile_3_0_0_,
        designer0_.introduction as introduc4_0_0_,
        designer0_.name as name5_0_0_,
        designer0_.position as position6_0_0_,
        designer0_.updated_at as updated_7_0_0_,
        reservatio1_.date as date2_4_1_,
        reservatio1_.designer_id as designer5_4_1_,
        reservatio1_.hairshop_id as hairshop6_4_1_,
        reservatio1_.reserved as reserved3_4_1_,
        reservatio1_.time as time4_4_1_,
        reservatio1_.designer_id as designer5_4_0__,
        reservatio1_.id as id1_4_0__ 
    from
        designer designer0_ 
    inner join
        reservation_time reservatio1_ 
            on designer0_.id=reservatio1_.designer_id 
    where
        designer0_.hairshop_id = ?
        and reservatio1_.date= ?
        and reservatio1_.reserved=0;

Index

show index from designer;
show index from reservation_time;

Optimizer 실행 계획

• designer hairshopId를 통해서 조회하기 위해 index를 사용하는 것을 확인하였습니다.
• designer와 속한 reservationTime list를 조회하기 위해 index를 사용하는 것을 확인하였습니다.

 

3. Index 대상 Column 선정

카디널리티 (Cardinality)

특정 데이터 집합의 유니크(Unique)한 값의 개수

• 한 컬럼이 갖고 있는 값의 상대적인 중복 정도
• Cardinality가 높을수록 컬럼을 인덱싱하는 것이 검색 성능에 유리합니다. (중복도가 낮아진다)
  • 중복도가 낮은 컬럼이 인덱스로 설정하기 좋은 컬럼이다.
  • Index를 통해 데이터를 더 많이 필터링할 수 있기 때문이다.

INSERT, DELETE, UPDATE 이 자주 일어나지 않는 column

• UPDATE
  • 기존의 인덱스를 사용하지 않음 처리, 갱신된 데이터에 대한 인덱스 추가합니다.
  • 데이터의 수정이 많다면 성능 저하의 원인이 될 수 있습니다.

결론

• ReservationTime Table에서 designer_id, date, reserved 순으로 중복도가 낮습니다.
  • designer_id Column이 카디널리티가 가장 높다.
  • reserved Column이 카디널리티가 가장 낮다.
• ReservationTime Table의 reserved Column 은 예약 성공 시에 0에서 1로 Update 됩니다.
  • reserved Column은 데이터의 수정이 잦다고 생각되었습니다.
• designer_id, date Column를 순서로 Index을 걸어야 한다고 판단하였습니다.

 

4. Query Index

Index

@Entity
@Table(name = "reservation_time", indexes = @Index(name = "i_reservationTime", columnList = "designer_id, date"))
@Getter
@NoArgsConstructor
public class ReservationTime { ...  }

• designer_id, date Column에 Index를 걸어주었습니다.

Optimizer 실행계획

• ReservationTime Table 조회 시에 i_reservationTime Index를 사용한 것을 확인할 수 있습니다.
• i_reservationTime Index 사용 시에 designer_id 그리고 date를 이용해서 filter를 거치기 때문에 오직 47 rows만 조회됩니다.
  • ReservationTime 47 rows와 designer 5 rows Join이 일어나기 때문에 성능이 더 향상되었을 것이라고 기대됩니다.

 

5. 성능 개선 후 결과

• Number of Threads (users) : 300
• Ramp-up period (seconds) : 120초
• Loop Count : 200

• 평균 TPS : 약 490
• 실패율: 0%

결과 분석

• 개선 전 평균 TPS 240에서 개선 후 평균 TPS 490으로 개선되었습니다.
• 전체 트래픽을 처리하는 시간이 4분 14초에서 2분 02초로 개선되었습니다.
• 개선 전 HikariCP Connection Timeout이 발생하였지만 개선 후 실패율이 0%가 되었습니다.