Monitorowanie kondycji PostgreSQL

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 bazy
  • xact_commit oraz xact_rollback, która pozwala ustalić stosunek zatwierdzonych oraz wycofywanych transakcji
  • blks_hit oraz blks_read określających stopień skuteczności zapytań do operowania na danych znajdujących się w cache
  • tup_*, 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.

Analiza statystyk systemowych w czasie z EuroDB

Naturą danych zbieranych w prezentowanych widokach jest ich zmienność. Nie jesteśmy w stanie zapytać o ich stan z przeszłości, jeżeli sami nie zaplanujemy jakiejś formy ich przechowywania. Naprzeciw tym potrzebom wychodzi moduł EuroDB do analizy statystyk. Przy jego użyciu możemy zauważyć niepokojące trendy mające miejsce przy wprowadzaniu zmian w bazie, czy też wynikające ze wzrostu liczby użytkowników.

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ść to stderr
  • logging_collector – opcja pozwalająca na zbieranie danych do plików przez samego Postgresa. Domyślnie ustawiona na off
  • log_directory – katalog, do którego będą odkładane pliki logów
  • log_filename – nazwa pliku, która w połączeniu z opcją log_truncate_on_rotation = on pozwala na stworzenie „polityki” retencji informacji – popularnym ustawieniem jest postgresql_%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 poleceniach CREATE lub ALTER
  • mod – poszerzające informacje o zmianach w danych
  • all – 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

Analiza logów przy użyciu modułu EuroDB

W dużych systemach bazodanowych analiza logów to syzyfowa praca. Często odkładana na później, aż w końcu w momencie wystąpienia awarii staje się niezbędna. Trudno jednak przebrnąć przez miliony linii w poszukiwaniu przyczyny błędów, czując jednocześnie na sobie presję wynikającą z sytuacji kryzysowej. Rozwiązaniem czasochłonności analizy logów jest moduł EuroDB, który prezentuje dane w zagregowanej formie graficznej. Jego innym atutem jest możliwość tworzenia raportów inkrementalnych, co pozwala na analizę podobnych okresów między sobą.

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, na których poruszamy tę tematykę w dużo szerszym zakresie. Dziękuję za przeczytanie tego tekstu oraz zachęcam do subskrypcji newslettera EuroLinux, w którym co miesiąc można znaleźć podobne teksty związane z bazami danych oraz Open Source’ową stroną mocy świata IT.

Autorzy

Artykuły na blogu są pisane przez osoby z zespołu EuroLinux. 80% treści zawdzięczamy naszym developerom, pozostałą część przygotowuje dział sprzedaży lub marketingu. Dokładamy starań, żeby treści były jak najlepsze merytorycznie i językowo, ale nie jesteśmy nieomylni. Jeśli zauważysz coś wartego poprawienia lub wyjaśnienia, będziemy wdzięczni za wiadomość.