Jak włączyć i czytać pg_stat_statements (najcięższe zapytania)

PostgreSQL

Jak włączyć i czytać pg_stat_statements (najcięższe zapytania)

W skrócie

pg_stat_activity mówi, co dzieje się teraz, ale nie odpowiada na pytanie, co obciąża bazę na dłuższą metę. Do tego służy pg_stat_statements - rozszerzenie, które zbiera zbiorcze statystyki dla każdego wzorca zapytania: ile razy został wykonany, ile łącznie zajął czasu, ile średnio i ile wierszy zwrócił. Dzięki niemu zamiast zgadywać, mierzymy, i optymalizujemy dokładnie te zapytania, które naprawdę kosztują. To najważniejsze narzędzie do tuningu wydajności w PostgreSQL.

Jak to wygląda w praktyce

Klasyczna pułapka to skupienie się na jednym wolnym raporcie, który trwa trzy sekundy, podczas gdy prawdziwy problem to malutkie zapytanie trwające dwie milisekundy, ale wykonywane pół miliona razy na godzinę. Sumarycznie to drugie zżera wielokrotnie więcej zasobów, a nie widać go gołym okiem, bo każde pojedyncze wykonanie jest szybkie. Bez zbiorczych statystyk optymalizujemy nie to, co trzeba. Objawem jest baza pod stałym, wysokim obciążeniem procesora bez jednego oczywistego winowajcy w podglądzie aktywności. Dopiero posortowanie zapytań po łącznym czasie wykonania pokazuje, że kilka wzorców odpowiada za większość całego kosztu serwera.

Dlaczego tak się dzieje

Standardowo PostgreSQL nie przechowuje historii wykonania zapytań - każde znika po zakończeniu. Rozszerzenie pg_stat_statements zakłada w pamięci współdzielony bufor, w którym normalizuje zapytania, sprowadzając je do wzorca, i sumuje dla każdego wzorca liczby wywołań oraz czasy. Dwa zapytania różniące się tylko wartością w klauzuli WHERE traktowane są jako ten sam wzorzec, dlatego statystyki mają sens mimo tysięcy różnych parametrów. Ponieważ rozszerzenie musi działać od startu serwera i alokować pamięć współdzieloną, wymaga wpisania do shared_preload_libraries i restartu - nie da się go w pełni włączyć w locie. Bez tego kroku samo CREATE EXTENSION nie wystarczy.

Jak to rozwiązać krok po kroku

1. Dopisz bibliotekę do preload i zrestartuj serwer: ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';, a potem systemctl restart postgresql. To jedyny krok wymagający restartu.
2. Załóż rozszerzenie w bazie, którą chcesz obserwować: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;.
3. Znajdź zapytania o największym łącznym czasie: SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;. To one najbardziej obciążają serwer.
4. Osobno sprawdź najwolniejsze pojedynczo, sortując po mean_exec_time, oraz najczęstsze, sortując po calls - to trzy różne perspektywy tego samego problemu.
5. Weź najcięższy wzorzec, podstaw realne wartości i przeanalizuj plan przez EXPLAIN (ANALYZE, BUFFERS). Popraw go indeksem, przepisaniem zapytania albo dostrojeniem parametrów pamięci.
6. Po wdrożeniu poprawki wyzeruj statystyki, aby mierzyć od nowa: SELECT pg_stat_statements_reset();, i po jakimś czasie porównaj wynik.

Jak sprawdzić, że zadziałało

Najpierw upewnij się, że rozszerzenie w ogóle zbiera dane: SELECT count(*) FROM pg_stat_statements; powinno zwrócić liczbę większą od zera. Po optymalizacji konkretnego zapytania i wyzerowaniu statystyk pozwól bazie popracować pod normalnym ruchem, a następnie znowu posortuj po total_exec_time. Jeśli poprawiony wzorzec spadł w rankingu albo ma wyraźnie niższy mean_exec_time przy tej samej liczbie wywołań, tuning zadziałał. Ostatecznym potwierdzeniem jest spadek ogólnego obciążenia procesora serwera bazy przy tym samym natężeniu ruchu aplikacji.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5