PostgreSQL Prático/Administração/Otimização e Desempenho
Otimização e Desempenho
editarPara isso ajusta-se bem o postgresql.conf, utiliza-se o vacuum, analyze e explain.
Lembrando que na versão 8.1 o vacuum não mais é um programa separado e vem embutido no executável. Mesmo embutido ele é configurável e podemos utilizar ou não e se usar, podemos também configurar sua periodicidade.
How to optimize PostgreSQL database size
http://www.linuxinsight.com/optimize_postgresql_database_size.html
Uma ótima fonte de consulta
http://www.metatrontech.com/wpapers/mysql2postgresql.pdf
Capítulo 21 do manual:
http://pgdocptbr.sourceforge.net/pg80/maintenance.html
Vacuum:
http://pgdocptbr.sourceforge.net/pg80/sql-vacuum.html
Analyze: http://pgdocptbr.sourceforge.net/pg80/sql-analyze.html
VACUUM
O comando Vacuum tanto recupera espaço em disco, quanto otimiza o desempenho do banco e previne contra perda de dados muito antigos devido ao recomeço do ID das transações, portanto deve ser utilizado constantemente, como também atualiza as estatísticas dos dados utilizados pelo planejador de comandos. Lembrando que na versão 8.1 já vem embutido no executável, podendo apenas ser configurado para que seja executado automaticamente.
Na linha de comando:
vacuumdb -faze ou vacuumdb -fazq.
ANALYZE
O comando ANALYZE coleta estatísticas sobre o conteúdo das tabelas do banco de dados e armazena os resultados na tabela do sistema pg_statistic. Posteriormente, o planejador de comandos utiliza estas estatísticas para ajudar a determinar o plano de execução mais eficiente para os comandos. Caso não atualizemos estas estatísticas com freqüência podemos comprometer o desempenho do banco de dados por uma escolha errada do plano de comandos.
Normalmente operações DELETE ou UPDATE não removem os registros automaticamente. Somente após a execução do VACUUM isso acontece.
Recomendação
Para a maioria das instalações executar o comando VACUUM ANALYZE para todo o banco de dadosuma vez ao dia em horário de pouca utilização. Também podemos utilizar o comando: vacuumdb -fazq.
Quando foi excluída a maioria dos registros de uma tabela sugere-se a execução do comando VACUUM FULL. Este comando gera um forte bloqueio nas tabelas em que é executado.
Em tabelas cujo conteúdo é excluído periodicamente, como tabelas temporárias, é indicado o uso do comando TRUNCATE ao invés de DELETE.
Exemplo de uso do vacuum. Acesse o banco e execute:
VACUUM VERBOSE ANALYZE nometabela;
De fora do psql usar o comando “vacuumdb -faze” ou “vacuumdb -fazq” (silencioso).
VACUUM VERBOSE ANALYZE autor;
INFO: vacuuming "public.autor"
INFO: "autor": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.autor"
INFO: "autor": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
Em um Banco Completo
Só VACUUM
Ou
VACUUM FULL ANALYZE;
Dicas de Desempenho
- Adicionar índice à tabela (toda chave primária já contém um índice)
- Adicionar índices aos campos de cláusulas WHERE;
- Evitar campos com tamanho variável. Preferir o CHAR ao VARCHAR.
- Evitar muitos índices e índices com mais de um campo
- Evitar índice em tabela muito pequena (poucos registros, não compensa)
- Evitar, sempre que possível, chaves compostas
- Separar bancos em um HD e logs em outro HD
- Aumentar shared buffers (postgresql.conf) de acordo com RAM disponível.
Recomendações: 25% da RAM para shared buffers cache e 2-4% para sort buffer.
Separando bancos e Logs
bancos em /usr/local/pgsql/data (hda)
logs em /usr/local/pgsql/data/pg_xlog (hdb)
Utilizar links simbólicos para mover tabelas, índices, ... para outro HD.
Ativar o chip DMA do HD
Testar: hdparm -Tr /dev/hda (status)
Ativar o chip: hdparm -d 1 /dev/hda
Desativar: hdparm -d 0 /dev/hda
No postgresql.conf existem configurações para shared_buffers, que quanto maior melhor, respeitando-se a RAM.
O default da versão 8.1.4 é:
shared_buffers = 1000 # min 16 ou max_connections*2 (8KB cada)
Plano de Consulta
O PostgreSQL concebe um plano de comando para cada comando recebido. A escolha do plano correto, correspondendo à estrutura do comando e às propriedades dos dados, é absolutamente crítico para o bom desempenho. Pode ser utilizado o comando EXPLAIN para ver o plano criado pelo sistema para qualquer comando (conjunto executável de instruções). A leitura do plano é uma arte que merece um tutorial extenso, o que este não é; porém, aqui são fornecidas algumas informações básicas.
Os números apresentados atualmente pelo EXPLAIN são:
- O custo de partida estimado (O tempo gasto antes de poder começar a varrer a saída como, por exemplo, o tempo para fazer a classificação em um nó de classificação).
- O custo total estimado (Se todas as linhas fossem buscadas, o que pode não acontecer: uma consulta contendo a cláusula LIMIT pára antes de gastar o custo total, por exemplo).
- Número de linhas de saída estimado para este nó do plano (Novamente, somente se for executado até o fim).
- Largura média estimada (em bytes) das linhas de saída deste nó do plano.
EXPLAIN SELECT * FROM NOMETABELA;
Mostra plano de execução interna da consulta, acusando tempo gasto
EXPLAIN SELECT sum(i) FROM tabela1 WHERE i = 4;
Agora a consulta será modificada para incluir uma condição WHERE:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
Modificando-se a consulta para restringir mais ainda a condição
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50;
Adição de outra condição à cláusula WHERE:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 50 AND stringu1 = 'xxx';
A seguir é feita a junção de duas tabelas, utilizando as colunas sendo discutidas:
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
Uma forma de ver outros planos é forçar o planejador a não considerar a estratégia que sairia vencedora, habilitando e desabilitando sinalizadores de cada tipo de plano (Esta é uma ferramenta deselegante, mas útil.
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
É possível verificar a precisão dos custos estimados pelo planejador utilizando o comando EXPLAIN ANALYZE. Na verdade este comando executa a consulta, e depois mostra o tempo real acumulado dentro de cada nó do plano junto com os custos estimados que o comando EXPLAIN simples mostraria. Por exemplo, poderia ser obtido um resultado como este:
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
Reinício do ID de Transações
Para prevenir com segurança o recomeço do ID das Transações devemos utilizar o comando VACUUM em todas as tabelas do banco de dados pelo menos uma vez a cada meio bilhão de transações. Caso o VACUUM não seja executado pelo menos uma vez a cada 2 bilhões de transações ocorrerá a perda de todos os dados do banco. De fato eles não se perdem, voltando dentro de mais 2 bilhões de transações, mas isso não ajuda.
Como saber quantas transações ainda falta para a perda dos dados:
SELECT datname AS banco, AGE(datfrozenxid) AS idade FROM pg_database;
Sempre que se executa o comando VACUUM em um banco, a coluna com age começa de 1 bilhão. Ao executar as transações vai incrementando. Ao se aproximar de 2 bilhões devemos executar novamente o comando VACUUM.
Alerta
Caso um banco já esteja com mais de 1,5 bilhões de transações, ao executar o comando VACUUM para o banco inteiro receberá um alerta sobre a necessidade de execução do VACUUM.