회사에서 제가 메인테인하고 있는 서비스 중, 음원에 관한 기능이 존재합니다.
유저가 음원 콘텐츠를 실행하면 데이터베이스에 재생 통계를 포함한 메타 데이터를 기록하고 있는데요, 런칭 6개월 동안 1200만개의 행이 쌓여있습니다.
크리에이터 분들과 사내 운영팀에 제공할 신규 통계들이 기획되고있었는데, 현 스키마로는 설계 상 제공하기 어려웠고, 해당 테이블에 신규 컬럼과 인덱스가 만들어져야한다는 판단이 들어, 몇단계에 거쳐 테이블을 개선하기로 의사결정 되었습니다.
신규 컬럼과 인덱스를 추가함과 동시에, 기존에 존재하던 데이터들에게도 적절한 값을 만들어주어야했기에, 아래와 같은 순서로 처리하였습니다.
- nullable 컬럼 및 인덱스 추가
- 기존 데이터들에게 적절한 값을 업데이트 해주는 스크립트 실행
- 컬럼 non-nullable 하게 수정
1200만개의 행을 업데이트 해야한다.
Batch Update 기능을 만들어서, 모든 행을 업데이트 할 때까지 반복 실행시키는 스크립트를 만들었습니다. 단일 실행 당 업데이트시킬 batchSize 와, 다음 실행 전까지 휴식시간을 동적으로 컨트롤 할 수 있습니다.
제가 이번 포스팅에서 다룰 것은 스크립트에서 사용한 Batch Update 쿼리 최적화의 대한 내용입니다.
먼저 처음에 작성했던 SQL 문을 보여드리겠습니다.
UPDATE play_history AS history
INNER JOIN (
SELECT h.userId, sound.id AS soundId, h.createdAt, IF(sub.userId IS NOT NULL, 1, 0) AS isSubscriber, sound.channelId
FROM play_history AS h
INNER JOIN sound AS sound ON h.soundId = s.id
LEFT JOIN subscription AS sub ON sound.channelId = sub.channelId AND h.userId = sub.userId -- AND sub.createdAt <= h.createdAt
WHERE h.channelId IS NULL
LIMIT ${batchSize}
) AS subquery ON
history.userId = subquery.userId
AND history.soundId = subquery.soundId
AND history.createdAt = subquery.createdAt
SET history.isSubscriber = subquery.isSubscriber, history.channelId = subquery.channelId;요약하면 두가지 컬럼을 추가했고, 기존 데이터들의 두 컬럼을 채워주는 쿼리문입니다.
두 컬럼은 음원 소유자 (channelId) 정보와 재생한 사람이 구독자인지 (isSubscriber) 에 대한 정보를 담습니다.
UPDATE 하는 부분은 실행계획이 없습니다. 그저 차례로 메모리로 부터 값을 참조하여 값을 바꿔줄 뿐입니다.
그렇기에 위와 같은 Batch Update 의 경우 가장 중요한 것은 "조회시간" 입니다. 무조건 최적의 인덱스를 사용해야만 했습니다.
저는 처음에 아래와 같은 실행계획을 기대하고 위 쿼리를 작성했습니다.
- channelId 가 null 이면, 업데이트가 필요함을 의미한다.
- 따라서, channelId 인덱스를 사용하여 이미 업데이트 된 행들을 제외한다.
- 다른 테이블을 조인하여 필요한 정보 (channelId, isSubscriber) 를 가져온다.
- 업데이트한다.
하지만 실제로 실행계획을 확인했을 때 제일 중요한 channelId 인덱스 (1번) 을 활용하지 않고, 아래와 같은 형태로 실행계획을 보여주었습니다.
- key: idx_soundId
- extra: using index, using where
엉뚱한 soundId 인덱스를 이용하던 탓에, DB 는 channelId 가 null 인 행들을 필터링하기 위해 using where 를 사용할 심산이였습니다.
해당 실행계획은 당연합니다. 메타데이터를 가져오기 위해 sound 테이블을 조인했어야했고, 해당 조인을 위해 soundId 인덱스를 활용할 수 밖에 없었습니다.
위 쿼리문을 포함한 스크립트를 실행하니 아래와 같이 엄청난 리소스를 소비하였습니다.
6500 개의 행을 업데이트할때마다 43초가 걸렸습니다. 여기서 재미있는점은, 아래와 같이 batch size 를 두배로 늘려도 지표상의 차이가 없다는 점이었습니다.
이것은 무엇을 의미할까요?
동일한 인덱스에서 동일한 실행계획으로 필터링과 정렬을 처리한 뒤 LIMIT 으로 상위 N 개의 데이터를 자르는 동작에서, N 값을 늘리는것은 CPU 나 조회시간에 큰 변화를 주지 않습니다. (물론 매우 크게 할 경우에는 디스크에서 읽어오는 시간 자체가 늘어날 수는 있을것입니다.)
즉 batch size 에 따라 선형적으로 영향을 받는 것은 UPDATE 인데요, batch size 를 두배가량 늘렸음에도 실행시간이 늘어나지 않았다, 이말은 현재 40초가량 실행되고 있는 시간의 대부분은 "조회시간" 임을 뜻합니다. 더불어 UPDATE 는 거의 몇초 이내에 이루어질 것이라 유추할 수 있습니다.
그렇기 때문에 batch update 에서 가장 중요한 것은 "조회시간"이고, 조회할 때 반드시 최적의 인덱스를 활용해야합니다.
해당 스크립트가 잠시 실행될 동안의 Datadog APM 의 Latency 지표입니다.
위 지표는 P99 LATENCY 이고 P50 지표는 정상이였기에 대부분의 유저에게는 이상이 없었지만, 몇 분 간 서비스 이용에 불편함을 느끼셨을 일부 사용자 분들께 죄송한 마음을 전합니다
옵티마이저에게 인덱스에 대한 힌트를 제공해주기
위 쿼리의 문제점이 무엇이였을지 가만히 생각해보았습니다.
- channelId 인덱스를 사용못한 이유는, sound 테이블을 조인했기 때문입니다.
- 업데이트에 필요한 메타데이터를 가져오기 위해 조인을 피할 수는 없습니다.
- 스코프 뎁스를 늘려서 channelId 필터링을 먼저 진행하려고 해도, 쿼리의 복잡성이 늘어나 예기치 못한 동작을 발생시킬 수 있기에 조심스러웠습니다.
이때 이전에 다른 작업에서 에서 이용했던 Mysql 의 Lateral 조인이 떠올랐습니다.
MYSQL 8.0 Lateral Derived Table 공식문서
8.0 버전에서 출시된 기능으로, 오라클이나 Postgresql 를 사용하셨던 분들에게는 익숙한 기능일 것입니다.
아래는 Lateral Derived Table 을 이용한 개선안입니다.
UPDATE play_history AS history
INNER JOIN (
SELECT h.userId, h.soundId, h.createdAt
FROM play_history AS h
WHERE h.channelId IS NULL
LIMIT ${batchSize}
) AS subquery ON
history.userId = subquery.userId
AND history.soundId = subquery.soundId
AND history.createdAt = subquery.createdAt
INNER JOIN LATERAL (
SELECT id, channelId
FROM sound
WHERE id = subquery.soundId
) AS sound
LEFT JOIN (
SELECT userId, channelId
FROM subscription
) AS sub ON sub.userId = history.userId AND sub.channelId = sound.channelId
SET history.isSubscriber = IF(sub.userId IS NOT NULL, 1, 0), history.channelId = sound.channelId;위 쿼리는 처음에 기대했던 대로 channelId 인덱스를 사용합니다.
- key: idx_channelId
- extra: using index
왜 동일한 쿼리문에서 LATERAL 을 넣었는데, 실행계획이 달라졌을까요?
일반 조인문과 LATERAL 조인은 아래와 같은 동작방식의 차이가 있습니다.
- A INNER JOIN B
ON 조건에 맞는 A 와 B 의 각 행을 가져와 병합한다.
- A INNER JOIN LATERAL B
A 테이블의 각 행 마다 반복해서 B 서브쿼리를 실행한다.
그냥 조인을 했을 때는 A 와 B 를 동시에 읽어와야하기때문에 옵티마이저는 조인시킬 테이블 (B) 와 연관된 인덱스를 활용할 수 밖에 없습니다.
하지만 LATERAL 조인을 사용하면 A 테이블과 B 테이블을 동시에 처리하는 것이 아닌, A 를 먼저 읽어들이고, 각 행마다 반복적으로 B 테이블에서 가져오기때문에, A 테이블을 읽어오는 과정에서 옵티마이저가 B 를 신경쓸 필요가 없음을 의미합니다. 저의 경우 A 테이블에서 where channelId is null 문장이 있었기에, 비로소 channelId 인덱스를 사용하게 되었던 것입니다.
사실 LATERAL 을 사용하는 컨텍스트는, 보통 스코프 외부의 데이터를 참조하기 위하여 사용됩니다.
A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.공식문서에서도 그렇게 이야기하고있습니다. 이 역시, 스코프 외부의 데이터를 먼저 읽었기에 사용가능한 것입니다.
하지만 이번 포스팅에서의 사례와 같이 옵티마이저에게 적절한 힌트를 제공하는 방식으로도 활용이 가능했습니다.
개선된 쿼리로 아래와 같이 굉장히 빠르게 모든 데이터를 업데이트 할 수 있었습니다.
v 기존: 10초마다 6500개 업데이트 => 실행시간 40초 / CPU 70%
v 개선: 4초마다 15000개 업데이트 => 실행시간 3초 / CPU 40%
DBMS 가 내부적으로 어떻게 동작할지는 DB 종류에따라, 버전에 따라, 데이터 스키마에 따라 다르기에, 데이터베이스가 제가 기대하는 방식으로 동작할 수 있는 쿼리 환경을 조성하는 것이 중요한 것 같습니다.
그러한 환경을 조성하는데에 필요한 도구들을 파악하기 위해서 공식문서를 자주 참고하는 것이 도움이 되는 것 같습니다. 저의 경우 예전에 API 서버를 작업하며 쿼리를 단순하고 예측하기 쉽게 만들기 위한 방법을 고민하다 공식문서를 참고하여 LATERAL 조인을 사용했던 기억이 이번 개선작업에 도움을 주었습니다.
많은 데이터를 처리할 때에 고려해야할 점들을 배울 수 있었던 시간이였던 것 같습니다.
더 효과적으로 처리할 수 있는 아이디어가 있으신 분들은 댓글로 남겨주시면 많이 배울 수 있을 것 같습니다 : )
내용 피드백 및 질문 댓글은 언제나 환영입니다!!
