Co się dzieje w bazie teraz - pg_stat_activity w praktyce

PostgreSQL

Co się dzieje w bazie teraz - pg_stat_activity w praktyce

W skrócie

Kiedy baza działa wolno tu i teraz, pierwszym miejscem, do którego zaglądamy, jest widok pg_stat_activity. Pokazuje on każde aktywne połączenie do serwera: kto się połączył, jakie zapytanie wykonuje, od jak dawna trwa i na co ewentualnie czeka. To odpowiednik listy procesów dla bazy danych. Nauka czytania tego widoku to jedna z najbardziej zwrotnych umiejętności administratora PostgreSQL, bo pozwala rozwiązać większość nagłych problemów wydajnościowych bez zgadywania.

Jak to wygląda w praktyce

Typowe sytuacje to aplikacja, która przestaje odpowiadać, rosnąca liczba połączeń aż do wyczerpania limitu oraz operacja administracyjna, która wisi w nieskończoność. Bez widoku aktywności widzimy tylko skutek: użytkownicy zgłaszają, że serwis nie działa. Po zajrzeniu do pg_stat_activity obraz robi się jasny - jedna sesja trzyma otwartą transakcję od godziny, inne sesje ustawiają się w kolejce po blokadę, a osobne zapytanie skanuje ogromną tabelę od kilkunastu minut. Często winowajcą jest transakcja w stanie idle in transaction, czyli otwarta, ale nieaktywna - aplikacja zapomniała ją zamknąć, a ona blokuje sprzątanie i trzyma blokady.

Dlaczego tak się dzieje

Każde połączenie do PostgreSQL to osobny proces, który ma swój stan. Widok pg_stat_activity udostępnia ten stan w kolumnach. Najważniejsze z nich to state, które mówi, czy sesja aktywnie liczy zapytanie (active), czeka bezczynnie (idle), czy trzyma otwartą transakcję bez pracy (idle in transaction). Kolumna query pokazuje tekst ostatniego zapytania, a różnica między query_start a chwilą obecną to czas jego trwania. Kolumny wait_event_type i wait_event mówią, na co sesja czeka - jeśli to Lock, to znaczy, że stoi za inną sesją trzymającą blokadę. Problemy biorą się stąd, że długa transakcja albo zapomniane idle in transaction blokuje innych i wstrzymuje VACUUM, co z czasem rozdyma bazę.

Jak to rozwiązać krok po kroku

1. Zacznij od przeglądu długo trwających zapytań: SELECT pid, usename, state, now() - query_start AS czas, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY czas DESC;.
2. Wypatrz zawieszone transakcje: sesje ze stanem idle in transaction trwające długo są częstym źródłem blokad i rozdmuchania tabel. Znajdziesz je, filtrując po state = 'idle in transaction'.
3. Sprawdź, kto na kogo czeka. Kolumny wait_event_type i wait_event pokażą sesje stojące na blokadzie; do dokładnego drzewa blokad zajrzyj dodatkowo do pg_locks.
4. Jeśli musisz przerwać zapytanie, ale zachować połączenie, użyj łagodnego anulowania: SELECT pg_cancel_backend(pid); - przerywa bieżące zapytanie danej sesji.
5. Jeśli sesja nie reaguje albo trzeba ją zamknąć całkowicie, użyj mocniejszego: SELECT pg_terminate_backend(pid); - kończy całe połączenie. Używaj go świadomie, bo aplikacja zobaczy zerwane połączenie.
6. Zabezpiecz się na przyszłość: ustaw idle_in_transaction_session_timeout oraz statement_timeout, aby baza sama zamykała zapomniane transakcje i zbyt długie zapytania.

Jak sprawdzić, że zadziałało

Po interwencji ponów zapytanie do pg_stat_activity - problematyczny pid powinien zniknąć albo wrócić do stanu idle. Sesje, które wcześniej czekały na blokadę, powinny przejść w stan active i zakończyć pracę, a ich wait_event przestaje wskazywać Lock. Sprawdź też, czy liczba połączeń wróciła do normy: SELECT count(*) FROM pg_stat_activity;. Jeśli ustawiłeś limity czasowe, kolejne zawieszone transakcje będą zamykane automatycznie, co potwierdzi brak długich wpisów idle in transaction przy następnym podglądzie.

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

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5