Eu tenho uma instância do PostgreSQL 9.2 em execução no RHEL 6.3, máquina de 8 núcleos com 16 GB de RAM. O servidor é dedicado a este banco de dados. Dado que o postgresql.conf padrão é bastante conservador em relação às configurações de memória, achei que seria uma boa idéia permitir que o Postgres usasse mais memória. Para minha surpresa, seguir o conselho em wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server diminuiu significativamente praticamente todas as consultas que eu executo, mas é obviamente mais perceptível nas consultas mais complexas.
Eu também tentei executar o pgtune que deu a seguinte recomendação com mais parâmetros ajustados, mas isso não mudou nada. Ele sugere shared_buffers de 1/4 do tamanho da RAM, o que parece estar de acordo com os conselhos de outros lugares (e no PG wiki em particular).
default_statistics_target = 50
maintenance_work_mem = 960MB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 11GB
work_mem = 96MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 3840MB
max_connections = 80
Tentei reindexar todo o banco de dados depois de alterar as configurações (usando reindex database), mas isso também não ajudou. Eu brinquei com shared_buffers e work_mem. Mudá-los gradualmente dos valores padrão muito conservadores (128k / 1 MB) diminuiu gradualmente o desempenho.
Executei EXPLAIN (ANALYZE,BUFFERS)algumas consultas e o culpado parece ser que o Hash Join é significativamente mais lento. Não está claro para mim o porquê.
Para dar um exemplo específico, tenho a seguinte consulta. Ele é executado em ~2100ms na configuração padrão e ~3300ms na configuração com tamanhos de buffer aumentados:
select count(*) from contest c
left outer join contestparticipant cp on c.id=cp.contestId
left outer join teammember tm on tm.contestparticipantid=cp.id
left outer join staffmember sm on cp.id=sm.contestparticipantid
left outer join person p on p.id=cp.personid
left outer join personinfo pi on pi.id=cp.personinfoid
where pi.lastname like '%b%' or pi.firstname like '%a%';
EXPLAIN (ANALYZE,BUFFERS)para a consulta acima:
- Buffers padrão: http://explain.depesz.com/s/xaHJ
- Buffers maiores: http://explain.depesz.com/s/Plk
A questão é por que estou observando um desempenho reduzido quando aumento os tamanhos de buffer? A máquina definitivamente não está ficando sem memória. Alocação se a memória compartilhada no SO for ( shmmaxe shmall) estiver configurada para valores muito grandes, isso não deve ser um problema. Também não estou recebendo nenhum erro no log do Postgres. Estou executando o autovacuum na configuração padrão, mas não espero que tenha algo a ver com isso. Todas as consultas foram executadas na mesma máquina com poucos segundos de intervalo, apenas com configuração alterada (e PG reiniciado).
Edit: Acabei de encontrar um fato particularmente interessante: quando executo o mesmo teste no meu iMac de meados de 2010 (OSX 10.7.5) também com Postgres 9.2.1 e 16 GB de RAM, não sinto a lentidão. Especificamente:
set work_mem='1MB';
select ...; // running time is ~1800 ms
set work_mem='96MB';
select ...' // running time is ~1500 ms
Quando faço exatamente a mesma consulta (aquela acima) com exatamente os mesmos dados no servidor, recebo 2100 ms com work_mem=1MB e 3200 ms com 96 MB.
O Mac tem SSD, então é compreensivelmente mais rápido, mas exibe um comportamento que eu esperaria.
Veja também a discussão de acompanhamento sobre pgsql-performance .