web
[MySql] DB 성능 최적화하기
뽀리님
2024. 7. 17. 11:47
회사에서 운영하고 있는 배치서비스 모니터링중 자꾸 memory leak 이 나는 현상을 발견했다.
2024-07-16 06:05:00.041 INFO 11456 --- [scheduling-1] o.s.batch.core.job.SimpleStepHandler : Executing step: [play-day-stat]
2024-07-16 06:05:20.062 WARN 11456 --- [HikariPool-1 housekeeper] cohttp://m.zaxxer.hikari.pool.ProxyLeakTask : Connection leak detection triggered for cohttp://m.mysql.cj.jdbc.ConnectionImpl@5a25810 on thread scheduling-1, stack trace follows
java.lang.Exception: Apparent connection leak detected
2024-07-16 06:05:36.630 INFO 11456 --- [scheduling-1] cohttp://m.zaxxer.hikari.pool.ProxyLeakTask : Previously reported leaked connection cohttp://m.mysql.cj.jdbc.ConnectionImpl@5a25810 on thread scheduling-1 was returned to the pool (unleaked)
playDayStatJob 에서 슬로우쿼리로 인해 메모리가 Leak나는 상황이다.
어차피 뒤에 다시 회수하긴하지만 그래도 성능에 문제가 있다고 판단하여 개선을 해보고자 정리하는 글
일단 문제가 있는 쿼리다.
SELECT
DATE_FORMAT(CURDATE() - INTERVAL 1721077500000 DAY, '%Y-%m-%d') AS start_date,
b.id AS media_ref_id,
CASE
WHEN p.name IS NULL THEN ''
ELSE p.name
END AS program_name,
b.title,
b.content_length,
CASE
WHEN a.distinct_display_count IS NULL THEN 0
ELSE a.distinct_display_count
END AS distinct_display_count,
CASE
WHEN a.duration IS NULL THEN 0
ELSE a.duration
END AS play_time,
'system' AS created_by,
NOW() AS created_date,
'system' AS last_modified_by,
NOW() AS last_modified_date
FROM
media b
LEFT JOIN (
SELECT
media_id,
SUM(duration) AS duration,
COUNT(DISTINCT member_id) AS distinct_display_count,
DATE_FORMAT(play_start_time, '%Y-%m-%d') AS start_date
FROM
stat_play_raw
WHERE
DATE_FORMAT(play_start_time, '%Y-%m-%d') = DATE_FORMAT(CURDATE() - INTERVAL 1721077500000 DAY, '%Y-%m-%d')
GROUP BY
media_id, DATE_FORMAT(play_start_time, '%Y-%m-%d')
) a ON b.id = a.media_id
LEFT JOIN
program p ON b.program_ref_id = p.id
WHERE
b.service_state = 'OPEN'
AND b.state = 'REGISTERED'
AND b.pub_type = 'VOD'
AND b.pub_date <= NOW()
AND b.pub_end_date >= NOW()
ORDER BY
b.id ASC;
✔️ 원인분석
1. 기존 쿼리 비용 분석
-> Nested loop left join (actual time=6175.654..6183.829 rows=4955 loops=1)
-> Nested loop left join (actual time=6175.627..6181.487 rows=4955 loops=1)
-> Filter: ((b.pub_type = 'VOD') and (b.state = 'REGISTERED') and (b.service_state = 'OPEN') and (b.pub_date <= <cache>(now())) and (b.pub_end_date >= <cache>(now()))) (cost=740.35 rows=1) (actual time=0.047..3.891 rows=4955 loops=1)
-> Index scan on b using PRIMARY (cost=740.35 rows=6046) (actual time=0.028..3.112 rows=6261 loops=1)
-> Index lookup on a using <auto_key0> (media_id=b.id) (actual time=0.000..0.000 rows=0 loops=4955)
-> Materialize (actual time=1.247..1.247 rows=0 loops=4955)
-> Group aggregate: sum(stat_play_raw.duration), count(distinct stat_play_raw.member_id) (actual time=6165.961..6175.411 rows=605 loops=1)
-> Sort: stat_play_raw.media_id, date_format(stat_play_raw.play_start_time,'%Y-%m-%d') (cost=1366606.19 rows=12022955) (actual time=6165.917..6167.227 rows=19461 loops=1)
-> Filter: (date_format(stat_play_raw.play_start_time,'%Y-%m-%d') = <cache>(date_format((curdate() - interval 1 day),'%Y-%m-%d'))) (cost=1366606.19 rows=12022955) (actual time=6145.041..6156.276 rows=19461 loops=1)
-> Table scan on stat_play_raw (cost=1366606.19 rows=12022955) (actual time=0.071..3203.257 rows=12670510 loops=1)
-> Single-row index lookup on p using PRIMARY (id=b.program_ref_id) (cost=0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=4955)
- Nested loop left join (actual time=6175.654..6183.829 rows=4955 loops=1)
- 가장 바깥쪽 중첩 루프 조인이다. 총 4955개의 행을 처리하는 데 약 8초가 걸린다.
- Nested loop left join (actual time=6175.627..6181.487 rows=4955 loops=1)
- 두 번째 중첩 루프 조인이다. 내부 루프에서 실행되고 있으며, 이 단계에서 대부분의 시간이 소요되고 있다.
Nested Loops는 한쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식이다. 즉 이름 그대로 중첩 반복이 이루어진다. Temporary는 쿼리 실행에 있어서 임시 테이블을 생성해서 사용하는 것이다. 실행 계획에서 해당 키워드가 보인다면 이 역시 피하도록 하는 것이 좋다. Full Scan 역시 row를 전체 도는 것으로 피하는 것이 좋다.
✔️ 조치방법
Plan1. 인덱스추가
Full Scan을 도는 stat_play_raw, media 테이블에 인덱스를 추가하여 성능을 개선해보자.
CREATE INDEX idx_stat_play_raw_play_start_time ON stat_play_raw (play_start_time);
CREATE INDEX idx_stat_play_raw_media_id_play_start_time ON stat_play_raw (media_id, play_start_time);
CREATE INDEX idx_media_filter ON media (pub_type, state, service_state, pub_date, pub_end_date);
⇛ 변화없다.ㅜㅜ
Plan1-2. 서브쿼리 WITH 처리
WITH temp_stat_play_raw AS (
SELECT
media_id,
SUM(duration) AS total_duration,
COUNT(DISTINCT member_id) AS distinct_member_count,
DATE(play_start_time) AS start_date
FROM
stat_play_raw
WHERE
play_start_days = TO_DAYS(CURDATE() - INTERVAL 1 DAY)
GROUP BY
media_id, start_date
)
결과는?
-> Nested loop left join (actual time=6481.616..6490.970 rows=4955 loops=1)
-> Nested loop left join (actual time=6481.565..6488.656 rows=4955 loops=1)
-> Filter: ((b.pub_type = 'VOD') and (b.state = 'REGISTERED') and (b.service_state = 'OPEN') and (b.pub_date <= <cache>(now())) and (b.pub_end_date >= <cache>(now()))) (cost=686.93 rows=1008) (actual time=0.028..5.241 rows=4955 loops=1)
-> Index scan on b using PRIMARY (cost=686.93 rows=6046) (actual time=0.019..4.467 rows=6261 loops=1)
-> Index lookup on a using <auto_key0> (media_id=b.id) (actual time=0.000..0.000 rows=0 loops=4955)
-> Materialize CTE temp_stat_play_raw (actual time=1.308..1.308 rows=0 loops=4955)
-> Group aggregate: sum(stat_play_raw.duration), count(distinct stat_play_raw.member_id) (actual time=6471.700..6481.218 rows=605 loops=1)
-> Sort: stat_play_raw.media_id, date_format(stat_play_raw.play_start_time,'%Y-%m-%d') (cost=1364052.75 rows=12022955) (actual time=6471.658..6472.969 rows=19461 loops=1)
-> Filter: (date_format(stat_play_raw.play_start_time,'%Y-%m-%d') = <cache>(date_format((curdate() - interval 1 day),'%Y-%m-%d'))) (cost=1364052.75 rows=12022955) (actual time=6448.777..6461.554 rows=19461 loops=1)
-> Table scan on stat_play_raw (cost=1364052.75 rows=12022955) (actual time=0.086..3436.464 rows=12670510 loops=1)
-> Single-row index lookup on p using PRIMARY (id=b.program_ref_id) (cost=0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=4955)
⇛ ???? 티끌도 변화없음 (챗GPT 개멍청....)
데이터 읽을 때와 저장할 때 메모리 사용량이 많아지면 WITH절을 써서 성능을 개선하는 큰 이유가 없다. 추출하는데에는 오랜 시간이 걸리나 건수가 적을 경우에 사용하자
Plan2. 힌트추가
서브쿼리에 힌트 추가
WITH temp_stat_play_raw AS (
SELECT
/*+ INDEX(stat_play_raw idx_stat_play_raw_play_start_time_media_id) */
media_id,
⇛ 똑같다 ㅠㅠ 헝..
Plan3. 테이블 파티셔닝
파티셔닝을 위해선 기준이 되는 Column 이 PK이어야 한다.
1) play_start_time PK 추가
ALTER TABLE stat_play_raw
DROP PRIMARY KEY,
ADD PRIMARY KEY(`id`, `play_start_time`);
2) play_start_time(timestamp) 기준
-- 테이블 파티셔닝
ALTER TABLE stat_play_raw
PARTITION BY RANGE (UNIX_TIMESTAMP(play_start_time)) (
PARTITION p2020 VALUES LESS THAN (UNIX_TIMESTAMP('2020-01-01 00:00:00')),
PARTITION p2021 VALUES LESS THAN (UNIX_TIMESTAMP('2021-01-01 00:00:00')),
PARTITION p2022 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')),
PARTITION p2023 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')),
PARTITION p2024 VALUES LESS THAN (UNIX_TIMESTAMP('2024-01-01 00:00:00')),
PARTITION p VALUES LESS THAN (MAXVALUE)
);
⇛ 하지만 크게 변화없었다.
.... 왜지 왜왜왜???
확인해보니
날짜 컬럼에 대한 Range 파티션 적용시 YEAR(),TO_DAYS() 함수만 사용하길 권장한다.
두 함수는 MYSQL 서버 내부적으로 파티션 프루닝 처리가 되어 성능상의 문제가 발생하지 않지만
그 외의 함수는 파티션 프루닝이 제대로 작동하지 않을 수도 있기 때문에 지양하길 권장한다고.😭😭
timestamp 형이라 year(), to_days()로 변환해봤다!
SQL Error [1486] [HY000]: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed
ㅠㅠ 안된다. YEAR(),TO_DAYS() 함수는 DATE 또는 DATETIME 타입의 컬럼에서 사용이 가능하다한다.
그럼 결국 남은건 임시테이블 방법뿐인가..
Plan4. 임시테이블 생성
-- 임시 테이블 생성 및 데이터 삽입
CREATE TEMPORARY TABLE temp_stat_play_raw AS
SELECT
media_id,
SUM(duration) AS total_duration,
COUNT(DISTINCT member_id) AS distinct_member_count,
DATE_FORMAT(play_start_time, '%Y-%m-%d') AS start_date
FROM
stat_play_raw
WHERE
DATE_FORMAT(play_start_time, '%Y-%m-%d') = DATE_FORMAT(CURDATE() - INTERVAL 1 DAY, '%Y-%m-%d')
GROUP BY
media_id, DATE_FORMAT(play_start_time, '%Y-%m-%d');
-- 메인 쿼리 실행
SELECT
DATE_FORMAT(CURDATE() - INTERVAL 1 DAY, '%Y-%m-%d') AS start_date,
b.id AS media_ref_id,
CASE
WHEN p.name IS NULL THEN ''
ELSE p.name
END AS program_name,
b.title,
b.content_length,
CASE
WHEN a.distinct_member_count IS NULL THEN 0
ELSE a.distinct_member_count
END AS distinct_display_count,
CASE
WHEN a.total_duration IS NULL THEN 0
ELSE a.total_duration
END AS play_time,
'system' AS created_by,
NOW() AS created_date,
'system' AS last_modified_by,
NOW() AS last_modified_date
FROM
media b
LEFT JOIN
temp_stat_play_raw a ON b.id = a.media_id
LEFT JOIN
program p ON b.program_ref_id = p.id
WHERE
b.service_state = 'OPEN'
AND b.state = 'REGISTERED'
AND b.pub_type = 'VOD'
AND b.pub_date <= NOW()
AND b.pub_end_date >= NOW()
ORDER BY
b.id ASC;
[성능계산]
-> Sort: b.id (actual time=14.961..15.487 rows=4955 loops=1)
-> Stream results (cost=137287.50 rows=685145) (actual time=0.270..12.943 rows=4955 loops=1)
-> Nested loop left join (cost=137287.50 rows=685145) (actual time=0.261..7.984 rows=4955 loops=1)
-> Left hash join (a.media_id = b.id) (cost=68521.13 rows=685145) (actual time=0.252..5.581 rows=4955 loops=1)
-> Filter: ((b.pub_type = 'VOD') and (b.state = 'REGISTERED') and (b.service_state = 'OPEN') and (b.pub_date <= <cache>(now())) and (b.pub_end_date >= <cache>(now()))) (cost=539.31 rows=1008) (actual time=0.019..4.675 rows=4955 loops=1)
-> Table scan on b (cost=539.31 rows=6046) (actual time=0.009..3.758 rows=6261 loops=1)
-> Hash
-> Table scan on a (cost=0.07 rows=680) (actual time=0.013..0.163 rows=605 loops=1)
-> Single-row index lookup on p using PRIMARY (id=b.program_ref_id) (cost=0.00 rows=1) (actual time=0.000..0.000 rows=1 loops=4955)✔️ 기존 쿼리와 차이점 분석
- 실행 시간:
기존 쿼리: Nested loop left join의 실제 시간이 6175.654..6183.829로 매우 길며, 테이블 스캔 및 정렬에 많은 시간이 소요.
임시 테이블 쿼리: Nested loop left join의 실제 시간이 0.261..7.984로 훨씬 빠름. 전체 실행 시간도 더 짧음
- 테이블 스캔:
기존 쿼리: stat_play_raw 테이블의 전체 스캔 비용이 매우 높음 (cost=1366606.19).
임시 테이블 쿼리: stat_play_raw 테이블을 임시 테이블로 만들어서 필요한 데이터만 스캔. cost=0.07로 매우 낮음
- 조인 방식:
기존 쿼리: 중첩 루프 조인과 해시 조인을 사용하지만, 전체적인 비용과 실제 시간이 높음
임시 테이블 쿼리: 해시 조인을 사용하여 더 효율적으로 조인을 수행. 실제 시간이 훨씬 짧음
✔️ 추가 개선 방안
➔ 인덱스 최적화 : 필요한 경우 추가 인덱스를 생성.
➔ 임시 테이블 사용 : 임시 테이블을 사용하여 중간 결과를 저장하고 이를 활용하여 메인 쿼리의 성능을 최적화
➔ 쿼리 리팩토링 : 쿼리를 최적화하여 불필요한 테이블 스캔을 줄이고, 필요한 데이터만 처리.
참조
https://gradle.tistory.com/4
https://velog.io/@blakekim93/%EC%BF%BC%EB%A6%AC-%EC%B5%9C%EC%A0%81%ED%99%94-eu4ti0e0
https://velog.io/@maketheworldwise/MySQL-%EC%84%B1%EB%8A%A5-%EC%B5%9C%EC%A0%81%ED%99%94-%EC%B0%8D%EB%A8%B9%ED%95%B4%EB%B3%B4%EA%B8%B0