Checklist de performance do PostgreSQL 8.0

Segue abaixo a tradução do texto do Josh Berkus (desenvolvedor do PostgreSQL). Este texto é um bom ponto de partida para quem está aprendendo sobre tuning no PostgreSQL 8.0. Espero em breve atualizar o artigo para as versões 8.1 e 8.2.

Tradução livre do texto: “PostgreSQL 8.0 Performance Checklist” Publicado por Josh Berkus em 12/01/2005 em http://www.powerpostgresql.com/PerfList

Copyright (c) 2005 by Josh Berkus and Joe Conway. This material may be distributed only subject to the terms and conditions set forth in the Open Publication License, v1.0 or later (the latest version is presently available at http://www.opencontent.org/openpub/).

Aqui está um conjunto de regras para configurar seu servidor PostgreSQL 8.0 . Muito do que está abaixo é baseado em evidências ou testes de escalabilidade práticos; há muito sobre performance de bancos de dados que nós e a OSDL, ainda estamos trabalhando. Contudo, isto deve ser um inicio. Todas as informações abaixo são úteis a partir de 12 de janeiro de 2005 e serão atualizadas depois. Discussões sobre configurações abaixo superam as que eu realizei no General Bits.

Cinco Princípios de Hardware para Configurar o seu Servidor PostgreSQL

1. Discos > RAM > CPU

   Se você vai gastar dinheiro em um servidor PostgreSQL, gaste em arranjos de discos de alta performance e tenha processadores medianos e uma memória adequada. Se você tiver um pouco mais de dinheiro, adquira mais RAM. PostgreSQL, como outros SGDBs que suportam ACID, utilizam E/S muito intensamente e é raro uma aplicação utilizar mais a CPU do que a placa SCSI (com algumas exceções, claro). Isto se aplica tanto a pequenos como grandes servidores; obtenha uma CPU com custo baixo se isso permitir você comprar uma placa RAID de alta performance ou vários discos.

2. Mais unidades de discos == Melhor

   Tendo múltiplos discos, o PostgreSQL e a maioria dos sistemas operacionais irão paralelizar as requisições de leitura e gravação no banco de dados. Isto faz uma enorme diferença em sistemas transacionais, e uma significativa melhoria em aplicações onde o banco de dados inteiro não cabe na RAM. Com os tamanhos mínimos de discos (72GB) você será tentado a utilizar apenas um disco ou um único par espelhado em RAID 1; contudo, você verá que utilizando 4, 6 ou até 14 discos irá render um impulso na performance. Ah, e SCSI é ainda significativamente melhor em fluxo de dados em BD que um IDE ou mesmo um Serial ATA.

3. Separe o Log de Transações do Banco de Dados

   Assumindo que você já investiu dinheiro num arranjo com tamanho decente num conjunto de discos, existe um monde de opções mais inteligentes do que jogar tudo num único RAID. De inicio coloque o log de transações (pg_xlog) no seu próprio recurso de discos (um arranjo ou um disco), o que causa uma diferença de cerca de 12% na performance de bancos de dados com grande volume de gravações. Isto é especialmente vital em pequenos sistemas com discos SCSI ou IDE lentos: mesmo em um servidor com dois discos, você pode colocar o log de transações sobre o disco do sistema operacional e tirar algum benefício.

4. RAID 1+0/0+1 > RAID 5

   RAID 5 com 3 discos tem sido um desafortunado padrão entre vendedores de servidores econômicos. Isto possibilita a mais lenta configuração de discos possível para o PostgreSQL; você pode esperar pelo menos 50% a menos de velocidade nas consultas em relação ao obtido com discos SCSI normais. Por outro lado, foque em RAID 1 ou 1+0 para um conjunto de 2, 4 ou 6 discos. Acima de 6 discos, o RAID 5 começa a ter uma performance aceitável novamente, e a comparação tende a ser bem melhor com base na sua controladora individual. No entanto, o mais importante, usar uma placa RAID barata pode ser um risco; é sempre melhor usar RAID por software do que um incorporado numa placa Adaptec que vem com seu servidor.

5. Aplicações devem rodar bem junto

   Outro grande erro que eu vejo em muitas organizações e colocar o PostgreSQL em um servidor com várias outras aplicações competindo pelos mesmos recursos. O pior caso é colocar o PostgreSQL junto com outros SGDBs na mesma máquina; ambos bancos de dados irão lutar pela banda de acesso aos discos e o cache de disco do SO, e ambos vão ter uma performance pobre. Servidores de arquivo e programas de log de segurança também são ruins. O PostgreSQL pode compartilhar a mesma máquina com aplicações que utilizam principalmente CPU e RAM intensamente, como o Apache, garantindo que exista RAM suficiente.

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.

Memoria

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: costuama 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 veses 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 órdem, 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 extrangeiras. 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 partialmente 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 atrazo 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 verssõ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 vaccum baseados nas suas atividades, excluindo tabelas que apenas sofrem leituras.
• A freqüência dos vaccums 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:

-D -v 400 -V 0.4 -a 100 -A 0.3

Isto irá rodar o vaccum 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ê.