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.
- PostgreSQL Performance Tuning (o "QuickStart Guide" é similar a este documento)
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.
Comentários
Postar um comentário