Tunning Postgres: Técnicas para otimização do banco

Publicado por Rafael Henter em 27/06/2008

[ Hits: 24.382 ]

Blog: http://www.henter.org

 


Tunning Postgres: Técnicas para otimização do banco



I. Editando as configurações do banco

Edite o arquivo postgresql.conf:

# vi /usr/local/pgsql/data/postgresql.conf.

Altere as sentenças nos seguintes grupos para:

# CONNECTIONS AND AUTHENTICATION (refere-se a parte de conexões e autenticação do banco)

listen_addresses = '*' #ip que ele está escutando
port = 5432 #porta postgres
max_connections = 40 #número de conexões ao banco, depende da quantidade de memória deste

# RESOURCE USAGE (esse grupo se refere ao uso de recursos utilizados pelo banco)

# Memory
shared_buffers = 8192 #min 16MB ou 2x o max_connections
temp_buffers = 8192 #min 100, 8KB each
work_mem = 65536 #min 64, size in KB
maintenance_work_mem = 524288 #min 1024, size in KB
max_stack_depth = 8192 #min 100, size in KB

# Free Space Map
max_fsm_pages = 120000 #min max_fsm_relations*16, 6 bytes each


# WRITE AHEAD LOG (refere-se a escrita dos logs na base do banco)

# Settings
wal_buffers = 512 #min 4, 8KB each


# QUERY TUNING (refere-se à otimização das querys do banco, lembrando que habilitar alguma tag nesse grupo não significa que você melhorá-lo)

# Planner Cost Constants
effective_cache_size = 8192000 #typically 8KB each
random_page_cost = 2.0 #units are one sequential page fetch


# ERROR REPORTING AND LOGGING (refere-se a coleta e ao armazenamento dos logs de erros do banco)

log_destination = 'stderr'
redirect_stderr = on #log do banco em um arquivo separado
log_directory = '/var/log/pgsql' #diretório dos logs
log_filename = '%Y-%m-%d_%H%M%S.log' #nome do log
log_rotation_size = 10240 #rotação automatica dos logs
silent_mode = off #habilita os logs no terminal


#Query/Index Statistics Collector
stats_start_collector = on
stats_row_level = on


# AUTOVACUUM PARAMETERS (refere-se ao vaccuum do banco, rotina que serve para otimizar e limpar o lixo deixado na base do banco)

autovacuum = on #habilitar o autovaccum
autovacuum_naptime = 600 #Tempo entre rodar os autovaccums em segundos
autovacuum_vacuum_threshold = 1000 #min de updates depois do vaccum
autovacuum_analyze_threshold = 500 #min de updates depois da analize

Crie uma pasta para armazenar os logs e após altere suas permissões:

# mkdir /var/log/pgsql
# chmod -R 775 /var/log/pgsql
# chown -R pgsql:pgsql /var/log/pgsql


II. Adicionando Segurança

Edite o arquivo pg_hba.conf para liberação de acesso externo ao banco:

# vi /usr/local/pgsql/data/pg_hba.conf

Caso outro servidor ou rede precise, acesse esse banco, adicione os IPs dos mesmos no grupo IPv4 local connections (Não retirar a permissão do 127.0.0.1).

Segue o formato:

a) liberação para de uma rede especifica:

host all all 192.168.0.0/24 trust

b) liberação para um determinado IP:

host all all 192.168.0.1/32 trust

III. Crie um usuário no banco para acessar ao banco

Para aumentar a segurança usaremos um usuário próprio no banco de dados:

# createuser -U pgsql (nome do usuário que você quer criar) -W

Outras dicas deste autor

Instalação modem Claro 3G e2266 no Linux

Gerando relatórios do PosgreSQL usando o PgFouine

Leitura recomendada

Corrigindo erro ao adicionar plugin AddThis no Joomla! 3.0

Shell, algumas dicas para iniciantes

E-book sobre Ubuntu 12.04

Hack 'n' Cast v0.3 - Introdução ao GNU/Linux

Criar disquete de boot de instalação do Debian

  

Comentários
[1] Comentário enviado por fabio.telles em 30/07/2008 - 16:45h

Rafael, seu texto é interessante e espero que seja o fruto de uma série de testes até chegar na melhor performance para as suas demandas. Mas o que ocorre é que as demandas e situações variam muito. É claro que a configuração padrão não é a ideal. Mas se houvesse uma receita de bolo simples para ser seguida, certamente o time de desenvolvimento do PostgreSQL implantaria ela para você. Então alguns ajustes podem ser bons ou ruins, dependendo da situação. Particularmente, mexer no random_page_cost por exemplo, só mesmo em casos muito extremos.

Não podemos adotar as suas sugestões sem contextualiza-las primeiro. Isto faz sentido para você?
[]s
Fábio Telles

[2] Comentário enviado por henter em 31/07/2008 - 12:53h

Fabio,

Concordo com o seu argumento a minha ideia foi mostrar como eu resolvi em grande problema. Eu vou ver se reescrevo a dica, dizendo o que faz cada linha alterada, afim de que a pessoa entenda o que esta sendo feito, e por que eu edotei essas configs.

[3] Comentário enviado por salima em 04/12/2010 - 09:20h

Rafael, seu texto é interessante! Mas imagine uma aplicação WEB, nós nunca vamos saber o número exato de conexões abertas simultaneamente, dai surge a dúvida como fazer o postgresql NÃO LIMITAR AS CONEXÕES ?? Caso isso não for possível, como colocar ao menos o postgresql pra aceitar umas 5.000 conexões?? O servidor deverá que ter uns 50 GIGAS de memórias. Como resolvo esta questão??

abs



Contribuir com comentário




Patrocínio

Site hospedado pelo provedor RedeHost.
Linux banner

Destaques

Artigos

Dicas

Tópicos

Top 10 do mês

Scripts