Olá, Khabrovites! Trago à sua atenção uma tradução do artigo
"Como uma única alteração na configuração do PostgreSQL melhorou o desempenho de consultas lentas em 50x" por Pavan Patibandla. Isso me ajudou muito a melhorar o desempenho do PostgreSQL.
Na Amplitude, nosso objetivo é fornecer análises interativas de produtos fáceis de usar, para que todos possam encontrar respostas para suas perguntas sobre o produto. Para garantir a usabilidade, o Amplitude deve fornecer essas respostas rapidamente. Portanto, quando um de nossos clientes se queixou de quanto tempo levou para carregar a lista suspensa de propriedades de eventos na interface do usuário do Amplitude, iniciamos um estudo detalhado do problema.
Ao rastrear o atraso em diferentes níveis, percebemos que eram necessários 20 segundos para que uma consulta específica do PostgreSQL fosse concluída. Isso nos surpreendeu, pois as duas tabelas têm índices na coluna de junção.
Pedido lento
O plano de execução do PostgreSQL para esta consulta foi inesperado para nós. Apesar de ambas as tabelas possuírem índices, o PostgreSQL decidiu executar um Hash Join com varredura seqüencial de uma tabela grande. A varredura seqüencial de uma tabela grande ocupou a maior parte do tempo da consulta.
Plano de execução de consulta lenta
Inicialmente, suspeitei que isso pudesse ocorrer devido à fragmentação. Mas, depois de verificar os dados, percebi que os dados são adicionados apenas a esta tabela e praticamente não são excluídos de lá. Como limpar o local com VACUUM não ajudará muito aqui, comecei a cavar mais. Depois, tentei o mesmo pedido em outro cliente com um bom tempo de resposta. Para minha surpresa, o plano de execução da consulta parecia completamente diferente!
Plano de execução para a mesma solicitação em outro cliente
Curiosamente, o aplicativo A só teve acesso a 10 vezes mais dados que o aplicativo B, mas o tempo de resposta foi 3.000 vezes maior.
Para ver planos alternativos de consulta do PostgreSQL, desliguei a conexão de hash e reiniciei a consulta.
Plano de execução alternativo para consulta lenta
Bem aqui! A mesma solicitação é concluída 50 vezes mais rápido ao usar um loop aninhado em vez de uma junção de hash. Então, por que o PostgreSQL escolheu o pior plano para a aplicação A?
Com uma análise mais detalhada do custo estimado e do lead time real dos dois planos, as proporções estimadas de custo e lead time real foram muito diferentes. O principal culpado por essa discrepância foi a estimativa de custo da varredura seqüencial. O PostgreSQL estima que as varreduras seqüenciais seriam melhores que as mais de 4000 varreduras de índice, mas, na verdade, as varreduras de índice eram 50 vezes mais rápidas.
Isso me levou às
opções de configuração
random_page_cost e
seq_page_cost . Os valores padrão do PostgreSQL são
4 e
1 para
random_page_cost ,
seq_page_cost , configurados para o HDD, onde o acesso aleatório ao disco é mais caro que o acesso seqüencial. No entanto, esses custos eram imprecisos para nossa implantação usando o volume
gp2 EBS , que são unidades de estado sólido. Para nossa implantação, o acesso aleatório e seqüencial é quase o mesmo.
Alterei o valor de
random_page_cost para
1 e
tentei novamente a solicitação. Dessa vez, o PostgreSQL usou o loop aninhado e a consulta foi executada 50 vezes mais rápido. Após a alteração, também observamos uma diminuição significativa no tempo máximo de resposta do PostgreSQL.
O desempenho geral de uma solicitação lenta melhorou significativamente
Se você usa o SSD e o PostgreSQL com a configuração padrão, aconselho a tentar
definir random_page_cost e
seq_page_cost . Você pode se surpreender com a dramática melhoria de desempenho.
Por conta própria, acrescentarei que defino os parâmetros mínimos
seq_page_cost = random_page_cost = 0.1 para dar prioridade aos dados na memória (cache) sobre as operações do processador, pois aloquei uma grande quantidade de RAM para o PostgreSQL (o tamanho da RAM excede o tamanho do banco de dados no disco). Não está muito claro por que a comunidade do postgres ainda usa as configurações padrão relevantes para um servidor com uma pequena quantidade de RAM e HDDs, e não para servidores modernos. Espero que isso seja corrigido em breve.