Infra/PostgreSql

De Wiki Expresso V3
Ir para: navegação, pesquisa

Conteúdo

Alto desempenho com PostgreSQL

Abordaremos tópicos principais a considerar para uma boa instalação, manutenibilidade e desempenho de um servidor PostgreSQL rodando sobre Debian GNU/Linux.

Por gentileza, debates, correções, sugestões sobre o assunto devem prosseguir no tópico do fórum

http://comunidadeexpresso.serpro.gov.br/portal/index.php?option=com_kunena&view=topic&catid=9&id=136&Itemid=482&lang=pt-BR


Corrigir primeiro a aplicação

O banco de dados da aplicação está corretamente modelado?

Não existe tuning que compense uma modelagem ruim.

O banco de dados da aplicação está corretamente normalizado?

Já existem os corretos índices no banco de dados da aplicação? Não tem como o servidor alcançar alto desempenho sem bons índices. Índices incorretos podem até prejudicar o desempenho.

Se a aplicação utiliza um framework, você domina a forma como o garbage collector fecha as conexões ao servidor de banco de dados?

Todas as instruções SQL já estão encapsuladas em uma transação? A melhor forma de reaproveitamento de conexões ao banco num pool depende de que todas as operações ao banco logicamente encadeadas de forma atômica sejam encapsuladas em uma transação. Senão o programa de pooling não pode distinguir quando pode reaproveitar uma conexão parada, pois ela poderia estar aguardando o final de outro processamento, quando na realidade está esperando o garbage collector encerrá-la. Apenas esta providência pode resultar uma melhoria de até 40% no comportamento do servidor de banco de dados. E ainda sem começar nenhum tuning.

LEFT OUTER JOIN is evil. Fuja como o diabo da cruz desse tipo de instrução sql. Avalie a modelagem. Avalie como o framework mapeia os objetos para o modelo relacional. Utilize o framework de forma otimizada para evitar que ele gere sql assim.

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

http://www.bennadel.com/blog/2244-performance-of-left-outer-join-insert-vs-inner-join-delete-statement.htm

As versões atualizadas do Expresso V3 já implementaram estas melhorias em razoável extensão. Outras aplicações precisam ser corrigidas.

http://virtualdeveloper.blogspot.com.br/2009/08/outer-joins-nulls-and-related-evil.html

http://en.wikipedia.org/wiki/Join_%28SQL%29#Left_outer_join

http://en.wikipedia.org/wiki/Join_%28SQL%29#Full_outer_join

Escolha do hardware

CPU

Idealmente, o maior número de núcleos REAIS, com a maior velocidade, com maiores caches possíveis L1, L2 e até L3, nessa ordem, e com MUUUUUITA memória RAM.

O custo de cache L1 é maior que L2 que é maior que L3 (quando este houver).

Núcleos REAIS são mais caros que virtuais (HT).

Freqüência ("velocidade") é cara.

Maior número de núcleos dentro de um mesmo soquete é mais conveniente e com maior desempenho devido à proximidade, mas esquenta mais para uma mesma freqüência. Assim fabricam só freqüências menores (mais lentos e frios). Um trade-off.

A resposta não é simples de obter, mas se escolher bem o kernel e tudo for bem configurado e tunado, prefira o de maior freqüência em vez de maior quantidade de núcleos por soquete. Mas isso é apenas orientação geral, pois evoluções nas CPU as tornam mais eficientes, fornecendo desempenho com menor freqüência. Veja exemplos em http://www.cpubenchmark.net

Prefira menor número de soquetes. Pode ser que implique em maior número de núcleos por soquete, então cuidado com freqüência e seu aquecimento.

Mas isso vai depender da evolução de cada versão de kernel e do hardware cpu e chipset. A cada ano isso pode mudar.

Estude o documento http://www.intel.com/content/dam/www/public/us/en/documents/white-papers/virtualization-xeon-core-count-impacts-performance-paper.pdf Especialmente o freq/vCPU X arquitetura e número de soquetes e núcleos.


HT ou não?

Intel Hyper Threading é uma tecnologia de pipelining de instruções que promete ATÉ mais 30% (em casos ideais irreais) de desempenho usando apenas 5% a mais de silício.

Portanto, 2 "núcleos" são ATÉ 1,3 núcleo na verdade. E apenas para alguns perfis de execução de instruções. Em alguns casos pode piorar.

As mais novas versões de kernel Linux, FreeBSD, Illumos (sucessor do kernel OpenSolaris) já incorporam código para procurar utilizar primeiro os núcleos REAIS e depois os virtuais.

A cada nova versão melhoram esse código de distribuição de processos entre os núcleos reais e virtuais, mas não existe mágica.

Prefira adquirir maior número de núcleos REAIS ou leve em conta que o máximo de desempenho possível é ATÉ 1,3 vezes o núcleo real.

E considere que queries executando em núcleos virtuais irão ser mais lentas.

NUMA

Em 2013 não é mais viável construir máquinas SMP com mais de oito núcleos. As contenções e latências não justificam mais de 8 núcleos.

Portanto, todas os fabricantes de máquinas modernas estão evoluindo seu hardware para NUMA.

http://en.wikipedia.org/wiki/Non-uniform_memory_access

ECC-RAM

Toda a ECC-RAM que você puder comprar e o servidor suportar.

http://en.wikipedia.org/wiki/ECC_memory

Idealmente, seus bancos de dados no servidor deveriam estar todos contidos em RAM, especificamente no tamanho dos shared buffers, que serão configurados a seguir apenas como PARTE da memória. Atenção ao PARTE da memória.

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

http://rhaas.blogspot.com.br/2012/03/tuning-sharedbuffers-and-walbuffers.html

http://momjian.us/main/writings/pgsql/hw_performance/

http://www.varlena.com/GeneralBits/Tidbits/perf.html

Leve em conta a expansão do volume de dados armazenados durante o ciclo de vida útil do equipamento (período de garantia).

Se for viável expandir ECC-RAM depois da aquisição inicial, utilize os pentes de maior capacidade que viabilizem ao serem todos instalados a capacidade máxima. Siga as recomendações do fornecedor do equipamento. E cuidado ao mesclar lotes e fabricantes. Talvez não valha a pena o risco e seja melhor adquirir tudo de uma vez.

ECC-RAM veloz, a máxima velocidade suportada pelo equipamento.

De boa qualidade, pois erros corromperão os dados do banco de dados silenciosamente.

Se não for possível adquirir ECC-RAM para conter todo os bancos de dados, adquira ao menos o dataset ativo diário. Usualmente 10% do tamanho dos bancos de dados ou o tamanho da tabela mais utilizada, o que for maior, como ponto de partida. Mas depende de cada aplicação e perfil de uso.

Quando as tabelas mais acessadas ficam um pouco maior que os caches, a perda de desempenho é assustadora. É o cache thrashing http://en.wikipedia.org/wiki/Thrashing_%28computer_science%29.


https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

http://www.revsys.com/writings/postgresql-performance.html

http://www.kennygorman.com/wordpress/?p=239

http://www.linux.com/learn/tutorials/394523-configuring-postgresql-for-pretty-good-performance

http://www.postgresql.org/docs/9.1/static/storage-fsm.html

max_fsm_pages foi removido a partir de 8.4. Agora são arquivos que contém o mapa de espaço reusável. Aumentou a importância de cache de disco e sistema de arquivos e desempenho do armazenamento.

http://wiki.postgresql.org/wiki/Deprecated_Features

http://www.postgresql.org/docs/8.4/static/release-8-4.html

http://www.varlena.com/GeneralBits/Tidbits/perf.html#maxfsmp

https://wiki.postgresql.org/wiki/Checklist_de_Performance_do_PostgreSQL_8.0


queda abrupta de desempenho conforme tamanho de tabela no PostgreSQL e MySQL (inclusive para Zabbix)

A discussão abaixo é sobre 2 casos específicos, mas de aplicação para vários serviços que necessitem tabelas muito grandes.

Já observamos que há queda abrupta de desempenho se o log de acessos fica maior de 2,5 milhões de registros (em nosso hw usado em 2012, hoje o limite é outro).

Também observamos similar queda significativa de desempenho no MySQL de Zabbix quando há muitos milhões de registros históricos.


A causa está no limite de cache size.


O "joelho" ocorre quando o dataset excede o tamanho do cache size num full sequential scan.

Isso faz um thrashing completo do cache.

Está simplificadamente explicado na seção "Effects of cache size" na página http://www.raj2u.net/postgresql-performance-tuning.html

Quais as soluções possíveis?

Esvaziar periodicamente as tabelas de logs é solução radical e descarta dados. Exportar ou copiar os dados a outra tabela antes de esvaziar é uma solução de contorno. Apagar seletivamente numa tabela gigantesca é uma operação lenta demais para efeitos práticos.


Aumentar demais o(s) cache(s) é imediatista e de curto prazo.

Tabelas de logs crescem indefinidamente e em algum tempo enfrentaremos o problema novamente, apenas em outro patamar.

E se o Sistema Operacional gerencia bem os buffers, pode até prejudicar aumentar demais.

http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm

http://wiki.postgresql.org/wiki/Number_Of_Database_Connections

http://www.anchor.com.au/hosting/dedicated/Tuning_PostgreSQL_on_your_Dedicated_Server

http://rhaas.blogspot.com.br/2012/03/tuning-sharedbuffers-and-walbuffers.html

http://packages.debian.org/pgtune

http://www.manniwood.com/postgresql_stuff/index.html

http://etutorials.org/SQL/Postgresql/Part+I+General+PostgreSQL+Use/Chapter+4.+Performance/How+PostgreSQL+Organizes+Data/


Utilizar locale=C e modificar encoding=UTF8 ou LATIN1

Essa ainda vamos testar, mas não promete ganhos estelares.

http://gmod.org/wiki/PostgreSQL_Performance_Tips


Utilizar cluster de índices sobre as tabelas.

http://www.postgresql.org/docs/8.3/static/sql-cluster.html

As queries sobre tabelas de logs acabam provocando full sequential scan. Não deve ajudar muito... http://reorg.projects.pgfoundry.org/


Particionar tabelas

O que deverá resolver para longo prazo nessa situação é PARTICIONAR TABELAS, usando triggers em vez de rules para maior desempenho e compatibilidade.

http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

http://blog.enfranchisedmind.com/2006/11/postgres-for-the-win/

http://www.day32.com/MySQL/Meetup/Presentations/postgresql_partitioning_short.pdf

http://www.webhostingskills.com/articles/freebsd_postgresql_tuning_the_database_server

http://blog.akquinet.de/2014/04/07/postgresql-partitioning-big-tables-part-1/

Dependendo do seu hardware, terá também de fazer realocação dos tablespaces para outros discos.


Esta abordagem também é recomendada para o Zabbix com tabelas históricas gigantes

http://www.slideshare.net/xsbr/alexei-vladishev-zabbixperformancetuning

inclusive já documentado para PostgreSQL

https://www.zabbix.org/wiki/Docs/howto/zabbix2_postgresql_partitioning

https://www.zabbix.org/wiki/Docs/howto/zabbix2_postgresql_autopartitioning

http://www.zabbix.com/wiki/non-english/ru/partitioning_in_postgresql?s[]=partitioning

e MySQL

http://www.zabbix.com/wiki/non-english/ru/partitioning_in_mysql?s[]=partitioning


Particionar tabelas não exime da necessidade de fazer os tunings para seu cenário

http://www.techforce.com.br/news/linux_blog/postgresql_tuning_fazer_elefante_voar


Precisamos planejar um bom particionamento (e os triggers) para as tabelas do V2 e do V3.

Data Storage Server

É um subsistema CRÍTICO e VITAL para o desempenho final do servidor.

Armazenamento rápido define o desempenho e a experiência do usuário.

Baixa latência e alto IOPS de escritas síncronas aleatórias de arquivos pequenos são essenciais.

A não ser talvez guardar toda a base em ECC-RAM, nada mais consegue contornar PARCIALMENTE um baixo desempenho de armazenamento. E isso porque em algum momento (checkpoint) VAI ter de escrever no armazenamento final e ainda tem o vacuum full analyze.

O melhor funcionamento e relação custo benefício foi encontrado com ZFS data storage servers.


ZFS data storage server

Os testes são aplicáveis também a outras tecnologias de armazenamento, pois são focados em resultados finais.


https://comunidadeexpresso.serpro.gov.br/mediawiki/index.php/Infra/DataStorageServers

Interfaces de rede

Fiber Channel Host Bus Adapter FC-HBA

Escolha do software

Virtualização ou Bare Metal?

Bare Metal

Melhor opção possível para desempenho.

Nem sempre viável economicamente para um dado projeto.

Pode ser necessário justificar volume, carga de uso, custos e lucros.

Se for necessário atender mais de um projeto no mesmo servidor, antes de partir para Zones, Jails ou Containers, vale a pena considerar criar um "cluster" adicional de PostgreSQL no mesmo servidor. (Cluster na nomenclatura do Pg, que é praticamente outra instância do PostgreSQL)

http://www.postgresql.org/docs/9.2/static/creating-cluster.html

No Debian tem scripts para facilitar:

http://www.techforce.com.br/news/linux_blog/postgresql_upgrade_8_4_a_9_1_novo_pgdata_sobre_debian

Mas cuidado com a distribuição e gestão da carga provocada por cada projeto. Pode ser necessário partir para os Zones, Jails ou Containers.

Containers, Jails, Zones

Ótima opção em termos de compartilhamento de desempenho entre quase instâncias virtuais de um mesmo kernel. Baixíssimo overhead de processamento e I/O.

Zones do illumos e do Solaris são as soluções mais maduras. Desde 2005 em produção.

http://en.wikipedia.org/wiki/Solaris_Containers

Também temos os Jails do FreeBSD como solução madura. Desde 1998 em produção.

http://en.wikipedia.org/wiki/FreeBSD_jail

Mais recente solução no mercado são os Containers do Linux. Evolução rápida. Atenção em avaliar a estabilidade e gerenciamento de recursos. Os containers disponíveis desde 2008, mas com maior isolamento a partir de 2012. O gerenciamento com Docker a partir de 2013.

http://en.wikipedia.org/wiki/Linux_container

Outros: Xen, KVM, VmWare

Uma das piores opções possíveis em termos de desempenho. Só perde para virtualizadores de emulação completa.

Evite ao máximo, pois mesmo com bastante tunings de kernel e do virtualizador escolhido, o impacto especialmente em desempenho de I/O é grande.

Fora as limitações em tamanho máximo das VM, em número de núcleos e tamanho de RAM.

Pode ser adequado e suficiente para volumes de carga governamentais modestos. Nestes cenários é uma opção válida, pois as vantagens de gerenciamento são grandes.

Versão do kernel

As sugestões utilizaram dados até dezembro 2013. Todos os projetos continuam evoluindo e periodicamente a avaliação precisa ser refeita. A cada nova versão Estável de um dos projetos, os desempenhos relativos podem alterar. Linux parece evoluir mais rapidamente que FreeBSD que evolui mais rapidamente que Illumos. Mas os desempenhos relativas aparecem em ordem quase inversa. E não pode ser o único fator de escolha.

Não é que outros não vão funcionar. São sugestões de kernel para extrair o máximo de desempenho do hardware disponível e da versão do PostgreSQL. Versões recentes de kernel Linux, no repositório backports do Debian, são cada vez mais escaláveis. FreeBSD ainda não está otimizado para hardwares maiores; é um trabalho em progresso. Em hardwares para os quais já otimizado, ele é superior ao Linux. Illumos kernel, usado em algumas distros, é o mais escalável linearmente em máquinas grandes. Outros fatores precisam ser avaliados também na hora de escolher um kernel, como a experiência da equipe. Todos 64 bits, claro.

Linux

Várias distribuições utilizam o kernel Linux, como Debian GNU/Linux, Red Hat Enterprise Linux, CentOS, Suse, Slackware, Oracle Unbreakable Linux, Ubuntu, etc.

*BSD

O kernel BSD originou alguns forks e distribuições, focadas em diferentes usos, como FreeBSD, OpenBSD, NetBSD, DragonFly, etc.

http://en.wikipedia.org/wiki/Berkeley_Software_Distribution#Significant_BSD_descendants

Illumos

Illumos é o sucessor do extinto kernel [1]OpenSolaris.

http://en.wikipedia.org/wiki/Illumos

Várias distribuições utilizam o kernel Illumos, focadas em diferentes usos e métodos de empacotamento de programas. Algumas usam o original [2]IPS, outras o [3]pkgsrc, e outras o [4]APT com pacotes do Illumos user-land ou GNU do Debian.

As de uso geral ou ao menos um espectro razoável de usos mais ativamente desenvolvidas parecem ser OpenIndiana, SmartOS, OmniOS, OSDyson, DilOS, OpenSXCE. Outras distribuições são de uso bastante específico, como Napp-it, Nexenta, ZFSGuru, todas focadas em data storage.

OpenIndiana e OpenSXCE parecem ser os únicos com interface gráfica disponível.

http://wiki.illumos.org/display/illumos/Distributions

Se CPU count > 8

então Big ram. Linux kernel >= 3.2.x, FreeBSD >= 10.x.

Se CPU count < 32

então NUMA aware, Big ram. FreeBSD >= 10.x.

Se CPU count > 32

então NUMA aware, Big ram.  Illumos kernel: OpenIndiana, OpenSolaris, SmartOS, OmniOS. Linux kernel >= 3.12,

gráfico comparativo

Obviamente o gráfico é uma simplificação elementar.

Os gráficos reais encontrados pela internet são curvas complexas, que variam de acordo com o exato ambiente de teste, e exatas configurações e versões de kernel e filesystem. O gráfico abaixo é apenas uma visão geral simplificada do cenário em determinada data. Tudo pode mudar no ano seguinte.

Lembre de atribuir um peso ao fator desempenho bruto, pois não deve ser o único critério de escolha. Por exemplo, se ninguém na equipe domina uma distribuição baseada em kernel Illumos, como o OpenIndiana, e ninguém está disposto a estudar para dominar a distribuição, ou ao menos conhecer e fazer um contrato de suporte externo para tarefas mais complexas, seria um risco muito alto implantar um servidor de produção sobre tal plataforma. Mesmo que a diferença de desempenho fosse substancial numa máquina grande. Cada empresa deve fazer sua própria análise.

Illumos freebsd linux scalability cores.png


2014:

https://kib.kiev.ua/kib/pgsql_perf.pdf

Versão do PostgreSQL

Se CPU count > 8

então PostgreSQL >= 9.2. Use o do Debian Backports oficial ou faça seu backport a partir do repositório Debian Testing se já não estiver no Debian Estável

PgBouncer connection pooling

Um servidor postgresql "deve" ser configurado para não aceitar mais conexões (queries) simultâneas que o dobro do número de núcleos para reduzir a latência. Esse número não é absoluto e precisa ser experimentado caso a caso, pois depende da velocidade relativa entre cpu e rede e dos clientes enviando queries. Fique atento ao chaveamento de contexto indicado por %sys no top.

Por exemplo, se utilizar rede 10 Gb/S, com aplicação capaz de gerar queries MUITO rapidamente, alimentando continuamente o servidor sem pausas de milissegundos, pode ser que seja necessário não ultrapassar o número de núcleos de cpu. Se a rede for muito lenta, e a aplicação submeta queries muito espaçadas no tempo em relação ao tempo de processamento delas no servidor, pode ser viável aceitar várias vezes o número de núcleos, limitado pelo %sys.

Se a aplicação não é capaz de lidar com um pedido de conexão ao servidor de banco de dados recusado, então é necessário utilizar um programa para fazer conection pooling. Ele aceita novas conexões e as deixa aguardando em fila(s) até que o postgresql possa atender. E também ganha algum tempo já mantendo conexões abertas ao servidor de banco de dados.

Dois interessantes programas são o PgPool e o PgBouncer. Optamos por utilizar o PgBouncer pela simplicidade e facilidade de monitoração, já que o pgpool é focado em replicação e balanceamento de carga, e porque pgbouncer tem o modo transacional de pooling.

Mudança no default de timezone a partir do postgresql 9.3.x . Até a versão 9.2.x o default era GMT. Depois passou a ser localtime. Muita atenção nisso. Configure se necessário. E atenção na conexão de clientes em cada pool, veja o parâmetro de definição de timezone e tamanho de cada pool de cada banco de dados.

pgbench = host=localhost dbname=pgbench port=5432 user=meu_user_name pool_size=32  timezone=GMT


transaction mode.

Esse modo é extremamente útil para liberar conexões rapidamente e reciclá-las. Mas exige que a aplicação encapsule todos acessos em transações, para manter o controle e evitar confusões com o framework presumindo que ainda está conectado ao mesmo processo no servidor de banco de dados.

Mas também força o pgbouncer a ler todas as queries, aumentando o uso de cpu.

Em junho de 2014, você pode estimar em máquinas topo de linha um máximo de 15000 TPS ou 15 MB/s , o que acontecer primeiro.

Garanta que haja um núcleo "exclusivo" para o pgbouncer executar, e ou que tenha alta prioridade (renice), ou que esteja em máquina separada.

Também "pode" ajudar, dependendo do perfil de queries e datasets, aumentar o tamanho dos buffers internos.

;AFM 20140623 reduce cpu usage , from 2048
pkt_buf = 1048576
;; In-kernel buffer size (linux default: 4096)
;AFM 20140623 reduce cpu usage , from 4096
tcp_socket_buffer = 1048576

Mas isso trará apenas ganhos marginais. E em alguns perfis de uso.

Uma alternativa é prover diferentes instâncias ou máquinas com pgbouncer para cada banco de dados, ou para um grupo de frontends web que gere até tal tráfego, cuidadosamente dimensionando os pools.

Outra alternativa é usar algum balanceador ou DNS para ter vários pgbouncers na frente do(s) servidor(es) de banco(s) de dado(s).

Num exemplo hipotético, a cada 5 frontends web, um pgbouncer configurado para pool de 16 conexões, conectado a um servidor capaz de 64 conexões. Você poderá ter até 4 pgbouncers, conectados a 20 frontends web nesse exemplo.


http://lists.pgfoundry.org/pipermail/pgbouncer-general/2012-December/001091.html

http://lists.pgfoundry.org/pipermail/pgbouncer-general/2014-June/001255.html

http://lists.pgfoundry.org/pipermail/pgbouncer-general/2013-June/001147.html

http://www.df7cb.de/blog/2012/pgbouncer_running_on_the_same_hardware.html

http://blog.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/month=20130601

https://news.ycombinator.com/item?id=7263696

http://www.sraoss.jp/pipermail/pgpool-general/2013-May/001803.html


Backup e Restore

Backups e restores de grandes bases podem demorar várias horas ou até dias. Estude como reduzir o downtime ao máximo.

Você PRECISA ter os dados armazenados em mais de um lugar, pois hardware estraga. Até prédios sofrem destruição, inundações e incêndios.

Recomenda-se ENFATICAMENTE fazer backups para fitas LTO que sejam armazenadas em outro prédio com segurança apropriada.

http://en.wikipedia.org/wiki/Linear_Tape-Open

Snapshots para Disaster Recovery

ZFS snapshots. Mas cuidado com dados em RAM, nos shared buffers. Fazer um checkpoint antes.

Snapshots não podem ser a única forma de restaurar dados, pois ficam no mesmo sistema de armazenamento. E se o data storage server queimar? Snapshots são rápidos e convenientes, mas você PRECISA ter outras formas de recuperar os dados também.

http://www.postgresql.org/docs/current/static/backup-file.html

Procurar implementar em scripts o que a Delphix faz. A partir da versão 4.0 já suporta PostgreSQL.

http://www.delphix.com

http://www.oraclerealworld.com/delphix/

Precisa tomar cuidado para fazer um tipo de flush do banco e depois do sistema de arquivos.

http://www.postgresql.org/docs/current/static/sql-checkpoint.html

http://postgresql.1045698.n5.nabble.com/clearing-the-buffer-cache-td1915083.html

http://www.postgresql.org/docs/current/static/app-pgbasebackup.html

http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-BASE-BACKUP

http://alestic.com/2009/09/ec2-consistent-snapshot

http://wiki.postgresql.org/images/8/86/PostgreSQL_on_ZFS.pdf

Esse script precisa ser avaliado com atenção. Especialmente os efeitos colaterais dos comandos.

http://letsgetdugg.com/2010/06/23/postgresql-zfs-snapshot-backups/

http://letsgetdugg.com/2010/03/30/automated-zfs-snapshots/

Backup em sql

pg_dump e pg_dumpall

Plain text para flexibilidade e compatibilidade máxima com psql e eventual editor de texto.

Custom format no pg_dump para rápida carga multiprocessos com pg_restore.


http://www.depesz.com/2013/09/11/how-to-make-backups-of-postgresql/

Backup archiving contínuo

Barman

Faça backport do pacote do Debian 8.x Jessie pois não há pacote pronto para o 7.x Wheezy.

https://packages.debian.org/barman

OmniPITR

https://github.com/omniti-labs/omnipitr

Warm standby

Hot standby

Replicação e balanceamento de carga

É uma solução de disponibilidade, mas não pode ser a única aplicada.

O banco do expresso v3 tem perfil de escritas alto, 40% escritas.

Isso é EXTRAORDINARIAMENTE alto para fazer uma boa replicação síncrona e deixa uma replicação assíncrona MUITO difícil.

Tunings para alto desempenho

Rede

Memory

Queues

timeouts

reusos de sockets

Kernel

irqbalance

haveged

Shared memory

VFS

Buffer memory

Buffer memory pressure

I/O queues

I/O scheduler

Flush renice

Block device drivers

I/O queues

Filesystem

O tamanho de blocos padrão do PostgreSQL é 8 KB para versões 9.x. Se for necessário alterar, é preciso recompilar.

ZFS

Criação de pool e tunings filesystem

XFS

Formatação e tunings filesystem

O tamanho de blocos padrão do sistema de arquivos no Linux é 4 KB.

PostgreSQL

http://apdex.org/specs.html


índices

estatísticas

http://bonesmoses.org/2014/08/05/on-postgresql-logging-verbosity/

movimentação e limpeza dados antigos

autovacuum

Jamais desligue o autovacuum. O engine postgresql procura identificar momentos de menor atividade e executa autovacuum em tabelas que atingiram as condições necessárias. Você pode até tornar o autovacuum menos agressivo ou mais agressivo ajustando parâmetros cuidadosamente. Os defaults já são bem interessantes. Consulte documentação do postgresql.

http://www.postgresql.org/docs/9.1/static/runtime-config-autovacuum.html

vacuum analyze

Todos os dias, ao menos uma vez por dia, agende em cron job do usuário postgres para executar um vacuumdb analyze. Permitirá marcar para reaproveitamento os espaços nos arquivos do banco de dados e reanalisará as estatísticas, refazendo os planos de execução de acordo com os padrões de uso. Também previnirá a exaustão dos transaction ID disponíveis. Apesar de haverem 32 bits de ID, em bases de alta carga isso pode acontecer.

http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html

O vacuum analyze não bloqueia as tabelas enquanto executa, mas impacta no desempenho geral. Portanto procure agendar para horário de menor atividade. O vacuum analyze não recupera espaço em disco, não "desfragmenta" o banco de dados e vai deixando aumentar a quantidade de acessos aleatórios físicos ao disco. Portanto você precisa ter um armazenamento, data storage server, de alto desempenho.

vacuum full analyze

Ao menos uma vez por semana agende para o horário de menor atividade um vacuumdb full analyze. Isso permitirá compactar os arquivos do banco de dados, e apagando os que não sejam mais necessários, reconstruirá índices e reanalisará as estatísticas, refazendo so planos de execução de acordo com os padrões de uso. É algo conceitualmente similar a desfragmentar o banco de dados, melhorando o desempenho de acesso. O vacuum full bloqueia a tabela que esteja otimizando e exige ao máximo o sistema de armazenamento no limite que um núcleo possa executar. Avalie o comportamento da aplicação durante esse tempo de bloqueio.

http://www.postgresql.org/docs/9.3/static/app-vacuumdb.html

pg_repack , pg_reorg

Uma extensão NÃO OFICIAL experimental que executa uma função EQUIVALENTE ao vacuum full mas que não bloqueia tabela enquanto otimiza. Copia a tabela para uma outra tabela temporária, reconstrói índices e depois substitui a tabela.

Ainda não homologada e sujeita a bugs de perda de dados. 
Use por sua conta e risco.

pg_reorg ficou em modo manutenção e não está mais evoluindo. pg_repack substituiu inicialmente como fork e agora com novas funcionalidades.

https://github.com/reorg/pg_repack/

Ferramentas de análise e monitoração

Tabelas internas

http://www.postgresql.org/docs/current/static/monitoring-stats.html

http://www.postgresql.org/docs/current/static/pgstatstatements.html

Queries de monitoração sobre as tabelas internas. Estão na palestra sobre tuning extremo em

http://www.techforce.com.br/news/linux_blog/postgresql_extreme_tuning_40_cores_512_gb_ram_debian


monitora_tps.sh

#!/bin/bash
tps_anterior=`psql --tuples-only -U postgres -d pgbouncer -p 5433 -c 'SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;' `
while true; do
   sleep 1;
   tps_atual=`psql --tuples-only -U postgres -d pgbouncer -p 5433 -c 'SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database;' `
   echo $(( tps_atual - tps_anterior ))  `date`;
   tps_anterior="$tps_atual";
done;

./monitora_tps.sh | tee /tmp/tps_sgbdv3.log
sort -n -k1 /tmp/tps_sgbdv3.log
watch -n 1 -c "psql -U postgres -d pgbouncer -p 5433 -h localhost  -c 'SHOW POOLS' ; ps gawx |grep postgre | grep expressov3user |wc -l "


http://michael.otacoo.com/postgresql-2/postgres-feature-highlight-pg_buffercache/

http://stackoverflow.com/questions/934360/postgresql-cache-memory-performance-how-to-warm-up-the-cache

http://www.postgresql.org/docs/9.2/static/pgbuffercache.html

http://stackoverflow.com/questions/15034622/check-statistics-targets-in-postgresql

http://stackoverflow.com/questions/6903938/how-do-i-know-if-the-statistics-of-a-postgres-table-are-up-to-date

http://onewebsql.com/blog/monitoring-postgresql

pg_activity

sudo pg_activity -U postgres -p 5433

pg_top

Pgfouine

PgBadger

htop

atop

top

sar

zabbix

postgresql.conf

Mudança no default de timezone a partir 9.3.x . Até a versão 9.2.x o default era GMT. Depois passou a ser localtime. Muita atenção nisso. Configure se necessário. E atenção na conexão de clientes, como o pgbouncer e sua aplicação.

 # AFM 20140619 from localtime to GMT
 timezone = 'GMT'
 log_timezone = 'localtime'

Arquivo abaixo comentado do que é preciso adaptar para cada máquina específica:

 PostgreSQLconf
Ferramentas pessoais
Espaços nominais

Variantes
Ações
Navegação
Ferramentas