Filozoficzne wprowadzenie
Jak wiadomo, istnieją tylko dwa sposoby rozwiązania problemów:
- Metoda analizy lub metoda dedukcji, czyli od ogółu do szczegółu.
- Metoda syntezy lub metoda indukcji, czyli od szczegółu do ogółu.
Aby rozwiązać problem "poprawy wydajności bazy danych", może to wyglądać następująco.
Analiza — rozkładamy problem na części składowe i staramy się w ten sposób poprawić wydajność bazy danych w całości.
W praktyce analiza wygląda mniej więcej tak:
- Pojawia się problem (incydent wydajności)
- Zbieramy informacje statystyczne o stanie bazy danych
- Szukać wąskich gardeł
- Rozwiązujemy problemy w wąskich gardłach
Wąskie gardła bazy danych — infrastruktura (CPU, pamięć, dyski, sieć, system operacyjny), ustawienia (postgresql.conf), zapytania:
Infrastruktura: możliwości wpływu i zmian dla inżyniera — prawie zerowe.
Ustawienia bazy danych: możliwości zmian są nieco większe niż w poprzednim przypadku, ale zazwyczaj nadal dość trudne, szczególnie w chmurach.
Zapytania do bazy danych: jedyny obszar do manewru.
SYNTEZA — poprawiamy wydajność poszczególnych części, licząc na to, że w rezultacie poprawi się wydajność bazy danych.
Liryczne wprowadzenie lub po co to wszystko
Jak przebiega proces rozwiązywania incydentów wydajności, jeśli wydajność bazy danych nie jest monitorowana:
Klient - "u nas jest źle, wolno, proszę to poprawić"
Inżynier - "źle to jak?"
Klient – "tak jak teraz (godzinę temu, wczoraj, na ostatniej sprawie było), wolno"
Inżynier – "a kiedy było dobrze?"
Klient – "tydzień (dwa tygodnie) temu było nieźle." (To się udało)
Klient – "a nie pamiętam, kiedy było dobrze, ale teraz jest źle" (Typowa odpowiedź)
W rezultacie otrzymujemy klasyczny obraz:
Kto jest winny i co robić?
Na pierwszą część pytania najłatwiej odpowiedzieć — zawsze winny jest inżynier DBA.
Na drugą część odpowiedzieć też nie jest zbyt trudno — trzeba wdrożyć system monitorowania wydajności bazy danych.
Pojawia się pierwsze pytanie — co monitorować?
Droga 1. Będziemy monitorować WSZYSTKO
Obciążenie CPU, ilość operacji odczytu/zapisu na dysku, rozmiar przydzielonej pamięci oraz jeszcze megatonę różnych liczników, które może dostarczyć każda bardziej- mniej sprawna system monitorowania.
W efekcie otrzymujemy mnóstwo wykresów, tabel przestawnych i nieprzerwane powiadomienia na e-mail oraz 100% obłożenie inżyniera rozwiązywaniem tych samych biletów, które zazwyczaj mają standardową formułę — „Tymczasowy problem. Nie wymaga działania”. Wszyscy jednak są zajęci i zawsze jest co pokazać klientowi — praca wre.
Droga 2. Monitorować tylko to, co potrzebne, a to, co niepotrzebne, nie należy monitorować.
Można monitorować, trochę inaczej — tylko byty i zdarzenia:
- Na które inżynier DBA może wpływać.
- Dla których istnieje algorytm działań w przypadku wystąpienia zdarzenia lub zmiany bytu.
W oparciu o to założenie i przypominając „Filozoficzne wprowadzenie”, aby uniknąć regularnego powtarzania „Liryczne wprowadzenie lub po co to wszystko”, uzasadnione będzie monitorowanie wydajności poszczególnych zapytań w celu optymalizacji i analizy, co finalnie powinno prowadzić do poprawy wydajności całej bazy danych.
Ale aby poprawić obciążające zapytanie wpływające na ogólną wydajność bazy danych, najpierw trzeba je zlokalizować.
Zatem pojawiają się dwa powiązane pytania:
- które zapytanie uznaje się za obciążające?
- jak wyszukiwać obciążające zapytania.
Oczywiste jest, że obciążające zapytanie to zapytanie, które wykorzystuje wiele zasobów OS do uzyskania wyniku.
Przechodzimy do drugiego pytania — jak wyszukiwać, a następnie monitorować obciążające zapytania?
Jakie możliwości monitorowania zapytań są w PostgreSQL?
W porównaniu do Oracle, możliwości są nieco ograniczone, ale mimo wszystko można coś zrobić.
PG_STAT_STATEMENTS
Do wyszukiwania i monitorowania obciążających zapytań w PostgreSQL przeznaczone jest standardowe rozszerzenie pg_stat_statements.
Po zainstalowaniu rozszerzenia, w docelowej bazie danych pojawia się widok o tej samej nazwie, który należy wykorzystywać do celów monitorowania.
Docelowe kolumny pg_stat_statements do budowy systemu monitorowania:
- queryid Wewnętrzny kod haszujący wyliczany na podstawie drzewa rozbicia operatora.
- max_time Maksymalny czas spędzony na operatorze, w milisekundach.
Zbierając i wykorzystując statystyki na podstawie tych dwóch kolumn, można zbudować system monitorowania.
Jak wykorzystuje się pg_stat_statements do monitorowania wydajności PostgreSQL?
Do monitorowania wydajności zapytań używa się:
Po stronie docelowej bazy danych — widok pg_stat_statements.
Po stronie serwera i bazy danych monitorującej — zestaw skryptów bash i tabel serwisowych.
1 krok — zbieranie danych statystycznych
Na hoście monitorowania regularnie uruchamiany jest skrypt, który kopiuje zawartość widoku pg_stat_statements z docelowej bazy danych do tabeli pg_stat_history w bazie danych monitoringowej.
W ten sposób powstaje historia wykonania poszczególnych zapytań, którą można wykorzystać do tworzenia raportów wydajności i konfiguracji metryk.
2 krok — konfiguracja metryk wydajności
Na podstawie zebranych danych wybieramy zapytania, których wykonanie jest najistotniejsze dla klienta (aplikacji). Po uzgodnieniu z zamawiającym ustalamy wartości metryk wydajności, korzystając z pól queryid i max_time.
Wynik — rozpoczęcie monitorowania wydajności
- Skrypt monitorujący przy uruchomieniu sprawdza skonfigurowane metryki wydajności, porównując wartość max_time metryki z wartością z widoku pg_stat_statements w docelowej bazie danych.
- Jeśli wartość w docelowej bazie danych przekracza wartość metryki – generowane jest ostrzeżenie (incydent w systemie zgłoszeń)
Dodatkowa możliwość 1
Historia planów wykonania zapytań
Aby skutecznie rozwiązywać incydenty wydajności, dobrze jest mieć historię zmian planów wykonania zapytań.
Do przechowywania historii wykorzystywana jest tabela serwisowa log_query. Tabela ta jest wypełniana podczas analizy załadowanego pliku dziennika PostgreSQL. Ponieważ do pliku dziennika, w przeciwieństwie do widoku pg_stat_statements, trafia pełny tekst z wartościami parametrów wykonania, a nie znormalizowany tekst, istnieje możliwość rejestrowania nie tylko czasu i długości zapytań, ale także przechowywania planów wykonania w danym momencie.
Dodatkowa możliwość 2
Proces ciągłego poprawiania wydajności
Monitorowanie pojedynczych zapytań generalnie nie jest przeznaczone do rozwiązywania problemu ciągłego poprawiania wydajności bazy danych jako całości, ponieważ kontroluje i rozwiązuje problemy wydajności tylko dla pojedynczych zapytań. Jednak metodę tę można rozszerzyć i skonfigurować monitorowanie dla wszystkich baz danych.
W tym celu konieczne jest wprowadzenie dodatkowych metryk wydajności:
- W ciągu ostatnich dni
- Za okres bazowy
Skrypt wybiera zapytania z widoku pg_stat_statements w docelowej bazie danych i porównuje wartość max_time z średnią wartością max_time, w pierwszym przypadku za ostatnie dni lub za wybrany okres czasu (baseline), w drugim przypadku.
W ten sposób w przypadku degradacji wydajności dla dowolnego zapytania, ostrzeżenie zostanie automatycznie wygenerowane, bez ręcznej analizy raportów.
A co ma wspólnego syntez?
W opisanym podejściu, jak zakłada metoda syntezy — poprawiając poszczególne części systemu, poprawiamy system jako całość.
- Zapytanie wykonywane przez bazę danych – teza
- Zmienione zapytanie – antyteza
- Zmiana stanu systemu — synteza
Rozwój systemu
- Rozszerzenie zbieranej statystyki o dodanie historii dla systemowego widoku pg_stat_activity
- Rozszerzenie zbieranej statystyki o dodanie historii dla statystyk poszczególnych tabel biorących udział w zapytaniach
- Integracja z systemem monitorowania w chmurze AWS
- I jeszcze, coś można wymyślić…
Źródło: habr.com
