[트러블 슈팅] 20만 더미 데이터 삽입을 csv파일을 이용해서 DB connection timeout → 7초로 줄이기

⭐서론

코드스쿼드 에어비앤비 클론 코딩 프로젝트 중 성능 테스트를 하고 싶었고,

이를 위해 다량의 숙소, 예약, 회원의 더미 데이터를 만들고자 하였습니다.

 

더미 데이터를 숙소 20만개, 예약 100만개, 회원 1만명을 만들고자 하였습니다.

이 중, 숙소 20만개를 만드는 과정에서 생긴 일을 소개합니다.

 

20만개 숙소 데이터를 Java 코드로 숙소 1개마다 INSERT INTO 쿼리를 DB에 날렸더니 DB connection timeout 문제를 마주했습니다.

 

- 고려한 방안

• MySQL의 레코드는 테이블 단위의 파일로 저장된다는 점에 착안한 csv파일 사용
• 대량 삽입에 특화된 기술인 Spring Batch

 

- 2가지 이유로 csv파일을 이용하길 선택했습니다.

• 프로젝트 남은 1주 동안 성능 테스트를 해야된다는 점
• 더미 데이터는 1회성 작업이라는 점

 

아래 내용은 Docker 로 띄운 MySQL 서버에 CSV파일로 데이터를 벌크 Insert 하는 과정을 소개합니다.

⭐문제 해결 과정

개발 환경

가상환경OS : WSL2
Docker Engine : 26.1.1
JPA : 3.x
Mysql : 8.0
Spring boot : 3.x
JDK : 17
IDE : IntelliJ ultimate

인텔리제이에서 csv파일을 만드는 방법

먼저, 자바로 csv 파일을 만들기 위해서 build.gradle 에 외부 라이브러리 의존성을 추가합니다.

implementation 'org.apache.commons:commons-csv:1.11.0'

 

 

csv파일을 만들기 시작하는 방법은 별도의 api 를 만들어서 서버를 실행 시킨 뒤 컨트롤러에서 해당 api 를 받는 방법도 있지만,

컨트롤러와 별도의 api 를 만들기 보다는 @PostConstruct 를 이용해서 더미 데이터 만드는 과정을 간단하게 하고 싶었습니다.

 

@PostConstruct 가 실행되는 시점은 스프링컨텍스트가 빈을 생성하고, 빈들간의 의존성 주입이 끝난 이후에 실행됩니다.

그렇기에, DummyStayService 위에 @Component 를 붙여 해당 클래스가 스프링컨텍스트에 의해 관리되게 하였고,

2개의 stay, member 레포지토리에 의존성 주입을 해주었습니다.

마지막으로, 의존성 주입이 끝난 후에 실행될 메서드인 init() 위에 @PostConstruct 어노테이션을 붙여주었습니다.

 

아래 코드는 전체 코드이며, csv파일을 만드는 메서드는 writeStaysToCSV  입니다.

• 파일 입출력 리소스를 사용하기 때문에 try-with resource 구문으로 자동으로 파일 리소스를 close 해주었습니다. 
• withHeader 를 통해 csv파일의 행을 지정하였습니다.
• printer.printRecord 에는 csv파일 행에 맞춰 들어갈 레코드(데이터)를 넣어주었습니다.
• @lon, @lat 은 MySQL 에서 공간 데이터를 지원해주는 POINT 타입으로 저장하기 위한 지정된 표현입니다.
  • POINT 타입은 X(경도)와 Y(위도) 두 개의 좌표 값을 포함하며, POINT(x, y) 형식입니다.
  • csv파일에는 @lon 이름으로 경도, @lat 이름으로 위도가 저장되며 csv파일이 MySQL 테이블 파일에 쓰여질 때, POINT 타입으로 저장 될 것입니다.

전체 코드입니다.

package com.airdnb.clone.dummy;

// custom import
import com.airdnb.clone.domain.member.entity.Member;
import com.airdnb.clone.domain.member.repository.MemberRepository;
import com.airdnb.clone.domain.stay.entity.Stay;
import com.airdnb.clone.domain.stay.repository.StayRepository;
import com.airdnb.clone.dummy.member.DummyMemberGenerator;
import com.airdnb.clone.dummy.stay.DummyStayGenerator;
---
import jakarta.annotation.PostConstruct;
import java.io.FileWriter;
import java.io.IOException;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component
public class DummyStayService {

    @Autowired
    private StayRepository stayRepository;

    @Autowired
    private MemberRepository memberRepository;

    // 더미데이터 만들지 않을 때는 주석 풀지 않기
    @PostConstruct
    public void init() {
    	// 1만명의 회원을 랜덤으로 생성
        List<Member> members = IntStream.range(0, 10000)
                .mapToObj(i -> DummyMemberGenerator.generate())
                .collect(Collectors.toList());
                
        // 생성한 회원을 모두 저장
        memberRepository.saveAll(members);

        List<Member> allMembers = memberRepository.findAll();

        // 숙소를 20만개 만들 것인데, 지역에 따라 숙소 개수를 custom 하게 설정.
        List<Stay> stays = IntStream.range(0, 200000)
                .mapToObj(i -> {
                    if (0 <= i && i <= 60000) {
                        return DummyStayGenerator.generateJeju();
                    }
                    else if (60001 <= i && i <= 100000) {
                        return DummyStayGenerator.generateBusan();
                    }
                    else if (100001 <= i && i <= 140000) {
                        return DummyStayGenerator.generateGangwon();
                    }
                    return DummyStayGenerator.generate();
                })
                .collect(Collectors.toList());

        // 만들어진 회원과 숙소데이터를 stays.csv 란 이름을 가진 csv파일에 넣는 작업
        try {
            writeStaysToCSV(allMembers, stays, "stays.csv");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void writeStaysToCSV(List<Member> allMembers, List<Stay> stays, String fileName) throws IOException {
        try (FileWriter out = new FileWriter(fileName);
             CSVPrinter printer = new CSVPrinter(out, CSVFormat.DEFAULT
                     // csv파일의 첫 행의 이름을 지정
                     .withHeader("BATH_COUNT", "BEDROOM_COUNT", "BED_COUNT", "CHECK_IN_TIME"
                             , "CHECK_OUT_TIME", "GUEST_COUNT", "CLEANING_FEE", "HOST_ID"
                             , "PER_NIGHT", "ALIAS", "DESCRIPTION", "@lat", "@lon"))) {

            // count 는 회원 당 숙소를 2개 가지도록 하기 위함.
            int count = 0;
            
            // 숙소를 순회하며 csv파일 행에 맞게 숙소 데이터 쓰기
            for (Stay stay : stays) {
                printer.printRecord(
                    숙소 컬럼에 맞게 데이터 가공..., stay.getPoint().getY(), stay.getPoint().getX());
                if (count == 9999) {
                    count = 0;
                }
            }
        }
    }
}

 

이렇게 코드를 작성한 후, 서버를 실행하면 csv파일이 프로젝트의 루트 디렉토리에 생성됩니다.

csv 파일을 열어보면 아래와 같이 더미 데이터가 생성된 것을 확인할 수 있습니다.

Docker 로 띄운 MySQL 로 csv파일 옮기기

이제 이 csv파일을 docker 로 띄운 MySQL 서버로 파일을 전송하면 됩니다.

 

먼저, wsl2 에 접속을 했습니다.(윈도우 기준)

 

stays.csv 파일을 conta 라는 docker 컨테이너의 /var/lib/mysql-files 란 경로의 stays.csv 이름으로 파일을 전송합니다.

docker cp 는 Docker 컨테이너와 호스트 간에 파일을 복사하는 Docker 명령어입니다.

docker cp /mnt/c/Users/user/stays.csv conta:/var/lib/mysql-files/stays.csv
docker cp         [csv파일 경로]       [컨테이너 이름]:/var/lib/mysql-files/[csv파일 이름]

 

이제, docker 명령어로 MySQL 에 접속합니다.

docker exec -it [mysql이 있는 container이름] mysql -u root -p
docker exec -it conta mysql -u root -p

저는 root 말고, team04 라는 사용자로 접속했습니다.

 

docker에 있는 mysql에 접속 후, 사용할 DB를 선택(선택할 DB가 없으면 하나 만듭니다)합니다.

use airdnb_db;
use [사용할 DB 이름];

 

선택한 DB에 csv로 넣을 데이터의 컬럼이름과 타입에 맞게 테이블을 생성해줍니다.

저는 인텔리제이에서 JPA의 하이버네이트 구현체의 기능인 ddl-auto:create 로 생성해주었습니다.

⭐ MySQL 서버에 있는 csv파일에서 데이터를 읽어 테이블 파일에 삽입

LOAD DATA INFILE '/var/lib/mysql-files/stays.csv' // 해당 경로에 있는 csv파일에서 데이터를 로드합니다.
INTO TABLE STAY                               // 로드된 데이터를 삽입할 테이블을 지정합니다.
FIELDS TERMINATED BY ','                      // csv파일의 각 열이 쉼표로 구분되어 있음을 의미합니다.
ENCLOSED BY '"'                               // csv파일의 각 열 값이 큰따옴표로 감싸져 있음을 의미합니다.
LINES TERMINATED BY '\n'                      // csv파일에서 각 행이 줄 바꿈으로 구분되어 있음을 의미합니다.
IGNORE 1 ROWS                                 // 첫 번째 행(일반적으로 헤더 행)을 무시하도록 지정합니다.
(BATH_COUNT, BEDROOM_COUNT, BED_COUNT, CHECK_IN_TIME, CHECK_OUT_TIME, GUEST_COUNT, CLEANING_FEE, HOST_ID, PER_NIGHT, ALIAS, DESCRIPTION, @lat, @lon)
// csv파일의 각 열을 테이블의 해당 열에 매핑합니다.
// csv파일의 열 순서와 동일하게 지정합니다.
// @lat, @lon은 파일의 위도와 경도 값을 임시 변수로 저장합니다.
SET POINT = ST_GeomFromText(CONCAT('POINT(', @lat, ' ', @lon, ')'), 4326);
// POINT 열을 설정합니다.
/ ST_GeomFromText 함수는 텍스트 표현을 지리적 좌표로 변환합니다.
// CONCAT('POINT(', @lat, ' ', @lon, ')')는 POINT(@lat, @lon) 형식의 문자열을 생성합니다.
// 4326은 좌표 참조 시스템(SRID)를 지정합니다.

⭐결과

이 결과로 20만건의 데이터를 IDE에서 일일이 Stream으로 돌면서 Insert 하면 connection timeout 이 발생했던 문제를

csv파일로 벌크 insert 해서 7초만에 해결하게 되었습니다.

⭐마주한 에러 해결 과정을 공유합니다

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

• 로컬 데이터 로드를 허용하면 보안상 위험하기에 MySQL 에서는 이 기능을 기본으로 비활성화합니다.
  • 이 기능을 활성화 해주기 보다는 MySQL 파일에 데이터를 삽입하는 과정에서 명령어를 `LOAD DATA LOCAL INFILE` 이 아닌 `LOAD DATA INFILE` 로 합니다.

ERROR 1045 (28000): Access denied for user 'team04'@'%' (using password: YES) 

• 사용자(team04)에게 어떤 호스트에서도 접속할 수 있도록 권한을 줘야합니다.
• mysql 을 root 계정으로 접속해서 다음과 같은 명령어를 쳐서 team04 에게 권한을 줍니다.

GRANT FILE ON *.* TO 'team04'@'%';
FLUSH PRIVILEGES;

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

• 저는 /var/lib/mysql-files 로 csv파일을 옮기지 않고, LOAD DATE INFILE 명령을 실행할 때 나타났습니다.
• 이 에러는 MySQL 서버가 --secure-file-priv 옵션으로 실행되고 있어서, 해당 명령문을 실행할 수 없음을 나타냅니다.
• --secure-file-priv 옵션은 보안을 위해 도입된 기능으로, MySQL 서버에서 LOAD DATA INFILE 명령을 실행할 때 허용된 디렉토리 경로를 제한하는 역할을 합니다

LOAD DATA INFILE 명령이 실행될 수 있는 디렉토리 경로를 확인합니다.

mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

/var/lib/mysql-files 에서는 LOAD DATA INFILE 명령이 실행 가능하다고 나옵니다.

docker cp 명령을 사용해서 csv파일을 /var/lib/mysql-files/ 로 옮겨줍니다.

ERROR 1366 (HY000): Incorrect integer value: 'Location' for column 'BATH_COUNT' at row 1

• LOAD DATA INFILE 명령을 실행 할 때, 컬럼 이름과 csv파일의 행 순서가 일치하지 않을 때 발생합니다.
• csv파일 행 순서에 맞게 (BATH_COUNT, ... @lan, @lon) 을 적어줍니다.

ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field 

• 공간데이터를 사용할 경우 ORM 에도 공간 데이터 의존성을 추가해 줘야 합니다.
• 저는 querydsl 과 JPA 하이버네이트 구현체를 사용하고 있기 때문에 다음과 같은 의존성을 추가했습니다.

// 위도 경도
implementation 'org.hibernate:hibernate-spatial:6.5.2.Final'
implementation 'com.querydsl:querydsl-spatial:5.1.0'

ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'POINT'. The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID of the geometry or the SRID property of the column.

• SET POINT = ST_GeomFromText(CONCAT('POINT(', @lat, ' ', @lon, ')'), 4326); 에서 4326이 없을 때 발생하는 오류입니다.
• 4326을 추가해줍니다.

ERROR 3617 (22S03): Latitude 126.543171 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]

• 위도 경도 컬럼에 대한 데이터가 올바른지 확인합니다.
• Java 에서 Point.getY() 는 @lat (위도)에 해당되고, Point.getX() 는 @lon (경도)에 해당합니다.

ERROR 3037 (22023): Invalid GIS data provided to function st_geomfromtext.

• ST_GeomFromText(CONCAT('POINT(', @lat, ' ', @lon, ')') 구문이 잘 작성 되었는지 확인합니다.
• ST_GeomFromText() 함수에 전달할 문자열이 올바른 형식과 구조를 가져야 합니다.
  • 예를 들어, POINT(1 2) 와 같은 형식과 구조를 가져야 합니다.

⭐회고

성능 테스트를 위한 다량의 더미 데이터를 csv파일로 만들 때,

csv파일의 위도 경도 값을 POINT 타입에 넣기 위해 어떻게 쿼리를 짜야 하는지가 가장 어려웠고, 이를 공간데이터와 공간 함수를 학습하며 극복했습니다.

 

csv파일을 사용하며 생각한 단점입니다.

• 만약, enum 타입을 넣고 싶다면? 다른 공간 데이터 타입을 넣고 싶다면? MySQL 이 아닌 다른 DBMS 를 사용해야 한다면?  즉, MySQL이 지원하는 데이터 타입에 따라 작성해야 할 쿼리가 달라지기 때문에 자동화 작업이 안됩니다.
• 제 방법은 PostConstruct 의 csv파일을 만드는 작업 때문에 스프링 서버가 늦게 뜹니다.
  • 그래서, csv파일을 만들지 않기 위해 해당 코드를 주석처리하거나 삭제해야합니다(1회용이기 때문에)

그래서,

다음 번에는 Spring Batch 를 학습해서 사용하는 것이 좋다는 생각이 들었습니다

때로는, 데이터 타입이 varchar 나 Integer 같은 단순한 타입일 때 이 경험을 바탕으로 다시 시도할 것 같습니다.

 

하지만, MySQL의 공간 데이터와 공간 함수에 대해 학습할 수 있던 시간과 '문제 해결이라면 어떻게서든 하고 싶다는 집념이 있다'라는 것을 알게 된 트러블 슈팅이었습니다.