개요
- 게시글 댓글 목록 조회 기능에 대해 부하테스트를 했으나, TPS 가 늘숙날쑥하고, latency가 1분까지 치솟았다.
- RDS CPU 부하가 크게 치솟은 것을 보아, RDS가 병목지점으로 판단된다.
- 기존 게시글 목록 조회 쿼리를 분석해봐야한다.
- 게시글 댓글 수 조회 쿼리
- 게시글 댓글 조회 쿼리
게시글 댓글 수 조회쿼리
WITH cte AS (
SELECT article_comment_id
FROM article_comments
WHERE article_id = 24000000
LIMIT 501
)
SELECT COUNT(*)
FROM cte;
- 게시글 댓글수를 조회하는 부분이다.
- 이 쿼리는 기존 게시글 조회 기능처럼,
article_comment_counts 테이블을 통해 비정규화된 게시글 댓글 수를 가져와서, 조회하는 식으로 바꿀 예정이므로
성능 분석은 더 이상 하지 않을 것이다.
- 문제를 간단하게 이야기해보면
- 전체 테이블을 풀스캔해서,
article_id = 240000000 조건을 확인하여 카운팅한다. (인덱스 사용x)
-> article_id 에 대한 정렬 기준을 제공해주면 게시글 수를 좀 더 빠르게 조회할 수 있긴 하다.
- limit 값이 커질 수록 성능이 급격이 악화된다.
게시글 조회 쿼리
SELECT
article_comment_id,
content,
article_id,
root_parent_comment_id,
writer_id,
writer_nickname,
parent_comment_writer_id,
parent_comment_writer_nickname,
delete_status,
created_at,
modified_at
FROM article_comments
WHERE article_id = 24000000
ORDER BY root_parent_comment_id, article_comment_id
LIMIT 50;
articleId=24000000 인 게시글의 1페이지 댓글을 조회해봤다.- 그러나 30분, 40분, ... 이 되도록 응답이 오지 않는다.
mysql> EXPLAIN
-> SELECT
-> article_comment_id,
-> content,
-> article_id,
-> root_parent_comment_id,
-> writer_id,
-> writer_nickname,
-> parent_comment_writer_id,
-> parent_comment_writer_nickname,
-> delete_status,
-> created_at,
-> modified_at
-> FROM article_comments
-> WHERE article_id = 24000000
-> ORDER BY root_parent_comment_id, article_comment_id
-> LIMIT 50;
+----+-------------+------------------+------------+------+---------------+------+---------+------+----------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+------+---------------+------+---------+------+----------+----------+-----------------------------+
| 1 | SIMPLE | article_comments | NULL | ALL | NULL | NULL | NULL | NULL | 23659314 | 10.00 | Using where; Using filesort |
+----+-------------+------------------+------------+------+---------------+------+---------+------+----------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)
- 왜 이렇게 응답이 늦을 지는 간단히 생각해봐도 알 수 있는 문제다.
- 전체 게시글 댓글은 2400만건이고, 그 중
article_id=24000000 인 댓글은 1200만 828건이나 된다.
- 이것들을 전부 가져와서(전체 테이블을 뒤져가면서) 정렬하는 작업을 거쳐야한다.
mysql> EXPLAIN ANALYZE
-> SELECT
-> article_comment_id,
-> content,
-> article_id,
-> root_parent_comment_id,
-> writer_id,
-> writer_nickname,
-> parent_comment_writer_id,
-> parent_comment_writer_nickname,
-> delete_status,
-> created_at,
-> modified_at
-> FROM article_comments
-> WHERE article_id = 24000000
-> ORDER BY root_parent_comment_id, article_comment_id
-> LIMIT 50 OFFSET 0;
^C^C -- query aborted
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 50 row(s) (cost=2.53e+6 rows=50) (actual time=223673..223673 rows=0 loops=1)
-> Sort: article_comments.root_parent_comment_id, article_comments.article_comment_id, limit input to 50 row(s) per chunk (cost=2.53e+6 rows=23.7e+6) (actual time=223673..223673 rows=0 loops=1)
-> Filter: (article_comments.article_id = 24000000) (cost=2.53e+6 rows=23.7e+6) (actual time=1.92..222443 rows=2.49e+6 loops=1)
-> Table scan on article_comments (cost=2.53e+6 rows=23.7e+6) (actual time=1.92..222005 rows=4.97e+6 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (3 min 43.70 sec)
- 보통 Slow Query 에 대해서는
Explain analyze 까지 사용해서 성능을 분석하지만, 이 쿼리는 그 기능을 호출하면 30, 40분 길게는 몇 시간이 걸릴 것이 자명하다.
- 실제 상세실행계획 실행 도중 abort 시켜서 중단시켰다.
- 정렬하는 과정에서 시간을 엄청 잡아먹고 있고, 3-4분이 되도록 작업이 끝나지 않고 있다. 3-4분이면 양반이다. 1시간, 혹은 몇 시간이 걸릴 지 모른다.
인덱스 추가
-- 새 테이블 생성(기존 구조 복사)
CREATE TABLE article_comments_new LIKE article_comments;
-- 새 테이블에 인덱스 추가
ALTER TABLE article_comments_new
ADD INDEX idx_article_id_root_parent_comment_id_article_comment_id (article_id ASC, root_parent_comment_id ASC, article_comment_id ASC);
-- 기존 테이블 데이터를 새 테이블로 복사
INSERT INTO article_comments_new
SELECT * FROM article_comments;
-- 테이블 이름 변경
RENAME TABLE article_comments TO article_comments_old,
article_comments_new TO article_comments;
-- 기존 테이블 삭제
DROP TABLE article_comments_old;
- 문제를 해결할 방법은 간단하다.
- 각 게시글 별로(article_id), 댓글들이 root_parente_comment_id, article_comment_id 순으로 정렬되어 있도록 하여 이를 통해 빠르게 조회할 수 있도록 하면 된다.
- 보조 멀티칼럼 인덱스를 추가하여 게시글 댓글 조회 성능을 향상시켜보도록 하자.
- 다만 기존 테이블 자체에 인덱스를 추가하기에는 2400만건의 데이터가 있는 상황이라, 새로 테이블을 만들어서 다시 삽입하는 방식을 사용하기로 했다.
인덱스 추가 후 게시글 목록 조회 성능 확인
mysql> SELECT
-> article_comment_id,
-> content,
-> article_id,
-> root_parent_comment_id,
-> writer_id,
-> writer_nickname,
-> parent_comment_writer_id,
-> parent_comment_writer_nickname,
-> delete_status,
-> created_at,
-> modified_at
-> FROM article_comments
-> WHERE article_id = 24000000
-> ORDER BY root_parent_comment_id, article_comment_id
-> LIMIT 50 OFFSET 0;
+--------------------+-----------------+------------+------------------------+-----------+-----------------+--------------------------+--------------------------------+---------------+---------------------+---------------------+
| article_comment_id | content | article_id | root_parent_comment_id | writer_id | writer_nickname | parent_comment_writer_id | parent_comment_writer_nickname | delete_status | created_at | modified_at |
+--------------------+-----------------+------------+------------------------+-----------+-----------------+--------------------------+--------------------------------+---------------+---------------------+---------------------+
| 1 | content00000001 | 24000000 | 1 | 9933044 | user09933044 | NULL | NULL | NOT_DELETED | 2024-01-01 00:00:01 | 2024-01-01 00:00:01 |
| 3 | content00000003 | 24000000 | 1 | 4745000 | user04745000 | 9933044 | user09933044 | NOT_DELETED | 2024-01-01 00:00:03 | 2024-01-01 00:00:03 |
-- 생략
+--------------------+-----------------+------------+------------------------+-----------+-----------------+--------------------------+--------------------------------+---------------+---------------------+---------------------+
50 rows in set (0.02 sec)
- 20ms 만에 게시글 목록을 가져오는 것이 확인됐다.
mysql> SELECT
-> article_comment_id,
-> content,
-> article_id,
-> root_parent_comment_id,
-> writer_id,
-> writer_nickname,
-> parent_comment_writer_id,
-> parent_comment_writer_nickname,
-> delete_status,
-> created_at,
-> modified_at
-> FROM article_comments
-> WHERE article_id = 24000000
-> ORDER BY root_parent_comment_id, article_comment_id
-> LIMIT 50 OFFSET 14950;
+--------------------+-----------------+------------+------------------------+-----------+-----------------+--------------------------+--------------------------------+---------------+---------------------+---------------------+
| article_comment_id | content | article_id | root_parent_comment_id | writer_id | writer_nickname | parent_comment_writer_id | parent_comment_writer_nickname | delete_status | created_at | modified_at |
+--------------------+-----------------+------------+------------------------+-----------+-----------------+--------------------------+--------------------------------+---------------+---------------------+---------------------+
| 29988 | content00029988 | 24000000 | 29988 | 8332910 | user08332910 | NULL | NULL | NOT_DELETED | 2024-01-01 08:19:48 | 2024-01-01 08:19:48 |
| 29989 | content00029989 | 24000000 | 29988 | 10956473 | user10956473 | 8332910 | user08332910 | NOT_DELETED | 2024-01-01 08:19:49 | 2024-01-01 08:19:49 |
-- 생략
50 rows in set (0.08 sec)
- 300페이지를 조회했을 때는? -> 80ms 정도 소요된다.
mysql> EXPLAIN
-> SELECT
-> article_comment_id,
-> content,
-> article_id,
-> root_parent_comment_id,
-> writer_id,
-> writer_nickname,
-> parent_comment_writer_id,
-> parent_comment_writer_nickname,
-> delete_status,
-> created_at,
-> modified_at
-> FROM article_comments
-> WHERE article_id = 24000000
-> ORDER BY root_parent_comment_id, article_comment_id
-> LIMIT 50 OFFSET 14950;
+----+-------------+------------------+------------+------+----------------------------------------------------------+----------------------------------------------------------+---------+-------+----------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+------+----------------------------------------------------------+----------------------------------------------------------+---------+-------+----------+----------+-------+
| 1 | SIMPLE | article_comments | NULL | ref | idx_article_id_root_parent_comment_id_article_comment_id | idx_article_id_root_parent_comment_id_article_comment_id | 8 | const | 11582200 | 100.00 | NULL |
+----+-------------+------------------+------------+------+----------------------------------------------------------+----------------------------------------------------------+---------+-------+----------+----------+-------+
1 row in set, 1 warning (0.02 sec)
- 실행계획을 보면
idx_article_id_root_parent_comment_id_article_comment_id 를 활용하여 조회하면서, filteredd 역시 100.00 정도의 효율을 보인다.
mysql> EXPLAIN ANALYZE
-> SELECT
-> article_comment_id,
-> content,
-> article_id,
-> root_parent_comment_id,
-> writer_id,
-> writer_nickname,
-> parent_comment_writer_id,
-> parent_comment_writer_nickname,
-> delete_status,
-> created_at,
-> modified_at
-> FROM article_comments
-> WHERE article_id = 24000000
-> ORDER BY root_parent_comment_id, article_comment_id
-> LIMIT 50 OFFSET 14950;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit/Offset: 50/14950 row(s) (cost=1.66e+6 rows=50) (actual time=22.5..22.6 rows=50 loops=1)
-> Index lookup on article_comments using idx_article_id_root_parent_comment_id_article_comment_id (article_id=24000000) (cost=1.66e+6 rows=11.6e+6) (actual time=0.588..22 rows=15000 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
EXPLAIN ANALYZE 를 통해 확인해보면, 인덱스를 통해 lookup 하고, limit/offset 을 바로 적용한다.- 정렬 작업이 별도로 필요 없다. (이미 정렬되어 있으니까!)
API 직접 호출
- API 를 직접 호출해서 1페이지 조회해보면, 아까는 TimeOut 이 발생했지만, 104ms 정도로 빠르게 응답이 오는 것을 확인할 수 있다.
- 300 페이지 조회시 111ms 정도 소요된다.
- 심지어 5000페이지쯤까지 도달해도 1250ms 정도 소요된다. (다만 이렇게까지 시간이 걸리는건 위험성이 크므로 향후 페이지 제한을 두도록 할 예정이다.)
성능테스트
- 이전과 동일한 방식으로 성능 테스트를 해보겠다. 다만 10분간 Vus 를 0에서 1000까지 증가시키는 식으로 부하테스트의 강도를 더했다.
- VUs 448 쯤에서 TPS 가 389.6 정도를 달성.
- 그 이후는 TPS가 정체되기 시작하는 것을 확인
- 이 시점의 latency 는 446ms 정도로 준수한 편이다.
- EC2 CPU의 부하가 급증하고, RDS 는 18% 정도의 CPU 사용률을 보이고 있다.
- 성능상 병목이 되는 부분은 EC2가 됐다.