Postgre에서 삽입 성능을 향상시키는 방법SQL

Postgre에서 삽입 성능을 향상시키는 방법SQL

저는 Postgres 삽입 성능을 테스트하고 있습니다.데이터 유형으로 번호가 있는 열 하나가 있는 테이블이 있습니다.그것에는 색인도 있습니다.다음 쿼리를 사용하여 데이터베이스를 채웠습니다.

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

저는 위의 쿼리로 한 번에 10000개의 행을 매우 빠르게 400만 개를 삽입했습니다.데이터베이스가 6백만 행에 도달한 후 성능이 15분마다 1백만 행으로 급격히 떨어졌습니다.삽입 성능을 높이기 위한 요령이 있습니까?저는 이 프로젝트에서 최적의 삽입 성능이 필요합니다.

5GB RAM이 있는 시스템에서 Windows 7 Pro 사용.

Postgre에서 데이터베이스 채우기 참조SQL 매뉴얼, depesz의 평소와 다름없는 주제에 대한 훌륭한 기사, 그리고 이 SO 질문.

이 답변은 기존 DB에 데이터를 대량으로 로드하거나 새로운 DB를 작성하는 것에 대한 것입니다. 출력을 사용한 DB 복원 성능 또는 실행에 관심이 있는 경우에는 스키마+데이터 복원을 완료한 후 트리거 및 인덱스 생성과 같은 작업을 수행한 이후에는 이러한 작업이 대부분 적용되지 않습니다.

해야 할 일이 많습니다.은 이적인솔다가것입다니는져로 입니다.UNLOGGED인덱스가 없는 테이블을 로그로 변경하고 인덱스를 추가합니다.에서 변경은 SQL 9.4에서 .UNLOGGED로그에 기록합니다.9.5 추가ALTER TABLE ... SET LOGGED당신이 이 일을 할 수 있도록 허락합니다.

대량 가져오기를 위해 데이터베이스를 오프라인으로 전환할 수 있는 경우 를 사용합니다.

그렇지 않은 경우:

• 테이블에서 트리거 사용 안 함

• 가져오기를 시작하기 전에 인덱스를 삭제하고 나중에 인덱스를 다시 만듭니다. 동일한 데이터를 한 경로에 점진적으로 추가하는 것보다 한 경로에 인덱스를 작성하는 데 훨씬 적은 시간이 소요되며, 결과 인덱스는 훨씬 더 압축됩니다.

• 단일 트랜잭션 내에서 가져오기를 수행하는 경우 외부 키 제약 조건을 삭제하고 가져오기를 수행한 다음 커밋하기 전에 제약 조건을 다시 만드는 것이 안전합니다.가져오기가 여러 트랜잭션으로 분할된 경우 잘못된 데이터를 가져올 수 있으므로 이 작업을 수행하지 마십시오.

• 가한경우를 사용합니다.COPY에 INSERTs

• ▁할 수 경우COPY 다값사고려을 사용을 합니다.INSERT실용적이라면당신은 이미 이것을 하고 있는 것처럼 보입니다.단일에 너무 많은 값을 나열하려고 하지 않음VALUES하지만; 그 값들은 메모리에 두어 번 더 맞아야 하므로, 진술당 수백 개로 유지합니다.

• 트랜잭션당 수십만 또는 수백만 개의 삽입을 수행하여 명시적인 트랜잭션에 삽입물을 배치합니다.실제적인 제한은 없지만 배치를 사용하면 입력 데이터에서 각 배치의 시작을 표시하여 오류를 복구할 수 있습니다.다시 말하지만, 당신은 이미 이것을 하고 있는 것처럼 보입니다.

• 사용하다synchronous_commit=off그리고 엄청 큰commit_delayfsync() 비용을 절감할 수 있습니다.하지만 당신이 당신의 일을 큰 거래로 분류했다면 이것은 큰 도움이 되지 않을 것입니다.

• INSERT또는COPY몇 개의 연결점에서 병렬로 병렬로.하드웨어의 디스크 하위 시스템에 따라 몇 개가 결정됩니다. 직접 연결된 스토리지를 사용하는 경우 물리적 하드 드라이브당 하나의 연결을 사용하는 것이 일반적입니다.

• 이높을 설정합니다.max_wal_size값)checkpoint_segments를 선택하고 사용 가능으로 설정합니다.log_checkpoints포스트그레를 보세요.SQL 로그는 체크포인트가 너무 자주 발생하는 것에 대해 불평하지 않는지 확인합니다.

• 만당 약신당 ▁post▁▁if▁)▁and▁don▁you▁your▁▁entire를만▁you▁system▁only▁the▁during▁sql▁cor▁and▁losing▁ifruption▁set데rophic▁the약▁stop▁catast▁pg▁can,'▁mindt충클시가데동당다신돌할▁database이템져스오는)이베스안▁the▁to이 Pg를 중지할 수 있습니다.fsync=offPg, your import, Pg를 설정합니다.fsync=onWAL 구성을 참조하십시오.Postgre의 데이터베이스에 이미 관심 있는 데이터가 있는 경우 이 작업을 수행하지 마십시오.SQL 설치.설정하는 경우fsync=off설정할 수도 있습니다.full_page_writes=off데이터베이스 손상 및 데이터 손실을 방지하려면 가져온 후 다시 켜야 합니다.페이지 매뉴얼의 비내구성 설정을 참조하십시오.

또한 시스템을 조정해야 합니다.

• 가능한 한 좋은 품질의 SSD를 스토리지에 사용합니다.안정적이고 전원 보호된 라이트백 캐시를 갖춘 우수한 SSD는 커밋 속도를 엄청나게 빠르게 합니다.위의 조언을 따르면 디스크 플러시/디스크 수를 줄일 수 있습니다.fsync()s - 하지만 여전히 큰 도움이 될 수 있습니다.데이터 보관에 신경 쓰지 않는 한 적절한 정전 보호 없이 저렴한 SSD를 사용하지 마십시오.

• 직접 연결 스토리지에 RAID 5 또는 RAID 6을 사용하는 경우 지금 중지합니다.데이터를 백업하고 RAID 어레이를 RAID 10으로 재구성한 후 다시 시도하십시오.RAID 5/6은 대용량 캐시를 갖춘 우수한 RAID 컨트롤러가 도움이 될 수 있지만 대량 쓰기 성능에는 적합하지 않습니다.

• 하드웨어 RAID 컨트롤러를 대용량 배터리 백업 쓰기 캐시와 함께 사용할 수 있는 옵션이 있다면 커밋이 많은 워크로드의 쓰기 성능을 크게 향상시킬 수 있습니다.commit_delay와 함께 비동기 커밋을 사용하거나 대량 로드 중에 빅 트랜잭션을 적게 수행하는 경우에는 그다지 도움이 되지 않습니다.

• WALWAL)을합니다.pg_wal또는pg_xlog(이전 버전의 경우)는 별도의 디스크/디스크 어레이에 있습니다.동일한 디스크에 별도의 파일 시스템을 사용하는 것은 의미가 없습니다.사람들은 종종 WAL에 RAID 1 쌍을 사용하기로 선택합니다.이는 커밋 비율이 높은 시스템에 더 많은 영향을 미치며, 기록되지 않은 테이블을 데이터 로드 대상으로 사용하는 경우에는 거의 영향을 미치지 않습니다.

또한 Optimise Postgre에 관심이 있을 수 있습니다.빠른 테스트를 위한 SQL.

저는 오늘 같은 문제로 6시간 정도를 보냈습니다.인서트는 최대 5MI(총 30MI 중) 행까지 '정규' 속도(100K당 3초 미만)로 이동한 다음 성능이 크게 저하됩니다(100K당 1분까지).

저는 작동하지 않고 바로 고기를 잘라낸 모든 것들을 나열하지 않을 것입니다.

기본 키를 대상 테이블(GUID)에 떨어뜨려 30MI 또는 행이 100K당 3초 미만의 일정한 속도로 대상으로 흐르게 되었습니다.

사용하다COPY table TO ... WITH BINARY문서에 따르면 이는 "텍스트 및 CSV 형식보다 다소 빠릅니다."삽입할 행이 수백만 개이고 이진 데이터에 익숙한 경우에만 이 작업을 수행합니다.

다음은 바이너리 입력이 있는 사이코프2를 사용한 파이썬의 레시피 예제입니다.

Craig Ringer의 훌륭한 게시물과 depesz의 블로그 게시물 외에도, 트랜잭션 내의 준비된 문장 삽입을 사용하여 ODBC(psqlodbc) 인터페이스를 통해 삽입 속도를 높이고 싶다면, 빠르게 작동하기 위해 몇 가지 추가적으로 해야 할 일이 있습니다.

1. of 를 "Transaction"으로 하여 "합니다.Protocol=-1연결 문자열에 있습니다.기본적으로 psqlodbc는 전체 트랜잭션이 아닌 각 문에 대해 SAVEPOINT를 만들어 삽입 속도를 늦춥니다.
2. statement를 하기 위해서는 prepared 를 지정해야 .UseServerSidePrepare=1연결 문자열에 있습니다.이 옵션을 선택하지 않으면 클라이언트는 삽입되는 각 행과 함께 전체 삽입 문을 보냅니다.
3. 사여 문서 커자사 안함용을 사용하여 각 사용 안 함SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);
4. 되면 모행을삽으다면사음용트커다밋니합랜을션을 사용하여 을 .SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);트랜잭션을 명시적으로 열 필요가 없습니다.

을 합니다.SQLBulkOperations준비되지 않은 일련의 삽입 문을 실행하여 가장 빠른 삽입을 위해 위의 단계를 수동으로 코딩해야 합니다.

UUID가 포함된 열을 삽입할 경우(정확하게는 해당되지 않음), @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)

또한 제안된 공식적인 방법입니다.

메모

임의로 생성된(버전 4) UUID만 필요한 경우 pgcrypto 모듈의 gen_random_uuid() 함수를 대신 사용하는 것이 좋습니다.

이렇게 하면 3.7M 행에 대해 삽입 시간이 ~2시간에서 ~10분으로 단축됩니다.

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

이 삽입 성능 문제도 발생했습니다.제 해결책은 삽입 작업을 끝내기 위해 몇 가지 바둑 루틴을 만드는 것입니다. 사에이그.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()

제 프로젝트는 로딩 속도가 훨씬 빠릅니다.이 코드 조각은 어떻게 작동하는지에 대한 아이디어를 제공했습니다.독자들은 그것을 쉽게 수정할 수 있어야 합니다.

언급URL : https://stackoverflow.com/questions/12206600/how-to-speed-up-insertion-performance-in-postgresql