Sdílet prostřednictvím

Osvědčené postupy pro hromadné nahrávání dat do flexibilního 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ž 1 000 řá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 COPY příkaz. COPY Pokud příkaz není nemožné, je batch INSERT další nejlepší metodou. Vícevláknové zpracování pomocí COPY příkazu je optimální pro hromadné načítání dat.

Postup pro nahrání hromadných dat

Tady je postup hromadného nahrání dat na flexibilní server Azure Database for PostgreSQL.

Krok 1: Příprava dat

Ujistěte se, že jsou data pro databázi čistá a správně naformátovaná.

Krok 2: Volba metody načítání

Vyberte příslušnou metodu načítání na základě velikosti a složitosti vašich dat.

Krok 3: Provedení metody načítání

Spuštěním zvolené metody načítání nahrajte data do databáze.

Krok 4: Ověření dat

Po nahrání ověřte, že se data správně načetla do databáze.

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

Tady jsou 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 všechny indexy vypustit. Vytváření indexů po načtení dat je vždy efektivnější.

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 vyřadit 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

Před počátečním načtením dat doporučujeme vyřadit omezení cizího klíče a po dokončení načtení 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. Pokud ale změna není správně použitá, může ponechat data nekonzistentní.

Nepřilogované tabulky

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

Použití nepřilogovaných tabulek zrychluje načítání dat. Data zapsaná do nepřipsaných tabulek se nezapisují do protokolu s předstihem pro zápis.

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

  • auto vacuum': It's best to turn off automatického úklidu během počátečního načítání dat. 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 auto vacuum.

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á urychlit vytváření automatického vakua, 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 do instance 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: Tato možnost může být zapnutá. Povolení tohoto parametru může při protokolování a dekompresi v průběhu přehrání WAL (Write-ahead Log) kompresovat některé další náklady na procesor.

Doporučení

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 zajistěte minimální protokolování nebo ho zakažte společně (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 vytvoření indexů a přidá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

Osvědčené postupy pro přírůstkové načítání dat jsou popsány tady:

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.
  • Automatické úklid by se aktivovalo jenom 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. V podřízené tabulce byste mohli indexovat cizí klíče, aby se vyhledávání urychlila.
  • Dotazy, kde můžete zobrazit nadřazené a podřízené tabulky spojování u klíčových sloupců.

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ýznamnější hodnotu, například 15 minut, může snížit vstupně-výstupní zatížení, ale nevýhodou je, že obnovení trvá déle, pokud dojde k chybovému ukončení. 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. Následující příklad ukazuje jeden způsob, jak dorazit na správnou hodnotu pro max_wal_size.

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 running the second time','LSN value when run the first time')/1024/1024/1024,2) WAL_CHANGE_GB;
  • wal_compression: Tato možnost může být zapnutá. Povolením tohoto parametru se můžou stát další náklady na procesor při komprimaci během protokolování WAL a dekomprese během přehrávání WAL.

Související obsah