Monitorar o desempenho com o repositório de consultas
APLICA-SE A: 
Banco de Dados do Azure para PostgreSQL - Servidor Flexível
O repositório de consultas é um recurso no Banco de Dados do Azure para servidor flexível PostgreSQL que fornece uma maneira de acompanhar o desempenho da consulta ao longo do tempo. O repositório de consultas simplifica a solução de problemas de desempenho, ajudando você a encontrar rapidamente as consultas de execução mais longa e que consomem mais recursos. O repositório de consultas captura automaticamente um histórico de consultas e estatísticas de tempo de execução e as retém para sua revisão. Ele fatia os dados por tempo para que você possa ver padrões de uso temporal. Os dados de todos os usuários, bancos de dados e consultas são armazenados em um banco de dados nomeado azure_sys no Banco de Dados do Azure para instância de servidor flexível do PostgreSQL.
Habilitar armazenamento de consultas
A loja de consultas está disponível para uso sem custos extras. É um recurso de aceitação, portanto, não é habilitado por padrão em um servidor. O repositório de consultas pode ser habilitado ou desabilitado globalmente para todos os bancos de dados em um determinado servidor e não pode ser ativado ou desativado por banco de dados.
Importante
Não habilite o armazenamento de consultas no nível de preços Burstable pois isso causaria impacto no desempenho.
Habilitar o repositório de consultas no portal do Azure
- Entre no portal do Azure e selecione sua instância de servidor flexível do Banco de Dados do Azure para PostgreSQL.
- Selecione Parâmetros do servidor na seção Configurações do menu.
- Procure o
pg_qs.query_capture_modeparâmetro. - Defina o valor como
topouall, dependendo se você deseja rastrear consultas de nível superior ou também consultas aninhadas (aquelas que são executadas dentro de uma função ou procedimento) e selecione Salvar. Aguarde até 20 minutos para que o primeiro lote de dados persista noazure_sysbanco de dados.
Habilitar amostragem de espera do repositório de consultas
- Procure o
pgms_wait_sampling.query_capture_modeparâmetro. - Defina o valor como
alle Salvar.
Informações no repositório de consultas
O repositório de consultas consiste em dois armazenamentos:
- Um armazenamento de estatísticas de tempo de execução para persistir as informações de estatísticas de execução de consulta.
- Um armazenamento de estatísticas de espera para informações de estatísticas de espera persistentes.
Os cenários comuns para usar o repositório de consultas incluem:
- Determinar o número de vezes que uma consulta foi executada em uma determinada janela de tempo.
- Comparando o tempo médio de execução de uma consulta entre janelas de tempo para ver grandes variações.
- Identificação de consultas de execução mais longa nas últimas horas.
- Identificar as principais consultas N que estão aguardando recursos.
- Compreender a natureza das esperas por uma consulta específica.
Para minimizar o uso de espaço, as estatísticas de execução de tempo de execução no repositório de estatísticas de tempo de execução são agregadas em uma janela de tempo fixa e configurável. As informações nessas lojas podem ser consultadas usando modos de exibição.
Acessar informações de armazenamento de consultas
Os dados do repositório de consultas são armazenados no banco de dados em azure_sys sua instância de servidor flexível do Banco de Dados do Azure para PostgreSQL.
A consulta a seguir retorna informações sobre consultas que foram registradas no repositório de consultas:
SELECT * FROM query_store.qs_view;
E esta consulta retorna informações sobre estatísticas de espera:
SELECT * FROM query_store.pgms_wait_sampling_view;
Encontrar consultas de espera
Os tipos de evento de espera combinam diferentes eventos de espera em buckets por semelhança. O repositório de consultas fornece o tipo de evento wait, o nome específico do evento wait e a consulta em questão. Ser capaz de correlacionar essas informações de espera com as estatísticas de tempo de execução da consulta significa que você pode obter uma compreensão mais profunda do que contribui para as características de desempenho da consulta.
Aqui estão alguns exemplos de como você pode obter mais informações sobre sua carga de trabalho usando as estatísticas de espera no repositório de consultas:
| Observação | Ação |
|---|---|
| Espera de bloqueio alto | Verifique os textos de consulta para as consultas afetadas e identifique as entidades de destino. Procure no repositório de consultas outras consultas que são executadas com freqüência e/ou têm alta duração e estão modificando a mesma entidade. Depois de identificar essas consultas, considere alterar a lógica do aplicativo para melhorar a simultaneidade ou usar um nível de isolamento menos restritivo. |
| E/S de buffer alto aguarda | Encontre as consultas com um alto número de leituras físicas no repositório de consultas. Se eles corresponderem às consultas com altas esperas de E/S, considere habilitar o recurso de ajuste de índice automatizado para ver se ele pode recomendar a criação de alguns índices que podem diminuir o número de leituras físicas para essas consultas. |
| Espera de memória elevada | Encontre as principais consultas que consomem memória no repositório de consultas. Essas consultas provavelmente estão atrasando o progresso das consultas afetadas. |
Opções de configuração
Quando o armazenamento de consultas está habilitado, ele salva dados em janelas de agregação de comprimento determinado pelo parâmetro de servidor pg_qs.interval_length_minutes (o padrão é 15 minutos). Para cada janela, ele armazena até 500 consultas distintas por janela. Os atributos que distinguem a exclusividade de cada consulta são user_id (identificador do usuário que executa a consulta), db_id (identificador do banco de dados em cujo contexto a consulta é executada) e query_id (um valor inteiro que identifica exclusivamente a consulta executada). Se o número de consultas distintas atingir 500 durante o intervalo configurado, 5% das que são registradas são deslocadas para abrir espaço para mais. Os que foram desalocados primeiro são os que foram executados o menor número de vezes.
As seguintes opções estão disponíveis para configurar os parâmetros do Repositório de Consultas:
| Parâmetro | Descrição | Predefinição | Intervalo |
|---|---|---|---|
pg_qs.interval_length_minutes (*) |
Intervalo de captura em minutos para armazenamento de consultas. Define a frequência de persistência de dados. | 15 |
1 - 30 |
pg_qs.is_enabled_fs |
Somente uso interno: este parâmetro é usado como uma opção de substituição de recurso. Se ele for exibido como desativado, o repositório de consultas será desabilitado, apesar do valor definido para pg_qs.query_capture_mode. |
on |
on, off |
pg_qs.max_plan_size |
Número máximo de bytes salvos do texto do plano de consulta por armazenamento de consulta; planos mais longos são truncados. | 7500 |
100 - 10000 |
pg_qs.max_query_text_length |
Comprimento máximo da consulta que pode ser guardado; consultas mais longas são truncadas. | 6000 |
100 - 10000 |
pg_qs.parameters_capture_mode |
Se e quando capturar parâmetros posicionais de consulta. | capture_parameterless_only |
capture_parameterless_only, capture_first_sample |
pg_qs.query_capture_mode |
Declarações para acompanhar. | none |
none, top, all |
pg_qs.retention_period_in_days |
Janela de período de retenção em dias para armazenamento de consultas. Os dados mais antigos são automaticamente eliminados. | 7 |
1 - 30 |
pg_qs.store_query_plans |
Se os planos de consulta devem ser salvos no repositório de consultas. | off |
on, off |
pg_qs.track_utility |
Se o repositório de consultas deve rastrear comandos do utilitário. | on |
on, off |
(*) Parâmetro estático do servidor que requer uma reinicialização do servidor para que uma alteração em seu valor entre em vigor.
As seguintes opções aplicam-se especificamente às estatísticas de espera:
| Parâmetro | Descrição | Predefinição | Intervalo |
|---|---|---|---|
pgms_wait_sampling.history_period |
Frequência, em milissegundos, em que os eventos de espera são amostrados. | 100 |
1 - 600000 |
pgms_wait_sampling.is_enabled_fs |
Somente uso interno: este parâmetro é usado como uma opção de substituição de recurso. Se for mostrado como off, a amostragem de espera será desabilitada apesar do valor definido para pgms_wait_sampling.query_capture_mode. |
on |
on, off |
pgms_wait_sampling.query_capture_mode |
Quais declarações a pgms_wait_sampling extensão deve acompanhar. |
none |
none, all |
Nota
pg_qs.query_capture_modepgms_wait_sampling.query_capture_modesubstitui . Se pg_qs.query_capture_mode for none, a pgms_wait_sampling.query_capture_mode configuração não terá efeito.
Use o portal do Azure para obter ou definir um valor diferente para um parâmetro.
Vistas e funções
Você pode consultar as informações registradas pelo repositório de consultas e/ou excluí-las usando algumas exibições e funções disponíveis no query_store esquema do banco de azure_sys dados. Qualquer pessoa na função pública PostgreSQL pode usar essas exibições para ver os dados no repositório de consultas. Estas vistas só estão disponíveis na base de dados azure_sys .
As consultas são normalizadas olhando para sua estrutura e ignorando qualquer coisa que não seja semanticamente significativa, como literais, constantes, aliases ou diferenças na caixa.
Se duas consultas forem semanticamente idênticas, mesmo que usem aliases diferentes para as mesmas colunas e tabelas referenciadas, elas serão identificadas com a mesma query_id. Se duas consultas diferirem apenas nos valores literais usados nelas, elas também serão identificadas com o mesmo query_id. Para consultas identificadas com o mesmo query_id, seu sql_query_text é o da consulta executada primeiro desde que o repositório de consultas começou a registrar a atividade ou desde a última vez que os dados persistentes foram descartados porque a função query_store.qs_reset foi executada.
Como funciona a normalização de consultas
A seguir estão alguns exemplos para tentar ilustrar como essa normalização funciona:
Digamos que você crie uma tabela com a seguinte instrução:
create table tableOne (columnOne int, columnTwo int);
Você habilita a coleta de dados do Repositório de Consultas e um ou vários usuários executam as seguintes consultas, nesta ordem exata:
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 as consultas anteriores compartilham o mesmo query_id. E o texto que o Repositório de Consultas mantém é o da primeira consulta executada após habilitar a coleta de dados. Portanto, seria select * from tableOne;.
O seguinte conjunto de consultas, uma vez normalizado, não corresponde ao conjunto anterior de consultas porque a cláusula WHERE as torna semanticamente 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;
No entanto, todas as consultas neste último conjunto compartilham o mesmo query_id e o texto usado para identificá-las todas é o da primeira consulta no lote select columnOne as c1, columnTwo as c2 from tableOne as t1 where columnOne = 1 and columnTwo = 1;.
Por fim, veja abaixo algumas consultas que não correspondem ao query_id das do lote anterior e o motivo pelo qual elas não correspondem:
Consulta:
select columnTwo as c2, columnOne as c1 from tableOne as t1 where columnOne = 1 and columnTwo = 1;
Motivo para não corresponder: Lista de colunas refere-se às mesmas duas colunas (columnOne e ColumnTwo), mas a ordem em que são referidas é invertida, do columnOne, ColumnTwo lote anterior para ColumnTwo, columnOne esta consulta.
Consulta:
select * from tableOne where columnTwo = 25 and columnOne = 25;
Motivo da não correspondência: A ordem em que as expressões avaliadas na cláusula WHERE são referidas é invertida do columnOne = ? and ColumnTwo = ? lote anterior para ColumnTwo = ? and columnOne = ? nesta consulta.
Consulta:
select abs(columnOne), columnTwo from tableOne where columnOne = 12 and columnTwo = 21;
Motivo para não corresponder: A primeira expressão na lista de colunas não columnOne é mais, mas a função abs avaliada sobre columnOne (abs(columnOne)), que não é semanticamente equivalente.
Consulta:
select columnOne as "column one", columnTwo as "column two" from tableOne as "table one" where columnOne = ceiling(16) and columnTwo = 16;
Razão para não corresponder: A primeira expressão na cláusula WHERE não avalia mais a igualdade de columnOne com um literal, mas com o resultado da função ceiling avaliada sobre um literal, que não é semanticamente equivalente.
Visualizações
query_store.qs_view
Esta vista devolve todos os dados que são persistentes nas tabelas de suporte do arquivo de consultas. Os dados que ainda estão gravando na memória para a janela de tempo ativa no momento não são visíveis até que a janela de tempo chegue ao fim, e seus dados voláteis na memória são coletados e persistem em tabelas armazenadas no disco. Essa exibição retorna uma linha diferente para cada banco de dados (db_id), usuário (user_id) e consulta (query_id).
| Nome | Tipo | Referências | Descrição |
|---|---|---|---|
runtime_stats_entry_id |
bigint | ID da tabela runtime_stats_entries. | |
user_id |
Oide | pg_authid.oid | OID do usuário que executou a instrução. |
db_id |
Oide | pg_database.oid | OID do banco de dados no qual a instrução foi executada. |
query_id |
bigint | Código hash interno, calculado a partir da árvore de análise da instrução. | |
query_sql_text |
Varchar(10000) | Texto de uma declaração representativa. Consultas diferentes com a mesma estrutura são agrupadas; Este texto é o texto para a primeira das consultas no cluster. O valor padrão para o comprimento máximo do texto da consulta é 6000 e pode ser modificado usando o parâmetro pg_qs.max_query_text_lengthquery store . Se o texto da consulta exceder esse valor máximo, ele será truncado para os primeiros pg_qs.max_query_text_length bytes. |
|
plan_id |
bigint | ID do plano correspondente a esta consulta. | |
start_time |
carimbo de data/hora | As consultas são agregadas por janelas de tempo. O parâmetro pg_qs.interval_length_minutes Server define o período de tempo dessas janelas (o padrão é 15 minutos). Esta coluna corresponde à hora de início da janela em que esta entrada foi gravada. |
|
end_time |
carimbo de data/hora | Hora de término correspondente à janela de tempo para esta entrada. | |
calls |
bigint | Número de vezes que a consulta foi executada nesta janela de tempo. Observe que, para consultas paralelas, o número de chamadas para cada execução corresponde a 1 para o processo de back-end que conduz a execução da consulta, além de outras unidades para cada processo de trabalho de back-end que é iniciado para colaborar executando as ramificações paralelas da árvore de execução. | |
total_time |
precisão dupla | Tempo total de execução da consulta, em milissegundos. | |
min_time |
precisão dupla | Tempo mínimo de execução da consulta, em milissegundos. | |
max_time |
precisão dupla | Tempo máximo de execução da consulta, em milissegundos. | |
mean_time |
precisão dupla | Tempo médio de execução da consulta, em milissegundos. | |
stddev_time |
precisão dupla | Desvio padrão do tempo de execução da consulta, em milissegundos. | |
rows |
bigint | Número total de linhas recuperadas ou afetadas pela instrução. Observe que, para consultas paralelas, o número de linhas para cada execução corresponde ao número de linhas retornadas ao cliente pelo processo de back-end que conduz a execução da consulta, mais a soma de todas as linhas que cada processo de trabalho de back-end, iniciado para colaborar executando as ramificações paralelas da árvore de execução, retorna ao processo de back-end que conduz a execução da consulta. | |
shared_blks_hit |
bigint | Número total de acessos de cache de bloco compartilhado pela instrução. | |
shared_blks_read |
bigint | Número total de blocos compartilhados lidos pela declaração. | |
shared_blks_dirtied |
bigint | Número total de blocos compartilhados sujos pela declaração. | |
shared_blks_written |
bigint | Número total de blocos compartilhados escritos pela declaração. | |
local_blks_hit |
bigint | Número total de acessos de cache de bloco local pela instrução. | |
local_blks_read |
bigint | Número total de blocos locais lidos pela instrução. | |
local_blks_dirtied |
bigint | Número total de blocos locais sujo pela declaração. | |
local_blks_written |
bigint | Número total de blocos locais escritos pela declaração. | |
temp_blks_read |
bigint | Número total de blocos temporários lidos pela instrução. | |
temp_blks_written |
bigint | Número total de blocos temporários escritos pela instrução. | |
blk_read_time |
precisão dupla | Tempo total gasto na leitura de blocos da instrução, em milissegundos (se track_io_timing estiver habilitado, caso contrário, zero). | |
blk_write_time |
precisão dupla | Tempo total gasto na instrução escrevendo blocos, em milissegundos (se track_io_timing estiver habilitado, caso contrário, zero). | |
is_system_query |
boolean | Determina se a função com user_id = 10 (azuresu) executou a consulta. Esse usuário tem privilégios de superusuário e é usado para executar operações de plano de controle. Como esse serviço é um serviço de PaaS gerenciado, somente a Microsoft faz parte dessa função de superusuário. | |
query_type |
texto | Tipo de operação representado pela consulta. Os valores possíveis são unknown, select, update, , insert, mergedelete, utility, nothing, undefined. |
|
search_path |
texto | O valor de search_path definido no momento em que a consulta foi capturada. | |
query_parameters |
texto | Representação de texto de um objeto JSON com os valores passados para os parâmetros posicionais de uma consulta parametrizada. Esta coluna só preenche seu valor em dois casos: 1) para consultas não parametrizadas. 2) Para consultas parametrizadas, quando pg_qs.parameters_capture_mode é definido como capture_first_sample, e se o armazenamento de consultas pode buscar os valores para os parâmetros da consulta em tempo de execução. |
|
parameters_capture_status |
texto | Tipo de operação representado pela consulta. Os valores possíveis são succeeded (ou a consulta não foi parametrizada ou foi uma consulta parametrizada e os valores foram capturados com êxito), disabled (a consulta foi parametrizada, mas os parâmetros não foram capturados porque pg_qs.parameters_capture_mode foi definida como capture_parameterless_only), too_long_to_capture (a consulta foi parametrizada, mas os parâmetros não foram capturados porque o comprimento do JSON resultante que seria exibido na query_parameters coluna desta exibição foi considerado excessivamente longo para o armazenamento de consultas persistir), too_many_to_capture (a consulta foi parametrizada, mas os parâmetros não foram capturados porque o número total de parâmetros foi considerado excessivo para que o armazenamento de consultas persistisse), serialization_failed (a consulta foi parametrizada, mas pelo menos um dos valores passados como parâmetro não pôde ser serializado para texto). |
query_store.query_texts_view
Esta vista devolve dados de texto de consulta no Repositório de Consultas. Há uma linha para cada query_sql_text distinta.
| Nome | Tipo | Descrição |
|---|---|---|
query_text_id |
bigint | ID da tabela query_texts |
query_sql_text |
Varchar(10000) | Texto de uma declaração representativa. Consultas diferentes com a mesma estrutura são agrupadas; Este texto é o texto para a primeira das consultas no cluster. |
query_type |
smallint | Tipo de operação representado pela consulta. Na versão do PostgreSQL <= 14, os valores possíveis são 0 (unknown), 1 (select), 2 (update), 3 (insert), 4 (delete), 5 (utility), 6 (nothing). Na versão do PostgreSQL >= 15, os valores possíveis são 0 (unknown), 1 (select), 2 (update), 3 (insert), 4 (delete), 5 (merge), 6 (utility), 7 (nothing). |
query_store.pgms_wait_sampling_view
Esta vista devolve dados de eventos de espera no Repositório de Consultas. Essa exibição retorna uma linha diferente para cada banco de dados (db_id), usuário (user_id), consulta (query_id) e evento (evento) distintos.
| Nome | Tipo | Referências | Descrição |
|---|---|---|---|
start_time |
carimbo de data/hora | As consultas são agregadas por janelas de tempo. O parâmetro pg_qs.interval_length_minutes Server define o período de tempo dessas janelas (o padrão é 15 minutos). Esta coluna corresponde à hora de início da janela em que esta entrada foi gravada. |
|
end_time |
carimbo de data/hora | Hora de término correspondente à janela de tempo para esta entrada. | |
user_id |
Oide | pg_authid.oid | Identificador de objeto do usuário que executou a instrução. |
db_id |
Oide | pg_database.oid | Identificador de objeto do banco de dados no qual a instrução foi executada. |
query_id |
bigint | Código hash interno, calculado a partir da árvore de análise da instrução. | |
event_type |
texto | O tipo de evento para o qual o back-end está esperando. | |
event |
texto | O nome do evento de espera se o back-end estiver aguardando no momento. | |
calls |
integer | Número de vezes que o mesmo evento foi capturado. |
Nota
Para obter uma lista de valores possíveis nas event_type colunas e event da query_store.pgms_wait_sampling_view exibição, consulte a documentação oficial do pg_stat_activity e procure as informações referentes às colunas com os mesmos nomes.
query_store.query_plans_view
Esta vista devolve o plano de consulta que foi utilizado para executar uma consulta. Há uma linha por cada ID de banco de dados distinto e ID de consulta. O repositório de consultas registra apenas planos de consulta para consultas não utilitárias.
| Nome | Tipo | Referências | Descrição |
|---|---|---|---|
plan_id |
bigint | O valor de hash do plano de consulta normalizado produzido por EXPLAIN. Ele está na forma normalizada porque exclui os custos estimados dos nós de plano e o uso de buffers. | |
db_id |
Oide | pg_database.oid | OID do banco de dados no qual a instrução foi executada. |
query_id |
bigint | Código hash interno, calculado a partir da árvore de análise da instrução. | |
plan_text |
Varchar(10000) | Plano de execução da instrução fornecido costs=false, buffers=false e format=text. Saída idêntica à produzida pela EXPLAIN. |
Funções
query_store.qs_reset
Esta função descarta todas as estatísticas reunidas até agora pelo repositório de consultas. Ele descarta as estatísticas de janelas de tempo já fechadas, que já são persistidas em tabelas no disco. Ele também descarta as estatísticas para a janela de tempo atual, que só existem na memória. Somente membros da função de administrador do servidor (azure_pg_admin) podem executar essa função.
query_store.staging_data_reset
Essa função descarta todas as estatísticas coletadas na memória pelo armazenamento de consulta (ou seja, os dados na memória que ainda não foram liberados para as tabelas em disco que suportam a persistência de dados coletados para armazenamento de consulta). Somente membros da função de administrador do servidor (azure_pg_admin) podem executar essa função.
Modo somente leitura
Quando uma instância do Banco de Dados do Azure para Servidor Flexível PostgreSQL está no modo somente leitura, como quando o parâmetro é definido como on, ou se o default_transaction_read_only modo somente leitura é habilitado automaticamente devido ao alcance da capacidade de armazenamento, o repositório de consultas não captura nenhum dado.
Habilitar o armazenamento de consultas em um servidor que tenha réplicas de leitura não habilita automaticamente o armazenamento de consultas em nenhuma das réplicas de leitura. Mesmo que você o habilite em qualquer uma das réplicas de leitura, o repositório de consultas não registrará as consultas executadas em nenhuma réplica de leitura, porque elas operam no modo somente leitura até que você as promova para primárias.
Partilhe as suas sugestões e bugs com a equipa de produto da Base de Dados do Azure para PostgreSQL.
Conteúdos relacionados
- Cenários de uso para armazenamento de consultas no Banco de Dados do Azure para PostgreSQL - Servidor Flexível.
- Práticas recomendadas para armazenamento de consultas no Banco de Dados do Azure para PostgreSQL - Servidor Flexível.
- Query Performance Insight no Banco de Dados do Azure para PostgreSQL - Servidor flexível.