다음을 통해 공유

‘Azure Database for PostgreSQL - 유연한 서버의 대량 데이터 업로드’ 모범 사례

  • 아티클

적용 대상: Azure Database for PostgreSQL - 유연한 서버

이 문서에서는 빈 데이터베이스의 초기 데이터 로드 및 증분 데이터 로드에 대한 모범 사례와 함께 Azure Database for PostgreSQL 유연한 서버에서 대량으로 데이터를 로드하는 다양한 방법을 설명합니다.

로드 방법

다음 데이터 로드 방법은 최대 시간 소모에서 최소 시간 소모까지 순서대로 정리되어 있습니다.

  • 단일 레코드 INSERT 명령을 실행합니다.
  • 커밋당 100~1000개 행으로 일괄 처리합니다. 트랜잭션 블록을 사용하여 커밋당 여러 레코드를 래핑할 수 있습니다.
  • 여러 행 값으로 INSERT를 실행합니다.
  • COPY 명령을 실행합니다.

데이터베이스에 데이터를 로드하는 기본 방법은 COPY 명령을 사용하는 것입니다. COPY 명령을 사용할 수 없는 경우에는 일괄 처리 INSERT를 사용하는 것이 그 다음으로 좋은 방법입니다. COPY 명령을 사용하는 다중 스레딩은 데이터를 대량으로 로드하는 최적의 방법입니다.

초기 데이터 로드 모범 사례

인덱스 삭제

초기 데이터 로드를 수행하기 전에 테이블의 모든 인덱스를 삭제하는 것이 좋습니다. 데이터가 로드된 후 인덱스를 만드는 것이 언제나 더 효율적입니다.

제약 조건 삭제

여기서 주요 제약 조건 삭제에 대해 설명합니다.

  • 고유 키 제약 조건

    강력한 성능을 구현하려면 초기 데이터 로드 전에 고유 키 제약 조건을 삭제하고 데이터 로드가 완료된 후 다시 만드는 것이 좋습니다. 그러나 고유 키 제약 조건을 삭제하면 중복된 데이터에 대한 보호가 취소됩니다.

  • 외래 키 제약 조건

    초기 데이터 로드 전에 외래 키 제약 조건을 삭제하고 데이터 로드가 완료된 후 다시 만드는 것이 좋습니다.

    session_replication_role 매개 변수를 replica로 변경하면 모든 외래 키 검사도 사용하지 않도록 설정됩니다. 그러나 변경할 경우 데이터가 제대로 사용되지 않으면 일관되지 않은 상태로 남을 수 있습니다.

기록되지 않는 테이블

초기 데이터 로드에서 사용하기 전에 기록되지 않은 테이블을 사용할 때의 장단점을 고려합니다.

기록되지 않은 테이블을 사용하면 데이터 로드가 빨라집니다. 기록되지 않은 테이블에 기록된 데이터는 미리 쓰기 로그에 기록되지 않습니다.

기록되지 않은 테이블을 사용할 때의 단점은 다음과 같습니다.

  • 크래시로부터 안전하지 않습니다. 기록되지 않은 테이블은 크래시 또는 불완전 종료 후 자동으로 잘립니다.
  • 기록되지 않은 테이블의 데이터는 대기 서버로 복제할 수 없습니다.

기록되지 않은 테이블을 만들거나 기존 테이블을 기록되지 않은 테이블로 변경하려면 다음 옵션을 사용합니다.

  • 다음 구문을 사용해 기록되지 않는 테이블을 새로 만듭니다.

    CREATE UNLOGGED TABLE <tablename>;

  • 다음 구문을 사용해 기존의 기록된 테이블을 기록되지 않는 테이블로 변환합니다.

    ALTER TABLE <tablename> SET UNLOGGED;

서버 매개 변수 튜닝

  • autovacuum: 초기 데이터 로드 중에 autovacuum을 해제하는 것이 가장 좋습니다. 초기 로드가 완료된 후에는 데이터베이스의 모든 테이블에서 설명서 VACUUM ANALYZE를 실행한 다음 autovacuum를 켜는 것이 좋습니다.

참고 항목

메모리 및 디스크 공간이 충분한 경우에만 여기에 있는 권장 사항을 따릅니다.

  • maintenance_work_mem: Azure Database for PostgreSQL 유연한 서버 인스턴스에서 최대 2GB로 설정할 수 있습니다. maintenance_work_mem 는 자동 진공, 인덱스 및 외래 키 생성 속도를 향상하는 데 도움이 됩니다.

  • checkpoint_timeout: Azure Database for PostgreSQL 유연한 서버 인스턴스에서 checkpoint_timeout 값은 기본 설정인 5분에서 최대 24시간까지 늘릴 수 있습니다. Azure Database for PostgreSQL 유연한 서버 인스턴스에 처음으로 데이터를 로드하기 전에 값을 1시간으로 늘리는 것이 좋습니다.

  • checkpoint_completion_target: 0.9 값을 사용하는 것이 좋습니다.

  • max_wal_size: Azure Database for PostgreSQL 유연한 서버 인스턴스에서 허용되는 최대 값(초기 데이터 로드를 수행하는 동안 64GB)으로 설정할 수 있습니다.

  • wal_compression: 켤 수 있습니다. 이 매개 변수를 사용하도록 설정하면 WAL(미리 쓰기 로그) 로깅 시 압축과 WAL 재생 시 압축 해제에 약간의 추가 CPU 비용이 발생할 수 있습니다.

Azure Database for PostgreSQL 유연한 서버 권장 사항

Azure Database for PostgreSQL 유연한 서버 인스턴스에서 초기 데이터 로드를 시작하기 전에 다음을 수행하는 것이 좋습니다.

  • 서버에서 고가용성을 사용하지 않도록 설정합니다. 주 서버에서 초기 로드가 완료된 후에 사용하도록 설정할 수 있습니다.
  • 초기 데이터 로드가 완료된 후 읽기 복제본을 만듭니다.
  • 초기 데이터 로드 중에 로깅을 최소화하거나 사용하지 않도록 설정합니다(예: pgaudit, pg_stat_statements, 쿼리 저장소 사용 안 함).

인덱스를 다시 만들기 및 제약 조건 추가

초기 로드 전에 인덱스 및 제약 조건을 삭제했다고 가정하면 (앞에서 설명한 대로) maintenance_work_mem에 높은 값을 사용하여 인덱스를 만들고 제약 조건을 추가하는 것이 좋습니다. 또한 PostgreSQL 버전 11부터는 다음 매개 변수를 수정하여 초기 데이터 로드 후 병렬 인덱스를 더 빨리 만들 수 있습니다.

  • max_parallel_workers: 시스템에서 병렬 쿼리를 지원할 수 있는 최대 작업자 수를 설정합니다.

  • max_parallel_maintenance_workers: CREATE INDEX에서 사용할 수 있는 최대 작업자 프로세스 수를 제어합니다.

세션 수준에서 권장 설정을 만들어 인덱스를 만들 수도 있습니다. 그 사용 방법의 예는 다음과 같습니다.

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

증분 데이터 로드 모범 사례

파티션 테이블

항상 큰 테이블을 분할하는 것이 좋습니다. 특히 증분 로드 시 분할에는 다음과 같은 이점이 있습니다.

  • 새 델타를 기반으로 새 파티션을 만들면 테이블에 새 데이터를 효율적으로 추가할 수 있습니다.
  • 테이블 유지가 더 쉬워집니다. 증분 데이터 로드 중에 파티션을 삭제하면 큰 테이블에서 삭제할 때 시간이 많이 걸리는 것을 방지할 수 있습니다.
  • Autovacuum은 증분 로드 중에 변경되거나 추가된 파티션에서만 트리거되어 테이블에서 통계 유지가 더 쉽습니다.

최신 테이블 통계 유지

테이블 통계 모니터링 및 유지는 데이터베이스의 쿼리 성능에 중요한 영향을 줍니다. 여기에는 증분 로드가 있는 시나리오도 포함됩니다. PostgreSQL은 autovacuum 디먼 프로세스를 사용하여 데드 튜플을 정리하고 테이블을 분석하여 통계를 업데이트된 상태로 유지합니다. 자세한 내용은 Autovacuum 모니터링 및 튜닝을 참조하세요.

외래 키 제약 조건에 대한 인덱스 만들기

자식 테이블에 외래 키에 대한 인덱스를 만들면 다음과 같은 시나리오에서 도움이 될 수 있습니다.

  • 부모 테이블에서 데이터 업데이트 또는 삭제 부모 테이블에서 데이터를 업데이트하거나 삭제하면 자식 테이블에서 조회가 수행됩니다. 더 빠르게 조회하려면 자식 테이블에서 외세 키를 인덱싱하면 됩니다.
  • 키 열에서 부모 테이블과 자식 테이블의 조인을 볼 수 있는 쿼리입니다.

사용하지 않은 인덱스 식별

데이터베이스에서 사용되지 않은 인덱스를 식별하고 삭제합니다. 인덱스는 데이터 로드에 관한 오버헤드입니다. 테이블의 인덱스가 적을수록 데이터 수집 시 성능이 좋습니다.

사용하지 않은 인덱스는 쿼리 저장소 및 인덱스 사용량 쿼리의 두 가지 방법으로 식별할 수 있습니다.

쿼리 저장소

쿼리 저장소 기능은 데이터베이스의 쿼리 사용 패턴에 따라 삭제할 수 있는 인덱스를 식별하는 데 도움이 됩니다. 단계별 지침은 쿼리 저장소를 참조하세요.

서버에서 쿼리 저장소를 사용하도록 설정한 후 다음 쿼리를 사용해 azure_sys 데이터베이스에 연결하여 삭제할 수 있는 인덱스를 식별할 수 있습니다.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

인덱스 사용

다음 쿼리를 사용하여 사용되지 않은 인덱스를 식별할 수도 있습니다.

SELECT 
    t.schemaname, 
    t.tablename, 
    c.reltuples::bigint                            AS num_rows, 
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size, 
    psai.indexrelname                              AS index_name, 
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, 
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", 
    psai.idx_scan                                  AS number_of_scans, 
    psai.idx_tup_read                              AS tuples_read, 
    psai.idx_tup_fetch                             AS tuples_fetched 
FROM 
    pg_tables t 
    LEFT JOIN pg_class c ON t.tablename = c.relname 
    LEFT JOIN pg_index i ON c.oid = i.indrelid 
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid 
WHERE 
    t.schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY 1, 2;

number_of_scans, tuples_read 및 tuples_fetched 열에는 인덱스 usage.number_of_scans 열 값 0이 사용하지 않은 인덱스로 표시됩니다.

서버 매개 변수 튜닝

참고 항목

메모리와 디스크 공간이 충분한 경우에만 다음 매개 변수의 권장 사항을 따릅니다.

  • maintenance_work_mem: 이 매개 변수는 Azure Database for PostgreSQL 유연한 서버 인스턴스에서 최대 2GB로 설정할 수 있습니다. maintenance_work_mem 는 인덱스 생성 및 외래 키 추가 속도를 향상하는 데 도움이 됩니다.

  • checkpoint_timeout: Azure Database for PostgreSQL 유연한 서버 인스턴스에서 checkpoint_timeout 값을 기본 설정인 5분에서 10분 또는 15분으로 늘릴 수 있습니다. checkpoint_timeout을 15분과 같이 큰 값으로 늘리면 I/O 로드가 줄 수 있지만, 크래시가 있을 경우 복구하는 데 시간이 더 오래 걸린다는 단점이 있습니다. 신중하게 고려한 후에 변경하는 것이 좋습니다.

  • checkpoint_completion_target: 0.9 값을 사용하는 것이 좋습니다.

  • max_wal_size: 이 값은 SKU, 스토리지 및 워크로드에 따라 달라집니다. max_wal_size에 대한 올바른 값에 도달하는 한 가지 방법은 다음 예제에 나와 있습니다.

    사용량이 많은 업무 시간 동안 다음을 수행하여 값에 도달합니다.

    a. 다음 쿼리를 실행하여 현재 WAL LSN(로그 시퀀스 번호)을 가져옵니다.

    SELECT pg_current_wal_lsn ();

    b. checkpoint_timeout 시간(초)을 기다립니다. 다음 쿼리를 실행하여 현재 WAL LSN을 가져옵니다.

    SELECT pg_current_wal_lsn ();

    c. 두 결과를 사용하여 차이(GB)를 확인합니다.

    SELECT round (pg_wal_lsn_diff('LSN value when run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression: 켤 수 있습니다. 이 매개 변수를 사용하도록 설정하면 WAL 로깅 시 압축과 WAL 재생 시 압축 해제에 약간의 추가 CPU 비용이 발생할 수 있습니다.

다음 단계