Ajánlott eljárások adatok tömeges feltöltéséhez az Azure Database for PostgreSQL-be

Ez a cikk a rugalmas Azure Database for PostgreSQL-kiszolgálópéldányba történő tömeges adatbetöltés különböző módszereit ismerteti, valamint az üres adatbázisok kezdeti adatbetöltésére és a növekményes adatbetöltésekre vonatkozó ajánlott eljárásokat.

Betöltési módszerek

A következő adatbetöltési módszerek a legtöbb időigényestől a legkevésbé időigényesig rendezve vannak:

• Futtasson egy egyrekordos INSERT parancsot.
• Köteg 100–1000 sorba véglegesítésenként. Tranzakcióblokk használatával véglegesítésenként több rekordot is beburkolhat.
• Futtassa INSERT több sorértékkel.
• Futtassa a következő parancsot: COPY.

Az adatok adatbázisba való betöltésének elsődleges módja a COPY parancs. Ha a COPY parancs nem lehetetlen, a batch INSERT a következő legjobb módszer. A parancsokkal történő COPY többszálasítás optimális az adatok tömeges betöltéséhez.

A tömeges adatok feltöltésének lépései

Az alábbi lépésekkel tömegesen tölthet fel adatokat egy rugalmas Azure Database for PostgreSQL-kiszolgálópéldányra.

1. lépés: Az adatok előkészítése

Győződjön meg arról, hogy az adatok tisztaak és megfelelően formázva lesznek az adatbázishoz.

2. lépés: A betöltési módszer kiválasztása

Válassza ki a megfelelő betöltési módszert az adatok mérete és összetettsége alapján.

3. lépés: A betöltési módszer végrehajtása

Futtassa a kiválasztott betöltési módszert az adatok adatbázisba való feltöltéséhez.

4. lépés: Az adatok ellenőrzése

A feltöltés után ellenőrizze, hogy az adatok megfelelően lettek-e betöltve az adatbázisba.

Ajánlott eljárások a kezdeti adatbetöltéshez

Az alábbiakban a kezdeti adatbetöltés ajánlott eljárásait találja.

Indexek elvetése

A kezdeti adatbetöltés előtt javasoljuk, hogy az összes indexet elvetje a táblákból. Az indexek létrehozása az adatok betöltése után mindig hatékonyabb.

Elvetési korlátozások

A fő lemorzsolódási kényszereket az alábbiakban ismertetjük:

• Egyedi kulcskorlátozások

Az erős teljesítmény érdekében javasoljuk, hogy a kezdeti adatbetöltés előtt ejtse le az egyedi kulcskorlátozásokat, és hozza létre újra azokat az adatbetöltés befejezése után. Az egyedi kulcskorlátozások elvetése azonban megszünteti a duplikált adatokkal szembeni védelmet.

• Idegenkulcs-korlátozások

Javasoljuk, hogy a kezdeti adatbetöltés előtt ejtse ki az idegenkulcs-korlátozásokat, és hozza létre újra azokat az adatbetöltés befejezése után.

A paraméter session_replication_role módosítása az replica összes idegenkulcs-ellenőrzést is letiltja. Ha azonban a módosítás nincs megfelelően használva, akkor az adatok inkonzisztensek maradnak.

Nem megjelölt táblák

A kezdeti adatbetöltés előtt vegye figyelembe a nem megjelölt táblák előnyeit és hátrányait.

A nem megjelölt táblák használata felgyorsítja az adatbetöltést. A nem megjelölt táblákba írt adatok nem lesznek beírva az előreírási naplóba.

A nem megjelölt táblák használatának hátrányai a következők:

• Nem összeomlásbiztosak. A rendszer automatikusan csonkolja a nem megjelölt táblákat összeomlás vagy tisztátalan leállítás után.
• A nem megjelölt táblákból származó adatok nem replikálhatók készenléti kiszolgálókra.

Ha nem megjelölt táblát szeretne létrehozni, vagy meglévő táblát nem megjelölt táblára szeretne módosítani, használja az alábbi beállításokat:

• Hozzon létre egy új, nem megjelölt táblát az alábbi szintaxissal:

  CREATE UNLOGGED TABLE <tablename>;
  

• Meglévő naplózott táblát az alábbi szintaxissal alakíthat át nem megjelölt táblává:

  ALTER TABLE <tablename> SET UNLOGGED;
  

Kiszolgálói paraméter finomhangolása

• auto vacuum': It's best to turn off automatikus vákuum" a kezdeti adatbetöltés során. A kezdeti betöltés befejezése után javasoljuk, hogy futtasson egy kézikönyvet VACUUM ANALYZE az adatbázis összes tábláján, majd kapcsolja be auto vacuum.

Feljegyzés

Csak akkor kövesse az itt található javaslatokat, ha elegendő memória és lemezterület áll rendelkezésre.

• maintenance_work_mem: Egy rugalmas Azure Database for PostgreSQL-kiszolgálón legfeljebb 2 gigabájtra (GB) állítható be. maintenance_work_mem segítségével felgyorsíthatja az automatikus vákuum, az index és a külső kulcs létrehozását.

• checkpoint_timeout: Rugalmas Azure Database for PostgreSQL-kiszolgálón az checkpoint_timeout érték az alapértelmezett 5 perctől legfeljebb 24 órára növelhető. Javasoljuk, hogy növelje az értéket 1 órára, mielőtt először betöltené az adatokat a rugalmas Azure Database for PostgreSQL-kiszolgálópéldányra.

• checkpoint_completion_target: A 0,9 értéket javasoljuk.

• max_wal_size: Beállítható a rugalmas Azure Database for PostgreSQL-kiszolgálópéldány megengedett maximális értékére, amely a kezdeti adatbetöltés során 64 GB.

• wal_compression: Ez bekapcsolható. Ennek a paraméternek az engedélyezése többletköltséget okozhat a tömörítéshez az előre írt naplók (WAL) naplózása és a WAL-visszajátszás közbeni dekompresszió során.

Ajánlások

Mielőtt megkezdené a rugalmas Azure Database for PostgreSQL-kiszolgálópéldány kezdeti adatbetöltését, javasoljuk, hogy:

• Tiltsa le a magas rendelkezésre állást a kiszolgálón. Az elsődleges betöltés befejezése után is engedélyezheti.
• Olvasási replikák létrehozása a kezdeti adatbetöltés befejezése után.
• A kezdeti adatbetöltések (például a pgaudit, a pg_stat_statements és a lekérdezéstár letiltása) során minimálisra kell tenni a naplózást, vagy tiltsa le az összeset együtt.

Indexek újbóli létrehozása és korlátozások hozzáadása

Feltételezve, hogy a kezdeti betöltés előtt elvetette az indexeket és a kényszereket, javasoljuk, hogy a (korábban említett) magas értékeket maintenance_work_mem használva hozzon létre indexeket, és adjon hozzá korlátozásokat. Ezenkívül a PostgreSQL 11-es verziójától kezdve a következő paraméterek módosíthatók a párhuzamos indexek gyorsabb létrehozásához a kezdeti adatbetöltés után:

• max_parallel_workers: Beállítja, hogy a rendszer hány feldolgozót támogat a párhuzamos lekérdezésekhez.

• max_parallel_maintenance_workers: A munkavégző folyamatok maximális számát szabályozza, amely a következőben CREATE INDEXhasználható: .

Az indexeket úgy is létrehozhatja, hogy az ajánlott beállításokat a munkamenet szintjén adja meg. Íme egy példa a következő műveletekre:

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

Ajánlott eljárások növekményes adatbetöltésekhez

A növekményes adatbetöltés ajánlott eljárásait itt találja:

Partíciótáblák

Mindig nagy táblák particionálását javasoljuk. A particionálás néhány előnye, különösen a növekményes terhelések esetén:

• Az új partíciók új különbözeteken alapuló létrehozása hatékonyabbá teszi az új adatok táblához való hozzáadását.
• A táblák karbantartása egyszerűbbé válik. A partíciókat növekményes adatbetöltés során is elvetheti, így elkerülheti a nagyméretű táblákban az időigényes törléseket.
• Az Autovacuum csak a növekményes terhelések során módosított vagy hozzáadott partíciókon aktiválódik, ami megkönnyíti a táblák statisztikáinak karbantartását.

Naprakész táblázatstatisztikák karbantartása

A táblastatisztikák monitorozása és karbantartása fontos az adatbázis lekérdezési teljesítményének szempontjából. Ez olyan forgatókönyveket is tartalmaz, amelyekben növekményes terhelések vannak. A PostgreSQL az autovacuum démonfolyamatot használja az elhalt gócok megtisztítására és a táblák elemzésére a statisztikák frissítéséhez. További információ: Autovacuum monitorozás és hangolás.

Indexek létrehozása idegenkulcs-korlátozásokhoz

A gyermektáblákban az idegen kulcsokra mutató indexek létrehozása a következő forgatókönyvekben lehet hasznos:

• Adatfrissítések vagy -törlések a szülőtáblában. Ha az adatok frissülnek vagy törlődnek a szülőtáblában, a rendszer a gyermektáblán hajtja végre a kereséseket. Az idegen kulcsokat indexelheti a gyermektáblán, így gyorsabban végezhet kereséseket.
• Lekérdezések, ahol a szülő- és gyermektáblák összekapcsolhatók a kulcsoszlopokon.

Nem használt indexek azonosítása

Azonosítsa a nem használt indexeket az adatbázisban, és dobja el őket. Az indexek többletterhelést jelentenek az adatbetöltéseken. Minél kevesebb index van egy táblában, annál jobb a teljesítmény az adatbetöltés során.

A nem használt indexeket kétféleképpen azonosíthatja: a Lekérdezéstár és az indexhasználati lekérdezés alapján.

Lekérdezéstár

A Lekérdezéstár funkció segít azonosítani az indexeket, amelyeket az adatbázis lekérdezéshasználati mintái alapján lehet elvetni. Részletes útmutatásért tekintse meg a Lekérdezéstárat.

Miután engedélyezte a lekérdezéstárat a kiszolgálón, az alábbi lekérdezéssel azonosíthatja azokat az indexeket, amelyek eldobhatók azure_sys adatbázishoz való csatlakozással.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Indexhasználat

A nem használt indexek azonosításához a következő lekérdezést is használhatja:

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;

A number_of_scans, tuples_readés tuples_fetched az oszlopok azt jelzik, hogy az index usage.number_of_scans nulla pont oszlopértéket nem használt indexként.

Kiszolgálói paraméter finomhangolása

Feljegyzés

Csak akkor kövesse az alábbi paraméterek javaslatait, ha elegendő memória és lemezterület áll rendelkezésre.

• maintenance_work_mem: Ez a paraméter legfeljebb 2 GB-ra állítható be a rugalmas Azure Database for PostgreSQL-kiszolgálópéldányon. maintenance_work_mem segítségével felgyorsíthatja az indexek létrehozását és a külső kulcsok hozzáadását.

• checkpoint_timeout: A rugalmas Azure Database for PostgreSQL-kiszolgálópéldányon az checkpoint_timeout érték 10 vagy 15 percre növelhető az alapértelmezett 5 perces beállítástól. Ha nagyobb értékre( például 15 percre) növekszik checkpoint_timeout , csökkentheti az I/O-terhelést, de hátránya, hogy összeomlás esetén a helyreállítás hosszabb időt vesz igénybe. Javasoljuk, hogy a módosítás előtt körültekintően mérlegelje.

• checkpoint_completion_target: A 0,9 értéket javasoljuk.

• max_wal_size: Ez az érték a termékváltozattól, a tárolástól és a számítási feladattól függ. Az alábbi példa egy módszert mutat be a megfelelő érték max_wal_sizeeléréséhez.

A csúcsidőszakban az alábbi művelet végrehajtásával érkezzen meg egy értékre:

a). Az alábbi lekérdezés futtatásával vegye le az aktuális WAL-naplósorozat-számot (LSN):

SELECT pg_current_wal_lsn ();

b. Várja meg a checkpoint_timeout másodpercek számát. Az alábbi lekérdezés futtatásával vegye át az aktuális WAL LSN-t:

SELECT pg_current_wal_lsn ();

c) A két eredmény alapján ellenőrizze a különbséget GB-ban:

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: Ez bekapcsolható. Ennek a paraméternek az engedélyezése többletköltséget jelenthet a WAL-naplózás és a WAL-visszajátszás közbeni tömörítéshez.

Kapcsolódó tartalom

• Az Azure Database for PostgreSQL magas processzorhasználatának hibaelhárítása.
• Az Azure Database for PostgreSQL magas memóriahasználatának hibaelhárítása.
• Lassan futó lekérdezések hibaelhárítása és azonosítása az Azure Database for PostgreSQL-ben.
• Kiszolgálóparaméterek az Azure Database for PostgreSQL-ben.
• Autovacuum-hangolás az Azure Database for PostgreSQL-ben.