PostgreSQL Prático/Apêndices/Dicas sobre Desempenho e Otimizações do PostgreSQL
15.5 – Dicas sobre Desempenho e Otimizações do PostgreSQL
editarExistem duas principais formas de melhorar o desempenho de SGBDs: uma é melhorando o hardware, com CPUs, RAM, Discos mais novos, rápidos e confiáveis. A outra é otimizando as consultas realizadas nos bancos (usando VACUUM, VACUUM ANALYZE, EXPLAIN, criando CLUSTERS, entre outros).
Uma das medidas básicas adotada para melhorar o desempenho de tabelas com grandes quantidades de registros e especialmente com muitos acessos, é a inclusão de índices estratégicos. Além da chave primária é importante inserir índices em campos que compõem a cláusula WHERE, que fazem parte de cláusulas ORDER BY, GROUP BY entre outras. Em consultas com WHERE de vários campos usando OR, não adianta inserir índice, pois não será utilizado pelo PostgreSQL, somente usando AND.
Na criação do banco de dados e especialmente na criação das consultas é muito importante atentar para um bom planejamento, normalização, consultas otimizadas tendo em vista o planejador de consultas do PostgreSQL através do uso dos comandos EXPLAIN e ANALYZE.
A administração do PostgreSQL também é muito importante para tornar o SGBD mais eficiente e rápido. Desde a instalação e configuração temos cuidados que ajudam a otimizar o PostgreSQL.
Adaptação do Artigo sobre otimização do PostgreSQL do Diogo Biazus e do original do Bruce Momjian (http://www.ca.postgresql.org/docs/momjian/hw_performance).
Hardware
No computador as informações são manipuladas pelos registradores da CPU, pelo cache da CPU, pela memória RAM e pelos discos rígidos.
Na prática as informações utilizadas com mais freqüência são colocadas próximas à CPU.
Quem determina que informações devem ficar nos registradores são os compiladores.
Cache da CPU guarda ar informações utilizadas recentemente.
O Sistema Operacional controla o que está armazenado na RAM e o que mandar para o disco rígido.
Cache e Registradores da CPU não podem ser otimizados diretamente pelo administrador do SGBD. Efetivamente otimização em bancos de dados envolvem aumento da quantidade de informações úteis na RAM, prevenindo acesso a disco sempre que possível.
Não é tarefa simples de ser colocada em prática, pois a memória RAM guarda muitas outras informações: programas em execução, pilhas e dados de programas, memória cache compartilhada do PostgreSQL, cache do buffer de disco do kernel e kernel.
Otimização correta de bancos de dados procura manter a maior quantidade possível de informações do banco na memória RAM ao mesmo tempo que não afeta as demais áreas do sistema operacional.
Existem dois tipos de configuração de memória no PostgreSQL, a compartilhada e a individual. A compartilhada tem um tamanho fixo, ela é alocada sempre que o PostgreSQL inicializa e então é compartilhada por todos os clientes. Já a memória individual é tem um tamanho variável e é alocada separadamente para cada conexão feita ao SGBD.
Memória Cache Compartilhada do PostgreSQL
O PostgreSQL não altera as informações diretamente no disco. Ao invés disso ele solicita que os dados sejam lidos da memória cache compartilhada do PostgreSQL. O cliente PostgreSQL então lê e escreve os blocos e finalmente escreve no disco.
Clientes que precisam acessar tabelas primeiro procuram pelos blocos necessários no cache. Caso estejam aí então continuam processando normalmente. Caso contrário é feita uma solicitação ao sistema operacional para carregar os blocos. Os blocos são carregados do cache de buffer de disco do kernel ou diretamente do disco. Estas operações podem ser onerosas (lentas).
Na configuração default do PostgreSQL 8.1.3 ele aloca 1000 shared buffers. Cada buffer usa 8KB, o que soma 8MB. Aumentando o número de buffers fará com que os clientes encontrem as informações que procuram em cache e evita requisições onerosas ao sistema operacional. Mas cuidado, pois se aumentar muito a memória compartilhada (shared buffers) pode acarretar uso da memória virtual (swap). As alterações podem ser feitas através do comando postmaster na linha de comando ou através da configuração do valor do shared_buffers no postgresql.conf.
Que Porção da RAM Reservar para o PostgreSQL?
A maior porção útil que não atrapalhe os outros programas.
Nos sistemas UNIX as informações saem da RAM (quando insuficiente) para o swap. Ruim é quando as informações voltam do swap para a RAM, pois então os programas são suspensos até que as mesmas sejam carregadas.
Tamanho da Cache
Imaginemos que o PostgreSQL shared buffer cache seja suficiente para manipular uma tabela inteira. Repetidas buscas seqüenciais da tabela não devem necessitar de acesso ao disco já que todos os dados já estão em cache. Agora vamos imaginar que o cache é menor que a tabela, então neste caso as informações irão para o disco (swap) e terão um desempenho bem inferior.
Tamanho Adequado da Shared Buffer Cache
Idealmente a PostgreSQL shared buffer cache (Memória Cache Compartilhada do PostgreSQL) deve ser:
Grande o suficiente para conseguir manipular as tabelas mais comumente acessadas.
Pequena o bastante para evitar atividades de swap pagein.
Exemplo:
Por exemplo queremos x MB para memória compartilhada ( x / 8 ) * 1024 = Resultado a ser configurado em shared_buffer
Se x = 768 MB
(768 / 8) * 1024
Resultado a ser configurado em shared_buffer = 98304
Para informações sobre uma configuração do kernel para que vários sistemas operacionais trabalharem com o PostgreSQL:
http://developer.postgresql.org/docs/postgres/kernel-resources.html
Memória Individual (Sort Memory)
Principalmente utilizada em ordenações de registros das tabelas, em operações de criação de índices, ordenação (order by), merge join, etc.
Esta memória pode ser configurada através do parâmetro sort_mem do postgresql.conf.
Para a configuração leve em conta sua memória disponível (incluindo a memória já alocada para o shared buffers), também o número médio de conexões e o uso da memória virtual (swap).
Exemplo:
Considerando um servidor dedicado (rodando somente o servidor PostgreSQL), com memória RAM de 1,5GB e com até 10 conexões simultâneas com o SGBD:
shared_buffers = 80000 # 80.000 blocos de 8KB = 625 MB
sort_mem = 64000 # tamanho em KB = 62,5 MB, para cada usuário com
# 10 usuários = 526 MB
vacuum_mem = 2000
Por exemplo: queremos x KB para memória individual sort_men
( x * 1024 ) = resultado para memória individual
x = 16
(16 * 1024) = sort_mem = 16384
Seria bom mudar também memória para vaccum
vacuum_mem = 131072 (mesmo cálculo do sort_mem)
Uso de Vários Discos
Em sistemas com mais de um disco podemos melhorar a performance do mesmo distribuindo algumas tarefas entre discos diferentes.
Supondo que temos dois HDs, hda e hdb:
Movendo os logs de transação para outro disco:
- Parar o PostgreSQL
- Montar hdb em /mnt/hdb
- Mover a pasta /usr/local/pgsql/data/pg_xlog para o /mnt/hdb
- Criar um link simbólico para o diretório original:
ln -s /mnt/hdb/pg_xlog /usr/local/pgsql/data/pg_xlog
- Banco - /usr/local/pgsql/data (no hda)
- Logs - /usr/local/pgsql/data/pg_xlog (link simbólico para /mnt/hdb/pg_xlog).
Os logs de transação são os únicos registros que não podem ter o seu salvamento em disco adiado sem comprometer a segurança do sistema.
Mover os índices para um HD diferente de onde estão as tabelas:
- Parar PostgreSQL
- Mover os índices para o hdb
- Criar link simbólico para o local original
Para recriar os índices em outro Tablespace:
ALTER TABLE nometabela DROP CONSTRAINT nomeconstraint;
CREATE INDEX nome_idx ON nometabela (nomecampo) TABLESPACE nometablespace;
ALTER TABLE nometabela ADD CONSTRAINT nome_pk PRIMARY KEY (nomecampo);
ALTER INDEX nome_idx SET TABLESPACE nometablespace;
Ainda podemos separar astabelas mais utilizadas para o hdb, utilizando o comando tablespace no PostgreSQL 8.1.3 podemos fazer isso:
- Criar diretório /mnt/hdb/hotcluster e tornar postgres seu dono
CREATE TABLESPACE hotcluster OWNER postgres LOCATION '/mnt/hdb/hotcluster';
Criando um banco no novo cluster:
CREATE DATABASE hotbanco TABLESPACE = hotcluster;
Exportar as tabelas para este banco.
Uso de Mais de Um Processador
Atualmente o PostgreSQL está otimizado para uso de vários processadores, reforçando que cada conexão é gerenciada por um processo diferente.
Sistemas de Arquivos
Para sistemas BSD usa-se o tradicional UFS, que é robusto, rápido e tem a vantagem em relação ao PostgreSQL, de possuir os blocos de disco com um tamanho padrão de 8KB.
Para quem utiliza Linux as sugestões vão para EXT3 e ReiserFS.
Checkpoints
O wal_files é o parâmetro do postgresq.lconf que determina o número de arquivos usados pelo PostgreSQL para armazenar os logs de transação. Estes arquivos focam em pg_xlog, na pasta de dados.
Para que apareçam as datas e horas nos arquivos de logs usa-se no postgresql.conf:
log_timestamp = true
Para reduzir a freqüência dos checkpoints devemos aumentar o parâmetro do postgresql.conf:
checkpoint_segments = 3 (valor default)
O PostgreSQL não precisa de muito ajuste. Boa parte dos parâmetros é automaticamente ajustada para uma performance ótima. O cache size e sort size são dois parâmetros que o administrador pode controlar para ter um melhor uso da memória.
Tradução do Tutorial “Tuning PostgreSQL for Performance”
De Shridhar Daithankar e John Berkus
Shared Buffers
Definem um bloco de memória que o PostgreSQL usará para lidar com requisições que estão aguardando atenção no buffer do kernel e na CPU.
Deve ser manipulada com cuidado, pois simplesmente ampliada pode prejudicar a performance. Esta é a área que o PostgreSQL usa atualmente para trabalhar. Ela deve ser suficiente para controlar a carga do servidor do SGBD, do contrário o PostgreSQL irá iniciar empurrando dados para arquivos e isto irá prejudicar a performance geral. Esta é a principal configuração em termos de performance.
Seu valor deve ser configurado tendo em vista o tamanho do conjunto de bancos que se supões que no máximo o servidor irá carregar e da memória RAM (ter em mente que a memória RAM utilizada pelos demais aplicativos do servidor não estarão disponíveis).
Recomendações:
- Iniciar com 4MB (512) Workstation
- Médio tamanho do conjunto de bancos de dados e 256 a 512MB disponível de RAM: 16-32MB (2948 a 4096)
- Grande conjunto de bancos de dados e muita memória RAM disponível (1 a 4GB): 64 -256MB (8192 a 32768)
Obs.: Até para um conjunto de bancos de dados (dataset) que exceda 20GB, uma configuração de 128MB deve ser muito, caso você tenha apenas 1GB de RAM e um agressivo sistema de cache em Sistema Linux.
Sort Memory (Memória para Ordenação)
Limite máximo de memória que uma conexão pode usar para executar sort (ordenação). Caso suas consultas usem as cláusulas ORDER BY ou GROUP BY que ordenem grandes conjuntos de dados, incrementar este parâmetro deverá ajudar.
Uma Recomendação:
Ajustar o parâmetro por conexão como e quando precisar: pouca para consultas mais simples e muita para consultas complexas e para dumps de dados.
Effective Cache Size (Tamanho do Cache Efetivo)
Permite ao PostgreSQL fazer melhor uso da RAM disponível no servidor.
Exemplo:
Caso exista 1,5GB de RAM na máquina, shared buffers deve ser ajustado para 32MB e effective cache size para 800MB.
Fsync and the WAL files (Fsync e arquivos de WAL)
Caso não reste nenhuma opção, poderá usar a proteção do WAL e melhor performance. Simplesmente mova seus arquivos de WAL, montando outro dispositivo ou criando um link simbólico para o diretório pg_xlog, para um disco separado ou para o conjunto dos arquivos do seu cluster principal de arquivos de dados.
random_page_cost (custo de página aleatória)
Configura o custo para trazer um registro aleatório de um banco de dados, que influencia a escolha do planejador em usar index ou table scan.
Caso tenha um disco razoavelmente rápido como SCSI ou RAID, pode baixar o custo para 2.
Vacuum_mem
Configura a memória alocada para Vacuum. Deve acelerar permitindo que PostgreSQL copie grandes quantidades para a memória.
Entre 16-32MB é uma boa quantidade para muitos sistemas.
max_fsm_pages
PostgreSQL grava espaço livre em cada uma de suas páginas de dados.
Caso tenha um banco que usa muitos updates e deletes, que irá gerar registros mortos, devido ao sistema MVCC do PostgreSQL, então expanda o FSM para cobrir todos estes registros deads (mortos) e nunca mais precisará rodar vacuum full a não ser em feriados.
O mínimo FSM é max_fsm_relations * 16.
max_fsm_relations
Diz quantas tabelas devem ser localizadas no mapa de espaço livre.
wal_buffers
Esta configuração decide a quantidade de buffers WAL (Write Ahead Log) que pode ter.
Para chegar a uma quantidade ótima experimente e decida.
Um bom início está em torno de 32 a 64 correspondendo a 256-516 KB de memória.
Ativar o subprocesso do auto Vacuum
Vem desabilitado por defualt (autovacuum = off no 8.1.3). Para ativar edite o arquivo de configuração postgresq.conf e altere para autovacuum = on. Irá executar o vacuum quando necessário.
Melhor é executar o comando vacuum juntamente com o comando analyze:
vacuumdb -U postgres -a, caso seja executado na linha de comando.
Para adquirir informações sobre os índices (tornando a performance ainda melhor):
vacuumdb -U postgres -a -z
EXPLAIN
- !/usr/bin/perl
$count = 1;
$arquivosaida = "populate.sql";
@chars = ("A" .. "Z", "a" .. "z", 0 .. 9);
@numbers = (1 .. 9);
@single_chars = ("a" .. "e");
$totalrecords = 5000; # 5 milhões
open(OUTPUT, "> $arquivosaida");
print OUTPUT "DROP TABLE index_teste;\n";
print OUTPUT "CREATE TABLE index_teste (";
print OUTPUT "codigo INT, nome VARCHAR(10), numero INT, letra CHAR(1)";
print OUTPUT ");\n";
print OUTPUT "COPY index_teste (codigo, nome, numero, letra) FROM stdin;\n";
while ($count <= $totalrecords){
$randstring = join("", @chars [map{rand @chars} ( 1 .. 8 ) ]);
$randnum = join("", @numbers [map{rand @numbers} ( 1 .. 8 ) ]);
$randletter = join("", @single_chars [map{rand @single_chars} (1)]);
print OUTPUT
#print OUTPUT "INSERT INTO index_teste VALUES($count,'$randstring',$randnum,'$randletter');\n";
$count."\t".$randstring."\t".$randnum."\t".$randletter."\n";
$count++;
};
- print OUTPUT "\n";
- print OUTPUT "\nCREATE INDEX indexteste_codigo_index ON index_teste(codigo);\n";
- print OUTPUT "CREATE INDEX indexteste_numero_index ON index_teste(numero);\n";
- print OUTPUT "VACUUM ANALYZE index_teste;\n";
close OUTPUT;
Um bom artigo sobre performance no PostgreSQL "PostgreSQL 8.0 Checklist de Performance" encontra-se na revista eletrônica DBFree Magazine, número 02.