Compartilhar via

Ajuste de índice no Banco de Dados do Azure para PostgreSQL - Servidor Flexível

  • Artigo

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

O ajuste de índice é um recurso no servidor flexível do Banco de Dados do Azure para PostgreSQL que melhora automaticamente o desempenho da carga de trabalho analisando as consultas controladas e fornecendo recomendações de índice.

É uma oferta interna no servidor flexível do Banco de Dados do Azure para PostgreSQL, que se baseia na funcionalidade Monitorar o desempenho com o repositório de consultas. O ajuste de índice analisa a carga de trabalho controlada pelo repositório de consultas e produz recomendações de índice para melhorar o desempenho da carga de trabalho analisada ou para remover índices duplicados ou não utilizados.

Descrição geral do algoritmo de ajuste de índice

Quando o parâmetro do servidor index_tuning.mode é configurado como report, as sessões de ajuste serão iniciadas automaticamente com a frequência configurada no parâmetro do servidor index_tuning.analysis_interval, expressa em minutos.

Na primeira fase, a sessão de ajuste procura a lista de bancos de dados nos quais considera que quaisquer recomendações que possa produzir podem impactar significativamente o desempenho geral do sistema. Para isso, ele coleta todas as consultas registradas pelo repositório de consultas cujas execuções foram capturadas dentro do intervalo de pesquisa em que esta sessão de ajuste está se concentrando. O intervalo de pesquisa atualmente se estende até os últimos index_tuning.analysis_interval minutos, desde o tempo inicial da sessão de ajuste.

Para todas as consultas iniciadas pelo usuário com execuções registradas no repositório de consultas e cujas estatísticas de runtime não estão redefinidas, o sistema as classifica com base no tempo de execução total agregado. Ele concentra sua atenção nas consultas mais proeminentes, com base em sua duração.

As seguintes consultas são excluídas dessa lista:

  • Consultas iniciadas pelo sistema. (ou seja, as consultas executadas pela função azuresu)
  • Consultas executadas no contexto de qualquer banco de dados do sistema (azure_sys, template0, template1 e azure_maintenance).

O algoritmo itera nos bancos de dados de destino, procurando os índices possíveis que possam melhorar o desempenho das cargas de trabalho analisadas. Também pesquisa índices que podem ser eliminados porque estão identificados como duplicados ou não são usados por um período de tempo configurável.

Recomendações CREATE INDEX

Para cada banco de dados identificado como um candidato a ser analisado para produzir recomendações de índice, todas as consultas SELECT, UPDATE, INSERT e DELETE executadas durante o intervalo de pesquisa e no contexto desse banco de dados específico são levadas em consideração.

Observação

O ajuste de índice analisa não apenas instruções SELECT, mas também instruções DML (UPDATE, INSERT e DELETE).

O conjunto resultante de consultas é classificado com base no tempo de execução total agregado e as mais importantes index_tuning.max_queries_per_database são analisadas para possíveis recomendações de índice.

As possíveis recomendações visam melhorar o desempenho destes tipos de consultas:

  • Consultas com filtros (ou seja, consultas com predicados na cláusula WHERE),
  • Consultas que unem múltiplas relações, quer sigam a sintaxe em que as junções são expressas com a cláusula JOIN ou se os predicados de junção são expressos na cláusula WHERE.
  • Consultas combinando filtros e predicados de junção.
  • Consultas com agrupamento (consultas com uma cláusula GROUP BY).
  • Consultas combinando filtros e agrupamento.
  • Consultas com classificação (consultas com uma cláusula ORDER BY).
  • Consultas combinando filtros e classificação.

Observação

O único tipo de índice que o sistema recomenda atualmente são os do tipo Árvore B.

Se uma consulta fizer referência a uma coluna de uma tabela e essa tabela não tiver estatísticas, ela ignorará toda a consulta e não produzirá nenhuma recomendação de índice para melhorar sua execução.

A análise necessária para coletar estatísticas pode ser disparada manualmente usando o comando ANALYZE ou automaticamente pelo daemon de vácuo automático.

index_tuning.max_indexes_per_table especifica o número de índices que podem ser recomendados, excluindo todos os índices que já podem existir na tabela para qualquer tabela única referenciada por qualquer número de consultas durante uma sessão de ajuste.

index_tuning.max_index_count especifica o número de recomendações de índice produzidas para todas as tabelas de qualquer banco de dados analisadas durante uma sessão de ajuste.

Para que uma recomendação de índice seja emitida, o mecanismo de ajuste deve estimar que melhora pelo menos uma consulta na carga de trabalho analisada por um fator especificado com index_tuning.min_improvement_factor.

Da mesma forma, todas as recomendações de índice são verificadas para garantir que elas não introduzam regressão em nenhuma consulta única nessa carga de trabalho de um fator especificado com index_tuning.max_regression_factor.

Observação

index_tuning.min_improvement_factor e index_tuning.max_regression_factor se referem ao custo dos planos de consulta, não à duração ou aos recursos que consomem durante a execução.

Todos os parâmetros mencionados nos parágrafos anteriores, seus valores padrão e intervalos válidos são descritos nas opções de configuração.

O script produzido juntamente com a recomendação para criar um índice segue este padrão:

create index concurrently {indexName} on {schema}.{table}({column_name}[, ...])

Ele inclui a cláusula concurrently. Para obter mais informações sobre os efeitos dessa cláusula, visite a documentação oficial do PostgreSQL para CREATE INDEX.

O ajuste de índice gera automaticamente os nomes dos índices recomendados, que normalmente consistem nos nomes das diferentes colunas de chave separadas por "_" (sublinhados) e com um sufixo "_idx" constante. Se o comprimento total do nome exceder os limites do PostgreSQL ou se estiver em conflito com as relações existentes, o nome será ligeiramente diferente. Pode ser truncado e um número pode ser acrescentado ao final do nome.

Calcular o impacto de uma recomendação CREATE INDEX

O impacto da criação de uma recomendação de índice é medido em IndexSize (megabytes) e QueryCostImprovement (percentual).

IndexSize é um valor único que representa o tamanho estimado do índice, considerando a cardinalidade atual da tabela e o tamanho das colunas referenciadas pelo índice recomendado.

QueryCostImprovement consiste em uma matriz de valores, onde cada elemento representa a melhoria no custo do plano para cada consulta cujo custo do plano é estimado para melhorar se esse índice existir. Cada elemento mostra o identificador da consulta (consultado) e a porcentagem pela qual o custo do plano melhoraria se a recomendação fosse implementada (dimensional).

Recomendações DROP INDEX e REINDEX

Para cada banco de dados para o qual a funcionalidade de ajuste de índice é determinada, ele deve iniciar uma nova sessão e, após a conclusão da fase de recomendações CREATE INDEX, é recomendável remover ou reindexar índices existentes, com base nos seguintes critérios:

  • Solte se for considerado duplicado de outras pessoas.
  • Solte se ele não for usado por um período configurável de tempo.
  • Índices reindex que são marcados como inválidos.

Remover índices duplicados

Recomendações para remover índices duplicados: primeiro, identifique quais índices têm duplicatas.

As duplicatas são classificadas com base em diferentes funções que podem ser atribuídas ao índice e com base em seus tamanhos estimados.

Por fim, recomenda eliminar todas as duplicatas com uma classificação inferior à de seu líder de referência e descreve por que cada duplicata foi classificada daquela maneira.

Para que dois índices sejam considerados duplicatas, eles devem:

  • Ser criados na mesma tabela.
  • Ser um índice exatamente do mesmo tipo.
  • Corresponder às colunas de chave e, para as chaves de índice de várias colunas, corresponder à ordem em que elas são referenciadas.
  • Corresponder à árvore de expressão de seu predicado. Aplicáveis somente a índices parciais.
  • Corresponder à árvore de expressão de todas as referências de coluna não simplificada. Aplicáveis somente a índices criados em expressões.
  • Corresponder à ordenação de cada coluna referenciada na chave.

Remover índices não utilizados

As recomendações para remover índices não utilizados identificam os índices que:

  • Não são usados por pelo menos index_tuning.unused_min_period dias.
  • Mostrar um número mínimo (média diária) de DMLs index_tuning.unused_dml_per_table na tabela em que o índice é criado.
  • Mostrar um número mínimo (média diária) de leituras de index_tuning.unused_reads_per_table na tabela em que o índice é criado.

Índices reindex inválidos

Recomendações para índices reindex existentes identificam os índices marcados como inválidos. Para saber mais sobre por que e quando os índices são marcados como inválidos, consulte a documentação oficial REINDEX no PostgreSQL.

Calculam o impacto de uma recomendação DROP INDEX

O impacto de uma recomendação para remover índice é medido em duas dimensões: Benefício (percentual) e IndexSize (megabytes).

O benefício é um valor único que pode ser ignorado por enquanto.

IndexSize é um valor único que representa o tamanho estimado do índice, considerando a cardinalidade atual da tabela e o tamanho das colunas referenciadas pelo índice recomendado.

Configuração do ajuste de índice

O ajuste de índice pode ser habilitado, desabilitado e configurado por meio de um conjunto de parâmetros que controlam seu comportamento, como a frequência com que uma sessão de ajuste pode ser executada.

Explore todos os detalhes sobre a configuração correta do recurso de ajuste do índice em como habilitar, desabilitar e configurar o ajuste de índice.

Informações produzidas pelo ajuste de índice

Como ler, interpretar e usar recomendações produzidas pelo ajuste de índice descreve detalhadamente como obter e usar as recomendações produzidas pelo ajuste de índice.

Limitações e capacidade de suporte

Veja a seguir a lista de limitações e o escopo de suporte para ajuste de índice.

SKUs e camadas de computação com suporte

Há suporte para ajuste de índice em todas as camadas disponíveis no momento: Com capacidade de intermitência, Uso geral e Otimizado para memória, e em qualquer SKU de computação com suporte no momento com pelo menos 4 vCores.

Versões com suporte do PostgreSQL

Há suporte para ajuste de índice nas versões principais 12 ou superiores do Servidor Flexível do Banco de Dados do Azure para PostgreSQL.

Uso de search_path

O ajuste de índice consome o valor persistido na coluna search_path de query_store.qs_view, de modo que, quando cada consulta é analisada, o mesmo valor de search_path que foi definido quando a consulta executada originalmente é aquela para a qual ela está definida para analisar as recomendações possíveis.

Consultas parametrizadas

Consultas parametrizadas criadas com PREPARE ou usando o protocolo de consulta estendida são analisadas e analisadas para produzir recomendações de índice sobre elas.

Para a análise de consultas parametrizadas, o ajuste de índice requer que pg_qs.parameters_capture_mode seja definido como capture_first_sample quando o repositório de consultas capturar a execução da consulta. Ele também requer que os parâmetros sejam capturados corretamente pelo repositório de consultas quando a consulta é executada. Em outras palavras, para a consulta que está sendo analisada, query_store.qs_view deve ter sua coluna parameters_capture_status definida como succeeded.

Modo somente leitura e réplicas de leitura

Como o ajuste de índice depende de repositório de consultas, que não tem suporte em réplicas de leitura ou quando uma instância está no modo somente leitura, não há suporte para ela em réplicas de leitura ou em instâncias que estão no modo somente leitura.

Todas as recomendações vistas em uma réplica de leitura foram produzidas na réplica primária depois de analisar exclusivamente a carga de trabalho executada na réplica primária.

Redução horizontal da computação

Se o ajuste de índice estiver habilitado em um servidor e você reduzir verticalmente a computação desse servidor para menos do que o número mínimo de vCores necessários, o recurso permanecerá habilitado. Como o recurso não tem suporte em servidores com menos de 4 vCores, ele não será executado para analisar a carga de trabalho e produzir recomendações, mesmo que tenha sido index_tuning.mode definido para ON quando a computação foi horizontal. Embora o servidor não atenda aos requisitos mínimos, todos os parâmetros de servidor index_tuning.* estão inacessíveis. Sempre que você dimensionar o servidor de volta para uma computação que atenda aos requisitos mínimos, index_tuning.mode será configurado com qualquer valor definido antes de dimensioná-lo para uma computação que não atenda aos requisitos.

Alta disponibilidade e réplicas de leitura

Se você tiver alta disponibilidade ou réplicas de leitura configuradas em seu servidor, esteja ciente das implicações associadas à produção de cargas de trabalho com o uso intensivo de gravação no servidor primário ao implementar os índices recomendados. Tenha especial cuidado ao criar índices cujo tamanho é estimado como grande.

Conteúdo relacionado