Udostępnij za pośrednictwem

Najlepsze rozwiązania dotyczące zbiorczego przekazywania danych w usłudze 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 użycie COPY polecenia . COPY Jeśli polecenie nie jest możliwe, użycie usługi Batch INSERT jest następną najlepszą metodą. Wielowątkowa obsługa COPY polecenia to optymalna metoda ładowania danych zbiorczo.

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. Zawsze wydajniejsze jest tworzenie indeksów po załadowaniu danych.

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 ponowne utworzenie 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 zał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. Należy jednak pamiętać, że wprowadzenie zmiany może pozostawić dane w stanie niespójnym, jeśli nie jest prawidłowo używane.

Nielogowane tabele

Przed użyciem ich w początkowych obciążeniach danych należy wziąć pod uwagę zalety i wady używania nieznakowanych tabel.

Korzystanie z nielogowanych tabel sprawia, że ładowanie danych jest szybsze. Dane zapisywane w tabelach nieznakowanych 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

  • autovacuum: Podczas początkowego ładowania danych najlepiej wyłączyć funkcję autovacuum. Po zakończeniu początkowego ładowania zalecamy uruchomienie ręcznego VACUUM ANALYZE polecenia we wszystkich tabelach w bazie danych, a następnie włączenie polecenia autovacuum.

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, indeksu 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 w wystąpieniu 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 włączyć. Włączenie tego parametru może spowodować poniesienie dodatkowych kosztów procesora CPU na kompresję podczas rejestrowania z wyprzedzeniem zapisu (WAL) i dekompresji podczas odtwarzania WAL.

Rekomendacje dotyczące serwera elastycznego usługi Azure Database for PostgreSQL

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 całkowicie 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) do tworzenia indeksów i dodawania 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

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 wydajne jest dodawanie nowych danych do tabeli.
  • 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 wyzwolenie byłoby wyzwalane 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. Aby szybciej wyszukiwać, można indeksować klucze obce w tabeli podrzędnej.
  • Zapytania, w których można zobaczyć łączenie tabel nadrzędnych i podrzędnych w kolumnach 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 większej wartości, takiej jak 15 minut, może zmniejszyć obciążenie we/wy, ale wadą jest to, że odzyskiwanie trwa dłużej, jeśli wystąpi awaria. 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 jedną ze sposobów uzyskania poprawnej wartości 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 na checkpoint_timeout 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 run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • wal_compression: można włączyć. Włączenie tego parametru może spowodować naliczenie dodatkowych kosztów procesora CPU na kompresję podczas rejestrowania WAL i dekompresji podczas odtwarzania WAL.

Następne kroki