Supervisión del rendimiento con el Almacén de consultas
SE APLICA A: 
Azure Database for PostgreSQL: servidor flexible
La característica 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, las bases de datos y las consultas se almacenan en una base de datos llamada azure_sys en la instancia del servidor flexible de Azure Database for PostgreSQL.
Importante
No modifique la base de datos azure_sys ni su esquema. Si lo hace, impedirá que el Almacén de consultas y las características de rendimiento relacionados funcionen correctamente.
Habilite el Almacén de consultas.
El Almacén de consultas está disponible en todas las regiones 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 haga clic en Guardar. Espere hasta 20 minutos para que el primer lote de datos se conserve en la base de datos azure_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.
- Comparación del tiempo de ejecución medio de una consulta entre períodos para ver grandes diferencias.
- Identificación de consultas de larga duración en las últimas horas.
- Identificación de las N consultas principales que esperan recursos.
- Descripción de la naturaleza de espera de una determinada consulta.
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 en el Almacén de consultas:
SELECT * FROM query_store.qs_view;
O esta consulta para estadísticas de 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 modifiquen la misma entidad, que se ejecuta con frecuencia o tiene una gran duración. 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 largas esperas de E/S, considere la posibilidad de introducir un índice en la entidad subyacente para llevar a cabo búsquedas en lugar de exámenes. Esto podría minimizar la sobrecarga de E/S de las consultas. Compruebe las Recomendaciones de rendimiento para el servidor en el portal para ver si hay recomendaciones de índices para este servidor que optimizarían las 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. Compruebe las Recomendaciones de rendimiento para el servidor en el portal para ver si hay recomendaciones de índices que optimizarían estas consultas. |
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 (con un identificador de usuario, dbid y queryid distintos) por ventana. Si durante un intervalo se hacen 500 consultas distintas, el 5 % de ellas con un uso menor se desasignan para dejar espacio a otras.
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.query_capture_mode | Establece las instrucciones de las que se realiza el seguimiento. | None | none, top, all |
| pg_qs.interval_length_minutes (*) | Establece el intervalo de captura de query_store en minutos para pg_qs: esta es la frecuencia de persistencia de datos. | 15 | 1 - 30 |
| pg_qs.store_query_plans | Activa o desactiva el guardado de planes de consulta para pg_qs. | apagado | on, off |
| pg_qs.max_plan_size | Establece el número máximo de bytes que se guardarán para el texto del plan de consulta para pg_qs; los planes más largos se truncarán. | 7500 | 100 - 10 000 |
| pg_qs.max_query_text_length | Establece la longitud máxima de la consulta que se puede guardar; las consultas más largas se truncarán. | 6000 | 100 - 10K |
| pg_qs.retention_period_in_days | Establece el período de retención en días para pg_qs: después de este tiempo se eliminan los datos. | 7 | 1 - 30 |
| pg_qs.track_utility | Establece si pg_qs realiza un seguimiento de los comandos de la utilidad. | en | 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.query_capture_mode | Selecciona las instrucciones a las que realiza el seguimiento la extensión pgms_wait_sampling. | None | none, all |
| Pgms_wait_sampling.history_period | Establece la frecuencia, en milisegundos, con la que se muestrean los eventos de espera. | 100 | 1-600000 |
Nota
pg_qs.query_capture_mode reemplaza a pgms_wait_sampling.query_capture_mode. Si el valor de pg_qs.query_capture_mode es NONE, la configuración de 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
Vea y administre el Almacén de consultas mediante las siguientes vistas y funciones. 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 del lote anterior y el motivo por el que no:
Consulta:
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.
Consulta:
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.
Consulta:
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.
Consulta:
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 ya se han conservado 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 caracteres. |
|
| plan_id | bigint | Identificador del plan correspondiente a esta consulta. | |
| start_time | timestamp | Las consultas se agregan por período, cuyo intervalo de tiempo se define mediante el parámetro de servidor pg_qs.interval_length_minutes (el valor predeterminado son 15 minutos). Esta es la hora de inicio correspondiente al período de 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 la consulta se ejecutó por rol con user_id = 10 (azuresu), que tiene privilegios de superusuario y se usa para realizar operaciones de panel 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. |
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 período, cuyo intervalo de tiempo se define mediante el parámetro de servidor pg_qs.interval_length_minutes (el valor predeterminado son 15 minutos). Esta es la hora de inicio correspondiente al período de esta entrada. |
|
| end_time | timestamp | Hora de finalización correspondiente al período de esta entrada. | |
| 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. | |
| 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 ha capturado el mismo evento. |
Nota:
Para obtener una lista de los valores posibles de las columnas event_type y event de la vista query_store.pgms_wait_sampling_view, consulte la documentación oficial de pg_stat_activity y busque la información relativa 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. Esto solo almacenará planes de consulta de consultas que no son de utilidad.
| plan_id | db_id | query_id | plan_text |
|---|---|---|---|
| plan_id | bigint | Valor hash del plan de consulta normalizado generado por EXPLAIN. Se considera normalizado 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. Es la misma salida dada por EXPLAIN. |
Functions
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 los períodos ya cerrados, que se han conservado en tablas de disco y las del período actual, que todavía se mantienen en memoria. Esta función solo se puede ejecutar mediante el rol de administrador del servidor (azure_pg_admin).
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). Esta función solo se puede ejecutar mediante el rol de administrador del servidor (azure_pg_admin).
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.