Postgresql pg_stat_statements

F.29.1. los pg_stat_statements Vista

F.29.2. Funciones

F.29.3. Parámetros de configuración

F.29.4. Salida de muestra

F.29.5. Autores

los pg_stat_statements El módulo proporciona un medio para realizar un seguimiento de las estadísticas de planificación y ejecución de todas las sentencias SQL ejecutadas por un servidor.

El módulo debe cargarse agregando pg_stat_statements a shared_preload_libraries en postgresql.conf, porque requiere memoria compartida adicional. Esto significa que es necesario reiniciar el servidor para agregar o quitar el módulo.

Cuando pg_stat_statements está cargado, realiza un seguimiento de las estadísticas en todas las bases de datos del servidor. Para acceder y manipular estas estadísticas, el módulo proporciona una vista, pg_stat_statements, y las funciones de utilidad pg_stat_statements_reset y pg_stat_statements. Estos no están disponibles a nivel mundial, pero se pueden habilitar para una base de datos específica con CREATE EXTENSION pg_stat_statements.

F.29.1. los pg_stat_statements Vista

Las estadísticas recopiladas por el módulo están disponibles a través de una vista denominada pg_stat_statements. Esta vista contiene una fila para cada ID de base de datos, ID de usuario e ID de consulta distintos (hasta el número máximo de declaraciones distintas que el módulo puede rastrear). Las columnas de la vista se muestran en la Tabla F.21.

Cuadro F.21. pg_stat_statements Columnas

Por motivos de seguridad, solo los superusuarios y los miembros del pg_read_all_stats rol pueden ver el texto SQL y queryid de consultas ejecutadas por otros usuarios. Sin embargo, otros usuarios pueden ver las estadísticas si la vista se ha instalado en su base de datos.

Consultas planificables (es decir, SELECT, INSERT, UPDATE, y DELETE) se combinan en un solo pg_stat_statements entrada siempre que tengan estructuras de consulta idénticas de acuerdo con un cálculo hash interno. Normalmente, dos consultas se considerarán iguales para este propósito si son semánticamente equivalentes, excepto por los valores de las constantes literales que aparecen en la consulta. Sin embargo, los comandos de utilidad (es decir, todos los demás comandos) se comparan estrictamente sobre la base de sus cadenas de consulta textuales.

Cuando se ha ignorado el valor de una constante para hacer coincidir la consulta con otras consultas, la constante se reemplaza por un símbolo de parámetro, como $1, en el pg_stat_statements monitor. El resto del texto de la consulta es el de la primera consulta que tenía el particular queryid valor hash asociado con el pg_stat_statements entrada.

En algunos casos, las consultas con textos visiblemente diferentes pueden fusionarse en un solo pg_stat_statements entrada. Normalmente, esto sucederá solo para consultas semánticamente equivalentes, pero existe una pequeña posibilidad de que las colisiones de hash provoquen que las consultas no relacionadas se fusionen en una sola entrada. (Sin embargo, esto no puede suceder con consultas que pertenecen a diferentes usuarios o bases de datos).

Desde el queryid El valor hash se calcula en la representación posterior al análisis sintáctico de las consultas, lo contrario también es posible: las consultas con textos idénticos pueden aparecer como entradas separadas, si tienen diferentes significados como resultado de factores como diferentes search_path ajustes.

Consumidores de pg_stat_statements puede que desee utilizar queryid (quizás en combinación con dbid y userid) como un identificador más estable y confiable para cada entrada que su texto de consulta. Sin embargo, es importante comprender que solo existen garantías limitadas en torno a la estabilidad del queryid valor hash. Dado que el identificador se deriva del árbol de análisis posterior al análisis sintáctico, su valor es una función de, entre otras cosas, los identificadores de objetos internos que aparecen en esta representación. Esto tiene algunas implicaciones contrarias a la intuición. Por ejemplo, pg_stat_statements considerará que dos consultas aparentemente idénticas son distintas, si hacen referencia a una tabla que se eliminó y se volvió a crear entre las ejecuciones de las dos consultas. El proceso de hash también es sensible a las diferencias en la arquitectura de la máquina y otras facetas de la plataforma. Además, no es seguro asumir que queryid será estable en las principales versiones de PostgreSQL.

Como una regla de oro, queryid Se puede suponer que los valores son estables y comparables solo mientras la versión del servidor subyacente y los detalles de los metadatos del catálogo permanezcan exactamente iguales. Se puede esperar que dos servidores que participan en la replicación basada en la reproducción WAL física tengan idéntica queryid valores para la misma consulta. Sin embargo, los esquemas de replicación lógica no prometen mantener las réplicas idénticas en todos los detalles relevantes, por lo que queryid no será un identificador útil para acumular costos en un conjunto de réplicas lógicas. En caso de duda, se recomienda realizar pruebas directas.

Los símbolos de parámetro utilizados para reemplazar constantes en textos de consulta representativos comienzan desde el siguiente número después del más alto $n parámetro en el texto de la consulta original, o $1 si no hubiera ninguno. Vale la pena señalar que en algunos casos puede haber símbolos de parámetros ocultos que afectan esta numeración. Por ejemplo, PL / pgSQL usa símbolos de parámetros ocultos para insertar valores de variables locales de función en consultas, de modo que una declaración PL / pgSQL como SELECT i + 1 INTO j tendría un texto representativo como SELECT i + $2.

Los textos de consulta representativos se guardan en un archivo de disco externo y no consumen memoria compartida. Por lo tanto, incluso los textos de consulta muy largos se pueden almacenar correctamente. Sin embargo, si se acumulan muchos textos de consulta largos, el archivo externo puede crecer de forma inmanejable. Como método de recuperación si eso sucede, pg_stat_statements puede optar por descartar los textos de la consulta, con lo cual todas las entradas existentes en el pg_stat_statements la vista mostrará nulo query campos, aunque las estadísticas asociadas con cada queryid se conservan. Si esto sucede, considere reducir pg_stat_statements.max para prevenir recurrencias.

plans y calls no siempre se espera que coincidan porque las estadísticas de planificación y ejecución se actualizan en su respectiva fase final, y solo para operaciones exitosas. Por ejemplo, si una declaración se planifica con éxito pero falla durante la fase de ejecución, solo se actualizarán sus estadísticas de planificación. Si se omite la planificación porque se utiliza un plan almacenado en caché, solo se actualizarán sus estadísticas de ejecución.

F.29.2. Funciones

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void

pg_stat_statements_reset descarta las estadísticas recopiladas hasta ahora por pg_stat_statements correspondiente al especificado userid, dbid y queryid. Si no se especifica alguno de los parámetros, el valor predeterminado 0(inválido) se utiliza para cada uno de ellos y se restablecerán las estadísticas que coincidan con otros parámetros. Si no se especifica ningún parámetro o todos los parámetros especificados son 0(no válido), descartará todas las estadísticas. De forma predeterminada, esta función solo puede ser ejecutada por superusuarios. Se puede conceder acceso a otras personas que utilicen GRANT.

pg_stat_statements(showtext boolean) returns setof record

los pg_stat_statements La vista se define en términos de una función también denominada pg_stat_statements. Es posible que los clientes llamen al pg_stat_statements funcionar directamente, y especificando showtext := false que se omita el texto de la consulta (es decir, el OUT argumento que corresponde a la vista query columna devolverá nulos). Esta función está destinada a admitir herramientas externas que deseen evitar la sobrecarga de recuperar repetidamente textos de consulta de longitud indeterminada. En cambio, estas herramientas pueden almacenar en caché el primer texto de consulta observado para cada entrada, ya que eso es todo pg_stat_statements sí mismo lo hace, y luego recupera los textos de consulta solo cuando sea necesario. Dado que el servidor almacena los textos de consulta en un archivo, este enfoque puede reducir la E / S física para el examen repetido de la pg_stat_statements datos.

F.29.3. Parámetros de configuración

pg_stat_statements.max (integer)

pg_stat_statements.max es el número máximo de declaraciones rastreadas por el módulo (es decir, el número máximo de filas en el pg_stat_statements vista). Si se observan más declaraciones distintas que ésa, se descarta la información sobre las declaraciones menos ejecutadas. El valor predeterminado es 5000. Este parámetro solo se puede configurar al iniciar el servidor.

pg_stat_statements.track (enum)

pg_stat_statements.track controla qué declaraciones cuenta el módulo. Especificar top para realizar un seguimiento de los estados de cuenta de nivel superior (los emitidos directamente por los clientes), all para rastrear también declaraciones anidadas (como declaraciones invocadas dentro de funciones), o none para deshabilitar la recopilación de estadísticas de declaraciones. El valor predeterminado es top. Solo los superusuarios pueden cambiar esta configuración.

pg_stat_statements.track_utility (boolean)

pg_stat_statements.track_utility controla si el módulo rastrea los comandos de la utilidad. Los comandos de utilidad son todos aquellos que no sean SELECT, INSERT, UPDATE y DELETE. El valor predeterminado es on. Solo los superusuarios pueden cambiar esta configuración.

pg_stat_statements.track_planning (boolean)

pg_stat_statements.track_planning controla si el módulo realiza un seguimiento de las operaciones de planificación y la duración. Habilitar este parámetro puede incurrir en una notable penalización de rendimiento, especialmente cuando se ejecutan menos tipos de consultas en muchas conexiones simultáneas. El valor predeterminado es off. Solo los superusuarios pueden cambiar esto configuración.

pg_stat_statements.save (boolean)

pg_stat_statements.save especifica si se deben guardar las estadísticas de las declaraciones durante los cierres del servidor. Si esto es off luego, las estadísticas no se guardan al apagar ni se vuelven a cargar al iniciar el servidor. El valor predeterminado es on. Este parámetro solo se puede configurar en el postgresql.conf archivo o en la línea de comando del servidor.

El módulo requiere memoria compartida adicional proporcional a pg_stat_statements.max. Tenga en cuenta que esta memoria se consume siempre que se carga el módulo, incluso si pg_stat_statements.track se establece en none.

Estos parámetros deben configurarse en postgresql.conf. El uso típico podría ser:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

F.29.4. Salida de muestra

bench=# SELECT pg_stat_statements_reset();

$ pgbench -i bench
$ pgbench -c10 -t300 bench

bench=# x
bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
calls           | 3000
total_exec_time | 25565.855387
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls           | 3000
total_exec_time | 20756.669379
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------​------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 291.865911
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 4 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 271.232977
rows            | 3000
hit_percent     | 98.8454011741682975
-[ RECORD 5 ]---+--------------------------------------------------​------------------
query           | alter table pgbench_accounts add primary key (aid)
calls           | 1
total_exec_time | 160.588563
rows            | 0
hit_percent     | 100.0000000000000000


bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
            WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';

bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls           | 3000
total_exec_time | 20756.669379
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------​------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 291.865911
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------​------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 271.232977
rows            | 3000
hit_percent     | 98.8454011741682975
-[ RECORD 4 ]---+--------------------------------------------------​------------------
query           | alter table pgbench_accounts add primary key (aid)
calls           | 1
total_exec_time | 160.588563
rows            | 0
hit_percent     | 100.0000000000000000
-[ RECORD 5 ]---+--------------------------------------------------​------------------
query           | vacuum analyze pgbench_accounts
calls           | 1
total_exec_time | 136.448116
rows            | 0
hit_percent     | 99.9201915403032721

bench=# SELECT pg_stat_statements_reset(0,0,0);

bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------​---------------------------
query           | SELECT pg_stat_statements_reset(0,0,0)
calls           | 1
total_exec_time | 0.189497
rows            | 1
hit_percent     | 
-[ RECORD 2 ]---+--------------------------------------------------​---------------------------
query           | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit /          +
                |                nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+
                |           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3
calls           | 0
total_exec_time | 0
rows            | 0
hit_percent     | 

F.29.5. Autores

Takahiro Itagaki <[email protected]>. Normalización de consultas agregada por Peter Geoghegan <[email protected]>.