Udostępnij za pośrednictwem

Dostosowywanie indeksu

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 funkcja w serwerze elastycznym usługi Azure Database for PostgreSQL, która opiera się na funkcjonalności monitorowania wydajności przy użyciu magazynu zapytań. Optymalizacja indeksów analizuje obciążenie śledzone przez magazyn zapytań i generuje zalecenia dotyczące indeksów w celu zwiększenia wydajności analizowanego obciążenia lub usunięcia zduplikowanych czy nieużywanych indeksów.

Ogólny opis algorytmu dostrajania indeksu

Gdy parametr serwera jest skonfigurowany do index_tuning.modeparametru report , 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, które kontrolują jego zachowanie.

Po włączeniu dostrajania indeksu wznawia się z częstotliwością skonfigurowaną w parametrze index_tuning.analysis_interval serwera (domyślnie 720 minut lub 12 godzin) i rozpoczyna analizowanie obciążenia rejestrowanego przez magazyn zapytań w tym okresie.

Zwróć uwagę, że jeśli zmienisz wartość parametru index_tuning.analysis_interval, będzie ona obserwowana tylko po zakończeniu następnego zaplanowanego wykonywania. Na przykład jeśli włączysz dostrajanie indeksu jeden dzień o godzinie 10:00, ponieważ wartość domyślna to index_tuning.analysis_interval 720 minut, pierwsze wykonanie zostanie zaplanowane do rozpoczęcia o godzinie 10:00 tego samego dnia. Wszelkie zmiany wprowadzone w wartości index_tuning.analysis_interval od 10:00 do 10:00 nie będą mieć wpływu na ten początkowy harmonogram. Tylko po zakończeniu zaplanowanego przebiegu odczytuje bieżącą wartość ustawioną dla index_tuning.analysis_interval i będzie planować następne wykonanie zgodnie z tej wartości.

Dostępne są następujące opcje konfigurowania parametrów dostrajania indeksu:

Parametr Opis Wartość domyślna Zakres Jednostek
index_tuning.analysis_interval Ustawia częstotliwość wyzwalania każdej sesji optymalizacji indeksu po ustawieniu index_tuning.mode na REPORTwartość . 720 60 - 10080 minuty
index_tuning.max_columns_per_index Maksymalna liczba kolumn, które mogą być częścią klucza indeksu dla dowolnego zalecanego indeksu. 2 1 - 10
index_tuning.max_index_count Maksymalna liczba indeksów zalecanych dla każdej bazy danych podczas jednej sesji optymalizacji. 10 1 - 25
index_tuning.max_indexes_per_table Maksymalna liczba indeksów, które mogą być zalecane dla każdej tabeli. 10 1 - 25
index_tuning.max_queries_per_database Liczba najwolniejszych zapytań na bazę danych, dla której indeksy mogą być zalecane. 25 5 - 100
index_tuning.max_regression_factor Akceptowalna regresja wprowadzona przez zalecany indeks na dowolnej z zapytań analizowanych podczas jednej sesji optymalizacji. 0.1 0.05 - 0.2 procent
index_tuning.max_total_size_factor Maksymalny całkowity rozmiar w procentach całkowitego miejsca na dysku, z którego mogą korzystać wszystkie zalecane indeksy dla dowolnej bazy danych. 0.1 0 - 1 procent
index_tuning.min_improvement_factor Poprawa kosztów zalecana przez indeks musi zapewnić co najmniej jedno z zapytań analizowanych podczas jednej sesji optymalizacji. 0.2 0 - 20 procent
index_tuning.mode Konfiguruje optymalizację indeksu jako wyłączoną (OFF) lub włączoną, aby emitować tylko zalecenie. Wymaga włączenia magazynu zapytań przez ustawienie pg_qs.query_capture_mode na TOP lub ALL. OFF OFF, REPORT
index_tuning.unused_dml_per_table Minimalna liczba codziennych średnich operacji DML wpływających na tabelę, więc ich nieużywane indeksy są uznawane za upuszczanie. 1000 0 - 9999999
index_tuning.unused_min_period Minimalna liczba dni, w których indeks nie został użyty, na podstawie statystyk systemowych, dlatego jest brany pod uwagę do upuszczania. 35 30 - 70
index_tuning.unused_reads_per_table Minimalna liczba codziennych operacji odczytu wpływających na tabelę, tak aby ich nieużywane indeksy były brane pod uwagę podczas upuszczania. 1000 0 - 9999999

Jeśli używasz poleceń interfejsu wiersza polecenia az postgres flexible-server index-tuning show-settings i az postgres flexible-server index-tuning set-settings do wyświetlania lub modyfikowania dowolnego z ustawień dostrajania indeksu, wartości akceptowane jako argumenty dla --name parametru to te wyświetlane w kolumnie Parametr poprzedniej tabeli, ale bez uwzględniania prefiksu index_tuning..

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.

Automatyczne usuwanie zaleceń

Rekomendacje są automatycznie usuwane 35 dni po ostatnim ich wyprodukowaniu. Aby ten mechanizm automatycznego usuwania działał, należy włączyć dostrajanie 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 wersji12 lub nowszej .

Korzystanie z search_path

Dostrajanie indeksu zużywa wartość utrwalone w kolumnie search_pathquery_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, 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 replice do odczytu zostały utworzone w replice podstawowej po przeanalizowaniu wyłącznie obciążenia wykonywanego na replice 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ż ta funkcja nie jest obsługiwana na serwerach mających mniej niż 4 rdzeni wirtualnych, nie zostaje uruchomiona w celu analizowania obciążenia i generowania zaleceń, nawet jeśli index_tuning.mode była ustawiona na ON w momencie zmniejszania zasobów obliczeniowych. 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ść