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

Publicado por Rafael Henter em 27/06/2008

[ Hits: 24.627 ]

Blog: http://www.henter.org

1 0

Denuncie Favoritos Indicar Impressora

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

Gerando relatórios do PosgreSQL usando o PgFouine

Instalação modem Claro 3G e2266 no Linux

Leitura recomendada

Instalando codecs e players não-livres no Debian GNU/Linux

Replicar banco de login do Myauth 3

Segurança e atualização

Leitura recomendadíssima!

Listnux - Lista de programas no estilo wiki

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

0 0

[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.

1 0

[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

0 0