부하테스트 ‐ (6) 게시글 목록 페이지 조회 성능 개선 (게시글수 비정규화, 쿼리 튜닝)

개요

지난 글에서 게시글 목록 페이지 조회 성능을 크게 향상시켰다. (인덱스 추가)
그러나 게시글 조회 성능을 더 향상시킬 수 있는 여지가 보이기 때문에, 게시글 조회 기능을 손 보려고 한다.

기존 방식

mysql> WITH cte AS (SELECT a.article_id
    ->              FROM articles AS a
    ->              WHERE a.board_id = 1
    ->              LIMIT 1001)
    -> SELECT COUNT(*)
    -> FROM cte;
+----------+
| COUNT(*) |
+----------+
|     1001 |
+----------+
1 row in set (0.03 sec)

mysql> WITH cte AS (SELECT a.article_id
    ->              FROM articles AS a
    ->              WHERE a.board_id = 1
    ->              LIMIT 10000001)
    -> SELECT COUNT(*)
    -> FROM cte;

+----------+
| COUNT(*) |
+----------+
| 10000001 |
+----------+
1 row in set (25.75 sec)

게시글 수 조회 (페이지 조회 과정에서, 접근할 수 있는 페이지가 몇 페이지까지인 지 확인하기 위함.)
- 매번 Count(*) 를 사용하는데, 뒷페이지로 갈 수록 성능이 현저히 느려지는 문제가 있다.
- 예) 1번 게시판의 20페이지 + 1개의 게시글(1001개) 수를 조회하는 쿼리는 0.03ms 정도의 성능을 보인다.
- 예) 1번 게시판의 2만페이지 + 1개의 게시글(1000만 1개) 수를 조회하는 쿼리는 25750ms 정도의 성능을 보인다.

mysql> select a.article_id,
    ->        a.title,
    ->        a.board_id,
    ->        b.name,
    ->        b.slug,
    ->        a.article_category_id,
    ->        ac.name,
    ->        ac.slug,
    ->        a.writer_id,
    ->        a.writer_nickname,
    ->        coalesce(acc.comment_count, 0),
    ->        coalesce(alc.like_count, 0),
    ->        coalesce(adc.dislike_count, 0),
    ->        a.created_at
    -> from articles a
    ->          join
    ->      boards b
    ->      on b.board_id = a.board_id
    ->          join
    ->      article_categories ac
    ->      on ac.article_category_id = a.article_category_id
    ->          left join
    ->      article_comment_counts acc
    ->      on acc.article_id = a.article_id
    ->          left join
    ->      article_like_counts alc
    ->      on alc.article_id = a.article_id
    ->          left join
    ->      article_dislike_counts adc
    ->      on adc.article_id = a.article_id
    -> where a.board_id = 500
    -> order by a.article_id desc
    -> limit 50 offset 950;
+------------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+
| article_id | title         | board_id | name          | slug          | article_category_id | name   | slug     | writer_id | writer_nickname | coalesce(acc.comment_count, 0) | coalesce(alc.like_count, 0) | coalesce(adc.dislike_count, 0) | created_at          |
+------------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+
|   23080717 | title23080717 |      500 | board00000500 | board00000500 |                1999 | 정보   | tip      |   1751748 | user01751748    |                              0 |                           0 |                              0 | 2024-09-24 03:18:37 |
|   23080707 | title23080707 |      500 | board00000500 | board00000500 |                1998 | 질문   | question |   4929538 | user04929538    |                              0 |                           0 |                              1 | 2024-09-24 03:18:27 |
|   23077000 | title23077000 |      500 | board00000500 | board00000500 |                1997 | 일반   | general  |   1849600 | user01849600    |                              0 |                           0 |                              1 | 2024-09-24 02:16:40 |

-- 생략
50 rows in set (0.02 sec)


mysql> select a.article_id,
    ->        a.title,
    ->        a.board_id,
    ->        b.name,
    ->        b.slug,
    ->        a.article_category_id,
    ->        ac.name,
    ->        ac.slug,
    ->        a.writer_id,
    ->        a.writer_nickname,
    ->        coalesce(acc.comment_count, 0),
    ->        coalesce(alc.like_count, 0),
    ->        coalesce(adc.dislike_count, 0),
    ->        a.created_at
    -> from articles a
    ->          join
    ->      boards b
    ->      on b.board_id = a.board_id
    ->          join
    ->      article_categories ac
    ->      on ac.article_category_id = a.article_category_id
    ->          left join
    ->      article_comment_counts acc
    ->      on acc.article_id = a.article_id
    ->          left join
    ->      article_like_counts alc
    ->      on alc.article_id = a.article_id
    ->          left join
    ->      article_dislike_counts adc
    ->      on adc.article_id = a.article_id
    -> where a.board_id = 500
    -> order by a.article_id desc
    -> limit 50 offset 19950;
+------------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+
| article_id | title         | board_id | name          | slug          | article_category_id | name   | slug     | writer_id | writer_nickname | coalesce(acc.comment_count, 0) | coalesce(alc.like_count, 0) | coalesce(adc.dislike_count, 0) | created_at          |
+------------+---------------+----------+---------------+---------------+---------------------+--------+----------+-----------+-----------------+--------------------------------+-----------------------------+--------------------------------+---------------------+
|    4292607 | title04292607 |      500 | board00000500 | board00000500 |                1999 | 정보   | tip      |   9047666 | user09047666    |                              0 |                           0 |                              0 | 2024-02-19 16:23:27 |
|    4291970 | title04291970 |      500 | board00000500 | board00000500 |                1997 | 일반   | general  |   3920213 | user03920213    |                              1 |                           1 |                              2 | 2024-02-19 16:12:50 |
|    4291427 | title04291427 |      500 | board00000500 | board00000500 |                1997 | 일반   | general  |   1031354 | user01031354    |                              0 |                           1 |                              2 | 2024-02-19 16:03:47 |
-- 생략
50 rows in set (34.72 sec)

게시글 조회 쿼리 역시 마찬가지이다. 게시글에 접근시 offset, limit 를 사용하여 접근하는데 offset이 커지면 커질 수록, 조회성능이 크게 악화되는 현상을 보인다.
뒷페이지로 갈 수록 조회 성능이 안 좋아지는 것이다.

문제 해결안

게시판의 게시글수 조회 쿼리는 뒷페이지로 갈 수록 성능이 악화된다.
- 아예 게시판 별 게시글 수를 비정규화시키는게 나을 듯 하다.

mysql> explain analyze
    -> select a.article_id,
    ->        a.title,
    ->        a.board_id,
    ->        b.name,
    ->        b.slug,
    ->        a.article_category_id,
    ->        ac.name,
    ->        ac.slug,
    ->        a.writer_id,
    ->        a.writer_nickname,
    ->        coalesce(acc.comment_count, 0),
    ->        coalesce(alc.like_count, 0),
    ->        coalesce(adc.dislike_count, 0),
    ->        a.created_at
    -> from articles a
    ->          join
    ->      boards b
    ->      on b.board_id = a.board_id
    ->          join
    ->      article_categories ac
    ->      on ac.article_category_id = a.article_category_id
    ->          left join
    ->      article_comment_counts acc
    ->      on acc.article_id = a.article_id
    ->          left join
    ->      article_like_counts alc
    ->      on alc.article_id = a.article_id
    ->          left join
    ->      article_dislike_counts adc
    ->      on adc.article_id = a.article_id
    -> where a.board_id = 500
    -> order by a.article_id desc
    -> limit 50 offset 19950;

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit/Offset: 50/19950 row(s)  (cost=238144 rows=50) (actual time=34520..34613 rows=50 loops=1)
    -> Nested loop left join  (cost=238144 rows=44248) (actual time=3.29..34606 rows=20000 loops=1)
        -> Nested loop left join  (cost=191143 rows=44248) (actual time=3.28..26992 rows=20000 loops=1)
            -> Nested loop left join  (cost=144007 rows=44248) (actual time=3.27..19407 rows=20000 loops=1)
                -> Nested loop inner join  (cost=96882 rows=44248) (actual time=3.25..11832 rows=20000 loops=1)
                    -> Index lookup on a using idx_board_id_article_id (board_id=500)  (cost=48209 rows=44248) (actual time=0.734..11731 rows=20000 loops=1)
                    -> Single-row index lookup on ac using PRIMARY (article_category_id=a.article_category_id)  (cost=1 rows=1) (actual time=0.00449..0.00454 rows=1 loops=20000)
                -> Single-row index lookup on acc using PRIMARY (article_id=a.article_id)  (cost=0.965 rows=1) (actual time=0.378..0.378 rows=0.392 loops=20000)
            -> Single-row index lookup on alc using PRIMARY (article_id=a.article_id)  (cost=0.965 rows=1) (actual time=0.379..0.379 rows=0.403 loops=20000)
        -> Single-row index lookup on adc using PRIMARY (article_id=a.article_id)  (cost=0.962 rows=1) (actual time=0.38..0.38 rows=0.401 loops=20000)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (34.64 sec)

게시글 조회 쿼리
- 게시글 조회 쿼리의 성능 문제는 SELECT 절의 프로젝션에 인덱스에 없는 컬럼들이 포함되어 있다는 점에서 발생한다.
- 예를 들어 게시글 제목, 작성자 ID, 작성자 닉네임 등의 칼럼은 게시글 테이블의 인덱스(idx_board_id_article_id)에 포함되어 있지 않다.
- MySQL(InnoDB)은 OFFSET에 도달하지 않았더라도, SELECT에 필요한 컬럼이 인덱스에 없으면 실제 테이블(클러스터드 인덱스)에 접근(Disk I/O) 하여 데이터를 읽는다.
- 즉, LIMIT/OFFSET 처리를 위해 불필요하게 수천 ~ 수만 건의 물리적인 테이블 접근이 발생할 수 있어 쿼리 성능이 급격히 저하된다.
- 이를 개선하려면, 먼저 프로젝션을 최소화하여 OFFSET 범위까지는 인덱스만으로 처리할 수 있는 커버링 인덱스를 활용해야 한다. 예를 들어, 서브쿼리를 통해 게시글 식별자(article_id)만 먼저 조회한 뒤, 이 식별자들을 기반으로 실제 데이터를 조회하는 방식으로 쿼리를 분리하면 성능이 훨씬 효율적이다.

게시글 수 조회 수정

mysql> SELECT ac.article_count
    -> FROM board_article_counts ac
    -> WHERE ac.board_id = 500;
+---------------+
| article_count |
+---------------+
|         24280 |
+---------------+
1 row in set (0.02 sec)

게시글 수는 이제 board_article_count 테이블로 비정규화해서 조회하도록 한다.

mysql> EXPLAIN
    -> SELECT ac.article_count
    -> FROM board_article_counts ac
    -> WHERE ac.board_id = 500;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | ac    | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN ANALYZE
    -> SELECT ac.article_count
    -> FROM board_article_counts ac
    -> WHERE ac.board_id = 500;
+--------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                          |
+--------------------------------------------------------------------------------------------------+
| -> Rows fetched before execution  (cost=0..0 rows=1) (actual time=90e-6..139e-6 rows=1 loops=1)
 |
+--------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

pk 로 조회하기 때문에 조회는 게시글 수와 무관하게 클러스터링 인덱스를 통해 매우 빠르게 조회된다.

게시글 조회 수정

mysql> WITH cte(article_id) AS (
    ->     SELECT article_id
    ->     FROM articles
    ->     WHERE board_id = 500
    ->     ORDER BY article_id DESC
    ->     LIMIT 50 OFFSET 950
    -> )
    -> SELECT
    ->     a.article_id AS articleId,
    ->     a.title AS title,
    ->
    ->     b.board_id AS boardId,
    ->     b.name AS boardName,
    ->     b.slug AS boardSlug,
    ->
    ->     ac.article_category_id AS articleCategoryId,
    ->     ac.name AS articleCategoryName,
    ->     ac.slug AS articleCategorySlug,
    ->
    ->     a.writer_id AS writerId,
    ->     a.writer_nickname AS writerNickname,
    ->
    ->     COALESCE(acc.comment_count, 0) AS commentCount,
    ->     COALESCE(alc.like_count, 0) AS likeCount,
    ->     COALESCE(adc.dislike_count, 0) AS dislikeCount,
    ->
    ->     a.created_at AS createdAt
    -> FROM articles a
    ->          JOIN cte ON a.article_id = cte.article_id
    ->          JOIN boards b ON b.board_id = a.board_id
    ->          JOIN article_categories ac ON ac.article_category_id = a.article_category_id
    ->          LEFT JOIN article_comment_counts acc ON acc.article_id = a.article_id
    ->          LEFT JOIN article_like_counts alc ON alc.article_id = a.article_id
    ->          LEFT JOIN article_dislike_counts adc ON adc.article_id = a.article_id;
+-----------+---------------+---------+---------------+---------------+-------------------+---------------------+---------------------+----------+----------------+--------------+-----------+--------------+---------------------+
| articleId | title         | boardId | boardName     | boardSlug     | articleCategoryId | articleCategoryName | articleCategorySlug | writerId | writerNickname | commentCount | likeCount | dislikeCount | createdAt           |
+-----------+---------------+---------+---------------+---------------+-------------------+---------------------+---------------------+----------+----------------+--------------+-----------+--------------+---------------------+
|  23080717 | title23080717 |     500 | board00000500 | board00000500 |              1999 | 정보                | tip                 |  1751748 | user01751748   |            0 |         0 |            0 | 2024-09-24 03:18:37 |
|  23080707 | title23080707 |     500 | board00000500 | board00000500 |              1998 | 질문                | question            |  4929538 | user04929538   |            0 |         0 |            1 | 2024-09-24 03:18:27 |
-- 생략
50 rows in set (0.02 sec)

기존에 50ms 정도 소요되던 500번 게시판 20페이지 게시글 조회는 20ms 로 성능이 향상됐다. (약 2.5배 성능 향상)

mysql> WITH cte(article_id) AS (
    ->     SELECT article_id
    ->     FROM articles
    ->     WHERE board_id = 500
    ->     ORDER BY article_id DESC
    ->     LIMIT 50 OFFSET 14950
    -> )
    -> SELECT
    ->     a.article_id AS articleId,
    ->     a.title AS title,
    ->
    ->     b.board_id AS boardId,
    ->     b.name AS boardName,
    ->     b.slug AS boardSlug,
    ->
    ->     ac.article_category_id AS articleCategoryId,
    ->     ac.name AS articleCategoryName,
    ->     ac.slug AS articleCategorySlug,
    ->
    ->     a.writer_id AS writerId,
    ->     a.writer_nickname AS writerNickname,
    ->
    ->     COALESCE(acc.comment_count, 0) AS commentCount,
    ->     COALESCE(alc.like_count, 0) AS likeCount,
    ->     COALESCE(adc.dislike_count, 0) AS dislikeCount,
    ->
    ->     a.created_at AS createdAt
    -> FROM articles a
    ->          JOIN cte ON a.article_id = cte.article_id
    ->          JOIN boards b ON b.board_id = a.board_id
    ->          JOIN article_categories ac ON ac.article_category_id = a.article_category_id
    ->          LEFT JOIN article_comment_counts acc ON acc.article_id = a.article_id
    ->          LEFT JOIN article_like_counts alc ON alc.article_id = a.article_id
    ->          LEFT JOIN article_dislike_counts adc ON adc.article_id = a.article_id;
+-----------+---------------+---------+---------------+---------------+-------------------+---------------------+---------------------+----------+----------------+--------------+-----------+--------------+---------------------+
| articleId | title         | boardId | boardName     | boardSlug     | articleCategoryId | articleCategoryName | articleCategorySlug | writerId | writerNickname | commentCount | likeCount | dislikeCount | createdAt           |
+-----------+---------------+---------+---------------+---------------+-------------------+---------------------+---------------------+----------+----------------+--------------+-----------+--------------+---------------------+
|   9261611 | title09261611 |     500 | board00000500 | board00000500 |              1997 | 일반                | general             |  2297565 | user02297565   |            1 |         0 |            2 | 2024-04-17 04:40:11 |
|   9261269 | title09261269 |     500 | board00000500 | board00000500 |              1999 | 정보                | tip                 |  4352823 | user04352823   |            1 |         0 |            0 | 2024-04-17 04:34:29 |
-- 생략
50 rows in set (0.02 sec)

500번 게시판의 300페이지(offset 14950, limit 50) 게시글 조회 속도 역시 20ms 로 향상됐다. (기존: 25480ms -> 20ms, 1274배 성능 향상)

mysql> EXPLAIN
    -> WITH cte(article_id) AS (
    ->     SELECT article_id
    ->     FROM articles
    ->     WHERE board_id = 500
    ->     ORDER BY article_id DESC
    ->     LIMIT 50 OFFSET 14950
    -> )
    -> SELECT
    ->     a.article_id AS articleId,
    ->     a.title AS title,
    ->
    ->     b.board_id AS boardId,
    ->     b.name AS boardName,
    ->     b.slug AS boardSlug,
    ->
    ->     ac.article_category_id AS articleCategoryId,
    ->     ac.name AS articleCategoryName,
    ->     ac.slug AS articleCategorySlug,
    ->
    ->     a.writer_id AS writerId,
    ->     a.writer_nickname AS writerNickname,
    ->
    ->     COALESCE(acc.comment_count, 0) AS commentCount,
    ->     COALESCE(alc.like_count, 0) AS likeCount,
    ->     COALESCE(adc.dislike_count, 0) AS dislikeCount,
    ->
    ->     a.created_at AS createdAt
    -> FROM articles a
    ->          JOIN cte ON a.article_id = cte.article_id
    ->          JOIN boards b ON b.board_id = a.board_id
    ->          JOIN article_categories ac ON ac.article_category_id = a.article_category_id
    ->          LEFT JOIN article_comment_counts acc ON acc.article_id = a.article_id
    ->          LEFT JOIN article_like_counts alc ON alc.article_id = a.article_id
    ->          LEFT JOIN article_dislike_counts adc ON adc.article_id = a.article_id;
+----+-------------+------------+------------+--------+---------------------------------+-------------------------+---------+-------------------------------------------+-------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys                   | key                     | key_len | ref                                       | rows  | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------------------------+-------------------------+---------+-------------------------------------------+-------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                            | NULL                    | NULL    | NULL                                      | 15000 |   100.00 | NULL        |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY,idx_board_id_article_id | PRIMARY                 | 8       | cte.article_id                            |     1 |   100.00 | NULL        |
|  1 | PRIMARY     | ac         | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | board_db_production.a.article_category_id |     1 |   100.00 | NULL        |
|  1 | PRIMARY     | acc        | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | cte.article_id                            |     1 |   100.00 | NULL        |
|  1 | PRIMARY     | alc        | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | cte.article_id                            |     1 |   100.00 | NULL        |
|  1 | PRIMARY     | adc        | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | cte.article_id                            |     1 |   100.00 | NULL        |
|  1 | PRIMARY     | b          | NULL       | eq_ref | PRIMARY                         | PRIMARY                 | 8       | board_db_production.a.board_id            |     1 |   100.00 | NULL        |
|  2 | DERIVED     | articles   | NULL       | ref    | idx_board_id_article_id         | idx_board_id_article_id | 8       | const                                     | 44248 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------------------------+-------------------------+---------+-------------------------------------------+-------+----------+-------------+
8 rows in set, 1 warning (0.01 sec)

실행계획은 기존과 별 차이가 없어보이는데...? 뭔 일이 있었던걸까?

mysql> EXPLAIN ANALYZE
    -> WITH cte(article_id) AS (
    ->     SELECT article_id
    ->     FROM articles
    ->     WHERE board_id = 500
    ->     ORDER BY article_id DESC
    ->     LIMIT 50 OFFSET 14950
    -> )
    -> SELECT
    ->     a.article_id AS articleId,
    ->     a.title AS title,
    ->
    ->     b.board_id AS boardId,
    ->     b.name AS boardName,
    ->     b.slug AS boardSlug,
    ->
    ->     ac.article_category_id AS articleCategoryId,
    ->     ac.name AS articleCategoryName,
    ->     ac.slug AS articleCategorySlug,
    ->
    ->     a.writer_id AS writerId,
    ->     a.writer_nickname AS writerNickname,
    ->
    ->     COALESCE(acc.comment_count, 0) AS commentCount,
    ->     COALESCE(alc.like_count, 0) AS likeCount,
    ->     COALESCE(adc.dislike_count, 0) AS dislikeCount,
    ->
    ->     a.created_at AS createdAt
    -> FROM articles a
    ->          JOIN cte ON a.article_id = cte.article_id
    ->          JOIN boards b ON b.board_id = a.board_id
    ->          JOIN article_categories ac ON ac.article_category_id = a.article_category_id
    ->          LEFT JOIN article_comment_counts acc ON acc.article_id = a.article_id
    ->          LEFT JOIN article_like_counts alc ON alc.article_id = a.article_id
    ->          LEFT JOIN article_dislike_counts adc ON adc.article_id = a.article_id;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=88463 rows=50) (actual time=4.94..5.74 rows=50 loops=1)
    -> Nested loop left join  (cost=76271 rows=50) (actual time=4.93..5.72 rows=50 loops=1)
        -> Nested loop left join  (cost=61937 rows=50) (actual time=4.93..5.54 rows=50 loops=1)
            -> Nested loop left join  (cost=47539 rows=50) (actual time=4.92..5.36 rows=50 loops=1)
                -> Nested loop inner join  (cost=33148 rows=50) (actual time=4.91..5.18 rows=50 loops=1)
                    -> Nested loop inner join  (cost=19393 rows=50) (actual time=4.9..5.12 rows=50 loops=1)
                        -> Table scan on cte  (cost=4560..4563 rows=50) (actual time=4.88..4.89 rows=50 loops=1)
                            -> Materialize CTE cte  (cost=4560..4560 rows=50) (actual time=4.88..4.88 rows=50 loops=1)
                                -> Limit/Offset: 50/14950 row(s)  (cost=4555 rows=50) (actual time=4.85..4.87 rows=50 loops=1)
                                    -> Covering index lookup on articles using idx_board_id_article_id (board_id=500)  (cost=4555 rows=44248) (actual time=0.021..4.27 rows=15000 loops=1)
                        -> Single-row index lookup on a using PRIMARY (article_id=cte.article_id)  (cost=0.988 rows=1) (actual time=0.00437..0.0044 rows=1 loops=50)
                    -> Single-row index lookup on ac using PRIMARY (article_category_id=a.article_category_id)  (cost=0.917 rows=1) (actual time=924e-6..959e-6 rows=1 loops=50)
                -> Single-row index lookup on acc using PRIMARY (article_id=cte.article_id)  (cost=0.959 rows=1) (actual time=0.00337..0.00339 rows=0.38 loops=50)
            -> Single-row index lookup on alc using PRIMARY (article_id=cte.article_id)  (cost=0.96 rows=1) (actual time=0.00341..0.00343 rows=0.44 loops=50)
        -> Single-row index lookup on adc using PRIMARY (article_id=cte.article_id)  (cost=0.955 rows=1) (actual time=0.00336..0.00338 rows=0.44 loops=50)
    -> Single-row index lookup on b using PRIMARY (board_id=a.board_id)  (cost=0.813 rows=1) (actual time=234e-6..271e-6 rows=1 loops=50)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

커버링 인덱스(인덱스를 통해서만 대상 행 결정)를 활용해 조건에 맞는 article_id 만 추출해냈고, 덕분에 불필요한 Disk I/O 를 없앴다.
Disk I/O 로 인한 병목이 사라져서 성능이 대폭 향상된 것이다.

한계

mysql> WITH cte(article_id) AS (
    ->     SELECT article_id
    ->     FROM articles
    ->     WHERE board_id = 1
    ->     ORDER BY article_id DESC
    ->     LIMIT 50 OFFSET 499950
    -> )
    -> SELECT
    ->     a.article_id AS articleId,
    ->     a.title AS title,
    ->
    ->     b.board_id AS boardId,
    ->     b.name AS boardName,
    ->     b.slug AS boardSlug,
    ->
    ->     ac.article_category_id AS articleCategoryId,
    ->     ac.name AS articleCategoryName,
    ->     ac.slug AS articleCategorySlug,
    ->
    ->     a.writer_id AS writerId,
    ->     a.writer_nickname AS writerNickname,
    ->
    ->     COALESCE(acc.comment_count, 0) AS commentCount,
    ->     COALESCE(alc.like_count, 0) AS likeCount,
    ->     COALESCE(adc.dislike_count, 0) AS dislikeCount,
    ->
    ->     a.created_at AS createdAt
    -> FROM articles a
    ->          JOIN cte ON a.article_id = cte.article_id
    ->          JOIN boards b ON b.board_id = a.board_id
    ->          JOIN article_categories ac ON ac.article_category_id = a.article_category_id
    ->          LEFT JOIN article_comment_counts acc ON acc.article_id = a.article_id
    ->          LEFT JOIN article_like_counts alc ON alc.article_id = a.article_id
    ->          LEFT JOIN article_dislike_counts adc ON adc.article_id = a.article_id;
+-----------+---------------+---------+---------------+---------------+-------------------+---------------------+---------------------+----------+----------------+--------------+-----------+--------------+---------------------+
| articleId | title         | boardId | boardName     | boardSlug     | articleCategoryId | articleCategoryName | articleCategorySlug | writerId | writerNickname | commentCount | likeCount | dislikeCount | createdAt           |
+-----------+---------------+---------+---------------+---------------+-------------------+---------------------+---------------------+----------+----------------+--------------+-----------+--------------+---------------------+
|  23000919 | title23000919 |       1 | board00000001 | board00000001 |                 3 | 정보                | tip                 | 11794916 | user11794916   |            1 |         1 |            0 | 2024-09-23 05:08:39 |
|  23000917 | title23000917 |       1 | board00000001 | board00000001 |                 1 | 일반                | general             |  2899684 | user02899684   |            2 |         0 |            1 | 2024-09-23 05:08:37 |
-- 생략
50 rows in set (13.42 sec)

아무리 커버링 인덱스를 활용한 쿼리로 개선한다 해도, page 가 계속 커지면 offset 까지 인덱스를 통해 접근하는 것 역시 성능상 한계가 있다.
위 쿼리는 1만번째 페이지에 접근하는 쿼리인데 13.42초나 걸린다.
- 만약 복수의 사용자가 악의를 가지고 높은 페이지를 반복해서 접근한다면?
- 커넥션을 한 스레드에서 오래 점유하게 되고 커넥션 고갈로 이어진다. -> 장애의 주 원인
그래서 page 번호 자체에 제약을 두기로 했다. (최대 100페이지)
- 사실 일반사용자 입장에서 대부분 게시판의 게시글은 최신 페이지 위주로 조회되고, 100페이지정도까지 도달해서 조회하는 사람은 극히 드물다.
- page 번호가 지나치게 높아지는 것은 정상적인 접근으로 판단하기 힘들다.

부하테스트

10분간 점진적으로 VUs 를 50까지 증가시켜봤는데, TPS 가 떨어지지 않는다.
CPU 사용률도 25% 전후고, RDS CPU 사용률도 7% 전후.

이번엔 10분간 점진적으로 VUs 를 100까지 증가. 그래도 TPS가 떨어지지 않는다.
CPU 사용률도 51.7%, RDS CPU 사용률 역시 11% 전후까지 도달.

이번엔 10분간 점진적으로 VUs 를 300까지 증가. VUs 224 쯤에서 TPS 값이 207.5 정도를 찍고, 이 시점의 latency는 206ms 정도를 보인다.
그 이후 구간에서는 점점 TPS 가 정체되고, latency도 1s 까지 증가한다.
최대 TPS 를 207.5 정도로 보면 될 것 같다. 이전 글에서, 인덱스를 추가하여 조회성능을 개선했을 당시 최대 tps가 20 이였던 것을 생각하면 동시요청 처리 성능이 10.375배 향상된 것이다!

EC2 의 CPU 사용률이 99.6%정도까지 치솟았다. 이제 병목지점은 EC2가 되며, RDS의 CPU 사용률은 21.4% 정도.
여기서 동시 사용자수를 더 늘리려면, EC2 인스턴스의 갯수를 수평확장하면 될 것 같다.

부하테스트 ‐ (6) 게시글 목록 페이지 조회 성능 개선 (게시글수 비정규화, 쿼리 튜닝) - ttasjwi/board-system GitHub Wiki

개요

기존 방식

문제 해결안

게시글 수 조회 수정

게시글 조회 수정

한계

부하테스트

⚠️ GitHub.com Fallback ⚠️

부하테스트 ‐ (6) 게시글 목록 페이지 조회 성능 개선 (게시글수 비정규화, 쿼리 튜닝) - ttasjwi/board-system GitHub Wiki

개요

기존 방식

문제 해결안

게시글 수 조회 수정

게시글 조회 수정

한계

부하테스트

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️