How to locate SQL statements with the highest resource consumption - ApsaraDB RDS

Cuando su instancia de ApsaraDB RDS for PostgreSQL está bajo carga, no todas las sentencias SQL contribuyen por igual al consumo de recursos. La extensión pg_stat_statements rastrea estadísticas de ejecución para cada sentencia que ejecuta la instancia. Esto le permite clasificar las consultas por tiempo de E/S, tiempo de ejecución, variabilidad de respuesta, uso de memoria compartida o espacio temporal, de modo que pueda enfocar sus esfuerzos de optimización donde tengan mayor impacto.

Este tema explica cómo instalar la extensión, ejecutar consultas de diagnóstico para cada dimensión de recursos y restablecer las estadísticas acumuladas.

Nota

La función SQL Explorer and Audit ofrece una forma alternativa de analizar la ejecución de consultas. Registra sentencias SQL a nivel del kernel de la base de datos, incluidos detalles de ejecución, cuentas de ejecución y direcciones IP, sin afectar el rendimiento de la instancia. Para más información, consulte Usar la función SQL Explorer and Audit.

Instalar la extensión

Ejecute la siguiente sentencia para habilitar pg_stat_statements en su instancia:

CREATE EXTENSION pg_stat_statements;

Una vez instalada, la extensión comienza a recopilar automáticamente estadísticas de ejecución para todas las sentencias SQL.

Funcionamiento de pg_stat_statements

pg_stat_statements normaliza las sentencias SQL al reemplazar los valores literales de los filtros con variables y luego agrupa los patrones de sentencias idénticos. Por ejemplo, WHERE id = 1 y WHERE id = 2 se asignan a la misma entrada. Esto evita entradas duplicadas para consultas que solo difieren en sus valores de parámetro.

La extensión expone sus datos mediante una vista con el mismo nombre. La vista registra:

Estadísticas de ejecución: número de llamadas, tiempo de ejecución total/mínimo/máximo/promedio (en milisegundos) y desviación estándar del tiempo de ejecución. La desviación estándar (stddev_time) refleja la variabilidad de la respuesta.
Uso de búfer compartido: aciertos de caché, lecturas (fallos), bloques modificados generados y bloques modificados escritos.
Uso de búfer local: aciertos de caché, lecturas, bloques modificados generados y bloques modificados escritos.
Uso de búfer temporal: bloques temporales leídos y escritos.
Duraciones de E/S de bloques: tiempo dedicado a leer y escribir bloques (requiere que track_io_timing esté habilitado; de lo contrario, se reporta como cero).

Columnas de la vista

A continuación se describen todas las columnas de la vista pg_stat_statements.

Columna	Tipo	Descripción
`userid`	oid	Identificador de objeto (OID) del usuario que ejecutó la sentencia. Hace referencia a `pg_authid.oid`.
`dbid`	oid	OID de la base de datos en la que se ejecutó la sentencia. Hace referencia a `pg_database.oid`.
`queryid`	bigint	Código hash interno calculado a partir del árbol de análisis sintáctico de la sentencia.
`query`	text	Texto de una sentencia representativa.
`calls`	bigint	Número de veces que se ejecutó la sentencia.
`total_time`	double precision	Tiempo total de ejecución en todas las llamadas, en milisegundos.
`min_time`	double precision	Tiempo mínimo de ejecución para una sola llamada, en milisegundos.
`max_time`	double precision	Tiempo máximo de ejecución para una sola llamada, en milisegundos.
`mean_time`	double precision	Tiempo promedio de ejecución por llamada, en milisegundos.
`stddev_time`	double precision	Desviación estándar poblacional del tiempo de ejecución, en milisegundos.
`rows`	bigint	Número total de filas recuperadas o afectadas por la sentencia.
`shared_blks_hit`	bigint	Total de aciertos de caché de bloques compartidos.
`shared_blks_read`	bigint	Total de bloques compartidos leídos del disco.
`shared_blks_dirtied`	bigint	Total de bloques compartidos modificados.
`shared_blks_written`	bigint	Total de bloques compartidos escritos.
`local_blks_hit`	bigint	Total de aciertos de caché de bloques locales.
`local_blks_read`	bigint	Total de bloques locales leídos del disco.
`local_blks_dirtied`	bigint	Total de bloques locales modificados.
`local_blks_written`	bigint	Total de bloques locales escritos.
`temp_blks_read`	bigint	Total de bloques temporales leídos.
`temp_blks_written`	bigint	Total de bloques temporales escritos.
`blk_read_time`	double precision	Tiempo total dedicado a leer bloques, en milisegundos. Requiere `track_io_timing`.
`blk_write_time`	double precision	Tiempo total dedicado a escribir bloques, en milisegundos. Requiere `track_io_timing`.

Encontrar las consultas con mayor consumo de recursos

Cada consulta a continuación selecciona datos de pg_stat_statements y devuelve las cinco principales sentencias según una métrica específica. Ejecute estas consultas para identificar candidatas a optimización.

Mayor consumo de E/S

Un tiempo alto de E/S suele indicar índices faltantes, escaneos completos de tablas o consultas que recuperan muchos más datos de los necesarios.

Las 5 principales por tiempo promedio de E/S por llamada (ideal para encontrar consultas costosas en cada ejecución):

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY (blk_read_time + blk_write_time) / calls DESC
LIMIT 5;

Las 5 principales por tiempo total de E/S en todas las llamadas (ideal para encontrar consultas con el mayor impacto acumulativo):

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY (blk_read_time + blk_write_time) DESC
LIMIT 5;

Tiempo de ejecución más lento

Un tiempo promedio de ejecución alto señala consultas que son individualmente lentas. Estas son buenas candidatas para analizar el plan de consulta con EXPLAIN ANALYZE.

Las 5 principales por tiempo promedio de ejecución por llamada:

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 5;

Las 5 principales por tiempo total de ejecución en todas las llamadas (útil para identificar consultas de alta frecuencia que suman un impacto significativo):

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Mayor variabilidad de respuesta

Una desviación estándar alta en el tiempo de ejecución (stddev_time) indica tiempos de respuesta impredecibles. Esto suele señalar contención de bloqueos, presión de desalojo de caché o competencia intermitente por recursos.

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY stddev_time DESC
LIMIT 5;

Mayor uso de memoria compartida

Las consultas con alto consumo de búfer compartido ocupan grandes porciones del pool de búferes, lo que puede desplazar a otras consultas y aumentar los fallos de caché en toda la instancia.

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY (shared_blks_hit + shared_blks_dirtied) DESC
LIMIT 5;

Mayor uso de espacio temporal

Las consultas que escriben bloques temporales realizan ordenamientos o uniones hash que exceden work_mem. Aumentar work_mem a nivel de sesión o reescribir la consulta para reducir el tamaño del conjunto de resultados puede eliminar el desbordamiento a disco.

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY temp_blks_written DESC
LIMIT 5;

Restablecer estadísticas

pg_stat_statements acumula estadísticas desde que se instaló la extensión o desde el último restablecimiento. Para establecer una línea base limpia, por ejemplo, después de desplegar una optimización de consultas, restablezca los contadores:

SELECT pg_stat_statements_reset();

Ejecute este comando periódicamente para evitar que los datos históricos oculten los patrones de rendimiento actuales.

Referencias

Documentación de PostgreSQL 9.6.2 - F.29. pg_stat_statements