PostgreSQL: como fazer um elefante voar

Dicas de tuning e desempenho para PostgreSQL
Se você já esgotou o arsenal de otimizações e tunings de desempenho para PostgreSQL, está na hora de conhecer alguns segredos de ultra desempenho para servidores de banco de dados. Destes que são divulgados como “novo recorde de TPC”, por algumas empresas.

Digamos que você já modelou corretamente seu banco de dados, já normalizou , já criou os índices com seus respectivos tipos mais indicados, já baniu os left outer joins e reescreveu as queries, já analisou suas queries com EXPLAIN e reescreveu-as, seguiu dicas de desempenho do PostgreSQL.

Você analisou os índices de suas tabelas e criou clusters em cada tabela com seus índices. Os ganhos POTENCIAIS são muito bons.

Já analizou os dados de profilers pg_top e pgfouine e reescreveu as queries.

Não existe tuning que resolva um banco de dados mal modelado, e com queries mal concebidas.

Você teria de pagar um preço elevado em força bruta havendo ainda outras formas de obter o desempenho necessário. Deixe isso para outras situações.

Você também modificou sua aplicação para utilizar lazy connections, se for o caso. Ou passou a utilizarconnection pooling, situação bem mais comum e genérica, para poder configurar mais flexivelmente o max_connections.

Depois disso, você resolveu aumentar o número de servidores, usando balanceamento e replicação síncrona ou assíncrona.

Então você decidiu melhorar o hardware, usando mais núcleos de CPU, mais RAM.

Em seguida, aumentou o número de discos para distribuir tabelas por eles gerenciando tablespaces. (voltaremos a esse assunto).

E já descobriu que RAID 5 ou 6 são muito lentos comparados a RAID 1. E ainda possuem problema do write hole por arquitetura.

Voltou uns passos e reviu configurações de memória para índices, espaço de trabalho e outros no postgresql.conf.

Porque o Postgresql não utiliza automaticamente a RAM da melhor forma na configuração default muito conservadora.

Se tiveres recursos, conseguiu adquirir RAM suficiente para armazenar seu dataset ativo todo em RAM.

Aproveitou e reviu as configurações de vacuum, background writer e I/O concurrency (voltaremos a esses).

Como você tem mais discos agora, reavaliou configurações do query planner.

Também lembrou de não ter excessivo log de estatísticas, nem log de erros demais, apenas o mínimo seguro, no melhor local, e para o query planner, pois a aplicação já está depurada neste estágio. Voltaremos a esses tópicos.
Configurou o syslog para comportamento assíncrono, claro. E num disco separado (não basta partição separada).

Ainda não foi suficiente para dar conta da carga.

O que ainda pode ser feito?

De volta aos conceitos.
Você reparou no que é um ACID database como é o PostgreSQL?
Para ter Consistência e Durabilidade, um dos requisitos é armazenar os dados confiavelmente.

Para fazer isso confiavelmente, o PostgreSQL, entre outras características, utiliza o Write Ahead Log, WAL.

WAL, também conhecido por log de transações e REDO log.

Para obter mais desempenho é vital compreendê-lo antes de fazer tuning de WAL:

Resumidamente, o PostgreSQL escreve as transações sincronamente PRIMEIRO no WAL e periodicamente as repassa (checkpoint, flush) para a área durável final.

Releia o parágrafo acima 5 vezes e pense nas implicações de cada palavra e perceberás onde iremos chegar.
O WAL também é utilizado para recursos de replicação, fazendo seu comportamento físico no disco um tanto diferente do inicialmente esperado.

Para PostgreSQL ter melhor desempenho, a LATÊNCIA de acesso ao WAL deve ser baixa.

Lembre: SGBDs escrevem em tamanho de blocos definidos e configuráveis.

Em algumas situações de modelo de dados pode ser necessário alterar o tamanho do bloco. E configurar o sistema todo para isso.

No Blog TechForce há uma boa quantidade de artigos e bibliografia sobre redução de latência de acesso a arquivos pequenos, tuning de kernel, de XenServer, de filesystems, multipath, LVM, data storage, redução de latência de rede para acessos pequenos.
Estude e aplique todos.

Mesmo depois de tudo isso, ainda não é suficiente para a carga exigida.

Chegou a hora de PostgreSQL em mainframe?

Calma. É uma solução de maior custo para algumas situações extremas. Antes disso precisamos explorar as alternativas.

Então vamos estudar os segredos de força bruta das soluções high end em plataforma baixa. Aqui se fala de IOPS não de troughoutput.

Consistência e Durabilidade.

Em 2012, a tecnologia de armazenamento corporativa é “disco”, de algum tipo.
No segmento enterprise, temos discos Enterprise SATA, NL-SAS , SAS, FC-AL, Infiniband SAS, variando de 5,4 krpm a 15 krpm, MLC Flash SSD, SLC Flash SSD, e o topo de linha SLC DRAM SSD.

Uma característica pobre pouco divulgada das Flash SSD é que degradam muito o desempenho e rapidamente.

Algo como de 40 mil IOPS para 5 mil IOPS após poucas centenas de ciclos de escrita.
Basicamente, assim que todas as células tiverem sido escritas uma vez, exigirão uma operação de apagamento para serem reescritas. E isso é lento.

Ainda, Multi Level Cell Flash SSD são mais baratas e lentas que Single Level Cell Flash SSD.

O que ainda não contaram para você:

Vamos conhecer alguns dos segredos de força bruta para appliances servidores de bancos de dados high end.
Vejamos alguns exemplos de armazenamento de altíssimo desempenho. Compare os dados técnicos com o que você conhecia até agora:

A lista não é nem completa nem implica recomendação de qualquer tipo. Apenas uma amostra de cenário corporativo nesta data.
Fusion i-o drive
http://www.dell.com/br/corporativo/p/fusion-io-drive

http://www.fusionio.com/platforms/iodrive2/

Zeus RAM SSD
http://www.stec-inc.com/product/zeusram.php

Gavetas de expansão para instalar discos enterprise em conexão direta
http://www.dell.com/br/corporativo/p/disk-expansion-enclosures
http://www.sgi.com/products/storage/jbod/mis_jbod.html

Data storage completo em RAM
http://www.ramsan.com/products/rackmount-ram-storage/ramsan-420

Data storage flash array
http://www.oracle.com/us/products/servers-storage/storage/flash-storage/f5100/overview/index.html

Quinze microssegundos de latência. ISSO é baixa latência.

De um milhão de IOPS até 50 milhões de IOPS num rack. ISSO é desempenho.

Com esse tipo de hardware, e iSCSI HBA em redes 10 Gb/s segmentadas, ou FC 8 Gb/s ou redes Infiniband você poderá reavaliar e reconfigurar seus servidores, seguindo um roteiro:

1. Vacuum, background writer, I/O concurrency.
2. Tabelas de estatísticas.
3. Log de erros e atividade.
4. Syslog, arquivos temporários.
5. Write Ahead Log, checkpoints, WAL archiving.
6. Clusters sobre os índices em cada tabela.
7. índices em tablespaces próprios.
8. Tablespaces para tabelas mais ativas.
9. Configurações do query planner.

Redistribuir o tablespaces fica por último pois é o passo que demanda mais espaço físico de disco, que é o recurso caro neste estágio. Com o novo desempenho dos discos, o query planner poderia ser reparametrizado para calcular os custos melhor. Avalie a necessidade com o EXPLAIN.

Como você percebeu, isto é um roteiro de orientações, não uma receita.

Tuning envolve muita análise específica de cada situação individual. E cada tópico de análise merece um livro.

Além do banco de dados, você fará tuning do sistema operacional e do hardware para essa aplicação. Os servidores terão dedicação exclusiva, devido à especialização.

Envie suas sugestões como comentários a este artigo.

Bibliografia adicional

http://www.solarisinternals.com/wiki/index.php/ZFS_for_Databases

https://blogs.oracle.com/paulvandenbogaard/entry/running_postgresql_on_zfs_file

http://blog.dryft.net/2011/09/benchmarking-zfs-xfs-ext4-and-btrfs.html