Performance Tuning para banco de dados PostgreSQL
Fernando Ike de Oliveira
B2BR - Grupo TBA
Agosto de 2008 / Congresso Internacional Sociedade e Governo Eletrônico 2008
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Para não cometer gafes...
Como fala/escreve mesmo? Escreve-se PostgreSQL ou postgres Pronúncia-se postígres-és-qüi-uel ou postígres Não se diz/escreve: Postgree, PostrGREE, Postgrí
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Qual a velocidade máxima de um carro supersônico?
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Características de um carro supersônico. . .
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Qual a velocidade máxima de um dragster?
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Qual a velocidade máxima de um fusca?
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Qual a velocidade máxima de um carro de rolemã?
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
O banco está lento!
Problemas comuns. . .
Proporcionalmente, os problemas de lentidão estão em: 60% dos problemas são relacionados ao mau uso de expressões SQL. 20% dos problemas são relacionados a má modelagem do banco de dados. 10% dos problemas são relacionados a má configuração do SGDB. 10% dos problemas são relacionados a má configuração do Sistema Operacional.
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
O banco está lento!
Equívocos de arquiteturas. . .
Concentração de regras de negócio na aplicação para processos em lote. Integridade referencial na aplicação. Mal dimensionamento de I/O (CPU, Plataforma, Disco). Ambientes virtualizados (Vmware, XEN, etc..) em AMD64/EMT64. Uso de configurações padrões do SO e/ou do PostgreSQL.
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Melhores escolhas
Melhor equipamento. . .
Servidores dedicados para o PostgreSQL Storage com Fiber Channel, iSCSI: Grupos de RAID dedicados SCSI > SAS > SATA > IDE RAID 5 ou 10: por Hardware Mais memória! (Até 4GB em 32 bits) Processadores de 64 bits: Performance até 3 vezes do que os 32 bits (AMD64 e EMT64 - Intel)
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Melhores escolhas
Melhor Sistema Operacional. . .
Sistemas Operacionais *nix: Linux (Debian, Gentoo), FreeBSD, Solaris, etc Em Linux: use Sistemas de arquivos XFS (noatime), Ext3 (writeback, noatime), EXT2 Instale a última versão do PostgreSQL (atualmente 8.3) e à partir do código-fonte Não usar serviços concorrentes (Apache, MySQL, SAMBA...) em discos, semáforos e shared memory Usar, se possível, um kernel (linux) mais recente (e estável)
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Ajustes de Performance
Parâmetros do Sistema Operacional. . .
echo “2” > /proc/sys/vm/overcommit_memory echo “25%” > /proc/sys/kernel/shmmax echo “25%/64” > /proc/sys/kernel/shmall echo “deadline” > /sys/block/sda/queue/scheduler echo “250 32000 100 128” > /proc/sys/kernel/sem echo “65536” > /proc/sys/fs/file-max ethtool -s eth0 speed 1000 duplex full autoneg off echo “16777216” > /proc/sys/net/core/rmem_default echo “16777216” > /proc/sys/net/core/wmem_default echo “16777216” > /proc/sys/net/core/wmem_max echo “16777216” > /proc/sys/net/core/rmem_max
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Ajustes de Performance
Limites do Sistema Operacional. . .
no shell pmanson:˜# su - postgres postgres@pmanson:˜$ ulimit 65535 /etc/security/limits.conf postgres postgres postgres postgres soft soft hard hard nofile nproc nofile nproc 4096 4096 63536 63536
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Como organizar discos
Melhor I/O. . .
Discos ou partições distintos para: Logs de transações (WAL) Índices: Ext2 Tabelas (particionar tabelas grandes) Tablespace temporário (em ambiente BI)* Archives SO + PostgreSQL Log de Sistema
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
postgresql.conf
Memória. . .
postgresql.conf max_connections: O menor número possível shared_buffers: 33% do total -> Para operações em execução temp_buffers: Acesso às tabelas temporárias work_mem: Para agregação, ordenação, consultas complexas maintenance_work_mem: 75% da maior tabela ou índice max_fsm_pages: Máximo de páginas necessárias p/ mapear espaço livre. Importante para operações de UPDATE/DELETE.
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
postgresql.conf
Discos e WAL. . .
postgresql.conf wal_sync_method: open_sync, fdatasync, open_datasync wal_buffers: tamanho do cache para gravação do WAL commit_delay: Permite efetivar várias transações na mesma chamada de fsync checkpoint_segments: tamanho do cache em disco para operações de escrita checkpoint_timeout: intervalo entre os checkpoints wal_buffers: 8192kB -> 16GB bgwriter: ????? join_collapse_limit = > 8
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
O terror dos DBA’s. . .
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
O terror dos DBA’s. . .
O terror dos DBA’s(Object-Relation Mapping). . . Use ORM que permitam expressões SQL. Expressões em Object Query Language ainda não usam o melhor plano para consultas. O mundo ainda não é todo objeto. Servidores de Aplicação tem pool de conexão, use-o! Você não vê mas as vezes o seu ORM faz. . . SELECT * FROM cadastro; SELECT * FROM cadastro, venda, produto;
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Aplicações bem construídas. . .
. . . Mas meu prazo não permite!
Boas práticas para desenvolvimento de aplicações Modele bem o banco de dados prevendo grande crescimento da base e acesso concorrente. Operações pesadas, execute com Funções/Store Procedures direto no PostgreSQL. De preferência para o padrão SQL/ISO(2008). Considere que um dia a aplicação tenha que usar múltiplas instâncias de banco de dados. Evite uso de LIKE, ILIKE. Prefira Full Text Search(tsearch2)
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
. . . e seu DBA feliz!
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Ferramentas auxiliares
Tuning SQL. . .
EXPLAIN ANALYZE test_base=# EXPLAIN ANALYZE SELECT foo FROM bar; Ferramentas gráficas Pgfouine Pgadmin3 PhpPgAdmin Cedrus
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Pgfouine - http://pgfouine.projects.postgresql.org
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Pgadmin3 - http://www.pgadmin.org/
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Pgadmin3 - http://www.pgadmin.org/
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
PhpPgAdmin - http://phppgadmin.sourceforge.net/
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
PhpPgAdmin - http://phppgadmin.sourceforge.net/
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Cedrus - http://sourceforge.net/projects/cedrus
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Cedrus - http://sourceforge.net/projects/cedrus
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Ferramentas auxiliares
Manutenção. . .
Autovacuum X Vacuum Depende do uso (Aplicações Web, OLTI, BI) Vacuum vacuum_cost_delay: tempo de atraso para vacuum executar automaticamente nas tabelas grandes Autovacuum (Habilitado por padrão à partir do 8.3) autovacuum_naptime: tempo de espera para execução do autovacuum.
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Ferramentas auxiliares
Ferramentas de Stress. . .
Pgbench: no diretório do contrib do PostgresSQL, padrão de transações do tipo TPC-B. DBT-2: Ferramenta da OSDL, padrão de transações do tipo TPC-C. BenchmarkSQL: Ferramenta Java para benchmark em SQL para vários banco de dados (JDBC), padrão de transações do tipo TPC-C. Jmeter: Ferramenta Java genérica para testes de stress, usado para aplicações (Web, ...) e também pode ser direto para um banco de dados.
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Quando o tuning não resolve. . . Quando o tuning não resolve. . .
Escalabilidade vertical:
Mais e melhores discos; Mais memória; Melhor processador (quad core, 64bits) Escalabilidade horizontal: Pgpool I (distribuição de carga de leitura e pool de conexões) PgPool II (PgPool I + paralelização de grandes consultas) Slony I (Replicação Multi-Master Assíncrona) Bucardo (Replicação Multí-Master Assíncrona) Warm Stand By/Log Shipping Pgbouncer + PL/Proxy + Slony
Fernando Ike de Oliveira PostgreSQL: O elefante mais rápido que o leopardo
Links Documentação Oficial:
http://www.postgresql.org/docs/
Power PostgreSQL:
http://www.powerpostgresql.com/
PostgreSQL Brasil:
http://www.postgresql.org.br/
Teste de stress com soluções livres e proprietárias:
http://www.vivaolinux.com.br/artigos/verArtigo.php?codigo=7053&pagina=5
Benchmark Brou-Ha-Ha:
http://blogs.ittoolbox.com/database/soup/archives/benchmark-brouhaha-17939
BenchmarkSQL, DBT-2 - SourceForge:
http://www.sf.net
Jmeter:
http://jakarta.apache.org/jmeter/
Tsung:
http://tsung.erlang-projects.org/ Fernando Ike de Oliveira PostgreSQL: O elefante mais rápido que o leopardo
Dúvidas
Links e Listas de discussão:
http://archives.postgresql.org/pgsql-performance/ https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral http://www.postgresql.org/docs/current/static/performance-tips.html http://www.revsys.com/writings/postgresql-performance.html http://www.powerpostgresql.com/Docs http://wiki.postgresql.org/wiki/Special:Search?search=performance&go=Go
IRC irc.freenodes.net: #postgresql #postgresql-br
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo
Conclusão
Contatos: fernando.ike@b2br.com.br fernando.ike@gmail.com http://www.midstorm.org/~fike/weblog PGCon Brasil 2008 http://pgcon.postgresql.org.br
Fernando Ike de Oliveira
PostgreSQL: O elefante mais rápido que o leopardo