개요
사내 검색 도메인에서 MariaDB에 저장된 raw 데이터를 ElasticSearch에 색인하기 위해 사용되는 쿼리에 대한 성능 개선 사례를 살펴보겠습니다.
기존 쿼리의 소요시간은 약 90초로, 하루에 한 번씩 스케줄링되어 실행되었습니다. 이 정도 소요 시간은 문제가 되지 않았지만, raw 데이터의 크기가 점점 증가하면서 동일한 시간대에 배치 프로세스 수행 시 메모리 및 디스크 부족 현상이 발생하게 되었습니다.
이에 따라 쿼리 튜닝이 필요한 상황이 되었습니다.
문제 분석
먼저, 기존 쿼리를 살펴보겠습니다.
SELECT
m.maker, b.brand, p.prod_name, c.save_count, p.prod_c
FROM
product p
INNER JOIN category c ON (p.prod_c = c.prod_c)
INNER JOIN maker m ON (p.maker_c = m.maker_c)
LEFT JOIN brand b ON (p.brand_c = b.brand_c)
LEFT JOIN bundle pb ON (p.prod_c = pb.prod_c)
WHERE
...
ORDER BY c.save_count DESC
사내에서 사용하는 쿼리이기 때문에, 일부 수정 후 여타 조건은 생략했습니다.
실제 쿼리 수행시간이 90초를 넘어가는 것을 알 수 있었습니다.
문제를 찾기위해 실행계획을 분석해보겠습니다.
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | m | index | PRIMARY | maker | 102 | 77701 | Using index; Using temporary; Using filesort |
|
1 | SIMPLE | p | ref | PRIMARY, maker_c_idx, idxinput_d |
maker_c_idx | 4 | DB.maker_c | 53 | Using where |
1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | DB.brand_c | 1 | Using where |
1 | SIMPLE | pb | eq_ref | PRIMARY | PRIMARY | 4 | DB.prod_c | 1 | Using where |
1 | SIMPLE | c | eq_ref | idxUCate1 | idxUCate1 | 4 | DB.prod_c | 1 |
쿼리 분석 결과, 첫 번째 row에서 "Using temporary", "Using filesort"가 발생하고 있음을 확인할 수 있습니다.
File Sort를 사용하는 경우는 두 가지로 나눌 수 있습니다.
- 드라이빙 테이블만 정렬 ("Using filesort")
- 임시 테이블을 이용한 정렬 ("Using temporary", "Using filesort")
실행 계획을 보시면 두 번째 상황이 발생한 것을 알 수 있는데, 이는 쿼리 실행 중 임시 테이블이 사용되고, 별도의 정렬 작업이 수행되고 있음을 의미합니다.
일반적으로 ORDER BY, GROUP BY 절을 사용할 때 이러한 현상이 나타나며, 임시 테이블 사용은 성능 저하의 주요 원인이 될 수 있습니다.
특히 임시 테이블이 디스크에 생성되는 경우, 심각한 I/O 부하가 발생할 수 있습니다. 이번 쿼리의 경우 약 800만 건의 레코드를 반환하고 있어, 디스크 접근이 강제되면서 I/O 부하가 발생할 수밖에 없는 상황입니다.
Using Filesort는 쿼리 결과를 정렬할 때 인덱스를 사용하지 않고 별도의 정렬작업을 수행하는데, ORDER BY 절에서 많이 발생합니다.
가장 좋은 방법은 인덱스를 활용한 정렬이지만, 드라이빙 테이블만 사용해서 정렬하는 경우도 마찬가지로 임시 테이블을 이용하는 방법 보다는 성능이 좋습니다.
따라서 이번 쿼리의 성능 개선을 위해서는 임시 테이블 사용을 최소화하고, 인덱스 활용도를 높이는 등의 방법을 고려해볼 필요가 있습니다. 이를 통해 쿼리 실행 시간을 단축하고, 메모리 및 디스크 사용량을 줄일 수 있을 것입니다.
SHOW GLOBAL STATUS LIKE 'Sort_%';
해당 명령어를 사용해 쿼리 수행 후 디스크 접근이 발생했는지 확인해보겠습니다.
Sort_merge_passes 변수를 통해 디스크 접근 횟수를 알 수 있는데, 쿼리 수행 후에 value가 증가한 것을 보아 디스크 접근이 발생한 것을 알 수 있습니다.
문제의 원인 분석
해당 쿼리에서는 왜 임시 테이블을 만든 후에 정렬을 하는걸까요?
쿼리를 자세히 살펴보면, 드라이빙 테이블은 'prod' 테이블이지만 정렬은 'category' 테이블의 컬럼을 이용하여 수행하고 있습니다. 이로 인해 임시 테이블이 생성되고, 별도의 정렬 작업이 필요하게 된 것입니다.
드라이빙 테이블을 category로 변경한 후 쿼리의 실행계획을 확인해보겠습니다.
SELECT
m.maker, b.brand, p.prod_name, c.save_count, p.prod_c
FROM
category c
INNER JOIN prod p ON (p.prod_c = c.prod_c)
INNER JOIN maker m ON (p.maker_c = m.maker_c)
LEFT JOIN brand b ON (p.brand_c = b.brand_c)
LEFT JOIN bundle pb ON (p.prod_c = pb.prod_c)
WHERE
...
ORDER BY c.save_count DESC
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | m | index | PRIMARY | maker | 102 | 77701 | Using index; Using temporary; Using filesort |
|
1 | SIMPLE | p | ref | PRIMARY, maker_c_idx, idxinput_d |
maker_c_idx | 4 | DB.maker_c | 53 | Using where |
1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | DB.brand_c | 1 | Using where |
1 | SIMPLE | pb | eq_ref | PRIMARY | PRIMARY | 4 | DB.prod_c | 1 | Using where |
1 | SIMPLE | c | eq_ref | idxUCate1 | idxUCate1 | 4 | DB.prod_c | 1 |
앞서 살펴본 바와 같이, 드라이빙 테이블과 정렬 조건이 불일치하는 문제를 해결하기 위해 'category' 테이블을 드라이빙 테이블로 변경하는 것이 효과적일 것으로 예상했습니다.
그러나 실제 실행 계획을 확인해 보니, 기존 쿼리와 동일한 실행 계획이 나타났습니다.
이는 MariaDB의 옵티마이저가 조인 순서를 최적화해주기 때문인데, 옵티마이저는 쿼리의 실행 계획을 자동으로 분석하고 최적화하는 기능을 수행하므로, 개발자가 직접 조인 순서를 지정하지 않아도 효율적인 실행 계획이 생성될 수 있습니다.
그러나 경우에 따라서는 옵티마이저의 판단이 적절하지 않을 수 있습니다.
이를 확인하기 위해 SELECT 절에 STRAIGHT_JOIN 키워드를 추가하여 조인 순서를 강제할 수 있습니다.
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | c | ALL | idxUCate1 | 8050321 | Using filesort | |||
1 | SIMPLE | p | eq_ref | PRIMARY, maker_c_idx, idxinput_d |
PRIMARY | 4 | DB.prod_c | 53 | Using where |
1 | SIMPLE | m | eq_ref | PRIMARY | PRIMARY | 4 | DB.maker_c | 1 | |
1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | DB.brand_c. | 1 | Using where |
1 | SIMPLE | pb | eq_ref | PRIMARY | PRIMARY | 4 | DB.prod_c | 1 | Using where |
쿼리 개선 이후의 실행계획입니다. 테이블 풀 스캔을 통해 File Sort를 사용하는 것을 알 수 있는데, 앞서 말씀드렸듯이 임시 테이블을 사용해 추가적인 정렬을 수행하는 것 보다는 성능이 좋습니다.
문제 해결
개선 이후의 쿼리를 수행해보겠습니다.
쿼리 실행 시간이 90초에서 5초로 크게 개선된 것을 확인할 수 있어 매우 긍정적인 결과라고 볼 수 있습니다.
그러나 이러한 개선이 영구적이지는 않을 것으로 예상됩니다. 시간이 지남에 따라 테이블의 데이터가 지속적으로 증가할 경우, buffer sort 메모리 부족으로 인한 디스크 접근 문제가 발생할 수 있기 때문입니다.
이에 대한 추가적인 개선 방안으로 인덱스 생성을 통해 성능을 개선하는 방법을 검토했지만, 이는 다른 모든 쿼리에 영향을 미칠 수 있고, CUD 성능에 직접적인 영향을 끼칠 수 있어 지양하기로 했습니다.
또한 애플리케이션 레이어에서 정렬을 수행하는 방법도 검토했지만, 800만 건 이상의 데이터 처리 시 OOM 에러가 발생할 수 있어 이 또한 반려되었습니다.
향후에는 추가적인 WHERE 절의 조건을 탐색하여 필터링을 강화하거나, possible_keys에 나타난 idxUCate1 인덱스를 활용하는 방향으로 성능 개선을 모색해볼 계획입니다.
'DB' 카테고리의 다른 글
[Database] Docker 컨테이너를 사용한 MySQL Master-Slave Replication 구축 및 예제 (1) | 2024.06.13 |
---|---|
[Database] B tree가 데이터베이스 인덱스로 사용되는 이유 (1) | 2024.06.12 |
[Database] 함수적 종속성을 활용한 테이블 정규화 과정 (0) | 2024.06.10 |
[Database] 함수적 종속성 (Functional Dependency) (1) | 2024.06.07 |
PostgreSQL, MySQL 에서의 Lost update 대처 방안 (0) | 2024.06.06 |