V2 게시물 리스트 API 결과 저장

애플리케이션 레벨 개선

k6 부하 테스트 결과

결과 디테일

결과 디테일 테이블

Metric	Avg	Min	Med	Max	P(90)	P(95)
Checks	100%	-	-	-	-	-
Data Received	756 MB	-	-	-	-	-
Data Received Size	7.82s	7.73s	7.83s	7.9s	7.9s	7.9s
Data Sent	34 MB	-	-	-	-	-
Dropped Iterations	0	-	-	-	-	-
HTTP Req Blocked	15.64µs	1µs	2.83µs	21.61ms	4.75µs	7.62µs
HTTP Req Connecting	10.67µs	0s	0s	21.59ms	0s	0s
HTTP Req Duration	7.65ms	2.13ms	2.95ms	375.77ms	9.36ms	22.04ms
HTTP Req Receiving	71.14µs	11.83µs	45.79µs	44.16ms	98.91µs	137.08µs
HTTP Req Sending	23.85µs	2.95µs	10.95µs	39.68ms	22.58µs	35.25µs
HTTP Req Waiting	7.55ms	2.04ms	2.88ms	375.73ms	9.19ms	21.71ms
HTTP Requests	90,000	-	-	-	-	-
Iteration Duration	1s	1s	1s	1.37s	1.01s	1.02s
Iterations	90,000	-	-	-	-	-
VUs (Virtual Users)	33	33	-	646	-	-
Max VUs	700	700	-	700	-	-

• k6 그라파나 시각화
• https://snapshots.raintank.io/dashboard/snapshot/k3Lo3EQqltmqU6aHXEXvaQZxmjBHzqwV

프로 파일링

플레임 그래프 Bubbleprof 그래프

데이터베이스 인덱스 설정

결과 디테일

결과 디테일 테이블

Metric	Avg	Min	Med	Max	P(90)	P(95)
Checks	100%	-	-	-	-	-
Data Received	771 MB	-	-	-	-	-
Data Received Size	7.82s	7.73s	7.83s	7.9s	7.9s	7.9s
Data Sent	35 MB	-	-	-	-	-
Dropped Iterations	0	-	-	-	-	-
HTTP Req Blocked	16.98µs	916ns	2.87µs	15.28ms	5.37µs	8.87µs
HTTP Req Connecting	11.07µs	0s	0s	15.25ms	0s	0s
HTTP Req Duration	7.85ms	2.11ms	2.99ms	307.39ms	10.55ms	30.04ms
HTTP Req Receiving	71.61µs	12.12µs	47.37µs	41.17ms	103.04µs	139.2µs
HTTP Req Sending	25.33µs	2.79µs	12.25µs	30.68ms	24.58µs	38.91µs
HTTP Req Waiting	7.76ms	2.06ms	2.92ms	307.29ms	10.41ms	29.73ms
HTTP Requests	91,801	-	-	-	-	-
Iteration Duration	1s	1s	1s	1.3s	1.01s	1.03s
Iterations	91,801	-	-	-	-	-
VUs (Virtual Users)	40	40	-	625	-	-
Max VUs	700	700	-	700	-	-

• k6 그라파나 시각화
• https://snapshots.raintank.io/dashboard/snapshot/kliihDbpdgTDTrAoYhRGl2kvzNaiAnVr

쿼리

// 메인 쿼리
WITH filtered_articles AS
(
         SELECT   id
         FROM     articles
         WHERE    id < ?
         ORDER BY id DESC limit ? )
SELECT     article.id           AS "articleId",
           article.title        AS "title",
           article.content      AS "content",
           article.starttime    AS "startTime",
           article.endtime      AS "endTime",
           article.articleimage AS "articleImage",
           article.createdat    AS "createdAt",
           article.updatedat    AS "updatedAt",
           member.id            AS "memberId",
           member.NAME          AS "memberName",
           member.nickname      AS "memberNickname",
           member.profileimage  AS "memberProfileImage",
           category.id          AS "categoryId",
           category.NAME        AS "categoryName",
           region.id            AS "regionId",
           region.NAME          AS "regionName",
           district.id          AS "districtId",
           district.NAME        AS "districtName"
FROM       filtered_articles fa
JOIN       articles article
ON         article.id = fa.id
INNER JOIN member
ON         member.id = article.memberid
INNER JOIN category
ON         category.id = article.categoryid
INNER JOIN region
ON         region.id = article.regionid
INNER JOIN district
ON         district.id = article.districtid
ORDER BY   article.id DESC

// 좋아요 개수
SELECT articleid,
       Count(*) AS "likeCount"
FROM   article_likes
WHERE  articleid IN ( ? )
GROUP  BY articleid 

// 내가 좋아요 한 게시글
SELECT DISTINCT articleid
FROM   article_likes
WHERE  memberid = ?
       AND articleid IN ( ? ) 

// 해당 게시글 모각밥 참여 개수
SELECT articleid,
       Count(*) AS "participantCount"
FROM   participation
WHERE  status = 'ACTIVE'
       AND articleid IN ( ? )
GROUP  BY articleid 

• 게시글 모각밥 참여 개수 쿼리 인덱스 변경

-> Group aggregate: count(0)  (cost=44.5 rows=42) (actual time=2.08..6.68 rows=8 loops=1)
    -> Filter: (participation.`status` = 'ACTIVE')  (cost=40.3 rows=42) (actual time=1.11..6.67 rows=14 loops=1)
        -> Index range scan on participation using IDX_dd9d5385352af57b512b9d4796 over (articleId = 29990) OR (articleId = 29991) OR (8 more), with index condition: (participation.articleId in (29999,29998,29997,29996,29995,29994,29993,29992,29991,29990))  (cost=40.3 rows=84) (actual time=0.638..6.59 rows=84 loops=1)

• Group aggregate: 2.08..6.68ms (전체 그룹화 및 COUNT 작업)
• Filter: 1.11..6.67ms (필터링 작업)
• Index range scan: 0.638..6.59ms (인덱스 스캔)

개선 방향

• status와 articleId 복합 인덱스 설정 추가

항목	인덱스 설정 이전	복합 인덱스 설정 후	개선 여부
Group aggregate 비용	44.5	5.06	✅ 대폭 개선
Filter 비용	40.3	3.46	✅ 대폭 개선
실제 실행 시간 (Group)	2.08ms ~ 6.68ms	0.186ms ~ 0.215ms	✅ 매우 빠름
실제 실행 시간 (Filter)	1.11ms ~ 6.67ms	0.168ms ~ 0.207ms	✅ 빠름
Index 활용 여부	단일 인덱스만 활용	복합 인덱스 활용	✅ 최적화

k6 부하 테스트 결과

결과 디테일

결과 디테일 테이블 k6 그라파나 시각화

프로 파일링

플레임 그래프 Bubbleprof 그래프

participations

• https://snapshots.raintank.io/dashboard/snapshot/byYaKD3zzyqfr86JoMxrF9FeoRYKadHi

결과 디테일 테이블

자세히 보기

Metric	Avg	Min	Med	Max	P(90)	P(95)
Checks	100%	-	-	-	-	-
Data Received	192 MB	-	-	-	-	-
Data Sent	51 MB	-	-	-	-	-
HTTP Req Blocked	23.28µs	0s	2.95µs	102.94ms	5.62µs	7.91µs
HTTP Req Connecting	18.19µs	0s	0s	102.88ms	0s	0s
HTTP Req Duration	5.72ms	349.95µs	1.92ms	250.45ms	6.73ms	15.19ms
HTTP Req Receiving	54.78µs	0s	38.45µs	38.75ms	84µs	115.5µs
HTTP Req Sending	23.01µs	2.83µs	11.29µs	33.51ms	25.25µs	37.54µs
Iterations	126001	-	-	-	-	-
Iteration Duration	1s	1.02ms	1s	1.32s	1s	1.01s
Max VUs	-	1000	-	1000	-	-

쿼리

SELECT `participation`.`id`        AS `participation_id`,
       `participation`.`memberid`  AS `participation_memberId`,
       `participation`.`articleid` AS `participation_articleId`,
       `participation`.`status`    AS `participation_status`,
       `participation`.`createdat` AS `participation_createdAt`
FROM   `participation` `participation`
WHERE  `participation`.`id` > 3
       AND `participation`.`articleid` = 23640
       AND `participation`.`status` = 'ACTIVE'
ORDER  BY `participation`.`id` ASC
LIMIT  11 

쿼리 실행 계획

-> Limit: 11 row(s)  (cost=1.93 rows=7) (actual time=9.13..9.13 rows=1 loops=1)
    -> Sort: participation.id, limit input to 11 row(s) per chunk  (cost=1.93 rows=7) (actual time=9.12..9.12 rows=1 loops=1)
        -> Filter: (participation.`status` = 'ACTIVE')  (cost=1.93 rows=7) (actual time=8.8..8.81 rows=1 loops=1)
            -> Index lookup on participation using IDX_dd9d5385352af57b512b9d4796 (articleId=23640), with index condition: (participation.id > 3)  (cost=1.93 rows=7) (actual time=8.71..8.72 rows=7 loops=1)

• Index Lookup: 8.71..8.72ms (인덱스 조회)
• Filter: 8.8..8.81ms (필터링 작업)
• Sort: 9.12..9.12ms (정렬 작업)
• Limit: 9.13..9.13ms (결과 제한)

개선 방향

• status와 articleId 복합 인덱스 설정 추가

항목	변경 전	변경 후	개선 여부
실행 비용 (cost)	cost=1.93 rows=7	cost=1.68 rows=1	✅ 감소
실행 시간 (Index)	8.71ms ~ 8.72ms	0.687ms ~ 0.701ms	✅ 대폭 감소
Filter/Sort 작업 시간	추가로 9.13ms	제거됨	✅ 제거
쿼리 최적화	일부 조건이 Filter 단계 처리	모든 조건이 인덱스 처리	✅ 최적화 완료