sql - pg_stat_activity - monitorear rendimiento postgres

¿Cómo detectar la consulta que mantiene el bloqueo en Postgres?

Question

Quiero rastrear bloqueos mutuos en postgres constantemente.

Me topé con el artículo de Locks Monitoring y traté de ejecutar la siguiente consulta:

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;

Desafortunadamente, nunca devuelve un conjunto de resultados no vacío. Si simplifico consulta dada a la siguiente forma:

SELECT bl.pid     AS blocked_pid,
     a.usename  AS blocked_user,
     a.query    AS blocked_statement
FROM  pg_catalog.pg_locks         bl
 JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
WHERE NOT bl.granted;

luego devuelve las consultas que están esperando para adquirir un bloqueo. Pero no puedo cambiarlo para que pueda devolver tanto las consultas bloqueadas como las bloqueadas.

¿Algunas ideas?

Answer 1

De este excelente artículo sobre bloqueos de consulta en Postgres , uno puede obtener consultas bloqueadas y consultas bloqueadas y su información de la siguiente consulta.

CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);

SELECT * from lock_monitor;

Como la consulta es larga pero útil, el autor del artículo ha creado una vista para simplificar su uso.

Answer 2

Desde 9.6 esto es mucho más fácil ya que introdujo la función pg_blocking_pids() para encontrar las sesiones que están bloqueando otra sesión.

Así que puedes usar algo como esto:

select pid, 
       usename, 
       pg_blocking_pids(pid) as blocked_by, 
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

Answer 3

Una cosa que encuentro que a menudo falta en estos es la capacidad de buscar bloqueos de fila. Al menos en las bases de datos más grandes en las que he trabajado, los bloqueos de fila no se muestran en pg_locks (si lo fueran, pg_locks sería mucho, mucho más grande y no hay un tipo de datos real para mostrar la fila bloqueada en esa vista correctamente).

No sé si hay una solución simple para esto, pero generalmente lo que hago es mirar la tabla donde está esperando el bloqueo y buscar filas donde el xmax sea menor que el ID de transacción presente allí. Eso generalmente me da un lugar para comenzar, pero es un poco práctico y no es fácil de automatizar.

Tenga en cuenta que muestra las escrituras no confirmadas en filas en esas tablas. Una vez confirmadas, las filas no son visibles en la instantánea actual. Pero para mesas grandes, eso es un dolor.