Compartir por

Supervisión del rendimiento con el Almacén de consultas

  • Artigo

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

  1. Inicie sesión en Azure Portal y seleccione la instancia de servidor flexible de Azure Database for PostgreSQL.
  2. Seleccione Parámetros del servidor en la sección Configuración del menú.
  3. Busque el parámetro pg_qs.query_capture_mode.
  4. Establezca el valor en TOP o ALL, 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

  1. Busque el parámetro pgms_wait_sampling.query_capture_mode.
  2. Establezca el valor en ALL y haga clic en Guardar.

Información del Almacén de consultas

El Almacén de consultas consta de dos almacenes:

  1. Un almacén de estadísticas de ejecución para conservar la información de estadísticas de ejecución de consultas.
  2. 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 las 500 consultas distintas por ventana. 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).

Limitaciones y problemas conocidos

Compatibilidad de Azure Storage y almacén de consultas

Debido a problemas de compatibilidad, no se pueden habilitar las extensiones de Azure Storage y almacén de consultas al mismo tiempo. Para garantizar un funcionamiento adecuado y evitar posibles conflictos, habilite solo una de estas extensiones a la vez.

Para usar Azure Storage:

  • Deshabilite el Almacén de consultas estableciendo el parámetro pg_qs.query_capture_mode en NONE. Este parámetro es dinámico, por lo que no es necesario reiniciarlo.

Para usar el Almacén de consultas:

  1. Deshabilite la extensión de Azure Storage emitiendo DROP EXTENSION azure_storage;.
  2. Quite Azure Storage de shared_preload_libraries.
  3. Reinicie el servidor de bases de datos.

Estos pasos son necesarios para evitar conflictos y asegurarse de que el sistema funciona correctamente. Estamos trabajando para resolver estos problemas de compatibilidad y le mantendrá informado de las actualizaciones.

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.

Contenido relacionado