Aanbevolen procedures voor het bulksgewijs uploaden van gegevens naar Azure Database for PostgreSQL flexibele server

In dit artikel worden verschillende methoden besproken voor het bulksgewijs laden van gegevens naar een flexibele Azure Database for PostgreSQL-serverinstantie, samen met aanbevolen procedures voor zowel initiële gegevensbelastingen in lege databases als incrementele gegevensbelastingen.

Laadmethoden

De volgende methoden voor het laden van gegevens worden gerangschikt op volgorde van de meeste tijdrovende tot de minst tijdrovende:

  • Voer een opdracht met één record INSERT uit.
  • Verdeel in batches van 100 tot 1.000 rijen per commit. U kunt een transactieblok gebruiken om meerdere records per doorvoer te verpakken.
  • Uitvoeren van INSERT met meerdere rijwaarden.
  • Voer de COPY opdracht uit.

De voorkeursmethode voor het laden van gegevens in een database is de COPY opdracht. Als de COPY opdracht niet onmogelijk is, is batch INSERT de volgende beste methode. Multithreading met een COPY opdracht is optimaal voor het bulksgewijs laden van gegevens.

Stappen voor het uploaden van bulkgegevens

Hier volgen stappen voor het bulksgewijs uploaden van gegevens naar een flexibele serverinstantie van Azure Database for PostgreSQL.

Stap 1: Uw gegevens voorbereiden

Zorg ervoor dat uw gegevens zijn opgeschoond en op de juiste wijze zijn opgemaakt voor de database.

Stap 2: De laadmethode kiezen

Selecteer de juiste laadmethode op basis van de grootte en complexiteit van uw gegevens.

Stap 3: De laadmethode uitvoeren

Voer de gekozen laadmethode uit om uw gegevens te uploaden naar de database.

Stap 4: De gegevens controleren

Controleer na het uploaden of de gegevens correct zijn geladen in de database.

Aanbevolen procedures voor het laden van initiële gegevens

Hier volgen aanbevolen procedures voor het laden van initiële gegevens.

Indexen verwijderen

Voordat u een eerste gegevensbelasting uitvoert, raden we u aan alle indexen in de tabellen te verwijderen. Het maken van de indexen nadat de gegevens zijn geladen, is altijd efficiënter.

Beperkingen voor neerzetten

De belangrijkste beperkingen voor slepen en neerzetten worden hier beschreven:

  • Unieke sleutelbeperkingen

Om sterke prestaties te bereiken, raden we u aan unieke sleutelbeperkingen te verwijderen voordat een eerste gegevensbelasting wordt geladen en deze opnieuw te maken nadat de gegevensbelasting is voltooid. Het verwijderen van unieke sleutelbeperkingen annuleert echter de beveiliging tegen dubbele gegevens.

  • Beperkingen voor refererende sleutels

We raden aan foreign key-constraints te verwijderen vóór het initiële laden van gegevens en deze opnieuw aan te maken nadat het laden van de gegevens is voltooid.

Als u de parameter session_replication_role wijzigt in replica, worden ook alle controles op foreign keys uitgeschakeld. Als de wijziging echter niet goed wordt gebruikt, kan deze gegevens inconsistent laten.

Tabellen zonder logging

Houd rekening met de voor- en nadelen van ongelogde tabellen voordat u ze gebruikt bij initiële gegevensladingen.

Het gebruik van tabellen zonder logging versnelt het laden van data. Gegevens die naar tabellen zonder logging worden geschreven, worden niet naar de write-ahead-log geschreven.

De nadelen van het gebruik van tabellen zonder logboekregistratie zijn:

  • Ze zijn niet crash-veilig. Een ongelogde tabel wordt na een crash of onjuiste afsluiting automatisch leeggemaakt.
  • Gegevens uit ongelogde tabellen kunnen niet worden gerepliceerd naar standbyservers.

Als u een niet-vastgelegde tabel wilt maken of een bestaande tabel wilt wijzigen in een niet-vastgelegde tabel, gebruikt u de volgende opties:

  • Maak een nieuwe ongelogde tabel met behulp van de volgende syntaxis:

    CREATE UNLOGGED TABLE <tablename>;

  • Converteer een bestaande vastgelegde tabel naar een niet-vastgelegde tabel met behulp van de volgende syntaxis:

    ALTER TABLE <tablename> SET UNLOGGED;

Parameterafstemming

  • auto vacuum': It's best to turn off 'auto-vacuum' tijdens het initiële laden van gegevens. Nadat de eerste belasting is voltooid, raden we u aan een handmatige bewerking VACUUM ANALYZE uit te voeren voor alle tabellen in de database en vervolgens in te schakelen auto vacuum.

Opmerking

Volg hier alleen de aanbevelingen als er voldoende geheugen en schijfruimte beschikbaar zijn.

  • maintenance_work_mem: kan worden ingesteld op maximaal 2 GIGABYTE (GB) op een flexibele Server-instantie van Azure Database for PostgreSQL. maintenance_work_mem versnelt automatisch vacuüm draaien, indexen en het aanmaken van foreign keys.

  • checkpoint_timeout: Op een exemplaar van een flexibele Azure Database for PostgreSQL-server kan de checkpoint_timeout waarde worden verhoogd tot maximaal 24 uur vanaf de standaardinstelling van 5 minuten. We raden u aan de waarde te verhogen naar 1 uur voordat u voor het eerst gegevens laadt in het flexibele serverexemplaar van Azure Database for PostgreSQL.

  • checkpoint_completion_target: We raden een waarde van 0,9 aan.

  • max_wal_size: Kan worden ingesteld op de maximaal toegestane waarde op een exemplaar van een flexibele Azure Database for PostgreSQL-server. Dit is 64 GB terwijl u de initiële gegevensbelasting uitvoert.

  • wal_compression: Dit kan worden ingeschakeld. Als u deze parameter inschakelt, kan dit extra CPU-kosten met zich meebrengen voor compressie tijdens het schrijven van het write-ahead log (WAL) en voor decompressie tijdens het opnieuw afspelen van het WAL.

Aanbevelingen

Voordat u begint met het voor het eerst laden van gegevens in het flexibele-serverexemplaar van Azure Database for PostgreSQL, raden we u aan om het volgende te doen:

  • Hoge beschikbaarheid op de server uitschakelen. U kunt deze inschakelen nadat de eerste belasting is voltooid op de primaire.
  • Maak leesreplica’s nadat het initiële laden van gegevens is voltooid.
  • Maak logboekregistratie minimaal of schakel het allemaal uit tijdens de initiële gegevensbelastingen (bijvoorbeeld pgaudit, pg_stat_statements, querystore uitschakelen).

Indexen opnieuw maken en beperkingen toevoegen

Ervan uitgaande dat u de indexen en beperkingen vóór de eerste belasting hebt verwijderd, raden we u aan hoge waarden te gebruiken in maintenance_work_mem (zoals eerder vermeld) om indexen te maken en beperkingen toe te voegen. Bovendien kunnen, vanaf PostgreSQL versie 11, de volgende parameters worden gewijzigd voor het sneller maken van parallelle indexen na de initiële gegevensbelasting:

  • max_parallel_workers: Stelt het maximum aantal workers in dat het systeem kan ondersteunen voor parallelle query's.

  • max_parallel_maintenance_workers: Bepaalt het maximum aantal werkprocessen, dat kan worden gebruikt in CREATE INDEX.

U kunt de indexen ook maken door de aanbevolen instellingen op sessieniveau te maken. Hier volgt een voorbeeld van hoe u dit doet:

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);

Aanbevolen procedures voor incrementele gegevensbelastingen

Aanbevolen procedures voor incrementele gegevensbelastingen worden hier beschreven:

Partitietabellen

U wordt altijd aangeraden grote tabellen te partitioneren. Enkele voordelen van partitioneren, met name tijdens incrementele belastingen, zijn:

  • Door nieuwe partities te maken op basis van nieuwe delta's, wordt het efficiënt om nieuwe gegevens aan de tabel toe te voegen.
  • Het onderhouden van tabellen wordt eenvoudiger. U kunt een partitie verwijderen tijdens een incrementele gegevensbelasting om tijdrovende verwijderingen in grote tabellen te voorkomen.
  • Autovacuum wordt alleen geactiveerd op partities die zijn gewijzigd of toegevoegd tijdens incrementele belastingen, waardoor het onderhouden van statistieken in de tabel eenvoudiger wordt.

Bijgewerkte tabelstatistieken onderhouden

Het bewaken en onderhouden van tabelstatistieken is belangrijk voor queryprestaties in de database. Dit omvat ook scenario's waarin u incrementele belastingen hebt. PostgreSQL gebruikt het autovacuum-daemonproces om dode tuples op te schonen en de tabellen te analyseren om de statistieken actueel te houden. Zie Autovacuum-bewaking en -afstemming voor meer informatie.

Indexen maken voor beperkingen van vreemde sleutels

Het aanmaken van indexen op vreemde sleutels in de onderliggende tabellen kan nuttig zijn in de volgende scenario's:

  • Gegevensupdates of -verwijderingen in de hoofdtabel. Wanneer gegevens in de bovenliggende tabel worden bijgewerkt of verwijderd, worden opzoekbewerkingen uitgevoerd in de onderliggende tabel. U kunt vreemde sleutels op de onderliggende tabel indexeren om zoekacties te versnellen.
  • Query's, waarin u kunt zien hoe bovenliggende en onderliggende tabellen via sleutelkolommen worden gekoppeld.

Ongebruikte indexen identificeren

Identificeer ongebruikte indexen in de database en zet ze neer. Indexen zijn een overhead voor het laden van gegevens. Hoe minder indexen in een tabel, hoe beter de prestaties tijdens gegevensopname.

U kunt ongebruikte indexen op twee manieren identificeren: door Query Store en een indexgebruiksquery.

Query Store

De functie Query Store helpt bij het identificeren van indexen, die kunnen worden verwijderd op basis van querygebruikspatronen in de database. Zie Query Store voor stapsgewijze instructies.

Nadat u Query Store op de server hebt ingeschakeld, kunt u de volgende query gebruiken om indexen te identificeren die kunnen worden verwijderd door verbinding te maken met azure_sys database.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Gebruik van indexen

U kunt ook de volgende query gebruiken om ongebruikte indexen te identificeren:

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;

De kolommen number_of_scans, tuples_read en tuples_fetched geven het indexgebruik aan. Een waarde van nul in de kolom usage.number_of_scans duidt op een index die niet wordt gebruikt.

Parameterafstemming

Opmerking

Volg de aanbevelingen in de volgende parameters alleen als er voldoende geheugen en schijfruimte beschikbaar zijn.

  • maintenance_work_mem: Deze parameter kan worden ingesteld op maximaal 2 GB op het flexibele serverexemplaar van Azure Database for PostgreSQL. maintenance_work_mem helpt het maken van indexen en het toevoegen van vreemde sleutels te versnellen.

  • checkpoint_timeout: Op het exemplaar van Azure Database for PostgreSQL Flexible Server kan de waarde van checkpoint_timeout worden verhoogd van de standaardinstelling van 5 minuten naar 10 of 15 minuten. Het verhogen checkpoint_timeout naar een significantere waarde, zoals 15 minuten, kan de I/O-belasting verminderen, maar het nadeel is dat het langer duurt om te herstellen als er een crash is. We raden u aan zorgvuldig na te denken voordat u de wijziging aanbrengt.

  • checkpoint_completion_target: We raden een waarde van 0,9 aan.

  • max_wal_size: deze waarde is afhankelijk van de SKU, opslag en workload. Het volgende voorbeeld laat een manier zien om tot de juiste waarde voor max_wal_size te komen.

Tijdens piekuren komt u op een waarde door het volgende te doen:

a. Bepaal het huidige WAL-logreeksnummer (LSN) door de volgende query uit te voeren:

SELECT pg_current_wal_lsn ();

b. Wacht op het checkpoint_timeout aantal seconden. Haal de huidige WAL LSN op door de volgende query uit te voeren:

SELECT pg_current_wal_lsn ();

c. Gebruik de twee resultaten om het verschil in GB te controleren:

SELECT round (pg_wal_lsn_diff('LSN value when running the second time','LSN value when run the first time')/1024/1024/1024,2) WAL_CHANGE_GB;
  • wal_compression: Dit kan worden ingeschakeld. Het inschakelen van deze parameter kan extra CPU-kosten met zich meebrengen voor compressie tijdens WAL-loggen en voor decomprimeren tijdens het afspelen van WAL.

Verwante inhoud

  • Last updated on