Postgres Tuningo elefante mais rapido que um leopardo

Fabio Telles Rodriguez

Timbira - A empresa brasileira de PostgreSQL

03 de maio de 2013

Agenda

Sobre o que estamos falando?

Ajustes iniciais

Ajustes no SQL

Consideracoes finais

Perguntas

Sobre esta apresentacao

I esta apresentacao esta disponıvel em:http://www.timbira.com.br/material

I esta apresentacao esta sob licenca Creative CommonsAtribuicao 3.0 Brasil :http://creativecommons.org/licenses/by/3.0/br

Sobre o que estamos falando?

Figura: Escolha difıcil

Sobre o que estamos falando?

Instalacoes eletricas

Sobre o que estamos falando?

Instalacoes eletricas

A maior preocupacao numa instalacao eletrica e sempre o calor;

I A corrente eletrica gera calor;

I Varios condutores em instalados juntos geram mais calor;

I As condicoes de ventilacao dificultam ou facilitam adissipacao do calor;

I As condicoes atmosfericas ou a presenca de outras fontes decalor devem ser levadas em consideracao;

I Materiais inflamaveis devem ser evitados.

Bancos de dados

A maior preocucao num banco de dados sao os Discos;

I Antes de confirmar uma transacao (COMMIT) voce devepersistir a informacao em disco;

I Discos sao milhares de vezes mais lentos que a CPU e amemoria;

I A lei de moore nao se aplica aos discos;

I Gravacoes e leituras em discos sao operacoes seriais e nao saoparalelizadas;

Discos

Figura: Discos num Storage

Uso de discos

I Um UPDATE pode obrigar um registro a migrar de bloco seele nao couber mais la;

I Ao atualizar um registro numa tabela, seus ındices tem de seratualizados tambem;

I Consultas pesadas que nao cabem na memoria utilizam oTABLESPACE temporario;

I Informacoes de rollback sao gravados para reverter umatrasacao;

I Varias pessoas podem querer alterar os mesmos dados aomesmo tempo;

Log de transacoes (WAL)

I Todo SGDB tenta guardar a maior parte possıvel dos dadosem memoria;

I Toda vez que uma transacao e confirmada (COMMIT) umregistro no WAL e gravado;

I O WAL e o ”Write Ahead Log”grava os registrossequencialmente;

I de tempos em tempos os dados em memoria (dirty buffers)sao gravados definitivamente nos datafiles (checkpoint);

I Se houver uma falha no SGDB o WAL e utilizado parareproduzir as tranzacoes em memoria que ainda nao foramconsolidadas em disco;

I Informacoes de rollback sao gravados para reverter trasacoesainda em memoria em caso de falha do SGDB;

Onde esta o problema afinal?

I 50% estao em SQL mal escrito;

I 20% estao em modelagem de dados mal feita;

I 10% estao em ajustes ruins do SGDB;

I 5% estao em ajustes ruins do SO;

I 5% estao em hardware mal dimensionado;

Agenda

Sobre o que estamos falando?

Ajustes iniciais

Ajustes no SQL

Consideracoes finais

Perguntas

Hardware

Figura: Discos num Storage

Hardware

I Utilize um servidor dedicado. (VMs go home!);

I Utilize DISCOS dedicados (Tirem a mao do meu storage!);

I Prioridade de gastos: discos, controladora de discos, memoria,processador;

I SSD SLC > SSD MLC > Fiber channel > SAS > SATA;

I Infiniband > Fiber channel > iSCSI;

I RAID 10 > RAID 1 > RAID 6 > RAID 5 > JBOD;

I Prefira controladoras com bastante cache, baterias externas esuportes a muitos discos;

I Prefira processadores com o melhor e maior cache possıvel emuitos cores;

Sistema Operacional

I Prefira sistemas UNIX: Linux, FreeBSD, OpenBSD, Solaris,etc;

I O melhor SO e aquele que sua equipe tem competencia paraoperar eficientemente;

I Sempre instale a versao em 64 bits do SO;

I Prioridade de gastos: discos, controladora de discos, memoria,processador;

I Prefira RAID por hardware e nao por software;

I Nao use LVM;

I Nao instale softwares e servicos desnecessarios: interfacesgraficas, Samba, NFS, Apache, etc;

Particionamento de Discos (Linux)

I /boot (EXT4);

I raiz (EXT4);

I dados (RAID 10 ou RAID 1 + XFS ou EXT4 + noatime);

I pg xlog (RAID 10 ou RAID 1 + EXT2 + noatime ou EXT3 +noatime + writeback);

I pg log (EXT2 + noatime);

I tablespaces com ındices, tablespaces temporarios (RAID 0 +EXT2 + noatime);

I tablespaces com dados historicos (RAID 5 + XFS ou EXT4 +noatime);

I backup e archives;

Ajustes no SO (Linux)

/etc/sysctl.conf

I kernel.shmmax (50% da RAM disponıvel);

I Semaforos (para suportar um numero alto de conexoes);

I file-max;

I overcommit;

/etc/security/limits.conf

I nproc;

I nofile;

/etc/fstab

I noatime para os dados;

I noatime + writeback para o pg xlog;

Ajustes no PostgreSQL

I max connections: O menor numero viavel;

I shared buffers: < 8GB ou 25% da RAM disponıvel;

I maintence work mem: 75% do tamanho da maior tabela;

I checkpoint segments: entre 16 e 64;

I checkpoint timeout: entre 10min e 30min;

Agenda

Sobre o que estamos falando?

Ajustes iniciais

Ajustes no SQL

Consideracoes finais

Perguntas

Acerte a sua modelagem

I Use o tipo de dados certo para a tarefa certa;

I Use chaves naturais;

I Nao use campos flex;

I Para dados nao estruturados, voce tem hstore, vetores e tiposcompostos;

I Use ındices e gatilhos com sabedoria (teste e monitore o seuuso);

I Pilhas e filas nao devem ficar no seu SGDB;

Escrevendo SQL

I Jamais utilize uma funcao em PL para algo que um SQL puroconsegue fazer;

I COMMIT a cada X alteracoes. X > 100 e < 100K;

I Se uma consulta retorna mais de 100 registros, reveja a regrade negocio;

I INSERT < INSERT multiplo < PREPARE e EXECUTE <COPY < INSERT ... SELECT;

I Aprenda a usar subconsultas e window functions e CommonTable Expression;

I Relatorios pesados devem utilizar visoes materializadas.

Agenda

Sobre o que estamos falando?

Ajustes iniciais

Ajustes no SQL

Consideracoes finais

Perguntas

Testes

I Teste as funcionalidades;

I Teste com volumes de dados o mais realistas possıvel;

I Teste com carga de concorrencia o mais realista possıvel;

I Aprenda a utilizar bem o EXPLAIN.

Monitoramento

I Monitore o SO, o PostgreSQL, a aplicacao;

I Gere logs que mostrem a operacao e a duracao de cada acao;

I Gere logs em formatos que possam ser manipulados porferramentas automatizadas;

I Aprenda a configurar o log do PostgreSQL e o PGBadger;

I Faca coletas periodicas e armazene tudo em um local central;

I Crie baselines e compare sempre com elas;

Para os DBAs...

I Durma bem antes de um novo deploy. Tire uns dias de folga;

I Nao deixe de tomar cerveja com os amigos...

I Pratique exercıcios fısicos regularmente!!!

Perguntas

?Fabio Telles Rodrigueztelles@timbira.com.br

http://www.timbira.com.br