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/

blank 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ść.