[일일 회고] 22.02.07~08 - 도커 컴포즈, DB, JDBC 🐳

📚개발 일지

어제오늘 프로젝트는 도커를 활용해 mysql:5.7 컨테이너 올리고 100만 개의 더미 데이터를 생성한다. Java에서 간단한 사용자 입력 콘솔 프로그램을 만들어 JDBC를 가지고 CRUD 작업을 했다.

내용에 앞서 과연 도커 컨테이너를 통해 데이터베이스를 운영하는게 좋은 방법일까? 테스트 용도의 DB는 상관 없겠지만 실제 프로덕션 DB는 안전해야하기 때문에 도커보다 로컬이나 클라우드가 제공하는 DB는 어떨까?

 

✅ 도커컴포즈 활용 

도커 명령어를 활용한 컨테이너 실행은 환경 변수나 데이터 복사 붙여 넣기가 불편했다. mysql root 비밀번호 노출도 있었다.

# 도커 명령어
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

물론 mysql 이미지를 활용해 dockerfile을 직접 생성하여. sh 파일로 넘겨줄 수 있었지만 이번 기회에 도커컴포즈를 활용해보자 했다.

 

# docker-compose.yaml
version: "3.8"
services:
  db:
    image: mysql:5.7
    restart: always
    container_name: mysql-test
    ports:
      - "3306:3306"
    volumes:
      - ./db/conf.d:/etc/mysql/conf.d # mysql 환경설정
      - ./initdb.d:/docker-entrypoint-initdb.d # .sql 진입점 실행
    env_file: .env # 계정 정보 관리
    environment:
      TZ: Asia/Seoul # 타임존 설정

mysql 환경 설정을 위해 로컬의 ./db/conf.d 에 my.cnf 파일을 만들어 컨테이너에 넘겨주었다. (utf-8)

초기 DB 테이블, 더미 데이터 생성을 위해. sql를 작성했고 컨테이너의 docker-entrypoint에 넘겨주어 자동 실행되게 만들었다.

mysql 사용자 id/pw나 주요 환경변수는 .env 파일로 작성해 전달했다.

 

 

✅  100만 건 더미 데이터 bulk insert

100만 건의 더미 데이터를 생성해 DB에 넣어야 했다. (엄청 많은 양은 아니지만) 단순하게 프로시저로 100만 건의 INSERT INTO 문을 만들어 실행했다. 10분이 넘어도 쿼리가 끝나지 않아 뭔가 잘못됨을 느꼈다. 그 이유는 레코드 단위 INSERT는 AUTO-COMMIT이 ON으로 되어있기 때문에 하나의 데이터마다 COMMIT을 한다. 이 작업은 FLUSH(디스크 동기화), 디스크 I/O에 많은 부하를 일으킨다. 

 

3가지 해결 방법 

1. AUTO-COMMIT OFF/ON

프로시저에서 100만 개의 INSERT INTO 앞 뒤에 AUTO-COMMIT을 끄고 켰다. 이는 100만 개의 작업을 하나의 트랜젝션으로 묶어 한 번에 처리했다. 그 결과 10분이 넘는 작업이 10초도 안되어 처리됐다. 

하지만 이렇게 내 마음대로 오토 커밋을 끄고 켜도 문제가 없을까? 몇 가지 이슈를 찾아봤다.

문제점

데이터베이스 락 현상

하나의 트랜잭션으로 묶인 100만 개의 INSERT TO 명령이 업데이트하는 동안 다른 유저가 해당 테이블에 접근한다면 100만 개의 작업이 끝날 때까지 기다리는 현상이 생길 수 있다. (디테일한 상황마다 다르겠지만 아직은 잘 모르겠다. 참조링크1, 참조링크2)

해결

참조링크2에서 postgresSQL의 해결 방법을 소개한다.  트랜젝션 세션에 타임아웃(idle_in_transaction_session_timeout)을 설정하여 트랜젝션에 타임아웃을 두는 것이다. 

SET AUTOCOMMIT = FALSE; -- Autocommit OFF

DROP PROCEDURE IF EXISTS loopInsert

CREATE PROCEDURE loopInsert()
BEGIN
	-- 생략
    WHILE i <= 1000000 DO
        INSERT INTO PC.user(생략)
          VALUES( 생략 );
        SET i = i + 1;
    END WHILE;
    COMMIT;
END

CALL loopInsert();

SET AUTOCOMMIT = TRUE; -- autocommit on

2. CSV 파일로 생성하여 데이터 삽입

LOAD DATA INFILE을 활용해서 CSV 파일을 한 번에 넣을 수 있다. 

발생할 수 있는 문제점으로는 업로드할 파일의 권한 설정을 열어주어야 하고 리눅스의 AppArmor 보안 프로그램이 켜져 있다면 MySql을 보호하고 있기 때문에 꺼줘야 한다. (링크) (thanks to 아더)

 

3. 패킷 크기 조절(max_allowed_packet)

mysql의 서버 송수신 최대 길이 패킷 옵션을 설정할 수 있다. 기본 값은 시스템마다 다르지만(예, 1MB, 4MB) 이 크기를 늘려준다.

(리눅스의 경우) my.cnf 파일에 아래 옵션을 추가해준다. 패킷 크기 조절은 INSERT INTO를 한 번마다 하는 게 아니라(방법 1) 한 번에 묶어 전송할 때 한 번의 전송 크기를 늘리는 것이다(방법 2)(thanks to 리아코)

[mysqld]
max_allowed_packet = 16M

-- 방법1
INSERT INTO 테이블명 VALUES (컬럼1, 컬럼2, ...)
INSERT INTO 테이블명 VALUES (컬럼1, 컬럼2, ...)
...

-- 방법 2
INSERT INTO 테이블명 (컬럼1, 컬럼2, ...)
VALUES
('값1','값2'),
('값1','값2'),
('값1','값2');

 

어떤 조합이 좋을까? 상황에 따라 다르다. +a) mysql dump 시 CSV가 아닌 INSERT INTO으로 나온다. 방법 1과 3을 적절히 조합하면 좋아 보인다.

+a) 호눅스 추천으로 PostgreSQL 소스 코드에 대한 호기심이 생겼다.(소스코드 Naver D2 한눈에 살펴보는 PostgreSQL , 소스코드 분석 youtube)

 

✅ JDBC

MySQL와 Java 연결을 위해 mysql connector을 설치하고 JDBC에서 연결하여 사용했다. DAO와 DTO 패턴에 컨트롤러를 두었다. 계층 간에 DTO로 데이터를 송수신했고 CRUD 부분은 DAO에서 담당했다. 하지만 DAO의 Connection을 분리하지 못해 코드가 난잡하다. 중복 메서드 리팩터링과 DB 관련 테스트는 어떻게 해야 할지 모르겠다. 앞으로 JDBC 쓸 일이 많을 테니 차근차근 공부해보자.

---

👍 Keep

- 동료들과 함께 개발하기

- 배운 것을 나누기

🔥Problem

- 프로젝트 종료 후 이전 코드에 대한 리팩터링을 하지 않음

- 개인 공부에 시간을 쏟지 못함

🚒 Try

- 이전 코드를 복기하고 시간이 지난 후 리팩터링을 다시 해보자

- 동료와 좋은 것을 나누기 위해 실력을 더 쌓아야겠다.

- 모르는 것을 인식하기

- 즐기기

 

---

⛓ Reference

 

https://stackoverflow.com/questions/16168055/what-is-the-effect-of-disabling-auto-commit-on-a-database-connection

https://www.cybertec-postgresql.com/en/disabling-autocommit-in-postgresql-can-damage-your-health/

https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

https://koreabigname.tistory.com/14

https://github.com/naneun/Trouble-Shooting/blob/main/BULK_INSERT.md

https://github.com/postgres/postgres

https://d2.naver.com/helloworld/227936

https://www.youtube.com/watch?v=qrlJsDU8UuI&ab_channel=teachmedatabase