Doze Ajustes que Você Irá Querer Fazer no Seu Arquivo PostgreSQL.Conf

Existem um monte de novas opções verdadeiramente assustadoras no arquivo PostgreSQL.conf. Mesmo as já familiares opções das 5 últimas versões mudaram de nomes e formato dos parâmetros. Elas tem a intenção de dar ao administrador de banco de dados mais controle, mas podem levar algum tempo para serem usados.

O que segue são configurações que a maioria dos DBAs vão querer alterar, focado no aumento de performance acima de qualquer outra coisa. Existem algumas poucas configurações que particularmente a maioria dos usuários não querem mexer, mas quem o fizer irá descobri-las indispensáveis. Para estes, vocês terão de aguardar pelo livro.

Lembre-se: as configurações no PostgreSQL.conf precisam ser descomentadas para fazerem efeito, mas recomentá-las não restaurará necessariamente o valor padrão!

Conexão

listen_addresses:
Substitui as configurações tcp_ip e o virtual_hosts do 7.4. O padrão é localhost na maioria das instalações, habilitando apenas conexões pelo console. A maioria dos DBAs irá querer mudar isto para "*", significando que todas as interfaces avaliáveis, após configurar as permissões em hba.conf apropriadamente, irão tornar o PostgreSQL acessível pela rede. Como uma melhoria sobre a versão anterior, o"localhost" permite conexões pela interface de "loopback", 127.0.0.1, habilitando vários utilitários baseados em servidores web.

max_connections:
Exatamente como na versão anterior, isto precisa ser configurado para o atual número de conexões simultâneas que você espera precisar. Configurações altas vão requerer mais memória compartilhada (shared_buffers). Como o overhead por conexão, tanto do PostgreSQL como do SO do host podem ser bem altos, é importante utilizar um pool de conexões se você precisar servir um número grande de usuários. Por exemplo, 150 conexões ativas em um servidor Linux com um processador médio de 32 bits consumirá recursos significativos, e o limite deste hardware é de 600. Claro que um hardware mais robusto irá permitir mais conexões.

Memória

shared_buffers:
Como um lembrete: Este não é a memória total do com o qual o PostgreSQL irá trabalhar. Este é o bloco de memória dedicado ao PostgreSQL utilizado para as operações ativas, e deve ser a menor parte da RAM total na máquina, uma vez que o PostgreSQL usa o cache de disco também. Infelizmente, o montante exato de shared buffers requer um complexo cálculo do total de RAM, tamanho do banco de dados, número de conexões e complexidade das consultas. Assim, é melhor seguir algumas regras na alocação, e monitorar o servidor (particularmente as visões pg_statio) para determinar ajustes.

Em servidores dedicados, valores úteis costumas ficar entre 8MB e 400MB (entre 1000 e 50.000 para páginas de 8K). Fatores que aumentam a quantidade de shared buffers são grandes porções ativas do banco de dados, consultas grandes e complexas, grande número de conexões simultâneas, longos procedimentos e transações, maior quantidade de RAM disponível, CPUs mais rápidas ou em maior quantidade obviamente, outras aplicações na mesma máquina. Contrário a muitas expectativas, alocando, muita, demasiadamente shared_buffers pode até diminuir a performance, aumentando o tempo requerido para explora-la. Aqui estão alguns exemplos baseados em experiências e testes TPC em máquinas Linux:

• Laptop, processador Celeron, 384MB RAM, banco de dados de 25MB: 12MB/1500;
• Servidor Athlon, 1GB RAM, banco de dados de 10GB para suporte a decisão: 120MB/15000;
• Servidor Quad PIII, 4GB RAM, banco de dados de 40GB, com 150 conexões e processamento pesado de transações: 240MB/30000;
• Servidor Quad Xeon, 8GB RAM, banco de dados de 200GB, com 300 conexões e processamento pesado de transações: 400MB/50000.

Favor notar que incrementando shared_buffer, e alguns outros parâmetros de memória, vão requerer que você modifique o System V do seu sistema operacional. Veja a documentação principal do PostgreSQL para instruções nisto.

work_mem:
Costuma ser chamado de sort_mem, mas foi renomeado uma vez que ele agora cobre ordenações, agregações e mais algumas operações. Esta memória não é compartilhada, sendo alocada para cada operação (uma a várias vezes por consulta); esta configuração está aqui para colocar um teto na quantidade de memória que uma única operação ocupar antes de ser forçada para o disco. Este deve ser calculado dividindo a RAM disponível (depois das aplicações e do shared_buffers) pela expectativa de máximo de consultas concorrentes vezes o número de memória utilizada por conexão. Considerações devem ser tomadas sobre o montante de work_mem por consulta; processando grandes conjuntos de de dados requisitará mais. Bancos de dados de aplicações Web geralmente utilizam números baixos, com numerosas conexões mas consultas simples, 512K a 2048K geralmente é suficiente. Contrariamente, aplicações de apoio a decisão com suas consultas de 160 linhas e agregados de 10 milhões de linhas precisam de muito, chegando a 500MB em um servidor com muita memória. Para bancos de dados de uso misto, este parâmetro pode ser ajustado por conexão, em tempo de execução, nesta ordem, para dar mais RAM para consultas específicas.

maintenance_work_mem:
Formalmente chamada de vacuum_mem, esta quantidade de RAM é utilizada pelo PostgreSQL para o VACUUM, ANALYZE, CREATE INDEX, e adição de chaves estrangeiras. Você deve aumentar quanto maior forem suas tabelas do banco de dados e quanto mais memória RAM você tiver de reserva, para fazer estas operações o mais rápidas possível. Uma configuração com 50% a 75% da sua maior tabela ou índice em disco é uma boa regra, ou 32MB a 256MB onde isto não pode ser determinado.

Disco e WAL

checkpoint_segments:
Define o tamanho do cache de disco do log de transações para operações de escrita. Você pode ignorar isto na maioria dos bancos de dados web com a maioria das operações em leitura, mas para bancos de dados de processamento de transações ou para bancos de dados envolvendo grandes cargas de dados, o aumento dele é crítico para a performance. Dependendo do volume de dados, aumente ele para algo entre 12 e 256 segmentos, começando conservadoramente e aumentando se você ver mensagens de aviso no log. O espaço requerido no disco é igual a (checkpoint_segments * 2 + 1) * 16MB, então tenha certeza de ter espaço em disco suficiente (32 significa mais de 1GB).

max_fsm_pages:
Dimensiona o registro que rastreia as páginas de dados parcialmente vazias para popular com novos dados; se configurado corretamente, torna o VACCUM mais rápido e remove a necessidade do VACUUM FULL ou REINDEX. Deve ser um pouco maior que o total de número páginas de dados que serão tocados por atualizações e remoções entre vacuums. Os dois modos de determinar este número são rodar o VACUUM VERBOSE ANALYZE, ou se estiver utilizando autovacuum (veja abaixo) configures este de acordo com o parâmetro -V como uma porcentagem do total de páginas de dados utilizado por seu banco de dados. fsm_pages requer muito pouco memória, então é melhor ser generoso aqui.

vacuum_cost_delay:
Se você tiver tabelas grandes e um significativo montante de atividades de gravações concorrentes, você deve querer fazer uso deste novo recurso que diminui a carga de I/O do VACUUM sobre o custo de fazê-las mais longas. Como este é um novo recurso, é um complexo de 5 configurações dependentes para o qual nós temos apenas poucos testes de performance. Aumentando o vacuum_cost_delay para um valor não zero ativa este recurso; use um atraso razoável, algo entre 50 e 200ms. Para um ajuste fino, aumente o vaccum_cost_page_hit e diminua o vacuum_cost_page_limit irá diminuir o impacto dos vacuums e tornará eles mais longos; em testes de Jan Wieck's num teste de processamento de transações, um delay de 200, page_hit de 6 e limit de 100 diminuiu o impacto do vacuum em mais de 80% enquanto triplicou o tempo de execução dele.

Planejador de Consultas

Estas configurações permitem o planejador de consultas fazer estimativas mais precisas dos custos de operação e assim escolher o melhor plano de execução. Os dois valores de configurações para se preocupar são:

effective_cache_size:
Diz ao planejador de consultas o mais largo objeto do banco de dados que pode se esperar ser cacheado. Geralmente ele deve ser configurado em cerca de 2/3 da RAM, se estiver num servidor dedicado. Num servidor de uso misto, você deve estimar quanto de RAM e cache de disco outras aplicações estarão utilizando e subtrair eles.

random_page_cost:
Uma variável que estima o custo médio em buscas por páginas de dados indexados. Em máquinas mais rápidas, com arranjos de discos velozes ele deve ser reduzido para 3.0, 2.5 ou até mesmo 2.0. Contudo, se a porção ativa do seu banco de dados é muitas vezes maior que a sua RAM, você vai querer aumentar o fator de volta para o valor padrão de 4.0. Alternativamente, você pode basear seus ajustem na performance. Se o planejador injustamente a favor de buscas seqüenciais sobre buscas em índices, diminua-o. Se ele estiver utilizando índices lentos quando não deveria, aumente-o. Tenha certeza de testar uma variedade de consultas. Não abaixe ele para menos de 2.0; se isto parecer necessário, você precisa de ajustem em outras áreas, como as estatísticas do planejador.

Logging

log_destination:
Isto substitui o intuitivo a configuração syslog em versões anteriores. Suas escolhas são usar o log administrativo do SO (syslog ou eventlog) ou usar um log separado para o PostgreSQL (stderr). O primeiro é melhor para monitorar o sistema; o último é melhor para encontrar problemas no banco de dados e para o tuning.

redirect_stderr:
Se você decidir usr um log separado para o PostgreSQL, esta configuração permitirá registrar num arquivo utilizando uma ferramenta nativa do PostgreSQL ao invés do redirecionamento em linha de comando, permitindo a rotação do log. Ajuste para True, e então ajuste o log_diretory para dizer onde colocar os logs. A configuração padrão para o log_filename, log_reotation_size e log_rotation)age são bons para a maioria das pessoas.

Autovacuum e você

Assim que você entra em produção no 8.0, você vai querer fazer um plano de manutenção incluindo VACUUMs e ANALYZEs. Se seus bancos de dados envolvem um fluxo contínuo de escrita de dados, mas não requer a maciças cargas e apagamentos de dados ou freqüentes reinícios, isto significa que você deve configurar o pg_autovacuum. Isto é melhor que agendar vaccuns porque:

• Tabelas sofrem o vacuum baseados nas suas atividades, excluindo tabelas que apenas sofrem leituras.
• A freqüência dos vacuums cresce automaticamente com o crescimento da atividade no banco de dados.
• É mais fácil calcular o mapa de espaço livre e evitar o inchaço do banco de dados.

Configurando o autovacuum requer a fácil compilação de um módulo do diretório contrib/pg_autovacuum da fonte do seu PostgreSQL (usuários Windows devem procurar o autovacuum incluído no pacote do instalador). Você liga as estatísticas de configuração detalhadas no README. Então você inicia o autovacuum depois de o PostgreSQL ser iniciado como um processo separado; ele será desligado automaticamente quando o PostgreSQL desligar.

As configurações padrões do autovacuum são muito conservadores, imagino, e são mais indicadas para bancos de dados muito pequenos. Eu geralmente uso algo mais agressivo como:

Isto irá rodar o vacuum nas tabelas após 400 linhas + 40% da tabela ser atualizada ou apagada e irá rodar o analyze após 100 linhas + 30% da tabelas sofres inserções, atualizações ou ser apagada. As configurações acima também me permitem configurar o meu max_fsm_pages para 50% das páginas de dados com a confiança de que este número não será subestimado gerando um inchaço no banco de dados. Nós atualmente estamos testando várias configurações na OSDL e teremos mais informações em breve.

Note que você também pode usar o autovacuum para configurar opções de atraso ao invés de configura-lo no PostgreSQL.conf. O atraso no Vaccum pode ser de vital importância em sistemas que tem tabelas e índices grandes; em último caso pode parar uma operação importante.

Existem infelizmente um par de limitações sérias para o autovacuum no 8.0 que serão eliminadas em versões futuras:

• Não tem memória de longa duração: autovacuum esquece toda a sua atividade quando você reinicia o banco de dados. Então se você reinicia regularmente, você deve realizar um VACUUM ANALYZE em todo o banco de dados imediatamente antes ou depois.
• Preste atenção em quanto o servidor está ocupado: há planos de checar a carga do servidor antes de realizar o vacuum, mas não é uma facilidade corrente. Então se você tem picos de carga extremos, o autovacuum não é para você.