[mysql] 성능 최적화 2편 (트랜잭션)

트랜잭션 최적화

트랜잭션은 데이터의 일관성과 안전성을 보장하는 핵심 요소다. 특히 다수의 데이터를 처리할 때 트랜잭션 범위를 어떻게 설정하느냐가 성능에 큰 영향을 미친다.

 

하지만 다수의 쿼리를 하나의 트랜잭션으로 처리하는 것이 반드시 성능 향상으로 이어질까? 한 트랜잭션에서 여러 쿼리를 처리하면 커밋이나 롤백이 발생하기 전까지 모든 변경사항이 보류된다. 그동안 다른 트랜잭션들이 해당 데이터에 접근하지 못하고 기다려야 하므로 트랜잭션의 처리 속도가 감소할 수 있다.

🚨 대량의 행을 삽입/수정/삭제한 후 롤백을 실행하는 것은 성능을 저하시킬 수 있다. 대규모 데이터 변경이 필요한 경우, 주기적으로 커밋을 실행하거나 작은 크기의 트랜잭션으로 나누는 것이 좋다. 

한편, 각각의 쿼리를 개별적인 트랜잭션으로 처리하면 트랜잭션의 시작과 끝을 처리하는 오버헤드가 발생한다. 이뿐만 아니라 락을 걸고 해제하는 작업도 늘어나게 된다. 따라서 적절한 트랜잭션의 범위를 설정하는 것이 아주 중요하다.

 

이번 포스팅에서는 트랜잭션 최적화의 일부만 다룰 것이다. 대규모 트랜잭션 처리에 대한 자세한 내용은 여기를 확인하는 것을 추천한다.

 

AUTOCOMMIT이란?

`AUTOCOMMIT`은 sql문 실행 후에 커밋 작업을 수행하는 설정이다. `AUTOCOMMIT`이 활성화되어 있는 경우, 각 sql문은 독립적인 트랜잭션으로 간주되어 실행될 때마다 자동으로 커밋된다. 반면, `AUTOCOMMIT`이 비활성화된 경우 여러 sql문을 하나의 트랜잭션으로 수행할 수 있다. 이 경우 모든 sql문이 모두 성공했을 때만 변경사항이 데이터베이스에 반영된다.

🚨 mysql은 `AUTOCOMMIT=1`로 기본 설정되어 있다. 이로 인해 sql문이 트랜잭션으로 취급되어 성능에 영향을 미칠 수 있다. 그러므로 `SET AUTOCOMMIT=0`이나 `START TRANSACTION`문을 사용하여 연관된 작업을 하나의 트랜잭션으로 묶어주는 것이 권장된다.

 

트랜잭션으로 연관된 작업 묶어주기

연관된 쿼리를 하나의 트랜잭션으로 묶어 일관성을 보장하고 트랜잭션 시작과 끝에 발생하는 오버헤드를 줄여줄 것이다. 다음 코드는 한 번에 여러 개의 쿼리를 일괄로 실행하는 작업이다. 하지만 트랜잭션을 사용하지 않아 각각의 `save` 작업이 개별적인 트랜잭션으로 처리된다. 이로 인해 각각의 작업이 독립적으로 실행되어 일관성이 보장되지 않고, 불필요한 오버헤드가 발생한다. 실행 시 약 395 밀리초가 소요된다.

async saveMessages(
  room: ChattingRoom,
  createMessageDtos: CreateChattingMessageDto[],
): Promise<void> {
  const startTime = new Date();
  for (const createMessageDto of createMessageDtos) {
    const message: ChattingMessage = ChattingMessage.fromDto(
      createMessageDto,
      room,
    );
    await this.chattingMessageRepository.save(message);
  }
  const endTime = new Date();
  const diff = endTime.getTime() - startTime.getTime();
  console.log(`findOneBy execution time: ${diff} milliseconds`);
}

[QUERY]: START TRANSACTION
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ? 
[QUERY]: COMMIT
[QUERY]: START TRANSACTION
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ? 
[QUERY]: COMMIT
[QUERY]: START TRANSACTION
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ?
[QUERY]: COMMIT
[QUERY]: START TRANSACTION
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ?
[QUERY]: COMMIT
[QUERY]: START TRANSACTION
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ? 
[QUERY]: COMMIT
[QUERY]: START TRANSACTION
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ? 
[QUERY]: COMMIT

이를 해결하기 위해 `EntityManager`의 트랜잭션을 사용하여 모든 쿼리를 하나의 트랜잭션으로 묶었다. 모든 쿼리가 성공하면 커밋되고, 하나라도 실패하면 롤백하도록 수정했다. 트랜잭션을 활용함으로써 약 140밀리초가 줄어들었다.

async saveMessages(
  room: ChattingRoom,
  createMessageDtos: CreateChattingMessageDto[],
): Promise<void> {
  return this.entityManager.transaction(async (manager) => {
    try {
      const startTime = new Date();
      const messages: ChattingMessage[] = createMessageDtos.map(
        (createMessageDto: CreateChattingMessageDto): ChattingMessage =>
          ChattingMessage.fromDto(createMessageDto, room),
      );
      await manager.save(ChattingMessage, messages);
      const endTime = new Date();
      const diff = endTime.getTime() - startTime.getTime();
      console.log(`findOneBy execution time: ${diff} milliseconds`);
    } catch (err: unknown) {
      throw err;
    }
  });
}

[QUERY]: START TRANSACTION
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ? 
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ? 
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ?
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ?
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ? 
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?) 
[QUERY]: SELECT `ChattingMessage`.`id` AS `ChattingMessage_id`, `ChattingMessage`.`created_at` AS `ChattingMessage_created_at`, `ChattingMessage`.`updated_at` AS `ChattingMessage_updated_at` FROM `chatting_message` `ChattingMessage` WHERE `ChattingMessage`.`id` = ? 
[QUERY]: COMMIT

아까와 달리 모든 쿼리가 끝난 후에 커밋이 이루어지는 것을 확인할 수 있다. 하지만 `INSERT` 후에 불필요한 `SELECT`가 발생하고 있다. 이는 TypeOrm의 `save` 때문이다. `save`는 `INSERT` 후에 `SELECT`를 실행하여 삽입한 엔티티를 반환한다. `SELECT`가 불필요한 경우 `INSERT` 메서드를 사용하여 불필요한 조회 작업을 하지 않아야 한다. 아래는 `INSERT`로 바꾼 후의 쿼리 로그다.

[QUERY]: START TRANSACTION
[QUERY]: INSERT INTO `chatting_message`(`id`, `is_host`, `message`, `created_at`, `updated_at`, `room_id`) VALUES (?, ?, ?, DEFAULT, DEFAULT, ?), (?, ?, ?, DEFAULT, DEFAULT, ?), (?, ?, ?, DEFAULT, DEFAULT, ?), (?, ?, ?, DEFAULT, DEFAULT, ?)
[QUERY]: COMMIT

트랜잭션 적용 전 / save 사용	트랜잭션 적용 후 / save 사용	트랜잭션 적용 후 / insert 사용
약 395 밀리초	약 255 밀리초	약 89 밀리초

 

리드 온리 트랜잭션 최적화하기

리드 온리 트랜잭션은 말 그대로 데이터베이스에서 읽기 작업만 수행하는 트랜잭션을 의미한다. 읽기 작업은 단순히 현재 데이터베이스의 상태를 읽어오기 때문에 해당 작업이 다른 작업에 영향을 미치지 않는다.

 

트랜잭션 ID*는 일관성과 격리를 관리하기 위해 사용된다. 여러 쿼리를 한 번에 수행하는 트랜잭션에서 트랜잭션 ID는 한 작업이 실패할 경우 롤백할 수 있는 기능을 제공한다. 하지만 읽기 작업은 데이터베이스를 수정하지 않기 때문에 롤백이 필요하지 않으므로 트랜잭션 ID가 필요없다.

*트랜잭션 ID : 트랜잭션이 시작될 때 자동으로 할당되어 롤백 시 해당 아이디를 가진 모든 작업의 결과를 버린다.

불필요한 트랜잭션 ID를 제거함으로써 버퍼 풀에 저장되는 데이터 양을 줄일 수 있다. 이는 캐싱 영역에서 자주 사용되는 영역이 사라지는 경우를 방지하여 데이터베이스 성능을 향상시킨다.

 

`START TRANSACTION READ ONLY`는 데이터베이스에서 읽기 전용 트랜잭션을 시작하는 데 사용된다. 이 명령을 사용하면 해당 트랜잭션 동안 데이터베이스의 내용을 읽을 수만 있고 변경할 수 없다. 만약 해당 트랜잭션에서 데이터를 변경하려고 하면 오류가 발생한다.

 

InnoDB는 repeatable read 격리 수준을 사용하기 때문에 `SELECT`문에 락을 걸지 않는다.* 따라서 InnoDB를 사용하고 있고 설정을 수정하지 않았다면 리드 온리 트랜잭션에 대한 별도의 처리가 필요하지 않다.

*repeatable read는 읽은 데이터를 고정시키므로 (스냅샷) 잠금을 사용하지 않는다.

---

참고자료

• https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-transaction-management.html
• https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-ro-txn.html