Dela via

Metodtips för massuppladdning av data till Azure Database for PostgreSQL – flexibel server

  • Artikel

GÄLLER FÖR: Azure Database for PostgreSQL – flexibel server

I den här artikeln beskrivs olika metoder för massinläsning av data i Azure Database for PostgreSQL – flexibel server, tillsammans med metodtips för både inledande datainläsningar i tomma databaser och inkrementella datainläsningar.

Inläsningsmetoder

Följande datainläsningsmetoder ordnas i ordning från de mest tidskrävande till minst tidskrävande:

  • Kör ett kommando med en post INSERT .
  • Batcha till 100 till 1 000 rader per incheckning. Du kan använda ett transaktionsblock för att omsluta flera poster per incheckning.
  • Kör INSERT med flera radvärden.
  • Kör kommandot COPY.

Den bästa metoden för att läsa in data i en databas är kommandot COPY . Om kommandot COPY inte är omöjligt är batch INSERT den näst bästa metoden. Flera trådar med ett COPY kommando är optimalt för att läsa in data i bulk.

Steg för att ladda upp massdata

Här följer steg för att massuppladda data till en flexibel Azure Database for PostgreSQL-server.

Steg 1: Förbereda dina data

Se till att dina data är rena och korrekt formaterade för databasen.

Steg 2: Välj inläsningsmetod

Välj lämplig inläsningsmetod baserat på dina datas storlek och komplexitet.

Steg 3: Kör inläsningsmetoden

Kör den valda inläsningsmetoden för att ladda upp dina data till databasen.

Steg 4: Verifiera data

När du har laddat upp kontrollerar du att data har lästs in korrekt i databasen.

Metodtips för inledande datainläsningar

Här följer metodtips för inledande datainläsningar.

Ta bort index

Innan du gör en inledande datainläsning rekommenderar vi att du tar bort alla index i tabellerna. Det är alltid effektivare att skapa indexen när data har lästs in.

Släpp begränsningar

De viktigaste släppbegränsningarna beskrivs här:

  • Begränsningar för unik nyckel

För att uppnå starka prestanda rekommenderar vi att du släpper unika nyckelbegränsningar innan en första datainläsning och återskapar dem när datainläsningen har slutförts. Om du tar bort unika nyckelbegränsningar avbryts dock skyddet mot duplicerade data.

  • Begränsningar för sekundärnyckel

Vi rekommenderar att du tar bort begränsningar för sekundärnyckeln innan den första datainläsningen och återskapar dem när datainläsningen har slutförts.

Om du ändrar parametern session_replication_role till replica inaktiveras även alla kontroller av sekundärnycklar. Men om ändringen inte används korrekt kan data vara inkonsekventa.

Ologgade tabeller

Överväg för- och nackdelar med ologgade tabeller innan du använder dem i inledande datainläsningar.

Om du använder ologgade tabeller går det snabbare att läsa in data. Data som skrivs till ologgade tabeller skrivs inte till loggen för framåtskrivning.

Nackdelarna med att använda ologgade tabeller är:

  • De är inte kraschsäkra. En ologgad tabell trunkeras automatiskt efter en krasch eller en oren avstängning.
  • Data från ologgade tabeller kan inte replikeras till väntelägesservrar.

Om du vill skapa en ologgad tabell eller ändra en befintlig tabell till en ologgad tabell använder du följande alternativ:

  • Skapa en ny ologgad tabell med hjälp av följande syntax:

    CREATE UNLOGGED TABLE <tablename>;

  • Konvertera en befintlig loggad tabell till en ologgad tabell med hjälp av följande syntax:

    ALTER TABLE <tablename> SET UNLOGGED;

Justering av serverparameter

  • auto vacuum': It's best to turn off automatiskt vakuum" under den inledande databelastningen. När den första inläsningen är klar rekommenderar vi att du kör en handbok VACUUM ANALYZE för alla tabeller i databasen och sedan aktiverar auto vacuum.

Kommentar

Följ rekommendationerna här bara om det finns tillräckligt med minne och diskutrymme.

  • maintenance_work_mem: Kan anges till högst 2 GIGABYTE (GB) på en flexibel Azure Database for PostgreSQL-serverinstans. maintenance_work_mem hjälper till att påskynda autovakuum, index och sekundärnyckelskapande.

  • checkpoint_timeout: På en flexibel Azure Database for PostgreSQL-serverinstans checkpoint_timeout kan värdet ökas till högst 24 timmar från standardinställningen på 5 minuter. Vi rekommenderar att du ökar värdet till 1 timme innan du först läser in data på den flexibla serverinstansen Azure Database for PostgreSQL.

  • checkpoint_completion_target: Vi rekommenderar värdet 0,9.

  • max_wal_size: Kan anges till det högsta tillåtna värdet på en flexibel Azure Database for PostgreSQL-serverinstans, som är 64 GB när du utför den första datainläsningen.

  • wal_compression: Detta kan aktiveras. Aktivering av den här parametern kan medföra vissa extra CPU-kostnader för komprimering under loggloggning (WAL) och dekomprimering under WAL-repris.

Rekommendationer

Innan du påbörjar en första datainläsning på azure database for PostgreSQL– flexibel serverinstans rekommenderar vi att du:

  • Inaktivera hög tillgänglighet på servern. Du kan aktivera den när den första inläsningen har slutförts på den primära.
  • Skapa skrivskyddade repliker när den första datainläsningen har slutförts.
  • Gör loggningen minimal eller inaktivera allt under inledande datainläsningar (till exempel inaktivera pgaudit, pg_stat_statements, frågearkiv).

Återskapa index och lägga till begränsningar

Om du har tagit bort indexen och begränsningarna före den första inläsningen rekommenderar vi att du använder höga värden i maintenance_work_mem (som tidigare nämnts) för att skapa index och lägga till begränsningar. Från och med PostgreSQL version 11 kan dessutom följande parametrar ändras för snabbare parallell indexskapande efter den första datainläsningen:

  • max_parallel_workers: Anger det maximala antalet arbetare som systemet kan stödja för parallella frågor.

  • max_parallel_maintenance_workers: Styr det maximala antalet arbetsprocesser som kan användas i CREATE INDEX.

Du kan också skapa indexen genom att göra de rekommenderade inställningarna på sessionsnivå. Här är ett exempel på hur du gör det:

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

Metodtips för inkrementella datainläsningar

Metodtips för inkrementell datainläsning beskrivs här:.

Partitionstabeller

Vi rekommenderar alltid att du partitioner stora tabeller. Några fördelar med partitionering, särskilt under inkrementella belastningar, är:

  • Att skapa nya partitioner baserat på nya delta gör det effektivt att lägga till nya data i tabellen.
  • Det blir enklare att underhålla tabeller. Du kan släppa en partition under en inkrementell databelastning för att undvika tidskrävande borttagningar i stora tabeller.
  • Autovacuum utlöses endast på partitioner som har ändrats eller lagts till under inkrementella belastningar, vilket gör det enklare att underhålla statistik i tabellen.

Underhålla uppdaterad tabellstatistik

Det är viktigt att övervaka och underhålla tabellstatistik för frågeprestanda i databasen. Detta omfattar även scenarier där du har inkrementella belastningar. PostgreSQL använder daemonprocessen autovacuum för att rensa upp döda tupplar och analysera tabellerna för att hålla statistiken uppdaterad. Mer information finns i Övervakning och justering av autovacuum.

Skapa index för begränsningar för sekundärnyckel

Det kan vara fördelaktigt att skapa index på sekundärnycklar i de underordnade tabellerna i följande scenarier:

  • Datauppdateringar eller borttagningar i den överordnade tabellen. När data uppdateras eller tas bort i den överordnade tabellen utförs sökningar på den underordnade tabellen. Du kan indexa sekundärnycklar på den underordnade tabellen för att göra sökningar snabbare.
  • Frågor, där du kan se överordnade och underordnade tabeller som kopplas till nyckelkolumner.

Identifiera oanvända index

Identifiera oanvända index i databasen och släpp dem. Index är ett omkostnader för datainläsningar. Ju färre index i en tabell, desto bättre prestanda vid datainmatning.

Du kan identifiera oanvända index på två sätt: via Query Store och en indexanvändningsfråga.

Query Store

Funktionen Query Store hjälper dig att identifiera index som kan tas bort baserat på frågeanvändningsmönster i databasen. Stegvis vägledning finns i Query Store.

När du har aktiverat Query Store på servern kan du använda följande fråga för att identifiera index som kan tas bort genom att ansluta till azure_sys databas.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Indexanvändning

Du kan också använda följande fråga för att identifiera oanvända index:

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;

Kolumnerna number_of_scans, tuples_readoch tuples_fetched anger indexet usage.number_of_scans kolumnvärdet noll punkter som ett index som inte används.

Justering av serverparameter

Kommentar

Följ rekommendationerna i följande parametrar endast om det finns tillräckligt med minne och diskutrymme.

  • maintenance_work_mem: Den här parametern kan anges till högst 2 GB på den flexibla serverinstansen Azure Database for PostgreSQL. maintenance_work_mem hjälper till att påskynda skapande av index och tillägg av sekundärnyckel.

  • checkpoint_timeout: På den flexibla serverinstansen checkpoint_timeout Azure Database for PostgreSQL kan värdet ökas till 10 eller 15 minuter från standardinställningen på 5 minuter. Om du ökar checkpoint_timeout till ett större värde, till exempel 15 minuter, kan du minska I/O-belastningen, men nackdelen är att det tar längre tid att återställa om det uppstår en krasch. Vi rekommenderar att du noga överväger innan du gör ändringen.

  • checkpoint_completion_target: Vi rekommenderar värdet 0,9.

  • max_wal_size: Det här värdet beror på SKU, lagring och arbetsbelastning. I följande exempel visas ett sätt att komma fram till rätt värde för max_wal_size.

Under hög belastning på kontorstid anländer du till ett värde genom att göra följande:

a. Ta det aktuella WAL-loggsekvensnumret (LSN) genom att köra följande fråga:

SELECT pg_current_wal_lsn ();

b. Vänta i checkpoint_timeout antal sekunder. Ta det aktuella WAL LSN genom att köra följande fråga:

SELECT pg_current_wal_lsn ();

c. Använd de två resultaten för att kontrollera skillnaden i GB:

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: Detta kan aktiveras. Aktivering av den här parametern kan medföra en extra CPU-kostnad för komprimering under WAL-loggning och dekomprimering under WAL-repris.

Relaterat innehåll