quinta-feira, 2 de maio de 2013

Introdução [de 5 minutos] ao tuning do PostgreSQL


Tradução livre, escrita por mim, do original "5-Minute Introduction to PostgreSQL Performance" (http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm). 
 
O PostgreSQL vem de fábrica com uma configuração básica que visa a compatibilidade com o máximo possível de dispositivos, o que prejudica muito o desempenho. Há boas chances de os parâmetros padrão serem muito aquém do que o seu sistema suportaria.

Ao invés de descrever cada detalhe de tudo o que você deveria eventualmente saber, aqui nós daremos uma visão simplificada dos princípios básicos, com um olhar voltado para os itens mais comuns relacionados ao desempenho e as coisas que os iniciantes no PostgreSQL geralmente não tem conhecimento. Para uma leitura verdadeiramente muito rápida, leia apenas as linhas em negrito em cada seção e siga essas orientações.

Os principais parâmetros do servidor de banco de dados são mantidos em um arquivo chamado postgresql.conf. Você precisará editar este arquivo e reiniciar o servidor para que as alterações façam efeito. O desempenho deverá cair por um curto período de tempo depois de fazer isso porque o sistema vai perder as informações que são armazenadas em cache durante a reinicialização.

Utilize uma versão atualizada

A primeira versão do PostgreSQL que tem bom desempenho em hardware atual é a 8.1, a versão atual 8.2 é ainda melhor (você provavelmente deve estar executando a versão 8.2.4, versões anteriores a 8.2 tinham algumas peculiaridades que é melhor você evitar). Caso esteja executando uma versão anterior a 8.1 considere realizar o upgrade. Há muitos problemas de desempenho que são insolúveis em versões antigas. Se você estiver executando uma versão antiga desconsidere a seção a seguir.

Defina shared_buffers e effective_cache_size baseado na memória total

O parâmetro de configuração shared_buffers determina quanta memória é dedicada ao uso do PostgreSQL para armazenar dados. Um valor razoável para shared_buffers é 1/4 da memória do seu sistema. É provável que seja necessário aumentar o tamanho da memória que o sistema operacional permite alocar de uma vez para definir este valor. Consulte “Gerenciando recursos do kernel” para mais detalhes. Note que no Windows grandes valores para shared_buffers não são tão eficazes e você pode encontrar melhores resultados mantendo-o relativamente baixo e usando o cache do sistema operacional ao invés.

effective_cache_size deve ser definido como a quantidade de memória de sobra para o cache de disco tendo em conta que é usado pelo sistema operacional, memória dedicada ao PostgreSQL e outras aplicações. Se o valor for muito baixo, os índices não podem ser utilizados para a execução de consultas da maneira que você esperaria. Definindo a effective_cache_size como 1/2 do total de memória seria uma configuração conservadora normal. É possível encontrar uma melhor estimativa olhando para as estatísticas do seu sistema operacional. Em sistemas UNIX-like, somando os números free+cached do free ou top. No Windows, consulte o "Cache do sistema" na aba Desempenho do Gerenciador de Tarefas do Windows. Observe que você deve adicionar o valor definido para shared_buffers a este total - o query planner utiliza estimated_cache_size tal como está, sem acrescentar valor para você.

Analise o seu banco de dados

O PostgreSQL mantém estatísticas sobre suas tabelas que permitem executar corretamente as consultas. Se estas estatísticas estão desatualizadas você não terá um bom desempenho. Você pode atualizar as estatísticas usando o comando ANALYZE. Para fazer isso e limpar os dados não utilizados, execute o comando VACCUM ANALYZE para forçar uma atualização das estatísticas e limpeza das tabelas. Isto é particularmente importante se você acabou de carregar ou modificar uma grande quantidade de dados. Você deve dar uma lida em limpeza automática das tabelas se ainda não o fez, o que também irá manter as estatísticas atualizadas.

EXPLAIN ANALYZE suas sentenças lentas

Se você tem uma sentença específica que está executando e que está levando mais tempo do que o esperado, a melhor maneira de descobrir por que ele não está funcionando mais rápido é olhar para o que está fazendo. Execute EXPLAIN ANALYZE para obter um relatório de porque uma declaração está demorando muito tempo para ser executada. Isso pode levá-lo a ajustar outros parâmetros do servidor, por exemplo, se você notar que uma operação de classificação está tomando muito tempo, pode ser necessário aumentar o parâmetro work_mem em seu postgresql.conf. Note que work_mem é definido em uma base de conexão por cliente, por isso é necessário multiplicar o valor por sessões simultâneas para obter um valor total de memória. Você pode usar aumentar o valor apenas para uma sessão que executa uma consulta maior do que a maioria, usando um comando como set work_mem GB = '1'; (que é a sintaxe 8.2, você terá que especificar manualmente um valor no 8.1).

Aqui termina seus 5 minutos. Daqui pra frente você pode ir sozinho. O principal problema com a maioria desses documentos é que eles são um pouco antigos. Dirigem-se a versões anteriores do PostgreSQL que funcionam um pouco diferente das atuais e algumas das recomendações específicas para os parâmetros são bastante subdimensionadas para equipamentos modernos. A maior diferença é que costumava ser impraticável usar valores grandes para shared_buffers, a partir da versão 8.1 e superiores está tudo certo.




Além disso, você não pode ignorar o enorme volume de informações na documentação do PostgreSQL. A sua força é também sua fraqueza: um monte de coisas não está organizada.
Copyright 2007 Gregory Smith. Última atualização 2010/01/29.