Search

대량 데이터의 GROUP BY와 temp size

MySQL에서는 GROUP BY 처리를 위해 temp 공간을 활용하기도 한다. 이 경우 심각한 성능 저하가 발생할 수 있다.
아래 SQL은 2019년 부터 2023년 12월말까지의 주문 데이터에 대해 회원ID(member_id) 별 주문 건수를 구하고 있다.
SELECT /*+ NO_INDEX(t1) */ t1.member_id ,COUNT(*) cnt FROM startdbmy.tr_ord_big t1 WHERE t1.ord_dtm >= STR_TO_DATE('20190101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230101','%Y%m%d') GROUP BY t1.member_id ORDER BY 2 DESC; [1] -> Sort: cnt DESC (actual time=496620..496621 rows=9999 loops=1) [2] -> Table scan on <temporary> (actual time=496604..496609 rows=9999 loops=1) [3] -> Aggregate using temporary table (actual time=496604..496604 rows=9999 loops=1) [4] -> Filter: ((startdbmy.t1.ord_dtm >= <cache>(str_to_date('20190101','%Y%m%d'))) (actual time=0.159..80258 rows=28.5e+6 loops=1) [5] -> Table scan on t1 (cost=3.13e+6 rows=30.4e+6) (actual time=0.144..57554 rows=31.3e+6 loops=1)
SQL
복사
현재 tr_ord_big 테이블에 2019년부터 2023년 12월말까지의 주문 데이터는 28,505,178 건이 입력되어 있다.
실행계획을 보면, Table Scan(FULL SCAN)을 사용해 조건에 맞는 데이터를 검색하고 있다. 데이터를 검색하는데 걸린 시간이 실행 계획 기준으로 80초(80258 ms)이다. 실행계획의 [5]번, [4]번이 이에 속한다. 필요한 데이터를 찾은 후에는 데이터를 GROUP BY 처리해야 한다. 실행계획의 [3]번이 이에 속한다. [3]번의 Actual time을 보면 종료 시간이 496.60초(496604 ms)다. 데이터를 찾는데 80초, 데이터를 GROUP BY 집계 처리하는데 410초가 걸린 것이다.
데이터 집계 과정의 실행계획([3]번 라인)을 살펴보면, Aggregate using temporary table을 하고 있다.
데이터 집계(GROUP BY) 처리를 위해 temp 공간을 사용하고 있는 것이다. 이로 인해 410초라는 어마어마한 시간이 걸리고 있는 것이다.
PostgreSQL이나 ORACLE의 경우는 대량의 데이터를 GROUP BY 하는 과정에서 HASH Aggregation이라는 매우 성능이 좋은 알고리즘을 사용하고 있다. PostgreSQL과 ORACLE의 실행계획을 살펴보며 다음과 같다.(각 DBMS별로 서버 자원과 설정은 같지 않지만 데이터 양은 동일하게 테스트했다.)
-- PostgreSQL Sort (actual time=22098.002..22098.764 rows=9999 loops=1) Sort Key: (count(*)) DESC Sort Method: quicksort Memory: 775kB Buffers: shared hit=15404 read=339800 -> HashAggregate (actual time=22094.009..22095.827 rows=9999 loops=1) Group Key: member_id Batches: 1 Memory Usage: 1169kB Buffers: shared hit=15404 read=339800 -> Seq Scan on tr_ord_big t1 (actual time=219.114..18942.242 rows=12163034 loops=1) Filter: ((ord_dtm >= to_date('20190101'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20230701'::text, 'YYYYMMDD'::text))) Rows Removed by Filter: 19095440 Buffers: shared hit=15404 read=339800 Execution Time: 22100.628 ms -- ORACLE -------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 100 |00:00:03.58 | 255K| 255K| | | | | 1 | SORT ORDER BY | | 1 | 9999 | 100 |00:00:03.58 | 255K| 255K| 407K| 407K| 361K (0)| | 2 | HASH GROUP BY | | 1 | 9999 | 9999 |00:00:03.58 | 255K| 255K| 1694K| 1694K| 1718K (0)| |* 3 | TABLE ACCESS FULL| TR_ORD_BIG | 1 | 27M| 12M|00:00:03.64 | 255K| 255K| | | | --------------------------------------------------------------------------------------------------------------------------------
SQL
복사
두 DBMS 모두 HASH 알고리즘을 사용해 GROUP BY를 빠르게 처리하고 있다. 특히, ORACLE의 성능은 인상적이다.
어쨋든, MySQL은 temp 영역을 사용한 GROUP BY를 하고 있기 때문에, 대량의 데이터를 빠르게 GROUP BY 처리하고 싶다면, 다음과 같이 temp size를 늘리는 방법을 고려해야 한다.
SHOW SESSION variables like 'tmp_table_size'; -- 75497472(72MB) SET SESSION tmp_table_size = 1024 * 1024 * 2048; -- temp table size를 2GB로 늘림 EXPLAIN ANALYZE SELECT /*+ NO_INDEX(t1) */ t1.member_id ,COUNT(*) cnt FROM startdbmy.tr_ord_big t1 WHERE t1.ord_dtm >= STR_TO_DATE('20190101','%Y%m%d') AND t1.ord_dtm < STR_TO_DATE('20230101','%Y%m%d') GROUP BY t1.member_id ORDER BY 2 DESC; -> Sort: cnt DESC (actual time=44706..44707 rows=9999 loops=1) -> Table scan on <temporary> (actual time=44693..44696 rows=9999 loops=1) -> Aggregate using temporary table (actual time=44693..44693 rows=9999 loops=1) -> Filter: ((startdbmy.t1.ord_dtm >= <cache>(str_to_date('20190101','%Y%m%d'))) (actual time=14.5..39607 rows=2.5e+6 loops=1) -> Table scan on t1 (cost=3.17e+6 rows=30.4e+6) (actual time=14.5..31350 rows=31.3e+6 loops=1)
SQL
복사
기존의 496초 짜리 SQL이, temp table size를 2GB로 크게 늘리자 44.7초로 개선되었다. 하지만 메모리는 한정된 자원이기 때문에 모든 세션에 대해 이처럼 temp table size를 늘리는 것은 엄청난 리스크가 있다. 상황에 따라 배치성 SQL에서만 가끔 사용해 볼 만한 방법이다.
MySQL에서 위와 같은 SQL 패턴의 성능 개선을 위해서는 방금 필자가 보여준 temp table size가 아닌, INDEX를 활용하는 것이 더욱 추천할만하다. 또한, 이처럼 천만 건 이상의 데이터를 일괄적으로 읽는 경우, 단순히 성능 최적화를 고려하는 것 이외에 해당 업무의 필요성과 빈도를 고려하는 것이 중요하다. 이러한 업무가 자주 실행되어야 하는지 개발팀과 함께 논의할 필요가 있다. 단순히 SQL 개선이 아닌 업무 프로세스의 개선을 고려할 필요가 있는 것이다.
•
Filter: ((startdbmy.t1.ord_dtm >= …생략… (actual time=0.159..80258 rows=28.5e+6 loops=1)
찾은 데이터를 GROUP BY 하는 과정ㅇ
ord_dtm에 대한 인덱스만 존재하는 경우에 각 DBMS 별로 성능을 요약해 보면 다음과 같다.
2,496,547 건 조회 및 GROUP BY
FULL SCAN(초)
INDEX RANGE SCAN(초)
PostgreSQL
19.72
1.61
ORACLE
2.16
3.23
MySQL
43.03
45.8
MySQL의 성능이 다른 DBMS에 비해 월등히 나쁘다. 물론, 각 DBMS가 설치된 설정이 동일하지 않으므로 DBMS 간 성능 차이가 날 수 있다. 그럼에도 불구하고, MySQL은 월등히 나쁜 편이다.
왜 그런지 살펴보기 위해서는 실행계획을 확인해야 한다. INDEX RANGE SCAN으로 데이터가 처리된 경우의 PostgreSQL과 MySQL의 실행계획을 살펴보면 다음과 같다.
------------------------------------- -- PostgreSQL - Index Scan(1610.866 ms) ------------------------------------- Sort (actual time=1609.067..1609.630 rows=9999 loops=1) Sort Key: (count(*)) DESC Sort Method: quicksort Memory: 775kB Buffers: shared hit=1876299 read=24153 -> HashAggregate (actual time=1605.949..1607.336 rows=9999 loops=1) Group Key: member_id Batches: 1 Memory Usage: 1169kB Buffers: shared hit=1876299 read=24153 -> Index Scan using tr_ord_big_x01 on tr_ord_big t1 (actual time=106.377..1045.740 rows=2496547 loops=1) Index Cond: ((ord_dtm >= to_date('20190101'::text, 'YYYYMMDD'::text)) AND (ord_dtm < to_date('20230101'::text, 'YYYYMMDD'::text))) Buffers: shared hit=1876299 read=24153 Execution Time: 1610.866 ms ------------------------------------- -- MySQL - INDEX RANGE SCAN(45810 ms) ------------------------------------- -> Sort: cnt DESC (actual time=45809..45810 rows=9999 loops=1) -> Table scan on <temporary> (actual time=45796..45800 rows=9999 loops=1) -> Aggregate using temporary table (actual time=45796..45796 rows=9999 loops=1) -> Index range scan on t1 using TR_ORD_BIG_X01 over ('2019-01-01 00:00:00' <= ord_dtm < '2023-01-01 00:00:00'), with index condition: ((startdbmy.t1.ord_dtm >= <cache>(str_to_date('20190101','%Y%m%d'))) ...(actual time=0.456..40671 rows=2.5e+6 loops=1)
SQL
복사
두 DBMS의 실행계획을 살펴보면, 보여주는 내용에 차이가 많다. 하지만 전반적인 처리 방향은 비슷하다. 조건에 해당하는 데이터를 읽어 들이고, GROUP BY에 따라 데이터를 집계해 결과를 내보내고 있다.
먼저 PostgreSQL의 실행계획을 살펴보면, Hash Aggregate 방식을 사용해 데이터를 GROUP BY 처리하고 있다. 반면에 MySQL은 Aggregate using temporary table이라는 temp 공간을 활용한 GROUP BY 처리를 하고 있다. MySQL은 총 45.81초가 걸렸는데, Index를 활용해 데이터를 찾아내는 과정까지, 40.67초가 걸렸고 데이터를 집계하는 과정까지 45.79초가 걸렸다. temp 공간을 사용해 데이터를 집계하는데 5.79초가 소모되었다고 할 수 있다.
전체 테스트 스크립트