
Monitorowanie kondycji PostgreSQL

Jeżeli przyrównać administratora baz danych do wojownika, to jego mieczem niewątpliwie jest powłoka systemowa, natomiast tarczą – system monitoringu oraz wiedza i doświadczenie związane z rejestrowaniem parametrów bazy danych. W tym artykule przybliżę, w jaki sposób określić kondycję bazy danych z wykorzystaniem parametrów, które prezentuje nam sam silnik PostgreSQL.
Jeżeli przyrównać administratora baz danych do wojownika, to jego mieczem niewątpliwie jest powłoka systemowa, natomiast tarczą – system monitoringu oraz wiedza i doświadczenie związane z rejestrowaniem parametrów bazy danych. W tym artykule przybliżę, w jaki sposób określić kondycję bazy danych z wykorzystaniem parametrów, które prezentuje nam sam silnik PostgreSQL.
W przeszłości pisaliśmy już na temat monitorowania bazy danych PostgreSQL „z zewnątrz”, wykorzystując do tego standardowe narzędzia dostępne dla każdego sysadmina. Niedawno prezentowaliśmy również narzędzie do monitorowania aktywności w bazie danych. Tym razem skupimy się na danych, które udostępnia sam PostgreSQL na potrzeby administratora.
Postgresowe widoki systemowe
Postgres oferuje administratorom sporo informacji w widokach o nazwach zaczynających się od pg_stat_
. Używając ich, możemy wyciągać sporo informacji na temat aktualnego stanu systemu bazodanowego. Dzięki ich analizie możemy łatwo zidentyfikować np. brakujące indeksy, potencjalnie źle dobrane ustawienia, jak na przykład work_mem
na zbyt niskim poziomie. Poniżej kilka najistotniejszych widoków z punktu widzenia administratora.
Tabela | Opis |
---|---|
pg_stat_activity |
informacje o procesach związane z aktualną aktywnością, jeden wiersz na proces |
pg_stat_database |
statystyki z poziomu baz danych w systemie, jeden wiersz na bazę |
pg_stat_user_tables |
statystyki z poziomu tabel, jeden wiersz na tabelę |
Widok pg_stat_database
Weźmy na warsztat widok pg_stat_database
. Jego struktura wygląda następująco:
postgres=# \d pg_stat_database View "pg_catalog.pg_stat_database" Column | Type | Modifiers ----------------+--------------------------+----------- datid | oid | datname | name | numbackends | integer | xact_commit | bigint | xact_rollback | bigint | blks_read | bigint | blks_hit | bigint | tup_returned | bigint | tup_fetched | bigint | tup_inserted | bigint | tup_updated | bigint | tup_deleted | bigint | conflicts | bigint | temp_files | bigint | temp_bytes | bigint | deadlocks | bigint | blk_read_time | double precision | blk_write_time | double precision | stats_reset | timestamp with time zone |
Możemy tutaj znaleźć parametry takie jak:
numbackends
, który mówi o ilości otwartych połączeń do bazyxact_commit
orazxact_rollback
, która pozwala ustalić stosunek zatwierdzonych oraz wycofywanych transakcjiblks_hit
orazblks_read
określających stopień skuteczności zapytań do operowania na danych znajdujących się w cachetup_*
, dzięki którym możemy stwierdzić, jaki jest ogólny model dostępu do danych w bazie (na zasadzie opozycji – odczyt czy zapis).
W końcu mamy również blks_read_time
oraz blk_write_time
. Odpowiadają one na pytanie dotyczące czasu potrzebnego na dokonanie zapisu oraz odczytu z dysku na danym systemie. Domyślnie będą wyzerowane, ponieważ włączenie parametru na inne niż track_io_timing = off
mogłoby powodować znaczący narzut na czas wykonywania operacji. Na szczęście twórcy PostgreSQL wraz z serwerem dostarczają narzędzie do określenia wpływu operacji wymaganych podczas zbierania statystyk – pg_test_timing
. W przypadku instalowania PostgreSQL na tzw. „bare metal”, czyli bez udziału wirtualizacji, włączenie tego parametru w zasadzie nie wymaga przeprowadzania testu. Gdy jednak baza pracuje w maszynie wirtualnej, warto dowiedzieć się, jaki narzut może spowodować włączenie tej opcji. Poniżej przykładowy wynik takiego testu:
$ pg_test_timing Testing timing overhead for 3 seconds. Per loop time including overhead: 18.16 nsec Histogram of timing durations: < usec % of total count 1 98.20300 162242046 2 1.79523 2965915 4 0.00053 871 8 0.00003 51 16 0.00067 1105 32 0.00054 899
Widok pg_stat_user_tables
Idąc głębiej, na kolejnym stopniu szczegółowości możemy przeanalizować zawartość widoku pg_stat_user_tables
. Zobaczmy, jakie dane możemy z niego wyczytać:
postgres=# \d pg_stat_user_tables View "pg_catalog.pg_stat_user_tables" Column | Type | Modifiers ---------------------+--------------------------+----------- relid | oid | schemaname | name | relname | name | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | bigint | n_live_tup | bigint | n_dead_tup | bigint | n_mod_since_analyze | bigint | last_vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | vacuum_count | bigint | autovacuum_count | bigint | analyze_count | bigint | autoanalyze_count | bigint |
Znajdują się tu najistotniejsze informacje z punktu widzenia wydajności, takie jak liczba zapytań, które musiały skorzystać ze skanowania sekwencyjnego (seq_scan
) oraz to, ile wierszy jest przeglądanych w czasie skanowania (seq_tup_read
). Następnie możemy również wyczytać, jak wiele razy został wykorzystany indeks związany z daną tabelą (idx_scan
).
Poprawna analiza tych danych to jedna z najistotniejszych umiejętności DBA. W czasie prowadzonych przez nas szkoleń z administracji PostgreSQL staramy się przekazać jak najwięcej przykładów wykorzystania zebranych danych. Jednym z prezentowanych zastosowań jest wyszukiwanie potencjalnych indeksów. Do tego celu możemy skorzystać z następującego zapytania:
SELECT schemaname || '.' || relname as table, seq_scan, seq_tup_read, seq_tup_read / seq_scan as avg_tup, idx_scan, idx_scan / seq_scan as ratio FROM pg_stat_user_tables WHERE seq_scan > 0 ORDER BY seq_tup_read DESC LIMIT 25;
Wynikiem tego zapytania będzie lista tabel, dla których seq_tup_read
ma największe wartości, co wskazuje na to, że najczęściej wykonywaną operacją na nich jest skanowanie sekwencyjne. Warto przypomnieć, że skanowanie sekwencyjne samo w sobie nie jest złe. Pojawia się na przykład podczas wykonywania backupów. Jednak jeśli będzie się pojawiać zbyt często, wydajność zdecydowanie spadnie.
Analizowanie logów PostgreSQL
Niezastąpioną kopalnią informacji na temat stanu systemu są również logi. Odpowiednie skonfigurowanie systemu wymaga jednak podjęcia kroków ze strony DBA, ponieważ domyślnie zbierane informacje mogą być niewystarczające do wyciągania wniosków na temat działania systemu.
Zbieranie logów i ich analiza jest szerokim tematem. W przeszłości pisaliśmy na temat jednego ze standardów logowania – syslog, który umożliwia centralizację logowania z wielu różnych serwisów. PostgreSQL zapewnia sporą elastyczność w zakresie logowania, dzięki czemu można go „spiąć” z istniejącymi w organizacji rozwiązaniami.
Najistotniejsze parametry konfiguracyjne związane z logowaniem w PostgreSQL to:
log_destination
– wskazująca na miejsce docelowe, do którego mają trafić logi. Domyślna wartość tostderr
logging_collector
– opcja pozwalająca na zbieranie danych do plików przez samego Postgresa. Domyślnie ustawiona naoff
log_directory
– katalog, do którego będą odkładane pliki logówlog_filename
– nazwa pliku, która w połączeniu z opcjąlog_truncate_on_rotation = on
pozwala na stworzenie „polityki” retencji informacji – popularnym ustawieniem jestpostgresql_%a.log
, które będzie dodawać do nazwy pliku dzień tygodnia, tworząc w ten sposób 7-dniowe okno przechowywania danych.
Więcej na ich temat można przeczytać w oficjalnej dokumentacji lub skorzystać z wyszukiwarki ustawień, która ułatwia znalezienie oraz dostosowanie pliku konfiguracyjnego postgresql.conf
do własnych potrzeb. Tutaj chciałbym jednak zwrócić jeszcze uwagę na jedno ustawienie, które w przypadku logowania do pliku jest szczególnie ważne. A mianowicie log_line_prefix
, które w zależności od wersji zmienia wartość domyślną.
Domyślne ustawienie | Wersja PostgreSQL |
---|---|
log_line_prefix = '%m [%p] ' |
12,11,10 |
log_line_prefix = '' |
9.6 i wcześniejsze |
Wynika to z faktu, że wielu administratorów często korzystało z domyślnych ustawień w myśl filozofii mówiącej, że twórcy będą wiedzieć najlepiej, co w tym przypadku powodowało często brak możliwości ustalenia przyczyny problemu, gdy ten już zdążył wystąpić.
Aktualne zachowanie zbiera informacje o aktualnym czasie z dokładnością do milisekund (%m
) oraz o ID procesu (%p
), do którego można przypisać dane zdarzenie.
Z punktu widzenia DBA istotne są również parametry pozwalające na zdefiniowanie, co tak naprawdę ma zostać zalogowane. Do dyspozycji mamy informacje o checkpointach (log_checkpoints
), połączeniach do bazy danych (log_connections
, log_disconnections
) czy informacje o długotrwałych zamkach na tabelach (log_lock_waits
, deadlocks_timeout
). O szczegółowości logu decyduje również parametr log_statement
, który pozwala na określenie, czy inne dodatkowe informacje (poza informacjami o błędach), mają zostać dopisane do logów. Do dyspozycji mamy wartości:
ddl
– dodające informacje o wszelkich poleceniachCREATE
lubALTER
mod
– poszerzające informacje o zmianach w danychall
– wszystkie informacje zostaną dodane do logu.
Chcąc dowiedzieć się czegoś o działaniu aplikacji korzystającej z bazy, często jesteśmy zmuszeni zwiększyć szczegółowość logu, płacąc za to spadkiem wydajności. Na szczęście możemy ten koszt zmniejszyć, ograniczając się jedynie do wolniejszych zapytań.
Logowanie powolnych zapytań w PostgreSQL
W celu odfiltrowania z logu szybkich zapytań, co pozwoli skupić się jedynie na tych problematycznych, możemy skorzystać z parametru log_min_duration_statement
. Jednostką w tym wypadku są milisekundy. Gdybyśmy zatem chcieli zbierać informacje o zapytaniach, które trwały dłużej niż 0,5 sekundy, parametr musiałby przyjąć następujący wygląd:
log_min_duration_statement = 500
Podsumowanie
Monitorowanie stanu zdrowia bazy danych to temat rzeka. Można by tutaj również wspomnieć o odpowiedniej higienie podczas użytkowania bazy oraz o standardowych pracach konserwacyjnych z nią związanych. Dla zainteresowanych polecam nasze szkolenia z administracji PostgreSQL,