Monitorar o desempenho com o armazenamento de consultas
APLICA-SE A: 
Banco de Dados do Azure para PostgreSQL - Servidor Flexível
O armazenamento de consultas é um recurso do Banco de Dados do Azure para PostgreSQL com Servidor Flexível que fornece uma maneira de acompanhar o desempenho de consultas ao longo do tempo. O armazenamento de consultas simplifica a solução de problemas de desempenho ajudando você a localizar rapidamente as consultas de execução mais prolongada e com uso mais intensivo de recursos. O armazenamento de consultas captura automaticamente um histórico de consultas e estatísticas de runtime e as retém para você as analise. 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 Banco de Dados do Azure para PostgreSQL com Servidor Flexível.
Habilitar o armazenamento de consultas
O armazenamento de consultas está disponível para uso sem cobrança adicional. É um recurso que requer aceitação e, portanto, não está 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 determinado servidor e não pode ser ativado ou desativado por banco de dados individualmente.
Importante
Não habilite o armazenamento de consultas no nível de preços "Com Capacidade de Intermitência" porque isso causaria um impacto sobre o desempenho.
Habilitar o armazenamento 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 da forma como você quer acompanhar consultas de nível superior e também consultas aninhadas (as que são executadas dentro de uma função ou procedimento), e clique em Salvar. Aguarde até 20 minutos para que o primeiro lote de dados seja persistido no banco de dadosazure_sys.
Habilitar a amostragem de espera do armazenamento de consultas
- Pesquise o parâmetro
pgms_wait_sampling.query_capture_mode. - Defina o valor como
alle Salve.
Informações no armazenamento de consultas
O armazenamento de consultas consiste de 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 armazenamento de consultas incluem:
- Determinação do número de vezes que uma consulta foi executada em uma determinada janela de tempo.
- Comparar o tempo médio de execução de uma consulta nos diversos períodos de tempo para observar grandes variações.
- Identificação de consultas de execução mais longas nas últimas horas.
- Identificação das principais N consultas que estão aguardando recursos.
- Entenda a natureza das esperas de 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 armazenamento de consultas
Os dados do armazenamento de consultas são armazenados no banco de dados azure_sys na sua instância do Banco de Dados do Azure para PostgreSQL com Servidor Flexível.
A consulta a seguir retorna informações sobre consultas que foram registradas no armazenamento de consultas:
SELECT * FROM query_store.qs_view;
E essa consulta retorna informações sobre 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 armazenamento 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 temos alguns exemplos de como você pode obter mais insights sobre sua carga de trabalho usando as estatísticas de espera no armazenamento de consultas:
| Observação | Ação |
|---|---|
| Esperas de bloqueio longas ou frequentes | Verifique os textos de consulta para as consultas afetadas e identifique as entidades de destino. Pesquise no armazenamento de consultas outras consultas que são executadas com frequência e/ou têm longa 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 use um nível de isolamento menos restritivo. |
| Esperas de E/S de buffer longas ou frequentes | Localize as consultas com um grande número de leituras físicas no armazenamento de consultas. Se corresponderem às consultas com esperas de E/S longas ou frequentes, pense em habilitar o recurso de ajuste de índice automatizado para ver se ele consegue recomendar a criação de alguns índices que talvez reduzam o número de leituras físicas para essas consultas. |
| Esperas de memória longas ou frequentes | Localize as consultas com maior consumo de memória no armazenamento de consultas. Essas consultas estão provavelmente atrasando o andamento das consultas afetadas. |
Opções de configuração
Quando estiver habilitado, o armazenamento de consultas salvará os dados em períodos de agregação com comprimento determinado pelo parâmetro do servidor pg_qs.interval_length_minutes (o padrão é 15 minutos). Para cada período, são armazenadas até 500 consultas distintas por período. 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 consultas que forem registradas serão desalocadas para abrir espaço para outras. As que foram executados o menor número de vezes são desalocadas primeiro.
As opções a seguir estão disponíveis para configurar os parâmetros do Repositório de Consultas:
| Parâmetro | Descrição | Default | Intervalo |
|---|---|---|---|
pg_qs.interval_length_minutes (*) |
O intervalo de captura, em minutos, do armazenamento de consultas. Define a frequência da persistência de dados. | 15 |
1 - 30 |
pg_qs.is_enabled_fs |
Somente uso interno: este parâmetro é usado como um comutador de substituição de recursos. Se aparecer como desativado, o armazenamento de consultas será desabilitado, apesar de o valor ter sido definido como 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 consultas; os planos mais longos serão truncados. | 7500 |
100 - 10000 |
pg_qs.max_query_text_length |
Comprimento máximo da consulta que pode ser salvo; consultas mais longas serã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 |
Instruções a serem acompanhadas. | none |
none, top, all |
pg_qs.retention_period_in_days |
Intervalo do período de retenção, em dias, para o armazenamento de consultas. Os dados mais antigos são excluídos automaticamente. | 7 |
1 - 30 |
pg_qs.store_query_plans |
Se os planos de consulta devem ser salvos no armazenamento de consultas. | off |
on, off |
pg_qs.track_utility |
Se o armazenamento de consultas deve acompanhar os comandos do utilitário. | 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 | Intervalo |
|---|---|---|---|
pgms_wait_sampling.history_period |
Frequência, em milissegundos, com a qual são coletadas amostras dos eventos de espera. | 100 |
1 - 600000 |
pgms_wait_sampling.is_enabled_fs |
Somente uso interno: este parâmetro é usado como um comutador de substituição de recursos. Se aparecer como off, a coleta de amostras da espera será desabilitada, apesar de o valor definido ter sido definido como pgms_wait_sampling.query_capture_mode. |
on |
on, off |
pgms_wait_sampling.query_capture_mode |
Quais instruções a extensão pgms_wait_sampling precisa acompanhar. |
none |
none, all |
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 tem nenhum efeito.
Use o portal do Azure para obter ou definir um valor diferente para um parâmetro.
Exibições e funções
Você pode consultar as informações registradas pelo armazenamento de consultas e/ou excluí-la usando algumas exibições e funções disponíveis no esquema query_store do banco de dados azure_sys. Qualquer pessoa na função pública do PostgreSQL pode usar essas exibições para ver os dados no armazenamento 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 a mesma query_id, o sql_query_text será o da consulta que foi executada primeiro desde que o armazenamento de consultas começou a registrar atividades, ou desde a última vez em 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;.
Para terminar, encontre abaixo algumas consultas que não correspondem ao query_id das consultas no lote anterior e o motivo pelo qual 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 se refere às mesmas duas colunas (columnOne e ColumnTwo), mas a ordem em que são mencionadas está invertida, de columnOne, ColumnTwo no lote anterior para ColumnTwo, columnOne nessa 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 usando a 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 usando um literal, o que não é semanticamente equivalente.
Exibições
query_store.qs_view
Essa exibição retorna todos os dados que estiverem persistidos nas tabelas de apoio do armazenamento de consultas. Os dados que estão sendo registrados na memória para o período de tempo ativo no momento não são visíveis até que o período de tempo chegue ao fim, e seus dados voláteis na memória são coletados e persistidos para 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 bytes. |
|
plan_id |
BIGINT | ID do plano correspondente a essa consulta. | |
start_time |
timestamp | As consultas são agregadas por períodos de tempo. O parâmetro de servidor pg_qs.interval_length_minutes define o tempo de duração desses períodos (o padrão é 15 minutos). Essa coluna corresponde à hora de início do período no qual essa entrada foi registrada. |
|
end_time |
timestamp | 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, mais tantas outras unidades para cada processo de trabalho de back-end que é iniciado 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, mais a 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 da consulta. | |
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 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 do 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. |
|
search_path |
text | Valor de search_path definido no momento em que a consulta foi capturada. | |
query_parameters |
text | Representação de texto de um objeto JSON com os valores repassados para os parâmetros posicionais de uma consulta parametrizada. Essa coluna preenche seu valor apenas 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 puder buscar os valores para os parâmetros da consulta no momento da execução. |
|
parameters_capture_status |
text | Tipo de operação representado pela consulta. Os valores possíveis são succeeded (a consulta não foi parametrizada ou foi uma consulta parametrizada e os valores foram capturados com sucesso), disabled (a consulta foi parametrizada, mas os parâmetros não foram capturados porque pg_qs.parameters_capture_mode foi definido 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 revelado na coluna query_parameters dessa exibição, foi considerado excessivamente longo para que o armazenamento de consultas o persistisse), 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 os persistisse) serialization_failed (a consulta foi parametrizada, mas pelo menos um dos valores repassados como um parâmetro não pôde ser serializado para texto). |
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 períodos de tempo. O parâmetro de servidor pg_qs.interval_length_minutes define o tempo de duração desses períodos (o padrão é 15 minutos). Essa coluna corresponde à hora de início do período no qual essa entrada foi registrada. |
|
end_time |
timestamp | Hora de término correspondente à janela de tempo para esta entrada. | |
user_id |
oid | pg_authid.oid | Identificador de objeto do usuário que executou a instrução. |
db_id |
oid | 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 |
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. | |
calls |
Número inteiro | Número de vezes em que o mesmo evento foi capturado. |
Observação
Para obter uma lista de valores possíveis nas colunas event_type e event do modo de exibição query_store.pgms_wait_sampling_view, consulte a documentação oficial de 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. O armazenamento de consultas registra apenas os planos de consulta para consultas que não sejam de utilitários.
| Nome | Tipo | Referências | Descrição |
|---|---|---|---|
plan_id |
bigint | O valor do hash do plano de consulta normalizado produzido por EXPLAIN. Está em um formato normalizado porque exclui os custos estimados dos nós do plano e do 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. Saída idêntica à produzida por EXPLAIN. |
Funções
query_store.qs_reset
Essa função descarta todas as estatísticas coletadas até o momento pelo armazenamento de consultas. Descarta as estatísticas para períodos de tempo já fechados, que já foram persistidas nas tabelas em disco. Também descarta as estatísticas do período de tempo em curso, que existem apenas dentro da 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 consultas (ou seja, os dados na memória que ainda não foram liberados para as tabelas no disco que dão apoio à persistência dos dados coletados para o armazenamento de consultas). 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 PostgreSQL com Servidor Flexível estiver no modo somente leitura, como quando o parâmetro default_transaction_read_only estiver definido como on, ou se o modo somente leitura estiver habilitado automaticamente devido à capacidade de armazenamento ter atingido o limite de capacidade, o armazenamento de consultas não irá capturar nenhum dado.
Habilitar o armazenamento de consultas em um servidor que tem réplicas de leitura não habilita o armazenamento de consultas automaticamente em nenhuma das réplicas de leitura. Mesmo se você o habilitar em uma das réplicas de leitura, o armazenamento de consultas não registrará as consultas executadas em nenhuma réplica de leitura, porque estas operam no modo somente leitura até que você as promova para primárias.
Conteúdo relacionado
- Cenários de uso do repositório de consultas no Banco de Dados do Azure para PostgreSQL – Servidor Flexível.
- Práticas recomendadas para o repositório de consultas no Banco de Dados do Azure para PostgreSQL – Servidor Flexível.
- Análise de Desempenho de Consultas no Banco de Dados do Azure para PostgreSQL – Servidor Flexível.