UPDATE 문을 96%나 최적화했다고?

6 min readJul 14, 2024

TL;DR

explain을 통해 쿼리 수행시 어떤 인덱스를 사용하는지 확인해보자.
OR 연산은 Index를 타지 않는다.
대용량 테이블을 다룰 때, transaction과 promise.all이 정말 필요한지 다시 한 번 생각해보자.

PR에서 CI가 실패했다

FATAL ERROR: Ineffective mark-compacts near heap limit Allocation failed - JavaScript heap out of memory

말 그대로 자바스크립트 Heap 메모리가 부족해서 발생하는 에러다.

해당 프로젝트에 현재 메모리가 어느정도 할당되어 있는지 확인해보았다.

node -e 'console.log(v8.getHeapStatistics().heap_size_limit/(1024*1024))'

찾아본 대부분의 자료에서는 export NODE_OPTIONS=--max_old_space_size=800 로 메모리 용량을 증가하는 방법을 알려주었지만, 다음과 같은 이유로 적용하지 않았다.

다른 프로젝트도 동일한 메모리로 할당되어 있다.
프로젝트를 build할 때마다 용량을 증가해줘야하는 귀차니즘.

무엇보다도 위 방법은 메모리 누수를 해결하는 근본적인 방법이 아니다.

마침 테스트 환경에서 out of memory 에러 발생 지점이 이전부터 쿼리 수행 속도가 현저히 느린 지점과 일치했다.

이로써 전부터 신경쓰였던 쿼리의 최적화를 진행했다.

해당 쿼리는 A테이블에서 데이터를 조회하고 B 테이블로 데이터를 이동시킨다.

A 테이블에서 이동시킬 데이터가 존재하는지 확인할 때(READ) 5s 정도 걸린다.
해당 데이터를 이동시킬 때(UPDATE), 예를 들어 데이터 10개(16 byte)를 이동하는데 35.30 ~ 38.27s, 즉 30s 중반 정도 소요된다.

참고로 테스트를 진행하는 환경은 다음과 같다.

mysql workbench: 8.0.31
mysql server: 8.0.28
aurora mysql: 8.0.21
A 테이블 스키마: 컬럼 40개 후반, 인덱스 10개 중반, 파티셔닝 0
B 테이블 스키마: 컬럼 60개 후반, 인덱스 5개, 파티셔닝 0
두 테이블의 row 수는 대략 1000만+a개 정도 된다.

다음은 시도했지만 별 차이가 없거나 기존보다 오래 걸려 포기했던 방법이다.

OR 조건 분리.
transaction 제거 및 UPDATE 쿼리 병렬 처리.
Sequelize의 literal을 사용하여 조건에 따라 업데이트했을 때: 35.45s
ORM이 실제로 어떻게 query되는지 궁금하여 logging: true 추가: 2m 중반

IndexHint를 걸어보자

IndexHint는 데이터베이스가 index를 직접 선택하는 것이 아닌 사용자가 직접 index를 설정하는 것을 의미한다.

결론적으로 차이는 커녕 오히려 인덱스를 추가할 때 10s 정도 늘어났다.

또한 테이블에 인덱스가 많을수록 create 및 insert 작업의 성능이 낮아진다고 한다.

즉, 무조건 Index를 추가하는 것보다 상황에 따라 사용 유무를 결정하는 것이다.

만약 조회가 중요한 경우에는 인덱스를 생성해서 사용하는게 좋다.

하지만 대상 쿼리에는 UPDATE 구문도 포함되어 있어 일단 인덱스는 건드리지 않았다.

대용량 테이블에서 transaction은 성능 저하 원인이다

현재 느린 쿼리에는 전부 transaction이 걸려있다.

해당 로직에 transaction이 꼭 필요한가?에 대해 생각해보았을 때의 답은 No였다.

일단 전체적으로 try ~catch 문이 걸려있으면서 실패 케이스가 예상가지 않는다.
row가 1,400만개 있는 cons 테이블은 대용량 테이블이라고 정의할 수 있는데, 대용량 테이블에서 transaction을 거는 것은 오히려 성능 저하로 이어진다고 한다.
데이터 10개를 대상으로 테스트를 진행했을 때 transaction 유무에 따라 10s 정도 차이가 난다면, 없는 것이 낫다고 생각했다.

해당 작업을 진행한지 n개월이 지난 지금, 다시 생각해보면 bulkCreate를 시도해봤는지 기억이 안난다..

OR 연산이 성능 저하를 발생시킬 수 있다

OR 연산은 하나의 조건만 충족되면 실행하는 반면, DB 엔진은 모든 조건을 검사(Full scan)한다.

그 것도 Index를 타지 않고.

만약 Index를 태우려면, OR 연산이 수행된 횟수 + 1번에 걸쳐 find 연산을 진행해야 한다.

다중 Index를 만드는 방법도 있지만 해당 로직에서 OR 연산이 상당 비중을 차지하는 것은 아니므로 다중 인덱스 부분은 고려하지 않았다.

`explain analyze`로 언제 오래 걸리는지 살펴보자

mysql 8.0.18부터 hash join과 explain analyze를 지원하면서 actual action을 확인할 수 있다.

explain analyze를 실행하면 table 형식으로 결과를 반환한다.

cost는 데이터베이스가 이 쿼리를 실행하는 데 필요한 예상 (논리적) 비용을 나타냅니다. 높은 비용은 성능 저하를 나타낼 수 있다.
actual time은 실제로 이 쿼리가 실행된 데 걸린 시간을 나타낸다.
rows는 결과 집합에 반환된 행의 수를 나타낸다.

역시 필터링 작업과 테이블 스캔이 많은 비용과 시간을 소비한다.

더불어, 쿼리 실행 중 not executable by iterator executor가 발생했다.

여기에서 NL, BNL, Hash Join 개념이 나왔는데, 에러를 이해하는데 (그 당시에는) 별 도움이 안됐다.

Nested Loop: Driving 테이블의 건수만큼 Inner 테이블 스캔(index full scan)을 하므로 성능 저하의 원인이 된다.
Block Nested Loop: 프로세스 내 별도의 버퍼(join buffer)에 drinving 테이블의 레코드를 저장한 후, inner table을 스캔하면서 join buffer를 탐색한다. NL의 한계를 개선하기 위해 고안된 방법이지만 근본적인 해결책이 아니다.
Hash Join: MySQL 8.0.18부터 MySQL은 각 join에 동일 join 조건이 있고 이 쿼리와 같이 join 조건에 적용할 수 있는 인덱스가 없는 모든 쿼리에 대해 해시 조인을 사용한다.

참고로 explain format=tree 와 explain analyze 는 같다.

explain으로 어떤 인덱스를 사용하는지 살펴보자

ChatGPT와 대화하다, 처음에 추가했다 취소했었던, A 테이블의 한 컬럼에 인덱스를 걸으라고 했다.

반신반의하며 인덱스를 추가하고 실행했더니, 860ms 대에서 1s 중반 대로 실행 시간이 현저히 줄어들었다.

추가로, 처음에는 별 차이 없었던 Promise.all 유무에 대해서도 테스트를 진행했다.

promise.all가 없을 때는 거의 900ms ~ 1s 초반으로 promise.all가 있을 때보다 조금 빨라서 Promise.all 는 지웠다.

사실 따지고 보면 메모리 누수와 쿼리 최적화는 직접적 연관이 없다.

또한 해당 테이블에는 이미 많은 인덱스가 걸려있어 내가 제시한 방안은 거절되었다.

그럼에도 많은 것을 배울 수 있었는데,

결론적으로 첫번째 시도인 인덱스 추가가 해결하는데 상당한 영향을 미쳤다.

나중에 찾아본 바로는, 인덱스된 열(column) 내에서 서로 다른 고유 값(distinct values)의 수를 의미하는 Cardinality가 인덱스를 설정하는 중요 지표라고 한다.

덕분에 explain이나 explain analyze를 통한 쿼리 행동에 대해 분석해보면서, 타 DBMS에 비해 비교적 늦게 적용된 hash join에 대해 공부할 수 있었다.