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

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

Laddningsmetoder

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

  • Kör kommandot för en enskild post INSERT.
  • Dela upp i 100 till 1 000 rader per commit. Du kan använda ett transaktionsblock för att omfatta flera poster i varje commit.
  • Kör INSERT med flera radvärden.
  • Kör COPY kommandot.

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. Flertrådning med kommandot COPY är optimalt för att ladda in data i stora mängder.

Steg för att ladda upp massdata

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

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: Kontrollera 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 främmande nycklar

Vi rekommenderar att du tar bort begränsningar för främmande nycklar före den inledande datainläsningen och återskapar dem när datainläsningen har slutförts.

Att ändra parametern session_replication_role till replica inaktiverar också alla kontrollerna av främmande nycklar. Men om ändringen inte används korrekt kan det leda till inkonsekventa data.

Ologgade tabeller

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

Att använda ologgade tabeller snabbar upp inläsningen av data. Data som skrivs till ologgade tabeller skrivs inte till write-ahead-loggen.

Nackdelarna med att använda ologgade tabeller är:

  • De är inte kraschsäkra. En unlogged tabell töms automatiskt efter en krasch eller en felaktig 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;

Parameterjustering

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

Anmärkning

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) för en flexibel serverinstans i Azure Database for PostgreSQL. maintenance_work_mem hjälper till att påskynda automatisk vakuumering, indexering och skapande av främmande nycklar.

  • 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. Att aktivera den här parametern kan medföra viss extra CPU-belastning för komprimering vid WAL-loggning och dekomprimering vid återuppspelning av WAL.

Recommendations

Innan du gör en inledande datainläsning till instansen Azure Database for PostgreSQL – flexibel server rekommenderar vi att du:

  • Inaktivera hög tillgänglighet på servern. Du kan aktivera den när den inledande inläsningen har slutförts på den primära noden.
  • 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 partitionerar 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 på begränsningar för främmande nycklar

Det kan vara fördelaktigt att skapa index på främmande nycklar 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 skapa index för främmande nycklar i den underordnade tabellen för att göra sökningar snabbare.
  • Frågor, där du kan se överordnade och underordnade tabeller sammanfogas på nyckelkolumner.

Identifiera oanvända index

Identifiera oanvända index i databasen och släpp dem. Index medför en extra belastning vid datainläsning. 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_read och tuples_fetched visar att värdet noll i kolumnen usage.number_of_scans pekar på ett index som inte används.

Parameterjustering

Anmärkning

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 skapandet av index och att lägga till främmande nycklar.

  • 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. Hämta det aktuella WAL LSN-värdet 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

  • Last updated on