FACULDADE DE ADMINISTRAÇÃO E NEGÓCIOS DE
SERGIPE - FANESE
NÚCLEO DE PÓS-GRADUAÇÃO E EXTENSÃO – NPGE
CURSO DE PÓS-GRADUAÇÃO “LATO SENSU”
ESPECIALIZAÇÃO EM BANCO DE DADOS
MELHORIAS NA PERFORMANCE DO POSTGRESQL
BRUNO VIEIRA DE MENEZES
ARACAJU – SE
2013
2
BRUNO VIEIRA DE MENEZES
MELHORIAS NA PERFORMANCE DO POSTGRESQL
Artigo Científico apresentado ao Núcleo de Pósgraduação e Extensão da Faculdade de
Administração e Negócios de Sergipe como
exigência para obtenção do título de Especialista
em Banco de Dados.
Orientação: Clailson S. D. de Almeida
ARACAJU – SE
2013
3
COMO AUMENTAR A PERFORMANCE DO POSTGRESQL
Bruno Vieira de Menezes 1
RESUMO
O artigo fala de algumas dicas e configurações para uma melhor performance do banco de
dados PostgresSQL. Dicas como melhor configuração do Hardware (memória RAM, Discos,
entre outros) e alguns dos principais parâmetros de configuração do banco de dados que pode
ajudar numa melhor performance desde que usados corretamente. Uma boa configuração para
o PostgreSQL varia de acordo com a realidade em que o banco irá trabalhar, se será bastante
requisitado ou não, se as consultas serão demoradas, entre outras situações.
PALAVRAS CHAVE
Postgresql; Banco de Dados; Hardware; Performance; Configuração.
SUMMARY
The article talks about some tips and settings for better performance from the database
PostgresSQL. Tips for best hardware configuration (RAM, disks, etc.) and some key
configuration parameters of the database which can help in better performance since used
correctly. A good setting for PostgreSQL varies with the reality in which the bank will work,
whether it is requested or not enough, if queries are time consuming, among other situations.
KEY WORDS
Postgresql; Database; Hardware; Performance; Configuration.
1
Formado em Sistemas para Internet, pela Faculdade de Administração e Negócios de Sergipe e Desenvolvedor
de Sistemas Pleno atuando na Xprocess – Tecnologia de Gestão Empresarial Aracaju/SE. E-mail:
[email protected]
4
1. INTRODUÇÃO
Atualmente, boa parte dos negócios estão informatizados, desde os Bancos aos
pequenos estabelecimentos no comércio. Todos usam algum determinado sistema que
armazena as informações que aparecem na tela do computador em um banco de dados.
Quando fazemos alguma consulta do nosso saldo bancário, por exemplo, o
sistema vai buscar essas informações dentro do banco de dados e a cada dia que passa, nós
usuários exigimos mais agilidade no retorno da informação quando solicitada. Por conta disso
existem configurações que são feitas para que o banco de dados trabalhe de uma forma
melhor e trazendo as informações mais rápidas ao usuário final. Para essas informações
chegarem cada vez mais rápidas, é necessário que o banco de dados tenha uma boa
performance.
Essa performance para o bando de dados não significa apenas trazer a
informação o mais rápido possível, trata-se de dar um retorno de forma mais rápida exigindo
o mínimo de esforço possível ao software e hardware.
Abordaremos neste artigo o SGBD PostgreSQL. Serão comentadas algumas
das possíveis configurações, desde o hardware ao software, para que tenha uma melhor
performance.
2. CONFIGURAÇÕES DO POSTGRESQL PARA MELHORAR A PERFORMANCE
Uma boa configuração para o PostgreSQL varia de acordo com a realidade em
que o banco irá trabalhar, se será bastante requisitado ou não, se as consultas serão
demoradas, entre outras situações.2
2
As informações de configuração têm como base a tradução do livro "PostgreSQL 8.0 Performance Checklist"
Publicado por Josh Berkus em 12/01/2005 (em http://www.powerpostgresql.com/PerfList) feira por Fábio
Telles Rodriguez e o Artigo “Database Tuning: Configurando o Interbase e o PostgreSQL” de Gregório Baggio
Tramontina.
5
De acordo com o artigo publicado por Bruce Momjian, podemos dizer que
existem duas principais formas para melhorar o desempenho de SGBDs: melhorando o
Hardware (CPU, Memória RAM, Discos novos, rápidos e confiáveis) e otimizando as
consultas realizadas (usando VACUUM, VACUUM ANALYZE, EXPLAIN, entre outros).
Além dessas duas formas citadas por Bruce, existe uma terceira muito
importante que é a correta configuração do SGBD. Não adianta ter um bom hardware se não
souber configurar de forma correta o SGBD, pois dessa forma o SGBD usará os recursos
disponíveis de melhor forma trazendo um resultado mais rápido.
De acordo com Berkus, temos cinco princípios de hardware para configurar o
servidor.
3 CINCO PRINCÍPIOS DE HARDWARE PARA CONFIGURAR O
SERVIDOR
3.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.” (BERKUS, 2005)
PostgreSQL, assim como outros SGDBs que suportam ACID, utilizam E/S
muito intensamente e normalmente usa mais a placa SCSI do que a CPU (existindo exceções).
Isto se aplica independente de serem pequenos ou grandes servidores. O ideal é obter uma
CPU com custo baixo se isso permitir você comprar uma placa RAID de alta performance ou
vários discos.
3.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. (BERKUS, 2005)
Berkus ainda diz que se for utilizado 4, 6 ou até 14 discos irá render um
aumento no desempenho. O SCSI é ainda significativamente melhor em fluxo de dados em
BD que um IDE ou mesmo um Serial ATA.
6
3.3 Separe o Log de Transações do Banco de Dados:
Levando-se em consideração que já foi investido dinheiro com um bom
tamanho num conjuntos de discos, existem várias opções ideais ao invés de jogar tudo em um
unido RAID.
De acordo com Berkus (2005), inicialmente deve ser colocado o log de
transações (pg_xlog) no seu próprio discos (um arranjo ou um disco), o que causará uma
diferença de cerca de 12% na performance de banco de dados que têm grande volume de
gravações. Ele ainda completa dizendo que:
“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.”
3.4 RAID 1+0/0+1 > RAID 5:
O RAID 5 com 3 discos tem sido comum entre os vendedores de servidores
econômicos, porém, essa configuração é a mais lenta de discos para o PostgreSQL. Com essa
configuração é esperado, pelo menos, 50% a menos de velocidade nas consultas em ralação
ao obtido com discos SCSI normais.
Com um conjuntos de 2, 4 ou 6 discos, o ideal é focar no RAID 1 ou 1+0. Caso
seja acima de 6 discos, o RAID 5 irá começar a ter uma performance aceitável e comparando
com uma controladora individual, tende a ser bem melhor.
Berkus ainda destaca um ponto como 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.”.
3.5 Evitar colocar no mesmo servidor o PostgreSQL e outras aplicações
“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.” Berkus (2005)
7
Um dos piores caso é quando o PostgreSQL é coloca na mesma máquina com
outros SGBDs, pois os bancos irão lutar pela banda de acesso ao disco e cachê de disco do
SO, com isso ambos terão uma performance muito baixa.
Caso seja garantido que haja memória RAM suficiente, o PostgreSQL poderá
compartilhar a mesma máquina com aplicações que utilizam principalmente CPU e RAM
intensamente, como o Apache.
4 MEMÓRIA CACHE COMPARTILHADA
O PostgresSQL não altera diretamente as informações no disco, ele solicita que
os dados sejam lidos da memória cache compartilhada, então o cliente PostgresSQL lê e
escreve os bloco para finalmente escrever no disco.
Quando é preciso acessar as tabelas no banco, primeiramente é procurado pelos
blocos necessários no cachê, caso encontre o processo é dado continuidade, caso contrário é
feito uma solicitação ao SO para que os blocos sejam carregados, assim as consultas podem se
tornar mais lentas.
Na configuração padrão do PostgreSQL, é alocado 1000 shared buffers, sendo
que cada buffer usa 8KB, o que soma 8MB. Caso esse número seja aumentado, o cache terá
mais informações, dessa forma diminuindo a necessidade de solicitações de acesso ao disco.
Deve-se ter cuidado ao aumentar essa quantidade de memória, pois se
exagerada pode acarretar o uso do swap, o que terá que haver acesso ao disco e tornará as
consultas mais lentas.
5 QUAL PORÇÃO DA RAM E QUAL TAMANHO IDEAL DO CACHE
DEVE SER RESERVADO?
A porção da memória RAM deve ser a maior possível desde que não atrapalhe
os outros programas. Pois caso seja necessário o SO utilizar o swap poderá causar lentidão.
O tamanho ideal para o Cache (shared buffer) tem que ser suficiente para
conseguir manipular tabelas que comumente sejam acessadas. Caso o Cache não tenha o
tamanho suficiente, há a necessidade de usar o swap (onde as informação irão para o disco)
fazendo com que tenha um desempenho inferior.
8
6 ALGUNS PARÂMETROS DE CONFIGURAÇÃO DO POSTGRESQL
Os arquivos de parâmetros de configuração ficam em um arquivo chamado de
postgresql.conf. Cada linha do arquivo especifica um parâmetro e cada parâmetro assume um
valor inteiro, booleano, de ponto flutuante ou string.
Para fazer alterações no valor de um parâmetro, pode-se simplesmente editar o
arquivo ou passar na linha de comando para o servidor quando é iniciado.
Abaixo TRAMONTINA, em seu artigo Database Tuning: Configurando o
Interbase e o PostgreSQL, destaca alguns desses parâmetros.
6.1 shared buffers (MEM)
Controla o número de buffers de memória compartilhada utilizados pelo
servidor. Cada buffer possui 8192 bytes (8Kb), a não ser que um tamanho diferente tenha sido
especificado em BLCKSZ durante a compilacão do servidor. Um valor mais alto nesse
parâmetro pode significar maior desempenho, embora um valor muito grande possa trazer
resultados inversos. 128MB é o valor default.
6.2 fsync (MEM)
Um valor booleano que, quando verdadeiro, determina que o sistema deve
realizar a chamada de sistema fsync() em vários pontos da execução das transações para
garantir que os dados modificados sejam escritos no disco. Manter este parâmetro ligado
causa uma degradação no desempenho, já que quando ele está desabilitado o sistema
operacional pode tentar fazer o seu melhor em termos de buffer-ing, ordenação e retardo das
escritas em disco. No entanto, desabilitá-lo deixa o sistema vulnerável a problemas de
persistência nos dados, já que até operações de escrita do log em disco (que seguem o
protocolo WAL) são deixadas a cargo do sistema operacional. A documentação do
PostgreSQL recomenda que “se você confia no seu sistema operacional, hardware e empresa
de suporte (ou baterias de backup), considere desabilitar este parâmetro”.
6.3 enable hasjoin, enable mergejoin, enable nestloop (PLN)
9
Habilita ou desabilita o uso de planos de consulta com junções feitas através de
hash join, merge join e nested loops, respectivamente.
6.4 enable indexscan (PLN)
Liga ou desliga o uso, por parte do planejador, de index scans nas relações,
quando possível.
6.5 cpu tuple cost, cpu index tuple cost, cpu operator cost (PLN/CPU)
Valores de ponto flutuante que especificam o custo de CPU assumido pelo
planejador para processar, respectivamente, uma tupla de uma relação, uma entrada de índice
em um index scan, e um operador na cláusula WHERE de uma consulta. Esse custo é dado
por uma fração do custo de ler páginas seqüencialmente do disco. Os valores default são, em
ordem, 0.01, 0.001, e 0.0025.
6.6 geqo (PLN)
O PostgreSQL também implementa um algoritmo genético para otimizar as
consultas feitas no servidor. Este parâmetro é um valor booleano que habilita ou desabilita o
uso de otimização genética. O valor default é ligado.
6.7 deadlock timeout (CCT)
Tempo, em milissegundos, que uma transação espera por um lock antes de se
fazer uma checagem de ocorrência de deadlocks. Em sistemas com grande carga de trabalho
pode ser útil aumentar esse tempo, para diminuir o tempo gasto pelo PostgreSQL para realizar
essa verificação e tomar menos recursos do sistema. Em um cenário ideal esse valor seria
maior que o tempo médio de uma transação, para se aproveitar do fato de que os locks serão
provavelmente liberados antes que se decida pela verificação. O valor default é 1s.
7. UTILIZANDO O VACUUM
10
Um grande número de inserções, atualizações e exclusões, podem ocasionar
uma perda de performance no PostgreSQL. Para auxiliar o DBA na correção dessa perda de
performance, existe o VACUUM.
O VACUUM surgiu a partir da versão 6.5 utilizando o modelo MVCC
(Multiversion Concurrency Control). Esse modelo tem como objetivo auxiliar na
implementação do padrão ACID (atomicidade, consistência, isolamento e durabilidade).
Em um ambiente onde vários usuários podem estar acessando os mesmos
dados, o modelo MVCC garante a melhora de performance, porém esse modelo ocasiona o
aumento do tamanho das tabelas de forma rápida. No PostgreSQL, se um registro é atualizado
por um usuário, internamente ele não é atualizado. Esse registro é marcado como inútil para a
base de dados e sua referência de índice é perdida sendo apontado para um novo registro que
é criado.
No exemplo abaixo, existe uma tabela de lista de clientes devedores, onde
ocorre o uso do UPDATE quando Maria paga 2.500 de sua dívida. Ao invés do registro ser
alterado, ele é marcado para sua posterior remoção e é criado um novo registro com o novo
valor da dívida de Maria.
Fonte: iMaster
Data: 27/02/2013
UPDATE Clientes SET Debito = Debito – 2500 WHERE cod = 2
11
Fonte: iMaster
Data: 27/02/2013
O mesmo ocorre numa situação de exclusão de registro, ele apenas fica
marcado. Assim agora é mais fácil entender o porquê que as tabelas crescem tão rapidamente.
O VACUUM surgiu como uma ferramenta para poder organizar esses
registros. Ele faz uma varredura nas tabelas buscando os dados marcados como inúteis e os
exclui, logo após, realoca os registros de forma que não fiquem espaços entre eles. A
depender de como for executado, além de fazer essa varredura, ele também atualiza as
estatísticas utilizadas pelo otimizador para determinar o modo mais eficaz de executar uma
consulta.
Abaixo um exemplo de uma tabela que foi utilizado o VACUUM:
12
Fonte: iMaster
Data: 27/02/2013
A sintaxe para utilização do VACUUM é a seguinte:
VACUUM [FULL] [FREEZE] [VERBOSE] [tabela] VACUUM [FULL] [FREEZE] [VERBOSE]
ANALYZE [tabela [ (coluna[,...])]]
Parâmetros:
FULL
Seleciona uma limpeza “completa”, que pode recuperar mais espaço,
mas é muito mais demorada e bloqueia a tabela em modo exclusivo.
FREEZE
Seleciona um “congelamento” agressivo das tuplas.
VERBOSE
Produz um relatório detalhado da atividade de limpeza de cada tabela.
ANALYZE
Atualiza as estatísticas utilizadas pelo otimizador para determinar o modo mais
eficiente de executar uma consulta.
tabela
O nome da tabela específica a ser limpa. O padrão é que sejam todas as tabelas
do banco de dados corrente.
coluna
O nome da coluna específica a ser analisada. O padrão e também o recomendado
é que sejam todas as colunas.
8. ALGUMAS BOAS PRÁTICAS AO ESCREVER AS CONSULTAS
8.1 Referências de coluna totalmente qualificada em join
O SGBD não precisará procurar nas tabelas cada coluna utilizada nas colunas.
Ao usar nomes para as tabelas, escrever as colunas com os respectivos nomes de tabelas, isso
evitará do SGBD procurar em qual tabela estará a coluna.
Escrita ruim:
13
SELECT p.name, pt.name, description, price FROM products p, product_types
pt WHERE p.product_type_id = pt.product_type_id AND p.product_id = 1;
Boa Escrita:
SELECT p.name, pt.name, p.description, p.price FROM products p,
product_types pt WHERE p.product_type_id = pt.product_type_id AND p.product_id = 1;
8.2 Usar Expressão Case em vez de várias consultas
Quando possível enviar apenas uma consulta ao SGBD ao invés de várias.
Escrita Ruim:
SELECT count(*) FROM products WHERE price < 13;
SELECT count(*) FROM products WHERE price BETWEEN 13 AND 15;
SELECT count(*) FROM products WHERE price > 15;
Boa Escrita:
SELECT
count( CASE WHEN price < 13 THEN 1 ELSE null ) as low,
count(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END )
) as med, count(CASE WHEN price > 15 THEN 1 ELSE null END ) ) as high, FROM
products;
8.3 Utilizar UNION ALL em vez de UNION
O UNION remove as linhas duplicadas, dessa forma há um esforço do SGBD.
Se possível utilizar o UNION ALL quando tiver certeza que não existirá duplicação.
Escrita Ruim:
SELECT product_id , product_type_id, name FROM products
UNION
SELECT product_id , product_type_id, name FROM more_products
Boa Escrita:
SELECT product_id , product_type_id, name FROM products
UNION ALL
SELECT product_id , product_type_id, name FROM more_products
14
8.4 Utilizar Exists em vez de IN
O IN verifica a existência de valores reais, já o Exists verifica apenas a
existência da linha.
Escrita Ruim:
SELECT product_id , name FROM products
WHERE product_id IN (SELECT product_id FROM purchases)
Boa Escrita:
SELECT product_id , name FROM products outer
WHERE
product_id EXISTS (SELECT 1 FROM purchases inner WHERE inner.
product_id = outer.product_id )
15
9 CONSIDERAÇÕES FINAIS
De acordo com o que foi apresentado em termos de configurações, verificamos
que apenas uma configuração boa do hardware ou apenas uma boa configuração no
postgresql.conf não resolve muita coisa.
O ideal é que os dois sejam bem configurados e, assim, trabalhando juntos vão
obter uma boa performance no retorno de informações, por exemplo.
Percebemos também que não se deve utilizar uma configuração padrão para
todos os ambientes possíveis com pouca, média, ou bastante requisições ao banco de dados.
Para cada situação há uma forma de se configurar o banco com o intuito de fazer que o banco
trabalhe na melhor performance possível.
16
REFERÊNCIAS BIBLIOGRÁFICAS
MILANI, André. PostgreSQL - Guia do Programador. Edição 1. Nova Tec. 2008.
http://wiki.postgresql.org/wiki/Checklist_de_Performance_do_PostgreSQL_8.0, acessado em
18/12/2011.
http://www.ic.unicamp.br/~geovane/mo410-091/Ch20-ConfigInterbasePosgres-art.pdf,
acessado em 18/12/2011.
http://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Ap%C3%AAndices/Dicas_sobre_
Desempenho_e_Otimiza%C3%A7%C3%B5es_do_PostgreSQL, acessado em 04/02/2013
http://momjian.us/main/writings/pgsql/hw_performance/, acessado em 04/02/2013
http://imasters.com.br/artigo/2421/postgresql/melhorando-a-performance-do-postgresql-como-comando-vacuum/, acessado em 27/02/2013
http://wiki.postgresql.org/wiki/Dicas_de_Performance_em_aplica%C3%A7%C3%B5es_com
_PostgreSQL, acessado em 27/02/2013