동시성 보장을 위해 비관적 락을 적용했지만, 부하 테스트에서 TPS가 1.3으로 너무 낮게 측정되는 문제를 확인했다.
락 자체를 포기하지 않은 상태에서(=비관적 락 유지) 어디에서 병목이 생기고 무엇을 줄이면 성능을 끌어올릴 수 있는지 검증·개선해 보고자 이 글을 정리했다.
아래는 이벤트를 응모할 때 발생하는 SQL문이다.
부하 테스트를 진행하며 성능이 너무 느리게 나와 가장 먼저 드는 의문이 DB가 잘못설계되었나? 쿼리를 개선할 수 있는 부분이 있지 않나해서 요청할 때 실행되는 SQL을 먼저 뜯어보았다.
2025-08-29T16:48:10.094+09:00 DEBUG 11684 --- [nio-8081-exec-2] org.hibernate.SQL :
select
m1_0.member_id,
m1_0.member_birthday,
m1_0.member_created_at,
m1_0.member_deleted_at,
m1_0.member_email,
m1_0.host_biz_address,
m1_0.host_biz_bank,
m1_0.host_biz_bank_number,
m1_0.host_biz_ceo,
m1_0.host_biz_depositor,
m1_0.host_biz_ecommerce_registration_number,
m1_0.host_biz_name,
m1_0.host_biz_number,
m1_0.member_img,
m1_0.member_role,
m1_0.member_nickname,
m1_0.member_pw,
m1_0.member_number,
m1_0.member_point_balance,
m1_0.member_updated_at
from
member m1_0
where
m1_0.member_email=?
2025-08-29T16:48:10.117+09:00 INFO 11684 --- [nio-8081-exec-2] c.p.t.global.security.filter.JwtFilter : Authentication: UsernamePasswordAuthenticationToken [Principal=com.profect.tickle.global.security.util.principal.CustomUserDetails@265291ab, Credentials=[PROTECTED], Authenticated=true, Details=null, Granted Authorities=[MEMBER]]
2025-08-29T16:48:10.172+09:00 DEBUG 11684 --- [nio-8081-exec-2] org.hibernate.SQL :
select
e1_0.event_id,
e1_0.event_accrued,
e1_0.coupon_id,
e1_0.event_created_at,
e1_0.event_goal_price,
e1_0.event_name,
e1_0.event_per_price,
e1_0.status_id,
e1_0.event_type,
e1_0.event_updated_at
from
event e1_0
where
e1_0.event_id=? for no key update
2025-08-29T16:48:10.191+09:00 DEBUG 11684 --- [nio-8081-exec-2] org.hibernate.SQL :
select
s1_0.seat_id,
s1_0.seat_created_at,
s1_0.event_id,
s1_0.member_id,
s1_0.performance_id,
s1_0.preempted_at,
s1_0.preempted_until,
s1_0.preemption_token,
s1_0.reservation_id,
s1_0.seat_code,
s1_0.seat_grade,
s1_0.seat_number,
s1_0.seat_price,
s1_0.status_id
from
seat s1_0
where
s1_0.event_id=?
2025-08-29T16:48:10.244+09:00 DEBUG 11684 --- [nio-8081-exec-2] org.hibernate.SQL :
select
m1_0.member_id,
m1_0.member_birthday,
m1_0.member_created_at,
m1_0.member_deleted_at,
m1_0.member_email,
m1_0.host_biz_address,
m1_0.host_biz_bank,
m1_0.host_biz_bank_number,
m1_0.host_biz_ceo,
m1_0.host_biz_depositor,
m1_0.host_biz_ecommerce_registration_number,
m1_0.host_biz_name,
m1_0.host_biz_number,
m1_0.member_img,
m1_0.member_role,
m1_0.member_nickname,
m1_0.member_pw,
m1_0.member_number,
m1_0.member_point_balance,
m1_0.member_updated_at
from
member m1_0
where
m1_0.member_id=?
2025-08-29T16:48:10.255+09:00 DEBUG 11684 --- [nio-8081-exec-2] org.hibernate.SQL :
select
p1_0.member_id,
p1_0.point_id,
p1_0.point_created_at,
p1_0.point_credit,
p1_0.point_order_id,
p1_0.point_target
from
point p1_0
where
p1_0.member_id=?
2025-08-29T16:48:10.291+09:00 DEBUG 11684 --- [nio-8081-exec-2] org.hibernate.SQL :
insert
into
point
(point_created_at, point_credit, member_id, point_order_id, point_target)
values
(?, ?, ?, ?, ?)
2025-08-29T16:48:10.331+09:00 DEBUG 11684 --- [nio-8081-exec-2] org.hibernate.SQL :
update
event
set
event_accrued=?,
coupon_id=?,
event_created_at=?,
event_goal_price=?,
event_name=?,
event_per_price=?,
status_id=?,
event_type=?,
event_updated_at=?
where
event_id=?
2025-08-29T16:48:10.343+09:00 DEBUG 11684 --- [nio-8081-exec-2] org.hibernate.SQL :
update
member
set
member_birthday=?,
member_deleted_at=?,
member_email=?,
host_biz_address=?,
host_biz_bank=?,
host_biz_bank_number=?,
host_biz_ceo=?,
host_biz_depositor=?,
host_biz_ecommerce_registration_number=?,
host_biz_name=?,
host_biz_number=?,
member_img=?,
member_role=?,
member_nickname=?,
member_pw=?,
member_number=?,
member_point_balance=?,
member_updated_at=?
where
member_id=?
2025-08-29T16:48:33.127+09:00 INFO 11684 --- [MessageBroker-2] o.s.w.s.c.WebSocketMessageBrokerStats : WebSocketSession[0 current WS(0)-HttpStream(0)-HttpPoll(0), 0 total, 0 closed abnormally (0 connect failure, 0 send limit, 0 transport error)], stompSubProtocol[processed CONNECT(0)-CONNECTED(0)-DISCONNECT(0)], stompBrokerRelay[null], inboundChannel[pool size = 0, active threads = 0, queued tasks = 0, completed tasks = 0], outboundChannel[pool size = 0, active threads = 0, queued tasks = 0, completed tasks = 0], sockJsScheduler[pool size = 4, active threads = 1, queued tasks = 2, completed tasks = 1]
위의 이벤트 응모 요청 시 도출되는 SQL을 확인해보면 Seat 테이블이 조회되는 것을 확인할 수 있다. 이벤트 응모할 때 해당 좌석에 대한 정보가 필요없는데 왜 조회하는걸까?
현재 Event와 연관관계가 설정되어있는 Seat에 Lazy설정이 아닌 디폴트 설정이 되어있었다.
만약 Event에 응모되면 Seat정보가 필요하기때문에, FetchType Lazy로 명시하여 필요할 때만 조회하도록 하자.
요청을 한 번 다시 보내보았다. Seat가 조회되지않는 것을 확인할 수 있었다.