Cześć.
Nazywam się Wania i jestem programistą Java. Tak się składa, że dużo pracuję z PostgreSQL – zajmuję się konfiguracją baz danych, optymalizacją struktury, wydajnością i trochę bawię się w DBA w weekendy.
Ostatnio uporządkowałem kilka baz danych w naszych mikroserwisach i napisałem bibliotekę java , która ułatwia tę pracę, oszczędza mój czas i pomaga uniknąć niektórych typowych błędów popełnianych przez programistów. To o tej bibliotece dzisiaj będzie mowa.
Zrzeczenie się odpowiedzialności
Główna wersja PostgreSQL, z którą pracuję, to 10. Wszystkie moje zapytania SQL zostały również sprawdzone na wersji 11. Minimalna obsługiwana wersja to 9.6.
Tło
Wszystko zaczęło się niemal rok temu od dziwnej sytuacji dla mnie: konkurencyjne tworzenie indeksu znikąd zakończyło się błędem. Sam indeks, jak to często bywa, pozostał w bazie w stanie nieprawidłowym. Analiza logów pokazała brak . I zaczęło się… Kopiąc głębiej, odkryłem cały szereg problemów w konfiguracji bazy danych i, podwijając rękawy, z błyskiem w oczach zabrałem się do ich naprawiania.
Problem pierwszy – domyślna konfiguracja
Prawdopodobnie metafora o Postgresie, który można uruchomić na ekspresie do kawy, już wszystkich zdążyła zmęczyć, ale… konfiguracja domyślna rzeczywiście budzi szereg pytań. Co najmniej warto zwrócić uwagę na maintenance_work_mem, temp_file_limit, statement_timeout i lock_timeout.
W naszym przypadku maintenance_work_mem domyślnie wynosiła 64 MB, a temp_file_limit około 2 GB – brakowało nam banalnie pamięci do stworzenia indeksu na dużej tabeli.
Dlatego w pg-index-health zebrałem szereg , moim zdaniem, parametrów, które warto skonfigurować dla każdej bazy danych.
Problem drugi – duplikujące się indeksy
Nasze bazy działają na dyskach SSD, a my używamy HA-konfiguracji z wieloma centrami danych, hostem głównym i n-liczbą replik. Miejsce na dysku to dla nas bardzo cenny zasób; jest tak samo ważne, jak wydajność i zużycie CPU. Dlatego z jednej strony potrzebujemy indeksów do szybkiego odczytu, a z drugiej strony nie chcemy widzieć w bazie zbędnych indeksów, ponieważ zajmują one miejsce i spowalniają aktualizację danych.
I tak, przywracając wszystkie i przyglądając się , postanowiłem przeprowadzić "wielkie" czyszczenie. Okazało się, że programiści nie lubią czytać dokumentacji dotyczącej bazy danych. Bardzo jej nie lubią. Z tego powodu występują dwa typowe błędy – ręcznie utworzony indeks na klucz główny oraz podobny "ręczny" indeks na unikalną kolumnę. Chodzi o to, że są one zbędne – Postgres sam wszystko załatwi. Takie indeksy można śmiało usuwać, a w tym celu powstała diagnostyka .
Problem trzeci – nakładające się indeksy
Większość początkujących programistów tworzy indeksy na jedną kolumnę. Stopniowo, kiedy poznają ten temat lepiej, ludzie zaczynają optymalizować swoje zapytania i dodawać bardziej złożone indeksy, które obejmują kilka kolumn. Tak powstają indeksy na kolumny A, A+B, A+B+C i tym podobne. Pierwsze dwa z tych indeksów można spokojnie wyrzucić, ponieważ są prefiksami trzeciego. To również znacząco oszczędza miejsce na dysku i w tym celu istnieje diagnostyka .
Problem czwarty – klucze obce bez indeksów
Postgres pozwala tworzyć ograniczenia klucza obcego bez wskazywania wspierającego indeksu. W wielu sytuacjach nie stanowi to problemu i może nawet w ogóle się nie objawiać… Do pewnego momentu…
Tak było i u nas: w pewnym momencie job’a, która działała według harmonogramu i oczyszczała bazę z testowych zamówień, zaczęła "składać" nam główny hosting. CPU i IO wzrosły do najwyższych wartości, zapytania zaczęły się zacinać i przerywać z powodu przekroczenia limitu czasu, serwis zwracał błąd 500. Szybka analiza pokazała, że zawieszają się zapytania typu:
usuń z <table> gdzie id w (…)Przy tym indeks po id w docelowej tabeli, naturalnie, istniał, a rekordy były usuwane na podstawie warunków, których było bardzo mało. Wydawało się, że wszystko powinno działać, ale niestety nie działało.
Na pomoc przyszedł cudowny explain analyze i powiedział, że oprócz usuwania rekordów w docelowej tabeli, odbywa się także sprawdzanie integralności referencyjnej i na jednej z powiązanych tabel to sprawdzenie spada w sequential scan z powodu braku odpowiedniego indeksu. Tak powstała diagnostyka .
Problem piąty – wartość null w indeksach
Domyślnie Postgres uwzględnia wartości null w indeksach btree, ale zazwyczaj nie są one potrzebne. Dlatego staram się usuwac te null’e (diagnostyka ), tworząc częściowe indeksy na kolumny nullable typu where is not nullW ten sposób udało mi się zmniejszyć rozmiar jednego z naszych indeksów z 1877 MB do 16 KB. A w jednym z usług rozmiar bazy danych SUMARYCZNIE zmniejszył się o 16% (o 4,3 GB w liczbach bezwzględnych) dzięki wykluczeniu wartości null z indeksów. Ogromna oszczędność miejsca na dysku przy stosunkowo prostych poprawkach. 🙂
Problem szósty – brak kluczy głównych
Ze względu na specyfikę mechanizmu możliwe jest wystąpienie takiej sytuacji, jak , kiedy rozmiar twojej tabeli szybko rośnie z powodu dużej ilości martwych rekordów. Naiwnie uważałem, że nas to nie dotyczy, że z naszą bazą nic takiego się nie stanie, bo my, oho!!!, jesteśmy normalnymi deweloperami… Jakże głupi i naiwny byłem…
Pewnego pięknego dnia jedna cudowna migracja wzięła i zaktualizowała wszystkie rekordy w dużej i aktywnie używanej tabeli. Zyskaliśmy +100 GB do rozmiaru tabeli z niczego. Było to strasznie frustrujące, ale nasze perypetie na tym się nie skończyły. Po tym, jak przez 15 godzin zakończył się automatyczny proces vacuum na tej tabeli, stało się jasne, że fizyczne miejsce nie wróci. Nie mogliśmy zatrzymać usługi i wykonać VACUUM FULL, dlatego podjęto decyzję o użyciu . I wtedy okazało się, że pg_repack nie potrafi obsługiwać tabel bez klucza głównego lub innego ograniczenia unikalnego, a w naszej tabeli klucza głównego nie było. Tak powstała diagnostyka .
W wersji biblioteki 0.1.5 dodano możliwość zbierania danych dotyczących bloatu tabel i indeksów oraz odpowiedniego reagowania na nie.
Problemy siedem i osiem – brak indeksów i nieużywane indeksy
Dwie następne diagnostyki – i – w swojej ostatecznej formie pojawiły się stosunkowo niedawno. Problem w tym, że nie można ich było po prostu dodać.
Jak już pisałem, używamy konfiguracji z wieloma replikami, a obciążenie czytania na różnych hostach jest zasadniczo różne. W efekcie uzyskuje się sytuację, że niektóre tabele i indeksy na niektórych hostach są prawie nieużywane, a do analizy trzeba zbierać statystyki ze wszystkich hostów w klastrze. też trzeba wykonywać na każdym hoście w klastrze, nie można tego zrobić tylko na masterze.
Takie podejście pozwoliło nam zaoszczędzić kilka dziesiątek gigabajtów dzięki usunięciu indeksów, które nigdy nie były używane, a także dodaniu brakujących indeksów do rzadko używanych tabel.
Na zakończenie
Oczywiście, dla prawie wszystkich diagnostyk można skonfigurować . Dzięki temu można szybko wprowadzić kontrole w swojej aplikacji, zapobiegając pojawieniu się nowych błędów, a następnie stopniowo naprawiać stare.
Część diagnostyk może być wykonywana już w testach funkcjonalnych zaraz po wgraniu migracji bazy danych. I to, moim zdaniem, jedna z najpotężniejszych możliwości mojej biblioteki. Przykład użycia można zobaczyć w .
Kontrole dotyczące nieużywanych lub brakujących indeksów, a także na bloat, mają sens przeprowadzać tylko na rzeczywistej bazie danych. Zebrane wartości mogą być zapisane w lub wysłane do systemu monitorowania.
Mam nadzieję, że pg-index-health będzie przydatna i poszukiwana. Możesz również przyczynić się do rozwoju biblioteki, zgłaszając wykryte problemy i proponując nowe diagnostyki.
Źródło: habr.com
