Supervisión del rendimiento con el Almacén de consultas
SE APLICA A: 
Azure Database for PostgreSQL con servidor flexible
La característica de Almacén de consultas del servidor flexible de Azure Database for PostgreSQL proporciona una manera de realizar un seguimiento del rendimiento de las consultas a lo largo del tiempo. El Almacén de consultas simplifica la solución de problemas de rendimiento al ayudar a encontrar rápidamente las consultas que tardan más en ejecutarse y consumen más recursos. El Almacén de consultas captura automáticamente un historial de consultas y estadísticas de tiempo de ejecución y lo conserva para su revisión. Segmenta los datos por tiempo para que pueda ver los patrones de uso temporales. Los datos de todos los usuarios, bases de datos y consultas se almacenan en una base de datos denominada azure_sys en la instancia de servidor flexible de Azure Database for PostgreSQL.
Habilite el Almacén de consultas
El almacén de consultas está disponible para usar sin cargos adicionales. Es una característica opcional, por lo que no está habilitada de forma predeterminada en un servidor. El Almacén de consultas se puede habilitar o deshabilitar globalmente para todas las bases de datos en un servidor determinado (no se puede activar o desactivar por base de datos).
Importante
No habilites el Almacén de consultas en el plan de tarifa ampliable, ya que provocaría un impacto en el rendimiento.
Habilitación del Almacén de consultas en Azure Portal
- Inicie sesión en Azure Portal y seleccione la instancia de servidor flexible de Azure Database for PostgreSQL.
- Seleccione Parámetros del servidor en la sección Configuración del menú.
- Busque el parámetro
pg_qs.query_capture_mode. - Establezca el valor en
topoall, en función de si quiere realizar un seguimiento de las consultas de nivel superior o también de las consultas anidadas (las ejecutadas dentro de una función o procedimiento) y seleccione Guardar. Espere hasta 20 minutos para que el primer lote de datos se conserve en la base de datosazure_sys.
Habilitar muestreo de espera de Almacén de consultas
- Busque el parámetro
pgms_wait_sampling.query_capture_mode. - Establezca el valor en
ally haga clic en Guardar.
Información del Almacén de consultas
El Almacén de consultas consta de dos almacenes:
- Un almacén de estadísticas de ejecución para conservar la información de estadísticas de ejecución de consultas.
- Un almacén de estadísticas de espera para conservar la información de estadísticas de espera.
Algunos escenarios habituales para usar el Almacén de consultas son:
- Determinación del número de veces que se ha ejecutado una consulta en un período determinado.
- Comparar el tiempo de ejecución medio de una consulta a través de ventanas de tiempo para ver variaciones grandes.
- Identificación de consultas de larga duración en las últimas horas.
- Identificación de las N consultas principales que esperan recursos.
- Comprender la naturaleza de las esperas de una consulta determinada.
Para minimizar el uso de espacio, se agregan las estadísticas de ejecución en tiempo de ejecución en el almacén de estadísticas de ejecución en un período fijo y configurable. La información de estos almacenes se puede consultar mediante vistas.
Acceso a información del Almacén de consultas
Los datos del Almacén de consultas se almacenan en la base de datos azure_sys en la instancia de servidor flexible de Azure Database for PostgreSQL.
La consulta siguiente devuelve información sobre las consultas que se registraron en el almacén de consultas:
SELECT * FROM query_store.qs_view;
Y esta consulta devuelve información sobre las estadísticas en espera:
SELECT * FROM query_store.pgms_wait_sampling_view;
Búsqueda de consultas de espera
Los tipos de evento de espera combinan eventos de espera diferentes en ciclos por similitud. El Almacén de consultas proporciona el tipo de evento de espera, el nombre del evento de espera específico y la consulta en cuestión. Poder correlacionar esta información de espera con las estadísticas de tiempo de ejecución de consultas significa que puede mejorar la comprensión de lo que contribuye a las características de rendimiento de consulta.
Estos son algunos ejemplos de cómo puede obtener más información sobre la carga de trabajo con las estadísticas de espera del Almacén de consultas:
| Observación | Acción |
|---|---|
| Largas esperas de bloqueo | Compruebe los textos de consulta para las consultas afectadas e identifique las entidades de destino. Busque en el almacén de consultas otras consultas que se ejecutan con frecuencia o tienen una duración alta y modifican la misma entidad. Tras identificar estas consultas, considere la posibilidad de cambiar la lógica de aplicación para mejorar la simultaneidad o usar un nivel de aislamiento menos restrictivo. |
| Largas esperas de E/S de búfer | Encuentre las consultas con un gran número de lecturas físicas en el Almacén de consultas. Si coinciden con las consultas con esperas de E/S elevadas, considere la posibilidad de habilitar la característica de optimización de índices automatizada para ver si puede recomendar la creación de algunos índices que podrían reducir el número de lecturas físicas para esas consultas. |
| Largas esperas de memoria | Encuentre las consultas que más memoria consumen en el Almacén de consultas. Estas consultas probablemente retrasan el progreso de las consultas afectadas. |
Opciones de configuración
Cuando el Almacén de consultas está habilitado, guarda los datos en ventanas de agregación de una longitud determinada por el parámetro de servidor pg_qs.interval_length_minutes (el valor predeterminado es de 15 minutos). Para cada ventana, almacena hasta 500 consultas distintas por ventana. Los atributos que distinguen la unicidad de cada consulta son user_id (identificador del usuario que ejecuta la consulta), db_id (identificador de la base de datos en cuyo contexto se ejecuta la consulta) y query_id (un valor entero que identifica de forma única la consulta ejecutada). Si el número de consultas distintas alcanza el 500 durante el intervalo configurado, el 5 % de los que se registran se desasignan para hacer espacio para más. Los desasignados primero son los que se ejecutaron el menor número de veces.
Las siguientes opciones están disponibles para configurar los parámetros del Almacén de consultas:
| Parámetro | Descripción | Valor predeterminado | Range |
|---|---|---|---|
pg_qs.interval_length_minutes (*) |
Intervalo de captura en minutos para el almacén de consultas. Define la frecuencia de persistencia de datos. | 15 |
1 - 30 |
pg_qs.is_enabled_fs |
Solo uso interno: Este parámetro se usa como modificador de invalidación de características. Si se muestra como desactivado, el almacén de consultas está deshabilitado, a pesar del valor establecido para pg_qs.query_capture_mode. |
on |
on, off |
pg_qs.max_plan_size |
Número máximo de bytes guardados del texto del plan de consulta por almacén de consultas; los planes más largos se truncan. | 7500 |
100 - 10000 |
pg_qs.max_query_text_length |
Longitud máxima de la consulta que se puede guardar; las consultas más largas se truncan. | 6000 |
100 - 10000 |
pg_qs.parameters_capture_mode |
Si y cuándo capturar parámetros posicionales de consulta. | capture_parameterless_only |
capture_parameterless_only, capture_first_sample |
pg_qs.query_capture_mode |
Instrucciones para realizar un seguimiento. | none |
none, top, all |
pg_qs.retention_period_in_days |
Período de retención en días para el almacén de consultas. Los datos más antiguos se eliminan automáticamente. | 7 |
1 - 30 |
pg_qs.store_query_plans |
Si los planes de consulta se deben guardar en el almacén de consultas. | off |
on, off |
pg_qs.track_utility |
Si el almacén de consultas debe realizar un seguimiento de los comandos de la utilidad. | on |
on, off |
(*) Parámetro de servidor estático que requiere un reinicio del servidor para que un cambio en su valor surta efecto.
Las siguientes opciones afectan específicamente a las estadísticas de espera:
| Parámetro | Descripción | Valor predeterminado | Range |
|---|---|---|---|
pgms_wait_sampling.history_period |
Frecuencia, en milisegundos, en las que se muestrea el muestreo de eventos de espera. | 100 |
1 - 600000 |
pgms_wait_sampling.is_enabled_fs |
Solo uso interno: Este parámetro se usa como modificador de invalidación de características. Si se muestra como off, el muestreo de espera está deshabilitado a pesar del valor establecido para pgms_wait_sampling.query_capture_mode. |
on |
on, off |
pgms_wait_sampling.query_capture_mode |
A qué instrucciones debe realizar un seguimiento la extensión pgms_wait_sampling. |
none |
none, all |
Nota:
pg_qs.query_capture_mode reemplaza a pgms_wait_sampling.query_capture_mode. Si pg_qs.query_capture_mode es none, la configuración pgms_wait_sampling.query_capture_mode no tiene ningún efecto.
Use Azure Portal para obtener o establecer otro valor para un parámetro.
Funciones y vistas
Puede consultar la información registrada por el almacén de consultas y eliminarla mediante algunas vistas y funciones disponibles en el esquema query_store de la base de datos azure_sys. Cualquier usuario en el rol público PostgreSQL puede utilizar estas vistas para ver los datos en el Almacén de consultas. Estas vistas solo están disponibles en la base de datos azure_sys.
Las consultas se normalizan examinando su estructura y omitiendo todo lo que no sea semánticamente significativo, como literales, constantes, alias o diferencias en el uso de mayúsculas y minúsculas.
Si dos consultas son semánticamente idénticas, aunque usen alias diferentes para las mismas columnas y tablas a las que se hace referencia, se identifican con el mismo query_id. Si dos consultas solo difieren en los valores literales usados en ellas, también se identifican con el mismo query_id. En todas las consultas identificadas con el mismo query_id, su sql_query_text será el de la consulta que se ejecutó primero desde que el Almacén de consultas iniciara la actividad de registro o desde la última vez que se descartaron los datos persistentes porque se ejecutó la función query_store.qs_reset.
Funcionamiento de la normalización de consultas
A continuación se muestran algunos ejemplos para intentar ilustrar cómo funciona esta normalización:
Supongamos que crea una tabla con la siguiente instrucción:
create table tableOne (columnOne int, columnTwo int);
Habilita la recopilación de datos del Almacén de consultas y uno o varios usuarios ejecutan las siguientes consultas en este orden exacto:
select * from tableOne;
select columnOne, columnTwo from tableOne;
select columnOne as c1, columnTwo as c2 from tableOne as t1;
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one";
Todas las consultas anteriores comparten el mismo query_id. Y el texto que mantiene el Almacén de consultas es el de la primera consulta ejecutada después de habilitar la recopilación de datos. Por lo tanto, sería select * from tableOne;.
El siguiente conjunto de consultas, una vez normalizado, no coincide con el conjunto anterior de consultas porque la cláusula WHERE las hace semánticamente diferentes:
select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
select * from tableOne where columnOne = -3 and columnTwo = -3;
select columnOne, columnTwo from tableOne where columnOne = '5' and columnTwo = '5';
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = 7 and columnTwo = 7;
Sin embargo, todas las consultas de este último conjunto comparten el mismo query_id y el texto usado para identificarlas todas es el de la primera consulta del lote select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;.
Por último, busque a continuación algunas consultas que no coincidan con el query_id de las del lote anterior y el motivo por el que no:
Consultar
select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
Motivo de la falta de coincidencia: la lista de columnas hace referencia a las mismas dos columnas (columnOne y ColumnTwo), pero el orden en el que se hace referencia se invierte, de columnOne, ColumnTwo en el lote anterior a ColumnTwo, columnOne en esta consulta.
Consultar
select * from tableOne where columnTwo = 25 and columnOne = 25;
Motivo de la falta de coincidencia: el orden en el que se hace referencia a las expresiones evaluadas en la cláusula WHERE se invierte de columnOne = ? and ColumnTwo = ? en el lote anterior a ColumnTwo = ? and columnOne = ? en esta consulta.
Consultar
select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;
Motivo de la falta de coincidencia: la primera expresión de la lista de columnas ya no es columnOne, pero la función abs se evalúa sobre columnOne (abs(columnOne)), que no es semánticamente equivalente.
Consultar
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;
Motivo de la falta de coincidencia: la primera expresión de la cláusula WHERE ya no evalúa la igualdad de columnOne con un literal, sino con el resultado de la función ceiling evaluada sobre un literal, que no es semánticamente equivalente.
Vistas
query_store.qs_view
Esta vista devuelve todos los datos que se conservan en las tablas auxiliares del almacén de consultas. Los datos que se están grabando en memoria para el período actualmente activo no son visibles hasta que el período llega a un final y sus datos volátiles en memoria se recopilan y se conservan en las tablas almacenadas en el disco. Esta vista devuelve una fila diferente para cada base de datos distinta (db_id), usuario (user_id) y consulta (query_id).
| Nombre | Tipo | Referencias | Descripción |
|---|---|---|---|
runtime_stats_entry_id |
bigint | Identificador de la tabla runtime_stats_entries. | |
user_id |
oid | pg_authid.oid | OID del usuario que ha ejecutado la instrucción. |
db_id |
oid | pg_database.oid | OID de la base de datos en la que se ejecutó la instrucción. |
query_id |
bigint | Código hash interno, calculado a partir del árbol de análisis de la instrucción. | |
query_sql_text |
varchar(10000) | Texto de una instrucción representativa. Las consultas diferentes con la misma estructura se agrupan; este texto es el texto para la primera consulta del clúster. El valor predeterminado de la longitud máxima del texto de consulta es 6000 y se puede modificar mediante el parámetro pg_qs.max_query_text_length del almacén de consultas. Si el texto de la consulta supera este valor máximo, se trunca a los primeros pg_qs.max_query_text_length bytes. |
|
plan_id |
bigint | Identificador del plan correspondiente a esta consulta. | |
start_time |
timestamp | Las consultas se agregan por ventanas de tiempo. El parámetro de servidor pg_qs.interval_length_minutes define el intervalo de tiempo de esas ventanas (el valor predeterminado es de 15 minutos). Esta columna corresponde a la hora de inicio de la ventana en la que se registró esta entrada. |
|
end_time |
timestamp | Hora de finalización correspondiente al período de esta entrada. | |
calls |
bigint | Número de veces que la consulta se ejecutó en este período. Nótese que, para las consultas paralelas, el número de llamadas para cada ejecución corresponde a 1 para el proceso back-end que dirige la ejecución de la consulta, más otras tantas unidades para cada proceso de trabajo back-end, iniciado para colaborar ejecutando las ramas paralelas del árbol de ejecución. | |
total_time |
double precision | Tiempo total de ejecución de las consultas, en milisegundos. | |
min_time |
double precision | Tiempo mínimo de ejecución de las consultas, en milisegundos. | |
max_time |
double precision | Tiempo máximo de ejecución de las consultas, en milisegundos. | |
mean_time |
double precision | Tiempo medio de ejecución de las consultas, en milisegundos. | |
stddev_time |
double precision | Desviación estándar del tiempo de ejecución de las consultas, en milisegundos. | |
rows |
bigint | Número total de filas recuperadas o afectadas por la instrucción. Nótese que, para las consultas paralelas, el número de filas para cada ejecución corresponde al número de filas devueltas al cliente por el proceso back-end que dirige la ejecución de la consulta, más la suma de todas las filas que cada proceso de trabajo back-end, iniciado para colaborar ejecutando las ramas paralelas del árbol de ejecución, devuelve al proceso back-end conductor. | |
shared_blks_hit |
bigint | Número total de aciertos de caché de bloques compartidos por la instrucción. | |
shared_blks_read |
bigint | Número total de bloques compartidos leídos por la instrucción. | |
shared_blks_dirtied |
bigint | Número total de bloques compartidos modificados por la instrucción. | |
shared_blks_written |
bigint | Número total de bloques compartidos escritos por la instrucción. | |
local_blks_hit |
bigint | Número total de aciertos de caché de bloques locales por la instrucción. | |
local_blks_read |
bigint | Número total de bloques locales leídos por la instrucción. | |
local_blks_dirtied |
bigint | Número total de bloques locales modificados por la instrucción. | |
local_blks_written |
bigint | Número total de bloques locales escritos por la instrucción. | |
temp_blks_read |
bigint | Número total de bloques temporales leídos por la instrucción. | |
temp_blks_written |
bigint | Número total de bloques temporales escritos por la instrucción. | |
blk_read_time |
double precision | Tiempo total que la instrucción dedica a leer los bloques, en milisegundos (si está habilitado track_io_timing; de lo contrario, cero). | |
blk_write_time |
double precision | Tiempo total que la instrucción dedica a escribir los bloques, en milisegundos (si está habilitado track_io_timing; de lo contrario, cero). | |
is_system_query |
boolean | Determina si el rol con user_id = 10 (azuresu) ejecutó la consulta. Ese usuario tiene privilegios de superusuario y se usa para realizar operaciones del plano de control. Como este servicio es un servicio PaaS administrado, solo Microsoft forma parte de ese rol de superusuario. | |
query_type |
text | Tipo de operación representada por la consulta. Los valores posibles son unknown, select, update, insert, delete, merge, utility, nothing, undefined. |
|
search_path |
text | Valor de search_path establecido en el momento en que se capturó la consulta. | |
query_parameters |
text | Representación de texto de un objeto JSON con los valores pasados a los parámetros posicionales de una consulta con parámetros. Esta columna solo rellena su valor en dos casos: 1) para consultas no parametrizadas. 2) Para las consultas con parámetros, cuando pg_qs.parameters_capture_mode se establece en capture_first_sample, y si el almacén de consultas puede capturar los valores de los parámetros de la consulta en tiempo de ejecución. |
|
parameters_capture_status |
text | Tipo de operación representada por la consulta. Los valores posibles son succeeded (ya sea que la consulta no se parametrizó o fue una consulta parametrizada y los valores se capturaron correctamente), disabled (la consulta se parametrizó pero los parámetros no se capturaron porque pg_qs.parameters_capture_mode se estableció en capture_parameterless_only), too_long_to_capture (la consulta estaba parametrizada, pero los parámetros no se capturaron porque la longitud del JSON resultante que se mostraría en la columna query_parameters de esta vista, se consideraba demasiado largo para que el almacén de consultas persistiera), too_many_to_capture (la consulta se parametrizó, pero no se capturaron parámetros porque el número total de parámetros se consideraba excesivo para que el almacén de consultas persista), serialization_failed (la consulta se parametrizó, pero al menos uno de los valores pasados como parámetro no se pudo serializar en texto). |
query_store.query_texts_view
Esta vista devuelve datos de texto de consulta en el Almacén de consultas. Hay una fila para cada query_sql_text distinto.
| Nombre | Tipo | Descripción |
|---|---|---|
query_text_id |
bigint | Identificador de la tabla query_texts. |
query_sql_text |
varchar(10000) | Texto de una instrucción representativa. Las consultas diferentes con la misma estructura se agrupan; este texto es el texto para la primera consulta del clúster. |
query_type |
smallint | Tipo de operación representada por la consulta. En la versión de PostgreSQL <= 14, los valores posibles son 0 (unknown), 1 (select), 3 (update), 2 (insert), 4 (delete), 5 (utility), 6 (nothing). En la versión de PostgreSQL >= 15, los valores posibles son 0 (unknown), 1 (select), 2 (update), 3 (insert), 4 (delete), 5 (merge), 6 (utility), 7 (nothing). |
query_store.pgms_wait_sampling_view
Esta vista devuelve datos de eventos de espera en el Almacén de consultas. Esta vista devuelve una fila diferente para cada base de datos (db_id), usuario (user_id), consulta (query_id) y evento (event) distintos.
| Nombre | Tipo | Referencias | Descripción |
|---|---|---|---|
start_time |
timestamp | Las consultas se agregan por ventanas de tiempo. El parámetro de servidor pg_qs.interval_length_minutes define el intervalo de tiempo de esas ventanas (el valor predeterminado es de 15 minutos). Esta columna corresponde a la hora de inicio de la ventana en la que se registró esta entrada. |
|
end_time |
timestamp | Hora de finalización correspondiente al período de esta entrada. | |
user_id |
oid | pg_authid.oid | Identificador de objeto del usuario que ejecutó la instrucción. |
db_id |
oid | pg_database.oid | Identificador de objeto de la base de datos en la que se ejecutó la instrucción. |
query_id |
bigint | Código hash interno, calculado a partir del árbol de análisis de la instrucción. | |
event_type |
text | Tipo de evento que está esperando el back-end. | |
event |
text | Nombre del evento de espera si el back-end está esperando. | |
calls |
integer | Número de veces que se capturó el mismo evento. |
Nota:
Para obtener una lista de los valores posibles en las columnas event_type y event de la vista de query_store.pgms_wait_sampling_view, consulte la documentación oficial de pg_stat_activity y busque la información que hace referencia a columnas con los mismos nombres.
query_store.query_plans_view
Esta vista devuelve el plan de consulta que se usó para ejecutar una consulta. Hay una fila por cada identificador de base de datos y de consulta distintos. El almacén de consultas solo registra los planes de consulta para las consultas que no son de utilidad.
| Nombre | Tipo | Referencias | Descripción |
|---|---|---|---|
plan_id |
bigint | Valor hash del plan de consulta normalizado generado por EXPLAIN. Está en forma normalizada porque excluye los costos estimados de los nodos de plan y el uso de búferes. | |
db_id |
oid | pg_database.oid | OID de la base de datos en la que se ejecutó la instrucción. |
query_id |
bigint | Código hash interno, calculado a partir del árbol de análisis de la instrucción. | |
plan_text |
varchar(10000) | Plan de ejecución de la instrucción según costs=false, buffers=false y format=text. Salida idéntica a la producida por EXPLAIN. |
Funciones
query_store.qs_reset
Esta función descarta todas las estadísticas recopiladas hasta ahora por el Almacén de consultas. Descarta las estadísticas de las ventanas de tiempo ya cerradas, que ya se conservan en tablas en disco. También descarta las estadísticas para el período de tiempo actual, que solo existe en memoria. Solo los miembros del rol de administrador del servidor (azure_pg_admin) pueden ejecutar esta función.
query_store.staging_data_reset
Esta función descarta todas las estadísticas recopiladas en memoria por el Almacén de consultas (es decir, los datos en memoria que aún no se han vaciado en las tablas de disco que admiten la persistencia de los datos recopilados para el Almacén de consultas). Solo los miembros del rol de administrador del servidor (azure_pg_admin) pueden ejecutar esta función.
Modo de solo lectura
Cuando una instancia de servidor flexible de Azure Database for PostgreSQL está en modo de solo lectura, como cuando el parámetro default_transaction_read_only está establecido en on, o si el modo de solo lectura se habilita automáticamente debido a que se ha alcanzado la capacidad de almacenamiento, el Almacén de consultas no captura ningún dato.
Al habilitar el almacén de consultas en un servidor que tiene réplicas de lectura, no se habilita automáticamente el almacén de consultas en ninguna de las réplicas de lectura. Incluso si lo habilita en cualquiera de las réplicas de lectura, el almacén de consultas no registra las consultas ejecutadas en ninguna réplica de lectura, ya que funcionan en modo de solo lectura hasta que se promueven a principal.
Comparta sugerencias y errores con el equipo de producto de Azure Database for PostgreSQL.