Ajuste autônomo no servidor flexível do Banco de Dados do Azure para PostgreSQL

O ajuste autônomo é um recurso em Banco de Dados do Azure para PostgreSQL servidor flexível que analisa as consultas registradas de sua carga de trabalho e fornece recomendações para melhorar o desempenho dessas consultas.

É um recurso integrado no servidor flexível do Banco de Dados do Azure para PostgreSQL, baseado na funcionalidade do query store. O ajuste autônomo analisa a carga de trabalho monitorada pelo Repositório de Consultas e gera recomendações de índices ou tabelas para melhorar o desempenho da carga de trabalho analisada. Ele pode produzir recomendações para criar novos índices, eliminar índices duplicados ou não utilizados, analisar tabelas que não têm estatísticas ou estatísticas desatualizadas ou tabelas inchadas a vácuo.

• Identifique quais índices são benéficos para criar, pois eles podem melhorar significativamente as consultas analisadas durante uma sessão de ajuste autônomo.
• Identifique índices que são duplicatas exatas e que podem ser eliminados.
• Identifique índices não usados em um período configurável que podem ser candidatos à eliminação.
• Identifique índices marcados como inválidos que devem ser reindexados para transformá-los em válidos.
• Identifique tabelas que não têm estatísticas atuais que devem ser analisadas.
• Identifique tabelas que estão sobrecarregadas nas quais o vaccum deve ser aplicado.

Descrição geral do algoritmo de ajuste autônomo

Quando você configura o index_tuning.mode parâmetro para report, o sistema inicia automaticamente as sessões de ajuste na frequência configurada no index_tuning.analysis_interval parâmetro, expresso em minutos.

Na primeira fase, a sessão de ajuste pesquisa a lista de bancos de dados em que as recomendações podem afetar 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. Ele também pesquisa índices que você pode eliminar porque eles são duplicados ou não são usados por um período configurável de tempo. Ele também identifica tabelas sem estatísticas atuais ou tabelas inchadas.

Recomendações CREATE INDEX

Para cada banco de dados identificado como um candidato a ser analisado, o processo considera todas as consultas SELECT, UPDATE, INSERT e DELETE executadas durante o intervalo de pesquisa e no contexto desse banco de dados específico.

O processo classifica o conjunto resultante de consultas com base no tempo total de execução agregado e analisa a parte superior index_tuning.max_queries_per_database 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.

Se uma consulta fizer referência a uma coluna de uma tabela e essa tabela não tiver estatísticas, o processo não produzirá nenhuma recomendação de índice para melhorar sua execução. No entanto, ele gera uma recomendação para analisar a tabela.

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, o processo verifica todas as recomendações de índice 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.

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 junto 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, consulte a documentação oficial do PostgreSQL para CREATE INDEX.

O ajuste autônomo 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 identificado como um candidato, o processo inicia uma nova sessão. Após a conclusão da fase de recomendações de CREATE INDEX, recomenda-se 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.
• Reindexe os índices que estão marcados como inválidos.

Remover índices duplicados

As recomendações para remover índices duplicados começam identificando quais índices têm duplicatas.

As duplicatas são classificadas com base em diferentes funções que você pode atribuir ao índice e com base em seus tamanhos estimados.

O processo finalmente recomenda a remoção de todas as duplicatas com uma classificação mais baixa do que seu líder de referência e descreve por que cada duplicada foi classificada do jeito que era.

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

• Ser criados na mesma tabela.
• Ser um índice exatamente do mesmo tipo.
• Corresponda suas colunas-chave e, para as chaves de índice multicoluna, respeite a ordem em que elas são referenciadas.
• Corresponder à árvore de expressão de seu predicado. Essa condição só se aplica a índices parciais.
• Corresponder à árvore de expressão de todas as referências de coluna não simplificada. Essa condição só se aplica a índices criados em expressões.
• Corresponder à ordenação de cada coluna referenciada na chave.

Remover índices não utilizados

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 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 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 único valor que você pode ignorar 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.

Recomendações de tabela

Para cada banco de dados identificado como um candidato a ser analisado, o processo inicia uma sessão que visa produzir recomendações de nível de tabela. Essas recomendações sugerem executar ANALYZE ou VACUUM nas tabelas acessadas pelas consultas inspecionadas. O mecanismo de ajuste considera que a execução desses comandos pode melhorar o desempenho da carga de trabalho.

ANALISAR recomendações de tabela

Recomendações para analisar uma tabela identificam as tabelas que:

• São referenciados em uma consulta e têm alguma coluna dessa tabela usada em um de seus predicados (WHERE, , JOIN, ORDER BY), GROUP BYe também atendem a uma das duas seguintes condições:
  • Nunca são analisados.
  • Foram analisados em algum momento, mas agora não há estatísticas (normalmente porque o servidor falhou antes que as estatísticas fossem mantidas no disco).

Recomendações da tabela VACUUM

Recomendações para aplicar vacuum em uma tabela identificam aquelas tabelas que estão sobrecarregadas. O processo produz essas recomendações apenas quando autovacuum_enabled não está definido como off no nível do servidor, quando a carga de trabalho é analisada.

Configurando o ajuste autônomo

Você pode habilitar, desabilitar e configurar o ajuste autônomo por meio de um conjunto de parâmetros que controlam seu comportamento.

Quando você habilita o ajuste autônomo, ele acorda em uma frequência configurada no index_tuning.analysis_interval parâmetro (que usa como padrão 720 minutos ou 12 horas) e começa a analisar a carga de trabalho registrada pelo repositório de consultas durante esse período.

Se você alterar o valor de index_tuning.analysis_interval, o novo valor passará a vigorar somente depois que a próxima execução agendada for concluída. Por exemplo, se você habilitar o ajuste autônomo um dia às 10h, porque o valor index_tuning.analysis_interval padrão é 720 minutos, a primeira execução está agendada para começar às 22h do mesmo dia. As alterações feitas no valor entre index_tuning.analysis_interval 10h e 22h não afetam esse agendamento inicial. Somente quando a execução agendada for concluída, ela lerá o valor atual definido index_tuning.analysis_interval e agenda a próxima execução de acordo com esse valor.

Use as seguintes opções para configurar parâmetros de ajuste autônomo:

Se você usar os comandos az postgres flexible-server autonomous-tuning show-settings da CLI e az postgres flexible-server autonomous-tuning set-settings exibir ou modificar qualquer uma das configurações de ajuste autônomo, os valores aceitos como argumentos para o --name parâmetro serão os mostrados na coluna Parâmetro da tabela anterior, mas sem incluir o prefixo index_tuning..

Informações produzidas pelo ajuste autônomo

Usar recomendações de ajuste autônomo descreve detalhadamente como obter e usar as recomendações produzidas pelo ajuste autônomo.

Limitações e capacidade de suporte

A lista a seguir descreve as limitações e o escopo de suporte para ajuste autônomo.

Exclusão automática de recomendações

O sistema exclui automaticamente as recomendações 35 dias após a última vez em que as produziu. Para que esse mecanismo de exclusão automática funcione, você deve habilitar o ajuste autônomo.

Dependência da extensão hypopg

Para produzir recomendações CREATE INDEX, o ajuste autônomo usa a extensão hypopg.

Se a extensão existir quando uma sessão de ajuste for iniciada, o processo a usará no esquema em que foi criada. Quando a sessão de ajuste termina, o processo não remove a extensão. Uma exceção a essa regra é se a extensão foi criada no pg_catalog esquema. Se esse for o caso, o ajuste autônomo removerá a extensão.

Se a extensão não existia inicialmente ou se o processo a remover porque foi criada no esquema pg_catalog, o ajuste autônomo a criará em um esquema chamado ms_temp_recommendations709253. Quando a sessão de ajuste for concluída com êxito, o processo removerá a extensão e removerá o esquema.

Os usuários que são membros da função azure_pg_admin podem remover a extensão hypopg a qualquer momento, mesmo quando ela é criada pelo recurso de ajuste autônomo. No entanto, removê-la enquanto uma sessão de ajuste autônoma está em execução pode fazer com que essa sessão falhe e não produza nenhuma recomendação.

SKUs e camadas de computação com suporte

O servidor flexível do Azure Database para PostgreSQL oferece suporte a ajuste automático em todas as camadas atualmente disponíveis: Burstable, General Purpose e Memory Optimized. Também oferece suporte a ajuste autônomo em qualquer SKU de computação com suporte atualmente com pelo menos 4 vCores.

Versões com suporte do PostgreSQL

O servidor flexível do Banco de Dados do Azure para PostgreSQL oferece suporte ao ajuste autônomo em versões principais12 ou posteriores.

Uso de search_path

O ajuste autônomo usa o valor da coluna search_path de query_store.qs_view. Quando analisa cada consulta, ela usa o mesmo search_path valor que foi definido quando a consulta foi executada originalmente para analisar possíveis recomendações.

Consultas parametrizadas

Consultas parametrizadas criadas com PREPARE ou usando o protocolo de consulta estendida são processadas e analisadas para gerar recomendações de índices.

Para a análise de consultas parametrizadas, o ajuste autônomo requer que pg_qs.parameters_capture_mode seja configurado para capture_first_sample quando a Query Store captura a execução da consulta. Ele também requer que o repositório de consultas capture corretamente os parâmetros quando a consulta é executada. Em outras palavras, para a consulta que está sendo analisada, a parameters_capture_status coluna em query_store.qs_view deve ser definida como succeeded.

Modo somente leitura e réplicas de leitura

Como o ajuste autônomo depende dos dados que o repositório de consultas mantém localmente no banco de dados azure_sys e não é compatível com réplicas de leitura nem com servidores em modo somente leitura, o recurso não está disponível nesses cenários.

Todas as recomendações que você vê 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 você habilitar o ajuste autônomo em um servidor e reduzir a capacidade de computação desse servidor para menos que o número mínimo de vCores necessário, o recurso permanecerá habilitado. Como o recurso não tem suporte em servidores com menos de 4 vCores, ele não é executado para analisar a carga de trabalho e produzir recomendações, mesmo que index_tuning.mode tenha sido definido ON para quando você reduziu a computação. Embora o servidor não atenda aos requisitos mínimos, todos os index_tuning.* parâmetros são inacessíveis. Sempre que você dimensionar o servidor para uma computação que atenda aos requisitos mínimos, index_tuning.mode será configurado com o valor que estava definido antes do escalonamento para uma computação que não atendia aos requisitos.

Alta disponibilidade e réplicas de leitura

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

Razões pelas quais o ajuste autônomo pode não produzir recomendações de criação de índice para determinadas consultas

O ajuste autônomo não gera CREATE INDEX recomendações para os seguintes tipos de consultas:

• Consultas que encontram um erro quando o mecanismo de ajuste autônomo tenta obter sua saída EXPLAIN durante a fase de análise.
• Consultas que fazem referência a tabelas sem estatísticas sobre seu conteúdo no catálogo do pg_statistic sistema. Execute ANALYZE nessas tabelas para que o mecanismo de ajuste possa considerar essas consultas no futuro.
• Consultas com texto de consulta truncado no repositório de consultas. Esse truncamento ocorre quando o comprimento do texto da consulta excede o valor configurado em pg_qs.max_query_text_length.
• Consultas que fazem referência a objetos que você retirou ou renomeou antes da análise ocorrer. Essas consultas ainda podem ser sintaticamente válidas, mas não são semanticamente válidas.
• Consultas que acessam tabelas temporárias ou índices em tabelas temporárias.
• Consultas que acessam exibições ou exibições materializadas.
• Consultas que acessam tabelas particionadas.
• Consultas identificadas como instruções utilitárias. Instruções utilitárias ou comandos utilitários são, basicamente, qualquer instrução que não seja considerada SELECT, INSERT, UPDATE, DELETE ou MERGE, e certos comandos que contêm uma dessas instruções.
• Consultas que não estão entre as index_tuning.max_queries_per_database mais lentas no banco de dados e no período analisado.
• Consultas que são executadas no contexto de um banco de dados específico, quando nenhuma dessas consultas é identificada como a mais lenta no nível do servidor.

Conteúdo relacionado

• Repositório de consultas.
• Configurar o ajuste autônomo.
• Use recomendações de ajuste autônomo.