Postgres Tuning o elefante mais r´ apido que um leopardo F´ abio Telles Rodriguez Timbira - A empresa brasileira de PostgreSQL 03 de maio de 2013

Agenda Sobre o que estamos falando? Ajustes iniciais Ajustes no SQL Considera¸ c˜ oes finais Perguntas

Sobre esta apresenta¸ c˜ ao esta apresenta¸ c˜ ao est´ a dispon´ ıvel em: http://www.timbira.com.br/material esta apresenta¸ c˜ ao est´ a sob licen¸ ca Creative Commons Atribui¸ c˜ ao 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? Instala¸ c˜ oes el´ etricas

Sobre o que estamos falando? Instala¸ c˜ oes el´ etricas A maior preocupa¸ c˜ ao numa instala¸ c˜ ao el´ etrica ´ e sempre o calor; A corrente el´ etrica gera calor; V´ arios condutores em instalados juntos geram mais calor; As condi¸ c˜ oes de ventila¸ c˜ ao dificultam ou facilitam a dissipa¸ c˜ ao do calor; As condi¸ c˜ oes atmosf´ ericas ou a presen¸ ca de outras fontes de calor devem ser levadas em considera¸ c˜ ao; Materiais inflam´ aveis devem ser evitados.

Bancos de dados A maior preocu¸ c˜ ao num banco de dados s˜ ao os Discos; Antes de confirmar uma transa¸ c˜ ao (COMMIT) vocˆ e deve persistir a informa¸ c˜ ao em disco; Discos s˜ ao milhares de vezes mais lentos que a CPU e a mem´ oria; A lei de moore n˜ ao se aplica aos discos; Grava¸ c˜ oes e leituras em discos s˜ ao opera¸ c˜ oes seriais e n˜ ao s˜ ao paralelizadas;

Discos Figura: Discos num Storage

Uso de discos Um UPDATE pode obrigar um registro a migrar de bloco se ele n˜ ao couber mais l´ a; Ao atualizar um registro numa tabela, seus ´ ındices tem de ser atualizados tamb´ em; Consultas pesadas que n˜ ao cabem na mem´ oria utilizam o TABLESPACE tempor´ ario; Informa¸ c˜ oes de rollback s˜ ao gravados para reverter uma trasa¸ c˜ ao; V´ arias pessoas podem querer alterar os mesmos dados ao mesmo tempo;

Log de transa¸ c˜ oes (WAL) Todo SGDB tenta guardar a maior parte poss´ ıvel dos dados em mem´ oria; Toda vez que uma transa¸ c˜ ao ´ e confirmada (COMMIT) um registro no WAL ´ e gravado; O WAL ´ e o ”Write Ahead Log”grava os registros sequˆ encialmente; de tempos em tempos os dados em mem´ oria (dirty buffers) s˜ ao gravados definitivamente nos datafiles (checkpoint); Se houver uma falha no SGDB o WAL ´ e utilizado para reproduzir as tranza¸ c˜ oes em mem´ oria que ainda n˜ ao foram consolidadas em disco; Informa¸ c˜ oes de rollback s˜ ao gravados para reverter trasa¸ c˜ oes ainda em mem´ oria em caso de falha do SGDB;

Onde est´ a o problema afinal? 50% est˜ ao em SQL mal escrito; 20% est˜ ao em modelagem de dados mal feita; 10% est˜ ao em ajustes ruins do SGDB; 5% est˜ ao em ajustes ruins do SO; 5% est˜ ao em hardware mal dimensionado;

Agenda Sobre o que estamos falando? Ajustes iniciais Ajustes no SQL Considera¸ c˜ oes finais Perguntas

Hardware

Hardware Utilize um servidor dedicado. (VMs go home!); Utilize DISCOS dedicados (Tirem a m˜ ao do meu storage!); Prioridade de gastos: discos, controladora de discos, mem´ oria, processador; SSD SLC > SSD MLC > Fiber channel > SAS > SATA; Infiniband > Fiber channel > iSCSI; RAID 10 > RAID 1 > RAID 6 > RAID 5 > JBOD; Prefira controladoras com bastante cache, baterias externas e suportes a muitos discos; Prefira processadores com o melhor e maior cache poss´ ıvel e muitos cores;

Sistema Operacional Prefira sistemas UNIX: Linux, FreeBSD, OpenBSD, Solaris, etc; O melhor SO ´ e aquele que sua equipe tem competˆ encia para operar eficientemente; Sempre instale a vers˜ ao em 64 bits do SO; Prioridade de gastos: discos, controladora de discos, mem´ oria, processador; Prefira RAID por hardware e n˜ ao por software; N˜ ao use LVM; N˜ ao instale softwares e servi¸ cos desnecess´ arios: interfaces gr´ aficas, Samba, NFS, Apache, etc;

Particionamento de Discos (Linux) /boot (EXT4); raiz (EXT4); dados (RAID 10 ou RAID 1 + XFS ou EXT4 + noatime); pg xlog (RAID 10 ou RAID 1 + EXT2 + noatime ou EXT3 + noatime + writeback); pg log (EXT2 + noatime); tablespaces com ´ ındices, tablespaces tempor´ arios (RAID 0 + EXT2 + noatime); tablespaces com dados hist´ oricos (RAID 5 + XFS ou EXT4 + noatime); backup e archives;

Ajustes no SO (Linux) /etc/sysctl.conf kernel.shmmax (50% da RAM dispon´ ıvel); Sem´ aforos (para suportar um n´ umero alto de conex˜ oes); file-max; overcommit; /etc/security/limits.conf nproc; nofile; /etc/fstab noatime para os dados; noatime + writeback para o pg xlog;

Ajustes no PostgreSQL max connections: O menor n´ umero vi´ avel; shared buffers: < 8GB ou 25% da RAM dispon´ ıvel; maintence work mem: 75% do tamanho da maior tabela; checkpoint segments: entre 16 e 64; checkpoint timeout: entre 10min e 30min;

Agenda Sobre o que estamos falando? Ajustes iniciais Ajustes no SQL Considera¸ c˜ oes finais Perguntas

Acerte a sua modelagem Use o tipo de dados certo para a tarefa certa; Use chaves naturais; N˜ ao use campos flex; Para dados n˜ ao estruturados, vocˆ e tem hstore, vetores e tipos compostos; Use ´ ındices e gatilhos com sabedoria (teste e monitore o seu uso); Pilhas e filas n˜ ao devem ficar no seu SGDB;

Escrevendo SQL Jamais utilize uma fun¸ c˜ ao em PL para algo que um SQL puro consegue fazer; COMMIT a cada X altera¸ c˜ oes. X > 100 e < 100K; Se uma consulta retorna mais de 100 registros, reveja a regra de neg´ ocio; INSERT < INSERT multiplo < PREPARE e EXECUTE < COPY < INSERT ... SELECT; Aprenda a usar subconsultas e window functions e Common Table Expression; Relat´ orios pesados devem utilizar vis˜ oes materializadas.

Agenda Sobre o que estamos falando? Ajustes iniciais Ajustes no SQL Considera¸ c˜ oes finais Perguntas

Testes Teste as funcionalidades; Teste com volumes de dados o mais realistas poss´ ıvel; Teste com carga de concorrˆ encia o mais realista poss´ ıvel; Aprenda a utilizar bem o EXPLAIN.

Monitoramento Monitore o SO, o PostgreSQL, a aplica¸ c˜ ao; Gere logs que mostrem a opera¸ c˜ ao e a dura¸ c˜ ao de cada a¸ c˜ ao; Gere logs em formatos que possam ser manipulados por ferramentas automatizadas; Aprenda a configurar o log do PostgreSQL e o PGBadger; Fa¸ ca coletas peri´ odicas e armazene tudo em um local central; Crie baselines e compare sempre com elas;

Para os DBAs... Durma bem antes de um novo deploy. Tire uns dias de folga; N˜ ao deixe de tomar cerveja com os amigos... Pratique exerc´ ıcios f´ ısicos regularmente!!!

Perguntas ? F´ abio Telles Rodriguez [email protected] http://www.timbira.com.br