Tiago Adami Sexta-feira, 21 de julho de 2006
Otimizando bancos PostgreSQL - Parte 01
Olá! Neste meu primeiro artigo gostaria de descrever alguns ajustes finos do PostgreSQL que ajudam a melhorar a performance geral do banco de dados. Estas configurações são válidas para a versão 8.1. Caso você possua uma versão anterior, recomendo fortemente a atualização para a 8.1, pois esta última é muito mais rápida que as anteriores, mesmo utilizando as configurações padrão.
Infelizmente, a maior parte da degradação de performance de um banco de dados está na estrutura e/ou nos comandos SELECT mal elaborados. Nete artigo, será feita uma abordagem única e específica nos ajustes de configuração do SGBD, fazendo-o usufruir do máximo dos recursos de hardware onde está instalado o serviço do PostgreSQL.
É importante salientar também que não existe fórmula mágica para as configurações, sendo que as opções de um servidor pode não ser a melhor opção para outro.
Edição do arquivo postgresql.conf
Para começar, localize o arquivo chamado postgresql.conf. Este arquivo encontra-se no diretório de dados do cluster (ou agrupamento de bancos de dados) o qual você está inicializando. Em instalações normais, você pode encontrá-lo em:
- Microsoft Windows:
- Pasta "Arquivos de Configuração" no Menu Iniciar/Programas
ou
- C:\Arquivos de Programas\PostgreSQL\8.1\data\
- Linux (Red Hat e Fedore)
/var/lib/pgsql/data
Após encontrá-lo, certifique-se de criar uma cópia de backup do arquivo antes de alterá-lo, pois erros na configuração podem fazer com que o PostgreSQL não seja inicializado.
Feito o backup, abra o arquivo em modo de edição. Se estiver no Linux, certifique-se de estar logado com o usuário root ou postgres.
Os parâmetros devem ser preenchidos seguindo o padrão nome_do_parametro = valor. Lembrando que para valores do tipo data e texto deve-se envolver o valor com aspas simples, e para os valores numéricos não inserir separador de milhar. Não esqueça de remover o caracter cerquilha "#" do início das opções que deseja ativar. Todas as alterações serão efetivadas após a reinicialização do serviço PostgreSQL.
shared_buffers
Define o espaço de memória alocado para o PostgreSQL armazenar as consultas SQL antes de devolvê-las ao buffer do sistema operacional. Esta opção pode solicitar que parâmetros do Kernel sejam modificados para liberar mais memória compartilhada do sistema operacional, pois esta passa a ser utilizada também pelo Postgre, em maior quantidade. O valor desta configuração está expresso em blocos de 8 Kbytes (128 representa 1.024 Kbytes ou 1 Mb).
Uma boa pedida é utilizar valores de 8% a 12% do total de RAM do servidor para esta configuração. Caso, após mudar o valor deste parâmetro, o PostgreSQL não inicializar o cluster em questão, altere o sistema operacional para liberar mais memória compartilhada. Consulte o manual ou equivalentes do seu sistema operacional para obter instruções de como aumentar a memória compartilhada (Shared Memory) disponível para os programas.
Exemplo:
shared_buffers = 2048 # Seta a memória compartilhada para 16 Mbytes
work_mem
Configura o espaço reservado de memória para operações de ordem e manipulação/consulta de índices. Este parâmetro configura o tamanho em KBytes utilizado no servidor para cada conexão efetivada ao SGBD, portanto esteja ciente que o espaço total da RAM utilizado (valor da opção multiplicado pelo número de conexões simultâneas) não deve ultrapassar 20% do total disponível (valor aproximado).
Exemplo:
work_mem = 2048 # Configura 2 Mbytes de RAM do servidor para operações de ORDER BY, CREATE INDEX e JOIN disponíveis para cada conexão ao banco.
maintenance_work_mem
Expressa em KBytes o valor de memória reservado para operações de manutenção (como VACUUM e COPY). Se o seu processo de VACUUM está muito custoso, tente aumentar o valor deste parâmetro.
Nota: O total de memória configurada neste parâmetro é utilizado somente durante as operações de manutenção do banco de dados, sendo liberada durante o seu uso normal.
Exemplo:
maintenance_work_mem = 16384 # 16 Mbytes reservados para operações de manutenção.
max_fsm_pages
Em bancos de dados grandes, é ideal que a cada execução do VACUUM mais páginas "sujas" sejam removidas do banco de dados do que a quantidade padrão, principalmente por questões de espaço em disco, e claro, performance. Porém, a configuração padrão traz apenas 20000 páginas. Em bancos transacionais, com no mínimo 10 usuários, o número mensal de páginas sujas pode exceder este valor.
Para realizar uma limpeza maior, aumente o valor desta configuração. Nota: incrementando o valor deste parâmetro pode resultar em aumento do tempo para execução do VACUUM, e cada página ocupa em média 6 bytes de RAM constantemente. O comando VACUUM pode ser comparado ao comando PACK do DBASE (DBFs), porém possui mais funcionalidades.
Exemplo:
max_fsm_pages = 120000 # Realiza a procura por até 120.000 páginas sujas na limpeza pelo VACUUM utilizando cerca de 71 Kb de RAM para isto.
wal_buffers
Número de buffers utilizados pelo WAL (Write Ahead Log). O WAL garante que os registros sejam gravados em LOG para possível recuperação antes de fechar uma transação. Porém, para bancos transacionais com muitas operações de escrita, o valor padrão pode diminuir a performance. Entretanto, é importante ressalvar que aumentar muito o valor deste parâmetro pode resultar em perda de dados durante uma possível queda de energia, pois os dados mantém-se . Cada unidade representa 8 Kbytes de uso na RAM. Valores ideais estão entre 32 e 64. Se o disco conjunto do servidor (HW & SW) são quase infalíveis, tente usar 128 ou 256.
Exemplo:
wal_buffers = 64 # Seta para 512 Kbytes a memória destinada ao buffer de escrita no WAL.
effective_cache_size
Esta configuração dita o quanto de memória RAM será utilizada para cache efetivo (ou cache de dados) do banco de dados. Na prática, é esta a configuração que dá mais fôlego ao SGBD, evitando a constante leitura das tabelas e índices ,dos arquivos do disco rígido.
Como exemplo, se uma tabela do banco de dados possui 20 Mbytes, e o tamanho para esta configuração limita-se aos quase 8 Mbytes padrão, o otimizador de querys irá carregar a tabela por etapas, em partes, até que ela toda seja vasculhada em busca dos registros. Esta opção impacta diretamente aumentando a performance do banco de dados, principalmente quando há concorrência, pois diminui consideravelmente as operações de I/O de disco.
Em consultas pela internet, encontrei várias referências para utilizar no máximo 25% da RAM total. Porém, se o servidor for dedicado, ou dispôr de uma grande quantidade de memória (512 Mbytes ou mais), recomendo o uso de 50% da RAM para esta configuração. Cada unidade corresponde a 8 Kbytes de RAM.
Exemplo:
effective_cache_size = 32768 # Seta o cache de dados do PostgreSQL para 256 Mbytes de RAM.
random_page_cost
Define o custo (em tempo) para a seleção do plano de acesso aos dados do banco. Se você possui discos rígidos velozes (SCSI, por exemplo), tente utilizar valores como 1 ou 2 para esta configuração. Para os demais casos, limite-se a 3 ou 4. Isto impacta no plano estabelecido pelo otimizador interno, que pode realizar um Index_Scan ou Table_Scan, etc, conforme aquilo que ele determinar ser mais otimizado.
Exemplo:
random_page_cost = 2 # Diminui o tempo para seleção aleatória de páginas do otimizador de consultas.
Notas gerais
Caso os valores inseridos possuam algum erro, é possível que o PostgreSQL não consiga inicializar o agrupamento de banco de dados no qual o arquivo postgresql.conf foi modificado. Se isto ocorrer, retorne o backup do mesmo e certifique-se de que as modificações estão corretamente setadas.
É possível obter ganhos de performance significativos com a correta seleção dos valores para as configurações acima. Tente várias combinações dependendo da capacidade e do uso do servidor (hardware) onde está instalado o PostgreSQL 8.1.
Dúvidas e/ou críticas sobre este artigo podem ser encaminhadas ao meu e-mail.
No próximo artigo, apresentarei mais dicas para a otimização de projetos de bancos de dados em PostgreSQL.
Até breve!