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

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

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

É uma oferta interna no Banco de Dados do Azure para servidor flexível PostgreSQL, que se baseia no desempenho do Monitor com a funcionalidade de armazenamento 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 descartar índices duplicados ou não utilizados.

• Identifique quais índices são benéficos para criar porque eles podem melhorar significativamente as consultas analisadas durante uma sessão de ajuste de índice.
• Identifique os índices que são duplicados exatos e podem ser eliminados para reduzir o impacto no desempenho que sua existência e manutenção têm no desempenho geral do sistema.
• Identificar índices não utilizados num período configurável que possam ser candidatos a eliminar.

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

Quando o index_tuning.mode parâmetro server é configurado para report, as sessões de ajuste são iniciadas automaticamente com a frequência configurada no parâmetro index_tuning.analysis_intervalserver , expressa em minutos.

Na primeira fase, a sessão de ajuste procura a lista de bases de dados em que considera que quaisquer recomendações que possa produzir podem afetar significativamente o desempenho geral do sistema. Para fazer isso, ele coleta todas as consultas registradas pelo repositório de consultas cujas execuções foram capturadas dentro do intervalo de pesquisa no qual esta sessão de ajuste está se concentrando. Atualmente, o intervalo de pesquisa se estende até os minutos anteriores index_tuning.analysis_interval , a partir da hora de início da sessão de ajuste.

Para todas as consultas iniciadas pelo usuário com execuções gravadas no repositório de consultas e cujas estatísticas de tempo de execução não são redefinidas, o sistema as classifica com base em seu tempo total de execução agregado. Concentra a sua atenção nas consultas mais proeminentes, com base na sua duração.

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

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

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

CRIAR RECOMENDAÇÕES DE ÍNDICE

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 consideradas.

O conjunto resultante de consultas é classificado com base em seu tempo de execução total agregado, e o topo index_tuning.max_queries_per_database é analisado para possíveis recomendações de índice.

As recomendações potenciais visam melhorar o desempenho destes tipos de consultas:

• Consultas com filtros (ou seja, consultas com predicados na cláusula WHERE),
• Consultas que unem várias relações, se elas seguem a sintaxe na qual 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 que combinam filtros e predicados de junção.
• Consultas com agrupamento (consultas com uma cláusula GROUP BY).
• Consultas que combinam filtros e agrupamento.
• Consultas com classificação (consultas com uma cláusula ORDER BY).
• Consultas que combinam filtros e classificação.

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 reunir estatísticas pode ser acionada manualmente usando o comando ANALYZE ou automaticamente pelo daemon autovacuum.

index_tuning.max_indexes_per_table Especifica o número de índices que podem ser recomendados, excluindo quaisquer índices que já possam 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 analisado 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 não introduzam regressão em nenhuma consulta única nessa carga de trabalho de um fator especificado com index_tuning.max_regression_factor.

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

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

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

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

O ajuste do í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 constante "_idx". Se o comprimento total do nome exceder os limites do PostgreSQL ou se entrar em conflito com quaisquer relações existentes, o nome será ligeiramente diferente. Ele poderia ser truncado, e um número poderia ser anexado 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 (porcentagem).

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 existisse. 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 de 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, ele recomenda descartar ou reindexar índices existentes, com base nos seguintes critérios:

• Solte se for considerado duplicado dos outros.
• Solte se não for usado por um período de tempo configurável.
• Reindexar índices marcados como inválidos.

Eliminar índices duplicados

Recomendações para eliminar índices duplicados: Primeiro, identifique quais índices têm duplicados.

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 à do seu líder de referência e descreve por que cada duplicata foi classificada da forma como estava.

Para que dois índices sejam considerados duplicados, devem:

• Ser criado sobre a mesma tabela.
• Seja um índice exatamente do mesmo tipo.
• Corresponder às suas colunas de chave e, para chaves de índice de várias colunas, corresponder à ordem em que são referenciadas.
• Corresponder à árvore de expressão de seu predicado. Aplicável apenas a índices parciais.
• Corresponder à árvore de expressões de todas as referências de coluna não simples. Aplicável apenas a índices criados em expressões.
• Corresponder ao agrupamento de cada coluna referenciada na chave.

Eliminar índices não utilizados

As recomendações para eliminar os í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 index_tuning.unused_dml_per_table DMLs na tabela onde o índice é criado.
• Mostrar um número mínimo (média diária) de index_tuning.unused_reads_per_table leituras na tabela onde o índice é criado.

Reindexar índices inválidos

As recomendações para reindexar índices 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 o REINDEX na documentação oficial do PostgreSQL.

Calcular o impacto de uma recomendação DROP INDEX

O impacto de uma recomendação de índice de queda é medido em duas dimensões: Benefit (percentagem) 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.

Configurando o 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 de índice em como habilitar, desabilitar e configurar o ajuste de índice.

Informação produzida por afinação do índice

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

Limitações e capacidade de suporte

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

Camadas de computação e SKUs suportadas

O ajuste de índice é suportado em todas as camadas atualmente disponíveis: Burstable, General Purpose e Memory Optimized, e em qualquer SKU de computação atualmente suportada com pelo menos 4 vCores.

Versões suportadas do PostgreSQL

O ajuste de índice é suportado nas versões principais 12 ou superiores do Banco de Dados do Azure para Servidor Flexível PostgreSQL.

Utilização 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 que foi definido quando a consulta executada search_path originalmente é aquele para o qual ela está definida para analisar possíveis recomendações.

Consultas parametrizadas

As consultas parametrizadas criadas com PREPARE ou usando o protocolo de consulta estendido 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 captura 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 do armazenamento de consultas, que não é suportado em réplicas de leitura ou quando uma instância está no modo somente leitura, não oferecemos suporte a ele 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 da computação

Se o ajuste de índice estiver habilitado em um servidor e você reduzir 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 é suportado 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 reduzida. Embora o servidor não atenda aos requisitos mínimos, todos os index_tuning.* parâmetros do servidor estão inacessíveis. Sempre que você dimensionar o backup do servidor para uma computação que atenda aos requisitos mínimos, index_tuning.mode será configurado com qualquer valor que tenha sido definido antes de reduzi-lo para uma computação que não atendesse aos requisitos.

Alta disponibilidade e réplicas de leitura

Se você tiver réplicas de leitura ou de alta disponibilidade configuradas em seu servidor, esteja ciente das implicações associadas à produção de cargas de trabalho com 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údos relacionados

• Monitorar o desempenho com o repositório de consultas
• Habilitar, desabilitar e configurar o ajuste de índice
• Leia, interprete e use recomendações produzidas pelo ajuste de índice