[PostgreSQL] 대량 데이터 인서트 시 성능 개선 및 주의 사항

대량 데이터 인서트 시 성능 개선

최초 서비스 배포나 데이터 마이그레이션을 할 때 대량의 데이터를 한 번에 인서트 하는 경우가 있다. PostgreSQL 공식문서에서는 대량 인서트 시에 효율적으로 진행할 수 있는 방법을 제시해 준다. (대량 데이터를 인서트 할 때 효율적인 설정이지 데이터베이스 조회나 업데이트 등실제 운영 시에 사용할 방법은 아니다.)

1. Autocommit 옵션 해제

대량의 인서트 실행 시, Autocommit 옵션을 해제하고 한 트랜잭션에서 작업 후에 커밋을 진행해야 한다(일반적으로 SQL를 실행 시에 자동으로 시작 시 BEGIN, 끝날 때 COMMIT으로 트랜잭션 처리가 되지만, 확실히 되고 있는지 확인필요하다.). 대량 데이터 인서트의 각각을 별도로 commit 한다면, PostgreSQL은 인서트 되는 각 열에 대해 너무 많은 작업을 수행하게 된다. 또한 모든 인서트를 한 트랜잭션에 처리할 경우에는 한 INSERT가 실패할 경우 그 시점까지 인서트 된 모든 작업이 취소되기에 실패 작업에 대한 부분 보완 및 무결성을 고려하지 않아도 된다.

2. COPY, PREPARE 사용

INSERT를 여러 번 실행시키기보다 COPY 커맨드 한 번으로 해결하라. COPY 명령어는 많은 ROW를 로드하는데 최적화되어 있다. INSERT 구문보다 덜 유연하지만, 대량 데이터를 로딩하는데 훨씬 적은 오버헤드를 발생시킨다. (COPY는 단일 명령어이기에 실행 시 Autocommit을 따로 비활성화시킬 필요 없다.) COPY를 사용할 수 없는 상황이라면 PREPAPRE 구문을 통해 준비된 INSERT 구문을  EXECUTE를 통해 필요한 만큼 실행시키는 방법도 있다. 이 방법은 INSERT 구분을 반복적으로 사용할 때 드는 파싱과 실행계획의 오버헤드를 줄여준다. 

 

COPY를 사용한 대량 데이터 로딩은 INSERT보다 거의 모든 경우에 더 빠르다. (PREPARE 구문을 사용하고 단일 트랜잭션에서 배치를 통해 INSERT를 한다고 해도 COPY를 사용하는 것이 더 빠르기 때문에 가능하다면 COPY를 사용하는 것이 유리하다.) COPY는 해당 테이블의 CREATE TABLE 혹은 TRUNCATE 명령어와 같이 쓸 때 더 빠르다. 이 경우에 에러가 날 경우에 최신으로 로드된 데이터를 포함하고 있는 파일은 무조건 삭제되기 때문에 WAL write가 필요 없다. (WAL의 개념은 다음 포스트를 참고)

https://junhkang.tistory.com/66

[PostgreSQL] WAL (Write-Ahead Logging) / 아카이브 모드 백업(Archive mode backup)의 개념 및 장단점

 

 그러나 wal_level이 minimal로 설정되어 있을 경우에만 유효하다.

3. 인덱스를 삭제하라

아얘 새로운 테이블을 생성하는 상황이라면, 가장 빠른 방법은 테이블을 만들고 COPY를 사용해서 테이블 데이터를 채우고 테이블에 필요한 인덱스를 그 후에 생성하는 것이다. 이미 존재하는 데이터에 인덱스를 생성하는 것이 데이터를 인서트 하면서 인덱스를 생성하는 것보다 더 빠르다.

 

기존 테이블에 데이터를 인서트 하는 상황이라면, 인덱스를 삭제하고, 데이터를 채우고, 인덱스를 다시 생성하는 것이 유리하다. 물론 인덱스가 사리진 기간 동안 해당 데이터에 접근하는 다른 사용자들은 성능 이슈를 겪을 것이고 Unique 인덱스를 drop 하는 경우에는 인덱스가 누락된 동안 무결성에 영향을 줄 수 있기에 작업과정에서의 영향도를 철저히 확인해야 한다.

4. FK 제약을 삭제하라

인덱스와 동일하게, FK는 벌크로 한 번에 체크하는 것이 row단위로 체크하는 것보다 효율적이다. 그래서 FK제약을 작업 전에 삭제하고, 데이터를 넣고, FK제약조건을 다시 생성한다. 인덱스와 동일하게 인서트 속도가 향상되는 반면 제약조건이 없는 사이에 데이터에 대한 무결성이 깨질 수 있다.

 

이미 존재하는 FK 제약조건이 있는 테이블에 데이터를 넣을 때, FK 제약조건을 체크하는 행위가 서버의 pending trigger 이벤트 목록에 추가된다. 수백만건의 데이터를 인서트 하는 경우에 트리거 이벤트 큐의 가용 메모리를 초과하여 적정량 이상의 메모리 스왑이 발생하거나 실행명령이 완전히 실패할 수도 있다. 그래서 많은 양의 데이터를 인서트 할 때는 FK 조건을 삭제하고 다시 설정해야 한다. 제약조건을 일시적으로 해제할 수 없다면, 더 작은 트랜잭션 단위로 분할하는 것이 유일한 방법일 수도 있다.

5. maintenance_work_mem 증가

일시적으로 maintenance_work_mem 설정 값을 늘리는 것은 대량 데이터를 로딩 시 성능을 향상할 수 있다. 이 옵션은 CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 명령어의 속도를 올려준다. COPY 자체의 속도를 올려주진 않기에 CREATE INDEX, ALTER TABLE ADD FOREIGN KEY를 사용할 때만 유효하다.

6. max_wal_size 증가

일시적으로 max_wal_size 설정값을 늘리는 것은 대량 데이터 로딩을 더 빠르게 해 준다. PostgreSQL에서의 대량 데이터 로딩은 일반적인 checkpoint 체크(checkpoint_timeout 옵션에 해당하는) 보다 더 잦은 checkpoint확인을 요한다. checkpoint가 발생할 때마다, 모든 부적절한 pages가 디스크에서 flush 된다. max_wal_Size를 늘림으로써 필요한 checkpoint의 빈도를 낮출 수 있다.

7. WAL Archival, Streaming Replication을 미사용

WAL Archiving이나 Streaming replication을 사용하는 경우 대량 데이터를 로딩할 때, 급격히 증가하는 WAL 데이터를 처리하는 것보다 로드가 완료된 후 새로운 백업을 실행하는 것이 유리하다. 데이터 로딩 중에 WAL 로깅이 증가하는 것을 방지하기 위해 WAL Archiving, streaming replication을 중지한다. (다음 3가지 옵션을 통해 설정가능)

• wal_level = minimal
• archive_mode = off
• max_wal_senders = 0

변경 후 서버를 재시작해야 하고, 기존의 기본 백업 정보들을 아카이브 복구나 standby서버로 사용이 불가능하게 된다. 데이터 손실로 이어질 수 있기 때문에 기존 백업 데이터에 대한 확인이 필요하다.

 

해당 옵션을 적용하면 Archiver 시간이나 WAL 샌더가 WAL데이터를 처리하는 시간을 줄여주는 것뿐만 아니라, 이 작업을 하면 실제로 특정 명령어들의 실행 속도를 더 빠르게 해 준다. WAL_LEVEL 이 mininal일 때, 현재 트랜잭션 혹은 상위 트랜잭션에서 테이블 변경 혹은 인덱스 생성/삭제할 때 WAL을 전혀 작성하지 않기 때문이다. (WAL 아카이빙을 하지 않아도, 마지막에 fsync 실행으로 충돌을 더 효율적으로 방지할 수 있다.)

8. 작업 후 ANALYZE 실행

테이블 내의 데이터 분포를 크게 변경하는 경우에는 ANALYZE를 실행시켜야 한다. 대량 데이터를 인서트 할 때도 유효하며, ANALYZE 혹은 VACUUM ANALZYE를 실행시키면 플래너가 테이블의 최신 통계를 가져오는 것을 확인할 수 있다. 정확하지 않은 통계나 수집되지 않은 통계가 있을 때 플래너는 쿼리 실행계획을 비효율적으로 세울 수 있고, 이는  테이블의 성능저하를 유발한다. (Autovacuum 데몬이 실행 중이라면 Analyze를 자동으로 실행하고 있을 것이다.)

9. pg_dump 확인 시 주의사항

pg_dump에 의해 생성된 Dump script는 대량 데이터 인서트시에 위의 가이드라인(1~8)의 일부만을 자동으로 적용시킨다.  pg_dump의 덤프를 최대한 빨리 복구하려면, 몇몇 추가 세팅을 수동으로 해야 한다. (이 작업은 dump를 복구하는데 적용되는 거지, 생성할 때 적용되는 것이 아니다.)

 

Default로, pg_dump는 COPY를 사용하며(가이드 2번 적용),  완벽한 schema-and-data 덤프를 생성할 때는 인덱스 및 외부키를 생성하기 전에 데이터를 로드 (가이드 3,4 적용) 하기 때문에 몇몇 가이드라인이 자동으로 적용되고, 유저는 다음 항목들만 정의하면 된다.

 

• maintenance_work_mem, max_wal_size를 적절한 값으로 설정
• WAL archiving, streaming replication을 사용 중이라면, 덤프 복구 중에는 미사용을 고려
• archive_mode = off, wal_level = minimal, max_wal_sender = 0을 덤프를 로딩하기 전에 설정하고, 덤프 복구 후에 원래 값으로 되돌리고 기본 백업을 최신으로 실행
• pg_dump, pg_restore의 병렬 dump 및 복구 모드를 테스트해서 최적의 동시 job 실행 개수를 적용. (-j 옵션을 사용하여 덤프 및 복원을 적절하게 병렬로 수행하면 직렬보다 더 높은 성능 가능하다.)
• 모든 덤프가 단일 트랜잭션으로 복구되도록 설정 (-1 혹은 --single-transaction 커맨드라인 옵션을 psql 혹은 pg_restore에 날리면 된다.) 다만, 이 모드를 사용하면, 아주 작은 에러라도 날 경우 전체 복구 과정이 롤백되어 몇 시간의 작업을 날릴 수도 있게 된다
• DB서버에 여러 개의 CPU를 가용하는 것이 가능하다면 pg_restore의 --jobs 옵션을 쓸 수 있다. 이를 통해 병렬로 동시에 데이터를 인서트 하고 인덱스도 생성할 수 있다.
• 작업 이후 ANALYZE를 실행시킨다

data-only 덤프는 여전히 COPY를 사용하지만 인덱스를 삭제하거나 재생성하지 않고 FK에 영향을 주지 않는다. 그래서 data-only 덤프를 로딩할 때, 인덱스나 FK를 삭제한 후 다시 생성하는 방식을 사용할지 말지는 유저의 선택이다. 대량 데이터를 인서트 할 때와 동일하게, max_wal_size를 증가시키는 것은 유리하다, 하지만 data-only 덤프는 maintenance_work_mem을 늘리는 것에 영향을 받지 않는다. 그보다 인덱스와 FK키를 삭제 후 수동으로 다시 생성하는 것이 효율적이다. 

 

(* FK를 삭제하는 것 대신에 --disable-triggers 옵션으로 FK검증트리거 실행을 방지할 수 있지만, 단지 체크를 지연시키는 것으로 무결성에 위배되는 데이터가 들어올 수 있음은 동일하다)

10. 결론 및 적용 검토

실제로 산군의 데이터베이스는 수백만 건의 데이터를 마이그레이션 & 백업하는 작업이 주기적으로 있다. 일부 작업의 경우 타 테이블을 참조하거나 대량 업데이트가 포함되어 있어 해당 테이블의 조회 성능에 직접적인 영향을 주는 경우가 있기에 트랜잭션을 분할하여 배치성으로 작업을 하거나, 특정 테이블을 격리 후 작업 및 동기화를 진행하고 있다. 인서트 시간이 길어지는 만큼 부담이 가는 작업인 만큼, 속도 향상을 위해 다양한 방법을 시도하고 있고, 공식문서에 나온 다음 가이드들을 추가 검토해 보게 되었다.

 

1~8 가이드 중 직접적으로 적용 가능한 부분이 있을까? 

• 1. Autocommit 해제 - 작업은 기존에도 사용 중 (데이터 무결성, 작업 내용 검증 및 백업을 위해 트랜잭션 컨트롤은 필수)
• 2. COPY, PREPARE - COPY 명령어도 사용 중이지만 한계가 있는 경우가 많고, 배치성으로는 이미 작업 중
• 3, 4 인덱스, FK 삭제 후 재설정 - 실제 운영 중인 라이브 테이블에 인덱스, FK를 일시적으로 없애는 것은 효율적이지 못함
• 5,6,7,8 PG옵션 추가 - 실제 운영 중인 DB에 설정값 적용 및 재부팅을 시도하는 것은 불가능

간단하게 적용가능한 1,2번은 보통 다 사용중일 것이고, 전체적으로 운영 중인 데이터베이스에 실행하기엔 위험부담이 큰 작업들이 대부분이다(특히 5~8 옵션을 통해 DB자체를 다운시켜야 하거나 기존 백업을 사용할 수 없는 상황은 적용 불가). 대부분 최초 서비스 배포 혹은 리뉴얼 시에 적용 가능한 방법들로 보인다. 현재 운영 중인 데이터베이스의 특정 테이블을 격리하는 방식으로 3~4번이 부분적용이 가능해 보이기는 하지만, 인덱스를 삭제하고 데이터를 인서트시 코스트가 줄어들더라도 대량의 데이터가 들어간 후의 테이블에 인덱스를 추가하는 시간도 만만치 않을 것이다. 

 

운영 중인 데이터베이스에 대량 데이터를 인서트시 인덱스, FK에 대한 조정, 옵션값 변경 후 DB 재실행이 불가능한 상황이라면, 배치성, 트랜잭션 분할을 통해 (데이터베이스 성능과 데이터 무결성) 모니터링을 함께 진행하는 것이 가장 효과적일 듯하다.

 

 

 

 

 

 

 

https://www.postgresql.org/docs/current/populate.html

https://postgresql.kr/docs/9.6/wal-intro.html