-
[PostgreSQL] 대량의 Insert를 가장 빠르게 하는 방법DB/PostgreSQL 2016. 10. 24. 16:47
데이터를 insert한다고 할 때, 단순한 insert의 경우는 속도를 고려해야할 필요가 거의 없습니다. 하지만 10만, 100만, 혹은 억단위의 데이터를 postgresql에 insert한다고 하면 퍼포먼스를 고려해야 되는 이슈가 생깁니다.
이러한 해당 이슈에 대한 많은 해결책이 있습니다.
이상적인 해결책은 인덱스없이 로깅되지 않은 테이블을 import한 후 이 로깅을 변경하고 인덱스를 추가하는 것입니다. 아쉽게도 PostgreSQL의 9.4에서 로깅되지 않은 테이블을 로깅으로 변경하는 것을 지원하지 않습니다. 9.5 버전에서는 ALTER TABLE ... SET LOGGED와 같은 명령어를 추가해 사용자가 변경할 수 있도록 추가하였습니다.
PostgreSQL Tuning Point
만약 사용자가 bulk import를 위해 데이터베이스를 오프라인으로 할 수있는 경우엔, pg_bulkload를 사용하면 됩니다.
그렇지 않으면 아래와 같이 따라합니다.
테이블에 있는 트리거를 사용하지 않도록 설정합니다.
import가 시작하기 전에 걸려있는 인덱스들을 지웁니다. insert가 완료가 된 다음 인덱스들을 재생성합니다.(인덱스를 한번에 구축하는 것이 데이터와 함께 인덱스를 동시에 구축하는 것 보다 시간이 덜 걸리고, 인덱스를 다시 생성하는 것이 더 컴팩트합니다.)
만약 단일 트랜잭션내에서 import를 실행하면, 외래키 제약을 삭제하고 import한 다음 commit하기 전에 재생성하는 것이 안전합니다.
잘못된 데이터를 소개하는 것처럼 import가 여러 트랜잭션에 걸쳐 분할되는 경우, 이 작업을 수행하지 않는 것이 좋습니다.
가능하면 INSERT문들(단일 insert와 bulk insert 등?) 대신 COPY문 사용이 좋습니다.
만약 COPY문을 사용할 수 없을 경우, multi-valued INSERT문을 사용하는 것이 좋습니다.
단일 VALUES에 너무 많은 값을 나열하면 안됩니다. (이미 알겠지만)
이러한 값들은 몇 번 이상 메모리에 맞춰야 하고 하나의 statement 당 수 백을 보관해야 합니다.(those values have to fit in memory a couple of times over, so keep it to a few hundred per statement.)
fsync()비용을 줄이기 위해 synchronous_commit=off와 commit_delay을 사용하는 것이 좋습니다. 이 옵션들은 사용자가 거대한 트랜잭션을 처리하는 경우에는 그다지 도움이 되지 않습니다.
여러 연결을 통해 INSERT 또는 COPY를 병렬적으로 처리합니다. 이 처리는 사용자의 하드웨어 디스크 서브시스템에 따라 달라집니다: 직접 연결된 스토리지를 사용하는 경우 실제 하드 드라이브 당 하나의 연결을 사용할 수 있습니다.
checkpoint_segments 값을 높게 설정하고 log_checkpoints 값을 사용가능하게 설정합니다. PostgreSQL의 로그를 보고 너무 자주 발생하는 checkpoint에 대한 에러처리가 아니라는 것을 확신해야 합니다.
만약 import하는 동안 시스템이 충돌난다면 치명적인 손상으로 전체 PostgreSQL의 클러스터를 잃는 것에 대해 신경쓰지 않아도 됩니다. 사용자는 fsync=off를 사용해 PostgreSQL을 중단할 수 있고 import를 시작할 수 있습니다. import가 시작된다면 PostgreSQL를 중지하고 다시 fsync=on을 설정할 수 있습니다. 만약 PostgreSQL내에 중요한 데이터가 이미 있는 경우, 해당 작업을 사용하지 않는 것이 좋습니다. fsync=off를 설정하면 full_page_writes=off을 사용자가 설정할 수 있습니다. 데이터 손실과 데이터베이스 붕괴를 예방하기 위해 import 이후 처리하는 것이 좋습니다.
System Tuning Point
SSD를 사용하면 좋습니다. 전력 보호, write-back cach, 신뢰성이 있는 좋은 SSD는 겁나 빠른 속도를 만들어줍니다. disk flush / fsync()들의 수를 줄이는 것과 같은 위의 조언을 따를 때 효율이 적어보이지만 큰 도움이 됩니다. 만약 데이터의 보존을 생각한다면 전원 장애를 보호해주는 기능이 없는 값싼 SSD를 사용하지 마세요.
사용자가 스토리지에 직접 연결을 위해 RAID 5또는 RAID 6을 사용한다면, 즉시 중단해야 합니다. 데이터를 백업하여 RAID 어레이를 RAID 10으로 재구성한 다음, 다시 시도합니다. RAID 5와 6은 대량의 쓰기 퍼포먼스는 절막적입니다. - 물론 큰 캐시를 가진 좋은 Raid 컨트롤러는 도움이 될 수 있긴 합니다.
사용자가 거대한 배터리 백업을 위한 다시 쓰기 캐시(write-back cache)를 가진 하드웨어인 RAID 컨트롤러를 사용한다면 수 많은 commit 작업량에 대한 쓰기 퍼포먼스를 향상시킬 수 있습니다. 사용자가 bulk loading하는 동안 적은 수의 거대한 트랜잭션을 처리를 한다면 commit_delay를 가진 async commit을 사용해도 많은 도음이 되지 않습니다.
결론
속도를 올릴수 있는 단순한 방법은 인덱스와 트리거를 지우고 insert문이 아닌 copy문을 사용해야 할 것 같음. (간단한 테스트나 고려해야되는 사항이 적을 시)
실제 서비스를 접목하고자 할때는 하드웨어 튜닝까지 처리
참조문서:
http://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql
https://www.postgresql.org/docs/current/static/populate.html