Monitorar o desempenho com o Repositório de Consultas

APLICA-SE A: Banco de Dados do Azure para PostgreSQL – Servidor Flexível

O recurso de Repositório de Consultas no 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 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.

Habilitando o Repositório de Consultas

O Repositório de Consultas é um recurso que requer aceitação, portanto, ele não está habilitado em um servidor por padrão. O repositório de consultas é 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.

Habilitar o Repositório de Consultas usando o portal do Azure

  1. Entre no portal do Azure e selecione seu servidor do Banco de Dados do Azure para PostgreSQL.
  2. Selecione Parâmetros de Servidor na seção Configurações do menu.
  3. Pesquise o parâmetro pg_qs.query_capture_mode.
  4. Defina o valor como TOP ou ALL e clique em Salvar. Permita que o primeiro lote de dados persista no banco de dados azure_sys por até 20 minutos. Para habilitar as estatísticas de espera no seu Repositório de Consultas:
  5. Pesquise o parâmetro pgms_wait_sampling.query_capture_mode.
  6. Defina o valor como ALL e Salve.

Informações no Repositório de Consultas

O Repositório de Consultas tem 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
  • Comparar o tempo médio de execução de uma consulta entre janelas de tempo para ver grandes deltas
  • Identificar consultas de execução mais longas nas últimas horas
  • Identificar as principais N consultas que estão aguardando recursos
  • Entendendo a natureza de espera para uma consulta particular 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 no servidor Postgres. 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;

Localizando 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 dados em janelas de agregação de 15 minutos, até 500 consultas distintas por janela. 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.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 de consulta que pode ser salvo. Consultas mais longas serão truncadas. 6000 100 a 10 mil
pg_qs.retention_period_in_days Define o período de retenção. 7 1 a 30
pg_qs.index_generation_interval Define a recomendação do índice gerando frequência para todos os bancos de dados quando o repositório de consultas está habilitado. 15 15 - 10080
pg_qs.track_utility Define se os comandos do utilitário são rastreados on on, off

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 Define quais instruções são rastreadas para as estatísticas de espera. nenhum none, all
Pgms_wait_sampling.history_period Define a frequência, em milissegundos, com a qual são realizadas amostras dos eventos de espera. 100 1 a 600000

[!NOTE] pg_qs.query_capture_mode sobrepondo-se ao 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. Consultas são normalizadas examinando sua estrutura após a remoção de literais e constantes. Se duas consultas forem idênticas, exceto por valores literais, elas terão a mesma queryId.

query_store.qs_view

Essa exibição retorna todos os dados no Repositório de Consultas. Há uma linha para cada ID de banco de dados, ID de usuário e ID de consulta distinta.

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, computado 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.
plan_id BIGINT ID do plano correspondente a essa consulta
start_time timestamp Consultas são agregadas por buckets de tempo: o período de um bucket é de 15 minutos por padrão. Essa é a hora de início correspondente ao bucket de tempo para esta entrada.
end_time timestamp Hora de término correspondente ao bucket de tempo para esta entrada.
chamadas BIGINT Número de vezes que a consulta foi executada
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
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 sujos 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 sujos 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)

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_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_store.pgms_wait_sampling_view

Essa exibição retorna os dados de eventos de espera no Repositório de Consultas. Há uma linha para cada ID de banco de dados, ID de usuário, ID de consulta e evento distinto.

Nome Tipo Referências Descrição
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, computado 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 Integer Número do mesmo evento capturado

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ários.

plan_id db_id query_id plan_text
plan_id BIGINT O valor de hash do query_text
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, computado da árvore de análise da instrução
plan_text varchar (10000) Plano de execução da instrução dado costs=false, buffers=false e format=false. Essa é a mesma saída dada por EXPLAIN.

Funções

qs_reset descarta todas as estatísticas coletadas até o momento pelo Repositório de Consultas. Essa função só pode ser executada pela função de administrador de servidor.

staging_data_reset descarta todas as estatísticas coletadas na memória pelo Repositório de Consultas (isto é, os dados na memória que ainda não foram liberados para o banco de dados). Essa função só pode ser executada pela função de administrador de servidor.

Limitações e problemas conhecidos

  • Se um servidor PostgreSQL tem o parâmetro default_transaction_read_only ativo, o Repositório de Consultas não irá capturar os dados.

Próximas etapas