To rozszerzenie służące do monitorowania wydajności zapytań w postgresie. Zbiera informacje o wykonywanych zapytaniach i pozwala na wgląd do ich statystyk zebranych w jednym widoku. Bazuje na innym rozszerzeniu, pg_stat_statements, ale jest jego bardziej zaawansowaną wersją. Najistotniejszą z nich jest, że pg_stat_statements zbiera statystyki inkrementalnie, podczas gdy pg_stat_monitor dzieli zebrane informacje na okresy czasowe, tak zwane "time buckets", dzięki czemu możemy sprawdzić wydajność zapytań w interesującym nas okresie czasu.

Dodatkowo może zbierać rzeczywiste wartości parametrów w zapytaniach, zamiast wartości zamiennych "?", jak również pozwala przechowywać komentarze do zapytań /* ... */. Pozwala również na zbieranie zapytań wywoływanych wewnątrz funkcji, automatyczne zapisanie planu wykonania zapytania, posiada informacje o tabelach i indeksach, z których zapytanie korzystało, o użytkownikach, aplikacjach i serwerach, z których zapytanie było wywołane, umożliwia przeszukiwanie zapytań według typów (select, insert, update, delete), według kodów błędów SQL. Pozwala tworzyć histogramy, dzięki czemu mamy dostęp do wizualnej reprezentacji danych dotyczących czasów wykonania zapytań. Rozszerzenie to możemy zainstalować do dowolnej wersji postgresa od wersji 11.

Instalacja pg_stat_monitor na Ubuntu

Przed instalacją należy pobrać repozytorium percony:

curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb


sudo apt install gnupg2 lsb-release
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt update

Włączyć repozytorium dla Postgresa 15:

sudo percona-release setup ppg-15

Instalacja pg_stat_monitora dla Postgresa 15:

sudo apt install percona-pg-stat-monitor15

Instalacja pg_stat_monitor na systemach z rodziny Red Hat

Instalacja na RedHat/CentOS jest dużo prostsza niż w przypadku Ubuntu, ponieważ pg_stat_monitor dostępny jest w oficjalnym repozytorium Postgresa od PGDG (Postgresql Global Development Group).

Wykonujemy ją jednym poleceniem:

sudo dnf install -y pg_stat_monitor_15

Utworzenie rozszerzenia w postgresie

W konfiguracji postgresa musimy dodać nazwę rozszerzenia do parametru "shared_preload_libraries",

edytując konfigurację patroni poprzez patronictl -c /etc/patroni/config.yml edit-config:

patronictl -c /etc/patroni/config.yml edit-config
shared_preload_libraries: 'pg_stat_monitor'

lub jeżeli korzystamy, nie korzystamy z patroni bezpośrednio w postgresql.conf lub przez ALTER SYSTEM SET.

ALTER SYSTEM SET shared_preload_libraries = pg_stat_monitor;

Następnie musimy zrestartować postgresa, ponieważ dodatkowe biblioteki zdefiniowane w shared_preload_libraries wczytują się tylko podczas startu postgresa.

[postgres@centos ~]$ patronictl -c /etc/patroni/config.yml restart szkolenie --force
+ Cluster: szkolenie -----+-----------+----+-----------+-----------------+
| Member | Host | Role    | State     | TL | Lag in MB | Pending restart |
+--------+------+---------+-----------+----+-----------+-----------------+
| pg1    | pg1  | Replica | streaming |  3 |         0 | *               |
| pg2    | pg2  | Leader  | running   |  3 |           | *               |
| pg3    | pg3  | Replica | streaming |  3 |         0 | *               |
+--------+------+---------+-----------+----+-----------+-----------------+
Success: restart on member pg1
Success: restart on member pg2
Success: restart on member pg3

lub

[postgres@centos ~]$ pg_ctl -D /data_pg restart -m fast

oraz stworzyć rozszerzenie w jednej z baz:

postgres=# create extension pg_stat_monitor ;
CREATE EXTENSION

Od teraz możemy sprawdzić informacje o interesujących nas zapytaniach w widoku pg_stat_monitor.

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_monitor;

Najistotniejsze wartości, jakie tam znajdziemy, to:

• bucket - numer kubełka/okna czasowego, "time bucket",


• bucket_start_time - czas, od którego zapytania są przechowywane w danym oknie,


• username - nazwa użytkownika, który zapytanie wywołał,


• dbname - baza, w której zapytanie było wywołane,


• client_ip - adres serwera, z którego podłączony był klient,


• queryid - identyfikator zapytania, jeżeli postgres wykonuje wiele podobnych zapytań, które różnią się tylko wartościami w klauzuli where, ich queryid będzie takie samo,


• query - treść zapytania,


• comment - komentarz do zapytania,


• planid - identyfikator planu wykonania,


• query_plan - plan wykonania zapytania, jeżeli był przechwytywany,


• application_name - nazwa aplikacji, z której pochodzi zapytanie,


• relations - lista tabel, z których korzysta zapytanie,


• cmd_type, cmd_type_text - typ zapytania, 1 - select, 2 - update, 3 - insert, 4 - delete,


• elevel - typ błędu,


• sqlcode - kod błędu,


• message - treść błędu,


• calls - liczba wykonań danego zapytania,


• total_exec_time - suma czasów wykonania zapytania w milisekundach,


• min_exec_time - najkrótszy czas wykonania w milisekundach,


• max_exec_time - najdłuższe wykonanie zapytania w milisekundach,


• mean_exec_time - średni czas wykonania w milisekundach,


• stddev_exec_time - odchylenie standardowe czasu poświęconego na zapytanie w milisekundach,


• rows - liczba zwróconych wierszy,


• shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written - liczba bloków w pamięci współdzielonej, które zostały przeczytane z pamięci, wczytane do pamięci, zbrudzone (przynajmniej jeden wiersz w bloku został zmieniony), zapisane,


• local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written - liczba bloków przeczytanych z tabel tymczasowych, liczba bloków przeczytanych z dysku, liczba zbrudzonych bloków danych (przynajmniej jeden wiersz w bloku został zmieniony), liczba bloków zapisanych na dysku,


• temp_blks_read, temp_blks_written - liczba bloków przeczytanych z plików tymczasowych, liczba bloków zapisanych w plikach tymczasowych,


• blk_read_time, blk_write_time - całkowity czas potrzebny na odczytanie wszystkich bloków i zapisanie wszystkich bloków,


• resp_calls - histogram czasów wykonania,


• cpu_user_time, cpu_sys_time - czas, który procesor wykorzystał na wykonywanie zapytania, czas procesora wykorzystany na wykonywanie funkcji systemowych przez proces wykonujący zapytanie


• wal_records - liczba wpisów WAL wygenerowanych przez zapytanie,


• wal_fpi - liczba bloków danych zapisanych w plikach WAL przez zapytanie,


• wal_bytes - liczba bajtów wykorzystanych w plikach WAL przez zapytanie,


• bucket_done - informacja, czy dany time bucket jest nadal aktywny, czy został już zakończony,


• plans - liczba przygotowanych planów wykonania zapytania,


• total_plan_time, min_plan_time, max_plan_time, mean_plan_time, sttdev_plan_time - całkowity czas potrzebny na zaplanowanie wykonania zapytań, minimalny czas generowania planu wykonania, maksymalny czas, odchylenie standardowe.

Domyślnie pg_stat_monitor nie korzysta ze swojego pełnego potencjału, przykładowo zbiera tylko zapytania wysłane bezpośrednio z aplikacji klienckiej, a ma możliwość zbierania również informacji o podzapytaniach i zapytaniach wywoływanych w funkcjach, nie przechwytuje komentarzy czy statystyk z operacji planowania wykonania zapytania. Możemy to zmienić poprzez konfigurację jego parametrów.

Listę parametrów, ich aktualną wartość, domyślną, minimalną oraz maksymalną i opis ustawienia możemy znaleźć w widoku:

postgres=# SELECT name, setting, unit, context, vartype, min_val, max_val, enumvals, boot_val, reset_val FROM pg_settings WHERE name LIKE '%pg_stat_monitor%';

• pgsm_max - dane zbierane przez pg_stat_monitor przechowywane są w pamięci shared_buffers. Parametr ten określa, ile pamięci, w MB, postgres może przeznaczyć na przechowywanie statystyk. Pamięć dzielona jest równomiernie pomiędzy wszystkie "time buckety" i alokowana jest podczas startu postgresa,


• pgsm_query_max_len - ograniczenie rozmiaru zapytania w celu oszczędzania pamięci. Wartość w znakach, domyślnie dłuższe zapytania skraca do 2048 znaków,


• pgsm_track_utility - śledzenie wykonywania poleceń "serwisowych" wszystkich DDL oraz poleceń takich jak VACUUM, COPY i wszystkich innych poza SELECT, INSERT, UPDATE, DELETE,


• pgsm_normalized_query - pg_stat_monitor domyślnie zapisuje zapytania razem z rzeczywistymi wartościami parametrów. Zmieniając wartość parametru na "true", spowodujemy zastąpienie rzeczywistych wartości wartościami zastępczymi, przykładowo "$1",


• pgsm_max_buckets - liczba okienek czasowych, "time bucketów", jeżeli wszystkie zdefiniowane buckety zostaną wykorzystane, najstarszy zostanie wyczyszczony i wykorzystany ponownie,


• pgsm_bucket_time - długość okienka czasowego, jak często pg_stat_monitor powinien zmienić "time bucket" na kolejny,


• pgsm_histogram_min - najniższa wartość (w milisekundach), którą chcemy zobaczyć w histogramie, od niej rozpocznie się pierwszy przedział czasowy,


• pgsm_histogram_max - jak wyżej, ale dla najwyższej wartości w histogramie,


• pgsm_histogram_buckets - ile przedziałów czasowych ma pokazywać histogram,


• pgsm_query_shared_buffer - limit wykorzystania pamięci wykorzystanej do śledzenia zapytania przez pg_stat_monitor,


• pgsm_enable_overflow - pozwolenie na wykorzystanie SWAPa, jeżeli skończy się miejsce w pamięci ograniczone parametrem pgsm_max,


• pgsm_overflow_target - historyczny parametr, został zastąpiony przez pgsm_enable_overflow,


• pgsm_enable_pgsm_query_id - generowanie unikalnych identyfikatorów dla zapytań na podstawie użytkownika, bazy, schematu i zapytania. Pozwala na porównanie wydajności dla tego samego zapytania na różnych wersjach postgresa, ponieważ hash będzie zawsze taki sam, niezależnie od wersji. Włączenie tej opcji generuje dodatkowe obciążenie,


• pgsm_enable_query_plan - przechwytywanie planów wykonania zapytania. Włączenie tej opcji generuje dodatkowe obciążenie na bazie i powoduje spadek wydajności rzędu 10-15%, ale może okazać się przydatne podczas doszukiwania się problematycznych zapytań,


• pgsm_track - określa, jakie typy zapytań powinny być gromadzone przez pg_stat_monitor. Dostępne opcje to: top - używany domyślnie, zbiera informacje tylko o zapytaniach wywołanych bezpośrednio przez aplikacje klienckie, all - zbiera informacje o wszystkich zapytaniach, również tych w podzapytaniach, funkcjach, itp, oraz none - wyłącza zbieranie informacji o zapytaniach, moduł cały czas jest załadowany, posiada alokowany blok pamięci w shared_buffers, ale nie zbiera żadnych informacji,


• pgsm_extract_comments - zapisywanie komentarzy z zapytań, ciekawe rozwiązanie- każde zapytanie ma na końcu dopisany komentarz z informacją, z którego pliku oraz z której linii pochodzi dane zapytanie, bardzo ułatwia to odnajdywanie i rozwiązywanie problemów z aplikacją,


• pgsm_track_planning - zbieranie informacji statystycznych odnośnie planowania wykonania zapytań, dostępny dla postgresa 14+.

Wszystkie parametry z kontextem "postmaster" wymagają restartu postgresa. Pozostałe można zmienić dla bieżącej sesji oraz za pomocą ALTER SYSTEM SET, zmiany wartości w plikach konfiguracyjnych i przeładowania konfiguracji, należy jednak nawiązać nowe połączenie, ponieważ będą aktywne dopiero po nawiązaniu nowego połączenia.

Przykłady użycia

Przed testem rozszerzenia wskazane jest uruchomienie pgbench i wygenerowanie jakiegoś ruchu w bazie. Dane w pf_stat_monitorze będą wyglądały chociaż trochę "prawdziwiej". Zobaczymy kilka rodzajów zapytań, zamiast samych healthchecków patroni i paru zapytań, które chwilę wcześniej wykonaliśmy.

pgbench -h pg1 -p 5432 -S -c 80 -P 5 -T 600

liczba wykonań dla każdego zapytania wg okienek czasowych

postgres=# select bucket, bucket_start_time,substr(query,0,50)||'...' as query, calls from pg_stat_monitor;

Dodając do zapytania kolumny takie jak: userid, datname, application_name, client_ip, możemy łatwo sortować zapytania według interesujących nas kryteriów.

Zapytania mogą być przechowywane w formie identycznej, w jakiej były wykonane, lub w formie znormalizowanej. Parametrem za to odpowiedzialnym jest pg_stat_monitor.pgsm_normalized_query. Zapisywanie rzeczywistych wartości ułatwia analizowanie zapytań i ich planów wykonania, ale jednocześnie przechowuje wrażliwe dane w widoku wspólnym dla wszystkich baz.

Jeżeli przechowujemy wartości parametrów, te same zapytania, różniące się tylko wartością parametru, są przechowywane jako pierwsze zapytanie w danym okienku czasowym. Jeżeli wyłączymy przechowywanie, wartości będą zastąpione przez $1.

# zmiana za pomocą alter system set, wymaga ponownego nawiązania połączenia
postgres=# alter system set pg_stat_monitor.pgsm_normalized_query = true;
ALTER SYSTEM


postgres=# select pg_reload_conf();
postgres=# exit
[postgres@centos ~]$ psql
psql (15.3)
Type "help" for help.

postgres=# select * from mvcc where x = 1;
 x | y
---+---
 1 | 6
(1 row)

postgres=# select query,calls from pg_stat_monitor where query like '%mvcc%';
              query              | calls
---------------------------------+-------
 select * from mvcc where x = $1 |     1
(1 row)

# zmiana parametru tylko dla obecnej sesji, po rozłączeniu się wraca do
# poprzedniej wartości
postgres=# set pg_stat_monitor.pgsm_normalized_query = false;
SET
postgres=# select * from mvcc where x = 1;
 x | y
---+---
 1 | 6
(1 row)

postgres=# select query,calls from pg_stat_monitor where query like '%mvcc%';
              query              | calls
---------------------------------+-------
 select * from mvcc where x = 1 |     1
(1 row)

Sprawdzanie błędów w zapytaniach

postgres=# select * from asd;
ERROR:  "asd" is an index
LINE 1: select * from asd;
                      ^
postgres=# select decode_error_level(elevel) as elevel,sqlcode,query,message from pg_stat_monitor where elevel != 0;
 elevel | sqlcode |       query        |      message
--------+---------+--------------------+-------------------
        | 42809   | select * from asd; | "asd" is an index
(1 row)

Histogram zapytań, sprawdzenie histogramu zapytań

postgres=# select bucket,queryid,substr(query,0,50)||'...' as query, calls, resp_calls from pg_stat_monitor limit 3;
 bucket |     queryid      |                        query                         | calls |      resp_calls
--------+------------------+------------------------------------------------------+-------+-----------------------
      7 | 4EAA55C74CE949AC | select * from mvcc where x = $1... |     2 | {1,1,0,0,0,0,0,0,0,0}
      7 | 5F2BE4B24E3AB9D1 | select queryid,substr(query,$1,$2)||$3 as query ... |     1 | {1,0,0,0,0,0,0,0,0,0}
      7 | C7B5821EE684D026 | select query,calls from pg_stat_monitor where que... |     1 | {1,0,0,0,0,0,0,0,0,0}

Funkcja histogram(bucket,queryid)

postgres=# select * from histogram(7,'73F0ACC152F17D03') as a(range text, freq int, bar text);
           range           | freq  |              bar
---------------------------+-------+--------------------------------
 {{0.000 - 1.000}          | 59982 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
  (1.000 - 2.778}          |  2318 | ■
  (2.778 - 4.162}          |  2297 | ■
  (4.162 - 6.623}          |  4460 | ■■
  (6.623 - 11.000}         |  6409 | ■■■
  (11.000 - 18.783}        |  6229 | ■■■
  (18.783 - 32.623}        |  4342 | ■■
  (32.623 - 57.234}        |  2489 | ■
  (57.234 - 101.000}       |   989 |
  (101.000 - 178.827}      |   184 |
  (178.827 - 317.226}      |    10 |
(10 rows)


Sprawdzenie planów wykonania dla zapytań

#włączenie zbierania planów wykonania zapytań
postgres=# alter system set pg_stat_monitor.pgsm_enable_query_plan to on;
ALTER SYSTEM
postgres=# select queryid,rows,substr(query,0,50)||'...' as query, query_plan,calls from pg_stat_monitor;