[MySQL] INSERT optimization(최적화 전략)

💡 July님의 200억건의 데이터를 MySQL로 마이그레이션 할 때 고려했던 개념과 튜닝 방법 강의를 듣고 정리한 내용입니다.

 

목차

배경

실무에서 개발을 하다보면 배치 작업을 비롯해서 대량의 데이터에 대해 일괄로 INSERT 작업이나 UPDATE 작업을 수행하게 된다. 이 또한 데이터가 많아지면 DB에 부하가 발생하고 배치가 실패하기도 한다. 결국 DB Connection이 들어가는 모든 데이터베이스 관련 작업은 고민을 하고 주의깊게 사용할 필요가 있는데, 이번에는 INSERT 쿼리를 사용할 때 DB 부하를 줄일 수 있는 최적화 방법을 알아본다.

서버 부하

본문에 들어가기에 앞서 서버 개발자는 항상 DB와 서버 부하에 대해서 고려해야한다. 일반적으로 서버에서 발생하는 부하는 데이터베이스에 이전하기 보다는 서버에서 감당하는 것이 좋다. DB 서버 자체가 비용이 비싼 장비이기도 하고, 일반적으로 고도화된 MSA 환경이 아니라면 하나의 DB에 여러대의 서버가 붙는다. (1:N 관계) 따라서 하나의 DB에 부하를 많이 줘서 CPU를 많이 사용하면, DB 서버의 장애는 전체 서비스 혹은 여러 서비스에 전파될 수 있다. 이러한 이유로 DB에서는 가능한 간단한 데이터 조회, 수정, 삭제 작업만 하고 복잡한 비즈니스 로직은 서버에서 감당하는 것이 좋다. 같은 이유로 INSERT 작업 또한 DB에 가능한 부하를 주지 않도록 최적화할 필요가 있다.

UPSERT

기본적으로 INSERT를 수행하는데 일부 조건에 따라 INSERT가 아닌 UPDATE를 수행해야하는 경우 어떤 방안이 있을까? Oracle의 경우 MERGE INTO 구문을 사용할 수 있다. Mongo DB의 경우에는 Upsert 쿼리를 주로 사용한다. 예를 들어 1주일전에 데이터가 잘못 집계되어 1주일 전 데이터에 대해 수집 모듈을 재기동해야하는 경우가 발생한 경우가 발생하는 경우, INSERT 쿼리를 작성하면, 데이터 정합성에 문제가 생기거나 중복된 데이터가 들어갈 수 있어 UPSERT(UPDATE + INSERT) 쿼리를 사용한다. 하나의 DB 커넥션 내에서 두 개의 쿼리가 실행되므로 두 개의 DB 커넥션을 각각 사용하는 것보다는 리소스가 절약된다.

 

MySQL의 경우에는 ON DUPLICATE KEY UPDATE을 지정한다. 이는 Upsert와 유사한 동작을 하는데, 데이터 삽입 시 PRIMARY KEY나 UNIQUE KEY가 중복되었을 경우 지정한 데이터만 UPDATE한다.

INSERT 쿼리를 사용할 때 DB 부하를 줄일 수 있는 방법

본격적으로 INSERT 쿼리 최적화 방안에 대해서 알아본다.

DB Connection 줄이기 - Bulk Insert

첫번째 방법으로는 DB Connection 자체를 줄이는 방안이 있다. DB에 Connection을 맺는 비용은 네트워크 요청까지 포함되어 있어 생각보다 큰데, 이 비용 자체를 절약하는 것이다. 구체적인 구현 방법으로는 Bulk Insert가 있다.

 

예를 들어 다음과 같이 행마다 INSERT 쿼리를 호출하는 경우 여러개의 DB Connection을 사용하므로 오버헤드가 발생한다.

INSERT INTO <table> (one, two, three)
VALUES ("a", "b", "c")
;

INSERT INTO <table> (one, two, three)
VALUES ("t", "ta", "tb")
;

...

 

 

하지만 Bulk Insert를 사용하여 하나의 Insert 구문에 여러개의 Value 값을 넣어서 SQL을 요청하면, (아래 예시 쿼리는 INSERT 구문이 한 번만 등장하므로) MySQL은 하나의 쿼리로 인식한다. 즉, 하나의 DB Connection 내에서 VALUES에 포함된 행만큼 INSERT 쿼리를 호출한다.

INSERT INTO <table> (one, two, three)
VALUES ("t", "ta", "tb"),
       ("a", "b", "c"),
       ...

쉽게 말해서 하나의 트랜잭션 내에서 여러개의 INSERT 쿼리를 호출하는 것이다. 따라서 대용량 데이터에 대해 INSERT 구문이 필요한 경우 Bulk Insert를 사용하는 것이 좋다.

 

하지만 하나의 트랜잭션 내에서 여러개의 INSERT 쿼리를 호출하므로 다음과 같이 타입 불일치 등으로 쿼리가 실패하는 경우 (one, two, three 모두 VARCHAR라는 가정에서 정수형이 들어오는 경우) INSERT 자체가 실패한다. 이는 트랜잭션 원자성에 의해 전체 INSERT 작업이 실패하게 된다. 따라서 Bulk 구문을 사용하는 경우 데이터 타입에 대해 정합성을 맞춰주는 것이 중요하다.

INSERT INTO <table> (one, two, three)
VALUES ("t", "ta", "tb"),
       ("a", "b", "c"),
       (1, "b", "c"),
       ...

 

또한 Bulk Insert의 VALUE는 최대 max_allowed_packet값을 초과할 수 없다. (default = 1MB) 이 max_allowed_packet 값을 초과하는 VALUES를 담게되면 Bulk Insert에 실패한다. 이 경우 해당 설정 값을 바꾸거나 DB 커넥션을 분리하는 방안이 필요하다.

Prepared Statement

다음으로는 Prepared Statement을 사용하는 방안이 있다. 쿼리가 DB에 전송이 되면 MySQL 서버는 전달받은 쿼리를 파싱하고 문제가 없는지 검토하는데, 이때 MySQL이 검토하는 과정을 한 번만 진행하도록 하면 리소스를 효율적으로 사용할 수 있다. 즉 DB Conenction이 여러번 사용되더라도 쿼리 분석 시간을 줄여 리소스를 효율적으로 사용하는 방안이다.

 

Go 언어로 예제 코드를 작성해보면 다음과 같다.

stmt, err := db.Prepare("SELECT name FROM users WHERE id = ? AND name = ? AND age = ?")
if err != nil {
    return err
}
defer stmt.Close()

_, err = stmt.Exec(1, "t", 99)
if err != nil {
    panic(err)
}

_, err = stmt.Exec(2, "t", 100)
if err != nil {
    panic(err)
}

Prepare() 메서드에 실행하고자 하는 쿼리는 전달한다. 쿼리를 전달하는 시점에 한 번만 쿼리를 검사한다. 이후 Prepare가 반환한 값 stmt에 적절한 Value를 전달하고 계속 쿼리를 실행한다. 이후 실제 쿼리는 Exec() 메서드를 호출할 때 DB로 전달되는데, 매번 DB Connection은 사용하겠지만, 쿼리를 분석하진 않는다. 따라서 쿼리를 분석하는데 소요되는 리소스를 줄일 수 있다.

 

이러한 쿼리는 쿼리 검토에 드는 리소스를 효율적으로 사용할 뿐 아니라 가독성도 좋고 리팩토링 하기도 수월하다. (유연한 코드)

Load Data

csv나 xlsx 파일 자체를 전달하여 대규모 데이터를 적재하는 경우에 해당한다. DBeaver, Orange와 같은 DB 클라이언트 툴에서 제공하는 기능을 사용하여 대량 데이터를 적재하는데, 보통 백엔드 엔지니어가 한다기보다는 DBA나 DB 엔지니어가 수행하는 경우가 많다.

정리

INSERT를 최적화하는 방안을 살펴보았는데, 이 3가지 방법 중에 Connection을 한 번만 사용하는 Bulk Insert가 리소스 측면에서는 더 효율적이다. (DB Connection과 관련된 오버헤드가 생각보다 크다.)

 

하지만 1만 건 미만인 데이터에 대해서 생각보다 두 방안의 차이는 거의 없다. (1만건이 넘어가는 경우 Bulk Insert가 효율적이다.) 데이터 Insert 양이 적다면 가독성과 유연성 측면에서 이점을 얻는 Prepared Statment가 더 좋다.