Delen via

Aanbevolen procedures voor het bulksgewijs uploaden van gegevens naar Azure Database for PostgreSQL - Flexible Server

  • Artikel

VAN TOEPASSING OP: Azure Database for PostgreSQL - Flexibele server

In dit artikel worden verschillende methoden besproken voor het bulksgewijs laden van gegevens in Azure Database for PostgreSQL flexibele server, 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.
  • Batchgewijs in 100 tot 1000 rijen per doorvoer. U kunt een transactieblok gebruiken om meerdere records per doorvoer te verpakken.
  • Uitvoeren INSERT met meerdere rijwaarden.
  • Voer de opdracht COPY 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 Azure Database for PostgreSQL-server.

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

Het is raadzaam om beperkingen voor refererende sleutels te verwijderen voordat de eerste gegevens worden geladen en deze opnieuw te maken nadat de gegevens zijn geladen.

Als u de session_replication_role parameter wijzigt, replica worden ook alle controles van refererende sleutels uitgeschakeld. Als de wijziging echter niet goed wordt gebruikt, kan deze gegevens inconsistent laten.

Niet-vastgelegde tabellen

Houd rekening met de voor- en nadelen van niet-vastgelegde tabellen voordat u ze in eerste gegevens laadt.

Het gebruik van niet-vastgelegde tabellen versnelt het laden van gegevens. Gegevens die naar niet-vastgelegde tabellen worden geschreven, worden niet naar het write-ahead-logboek geschreven.

De nadelen van het gebruik van niet-vastgelegde tabellen zijn:

  • Ze zijn niet crash-veilig. Een niet-vastgelegde tabel wordt automatisch afgekapt na een crash of onreine afsluiting.
  • Gegevens uit niet-vastgelegde tabellen kunnen niet worden gerepliceerd naar stand-byservers.

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 niet-vastgelegde 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;

Serverparameter afstemmen

  • auto vacuum': It's best to turn off automatisch vacuüm' tijdens de initiële gegevensbelasting. 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.

Notitie

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 helpt bij het versnellen van automatisch vacuüm, index en het maken van refererende sleutels.

  • 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 om de waarde te verhogen tot 1 uur voordat u in eerste instantie gegevens laadt op het flexibele serverexemplaren 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, kunnen er extra CPU-kosten in rekening worden gebracht voor compressie tijdens logboekregistratie voor write-ahead logboeken (WAL) en decompressie tijdens wal-herhaling.

Aanbevelingen

Voordat u begint met een eerste gegevensbelasting op het flexibele serverexemplaren van Azure Database for PostgreSQL, raden we u aan het volgende te doen:

  • Hoge beschikbaarheid op de server uitschakelen. U kunt deze inschakelen nadat de eerste belasting is voltooid op de primaire.
  • Leesreplica's maken nadat de initiële gegevensbelasting 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: Hiermee stelt u het maximum aantal werkrollen 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:

  • Als u nieuwe partities maakt op basis van nieuwe verschillen, worden nieuwe gegevens efficiënt aan de tabel toegevoegd.
  • 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 maakt gebruik van het autovacuum daemon-proces om dode tuples op te schonen en de tabellen te analyseren om de statistieken bijgewerkt te houden. Zie Autovacuum-bewaking en -afstemming voor meer informatie.

Indexen maken voor beperkingen voor refererende sleutels

Het maken van indexen voor refererende sleutels in de onderliggende tabellen kan nuttig zijn in de volgende scenario's:

  • Gegevens worden bijgewerkt of verwijderd in de bovenliggende tabel. Wanneer gegevens in de bovenliggende tabel worden bijgewerkt of verwijderd, worden zoekopdrachten uitgevoerd op de onderliggende tabel. U kunt refererende sleutels in de onderliggende tabel indexeren om zoekopdrachten sneller te maken.
  • Query's, waar u bovenliggende en onderliggende tabellen kunt zien die worden gecombineerd in sleutelkolommen.

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;

Indexgebruik

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 number_of_scanskolommen , tuples_readen tuples_fetched kolommen geven de index aan usage.number_of_scans kolomwaarde van nulpunten als een index die niet wordt gebruikt.

Serverparameter afstemmen

Notitie

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 serverexemplaren van Azure Database for PostgreSQL. maintenance_work_mem helpt het maken van indexen en toevoegingen van refererende sleutels te versnellen.

  • checkpoint_timeout: Op het flexibele serverexemplaren van Azure Database for PostgreSQL kan de checkpoint_timeout waarde worden verhoogd tot 10 of 15 minuten vanaf de standaardinstelling van 5 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. In het volgende voorbeeld ziet u een manier om de juiste waarde voor max_wal_sizete komen.

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

a. Voer de volgende query uit om het huidige WAL-logboekreeksnummer (LSN) uit te voeren:

SELECT pg_current_wal_lsn ();

b. Wacht op het checkpoint_timeout aantal seconden. Neem de huidige WAL LSN 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. Als u deze parameter inschakelt, kunnen extra CPU-kosten in rekening worden gebracht voor het comprimeren tijdens wal-logboekregistratie en het decomprimeren tijdens wal-herhaling.

Gerelateerde inhoud