Ajustando o desempenho do banco de dados - os desenvolvedores geralmente adoram ou odeiam. Gosto disso e quero compartilhar alguns dos métodos que usei recentemente para ajustar consultas mal executadas no PostgreSQL. Meus métodos não são exaustivos, mas sim um livro didático para quem apenas se diverte com o ajuste.
Pesquise consultas lentas
A primeira maneira óbvia de iniciar o ajuste é encontrar operadores específicos que funcionam mal.
pg_stats_statements
O módulo
pg_stats_statements é um ótimo lugar para começar. Ele apenas acompanha as estatísticas de execução das instruções SQL e pode ser uma maneira fácil de encontrar consultas ineficientes.
Depois de instalar este módulo, uma visualização do sistema chamada
pg_stat_statements estará disponível com todas as suas propriedades. Quando ele tiver a oportunidade de coletar dados suficientes, procure consultas que tenham um valor
total_time relativamente alto
. Concentre-se nesses operadores primeiro.
SELECT * FROM pg_stat_statements ORDER BY total_time DESC;
auto_explain
O módulo
auto_explain também
é útil para encontrar consultas lentas, mas possui 2 vantagens óbvias: registra o plano de execução real e suporta a gravação de instruções aninhadas usando a opção
log_nested_statements . Instruções aninhadas são instruções executadas dentro de uma função. Se seu aplicativo usa muitos recursos, a auto_explain é inestimável para obter planos de execução detalhados.
A opção
log_min_duration controla quais planos de execução de consulta são registrados com base em quanto tempo eles são executados. Por exemplo, se você definir o valor como 1000, todos os registros que levarem mais de 1 segundo serão registrados.
Ajuste do índice
Outra estratégia importante de ajuste é garantir que os índices sejam usados corretamente. Como pré-requisito, precisamos incluir o coletor de estatísticas.
O Postgres Statistics Collector é um subsistema de primeira classe que coleta todos os tipos de estatísticas de desempenho úteis.
Ao ativar esse coletor, você obtém
várias visualizações pg_stat _... que contêm todas as propriedades. Em particular, achei isso especialmente útil para encontrar índices ausentes e não utilizados.
Índices ausentes
Os índices ausentes podem ser uma das soluções mais fáceis para melhorar o desempenho da consulta. No entanto, eles não são uma bala de prata e devem ser usados corretamente (mais sobre isso mais tarde). Se você tiver o coletor de estatísticas ativado, poderá executar a seguinte consulta (
origem ).
SELECT relname, seq_scan - idx_scan AS too_much_seq, CASE WHEN seq_scan - coalesce(idx_scan, 0) > 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC;
A consulta localiza tabelas que têm mais varreduras seqüenciais (varreduras de índice) do que varreduras de índice - uma indicação clara de que o índice ajudará. Isso não informará em quais colunas criar o índice, portanto, será um pouco mais trabalhoso. No entanto, saber quais tabelas precisam delas é um bom primeiro passo.
Índices não utilizados
Indexar todas as entidades, certo? Você sabia que índices não utilizados podem afetar adversamente o desempenho da gravação? O motivo é que, ao criar o índice do Postgres, ele fica sobrecarregado com a tarefa de atualizar esse índice após as operações de gravação (INSERT / UPDATE / DELETE). Assim, adicionar um índice é um ato de equilíbrio, pois pode acelerar a leitura dos dados (se eles foram criados corretamente), mas diminuirá a velocidade das operações de gravação. Para encontrar índices não utilizados, você pode executar a seguinte consulta.
SELECT indexrelid::regclass as index, relid::regclass as table, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0 AND indisunique is false;
Nota sobre estatísticas do ambiente de desenvolvimento
Confiar nas estatísticas de um banco de dados de desenvolvimento local pode ser problemático. Idealmente, você pode obter as estatísticas acima da sua máquina de trabalho ou gerá-las a partir de um backup de trabalho restaurado. Porque Fatores ambientais podem alterar o comportamento do otimizador de consultas do Postgres. Dois exemplos:
- quando a máquina tem menos memória, o PostgreSQL pode não ser capaz de executar uma junção Hash, caso contrário, pode e fará mais rápido.
- se não houver tantas linhas na tabela (como no banco de dados de desenvolvimento), o PostgresSQL pode preferir executar uma varredura seqüencial da tabela em vez de usar um índice disponível. Quando os tamanhos das tabelas são pequenos, o Seq Scan pode ser mais rápido. (Nota: você pode executar
SET enable_seqscan = OFF
em uma sessão para que o otimizador opte por usar índices, mesmo que as verificações sequenciais possam ser mais rápidas. Isso é útil ao trabalhar com bancos de dados de desenvolvimento que não possuem muitos dados)
Noções básicas sobre planos de execução
Agora que você encontrou algumas consultas lentas, é hora de começar a diversão.
EXPLIQUE
O comando
EXPLAIN certamente é necessário ao configurar consultas. Ele diz o que realmente está acontecendo. Para usá-lo, basta adicionar
EXPLAIN à consulta e executá-la. O PostgreSQL mostrará o plano de execução que ele usou.
Ao usar EXPLAIN para ajuste, eu recomendo sempre a opção
ANALYZE (
EXPLAIN ANALYZE ), pois ela fornece resultados mais precisos. A opção ANALYZE realmente executa a instrução (em vez de apenas avaliá-la) e, em seguida, explica.
Vamos dar um mergulho e começar a entender a saída de
EXPLAIN . Aqui está um exemplo:
Nós
A primeira coisa a entender é que cada bloco recuado com o “->” anterior (junto com a linha superior) é chamado de nó. Um nó é uma unidade lógica de trabalho (uma "etapa", se você preferir) com custo e lead time associados. O custo e o tempo apresentados em cada nó são cumulativos e reúnem todos os nós filhos. Isso significa que a linha superior (nó) mostra o custo total e o tempo real para todo o operador. Isso é importante porque você pode pesquisar facilmente para determinar quais nós são o gargalo.
Custo
cost=146.63..148.65
O primeiro número é o custo inicial (o custo da obtenção do primeiro registro) e o segundo número é o custo do processamento de todo o nó (custo total do início ao fim).
De fato, este é o custo que as estimativas do PostgreSQL terão que ser cumpridas para executar a declaração. Esse número não significa quanto tempo levará para concluir a solicitação, embora geralmente exista um relacionamento direto necessário para concluir. O custo é uma combinação de 5 componentes de trabalho usados para avaliar o trabalho necessário: amostragem seqüencial, amostragem inconsistente (aleatória), processamento de linhas, operador de processamento (função) e registro do índice de processamento. Custo é a carga de entrada / saída e processador, e é importante saber que o custo relativamente alto significa que o PostgresSQL acredita que terá que fazer mais trabalho. O otimizador decide qual plano de execução usar com base no custo. O otimizador prefere custos mais baixos.
Tempo real
actual time=55.009..55.012
Em milissegundos, o primeiro número é a hora de início (hora de recuperar o primeiro registro) e o segundo número é o tempo necessário para processar o nó inteiro (tempo total do início ao fim). Fácil de entender, certo?
No exemplo acima, foram necessários 55,009 ms para obter o primeiro registro e 55,012 ms para concluir o nó inteiro.
Saiba mais sobre planos de execução.
Existem alguns artigos realmente bons para entender os resultados de EXPLAIN. Em vez de tentar recontá-los aqui, recomendo dedicar algum tempo para realmente entendê-los, acessando estes 2 recursos maravilhosos:
Solicitar ajuste
Agora que você sabe quais operadores estão funcionando mal e pode ver seus planos de execução, é hora de começar a ajustar sua consulta para melhorar o desempenho. Aqui, você faz alterações nas suas consultas e / ou adiciona índices para tentar obter um melhor plano de execução. Comece com gargalos e verifique se você pode fazer alterações para reduzir custos e / ou lead time.
Cache de dados e nota de custo
Ao fazer alterações e avaliar os planos de implementação, para verificar se haverá melhorias, é importante saber que implementações futuras podem depender do armazenamento em cache de dados que dê uma idéia dos melhores resultados. Se você executar a solicitação uma vez, faça uma correção e execute-a uma segunda vez, provavelmente ela será executada muito mais rapidamente, mesmo que o plano de execução não seja mais favorável. Isso ocorre porque o PostgreSQL pode armazenar em cache os dados usados na primeira inicialização e pode usá-los na segunda inicialização. Portanto, você deve concluir as consultas pelo menos três vezes e calcular a média dos resultados para comparar os custos.
O que aprendi pode ajudar a melhorar os planos de execução:
- Índices
- Excluir varredura sequencial (Seq Scan) adicionando índices (se o tamanho da tabela não for pequeno)
- Ao usar um índice de várias colunas, preste atenção na ordem em que define as colunas incluídas - Mais informações
- Tente índices muito seletivos para dados usados com freqüência. Isso tornará seu uso mais eficiente.
- Condição ONDE
- Evite CURTIR
- Evite chamadas de função na cláusula WHERE
- Evite grandes condições em ()
- JOINs
- Ao ingressar em tabelas, tente usar uma expressão de igualdade simples na cláusula ON (ou seja, a.id = b.person_id). Isso permite que você use métodos de associação mais eficientes (ou seja, Hash Join, não Nested Loop Join)
- Converta subconsultas em instruções JOIN quando possível, pois isso geralmente permite ao otimizador entender a meta e, possivelmente, escolher o melhor plano.
- Use COMPOSTOS corretamente: você usa GROUP BY ou DISTINCT apenas porque obtém resultados duplicados? Isso geralmente indica o uso inadequado de JOINs e pode resultar em custos mais altos.
- Se o plano de execução usar Hash Join, poderá ser muito lento se as estimativas de tamanho da tabela estiverem incorretas. Portanto, verifique se as estatísticas da tabela estão corretas revisando a estratégia de aspiração.
- Evite subconsultas correlatas sempre que possível; eles podem aumentar significativamente o custo de uma solicitação
- Use EXISTS ao verificar a existência de seqüências de caracteres com base em um critério, pois é semelhante a um curto-circuito (interrompe o processamento quando encontra pelo menos uma correspondência)
- Recomendações gerais