Udostępnij za pośrednictwem

Najlepsze rozwiązania dotyczące zbiorczego przekazywania danych do usługi Azure Database for PostgreSQL — serwer elastyczny

  • Artykuł

DOTYCZY: Azure Database for PostgreSQL — serwer elastyczny

W tym artykule omówiono różne metody zbiorczego ładowania danych na serwerze elastycznym usługi Azure Database for PostgreSQL wraz z najlepszymi rozwiązaniami dotyczącymi 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 jednokrotne INSERT polecenie.
  • Wsaduj do 100 do 1000 wierszy na zatwierdzenie. Blok transakcji umożliwia zawijanie wielu rekordów na zatwierdzenie.
  • 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ą. Wielowątkowa obsługa COPY polecenia jest optymalna do zbiorczego ładowania danych.

Kroki przekazywania danych zbiorczych

Poniżej przedstawiono kroki zbiorczego przekazywania danych na serwer elastyczny 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 przekazaniu 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_role replica 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 ich w początkowych obciążeniach danych należy wziąć pod uwagę zalety i wady nieznakowanych tabel.

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

Wady używania nielogowanych tabel to:

  • Nie są one bezpieczne dla awarii. Niezlogowana tabela jest automatycznie obcinana po awarii lub nieczystym zamknięciu.
  • Nie można replikować danych z nieznakowanych tabel do serwerów rezerwowych.

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 serwera

  • auto vacuum': It's best to turn off automatyczne czyszczenie danych podczas początkowego ładowania danych. Po zakończeniu początkowego ładowania zalecamy uruchomienie instrukcji ręcznej VACUUM ANALYZE we wszystkich tabelach w bazie danych, a następnie włączenie polecenia auto vacuum.

Uwaga

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 checkpoint_timeout elastycznego usługi Azure Database for PostgreSQL wartość można zwiększyć do maksymalnie 24 godzin od domyślnego ustawienia wynoszącego 5 minut. 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 wiązać się z dodatkowymi kosztami kompresji procesora CPU podczas rejestrowania i dekompresowania dziennika zapisu podczas odtwarzania wal.

Zalecenia

Przed rozpoczęciem początkowego ładowania danych w wystąpieniu serwera elastycznego usługi Azure Database for PostgreSQL zalecamy:

  • 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.
  • Utwórz minimalne rejestrowanie lub wyłącz je razem podczas początkowego ładowania danych (na przykład wyłącz narzędzie pgaudit, pg_stat_statements, magazyn zapytań).

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 różnic 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.
  • Automatyczne czyszczenie zostanie wyzwolone tylko na partycjach, które zostały zmienione lub dodane podczas obciążeń przyrostowych, co ułatwia utrzymywanie statystyk w 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 są ładowane przyrostowe. Usługa PostgreSQL używa procesu demona automatycznego czyszczenia, aby wyczyścić martwe krotki i przeanalizować tabele w celu zachowania aktualizacji statystyk. Aby uzyskać więcej informacji, zobacz Automatyczne monitorowanie i dostrajanie czyszczenia.

Tworzenie indeksów w ograniczeniach 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 można wyświetlać tabele nadrzędne i podrzędne łączące się z kolumnami kluczy.

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: według magazynu zapytań i zapytania użycia indeksu.

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ć szczegółowe wskazówki, zobacz Magazyn zapytań.

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_readi tuples_fetched wskazują indeks usage.number_of_scans wartość kolumny zero punktów jako indeks, który nie jest używany.

Dostrajanie parametrów serwera

Uwaga

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ć na maksymalnie 2 GB w wystąpieniu serwera elastycznego usługi Azure Database for PostgreSQL. maintenance_work_mem ułatwia przyspieszenie tworzenia indeksu i dodawania kluczy obcych.

  • checkpoint_timeout: W wystąpieniu checkpoint_timeout serwera elastycznego usługi Azure Database for PostgreSQL wartość można zwiększyć do 10 lub 15 minut od domyślnego ustawienia 5 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. Wykonaj bieżącą nazwę 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 wiązać się z dodatkowym kosztem procesora CPU podczas kompresowania podczas rejestrowania wal i dekompresowania podczas odtwarzania WAL.

Powiązana zawartość