Najlepsze rozwiązania dotyczące zbiorczego przekazywania danych na serwer elastyczny Azure Database for PostgreSQL

W tym artykule omówiono różne metody zbiorczego ładowania danych do wystąpienia elastycznego serwera usługi Azure Database for PostgreSQL, a także najlepsze rozwiązania dotyczące zarówno początkowego ładowania danych w pustych bazach danych, jak i przyrostowych obciążeń danych.

Metody ładowania

Następujące metody ładowania danych są uporządkowane w kolejności od najbardziej czasochłonnych do najmniej czasochłonnych:

• Uruchom polecenie dla pojedynczego rekordu INSERT.
• Grupuj po 100–1000 wierszy w jednym zatwierdzeniu. Można użyć bloku transakcji, aby objąć wiele rekordów w ramach jednego zatwierdzenia.
• Uruchom polecenie INSERT z wieloma wartościami wierszy.
• Uruchom polecenie COPY.

Preferowaną metodą ładowania danych do bazy danych jest COPY polecenie . COPY Jeśli polecenie nie jest niemożliwe, partia INSERT jest następną najlepszą metodą. Użycie polecenia COPY z wielowątkowością jest optymalne do masowego ładowania danych.

Kroki przekazywania danych zbiorczych

Poniżej przedstawiono kroki zbiorczego przekazywania danych do elastycznego serwera usługi Azure Database for PostgreSQL.

Krok 1. Przygotowanie danych

Upewnij się, że dane są czyste i prawidłowo sformatowane dla bazy danych.

Krok 2. Wybieranie metody ładowania

Wybierz odpowiednią metodę ładowania na podstawie rozmiaru i złożoności danych.

Krok 3. Wykonanie metody ładowania

Uruchom wybraną metodę ładowania, aby przekazać dane do bazy danych.

Krok 4. Weryfikowanie danych

Po przesłaniu sprawdź, czy dane zostały poprawnie załadowane do bazy danych.

Najlepsze rozwiązania dotyczące początkowego ładowania danych

Poniżej przedstawiono najlepsze rozwiązania dotyczące początkowego ładowania danych.

Usuwanie indeksów

Przed rozpoczęciem początkowego ładowania danych zalecamy usunięcie wszystkich indeksów w tabelach. Tworzenie indeksów po załadowaniu danych jest zawsze bardziej wydajne.

Usuwanie ograniczeń

Główne ograniczenia upuszczania zostały opisane tutaj:

• Unikatowe ograniczenia klucza

Aby osiągnąć silną wydajność, zalecamy usunięcie unikatowych ograniczeń klucza przed początkowym ładowaniem danych i ponownym utworzeniem ich po zakończeniu ładowania danych. Jednak usunięcie unikatowych ograniczeń klucza anuluje zabezpieczenia przed zduplikowanymi danymi.

• Ograniczenia klucza obcego

Zalecamy usunięcie ograniczeń klucza obcego przed początkowym ładowaniem danych i ponownym utworzeniem ich po zakończeniu ładowania danych.

Zmiana parametru na session_replication_rolereplica powoduje również wyłączenie wszystkich kontroli klucza obcego. Jeśli jednak zmiana nie zostanie prawidłowo użyta, może pozostawić dane niespójne.

Nielogowane tabele

Przed użyciem tabel nierejestrowanych do początkowego ładowania danych należy rozważyć ich zalety i wady.

Korzystanie z nielogowanych tabel przyspiesza ładowanie danych. Dane zapisywane w tabelach nielogowanych nie są zapisywane w dzienniku zapisu wyprzedzającego.

Wady używania nielogowanych tabel to:

• Nie są odporne na awarie. Tabela bez rejestrowania operacji jest automatycznie opróżniana po awarii lub nieprawidłowym zamknięciu.
• Danych z tabel bez logowania nie można replikować do serwerów zapasowych.

Aby utworzyć nieoznakowaną tabelę lub zmienić istniejącą tabelę na nieoznakowaną, użyj następujących opcji:

• Utwórz nową nieznakowaną tabelę przy użyciu następującej składni:

  CREATE UNLOGGED TABLE <tablename>;
  

• Przekonwertuj istniejącą zarejestrowaną tabelę na nieznakowaną tabelę przy użyciu następującej składni:

  ALTER TABLE <tablename> SET UNLOGGED;
  

Dostrajanie parametrów

• auto vacuum': It's best to turn off automatyczne czyszczenie danych podczas początkowego ładowania danych. Po zakończeniu początkowego ładowania zalecamy ręczne wykonanie VACUUM ANALYZE dla wszystkich tabel w bazie danych, a następnie włączenie auto vacuum.

Uwaga / Notatka

Postępuj zgodnie z zaleceniami w tym miejscu tylko wtedy, gdy jest wystarczająca ilość pamięci i miejsca na dysku.

• maintenance_work_mem: Można ustawić maksymalnie 2 gigabajty (GB) w wystąpieniu serwera elastycznego usługi Azure Database for PostgreSQL. maintenance_work_mem ułatwia przyspieszenie automatycznego czyszczenia, indeksowania i tworzenia kluczy obcych.

• checkpoint_timeout: W wystąpieniu serwera elastycznego usługi Azure Database for PostgreSQL wartość parametru checkpoint_timeout można zwiększyć z domyślnego ustawienia 5 minut do maksymalnie 24 godzin. Zalecamy zwiększenie wartości do 1 godziny przed początkowym załadowaniem danych do wystąpienia serwera elastycznego usługi Azure Database for PostgreSQL.

• checkpoint_completion_target: Zalecamy wartość 0,9.

• max_wal_size: Można ustawić maksymalną dozwoloną wartość w wystąpieniu serwera elastycznego usługi Azure Database for PostgreSQL, czyli 64 GB podczas początkowego ładowania danych.

• wal_compression: Można to włączyć. Włączenie tego parametru może powodować dodatkowe obciążenie procesora związane z kompresją podczas zapisywania do dziennika wyprzedzającego zapis (WAL) oraz z dekompresją podczas odtwarzania WAL.

Rekomendacje

Przed rozpoczęciem początkowego ładowania danych do wystąpienia serwera elastycznego usługi Azure Database for PostgreSQL zalecamy wykonanie następujących czynności:

• Wyłącz wysoką dostępność na serwerze. Można ją włączyć po zakończeniu początkowego ładowania na serwerze podstawowym.
• Tworzenie replik do odczytu po zakończeniu początkowego ładowania danych.
• Ogranicz rejestrowanie do minimum lub całkowicie je wyłącz podczas wstępnego ładowania danych (na przykład wyłącz pgaudit, pg_stat_statements, Query Store).

Ponowne tworzenie indeksów i dodawanie ograniczeń

Zakładając, że indeksy i ograniczenia zostały usunięte przed początkowym obciążeniem, zalecamy użycie wysokich wartości w maintenance_work_mem (jak wspomniano wcześniej) w celu utworzenia indeksów i dodania ograniczeń. Ponadto, począwszy od bazy danych PostgreSQL w wersji 11, można zmodyfikować następujące parametry w celu szybszego równoległego tworzenia indeksu po początkowym załadowaniu danych:

• max_parallel_workers: ustawia maksymalną liczbę procesów roboczych, które system może obsługiwać dla zapytań równoległych.

• max_parallel_maintenance_workers: określa maksymalną liczbę procesów roboczych, które mogą być używane w programie CREATE INDEX.

Indeksy można również utworzyć, tworząc zalecane ustawienia na poziomie sesji. Oto przykład tego, jak to zrobić:

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

Najlepsze rozwiązania dotyczące ładowania danych przyrostowych

W tym miejscu opisano najlepsze rozwiązania dotyczące przyrostowych obciążeń danych.

Tabele partycji

Zawsze zalecamy partycjonowanie dużych tabel. Niektóre zalety partycjonowania, szczególnie podczas obciążeń przyrostowych, obejmują:

• Tworzenie nowych partycji na podstawie nowych przyrostów sprawia, że dodawanie nowych danych do tabeli jest wydajne.
• Obsługa tabel staje się łatwiejsza. Partycję można usunąć podczas przyrostowego ładowania danych, aby uniknąć czasochłonnych operacji usuwania w dużych tabelach.
• Mechanizm autovacuum byłby uruchamiany tylko dla partycji, które zostały zmienione lub dodane podczas ładowań przyrostowych, co ułatwia utrzymanie statystyk tabeli.

Utrzymywanie aktualnych statystyk tabeli

Monitorowanie i utrzymywanie statystyk tabeli jest ważne w przypadku wydajności zapytań w bazie danych. Obejmuje to również scenariusze, w których występuje ładowanie przyrostowe. PostgreSQL używa procesu demona autovacuum do usuwania martwych krotek i analizowania tabel, aby utrzymać aktualność statystyk. Aby uzyskać więcej informacji, zobacz Monitorowanie i dostrajanie autovacuum.

Utwórz indeksy dla ograniczeń klucza obcego

Tworzenie indeksów na kluczach obcych w tabelach podrzędnych może być korzystne w następujących scenariuszach:

• Aktualizacje lub usunięcia danych w tabeli nadrzędnej. Gdy dane są aktualizowane lub usuwane w tabeli nadrzędnej, wyszukiwania są wykonywane w tabeli podrzędnej. Możesz indeksować klucze obce w tabeli podrzędnej, aby szybciej wyszukiwać.
• Zapytania, w których widać tabele nadrzędne i podrzędne połączone za pomocą kolumn kluczowych.

Identyfikowanie nieużywanych indeksów

Zidentyfikuj nieużywane indeksy w bazie danych i upuść je. Indeksy są obciążeniem podczas ładowania danych. Mniejsza liczba indeksów w tabeli, tym większa wydajność podczas pozyskiwania danych.

Nieużywane indeksy można zidentyfikować na dwa sposoby: za pomocą Query Store i zapytania dotyczącego użycia indeksów.

Magazyn zapytań

Funkcja Magazynu zapytań ułatwia identyfikowanie indeksów, które można porzucić na podstawie wzorców użycia zapytań w bazie danych. Aby uzyskać instrukcje krok po kroku, zobacz Query Store.

Po włączeniu magazynu zapytań na serwerze możesz użyć następującego zapytania, aby zidentyfikować indeksy, które można usunąć, łącząc się z bazą danych azure_sys.

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

Użycie indeksu

Możesz również użyć następującego zapytania, aby zidentyfikować nieużywane indeksy:

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;

Kolumny number_of_scans, tuples_read i tuples_fetched wskazują użycie indeksu. Wartość zero w kolumnie usage.number_of_scans oznacza indeks, który nie jest używany.

Dostrajanie parametrów

Uwaga / Notatka

Postępuj zgodnie z zaleceniami w poniższych parametrach tylko wtedy, gdy jest wystarczająca ilość pamięci i miejsca na dysku.

• maintenance_work_mem: Ten parametr można ustawić maksymalnie do 2 GB w instancji serwera elastycznego usługi Azure Database for PostgreSQL. maintenance_work_mem ułatwia przyspieszenie tworzenia indeksu i dodawania kluczy obcych.

• checkpoint_timeout: W ramach wystąpienia serwera elastycznego usługi Azure Database for PostgreSQL wartość parametru checkpoint_timeout można zwiększyć z domyślnego ustawienia wynoszącego 5 minut do 10 lub 15 minut. Zwiększenie checkpoint_timeout do bardziej znaczącej wartości, takiej jak 15 minut, może zmniejszyć obciążenie we/wy, ale wadą jest to, że odzyskiwanie w przypadku awarii trwa dłużej. Zalecamy staranne rozważenie przed wprowadzeniem zmiany.

• checkpoint_completion_target: Zalecamy wartość 0,9.

• max_wal_size: Ta wartość zależy od jednostki SKU, magazynu i obciążenia. W poniższym przykładzie pokazano jeden ze sposobów uzyskania poprawnej wartości dla elementu max_wal_size.

W godzinach szczytu pracy dotrzesz do wartości, wykonując następujące czynności:

a. Pobierz bieżący numer sekwencji dziennika WAL (LSN), uruchamiając następujące zapytanie:

SELECT pg_current_wal_lsn ();

b. Poczekaj checkpoint_timeout na liczbę sekund. Pobierz bieżący numer LSN WAL, uruchamiając następujące zapytanie:

SELECT pg_current_wal_lsn ();

c. Użyj dwóch wyników, aby sprawdzić różnicę w 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: Można to włączyć. Włączenie tego parametru może powodować dodatkowe obciążenie procesora podczas kompresji przy zapisie do dziennika WAL oraz dekompresji podczas odtwarzania WAL.

Treści powiązane

• Rozwiązywanie problemów z wysokim użyciem procesora CPU w usłudze Azure Database for PostgreSQL.
• Rozwiązywanie problemów z wysokim wykorzystaniem pamięci w usłudze Azure Database for PostgreSQL.
• Jak rozwiązywać problemy i identyfikować wolne wykonywanie zapytań w usłudze Azure Database for PostgreSQL.
• Parametry w usłudze Azure Database for PostgreSQL.
• Konfiguracja automatycznego czyszczenia w usłudze Azure Database for PostgreSQL