[SQL] 어떻게 PostgreSQL을에 삽입 성능을 향상하기

어떻게 PostgreSQL을에 삽입 성능을 향상하기

나는 포스트 그레스 삽입 성능을 테스트입니다. 나는 그것의 데이터 유형으로 번호를 하나의 열이있는 테이블이 있습니다. 뿐만 아니라 그것의 인덱스가 있습니다. 나는이 쿼리를 사용하여 데이터베이스를 작성 :

insert into aNumber (id) values (564),(43536),(34560) ...

나는 위의 쿼리를 한 번에 매우 빠르게 만 400 만 행을 삽입했다. 데이터베이스가 600 만 행에 도달 한 후 성능은 크게 1 백만 행마다 15 분으로 감소했다. 삽입 성능을 향상하기 위해 어떤 트릭이 있습니까? 나는이 프로젝트에 최적의 삽입 성능을 필요로한다.

5기가바이트 RAM이있는 컴퓨터에 윈도우 7 Pro를 사용.

해결법

1. ==============================

   1.PostgreSQL의 설명서에서 데이터베이스를 채울 참조 depesz의 우수한 것과 같이 일반적인 주제에 대한 기사,이 SO 질문입니다.

   PostgreSQL의 설명서에서 데이터베이스를 채울 참조 depesz의 우수한 것과 같이 일반적인 주제에 대한 기사,이 SO 질문입니다.

   (주이 대답은 기존의 DB로하거나 새로 작성하는 대량의 데이터로드에 관한입니다. 당신이있는 거 관심 DB pg_restore의 또는 pg_dump의 출력의 psql의 실행 성능을 복원하면,이 정도의은 pg_dump의 및 pg_restore에 있기 때문에 적용되지 않습니다 가 + 데이터 복원 스키마를) 완료 후 이미 작성, 트리거 및 인덱스 등의 작업을 수행.

   할 많은. 이상적인 솔루션은, 인덱스없이 로깅되지 않은 테이블로 가져 로그인으로 변경하고 인덱스를 추가하는 것입니다. 불행하게도 PostgreSQL의 9.4 로그인에 로깅되지 않은에서 테이블을 변경에 대한 지원이 없다. 9.5 ALTER TABLE ... SET LOGGED이 작업을 수행 할 수 있도록 허용하는 추가합니다.

   당신은 대량 가져 오기를 사용 pg_bulkload에 대한 데이터베이스를 오프라인으로 할 수 있습니다.

   그렇지 않으면:

   또한 시스템을 조정 보라 :

   또한 빠른 테스트하기 위해 최적화 PostgreSQL을에 관심이있을 수 있습니다.

2. ==============================

   2.설명서를 따라한다 사용, 복사 테이블에 ... 이진은 "빠른 텍스트 및 CSV 형식보다 다소"입니다. 만 삽입하는 수백만 개의 행이있는 경우이 작업을 수행하고 바이너리 데이터 편안합니다.

   설명서를 따라한다 사용, 복사 테이블에 ... 이진은 "빠른 텍스트 및 CSV 형식보다 다소"입니다. 만 삽입하는 수백만 개의 행이있는 경우이 작업을 수행하고 바이너리 데이터 편안합니다.

   여기서 이진 입력으로 psycopg2를 사용 파이썬 예 제조법이다.

3. ==============================

   3.우수한 크레이그 링어의 포스트 및 depesz의 블로그 게시물에 더하여, 당신은 트랜잭션 내에서 준비된 문 삽입을 사용하여 ODBC (psqlodbc) 인터페이스를 통해 삽입 속도를하려는 경우, 당신이 그것을 만들기 위해해야 ​​할 몇 가지 추가 가지가있다 빠른 작업 :

   우수한 크레이그 링어의 포스트 및 depesz의 블로그 게시물에 더하여, 당신은 트랜잭션 내에서 준비된 문 삽입을 사용하여 ODBC (psqlodbc) 인터페이스를 통해 삽입 속도를하려는 경우, 당신이 그것을 만들기 위해해야 ​​할 몇 가지 추가 가지가있다 빠른 작업 :

   불행하게도, 그래서 psqlodbc "구현"준비가 삽입 일련의 명령문을 실행하여 SQLBulkOperations 수동으로 위의 단계까지 코드에 대한 빠른 삽입 한 요구 사항을 달성하기 위해.

4. ==============================

   4.오늘 같은 문제에 대한 6 시간의 주위에 보냈다. 인서트 (모든 방법 100K 당 1 분에 아래로) 크게 성능 싱크 다음 행 (총 30MI 점 만점) 5MI 때까지 최대 (100K 당 3 초 미만)과 '일반'속도로 이동합니다.

   오늘 같은 문제에 대한 6 시간의 주위에 보냈다. 인서트 (모든 방법 100K 당 1 분에 아래로) 크게 성능 싱크 다음 행 (총 30MI 점 만점) 5MI 때까지 최대 (100K 당 3 초 미만)과 '일반'속도로 이동합니다.

   나는 바로 고기에 일을하고 절단하지 않은 모든 것들을 나열하지 않습니다.

   나는 행복하게 100K 당 3 초 이하의 일정한 속도로 목적지로 흘러 차 (GUID를했다) 대상 테이블에 키와 내 30MI 또는 행 떨어졌다.

5. ==============================

   5.당신이 (정확히이 아니다) UUID를 가진 colums를 삽입하고 (나는 아직 말씀 드릴 수 없습니다) @Dennis 응답에 추가 무슨 일이 생긴 경우, gen_random_uuid를 사용하는 것보다 조언 수 () (인을 (PG 9.4 및 pgcrypto 모듈 필요) 많은) 빠른 uuid_generate_v4 이상 ()

   당신이 (정확히이 아니다) UUID를 가진 colums를 삽입하고 (나는 아직 말씀 드릴 수 없습니다) @Dennis 응답에 추가 무슨 일이 생긴 경우, gen_random_uuid를 사용하는 것보다 조언 수 () (인을 (PG 9.4 및 pgcrypto 모듈 필요) 많은) 빠른 uuid_generate_v4 이상 ()

   =# explain analyze select uuid_generate_v4(),* from generate_series(1,10000);
                                                           QUERY PLAN
   ---------------------------------------------------------------------------------------------------------------------------
    Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=11.674..10304.959 rows=10000 loops=1)
    Planning time: 0.157 ms
    Execution time: 13353.098 ms
   (3 filas)
   

   대

   
   =# explain analyze select gen_random_uuid(),* from generate_series(1,10000);
                                                           QUERY PLAN
   --------------------------------------------------------------------------------------------------------------------------
    Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=252.274..418.137 rows=10000 loops=1)
    Planning time: 0.064 ms
    Execution time: 503.818 ms
   (3 filas)
   

   또한, 그것을 할 수있는 제안 공식적인 방법입니다

   ~에서 2시간 ~이 떨어 뜨린 삽입 시간 행 3.7M 10 분 거리에 있습니다.

6. ==============================

   6.즉 당신을위한 옵션이 있다면 최적의 삽입 성능을 위해 인덱스를 비활성화합니다. 그 외에는, 더 나은 하드웨어 (디스크, 메모리)도 도움이됩니다

   즉 당신을위한 옵션이 있다면 최적의 삽입 성능을 위해 인덱스를 비활성화합니다. 그 외에는, 더 나은 하드웨어 (디스크, 메모리)도 도움이됩니다

7. ==============================

   7.나뿐만 아니라이 삽입 성능 문제가 발생했습니다. 내 솔루션은 삽입 작업을 완료하기 위해 일부 이동 루틴을 산란한다. 한편, SetMaxOpenConns 그렇지 않으면 너무 많은 오픈 연결 오류가 경고 할 것 적절한 번호가 부여되어야한다.

   나뿐만 아니라이 삽입 성능 문제가 발생했습니다. 내 솔루션은 삽입 작업을 완료하기 위해 일부 이동 루틴을 산란한다. 한편, SetMaxOpenConns 그렇지 않으면 너무 많은 오픈 연결 오류가 경고 할 것 적절한 번호가 부여되어야한다.

   db, _ := sql.open() 
   db.SetMaxOpenConns(SOME CONFIG INTEGER NUMBER) 
   var wg sync.WaitGroup
   for _, query := range queries {
       wg.Add(1)
       go func(msg string) {
           defer wg.Done()
           _, err := db.Exec(msg)
           if err != nil {
               fmt.Println(err)
           }
       }(query)
   }
   wg.Wait()
   

   로딩 속도가 더 빠른 내 프로젝트에 대한 많은입니다. 이 코드는 어떻게 작동하는지 생각을했다. 독자는 쉽게 수정할 수 있어야합니다.

from https://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql by cc-by-sa and MIT license