pg_stat_statements nie pokazuje rozkładu czasów zapytań - kiedy sięgnąć po pg_stat_monitor

PostgreSQL

pg_stat_statements nie pokazuje rozkładu czasów zapytań - kiedy sięgnąć po pg_stat_monitor

W skrócie

Ten problem dotyczy administratorów i programistów, którzy szukają przyczyny okresowych spowolnień. pg_stat_statements, czyli standardowe rozszerzenie ze statystykami zapytań, podaje średnią i maksimum, ale nie pokazuje, czy wolne wykonania zdarzyły się wczoraj o 14:00, czy rozkładają się równo przez cały miesiąc. Pokażemy, czego brakuje w pg_stat_statements na PostgreSQL 16.4, jak włączyć pg_stat_monitor i jak odczytać z niego okna czasowe oraz histogram czasów.

Jak to wygląda w praktyce

Użytkownicy zgłaszają, że raport czasem liczy się długo, a w pg_stat_statements to samo zapytanie ma niską średnią. Widok pokazuje dla każdego zapytania jeden wiersz z licznikami narastającymi od ostatniego resetu. Sprawdziliśmy jego kolumny w PostgreSQL 16.4 (rozszerzenie w wersji 1.10): czasy wykonania to total_exec_time, min_exec_time, max_exec_time, mean_exec_time i stddev_exec_time. Nie ma kolumny z datą wykonania ani podziału na przedziały czasu. Znacznik czasu znajdziesz tylko w widoku pg_stat_statements_info (kolumna stats_reset), więc wiesz, od kiedy liczniki rosną, ale nie wiesz, kiedy wystąpiły wolne wykonania ani ile wykonań było wolnych.

Druga przeszkoda dotyczy obu rozszerzeń: bez wpisu w shared_preload_libraries i restartu nie zadziałają. W naszym teście samo CREATE EXTENSION przeszło, ale odczyt widoku skończył się błędem:

CREATE EXTENSION pg_stat_statements;
SELECT count(*) FROM pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

Dlaczego tak się dzieje

pg_stat_statements przechowuje wartości zbiorcze: liczbę wywołań, sumę, minimum, maksimum, średnią i odchylenie standardowe czasu. Z takich liczb nie odtworzysz rozkładu. Ta sama średnia może wynikać z równych wykonań albo z wielu szybkich i kilku bardzo wolnych. Reset funkcją pg_stat_statements_reset() kasuje całą zebraną historię.

Zobacz też: Szkolenie Zaawansowana administracja PostgreSQL: High Availability, Disaster Recovery

pg_stat_monitor to rozszerzenie firmy Percona zbudowane na bazie pg_stat_statements. Dziedziczy jego kolumny i dzieli statystyki na okna czasowe (time buckets). Kolumny bucket i bucket_start_time mówią, w którym oknie wykonano zapytanie, a resp_calls to histogram, czyli liczba wykonań w kolejnych przedziałach czasu. Granice histogramu ustawiają pgsm_histogram_min (domyślnie 1 ms), pgsm_histogram_max (10 000 ms) i pgsm_histogram_buckets (20 przedziałów). Widok ma też client_ip, application_name, relations oraz elevel, sqlcode i message dla zapytań zakończonych błędem.

Domyślnie okno trwa 60 s (pgsm_bucket_time), a okien jest 10 (pgsm_max_buckets). Gdy wszystkie się zapełnią, najstarsze jest czyszczone i używane ponownie, więc przy ustawieniach domyślnych w pamięci jest około 10 minut historii. Oba parametry mają kontekst postmaster, czyli zmiana wymaga restartu.

Jak to rozwiązać krok po kroku

1. Zainstaluj pakiet. Na Red Hat, Rocky i Alma z repozytorium PGDG: sudo dnf install -y pg_stat_monitor_16. Na Debianie i Ubuntu z repozytorium Percona: najpierw narzędzie percona-release (wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb i sudo dpkg -i percona-release_latest.generic_all.deb), potem sudo percona-release setup ppg16 i sudo apt-get install -y percona-pg-stat-monitor16.
2. Sprawdź obecną listę bibliotek: SHOW shared_preload_libraries;. ALTER SYSTEM zapisze nową wartość w całości, więc podaj wszystkie potrzebne moduły, każdy jako osobny napis. Jeśli zostawiasz pg_stat_statements, pg_stat_monitor musi być po nim:

   ALTER SYSTEM SET shared_preload_libraries =
       'pg_stat_statements', 'pg_stat_monitor';

   Nie wpisuj listy w jednym napisie ('pg_stat_statements, pg_stat_monitor'). ALTER SYSTEM zapisze ją wtedy w postgresql.auto.conf jako jedną nazwę w cudzysłowie, a serwer po restarcie nie wstanie. W naszym teście (z modułem auto_explain na drugiej pozycji) log pokazał FATAL: could not access file "pg_stat_statements, auto_explain".
3. Zrestartuj klaster, na przykład sudo systemctl restart postgresql-16 (nazwa usługi zależy od dystrybucji), i w bazie, z której będziesz czytać statystyki, wykonaj CREATE EXTENSION pg_stat_monitor;.
4. Dopiero teraz wydłuż historię. W PostgreSQL 16 ALTER SYSTEM odrzuca parametry modułu, którego serwer jeszcze nie wczytał, w naszym teście komunikatem unrecognized configuration parameter "pg_stat_monitor.pgsm_bucket_time" (od wersji 17 ALTER SYSTEM je przyjmuje). Polecenia ALTER SYSTEM SET pg_stat_monitor.pgsm_bucket_time = 300; i ALTER SYSTEM SET pg_stat_monitor.pgsm_max_buckets = 24; dają 24 okna po 5 minut, czyli 2 godziny. Pamięć na statystyki (pgsm_max, domyślnie 256 MB) dzielona jest równo między okna, więc więcej okien to mniej pamięci na każde z nich. Zrestartuj klaster ponownie. Jeśli wolisz jeden restart, dopisz oba parametry ręcznie do postgresql.conf przed restartem z kroku 3: plik konfiguracyjny przyjmuje parametry modułów, których serwer jeszcze nie wczytał.
5. Odczytaj rozkład wybranego zapytania w kolejnych oknach, a potem histogram dla jednego okna:

   SELECT bucket, bucket_start_time, queryid, calls,
          mean_exec_time, max_exec_time, resp_calls
   FROM pg_stat_monitor
   WHERE query LIKE '%FROM orders%'
   ORDER BY bucket_start_time;
   
   -- numer okna i queryid z poprzedniego wyniku
   SELECT * FROM histogram(3, '2920803561901199087')
          AS a(range text, freq int, bar text);

6. Pamiętaj o danych wrażliwych. Domyślnie pgsm_normalized_query jest wyłączony, więc do kolumny query trafiają rzeczywiste wartości parametrów. Gdy w zapytaniach są dane osobowe, włącz zastępowanie ich symbolami $1, $2: ALTER SYSTEM SET pg_stat_monitor.pgsm_normalized_query = on; i SELECT pg_reload_conf();. Zmiana obowiązuje w nowych połączeniach.

Jak sprawdzić, że zadziałało

Po restarcie SHOW shared_preload_libraries; musi zwrócić listę z pg_stat_monitor. Potem sprawdź, czy okna się zmieniają:

SELECT bucket, bucket_start_time, bucket_done, sum(calls) AS calls
FROM pg_stat_monitor
GROUP BY bucket, bucket_start_time, bucket_done
ORDER BY bucket_start_time;

Co pgsm_bucket_time sekund powinno pojawić się nowe okno z kolejnym bucket_start_time, a zamknięte okna mają bucket_done = true. Niepusta tablica w resp_calls oznacza, że histogram jest zbierany. Jeśli wolne wykonania skupiają się w jednym oknie, masz godzinę problemu, a z client_ip i application_name także jego źródło.

Powiązane problemy

• Jeśli dopiero zaczynasz od statystyk zapytań, przejdź do tekstu jak włączyć i czytać pg_stat_statements.
• Gdy histogram wskaże wolne zapytanie, dalsze kroki opisujemy w tekście zapytania są wolne, czyli od czego zacząć tuning w PostgreSQL.
• Jeśli po restarcie serwer nie wstaje albo nie widzi biblioteki, zajrzyj do tekstu ERROR: could not load library, czyli jak zainstalować rozszerzenie.

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