En esta página, se describe cómo supervisar el rendimiento de tu base de datos de AlloyDB Omni con secuencias de comandos de observabilidad de PostgreSQL.
Visualiza el estado de los procesos conectados y los eventos de espera
Puedes determinar los procesos conectados a tu instancia de AlloyDB Omni, así como los backends que esperan actividad, consultando la vista pg_stat_activity.
SELECT
pid,
datname,
age(backend_xid) AS age_in_xids,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
state,
wait_event_type,
wait_event,
query_id,
query
FROM
pg_stat_activity
WHERE
state != 'idle'
AND pid <> pg_backend_pid()
ORDER BY
4 DESC
LIMIT 10;
Cómo ver las tablas más grandes
Puedes determinar el tamaño de tus tablas más grandes consultando la vista pg_stat_user_tables.
SELECT
oid,
oid::regclass table_name,
pg_size_pretty(pg_relation_size(oid)),
relpages,
s.seq_scan,
s.idx_scan
FROM
pg_class,
pg_stat_user_tables s
WHERE
s.relid = oid
AND oid > 16383
AND relpages > 100
AND relkind = 'r'
ORDER BY
relpages DESC
LIMIT 20;
Consulta los principales análisis secuenciales
Puedes consultar los principales análisis secuenciales con la vista pg_stat_user_tables.
SELECT
relid,
relname,
seq_scan,
pg_size_pretty(pg_relation_size(relid))
FROM
pg_stat_user_tables
ORDER BY
seq_scan DESC
LIMIT 15;
Cómo ver los análisis de los índices principales
Puedes consultar los principales análisis de índices si realizas una consulta en la vista pg_stat_user_tables.
SELECT
relid,
relid::regclass table_name,
idx_scan,
pg_size_pretty(pg_relation_size(relid))
FROM
pg_stat_user_tables
WHERE
idx_scan > 10
ORDER BY
idx_scan DESC
LIMIT 15;
Cómo ver las transacciones en ejecución más largas
Puedes ver las transacciones de mayor duración si consultas la vista pg_stat_activity y verificas la antigüedad de la transacción.
SELECT
pid,
age(backend_xid) AS age_in_xids,
now() - xact_start AS xact_age,
now() - query_start AS query_age,
state,
query
FROM
pg_stat_activity
WHERE
state != 'idle'
ORDER BY
2 DESC
LIMIT 10;
Cómo verificar el progreso de la aspiración
Puedes verificar el progreso de las operaciones de vacío consultando la vista pg_stat_progress_vacuum y uniéndola a la vista pg_stat_activity con los IDs de proceso.
SELECT
p.pid,
now() - a.xact_start AS duration,
coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
CASE
WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
WHEN a.query ~*'^vacuum' THEN 'user'
ELSE
'regular'
END AS mode,
p.datname AS database,
p.relid::regclass AS table,
p.phase,
pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
p.index_vacuum_count,
round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a using (pid)
ORDER BY now() - a.xact_start DESC;
Cómo ver consultas asíncronas
Para ver las consultas que se ejecutan de forma asíncrona, puedes consultar la vista pg_stat_activity y filtrar las consultas que no son el proceso principal.
SELECT
query,
leader_pid,
array_agg(pid) FILTER (WHERE leader_pid != pid) AS members
FROM
pg_stat_activity
WHERE
leader_pid IS NOT NULL
GROUP BY
query,
leader_pid;
Ver el SQL de bloqueo
Puedes ver la actividad que se bloquea si consultas la vista pg_locks y la unes con la vista pg_stat_activity.
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocked_activity.wait_event AS blocked_wait_event,
blocking_activity.wait_event AS blocking_wait_event,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
Determina la efectividad del tamaño de work_mem y temp_buffers
Para determinar si tus elementos work_mem y temp_buffers tienen el tamaño correcto para tus necesidades, puedes consultar la vista pg_stat_database y verificar el archivo postgres.log.
Con pg_stat_database, ejecuta la siguiente consulta y, si hay algún crecimiento en temp_files o temp_bytes entre las ejecuciones, es probable que se necesite un ajuste para work_mem o temp_buffers.
SELECT
datname,
temp_files,
temp_bytes
FROM
pg_stat_database;
Después de ejecutar este comando, verifica el archivo postgres.log para ver si se usaron archivos temporales:
LOG: [fd.c:1772] temporary file: path "base/pgsql_tmp/pgsql_tmp4640.1", size 139264
El objetivo es minimizar la creación de archivos temporales, no evitar por completo que se creen. Esto se debe a que establecer work_mem y temp_buffers es un equilibrio entre la memoria disponible en el host y la cantidad de conexiones que requieren la memoria. Para configurar estos parámetros correctamente, es necesario comprender cada carga de trabajo individual.