Jak sprawdzić procesy i blokady? Jakie zapytania się aktualnie wykonują i od jakiego czasu? Odpowiedzi na takie i inne pytania znajdziecie poniżej.
Procesy
Ze względu na różne wersje postgres'a i rózne wersje systemów operacyjnych, na których postgresql jest zainstalowany poniższe zapytania mogą różnić się nazewnictwem kolumn. Zwłaszcza tabela pg_stat_activity, w której są przechowywane informacje o procesach działających w bazie danych. Każdy wiersz w tabeli odpowiada jednemu procesowi w bazie danych. Tak więc jeżeli któreś z poniższych zapytań zwraca błąd to proponuję sprawdzić nazwy kolumn. Poniższe zapytanie wyświetla wszystkie procesy zachodzące w bazie danych. Z wyniku zapytania wykluczyłem procesy IDLE, gdyż w większości przypadków są to procesy nie obciążające systemu, często czekające w kolejce na swoją kolej lub takie które z jakiejś przyczyny nie zostały zakończone. Jeżeli takie procesy występują pomimo braku aktywności na serwerze bazodanowym to powinniśmy zainteresować się tą sytuacją, gdyż to oznacza, że coś działa nie tak jak powinno.
SELECT pid, query_start, query
FROM pg_stat_activity
WHERE query != '<IDLE>'
ORDER BY query_start;
pid | query_start | query
------+-------------------------------+---------------------------------
1500 | 2016-04-14 20:39:05.360473+02 | SELECT pid, query_start, query +
| | FROM pg_stat_activity +
| | WHERE query != '<IDLE>' +
| | ORDER BY query_start;
(1 wiersz)
Pięć najdłużej wykonujących się zapytań
SELECT now()-query_start AS running_for, query FROM pg_stat_activity ORDER BY 1 DESC LIMIT 5; running_for | query -------------+----------------------------------------------------------------------------------------------- 00:00:00 | SELECT now()-query_start AS running_for, query FROM pg_stat_activity ORDER BY 1 desc limit 5; (1 wiersz)
Postgres w połączeniu z linuxem daje nam bardzo duże możliwości tworzenia prostych narzędzi skryptowych do obsługi bazy. Takie narzędzia mogą powstać po stronie bazy w PL/pgSQL, a moga i być tworzone w shellu. Przykładem może być powyższe zapytanie, wrzucone do skryptu lub jako polecenie wykonywane z poziomu crontab'a. Daje nam to możliwość prowadzenia regularnych statystyk.
while psql -qt -c "SELECT now()-query_start AS running_for, query FROM pg_stat_activity" >> querry_stats.txt; do sleep 1; done
Sprawdzenie PID'a w bazie
SELECT query
FROM pg_stat_activity
WHERE pid = 1500;
query
------------------------------------------------------
SELECT query FROM pg_stat_activity WHERE pid = 1500;
(1 wiersz)
Przerwanie zapytania - jeżeli mamy jakiś proces który musimy z jakiegos powodu przerwać. Tutaj w miejsce 1500 wstawiamy numer PID'a który chcemy przerwać.
SELECT pg_cancel_backend(1500);
Blokady na bazie
SELECT datname, locktype, relation::regclass, mode, transactionid AS tid, virtualtransaction AS vtid, pid, granted FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db ON db.oid = l.database WHERE true --(db.datname = 'dbname' OR db.datname IS NULL) AND NOT pid = pg_backend_pid() ORDER BY 3 ASC;
W postgres'ie mamy także dostęp do tabeli pg_locks, która zapewnia informacje o lock'ach zakładanych przez transakcje wykonywane na serwerze bazodanowym. Informacje z tej tabeli możemy połaczyć z naszą tabelą trzymającą informacje o aktywności.
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa ON pl.pid = psa.pid;
Ok, mamy te wszystkie tabele. A jak to wszystko wyświetlić w bardziej przyjazny sposób? Poniżej odpowiedź.
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;
blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement
-------------+--------------+--------------+---------------+-------------------
(0 wierszy)
Można oczywiście robić różnorodne wariacje powyższych zapytań.
SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
ka.query AS blocking_statement,
now() - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.query AS blocked_statement,
now() - a.query_start AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid;