Udostępnij za pośrednictwem

Dostrajanie indeksu w usłudze Azure Database for PostgreSQL — serwer elastyczny

  • Artykuł

DOTYCZY: Azure Database for PostgreSQL — serwer elastyczny

Dostrajanie indeksów to funkcja na serwerze elastycznym usługi Azure Database for PostgreSQL, która automatycznie poprawia wydajność obciążenia, analizując śledzone zapytania i dostarczając rekomendacje dotyczące indeksów.

Jest to wbudowana oferta na serwerze elastycznym usługi Azure Database for PostgreSQL, która opiera się na wydajności monitora z funkcjami magazynu zapytań. Dostrajanie indeksu analizuje obciążenie śledzone przez magazyn zapytań i generuje zalecenia dotyczące indeksu w celu zwiększenia wydajności analizowanego obciążenia lub porzucania zduplikowanych lub nieużywanych indeksów.

Ogólny opis algorytmu dostrajania indeksu

Gdy parametr serwera jest skonfigurowany do reportparametru index_tuning.mode , sesje dostrajania są automatycznie uruchamiane z częstotliwością skonfigurowaną w parametrze index_tuning.analysis_intervalserwera , wyrażonym w minutach.

W pierwszej fazie sesja dostrajania wyszukuje listę baz danych, w których uważa, że wszelkie zalecenia, które może wygenerować, mogą znacząco wpłynąć na ogólną wydajność systemu. W tym celu zbiera wszystkie zapytania zarejestrowane przez magazyn zapytań, których wykonania zostały przechwycone w interwale wyszukiwania, na której koncentruje się ta sesja dostrajania. Interwał wyszukiwania obejmuje obecnie ostatnie index_tuning.analysis_interval minuty od czasu rozpoczęcia sesji dostrajania.

W przypadku wszystkich zapytań inicjowanych przez użytkownika z wykonaniami zarejestrowanymi w magazynie zapytań i których statystyki środowiska uruchomieniowego nie są resetowane, system klasyfikuje je na podstawie zagregowanego łącznego czasu wykonywania. Skupia uwagę na najbardziej widocznych zapytaniach na podstawie ich czasu trwania.

Następujące zapytania są wykluczone z tej listy:

  • Zapytania inicjowane przez system. (czyli zapytania wykonywane przez azuresu rolę)
  • Zapytania wykonywane w kontekście dowolnej systemowej bazy danych (azure_sys, template0, template1i azure_maintenance).

Algorytm iteruje docelowe bazy danych, wyszukując możliwe indeksy, które mogą poprawić wydajność analizowanych obciążeń. Wyszukuje również indeksy, które można wyeliminować, ponieważ są one identyfikowane jako duplikaty lub nie są używane przez konfigurowalny okres czasu.

ZALECENIA DOTYCZĄCE TWORZENIA INDEKSU

Dla każdej bazy danych zidentyfikowanej jako kandydat do analizy pod kątem tworzenia zaleceń dotyczących indeksu wszystkie zapytania SELECT, UPDATE, INSERT i DELETE wykonywane w interwale wyszukiwania i w kontekście tej konkretnej bazy danych są uwzględniane.

Wynikowy zestaw zapytań jest klasyfikowany na podstawie zagregowanego łącznego czasu wykonywania, a górna część index_tuning.max_queries_per_database jest analizowana pod kątem możliwych zaleceń dotyczących indeksu.

Potencjalne zalecenia mają na celu poprawę wydajności tych typów zapytań:

  • Zapytania z filtrami (czyli zapytania z predykatami w klauzuli WHERE),
  • Zapytania łączące wiele relacji, niezależnie od tego, czy są zgodne ze składnią, w której sprzężenia są wyrażane za pomocą klauzuli JOIN, czy predykaty sprzężenia są wyrażane w klauzuli WHERE.
  • Zapytania łączące filtry i predykaty sprzężenia.
  • Zapytania z grupowaniem (zapytania z klauzulą GROUP BY).
  • Zapytania łączące filtry i grupowanie.
  • Zapytania z sortowaniem (zapytania z klauzulą ORDER BY).
  • Zapytania łączące filtry i sortowanie.

Uwaga

Jedynym typem indeksów, które obecnie zaleca system, są te typu B-Tree.

Jeśli zapytanie odwołuje się do jednej kolumny tabeli i ta tabela nie zawiera żadnych statystyk, pomija całe zapytanie i nie generuje żadnych zaleceń dotyczących indeksu w celu ulepszenia jego wykonywania.

Analiza wymagana do zbierania statystyk może być wyzwalana ręcznie przy użyciu polecenia ANALYZE lub automatycznie przez demona automatycznego czyszczenia.

index_tuning.max_indexes_per_table Określa liczbę indeksów, które mogą być zalecane, z wyłączeniem wszystkich indeksów, które mogą już istnieć w tabeli dla każdej pojedynczej tabeli, do których odwołuje się dowolna liczba zapytań podczas sesji dostrajania.

index_tuning.max_index_count określa liczbę zaleceń dotyczących indeksów generowanych dla wszystkich tabel każdej bazy danych analizowanej podczas sesji dostrajania.

Aby zalecenie dotyczące indeksu było emitowane, aparat dostrajania musi oszacować, że poprawia co najmniej jedno zapytanie w przeanalizowanym obciążeniu przez współczynnik określony za pomocą index_tuning.min_improvement_factorpolecenia .

Podobnie wszystkie zalecenia dotyczące indeksu są sprawdzane, aby upewnić się, że nie wprowadzają regresji w żadnym pojedynczym zapytaniu w tym obciążeniu czynnika określonego za pomocą index_tuning.max_regression_factorpolecenia .

Uwaga

index_tuning.min_improvement_factor oba index_tuning.max_regression_factor odnoszą się do kosztów planów zapytań, a nie do czasu ich trwania lub zasobów, które zużywają podczas wykonywania.

Wszystkie parametry wymienione w poprzednich akapitach, ich wartości domyślne i prawidłowe zakresy są opisane w opcjach konfiguracji.

Skrypt utworzony wraz z zaleceniem utworzenia indeksu jest następujący wzorzec:

create index concurrently {indexName} on {schema}.{table}({column_name}[, ...])

Zawiera klauzulę concurrently. Aby uzyskać więcej informacji na temat skutków tej klauzuli, odwiedź oficjalną dokumentację bazy danych PostgreSQL dotyczącą tworzenia indeksu.

Dostrajanie indeksu automatycznie generuje nazwy zalecanych indeksów, które zazwyczaj składają się z nazw różnych kolumn kluczy rozdzielonych znakami "_" (podkreślenia) i ze stałym sufiksem "_idx". Jeśli łączna długość nazwy przekracza limity bazy danych PostgreSQL lub jeśli starczy się z istniejącymi relacjami, nazwa jest nieco inna. Można go obcinać i można dołączyć liczbę na końcu nazwy.

Obliczanie wpływu zalecenia CREATE INDEX

Wpływ tworzenia rekomendacji indeksu jest mierzony dla wartości IndexSize (megabajtów) i QueryCostImprovement (wartość procentowa).

IndexSize to pojedyncza wartość, która reprezentuje szacowany rozmiar indeksu, biorąc pod uwagę bieżącą kardynalność tabeli i rozmiar kolumn, do których odwołuje się zalecany indeks.

QueryCostImprovement składa się z tablicy wartości, gdzie każdy element reprezentuje poprawę kosztów planu dla każdego zapytania, którego koszt planu jest szacowany w celu poprawy, jeśli ten indeks istniał. Każdy element pokazuje identyfikator zapytania (zapytanie) i wartość procentową, według której koszt planu poprawiłby się, gdyby zalecenie zostało zaimplementowane (wymiarowe).

Rekomendacje DOTYCZĄCE DROP INDEX i REINDEX

Dla każdej bazy danych, dla której jest określana funkcja dostrajania indeksu, powinna zainicjować nową sesję, a po zakończeniu fazy zaleceń CREATE INDEX zaleca się usunięcie lub ponowne indeksowanie istniejących indeksów na podstawie następujących kryteriów:

  • Upuść, jeśli jest traktowany jako duplikat innych.
  • Upuść, jeśli nie jest używany przez konfigurowalny czas.
  • Indeksy ponownego indeksowania, które są oznaczone jako nieprawidłowe.

Usuwanie zduplikowanych indeksów

Zalecenia dotyczące usuwania zduplikowanych indeksów: najpierw zidentyfikuj, które indeksy mają duplikaty.

Duplikaty są klasyfikowane na podstawie różnych funkcji, które można przypisać indeksowi i na podstawie szacowanych rozmiarów.

Na koniec zaleca upuszczanie wszystkich duplikatów o niższym rankingu niż lider odniesienia i opisuje, dlaczego każdy duplikat został sklasyfikowany w taki sposób, w jaki był.

Aby dwa indeksy były uznawane za zduplikowane, muszą:

  • Należy utworzyć w tej samej tabeli.
  • Być indeksem dokładnie tego samego typu.
  • Dopasuj kolumny kluczy i, w przypadku kluczy indeksu wielokolumna, dopasuj kolejność, do której się odwołujesz.
  • Dopasuj drzewo wyrażeń swojego predykatu. Dotyczy tylko indeksów częściowych.
  • Dopasuj drzewo wyrażeń wszystkich odwołań do kolumn innych niżimple. Dotyczy tylko indeksów utworzonych w wyrażeniach.
  • Dopasowuje sortowanie każdej kolumny, do których odwołuje się klucz.

Usuwanie nieużywanych indeksów

Zalecenia dotyczące usuwania nieużywanych indeksów identyfikują te indeksy, które:

  • Nie są używane przez co najmniej index_tuning.unused_min_period dni.
  • Pokaż minimalną (średnią dzienną) liczbę index_tuning.unused_dml_per_table list DML w tabeli, w której jest tworzony indeks.
  • Pokaż minimalną (średnią dzienną) liczbę odczytów index_tuning.unused_reads_per_table w tabeli, w której jest tworzony indeks.

Ponowne indeksowanie nieprawidłowych indeksów

Zalecenia dotyczące ponownego indeksowania istniejących indeksów identyfikują te indeksy, które są oznaczone jako nieprawidłowe. Aby dowiedzieć się więcej o tym, dlaczego i kiedy indeksy są oznaczone jako nieprawidłowe, zapoznaj się z oficjalną dokumentacją REINDEX w usłudze PostgreSQL.

Obliczanie wpływu zalecenia DROP INDEX

Wpływ zalecenia dotyczącego indeksu spadku jest mierzony na dwa wymiary: Korzyść (wartość procentowa) i IndexSize (megabajty).

Korzyść jest pojedynczą wartością, którą można na razie zignorować.

IndexSize to pojedyncza wartość, która reprezentuje szacowany rozmiar indeksu, biorąc pod uwagę bieżącą kardynalność tabeli i rozmiar kolumn, do których odwołuje się zalecany indeks.

Konfigurowanie dostrajania indeksu

Dostrajanie indeksu można włączyć, wyłączyć i skonfigurować za pomocą zestawu parametrów sterujących jego zachowaniem, takich jak częstotliwość uruchamiania sesji dostrajania.

Zapoznaj się ze wszystkimi szczegółowymi informacjami na temat prawidłowej konfiguracji funkcji dostrajania indeksów w sposób włączania, wyłączania i konfigurowania dostrajania indeksu.

Informacje generowane przez dostrajanie indeksu

Sposób odczytywania, interpretowania i używania zaleceń generowanych przez dostrajanie indeksów opisuje szczegółowo sposób uzyskiwania i używania zaleceń generowanych przez dostrajanie indeksów.

Ograniczenia i możliwość obsługi

Poniżej znajduje się lista ograniczeń i zakresu obsługi dostrajania indeksu.

Zależność od rozszerzenia hipopg

W przypadku dostrajania indeksu w celu utworzenia zaleceń dotyczących indeksu CREATE używa rozszerzenia hypopg .

Jeśli rozszerzenie już istnieje po rozpoczęciu sesji dostrajania, jest ono używane w schemacie, w którym został utworzony. A po zakończeniu sesji dostrajania rozszerzenie nie zostanie usunięte. Wyjątkiem jest to, czy rozszerzenie zostało utworzone w schemacie pg_catalog . Jeśli tak jest, dostrajanie indeksu spadnie rozszerzenie.

Jeśli rozszerzenie nie istnieje w pierwszej kolejności lub porzuciliśmy je, ponieważ zostało utworzone w schemacie, dostrajanie indeksu utworzy go w pg_catalog schemacie o nazwie ms_temp_recommendations709253 i po pomyślnym zakończeniu sesji dostrajania porzuca rozszerzenie i usuwa schemat.

Użytkownicy, którzy są członkami azure_pg_admin roli, mogą w dowolnym momencie porzucić rozszerzenie hipopg, nawet jeśli została utworzona przez funkcję dostrajania indeksu. Jednak usunięcie go podczas uruchamiania sesji dostrajania indeksu może spowodować niepowodzenie tej sesji i nie generować żadnych zaleceń.

Obsługiwane warstwy obliczeniowe i jednostki SKU

Dostrajanie indeksu jest obsługiwane we wszystkich aktualnie dostępnych warstwach: Z możliwością serii, ogólnego przeznaczenia i Zoptymalizowane pod kątem pamięci oraz na każdej aktualnie obsługiwanej jednostce SKU obliczeniowej z co najmniej 4 rdzeniami wirtualnymi.

Obsługiwane wersje bazy danych PostgreSQL

Dostrajanie indeksu jest obsługiwane na serwerze elastycznym usługi Azure Database for PostgreSQL w wersji 12 lub nowszej .

Korzystanie z search_path

Dostrajanie indeksu zużywa wartość utrwalone w kolumnie search_path query_store.qs_view, dzięki czemu po przeanalizowaniu każdej kwerendy ta sama wartość search_path , która została ustawiona podczas wykonywania zapytania, jest tym, do którego ustawiono analizę możliwych zaleceń.

Zapytania sparametryzowane

Zapytania sparametryzowane utworzone przy użyciu protokołu PREPARE lub korzystające z rozszerzonego protokołu zapytania są analizowane i analizowane w celu wygenerowania zaleceń dotyczących indeksu.

W przypadku analizy zapytań sparametryzowanych dostrajanie indeksu wymaga ustawienia capture_first_sample pg_qs.parameters_capture_mode, gdy magazyn zapytań przechwytuje wykonywanie zapytania. Wymaga również, aby parametry zostały prawidłowo przechwycone przez magazyn zapytań podczas wykonywania zapytania. Innymi słowy, w przypadku analizowanego zapytania query_store.qs_view musi mieć ustawioną kolumnę parameters_capture_status na succeededwartość .

Tryb tylko do odczytu i repliki do odczytu

Ponieważ dostrajanie indeksów opiera się na magazynie zapytań, który nie jest obsługiwany w replikach do odczytu lub gdy wystąpienie jest w trybie tylko do odczytu, nie obsługujemy go w replikach do odczytu ani w wystąpieniach, które są w trybie tylko do odczytu.

Wszystkie zalecenia widoczne w repliki do odczytu zostały utworzone w repliki podstawowej po przeanalizowaniu wyłącznie obciążenia wykonywanego na repliki podstawowej.

Skalowanie w dół zasobów obliczeniowych

Jeśli na serwerze włączono dostrajanie indeksu, a obliczenia tego serwera są skalowane w dół do mniejszej niż minimalna liczba wymaganych rdzeni wirtualnych, funkcja pozostaje włączona. Ponieważ funkcja nie jest obsługiwana na serwerach z mniej niż 4 rdzeniami wirtualnymi, nie zostanie uruchomiona w celu przeanalizowania obciążenia i wygenerowania zaleceń, nawet jeśli index_tuning.mode została ustawiona na ON czas skalowania obliczeń w dół. Chociaż serwer nie spełnia minimalnych wymagań, wszystkie index_tuning.* parametry serwera są niedostępne. Za każdym razem, gdy serwer jest skalowany w górę do zasobów obliczeniowych spełniających minimalne wymagania, index_tuning.mode jest skonfigurowany z dowolną ustawioną wartością przed skalowaniem w dół do zasobów obliczeniowych, które nie spełniają wymagań.

Wysoka dostępność i repliki do odczytu

Jeśli na serwerze skonfigurowano wysoką dostępność lub repliki do odczytu, należy pamiętać o konsekwencjach związanych z tworzeniem obciążeń intensywnie korzystających z zapisu na serwerze podstawowym podczas implementowania zalecanych indeksów. Należy zachować szczególną ostrożność podczas tworzenia indeksów, których rozmiar jest szacowany jako duży.

Powiązana zawartość