Continuación del artículo ««.
En este artículo se examinará y se mostrarán consultas y ejemplos específicos sobre la información útil que se puede obtener a través de la vista de pg_locks.
Advertencia.
Debido a la novedad del tema y a que el período de prueba no ha finalizado, el artículo puede contener errores. Se agradecen y se esperan críticas y comentarios.
Datos de entrada
Vista de pg_locks
archive_locking
CREAR TABLA archive_locking
( timepoint timestamp sin zona horaria ,
locktype text ,
relacion oid ,
mode text ,
tid xid ,
vtid text ,
pid entero ,
blocking_pids entero[] ,
granted boolean ,
queryid bigint
);Fundamentalmente, la tabla es análoga a la tabla archive_pg_stat_activity, descrita más detalladamente aquí — y aquí —
Para llenar la columna queryid se utiliza la función
update_history_locking_by_queryid
--update_history_locking_by_queryid.sql
CREAR O REEMPLAZAR FUNCIÓN update_history_locking_by_queryid() DEVUELVE boolean AS $$
DECLARE
resultado boolean ;
minuto_actual doble precisión ;
minuto_inicio entero ;
minuto_fin entero ;
periodo_inicio timestamp sin zona horaria ;
periodo_fin timestamp sin zona horaria ;
lock_rec registro ;
endpoint_rec registro ;
diferencia_hora_actual doble precisión ;
BEGIN
RAISE NOTICE '***update_history_locking_by_queryid';
resultado = TRUE ;
minuto_actual = extract ( minuto desde ahora() );
SELECT * FROM endpoint WHERE is_need_monitoring
INTO endpoint_rec ;
diferencia_hora_actual = endpoint_rec.hour_diff ;
IF minuto_actual < 5
THEN
RAISE NOTICE 'El tiempo actual es menos de 5 minutos.';
periodo_inicio = date_trunc('hour',ahora()) + (diferencia_hora_actual * interval '1 hora');
periodo_fin = periodo_inicio - interval '5 minutos' ;
ELSE
minuto_fin = extract ( minuto desde ahora() ) / 5 ;
minuto_inicio = minuto_fin - 1 ;
periodo_inicio = date_trunc('hour',ahora()) + interval '1 minuto'*minuto_inicio*5+(diferencia_hora_actual * interval '1 hora');
periodo_fin = date_trunc('hour',ahora()) + interval '1 minuto'*minuto_fin*5+(diferencia_hora_actual * interval '1 hora') ;
END IF ;
RAISE NOTICE 'periodo_inicio = %', periodo_inicio;
RAISE NOTICE 'periodo_fin = %', periodo_fin;
FOR lock_rec IN
WITH act_queryid AS
(
SELECT
pid ,
timepoint ,
query_start AS started ,
MAX(timepoint) OVER (PARTITION BY pid , query_start ) AS finished ,
queryid
FROM
activity_hist.history_pg_stat_activity
WHERE
timepoint ENTRE periodo_inicio y
periodo_fin
GROUP BY
pid ,
timepoint ,
query_start ,
queryid
),
lock_pids AS
(
SELECT
hl.pid ,
hl.locktype ,
hl.mode ,
hl.timepoint ,
MIN ( timepoint ) OVER (PARTITION BY pid , locktype ,mode ) como started
FROM
activity_hist.history_locking hl
WHERE
hl.timepoint entre periodo_inicio y
periodo_fin
GROUP BY
hl.pid ,
hl.locktype ,
hl.mode ,
hl.timepoint
)
SELECT
lp.pid ,
lp.locktype ,
lp.mode ,
lp.timepoint ,
aq.queryid
FROM lock_pids lp LEFT OUTER JOIN act_queryid aq ON ( lp.pid = aq.pid AND lp.started ENTRE aq.started Y aq.finished )
WHERE aq.queryid NO ES NULO
GROUP BY
lp.pid ,
lp.locktype ,
lp.mode ,
lp.timepoint ,
aq.queryid
LOOP
UPDATE activity_hist.history_locking SET queryid = lock_rec.queryid
WHERE pid = lock_rec.pid AND locktype = lock_rec.locktype AND mode = lock_rec.mode AND timepoint = lock_rec.timepoint ;
END LOOP;
DEVOLVER resultado ;
END
$$ LENGUAJE plpgsql;Explicación: El valor de la columna queryid se actualiza en la tabla history_locking, y luego, al crear una nueva sección para la tabla archive_locking, el valor se guardará en los valores históricos.
Datos de salida
Información general sobre los procesos en general.
ESPERANDO BLOQUEOS POR TIPOS DE BLOQUEO
Consulta
CON
t COMO
(
SELECCIONAR
tipobloqueo,
modo,
conteo(*) como total
DE
activity_hist.archive_locking
DÓNDE
tiempos entre pg_stat_history_begin+(current_hour_diff * intervalo '1 hora') Y pg_stat_history_end+(current_hour_diff * intervalo '1 hora') Y
NO concedido
AGRUPE POR
tipobloqueo,
modo
)
SELECCIONAR
tipobloqueo,
modo,
total * intervalo '1 segundo' como duración
DE t
ORDENAR POR 3 DESC Ejemplo
| ESPERANDO BLOQUEOS POR TIPOS DE BLOQUEO +--------------------+------------------------------+-------------------- | tipobloqueo| modo| duración +--------------------+------------------------------+-------------------- | transactionid| ShareLock| 19:39:26 | tuple| AccessExclusiveLock| 00:03:35 +--------------------+------------------------------+--------------------
TOMAS DE BLOQUEOS POR TIPOS DE BLOQUEO
Consulta
CON
t COMO
(
SELECCIONAR
tipobloqueo,
modo,
conteo(*) como total
DE
activity_hist.archive_locking
DÓNDE
tiempos entre pg_stat_history_begin+(current_hour_diff * intervalo '1 hora') Y pg_stat_history_end+(current_hour_diff * intervalo '1 hora') Y
concedido
AGRUPE POR
tipobloqueo,
modo
)
SELECCIONAR
tipobloqueo,
modo,
total * intervalo '1 segundo' como duración
DE t
ORDENAR POR 3 DESC Ejemplo
| TOMAS DE BLOQUEOS POR TIPOS DE BLOQUEO +--------------------+------------------------------+-------------------- | tipobloqueo| modo| duración +--------------------+------------------------------+-------------------- | relación| RowExclusiveLock| 51:11:10 | virtualxid| ExclusiveLock| 48:10:43 | transactionid| ExclusiveLock| 44:24:53 | relación| AccessShareLock| 20:06:13 | tuple| AccessExclusiveLock| 17:58:47 | tuple| ExclusiveLock| 01:40:41 | relación| ShareUpdateExclusiveLock| 00:26:41 | objeto| RowExclusiveLock| 00:00:01 | transactionid| ShareLock| 00:00:01 | extender| ExclusiveLock| 00:00:01 +--------------------+------------------------------+--------------------
Información detallada sobre solicitudes con queryid específicas.
ESPERANDO BLOQUEOS POR TIPOS DE BLOQUEO POR QUERYID
Consulta
CON
lt COMO
(
SELECCIONAR
pid,
tipobloqueo,
modo,
tiempos,
queryid,
blocking_pids,
MIN(tiempos) SOBRE (PARTICIONAR POR pid, tipobloqueo, modo) como iniciado
DE
activity_hist.archive_locking
DÓNDE
tiempos entre pg_stat_history_begin+(current_hour_diff * intervalo '1 hora') Y
pg_stat_history_end+(current_hour_diff * intervalo '1 hora') Y
NO concedido Y
queryid NO ES NULO
AGRUPE POR
pid,
tipobloqueo,
modo,
tiempos,
queryid,
blocking_pids
)
SELECCIONAR
lt.pid,
lt.tipobloqueo,
lt.modo,
lt.iniciado,
lt.queryid,
lt.blocking_pids,
CONTAR(*) * intervalo '1 segundo' como duración
DE lt
AGRUPE POR
lt.pid,
lt.tipobloqueo,
lt.modo,
lt.iniciado,
lt.queryid,
lt.blocking_pids
ORDENAR POR 4Ejemplo
| ESPERANDO BLOQUEOS POR TIPOS DE BLOQUEO POR QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| pid| locktype| mode| started| queryid| blocking_pids| duration
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| 11288| transactionid| ShareLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34
| 11626| transactionid| ShareLock| 2019-09-17 10:00:21.380921| 389015618226997618| {12380}| 00:00:29
| 11626| transactionid| ShareLock| 2019-09-17 10:00:21.380921| 389015618226997618| {11092}| 00:03:25
| 11626| transactionid| ShareLock| 2019-09-17 10:00:21.380921| 389015618226997618| {12213}| 00:01:55
| 11626| transactionid| ShareLock| 2019-09-17 10:00:21.380921| 389015618226997618| {12751}| 00:00:01
| 11629| transactionid| ShareLock| 2019-09-17 10:00:24.331935| 389015618226997618| {11092}| 00:03:22
| 11629| transactionid| ShareLock| 2019-09-17 10:00:24.331935| 389015618226997618| {12007}| 00:00:01
| 12007| transactionid| ShareLock| 2019-09-17 10:05:03.327933| 389015618226997618| {11629}| 00:00:13
| 12007| transactionid| ShareLock| 2019-09-17 10:05:03.327933| 389015618226997618| {11092}| 00:01:10
| 12007| transactionid| ShareLock| 2019-09-17 10:05:03.327933| 389015618226997618| {11288}| 00:00:05
| 12213| transactionid| ShareLock| 2019-09-17 10:06:07.328019| 389015618226997618| {12007}| 00:00:10BLOQUEANDO POR TIPOS DE BLOQUEO POR QUERYID
Consulta
CON
lt COMO
(
SELECCIONAR
pid ,
locktype ,
mode ,
timepoint ,
queryid ,
blocking_pids ,
MIN ( timepoint ) OVER (PARTITION BY pid , locktype ,mode ) as started
DE
activity_hist.archive_locking
DONDE
timepoint entre pg_stat_history_begin+(current_hour_diff * interval '1 hour') Y
pg_stat_history_end+(current_hour_diff * interval '1 hour') Y
granted Y
queryid IS NOT NULL
AGRUPAR POR
pid ,
locktype ,
mode ,
timepoint ,
queryid ,
blocking_pids
)
SELECCIONAR
lt.pid ,
lt.locktype ,
lt.mode ,
lt.started ,
lt.queryid ,
lt.blocking_pids ,
COUNT(*) * interval '1 second' as duration
DE lt
AGRUPAR POR
lt.pid ,
lt.locktype ,
lt.mode ,
lt.started ,
lt.queryid ,
lt.blocking_pids
ORDENAR POR 4Ejemplo
| OBTENIENDO BLOQUEOS POR TIPO DE BLOQUEO POR QUERYID
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| pid| locktype| mode| started| queryid| blocking_pids| duration
+----------+-------------------------+--------------------+------------------------------+--------------------+--------------------+--------------------
| 11288| relation| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34
| 11092| transactionid| ExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:03:34
| 11288| relation| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:00:10
| 11092| relation| RowExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:03:34
| 11092| virtualxid| ExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {}| 00:03:34
| 11288| virtualxid| ExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34
| 11288| transactionid| ExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34
| 11288| tuple| AccessExclusiveLock| 2019-09-17 10:00:00.302936| 389015618226997618| {11092}| 00:03:34Uso del historial de bloqueos al analizar incidentes de rendimiento.
- La consulta con queryid=389015618226997618 ejecutada por el proceso con pid=11288 ha estado esperando el bloqueo desde 2019-09-17 10:00:00 durante 3 minutos.
- El bloqueo fue sostenido por el proceso con pid=11092.
- El proceso con pid=11092, al ejecutar la consulta con queryid=389015618226997618 desde 2019-09-17 10:00:00, ha mantenido el bloqueo durante 3 minutos.
Summary
Ahora, espero que comience lo más interesante y útil: la recopilación de estadísticas y el análisis de casos del historial de esperas y bloqueos.
A futuro, espero crear un conjunto de algunas notas (análogo a las metalinks de Oracle).
En general, esta es la razón por la que la metodología utilizada se presenta de la manera más rápida posible para el conocimiento general.
En el corto plazo, intentaré publicar el proyecto en github.
Fuente: habr.com
