Cześć
Nazywam się Wania i jestem programistą Java. Tak się składa, że dużo pracuję z PostgreSQL - konfiguruję bazę danych, optymalizuję strukturę, wydajność, a w weekendy gram trochę w DBA.
Ostatnio uporządkowałem kilka baz danych w naszych mikroserwisach i napisałem bibliotekę Java , co ułatwia tę pracę, oszczędza mi czas i pozwala uniknąć typowych błędów popełnianych przez programistów. Właśnie o tej bibliotece będziemy dzisiaj mówić.
Odpowiedzialność
Główna wersja PostgreSQL, z którą pracuję, to 10. Wszystkie zapytania SQL, których używam, są również testowane w wersji 11. Minimalna obsługiwana wersja to 9.6.
prehistoria
Wszystko zaczęło się prawie rok temu od dziwnej dla mnie sytuacji: konkurencyjne utworzenie indeksu niespodziewanie zakończyło się błędem. Sam indeks jak zwykle pozostał w bazie w nieprawidłowym stanie. Analiza logów wykazała niedobór . I zaczynamy... Szperając głębiej, odkryłem całą masę problemów w konfiguracji bazy danych i zakasując rękawy, z błyskiem w oczach zacząłem je naprawiać.
Problem pierwszy – konfiguracja domyślna
Pewnie każdemu już dość metafory o Postgresie, który można uruchomić na ekspresie do kawy, jednak… domyślna konfiguracja naprawdę rodzi wiele pytań. Przynajmniej warto zwrócić na to uwagę konserwacja_praca_mem, limit_pliku tymczasowego, limit_czasu instrukcji и limit czasu_blokady.
W naszym przypadku konserwacja_praca_mem było domyślne 64 MB i limit_pliku tymczasowego coś w okolicach 2 GB - po prostu nie mieliśmy wystarczającej ilości pamięci, aby utworzyć indeks na dużej tabeli.
Dlatego w pg-index-zdrowie Zebrałem serię , moim zdaniem parametry, które należy skonfigurować dla każdej bazy danych.
Problem drugi – zduplikowane indeksy
Nasze bazy danych znajdują się na dyskach SSD i z nich korzystamy HA-konfiguracja z wieloma centrami danych, hostem głównym i n-liczba replik. Miejsce na dysku jest dla nas bardzo cennym zasobem; jest to nie mniej ważne niż wydajność i zużycie procesora. Dlatego z jednej strony potrzebujemy indeksów do szybkiego odczytu, a z drugiej strony nie chcemy widzieć w bazie danych zbędnych indeksów, bo zajmują miejsce i spowalniają aktualizację danych.
A teraz, po przywróceniu wszystkiego i zobaczyłem wystarczająco dużo , postanowiłem zorganizować „wielką” czystkę. Okazało się, że programiści nie lubią czytać dokumentacji baz danych. Bardzo im się to nie podoba. Z tego powodu powstają dwa typowe błędy - ręcznie utworzony indeks na kluczu podstawowym i podobny „ręczny” indeks na unikalnej kolumnie. Faktem jest, że nie są potrzebne – Postgres zrobi wszystko sam. Takie indeksy można bezpiecznie usunąć i w tym celu pojawiła się diagnostyka .
Problem trzeci – przecinające się indeksy
Większość początkujących programistów tworzy indeksy w jednej kolumnie. Stopniowo, po dokładnym wypróbowaniu tego biznesu, ludzie zaczynają optymalizować swoje zapytania i dodawać bardziej złożone indeksy zawierające kilka kolumn. Tak wyglądają indeksy w kolumnach A, A + B, A+B+C i tak dalej. Pierwsze dwa z tych indeksów można bezpiecznie wyrzucić, ponieważ są one przedrostkami trzeciego. Oszczędza to również dużo miejsca na dysku i istnieje do tego diagnostyka .
Problem czwarty – klucze obce bez indeksów
Postgres umożliwia tworzenie ograniczeń klucza obcego bez określania indeksu zapasowego. W wielu sytuacjach nie stanowi to problemu, a nawet może się nie objawiać... Na razie...
U nas było tak samo: po prostu w pewnym momencie zadanie, przebiegające zgodnie z harmonogramem i czyszczące bazę zleceń testowych, zaczęło być nam „dodawane” przez głównego hosta. Procesor i IO poszły na marne, żądania spowolniły i przekroczyły limit czasu, usługa wynosiła pięćset. Szybka analiza pokazało, że zapytania takie jak:
delete from <table> where id in (…)W tym przypadku oczywiście w tabeli docelowej znajdował się indeks według identyfikatora i bardzo niewiele rekordów zostało usuniętych zgodnie z warunkiem. Wydawało się, że wszystko powinno działać, ale niestety tak nie było.
Na ratunek przybył wspaniały wyjaśnić, przeanalizować i powiedział, że oprócz usuwania rekordów w tabeli docelowej odbywa się również sprawdzanie integralności referencyjnej i w jednej z powiązanych tabel ta kontrola kończy się niepowodzeniem skanowanie sekwencyjne z powodu braku odpowiedniego indeksu. Tak narodziła się diagnostyka .
Problem piąty – wartość null w indeksach
Domyślnie Postgres zawiera wartości null w indeksach btree, ale zwykle nie są one tam potrzebne. Dlatego pilnie staram się wyrzucić te wartości zerowe (diagnostics ), tworząc częściowe indeksy dla kolumn dopuszczających wartość null według typu where <A> is not null. W ten sposób udało mi się zmniejszyć rozmiar jednego z naszych indeksów z 1877 MB do 16 KB. Natomiast w jednym z serwisów wielkość bazy danych zmniejszyła się łącznie o 16% (w liczbach bezwzględnych o 4.3 GB) w związku z wykluczeniem z indeksów wartości null. Ogromna oszczędność miejsca na dysku przy bardzo prostych modyfikacjach. 🙂
Problem szósty – brak kluczy podstawowych
Ze względu na charakter mechanizmu taka sytuacja jest możliwa gdy rozmiar tabeli szybko rośnie z powodu dużej liczby martwych rekordów. Naiwnie wierzyłem, że nam to nie grozi i że nie przydarzy się to naszej bazie, bo my, wow!!!, jesteśmy normalnymi programistami... Jaki ja byłem głupi i naiwny...
Pewnego dnia jedna cudowna migracja pobrała i zaktualizowała wszystkie rekordy w dużej i aktywnie używanej tabeli. Niespodziewanie dostaliśmy +100 GB do rozmiaru stołu. Szkoda, ale to nie koniec naszych nieszczęść. Po 15 godzinach, gdy automatyczna próżnia na tym stole zakończyła się, stało się jasne, że fizyczna lokalizacja nie powróci. Nie mogliśmy zatrzymać usługi i sprawić, że PRÓŻNIA będzie PEŁNA, więc postanowiliśmy skorzystać . A potem okazało się, że pg_repack nie wie, jak przetwarzać tabele bez klucza podstawowego lub innego ograniczenia dotyczącego unikalności, a nasza tabela nie miała klucza podstawowego. Tak narodziła się diagnostyka .
W wersji bibliotecznej 0.1.5 Dodano możliwość zbierania danych z rozdętych tabel i indeksów oraz szybkiego reagowania na nie.
Zadania siódme i ósme – indeksy niewystarczające i indeksy niewykorzystane
Następujące dwie diagnostyki to: и – w ostatecznej formie pojawiły się stosunkowo niedawno. Rzecz w tym, że nie można ich po prostu wziąć i dodać.
Jak już pisałem, używamy konfiguracji z kilkoma replikami, a obciążenie odczytem na różnych hostach jest zasadniczo różne. W rezultacie okazuje się, że część tabel i indeksów na niektórych hostach praktycznie nie jest wykorzystywana, a do analizy trzeba zebrać statystyki ze wszystkich hostów w klastrze. Jest to również konieczne na każdym hoście w klastrze; nie można tego zrobić tylko na hoście głównym.
Takie podejście pozwoliło nam zaoszczędzić kilkadziesiąt gigabajtów poprzez usunięcie nigdy nie używanych indeksów, a także dodanie brakujących indeksów do rzadko używanych tabel.
Na zakończenie
Oczywiście dla prawie całej diagnostyki, którą możesz skonfigurować . W ten sposób możesz szybko wdrożyć w swojej aplikacji kontrole, zapobiegające pojawianiu się nowych błędów, a następnie stopniowo naprawiać stare.
Część diagnostyki można przeprowadzić w testach funkcjonalnych bezpośrednio po przeprowadzeniu migracji baz danych. Jest to prawdopodobnie jedna z najpotężniejszych funkcji mojej biblioteki. Przykład użycia można znaleźć w .
Sprawdzanie nieużywanych lub brakujących indeksów, a także wzdęć ma sens tylko w prawdziwej bazie danych. Zebrane wartości można zapisać w lub przesłane do systemu monitoringu.
Naprawdę mam taką nadzieję pg-index-zdrowie będzie przydatny i pożądany. Możesz także przyczynić się do rozwoju biblioteki zgłaszając znalezione problemy i proponując nową diagnostykę.
Źródło: www.habr.com