
Pozbywanie się bezczynnych transakcji w PostgreSQL – jak to zrobić?

Jedną ze zmór administratorów baz danych są sesje, które rozpoczęły blok transakcyjny poleceniem BEGIN, ewentualnie wykonały jakieś operacje i „zamarły”. Mogą stanowić sporą przeszkodę w sprawnej pracy klastra – alokują zasoby, mogą być przyczyną problemów z lokowaniem tabel (LOCK TABLE), ograniczają pulę połączeń, opóźniają replikację i procesy VACUUM.
Jedną ze zmór administratorów baz danych są sesje, które rozpoczęły blok transakcyjny poleceniem BEGIN, ewentualnie wykonały jakieś operacje i „zamarły”. Mogą stanowić sporą przeszkodę w sprawnej pracy klastra PostgreSQL – alokują zasoby, mogą być przyczyną problemów z lokowaniem tabel (LOCK TABLE), ograniczają pulę połączeń, opóźniają replikację i procesy VACUUM.
Docelowym rozwiązaniem jest naprawienie problemu w kodzie oprogramowania, ale ani decyzja o tym, ani tym bardziej jej realizacja nie leży zazwyczaj w kompetencjach administratora. Ponadto proces taki zwykle trochę trwa, tymczasem trzeba umożliwić stabilne działanie innym aplikacjom.
Wraz z wypuszczeniem wersji 9.2 przechwytywanie problematycznych sesji zostało znacznie ułatwione – widok pg_stat_activity
wprost zaczął je wyróżniać.
Status transakcji w PostgreSQL
Sprawdzenie statusu transakcji może zostać wykonane następującym zapytaniem:
([email protected]:5432) 08:15:04 [postgres] # \x Rozszerzone wyświetlanie jest włączone. ([email protected]:5432) 08:15:09 [postgres] # SELECT pid , datname , usename , state , wait_event_type , wait_event , query, query_start FROM pg_stat_activity WHERE backend_type = 'client backend' AND usename !='administartor' ORDER BY usename; -[ RECORD 1 ]---+---------------------------------------------- pid | 8074 datname | how_to usename | app1 state | idle in transaction (aborted) wait_event_type | Client wait_event | ClientRead query | SELECT 1/0; state_change | 2018-02-05 08:14:29.943037+00 -[ RECORD 2 ]---+---------------------------------------------- pid | 8079 datname | how_to usename | app2 state | idle in transaction wait_event_type | Client wait_event | ClientRead query | INSERT INTO very_important_table VALUES (10); state_change | 2018-02-05 08:14:24.135201+00 -[ RECORD 3 ]---+---------------------------------------------- pid | 8262 datname | how_to usename | app3 state | active wait_event_type | Timeout wait_event | PgSleep query | SELECT pg_sleep(28800); state_change | 2018-02-05 08:13:30.347907+00 Czas: 1,291 ms
Warunkiem uzyskania szczegółowych informacji o tym, co dzieje się na serwerze, jest ustawienie parametru track_activities
w konfiguracji na true
. Poszczególne kolumny oznaczają:
pid
– identyfikator procesu,datname
– nazwa bazy danych,usename
– nazwa użytkownika,state
– informacja interesująca nas najbardziej w tym wypadku, czyli stan transakcji 1:
–idle in transaction
— aktualnie bezczynny blok transakcyjny przeprowadzony dotychczas poprawnie,
–idle in transaction (aborted)
– również bezczynny blok transakcyjny jednak zawierający w sobie błąd,
–active
– aktywna transakcja,wait_event_type
– rodzaj zdarzenia, na który proces oczekuje 1:
–Client
– działanie klienta,
–Timeout
– interwał czasowy, po którym proces wygaśnie,wait_event
– konkretne zdarzenie, na które proces oczekuje 1:
–ClientRead
– odczytanie danych od klienta,
–PgSleep
– zakończenie procesu pg_sleep,query
– w wypadku aktywnej sesji zobaczymy tu aktualnie wykonujące się zapytanie, w wypadku nieaktywnej – ostatnie wykonane,state_change
– timestamp ostatniej zmiany stanu sesji.
Jak radzić sobie z bezczynnymi transakcjami?
Najłatwiej poradzić sobie z transakcjami w stanie idle in transaction (aborted) – nic nie stoi na przeszkodzie, aby tak często, jak tylko to możliwe bez nadmiernego obciążania serwera wykonywać zapytanie:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction (aborted)';
Możemy robić to zupełnie bez obaw – wiemy bowiem, że żadne zmiany wprowadzone w takich transakcjach nie zostaną w rzeczywistości zapisane do bazy.
Więcej szans możemy dać transakcjom „poprawnym” niezawierającym żadnego błędu – na takie możemy trochę dłużej „poczekać”. W PostgreSQL 9.6 powił się runtime’owy parametr idle_in_transaction_session_timeout
, który przyjmuje wartość w milisekundach, po których sesja w stanie idle in trasaction lub idle in transaction (aborted) będzie przerywana. Parametr ten można skonfigurować zarówno w pliku konfiguracyjnym, jak i poleceniem SET
po nawiązaniu połączenia do bazy:
([email protected]:5432) 12:12:08 [how_to] > SET idle_in_transaction_session_timeout = '1s'; BEGIN; SET Czas: 0,308 ms BEGIN Czas: 0,188 ms ([email protected]:5432) 12:12:14 [how_to] *> SELECT 1; KATASTROFALNY: zakończono połączenie na skutek przekroczenia czasu bezczynności podczas transakcji serwer zamknął połączenie niespodziewanie Oznacza to prawdopodobnie iż serwer zakończył działanie niepoprawnie przed lub podczas przetwarzania zapytania. Połączenie z serwerem zostało przerwane. Próba resetu: Udane. Czas: 3,754 ms
W logach tekstowych takie zdarzenie zostanie odpowiednio odnotowane:
app1@how_to 2018-02-05 12:12:15.714 UTC [12651] KATASTROFALNY: zakończono połączenie na skutek przekroczenia czasu bezczynności podczas transakcji app1@how_to 2018-02-05 12:12:15.714 UTC [12651] DZIENNIK: rozłączenie: czas sesji: 1:38:20.813 użytkownik=app1 baza=how_to host=10.0.0.9 port=45480
Funkcjonalność automatycznego kończenia sesji w stanie idle_in_transaction
znana jest administratorom, którzy mieli styczność z oprogramowaniem typu connection pooler – zdarza się jednak, że jest ona tam wadliwie zaimplementowana. Takim kłopotliwym przykładem było narzędzie pgbouncer. Zdarzało się mu bowiem zamykać aktywne sesje z zapytaniami, które wykonują się dłużej niż określona długość oczekiwania. Opis można zobaczyć tutaj. Ten problem nie dotyczy natywnego rozwiązania:
([email protected]:5432) 12:19:06 [how_to] > SET idle_in_transaction_session_timeout = '1s'; BEGIN; SELECT pg_sleep(2); COMMIT; SET Czas: 0,385 ms BEGIN Czas: 0,159 ms pg_sleep ---------- (1 wiersz) Czas: 2002,224 ms (00:02,002) COMMIT Czas: 0,315 ms
1 pole to może przyjąć więcej wartości, szczegółowy opis znajdziemy w dokumentacji.
Źródła:
https://www.postgresql.org/docs/10/static/monitoring-stats.html
https://www.depesz.com/2016/03/22/waiting-for-9-6-add-idle_in_transaction_session_timeout/