DB CPU가 터져요 (1편)

우리 서버는 어느 정도의 트래픽을 버틸 수 있을까요?

 

이를 검증해보기 위해, 더미데이터를 넣고 부하테스트를 진행해 보았습니다. 작성한 시나리오는 다음과 같습니다.

• 하루에 3개의 조직이 생겨나고, 각각의 조직에서 20개의 피드백이 생성된다.
• 일 년동안 상단의 데이터가 쌓인다.

이를 토대로 약 1,000개의 조직과 400만 개의 피드백을 더미데이터로 사용하였습니다.

 

처음 테스트한 API는 피드백 조회 쿼리였습니다. 특정 조직의 피드백을 최신순/오래된 순/좋아요 순의 조건을 통해 10개 가져오는 API입니다. JPQL로 작성한 쿼리문이지만, SQL로 변환하면 다음과 같은 쿼리가 나갑니다. (쿼리는 중요하지 않기 때문에 읽어보지 않아도 됩니다.)

 

최신순

select f1_0.*
from feedback f1_0
join organization o1_0 
  on o1_0.id=f1_0.organization_id and o1_0.deleted_at IS NULL
where o1_0.uuid=0x009BCFCAECF247D8A51C1737D2E90ADF
  and f1_0.deleted_at IS NULL
  and f1_0.status='WAITING'
  and f1_0.id < 4000000
order by f1_0.id desc
limit 10;

 

오래된순 (생략/ 최신순과 비슷)

 

좋아요 순 (생략/ 서브 쿼리가 나가지만, 해당 포스팅의 핵심이 아님)

 

이 쿼리문에 대해서 부하테스트를 진행해 보았는데요, VUs 100~120 정도까지는 감당할 수 있지만, 이후의 요청에서 RDS에서 부하가 발생했습니다.

 

 

 

VUs 약 140쯤부터, CPU가 80%를 돌파하는것을 확인할 수 있었습니다. (RDS 스펙은 t3.micro입니다.)

 

18:01:00 후반부터 초록색 수치 (응답속도 500ms 이하)가 줄어들더니, 18:01:30 이후부터는 초록색 수치가 눈에 띄게 줄어든 모습을 확인할 수 있습니다.  (18:01:00 ~ 18:01:30 사이가 기록이 안 되었는데요, 사실 다시 측정을 해봤어야 했는데... 귀찮아서 안 했습니다.)

 

우선, RDS CPU 사용량이 90%를 넘어섰기 때문에, 과부하로 인한 성능 저하라고 생각했습니다. 과부하가 온 원인은, 많은 요청 때문이라고 생각했습니다.

 

이후 두 번째 API 테스트를 진행해봤습니다. Uuid 값으로 조직을 조회하는 아주 단순한 쿼리문입니다. 실제로 날아가는 걸 확인해보진 않았지만, JPA의 기본 메서드이기 때문에 다음과 같은 쿼리가 날아갈 것 같네요.

SELECT *
FROM Organization o
WHERE o.uuid = uuid값;

 

아래는 성능 테스트 측정 결과입니다.

 

이전보다 더 많은 요청을 날려보았는데요, 초록색이 아주 예쁘게 정돈되어 있는 모습을 확인할 수 있습니다.

 

결과를 보니, 단순히 많은 요청 때문에 DB CPU가 과부화가 온 것이 아니구나라는 것을 알 수 있었습니다. 만약 많은 요청 때문이라면 두 번째 API도 똑같이 과부하가 왔었을 것입니다. 이 결과를 보고 RDS CPU 과부하의 원인을 추측할 수 있었는데요,

 

1) 쿼리문이 복잡해서

첫 번째 쿼리문은 join, where, orderBy 를 사용하는 쿼리문이고, 두 번째 쿼리문은 where로 조회하는 단순 쿼리문이었습니다. 즉, 쿼리문의 복잡성 차이로 인한 RDS CPU 사용량 차이가 났구나 라는 하나의 가설을 세울 수 있었습니다.

 

2) 인덱스를 안 타서

400만개의 테이블에서 full scan이 일어나면 당연히 CPU를 많이 사용하겠다고 판단했고, 실행계획을 분석하며 적절한 인덱스를 설정해 본 다음에, 다시 부하테스트를 진행해 봤습니다.

 

실행 계획

+----+-------------+-------+------------+-------+------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                                                                                              | key                   | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | o1_0  | NULL       | const | PRIMARY,organization_uuid                                                                                  | organization_uuid                   | 16      | const       |    1 |   100.00 | Using filesort                     |
|  1 | SIMPLE      | f1_0  | NULL       | ref   | PRIMARY,FK_organization_id,idx_organization_id_status_like_count_desc_id_asc,idx_organization_id_status_id | idx_organization_id_status_like_count_desc_id_asc | 9       | const,const |  627 |     5.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+------------------------------------------------------------------------------------------------------------+---------------------------------------------------+---------+-------------+------+----------+------------------------------------+

 

Explain Analyze

| -> Limit: 10 row(s)  (cost=558 rows=10) (actual time=2.71..2.71 rows=10 loops=1)
    -> Sort: f1_0.id, limit input to 10 row(s) per chunk  (cost=558 rows=627) (actual time=2.71..2.71 rows=10 loops=1)
        -> Filter: (f1_0.deleted_at is null)  (cost=558 rows=627) (actual time=0.147..2.49 rows=627 loops=1)
            -> Index lookup on f1_0 using idx_organization_id_status_like_count_desc_id_asc (organization_id='912', status='WAITING'), with index condition: ((f1_0.`status` = 'WAITING') and(f1_0.id > 100000))  (cost=558 rows=627) (actual time=0.145..2.44 rows=627 loops=1)

 

사실 기존에도 FK 값 때문에 full scan이 일어나지 않고 있었고, 많은 인덱스를 걸어도 의미 있는 수치 변경이 나타나지는 않았습니다. 속도가 20ms 정도 빨라졌고, 여전히 RDS CPU에서 과부하가 일어나고 있었습니다. 

 

3) 데이터양이 많아서

마지막은 데이터의 양 차이인데요, Organization 조회 API의 경우에는 1,000개의 데이터에서 결괏값을 찾아야 하고, Feedback 조회 API의 경우에는 400만 개의 데이터에서 찾아야 합니다.

 

문제를 검증하기 위해서 피드백 데이터의 양을 400만 개 -> 10만 개로 줄여서 부하테스트를 진행했습니다. 아래는 10만개로 데이터를 변경한 이후의 부하테스트 결과인데요, InfluxDB의 데이터 수집 문제로 중간중간 데이터가 수집이 되지 않았지만 중요한 값은 살펴볼 수 있었습니다.

 

 

 

VUs 400~500명일 때도 초록색 (응답속도 500ms 이하)가 많이 보입니다.

 

데이터의 양을 줄이니 RDS CPU가 안정화되는 모습을 확인할 수 있었습니다. VUs가 800일 때도 RDS CPU 사용량이 40%밖에 되지 않는다는 걸 확인할 수 있었습니다. 이후 속도가 느려지는 부분은 RDS 과부하가 아닌, DB Connection pool 부분에서의 병목으로 인해 느려진 것으로 추측됩니다. (물론 테스트 결과가 끊겨있기 때문에 정확하진 않습니다. 이 문제를 해결하려면 다시 테스트를 해봐야 할 것 같네요.)

 

테스트 결과가 끊겨있지만, 얻고자 하는 결과는 얻을 수 있었습니다. RDS CPU 사용량은 쿼리문이 복잡한 것도 영향이 있겠지만, 데이터양이 많기 때문에 발생한 일이라고 추측할 수 있었습니다.

---

인덱스를 걸면 디스크에 데이터가 많아도 상관없는 거 아닌가요?

제가 그렇게 생각했습니다. 사실 이 부분이 이해가 잘 되지 않아 글을 작성하게 되었습니다. 이해를 하기 위해서 인덱스의 동작 원리에 대해 설명해 보겠습니다.

 

우리가 인덱스를 건다고 하면, 별도의 인덱스 테이블이 생성이 됩니다. 이 과정에서 클러스터링 인덱스에 400만 개의 데이터가 존재한다고 하면, 세컨더리 인덱스 테이블에도 마찬가지로 400만개의 데이터가 존재하는 인덱스 테이블이 생기게 됩니다.

별도의 인덱스 테이블 생성

 

세컨더리 인덱스 테이블은 key 값을 기준으로 정렬이 되는데요, 정렬이 되어 있기 때문에 보다 빠르게 원하는 값을 찾을 수 있는 것이죠. 추가적으로 I/O 작업이 발생했을 때 MySQL 서버에서는 어떤 작업이 일어나는지 조금 더 자세히 알아볼까요?  클러스터링 인덱스에 2000개의 레코드가 저장되어 있다고 가정해 보겠습니다.

 

상단의 그림에서 클러스터링 인덱스, 세컨더리 인덱스를 간단하게 그렸는데, 이번에는 자세하게 그려봤습니다.

클러스터링 인덱스는 B+Tree 형태로 저장이 되어있는데요, B+Tree에 대해 자세히 알고 싶으신 분들이 계시다면, 망나니개발자님의 글을 읽어보는 것을 추천드립니다.

위에서 언급했다시피, 세컨더리 인덱스는 key 값 기준으로 정렬이 되어있습니다. 그리고 B+Tree 알고리즘을 사용하기에 각각의 리프노드들이 서로 연결되어 있는 모습을 확인할 수 있습니다.

 

이제 I/O 작업이 들어왔을 때, MySQL 서버에서는 어떤 과정을 거치는지 간략하게 알아봅시다.

 

지금까지 위의 사진에서 보았던 세컨더리 인덱스, 클러스터링 인덱스를 한눈에 살펴볼 수 있도록 그려놨습니다. 

 

I/O 작업이 들어왔을 때 그림과 같은 작업 순서를 확인할 수 있습니다.

 

아래 그림에서 중요하게 봐야 할 점은 세컨더리 인덱스, 클러스터링 인덱스 전부 InnoDB 버퍼풀에 올라온다는 점입니다. 물론, 커버링 인덱스는 제외입니다. 커버링 인덱스의 경우에는 빨간 부분을 아예 실행을 안 하기 때문에 더 빠른 성능을 낼 수 있습니다.

 

동작 과정을 직접 그려보니, 데이터가 많아지면 인덱스를 걸어도 CPU 사용량이 늘어나는 이유를 추측할 수 있었습니다.

 

물론 잘 알지 못하는 복합적인 요인 또한 존재하겠지만, 버퍼풀의 관리 비용이 증가하는 게 가장 큰 이유지 않나 싶습니다. 구체적으로는 읽고 관리해야 하는 페이지 수가 많아지는 게 문제인데요, 페이지로 용량이 가득 차면 기존 페이지를 삭제하고 새로운 페이지를 버퍼풀에 등록을 하는 일이 데이터가 적을 때보다 많이 일어나겠다고 생각했고, 이로 인해 CPU 사용량이 늘어난다고 추측할 수 있었습니다.

---

그렇다면, DB CPU 부하를 어떻게 해결해야 할까?

는 다음 포스팅에서 마저 설명해보도록 하겠습니다ㅎ 

 

긴 글 읽어주셔서 감사합니다. 글에 잘못된 내용이 있다면, 편하게 댓글 달아주시면 감사하겠습니다.