23-01-30 부하 테스트

[kimdev0206]

로컬 환경 (사용가능한 메모리: 1GB)에 부하 테스트를 통해 데드락을 감지하였다.

2023-01-30 06:44:44 ｜ warn: [History]_Deadlock found when trying to get lock; try restarting transaction  

• 부하는 3단계로 이루어진다 (코드 확인하기).

  1단계는 1초 동안 1명의 가상 사용자로 시작하며
  2단계는 10초 동안 5명의 가상 사용자를
  3단계는 20초 동안 10명의 가상 사용자를 정점으로 한다. 

• 다음은 데드락으로 인해, 10개 요청을 처리하지 못한 지표이다.

  All VUs finished. Total time: 32 seconds
  
  --------------------------------
  Summary report @ 15:49:46(+0900)
  --------------------------------
  
  http.codes.200: ................................................................ 1506
  http.codes.201: ................................................................ 241
  http.codes.500: ................................................................ 10

InnoDB 로그를 통해 경합이 되는 UPDATE 쿼리를 발견하였다. 단, 쿼리 간에 읽기조건 및 쓰기대상 모두 경합이 될 수 없다.

UPDATE 쿼리 수행 이후 트리거에 사용하는 쿼리는 경합이 될 수 있다. 그러면, 트리거 내부 쿼리도 감지할 수 있는지 커뮤니티에 물어보았다. 원하는 답변은 받지 못했지만, 데드락 해결에 관한 조언을 받을 수 있었다.

내용을 요약하면 다음과 같다.

1. 필자는 경합의 대상은 2가지 경우가 있다고 판단한다.

   • 경우A: 첫번째 쿼리의 UPDATE 쓰기대상인 question_option_seq가 두번째 쿼리의 SELECT 읽기조건이 된다. 즉, quiz_user_option 테이블에서 경합이 발생한다.
   • 경우B: 첫번째 쿼리의 UPDATE 쓰기대상인 new_score 간에 user_leaderboard테이블에서 경합이 발생한다.

   INSERT INTO quiz_user_option
       (question_seq, question_option_seq, user_seq, chapter_seq)
     VALUES
   (16, 63, 6, 31),(35, 266, 6, 31),(36, 268, 6, 31)
     ON DUPLICATE KEY UPDATE
       question_seq = VALUES(question_seq),
       question_option_seq = VALUES(question_option_seq),
       user_seq = VALUES(user_seq),
       chapter_seq = VALUES(chapter_seq);

   TRIGGER `quiz_user_option_update` AFTER UPDATE ON `quiz_user_option` 
   FOR EACH ROW BEGIN
       DECLARE new_score INT;
       ...
       SET new_score = (
           SELECT 
             COUNT(*) AS hit_count
           FROM quiz_question_option AS qo
           INNER JOIN quiz_user_option AS uo
             ON qo.question_option_seq = uo.question_option_seq    
           WHERE qo.answer_yn = 1
             AND uo.user_seq = NEW.user_seq
       ) * 100;
       ...
       UPDATE user_leaderboard
           SET quiz_score = new_score
       WHERE user_seq = NEW.user_seq;
   END

2. 조언자는 인덱스 조합을 알려주었다. quiz_user_option 테이블의 락 범위를 줄이기 위한 의도였다고 판단한다.

   _quiz_user_option 테이블의 user_seq + chapter_seq + questionseq 순서로 일반 인덱스 만들어주세요.

   _quiz_question_option 테이블의 question_option_seq + answeryn 순서로 일반 인덱스 만들어주세요.

3. 인덱스 조합 조언은 그대로 수행하였지만, 데드락은 해결하지 못했다.

4. 현재로서 이 일련의 쿼리들을 WAS로 옮겨 트랜잭션 처리하였다. (코드 확인하기)

다음은 조치 이후 진행한 부하테스트에서 요청을 모두 처리한 지표이다.

All VUs finished. Total time: 20 seconds

--------------------------------
Summary report @ 16:09:03(+0900)
--------------------------------

http.codes.200: ................................................................ 1506
http.codes.201: ................................................................ 251

---

• 질문 및 조언 확인하기
• ERD 확인하기
• InnoDB 로그 확인하기

[kimdev0206]

이관된 DBMS 플랫폼에서 트리거를 지원하지 않기 때문에, 결론적으로 WAS에서 트랜잭션 처리를 할 수 밖에 없다.

DBMS 이관이유 확인하기

하지만, 조언자 의도대로 복합 인덱스로 락에 검사되는 행 수를 줄이는 경험을 꼭 하고 싶었다.

InnoDB 로그를 다시 읽어보니, 원인은 경우A임을 확신했다.

_경우A: 첫번째 쿼리의 UPDATE 쓰기대상인 question_option_seq가 두번째 쿼리의 SELECT 읽기조건이 된다. 즉, quiz_user_option 테이블에서 경합이 발생한다._

-----------------------
LATEST DETECTED DEADLOCK
------------------------
2023-02-06 20:14:17 0x7f58ed046700
*** (1) TRANSACTION:
TRANSACTION 8857, ACTIVE 0 sec fetching rows
mysql tables in use 9, locked 9
LOCK WAIT 8 lock struct(s), heap size 1128, 120 row lock(s), undo log entries 1
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52 page no 3 n bits 400 index PRIMARY of table `withbible`.`quiz_user_option` trx id 8857 lock mode S waiting
...
*** (2) TRANSACTION:
TRANSACTION 8858, ACTIVE 0 sec fetching rows
mysql tables in use 9, locked 9
8 lock struct(s), heap size 1128, 82 row lock(s), undo log entries 1
...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 52 page no 3 n bits 400 index PRIMARY of table `withbible`.`quiz_user_option` trx id 8858 lock_mode X locks rec but not gap
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 52 page no 3 n bits 400 index PRIMARY of table `withbible`.`quiz_user_option` trx id 8858 lock mode S waiting
...

[kimdev0206]

조언자는 복합 인덱스를 카디널리티가 높은 컬럼을 우선순위로 조합했다고 판단한다. 카디널리티가 높으면 중복이 낮기 때문에, 데이터가 존재하는 리프 노드에 더 빨리 탐색할 수 있기 때문이다.

• quiz_user_option 테이블의 카디널리티 지표이다.

  COUNT(DISTINCT question_seq)|
  ----------------------------+
                          80|
  
  COUNT(DISTINCT user_seq)|
  ------------------------+
                      31|
  
  COUNT(DISTINCT chapter_seq)|
  ---------------------------+
                         30|

• 다음은 quiz_question_option 테이블의 카디널리티 지표이다.

  COUNT(DISTINCT question_option_seq)|
  -----------------------------------+
                                316|
  
  COUNT(DISTINCT answer_yn)|
  -------------------------+
                        2|

(토글 확인하기) 카디널리티는 다음과 같은 쿼리로 판별했다.

```sql SELECT COUNT(DISTINCT column_name) FROM table_name; ```

(토글 확인하기) 트리거 내부 쿼리는 다음과 같다.

```sql DROP TRIGGER IF EXISTS quiz_user_option_update; DELIMITER // CREATE TRIGGER quiz_user_option_update AFTER UPDATE ON quiz_user_option FOR EACH ROW BEGIN DECLARE new_score INT; DECLARE new_active_question_count TINYINT; DECLARE new_hit_question_count TINYINT; SET new_score = ( SELECT COUNT(*) AS hit_count FROM quiz_question_option AS qo INNER JOIN quiz_user_option AS uo ON qo.question_option_seq = uo.question_option_seq WHERE qo.answer_yn = 1 AND uo.user_seq = NEW.user_seq ) * 100; SET new_active_question_count = ( SELECT COUNT(uo.question_option_seq) FROM quiz_user_option AS uo GROUP BY uo.chapter_seq, uo.user_seq HAVING uo.chapter_seq = NEW.chapter_seq AND uo.user_seq = NEW.user_seq ); SET new_hit_question_count = ( SELECT COUNT(uo.question_option_seq) FROM quiz_question_option AS qo INNER JOIN quiz_user_option AS uo ON qo.question_option_seq = uo.question_option_seq WHERE qo.answer_yn = 1 GROUP BY uo.chapter_seq, uo.user_seq HAVING uo.chapter_seq = NEW.chapter_seq AND uo.user_seq = NEW.user_seq ); UPDATE user_leaderboard SET quiz_score = new_score WHERE user_seq = NEW.user_seq; UPDATE quiz_chapter_user_state SET active_question_count = new_active_question_count, hit_question_count = IFNULL(new_hit_question_count, 0) WHERE chapter_seq = NEW.chapter_seq AND user_seq = NEW.user_seq; END // DELIMITER ; ```

이제, 동일한 부하를 적용해보자.

적용 전	적용 후
``` (1차 시도) http.codes.500: ................................. 30 (2차 시도) http.codes.500: ................................. 14 (3차 시도) http.codes.500: ................................. 21 ```	``` (1차 시도) http.codes.500: ................................. 11 (2차 시도) http.codes.500: ................................. 11 (3차 시도) http.codes.500: ................................. 11 ```

효과는 없었다.

[kimdev0206]

트리거 내부 quiz_user_option 테이블에 읽기작업을 하는 모든 쿼리에 LOCK IN SHARE MODE를 설정하였다. 이를 통해 트리거 실행 중인 세션에서 변경 중인 데이터에 대해, 다른 세션에서 일관된 읽기를 수행할 수 있도록 의도하였다.

적용 전	적용 후
``` (1차 시도) http.codes.500: ................................. 16 (2차 시도) http.codes.500: ................................. 18 (3차 시도) http.codes.500: ................................. 28 ```	``` (1차 시도) http.codes.500: ................................. 27 (2차 시도) http.codes.500: ................................. 26 (3차 시도) http.codes.500: ................................. 26 ```

효과는 없었다.

[kimdev0206]

애초에 부하 테스트 시나리오가 잘못된 것을 늦게나마 깨달았다. 현재 다수의 가상 사용자는 모두 동일한 계정 및 동일한 세션을 사용한다.

현실에서 동일한 계정 및 동일한 세션에서 30초에 1700개가 넘는 요청은 DoS 공격이 아닌 이상 절대 이루어지지 않는다. 동일한 계정 및 다른 세션인 경우도 마찬가지이다.

만에 하나의 상황을 대비한다면 중복 로그인을 허용하지 않으면 된다.

따라서, 부하 테스트 시나리오에 다수의 가상 사용자를 모두 다른 계정을 사용하도록 설정해야한다.

https://github.com/WithBible/withbible-server/issues/9#issue-1584429863 이슈에도 동일한 태스크가 있다.

그러면, 데드락을 발생하는 2개의 쿼리가 부하 테스트만큼의 짧은 시간 안에 수행하지 않기 때문에 경합이 발생하지 않을 것이다.