Otimização autónoma no Base de Dados do Azure para PostgreSQL flexible server

O ajuste autónomo é uma funcionalidade do Base de Dados do Azure para PostgreSQL, um servidor flexível que analisa consultas registadas na sua carga de trabalho e fornece recomendações para melhorar o desempenho dessas consultas.

É uma oferta integrada no Base de Dados do Azure para PostgreSQL, um servidor flexível que se baseia na funcionalidade da loja de consultas. A otimização autónoma analisa a carga de trabalho monitorizada pelo arquivo de consultas e gera recomendações de índices ou tabelas para melhorar o desempenho da carga de trabalho analisada. Pode produzir recomendações para criar novos índices, eliminar índices duplicados ou não utilizados, analisar tabelas sem estatísticas ou estatísticas desatualizadas, ou inchar tabelas a vácuo.

• Identifique quais índices são benéficos de criar porque podem melhorar significativamente as consultas analisadas durante uma sessão autónoma de ajuste.
• Identifique índices que sejam duplicados exatos e que possam ser eliminados.
• Identificar índices não utilizados num período configurável que possam ser candidatos a eliminar.
• Identifique índices marcados como inválidos que devem ser reindexados para os transformar em índices válidos.
• Identifique tabelas que não possuem estatísticas atuais e que devam ser analisadas.
• Identifica tabelas inchadas que devem ser otimizadas.

Descrição geral do algoritmo de afinação autónoma

Quando configuras o index_tuning.mode parâmetro para report, o sistema começa automaticamente a sintonizar as sessões na frequência que configuras no index_tuning.analysis_interval parâmetro, expressa em minutos.

Na primeira fase, a sessão de afinação procura a lista de bases de dados onde as recomendações podem afetar significativamente o desempenho global 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 consulta abrange os index_tuning.analysis_interval minutos anteriores, 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 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 sobre os bancos de dados de destino, procurando possíveis índices que possam melhorar o desempenho das cargas de trabalho analisadas. Também procura índices que podes eliminar porque são duplicados ou não usados durante um período de tempo configurável. Identifica também tabelas sem estatísticas atuais ou tabelas inchadas.

Recomendações para CREATE INDEX

Para cada base de dados identificada como candidata a analisar, o processo considera todas as consultas SELECT, UPDATE, INSERT e DELETE executadas durante o intervalo de consulta e no contexto dessa base de dados específica.

O processo classifica o conjunto resultante de consultas com base no seu tempo total agregado de execução e analisa o topo index_tuning.max_queries_per_database para possíveis recomendações de índice.

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

• Consultas com filtros (isto é, 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 referenciar uma coluna de uma tabela e essa tabela não tiver estatísticas, o processo não produz quaisquer recomendações de índice para melhorar a sua execução. No entanto, 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 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, o processo verifica todas as recomendações de índices para garantir que não introduzem regressão numa única consulta nessa carga de trabalho de um fator especificado com index_tuning.max_regression_factor.

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

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

CREATE INDEX CONCURRENTLY {indexName} ON {schema}.{table}({column_name}[, ...])

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

A afinação autónoma 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 base de dados identificada como candidata, o processo inicia uma nova sessão. Após a conclusão da fase de recomendações CREATE INDEX, recomenda-se remover ou reindexar os índices existentes, com base nos seguintes critérios:

• Ignore se for considerado duplicado de outros.
• Eliminar caso não seja utilizado durante um período de tempo configurável.
• Reindexar índices marcados como inválidos.

Eliminar índices duplicados

As recomendações para eliminar índices duplicados começam por identificar quais os índices que têm duplicados.

Os duplicados são classificados com base em diferentes funções que pode atribuir ao índice e com base nos seus tamanhos estimados.

O processo recomenda finalmente eliminar todos os duplicados com uma classificação inferior à do líder de referência e descreve porque cada duplicado foi classificado da forma como foi.

Para que dois índices sejam considerados duplicados, devem:

• Ser criado sobre a mesma tabela.
• Seja um índice exatamente do mesmo tipo.
• Faça corresponder as respetivas colunas-chave e, no caso de chaves de índice de várias colunas, faça corresponder a ordem pela qual são referenciadas.
• Corresponde à árvore de expressão do respetivo predicado. Esta condição aplica-se apenas a índices parciais.
• Fazer corresponder à árvore de expressões todas as referências não simples a colunas. Esta condição aplica-se apenas a índices criados em expressões.
• Fazer corresponder a ordenação de cada coluna referida na chave.

Eliminar índices não utilizados

As recomendações para eliminar índices não utilizados identificam aqueles í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 aqueles que estão marcados como inválidos. Para saber mais sobre o motivo 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 de DROP INDEX

O impacto de uma recomendação para eliminar um índice é avaliado em duas dimensões: Benefício (percentagem) e Tamanho do índice (megabytes).

O benefício é um único valor que podes ignorar por agora.

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 tabelas

Para cada base de dados identificada como candidata a análise, o processo inicia uma sessão que visa produzir recomendações ao nível da tabela. Estas recomendações sugerem que execute ANALYZE ou VACUUM nas tabelas a que as consultas inspecionadas acedem. O motor de afinação considera que executar estes comandos pode melhorar o desempenho da sua carga de trabalho.

ANALISAR recomendações de tabela

Recomendações para análise de uma tabela Identifique aquelas tabelas que:

• Estão referenciados numa consulta, e têm alguma coluna dessa tabela usada num dos seus predicados (WHERE, JOIN, ORDER BY, GROUP BY), e também cumprem uma das duas condições seguintes:
  • Nunca são analisados.
  • Foram analisados em algum momento, mas agora faltam estatísticas (tipicamente porque o servidor crashou antes das estatísticas serem mantidas no disco).

Recomendações para a tabela VACUUM

Recomendações para aplicar compressão a uma tabela identificam aquelas tabelas que estão sobrecarregadas. O processo só produz estas recomendações quando autovacuum_enabled não está definido para off ao nível do servidor quando a carga de trabalho é analisada.

Configuração da afinação autónoma

Pode ativar, desativar e configurar a afinação autónoma através de um conjunto de parâmetros que controlam o seu comportamento.

Quando ativas a sintonia autónoma, ele acorda numa frequência configurada no index_tuning.analysis_interval parâmetro (que por defeito é de 720 minutos ou 12 horas) e começa a analisar a carga de trabalho registada pela loja de consultas durante esse período.

Se alterar o valor para index_tuning.analysis_interval, o novo valor só entra em vigor após a conclusão da próxima execução agendada. Por exemplo, se ativares a sintonia autónoma um dia às 10:00, porque o valor padrão para index_tuning.analysis_interval é 720 minutos, a primeira execução está agendada para começar às 22:00 desse mesmo dia. Quaisquer alterações que faças ao valor entre index_tuning.analysis_interval as 10:00 e as 22:00 não afetam esse horário inicial. Só quando a execução agendada termina é que lê o valor atual definido para 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 afinação autónoma:

Se usar os comandos az postgres flexible-server autonomous-tuning show-settings CLI e az postgres flexible-server autonomous-tuning set-settings para mostrar ou modificar qualquer uma das definições autónomas de afinação, os valores aceites como argumentos para o --name parâmetro são os mostrados na coluna Parâmetro da tabela anterior, mas sem incluir o prefixo index_tuning..

Informação produzida por ajuste autónomo

As recomendações de afinação autónoma descrevem em detalhe como obter e utilizar essas recomendações produzidas pelo processo de afinação autónoma.

Limitações e capacidade de suporte

A lista seguinte descreve as limitações e o âmbito de suporte para a sintonia autónoma.

Supressão automática de recomendações

O sistema apaga automaticamente as recomendações 35 dias após a última vez que as gerou. Para que este mecanismo de eliminação automática funcione, tem de ativar o ajuste automático.

Dependência da extensão hipopg

Para produzir CREATE INDEX recomendações, o ajuste automático utiliza a extensão hypopg.

Se a extensão existir quando uma sessão de afinação começa, o processo utiliza-a no esquema onde foi criada. Quando a sessão de afinação termina, o processo não elimina a extensão. Uma exceção a esta regra é se a extensão foi criada no pg_catalog esquema. Se for esse o caso, a afinação autónoma elimina a extensão.

Se a extensão nem sequer existisse à partida ou se o processo a eliminasse por ter sido criada no esquema pg_catalog, a otimização autónoma cria-a num esquema chamado ms_temp_recommendations709253. Quando a sessão de afinação termina com sucesso, o processo elimina a extensão e remove o esquema.

Os utilizadores que são membros da função azure_pg_admin podem eliminar a extensão hypopg a qualquer momento, mesmo quando foi a funcionalidade de ajuste automático que a criou. No entanto, interrompê-lo enquanto uma sessão de afinação autónoma está a decorrer pode fazer com que essa sessão falhe e não produza recomendações.

Escalões de computação e SKUs suportados

O servidor flexível Base de Dados do Azure para PostgreSQL suporta afinação autónoma em todos os níveis atualmente disponíveis: Burstable, Uso Geral e Otimização de Memória. Também suporta otimização autónoma em qualquer SKU de computação atualmente suportado com pelo menos 4 vCores.

Versões suportadas do PostgreSQL

O servidor flexível Base de Dados do Azure para PostgreSQL suporta otimização autónoma nas versões principais12 ou superiores.

Utilização de search_path

O ajuste automático utiliza o valor da coluna search_path de query_store.qs_view. Quando analisa cada consulta, utiliza o mesmo search_path valor que foi definido quando a consulta foi originalmente executada para analisar possíveis recomendações.

Consultas parametrizadas

As consultas parametrizadas criadas com PREPARE ou utilizando o protocolo de consulta estendida são processadas sintaticamente 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 definido para capture_first_sample quando o armazém de consultas captura a execução da consulta. Também exige que o armazenamento de consultas capture corretamente os parâmetros quando a consulta é executada. Por outras palavras, para a consulta a analisar, a parameters_capture_status coluna em query_store.qs_view deve ser definida como succeeded.

Modo de apenas leitura e réplicas de leitura

Como o ajuste autónomo depende dos dados que o Query Store mantém localmente na base de dados azure_sys, e as réplicas de leitura ou os servidores em modo só de leitura não são suportados, a funcionalidade não é suportada em réplicas de leitura nem em servidores em modo só de leitura.

Quaisquer recomendações que veja numa réplica de leitura foram produzidas na réplica primária após analisar exclusivamente a carga de trabalho que foi executada na réplica primária.

Redução da capacidade de computação

Se ativares a sintonia autónoma num servidor e depois reduzires o cálculo desse servidor para menos do que o número mínimo de vCore necessários, a funcionalidade mantém-se ativada. Como a funcionalidade não é suportada em servidores com menos de 4 vCores, não é executada para analisar a carga de trabalho e gerar recomendações, mesmo que index_tuning.mode estivesse definida para ON quando reduziu a computação. Embora o servidor não cumpra os requisitos mínimos, todos index_tuning.* os parâmetros são inacessíveis. Sempre que escalas o teu servidor de volta para uma computação que cumpra os requisitos mínimos, index_tuning.mode está configurado com o valor que foi definido antes de o reduzires para uma computação que não cumpria os requisitos.

Alta disponibilidade e réplicas de leitura

Se configurar alta disponibilidade ou réplicas de leitura no seu servidor, tenha em conta as implicações associadas à execução de cargas de trabalho com utilização intensiva de escrita no servidor primário ao implementar os índices recomendados. Tenha especial cuidado ao criar índices cujo tamanho é estimado como grande.

Razões pelas quais a afinação autónoma pode não produzir recomendações de índices para certas consultas

A afinação autónoma não gera CREATE INDEX recomendações para os seguintes tipos de consultas:

• Consultas que encontram um erro quando o motor de afinação autónoma tenta obter a sua saída EXPLAIN durante a fase de análise.
• Consultas que referenciam tabelas sem estatísticas sobre o seu conteúdo no pg_statistic catálogo do sistema. Execute ANALYZE nessas tabelas para que o motor de afinação possa considerar estas consultas no futuro.
• Consultas com texto de consulta truncado na loja de consultas. Este truncamento ocorre quando o comprimento do texto da consulta excede o valor configurado em pg_qs.max_query_text_length.
• Consultas que referenciam objetos que eliminaste ou renomeaste antes da análise ocorrer. Estas consultas ainda podem ser sintaticamente válidas, mas não são semanticamente válidas.
• Consultas que acedem a tabelas temporárias ou a índices dessas tabelas.
• Consultas que acedam a visualizações ou visualizações materializadas.
• Consultas que acedem a 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 contenham uma destas instruções.
• Consultas que não estão entre as index_tuning.max_queries_per_database mais lentas, na base de dados e no período analisados.
• Consultas que correm no contexto de uma base de dados específica, quando nenhuma dessas consultas é identificada como a mais lenta ao nível do servidor.

Conteúdo relacionado

• Query Store.
• Configurar a afinação autónoma.
• Use recomendações de ajuste automático.