Share via

Metodtips för massuppladdning av data i 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 är ordnade i ordning från 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 att använda COPY kommandot . Om kommandot COPY inte är möjligt är batch INSERT den näst bästa metoden. Multi-threading med ett COPY kommando är den optimala metoden för att läsa in data i bulk.

Metodtips för inledande datainläsningar

Ta bort index

Innan du gör en inledande datainläsning rekommenderar vi att du släpper 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 före 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 släpper 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. Tänk dock på att ändringen kan lämna data i ett inkonsekvent tillstånd om de inte används korrekt.

Ologgade tabeller

Överväg fördelarna och nackdelarna med att använda ologgade tabeller innan du använder dem i inledande datainläsningar.

Om du använder ologgade tabeller går datainläsningen snabbare. 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

  • autovacuum: Under den inledande datainläsningen är det bäst att inaktivera autovacuum. När den inledande inläsningen är klar rekommenderar vi att du kör en handbok VACUUM ANALYZE för alla tabeller i databasen och sedan aktiverar autovacuum.

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 autovacuum, 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 läser in data från början på azure database for PostgreSQL– flexibel serverinstans.

  • 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: Kan aktiveras. Aktivering av den här parametern kan medföra vissa extra CPU-kostnader som spenderas på komprimering under loggloggning (WAL) och dekomprimering under WAL-repris.

Rekommendationer för flexibel Azure Database for PostgreSQL-server

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 den helt under inledande datainläsningar (till exempel inaktivera pgaudit, pg_stat_statements, frågearkiv).

Återskapa index och lägga till begränsningar

Förutsatt att du tappade 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

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. Om du vill göra sökningar snabbare kan du indexeras sekundärnycklar i den underordnade tabellen.
  • Frågor, där du kan se sammanfogning av överordnade och underordnade tabeller i 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. Att öka checkpoint_timeout till ett större värde, till exempel 15 minuter, kan 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. Ett sätt att komma fram till rätt värde för max_wal_size visas i följande exempel.

    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. checkpoint_timeout Vänta i 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 run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression: Kan aktiveras. Aktivering av den här parametern kan medföra vissa extra CPU-kostnader som spenderas på komprimering under WAL-loggning och på dekomprimering under WAL-repris.

Nästa steg