Monitorar o desempenho com o Repositório de Consultas
APLICA-SE A: 
Banco de dados do Azure para PostgreSQL – Servidor Flexível
O recurso Repositório de Consultas no servidor flexível do Banco de Dados do Azure para PostgreSQL fornece uma maneira de acompanhar o desempenho de consultas ao longo do tempo. O Repositório de Consultas simplifica a solução de problemas ajudando você a rapidamente localizar as consultas de execução mais longa e que consomem mais recursos. O Repositório de Consultas captura automaticamente um histórico das estatísticas de runtime e consultas e o retém para sua análise. Ele divide os dados por tempo para que você possa ver os padrões de uso temporais. Os dados de todos os usuários, bancos de dados e consultas são armazenados em um banco de dados chamado azure_sys na instância do servidor flexível do Banco de Dados do Azure para PostgreSQL.
Importante
Não modifique o banco de dados azure_sys ou o esquema. Fazer isso impedirá que o Repositório de Consultas e os recursos de desempenho relacionados funcionem corretamente.
Habilitar o Repositório de Consultas
O Repositório de Consultas está disponível em todas as regiões sem encargos adicionais. É um recurso que requer aceitação, portanto, ele não está habilitado em um servidor por padrão. O Repositório de Consultas pode ser habilitado ou desabilitado globalmente para todos os bancos de dados em determinado servidor e não pode ser ativado ou desativado por banco de dados individualmente.
Importante
Não habilitar o Repositório de Consultas na camada de preços Com Capacidade de Intermitência, 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 do servidor flexível do Banco de Dados do Azure para PostgreSQL.
- Selecione Parâmetros de servidor na seção Configurações do menu.
- Pesquise o parâmetro
pg_qs.query_capture_mode. - Defina o valor como
TOPouALL, dependendo se você deseja acompanhar consultas de nível superior ou também consultas aninhadas (aquelas executadas dentro de uma função ou procedimento) e clique em Salvar. Permita que o primeiro lote de dados persista no banco de dados azure_sys por até 20 minutos.
Habilitar a Amostragem de Espera do Repositório de Consultas
- Pesquise o parâmetro
pgms_wait_sampling.query_capture_mode. - Defina o valor como
ALLe Salve.
Informações no Repositório de Consultas
O Repositório de Consultas consiste em dois repositórios:
- Um repositório de estatísticas de runtime para manter as informações de estatísticas de execução de consulta.
- Um repositório de estatísticas de espera para manter as informações de estatísticas de execução de espera.
Os cenários comuns para usar o Repositório de Consultas incluem:
- Determinação do número de vezes que uma consulta foi executada em uma determinada janela de tempo.
- Comparação do tempo médio de execução de uma consulta entre janelas de tempo para ver grandes deltas.
- Identificação de consultas de execução mais longas nas últimas horas.
- Identificação das principais N consultas que estão aguardando recursos.
- Entender a natureza das esperas para uma consulta específica.
Para minimizar o uso de espaço, as estatísticas de execução de runtime no repositório de estatísticas de runtime são agregadas em uma janela de tempo fixa configurável. As informações contidas nesses repositórios podem ser consultadas usando exibições.
Acessar as informações do Repositório de Consultas
Os dados do Repositório de Consultas são armazenados no banco de dados azure_sys na instância do servidor flexível do Banco de Dados do Azure para PostgreSQL. A consulta a seguir retorna informações sobre consultas no Repositório de Consultas:
SELECT * FROM query_store.qs_view;
Ou essa consulta para estatísticas de espera:
SELECT * FROM query_store.pgms_wait_sampling_view;
Localizar 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 de espera, o nome do evento de espera específico e a consulta em questão. Ser capaz de correlacionar essas informações de espera com as estatísticas de runtime de consulta significa que você pode obter uma compreensão mais profunda do que contribui para as características de desempenho de consulta.
Aqui estão alguns exemplos de como você pode obter mais insights sobre sua carga de trabalho usando as estatísticas de espera no Repositório de Consultas:
| Observação | Ação |
|---|---|
| Esperas 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 modificam a mesma entidade, que é executada com frequência e/ou têm alta duração. Depois de identificar essas consultas, considere alterar a lógica do aplicativo para melhorar a simultaneidade ou use um nível de isolamento menos restritivo. |
| Esperas de E/S de buffer alto | Localize as consultas com um grande número de leituras físicas no Repositório de Consultas. Se elas corresponderem às consultas com esperas de E/S altas, considere a possibilidade de introduzir um índice na entidade subjacente para realizar buscas em vez de verificações. Isso minimizaria a sobrecarga de E/S das consultas. Verifique as Recomendações de desempenho para seu servidor no portal para ver se há recomendações de índice para esse servidor que otimizariam as consultas. |
| Esperas de memória alta | Localize as consultas que consomem mais memória no Repositório de Consultas. Essas consultas estão provavelmente atrasando o andamento das consultas afetadas. Verifique as Recomendações de desempenho para seu servidor no portal para ver se há recomendações de índice que otimizariam essas consultas. |
Opções de configuração
Quando o Repositório de Consultas está habilitado, ele salva os dados em janelas de agregação de comprimento determinado pelo parâmetro do servidor pg_qs.interval_length_minutes (o padrão é de 15 minutos). Para cada janela, ela armazena até 500 consultas distintas (com userid, dbid e queryid distintos) por janela. Se, durante um intervalo, o número de consultas distintas atingir 500, os 5% com menor uso serão desalocados para abrir espaço para um número maior.
As opções a seguir estão disponíveis para configurar os parâmetros do Repositório de Consultas:
| Parâmetro | Descrição | Default | Range |
|---|---|---|---|
| pg_qs.query_capture_mode | Define quais instruções são rastreadas. | nenhum | none, top, all |
| pg_qs.interval_length_minutes (*) | Define o intervalo de captura do query_store em minutos para pg_qs – essa é a frequência da persistência de dados. | 15 | 1 a 30 |
| pg_qs.store_query_plans | Ativa ou desativa o salvamento de planos de consulta por pg_qs. | Desligar | on, off |
| pg_qs.max_plan_size | Define o número máximo de bytes que serão salvos para o texto do plano de consulta para o pg_qs; planos mais longos serão truncados. | 7500 | 100 a 10 mil |
| pg_qs.max_query_text_length | Define o comprimento máximo da consulta que pode ser salvo; consultas mais longas serão truncadas. | 6000 | 100 a 10 mil |
| pg_qs.retention_period_in_days | Define a janela de período de retenção em dias para pg_qs – após esse tempo, os dados serão excluídos. | 7 | 1 a 30 |
| pg_qs.track_utility | Define se os comandos do utilitário são rastreados pelo pg_qs. | on | on, off |
(*) Parâmetro de servidor estático que requer uma reinicialização do servidor para que uma alteração em seu valor entre em vigor.
As opções a seguir se aplicam especificamente às estatísticas de espera:
| Parâmetro | Descrição | Default | Range |
|---|---|---|---|
| pgms_wait_sampling.query_capture_mode | Seleciona quais instruções são controladas pela extensão pgms_wait_sampling. | nenhum | none, all |
| Pgms_wait_sampling.history_period | Define a frequência, em milissegundos, com a qual são realizadas as amostras dos eventos de espera. | 100 | 1 a 600000 |
Observação
pg_qs.query_capture_mode substitui pgms_wait_sampling.query_capture_mode. Se pg_qs.query_capture_mode for NONE, a configuração pgms_wait_sampling.query_capture_mode não terá efeito.
Use o portal do Azure para obter ou definir um valor diferente para um parâmetro.
Exibições e funções
Exiba e gerencie o Repositório de Consultas usando as seguintes exibições e funções. Qualquer pessoa na função pública do PostgreSQL pode usar essas exibições para ver os dados no Repositório de Consultas. Essas exibições estão disponíveis somente no banco de dados azure_sys.
As consultas são normalizadas ao examinar sua estrutura e ignorar qualquer coisa que não seja semanticamente significativa, como literais, constantes, aliases ou diferenças no uso de maiúsculas e minúsculas.
Se duas consultas forem semanticamente idênticas, mesmo que usem aliases diferentes para as mesmas colunas e tabelas referenciadas, elas serão identificadas com o mesmo query_id. Se duas consultas forem diferentes apenas nos valores literais usados nelas, elas também serão identificadas com o mesmo query_id. Para todas as consultas identificadas com o mesmo query_id, o sql_query_text será o da consulta que foi executada primeiro desde que o Repositório de Consultas começou a gravar atividades ou desde a última vez que os dados persistidos foram descartados porque a função query_store.qs_reset foi executada.
Como funciona a normalização da consulta
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 único usuário, ou vá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 a habilitação da 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 é 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, encontre abaixo algumas consultas que não correspondem ao query_id das consultas no 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: a lista de colunas refere-se às mesmas duas colunas (columnOne e ColumnTwo), mas a ordem em que são referidas está invertida, de columnOne, ColumnTwo no lote anterior para ColumnTwo, columnOne nesta consulta.
Consulta:
select * from tableOne where columnTwo = 25 and columnOne = 25;
Motivo para não corresponder: a ordem na qual as expressões avaliadas na cláusula WHERE são referidas está invertida de columnOne = ? and ColumnTwo = ? no 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 é mais columnOne, mas a função abs avaliada sobre columnOne (abs(columnOne)), o 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;
Motivo 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 em relação a um literal, o que não é semanticamente equivalente.
Modos de exibição
query_store.qs_view
Essa exibição retorna todos os dados que já foram persistidos nas tabelas de suporte do Repositório de Consultas. Os dados que estão sendo registrados na memória para a janela de tempo atualmente ativa 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 persistidos em tabelas armazenadas em disco. Essa exibição retorna uma linha diferente para cada banco de dados (db_id), usuário (user_id) e consulta (query_id) distintos.
| Nome | Tipo | Referências | Descrição |
|---|---|---|---|
| runtime_stats_entry_id | BIGINT | ID da tabela runtime_stats_entries. | |
| user_id | oid | pg_authid.oid | OID do usuário que executou a instrução. |
| db_id | oid | 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 instrução representativa. Consultas diferentes com a mesma estrutura são agrupadas. Este texto é o da 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_length do repositório de consultas. Se o texto da consulta exceder esse valor máximo, ele será truncado para os primeiros pg_qs.max_query_text_length caracteres. |
|
| plan_id | BIGINT | ID do plano correspondente a essa consulta. | |
| start_time | timestamp | As consultas são agregadas por janelas de tempo, cujo intervalo de tempo é definido pelo parâmetro de servidor pg_qs.interval_length_minutes (o padrão é de 15 minutos). Essa é a hora de início correspondente à janela de tempo para esta entrada. |
|
| end_time | timestamp | Hora de término correspondente à janela de tempo para esta entrada. | |
| chamadas | 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, mais tantas outras unidades para cada processo de trabalho de back-end, iniciadas para colaborar na execução dos branches paralelos da árvore de execução. | |
| total_time | double precision | Tempo total de execução da consulta em milissegundos. | |
| min_time | double precision | Tempo mínimo de execução da consulta em milissegundos. | |
| max_time | double precision | Tempo máximo de execução da consulta em milissegundos. | |
| mean_time | double precision | Tempo médio de execução da consulta em milissegundos. | |
| stddev_time | double precision | Desvio padrão de 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, além da soma de todas as linhas que cada processo de trabalho de back-end, iniciado para colaborar na execução dos branches paralelos da árvore de execução, retorna para o processo de back-end que está conduzindo a execução. | |
| shared_blks_hit | BIGINT | Número total de ocorrências no cache do bloco compartilhado pela instrução. | |
| shared_blks_read | BIGINT | Número total de blocos compartilhados lidos pela instrução. | |
| shared_blks_dirtied | BIGINT | Número total de blocos compartilhados modificados pela instrução. | |
| shared_blks_written | BIGINT | Número total de blocos compartilhados gravados pela instrução. | |
| local_blks_hit | BIGINT | Número total de ocorrências no cache do bloco local pela instrução. | |
| local_blks_read | BIGINT | Número total de leituras de blocos locais pela instrução. | |
| local_blks_dirtied | BIGINT | Número total de blocos locais modificados pela instrução. | |
| local_blks_written | BIGINT | Número total de blocos locais gravados pela instrução. | |
| temp_blks_read | BIGINT | Número total de leituras de blocos temporários pela instrução. | |
| temp_blks_written | BIGINT | Número total de gravações de blocos temporários pela instrução. | |
| blk_read_time | double precision | Tempo total que a instrução passou lendo blocos em milissegundos (se track_io_timing estiver habilitado, caso contrário, zero). | |
| blk_write_time | double precision | Tempo total que a instrução passou gravando blocos em milissegundos (se track_io_timing estiver habilitado, caso contrário, zero). | |
| is_system_query | boolean | Determina se a consulta foi executada pela função com user_id = 10 (azuresu), que tem privilégios de superusuário e é usada para executar operações de painel de controle. Como esse serviço é um serviço gerenciado de PaaS, somente a Microsoft faz parte dessa função de superusuário. | |
| query_type | text | Tipo de operação representado pela consulta. Os valores possíveis são unknown, select, update, insert, delete, merge, utility, nothing, undefined. |
query_store.query_texts_view
Essa exibição retorna os dados de texto da consulta no Repositório de Consultas. Há uma linha para cada query_sql_text distinto.
| Nome | Tipo | Descrição |
|---|---|---|
| query_text_id | BIGINT | ID da tabela query_texts |
| query_sql_text | varchar (10000) | Texto de uma instrução representativa. Consultas diferentes com a mesma estrutura são agrupadas. Este texto é o da 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 (desconhecido), 1 (selecionar), 2 (atualizar), 3 (inserir), 4 (excluir), 5 (utilitário) 6 (nada). Na versão do PostgreSQL >= 15, os valores possíveis são 0 (desconhecido), 1 (selecionar), 2 (atualizar), 3 (inserir), 4 (excluir), 5 (mesclar), 6 (utilitário), 7 (nada). |
query_store.pgms_wait_sampling_view
Essa exibição retorna os 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 | timestamp | As consultas são agregadas por janelas de tempo, cujo intervalo de tempo é definido pelo parâmetro de servidor pg_qs.interval_length_minutes (o padrão é de 15 minutos). Essa é a hora de início correspondente à janela de tempo para esta entrada. |
|
| end_time | timestamp | Hora de término correspondente à janela de tempo para esta entrada. | |
| user_id | oid | pg_authid.oid | OID do usuário que executou a instrução. |
| db_id | oid | 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. | |
| event_type | text | O tipo de evento pelo qual o back-end está esperando. | |
| event | text | O nome do evento de espera se o back-end estiver esperando no momento. | |
| chamadas | Número inteiro | Número de vezes que o mesmo evento foi capturado. |
Observação
Para obter uma lista de valores possíveis nas colunas de event_type e event da exibição query_store.pgms_wait_sampling_view, 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
Essa exibição retorna o plano de consulta que foi usado para executar uma consulta. Há uma linha para cada ID de banco de dados e ID de consulta distinta. Isso armazenará apenas planos de consulta para consultas não utilitárias.
| plan_id | db_id | query_id | plan_text |
|---|---|---|---|
| plan_id | BIGINT | O valor do hash do plano de consulta normalizado produzido por EXPLAIN. Ele é considerado normalizado porque exclui os custos estimados dos nós do plano e o uso de buffers. | |
| db_id | oid | 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 fornecida com costs=false, buffers=false e format=text. Essa é a mesma saída dada por EXPLAIN. |
Funções
query_store.qs_reset
Essa função descarta todas as estatísticas coletadas até o momento pelo Repositório de Consultas. Ela descarta tanto as estatísticas para janelas de tempo já encerradas, que foram persistidas em tabelas no disco, quanto aquelas para a janela de tempo atual, que ainda são mantidas na memória. Essa função só pode ser executada pela função de administrador de servidor (azure_pg_admin).
query_store.staging_data_reset
Essa função descarta todas as estatísticas coletadas na memória pelo Repositório de Consultas (ou seja, os dados na memória que ainda não foram liberados para as tabelas no disco que dão suporte à persistência dos dados coletados para o Repositório de Consultas). Essa função só pode ser executada pela função de administrador de servidor (azure_pg_admin).
Modo somente leitura
Quando uma instância do Banco de Dados do Azure para PostgreSQL – Servidor Flexível está no modo somente leitura, como quando o parâmetro default_transaction_read_only é definido como on, ou se o modo somente leitura for habilitado automaticamente devido à capacidade de armazenamento ter atingido o limite, o Repositório de Consultas não captura nenhum dado.