Jak podejrzeć aktywne zapytania i blokady w pg_stat_activity

PostgreSQL

Jak podejrzeć aktywne zapytania i blokady w pg_stat_activity

W skrócie

Widok pg_stat_activity to pierwsze miejsce, do którego zaglądamy, gdy baza "muli" albo coś wisi. Pokażemy Ci, jak z niego czytać stan sesji i jak w kilka sekund znaleźć zapytanie, które blokuje resztę.

Jak to wygląda w praktyce

Użytkownicy zgłaszają, że zapis do jednej tabeli "się zawiesza", choć serwer nie jest przeciążony. W logu widać rosnący czas oczekiwania na blokady, a pojedyncze UPDATE lub ALTER TABLE nie wracają. Nie wiesz jednak, która sesja trzyma blokadę i jak długo. Czasem winowajcą jest długo otwarta transakcja (idle in transaction), czasem ciężki raport, a czasem migracja schematu, która czeka na blokadę na poziomie tabeli. Zanim cokolwiek zabijesz, musisz zobaczyć pełny obraz: kto jest aktywny, kto czeka, i na kogo.

Dlaczego tak się dzieje

PostgreSQL steruje współbieżnym dostępem za pomocą blokad. Większość z nich jest zdejmowana od razu i nie widać ich gołym okiem, ale gdy dwie transakcje chcą tego samego zasobu w niekompatybilnych trybach, jedna musi poczekać. Widok pg_stat_activity pokazuje po jednym wierszu na proces serwera i mówi, w jakim jest stanie (active, idle, idle in transaction), na co czeka (wait_event_type i wait_event), od kiedy (state_change, query_start, xact_start) oraz jakie zapytanie wykonuje. Kluczowe jest zrozumienie, że blokada sama w sobie nie jest awarią - awarią jest dopiero blokada trzymana zbyt długo przez transakcję, która powinna była się już zakończyć.

Jak to rozwiązać krok po kroku

1. Zobacz, kto jest aktywny i jak długo: SELECT pid, usename, state, wait_event_type, wait_event, now() - query_start AS trwa, left(query, 60) AS zapytanie FROM pg_stat_activity WHERE state <> 'idle' ORDER BY query_start;
2. Wypatrz sesje w stanie idle in transaction - to najczęstsza przyczyna blokad trzymanych bez powodu. Sprawdź ich xact_start, żeby ocenić, jak długo transakcja jest otwarta.
3. Rozwikłaj łańcuch blokad. Dla każdej czekającej sesji sprawdź, kto ją blokuje: SELECT pid, pg_blocking_pids(pid) AS blokuja, wait_event, left(query,60) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;. Kolumna blokuja to lista PID-ów trzymających blokadę.
4. Jeśli chcesz zobaczyć konkretne blokady (tryb, obiekt, czy przyznana), połącz pg_locks z pg_stat_activity: SELECT l.pid, l.locktype, l.mode, l.granted, a.state FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE NOT l.granted; - wiersze z granted = false to sesje, które czekają.
5. Ustal winowajcę u źródła łańcucha - proces, który blokuje innych, a sam na nic nie czeka. To jego trzeba obsłużyć w pierwszej kolejności.
6. Zareaguj adekwatnie: jeśli winowajca tylko wykonuje długie zapytanie, anuluj je przez pg_cancel_backend(pid); jeśli wisi w otwartej transakcji, zakończ sesję przez pg_terminate_backend(pid).
7. Zabezpiecz się na przyszłość: ustaw idle_in_transaction_session_timeout, rozważ lock_timeout dla migracji i statement_timeout dla ról aplikacyjnych.

Jak sprawdzić, że zadziałało

Po interwencji uruchom ponownie zapytanie z pg_blocking_pids - dla wcześniej czekających sesji lista blokujących powinna być już pusta, a wcześniej zablokowane polecenie ruszy dalej. W pg_locks nie powinno być już Twoich wierszy z granted = false na spornym obiekcie. Warto też sprawdzić pg_stat_activity pod kątem tego, czy problematyczna sesja zmieniła stan (przy anulowaniu) albo zniknęła z listy (przy terminowaniu). Jeżeli blokady wracają cyklicznie, to znak, że przyczyna leży w kodzie aplikacji - szukaj miejsca, które otwiera transakcję i nie zamyka jej odpowiednio szybko.

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