Sdílet prostřednictvím

Osvědčené postupy pro hromadné nahrávání dat na flexibilním serveru Azure Database for PostgreSQL

  • Článek

PLATÍ PRO: Flexibilní server Azure Database for PostgreSQL

Tento článek popisuje různé metody hromadného načítání dat na flexibilním serveru Azure Database for PostgreSQL spolu s osvědčenými postupy pro počáteční načítání dat v prázdných databázích i přírůstkových načítání dat.

Metody načítání

Následující metody načítání dat jsou uspořádány v pořadí od nejvíce časově náročných po nejméně časově náročné:

  • Spusťte příkaz s jedním záznamem INSERT .
  • Dávkové do 100 až 1000 řádků na potvrzení. Blok transakce můžete použít k zabalení více záznamů na potvrzení.
  • Spusťte INSERT s více hodnotami řádků.
  • Spusťte příkaz COPY.

Upřednostňovanou metodou načítání dat do databáze je použití COPY příkazu. COPY Pokud příkaz není možný, je použití dávky INSERT další nejlepší metodou. Vícevláknové zpracování pomocí COPY příkazu je optimální metodou hromadného načítání dat.

Osvědčené postupy pro počáteční načtení dat

Vyřazení indexů

Před počátečním načtením dat doporučujeme v tabulkách odstranit všechny indexy. Po načtení dat je vždy efektivnější vytvářet indexy.

Omezení přetažení

Hlavní omezení poklesu jsou popsána zde:

  • Omezení jedinečných klíčů

    Pokud chcete dosáhnout silného výkonu, doporučujeme před počátečním načtením dat odstranit jedinečná omezení klíče a po dokončení načítání dat je znovu vytvořit. Vyřazení omezení jedinečných klíčů však zruší ochranu proti duplicitním datům.

  • Omezení cizího klíče

    Doporučujeme před počátečním načtením dat odstranit omezení cizího klíče a po dokončení načítání dat je znovu vytvořit.

    Změna parametru session_replication_role tak, aby replica se také zakázaly všechny kontroly cizího klíče. Mějte však na paměti, že provedení změny může ponechat data v nekonzistentním stavu, pokud se správně nepoužívá.

Nepřilogované tabulky

Než je použijete při počátečním načtení dat, zvažte výhody a nevýhody použití nezalogovaných tabulek.

Díky použití nepřilogovaných tabulek se data načítají rychleji. Data zapsaná do nepřipsaných tabulek se nezapisují do protokolu s předstihem zápisu.

Nevýhody použití nepřilogovaných tabulek jsou:

  • Nejsou v bezpečí. Nelogovaná tabulka se po chybovém ukončení nebo vypnutí automaticky zkrátí.
  • Data z nelogovaných tabulek se nedají replikovat na pohotovostní servery.

Pokud chcete vytvořit nepřiřazenou tabulku nebo změnit existující tabulku na nepřiřazenou tabulku, použijte následující možnosti:

  • Vytvořte novou nepřiřazenou tabulku pomocí následující syntaxe:

    CREATE UNLOGGED TABLE <tablename>;

  • Převeďte existující zaprotokolovanou tabulku na nezalogovanou tabulku pomocí následující syntaxe:

    ALTER TABLE <tablename> SET UNLOGGED;

Ladění parametrů serveru

  • autovacuum: Během počátečního načtení dat je nejlepší vypnout autovacuum. Po dokončení počátečního načtení doporučujeme spustit příručku VACUUM ANALYZE pro všechny tabulky v databázi a pak zapnout autovacuum.

Poznámka:

Postupujte podle doporučení, pouze pokud je dostatek paměti a místa na disku.

  • maintenance_work_mem: U instance flexibilního serveru Azure Database for PostgreSQL je možné nastavit maximálně 2 gigabajty (GB). maintenance_work_mem pomáhá zrychlit vytváření automatického úklidu, indexu a cizího klíče.

  • checkpoint_timeout: V instanci checkpoint_timeout flexibilního serveru Azure Database for PostgreSQL je možné hodnotu zvýšit na maximálně 24 hodin z výchozího nastavení 5 minut. Doporučujeme zvýšit hodnotu na 1 hodinu před počátečním načtením dat na instanci flexibilního serveru Azure Database for PostgreSQL.

  • checkpoint_completion_target: Doporučujeme hodnotu 0,9.

  • max_wal_size: Můžete nastavit maximální povolenou hodnotu v instanci flexibilního serveru Azure Database for PostgreSQL, což je 64 GB při počátečním načtení dat.

  • wal_compression: Je možné zapnout. Povolením tohoto parametru můžou vzniknout další náklady na procesor vynaložené na kompresi během protokolování do protokolu WAL (Write-ahead Log) a při dekompresi během přehrávání WAL.

Doporučení flexibilního serveru Azure Database for PostgreSQL

Než začnete s počátečním zatížením dat instance flexibilního serveru Azure Database for PostgreSQL, doporučujeme:

  • Zakažte vysokou dostupnost na serveru. Můžete ho povolit po dokončení počátečního načtení na primárním serveru.
  • Po dokončení počátečního načtení dat vytvořte repliky pro čtení.
  • Během počátečního načítání dat ho zajistěte minimální nebo úplně zakažte (například: zakažte pgaudit, pg_stat_statements, úložiště dotazů).

Opětovné vytvoření indexů a přidání omezení

Za předpokladu, že jste indexy a omezení před počátečním načtením vynechali, doporučujeme k vytváření indexů a přidávání omezení použít vysoké hodnoty ( maintenance_work_mem jak už jsme zmínili dříve). Kromě toho můžete od PostgreSQL verze 11 upravit následující parametry pro rychlejší paralelní vytvoření indexu po počátečním načtení dat:

  • max_parallel_workers: Nastaví maximální počet pracovních procesů, které může systém podporovat pro paralelní dotazy.

  • max_parallel_maintenance_workers: Řídí maximální počet pracovních procesů, které lze použít v CREATE INDEX.

Indexy můžete vytvořit také tak, že vytvoříte doporučená nastavení na úrovni relace. Tady je příklad postupu:

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

Osvědčené postupy pro přírůstkové načítání dat

Tabulky oddílů

Vždy doporučujeme rozdělit velké tabulky. Mezi výhody dělení, zejména při přírůstkových zatíženích, patří:

  • Vytváření nových oddílů na základě nových rozdílových dat umožňuje efektivní přidávání nových dat do tabulky.
  • Údržba tabulek je jednodušší. Během přírůstkového načítání dat můžete oddíl vypustit, abyste se vyhnuli časově náročným odstraňováním ve velkých tabulkách.
  • Funkce Autovacuum se aktivuje pouze u oddílů, které byly změněny nebo přidány během přírůstkových načítání, což usnadňuje udržování statistik v tabulce.

Udržování aktuálních statistik tabulek

Monitorování a údržba statistik tabulek je důležitá pro výkon dotazů v databázi. Patří sem také scénáře, ve kterých máte přírůstkové načtení. PostgreSQL používá proces démona autovacuum k vyčištění mrtvých řazených kolekcí členů a analýze tabulek, aby se statistiky aktualizovaly. Další informace najdete v tématu Monitorování a ladění automatického úklidu.

Vytváření indexů pro omezení cizího klíče

Vytváření indexů pro cizí klíče v podřízených tabulkách může být výhodné v následujících scénářích:

  • Data se aktualizují nebo odstraní v nadřazené tabulce. Při aktualizaci nebo odstranění dat v nadřazené tabulce se vyhledávání provádí v podřízené tabulce. Pokud chcete vyhledávání zrychlit, mohli byste indexovat cizí klíče v podřízené tabulce.
  • Dotazy, kde uvidíte spojení nadřazených a podřízených tabulek v klíčových sloupcích.

Identifikace nepoužívaných indexů

Identifikujte nepoužívané indexy v databázi a odstraňte je. Indexy představují režijní náklady na načtení dat. Čím méně indexů v tabulce, tím lepší je výkon během příjmu dat.

Nepoužívané indexy můžete identifikovat dvěma způsoby: úložištěm dotazů a dotazem na použití indexu.

Úložiště dotazů

Funkce Úložiště dotazů pomáhá identifikovat indexy, které je možné vynechat na základě vzorců využití dotazů v databázi. Podrobné pokyny najdete v tématu Úložiště dotazů.

Po povolení úložiště dotazů na serveru můžete pomocí následujícího dotazu identifikovat indexy, které se dají vynechat připojením k azure_sys databázi.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Využití indexu

K identifikaci nepoužívaných indexů můžete použít také následující dotaz:

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;

Sloupce number_of_scansa tuples_fetched , tuples_readby indikovaly index usage.number_of_scans hodnotu sloupce nula bodů jako index, který se nepoužívá.

Ladění parametrů serveru

Poznámka:

Postupujte podle doporučení v následujících parametrech jenom v případě, že je dostatek paměti a místa na disku.

  • maintenance_work_mem: Tento parametr lze nastavit na maximálně 2 GB na instanci flexibilního serveru Azure Database for PostgreSQL. maintenance_work_mem pomáhá urychlit vytváření indexů a přidávání cizích klíčů.

  • checkpoint_timeout: V instanci checkpoint_timeout flexibilního serveru Azure Database for PostgreSQL je možné hodnotu zvýšit na 10 nebo 15 minut z výchozího nastavení 5 minut. Zvýšení checkpoint_timeout na větší hodnotu, například 15 minut, může snížit vstupně-výstupní zatížení, ale nevýhodou je, že obnovení v případě chybového ukončení trvá déle. Před provedením změny doporučujeme pečlivě zvážit.

  • checkpoint_completion_target: Doporučujeme hodnotu 0,9.

  • max_wal_size: Tato hodnota závisí na SKU, úložišti a úloze. Jeden ze způsobů, jak získat správnou hodnotu, max_wal_size je uveden v následujícím příkladu.

    Během špičky pracovní doby dorazí na hodnotu následujícím způsobem:

    a. Spuštěním následujícího dotazu převezměte aktuální pořadové číslo protokolu WAL (LSN):

    SELECT pg_current_wal_lsn ();

    b. Počkejte na checkpoint_timeout počet sekund. Spuštěním následujícího dotazu převezměte aktuální WAL LSN:

    SELECT pg_current_wal_lsn ();

    c. Pomocí těchto dvou výsledků zkontrolujte rozdíl v 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: Je možné zapnout. Povolením tohoto parametru můžou vzniknout další náklady na procesor strávené při kompresi během protokolování WAL a při dekompresi během přehrávání WAL.

Další kroky