Podstawowe komendy monitoringu w PostgreSQL — pg_stat_activity i pg_stat_user_tables

Monitoring bazy danych bez pg_stat_activity i pg_stat_user_tables to jakbyś sprawdzał zdrowie tylko patrząc na temperaturę. Nie ogarniesz, gdzie jest problem, jeśli patrzysz tylko na ogólny obraz. Te dwa kluczowe narzędzia PostgreSQL pozwolą ci nie tylko obserwować, ale też aktywnie analizować, co się dzieje w twojej bazie.

Czym jest pg_stat_activity?

pg_stat_activity to systemowy widok PostgreSQL, który pokazuje info o wszystkich połączeniach do twojej bazy danych. Odpowiada na pytania: kto jest podłączony do bazy, jakie zapytania są teraz wykonywane i które połączenia "wiszą" w stanie bezczynności. To twoje narzędzie do analizy aktualnej aktywności na serwerze.

Rozkminmy podstawowe pola dostępne w pg_stat_activity. Pole datname zawiera nazwę bazy, do której klient jest podłączony, a usename pokazuje nazwę usera, który się połączył. application_name wskazuje nazwę aplikacji używającej połączenia, client_addr zawiera IP klienta podłączonego do serwera. backend_start pokazuje czas, kiedy klient się połączył, state odzwierciedla aktualny stan połączenia (active, idle, idle in transaction), a query zawiera zapytanie, które jest lub było ostatnio wykonywane.

Przykład 1: podgląd wszystkich aktywnych połączeń

Żeby zobaczyć aktywne połączenia, odpal takie zapytanie:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

Zwróć uwagę na pole query. Pokazuje ono zapytania, które są teraz wykonywane. Jeśli jakieś zapytanie trwa za długo, coś może być z nim nie tak.

Przykład 2: Analiza stanu transakcji

Czasem połączenia "wiszą" w stanie idle in transaction. To znaczy, że transakcja została rozpoczęta, ale nie zakończona, co może prowadzić do blokad.

SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Jak to ogarnąć? Jeśli znajdziesz "wiszącą" transakcję, możesz ją zakończyć komendą:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Niektórzy devsi za bardzo się tym jarają. Polecam najpierw zapytać ekipę, czy można "zabić" proces. Ups, sorry, miałem na myśli — zakończyć połączenie.

Monitoring użycia tabel: widok pg_stat_user_tables

Jeśli pg_stat_activity pozwala śledzić połączenia, to pg_stat_user_tables opowiada o wydajności tabel. Dzięki niemu dowiesz się: jak często dane z tabel są czytane lub zapisywane, które tabele są najczęściej używane i gdzie mogą być bottlenecks z wydajnością.

Oto podstawowe pola pg_stat_user_tables, które pomogą ci analizować tabele. relname to nazwa tabeli, seq_scan pokazuje liczbę sekwencyjnych skanów tabeli, idx_scan — liczbę skanów z użyciem indeksu. n_tup_ins to liczba wierszy wstawionych do tabeli, n_tup_upd — liczba wierszy zaktualizowanych, a n_tup_del — liczba wierszy usuniętych z tabeli.

Przykład 1: porównanie użycia indeksów i sekwencyjnych skanów

Jeśli indeks jest używany za rzadko (idx_scan blisko zera), pewnie da się zoptymalizować zapytania do tej tabeli.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Przykład wyniku:

Jeśli widzisz, że tabela orders ma mega dużo sekwencyjnych skanów (seq_scan), pomyśl o dodaniu indeksu. Wyobraź sobie tabelę orders z 3500 sekwencyjnymi skanami i tylko 100 skanami po indeksie, a tabela employees ma 50 sekwencyjnych i 1000 po indeksie — to ewidentny sygnał do optymalizacji.

Przykład 2: analiza liczby operacji na tabelach

Żeby zobaczyć, jak "żywe" są dane w tabelach, sprawdź info o wstawionych, zaktualizowanych i usuniętych wierszach:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

Co możesz z tego wyciągnąć? Tabele z dużą liczbą insertów (n_tup_ins) i delete'ów (n_tup_del) mogą być hot spotami w twojej bazie. To znaczy, że ich wydajność zasługuje na szczególną uwagę.

Praktyczne użycie komend do analizy wydajności: łączymy dane pg_stat_activity i pg_stat_user_tables

Kiedy analizujesz wydajność bazy, możesz połączyć dane z dwóch źródeł. Najpierw wyłap długie zapytania przez pg_stat_activity, potem sprawdź, które tabele są przez nie używane, przez pg_stat_user_tables. Jeśli zapytania długo mielą na tabelach z wysokim seq_scan, spróbuj zoptymalizować zapytania albo dodać indeks.

Przykład zapytania:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';