Hoje recebi um e-mail de alguém me perguntando porque os testes dele com o pgbench variam tanto, mesmo usando os mesmos parâmetros todas as veses. Para quem não sabe o pgbench é um pequeno aplicativo que lhe ajuda a fazer medições de perfomance no banco de dados simulando um teste TPC-B. Ele é muito simples de se utilizar e permite realizar testes com diferentes volumes de dados, conexões simultâneas e transações por conexão. O pgbench NÃO SERVE para fazer tuning em servidores de produção, mas serve para ajudar medir a diferença entre diferentes hardwares, SOs, sistemas de arquivo, etc. No mínimo, com o pgbench você vai começar a aprender muito sobre performance no PostgreSQL, comparando seus resultados em diferentes cenários.

Mas, voltando ao nosso colega, vejamos os resultados que ele obteve:

TPS: 19
TPS: 23
TPS: 18
TPS: 25
TPS: 23
TPS: 29
TPS: 35
TPS: 17

Veja que de 17 para 35, a direrença é de mais de 100%. Realmente, não dá para desprezar uma diferença tão grande. Eu diria que de qualquer forma, apredi no laboratório de física que você tem que realizar a mesma experiência pelo menos umas 5 vezes. Além disso, se você encontra um resultado que está destoando demais dos outros, é melhor repetir o teste mais umas 5 vezes com cuidado redobrado e se apenas um valor destoar, você deve descarta-lo. Assim o seu resultado é uma média entre os testes considerados válidos (sem o resultado descartado). No caso de um instrumento de medida direta (como uma régua ou termômetro) é fácil saber qual é a precisão da sua medida (metade da menor divisão, repetia o professor todas em as aulas no laboratório). No nosso caso, temos que ficar com o dado estatístico das medições considerando a variação dos resultados obtidos.

Veja, se você faz um ajuste na configuração do PostgreSQL e tem um aumento de desempenho de 2%, mas as suas amostras tem uma margem de erro (em relação a média) de 10%, você pode se questionar se realmente pode afirmar alguma coisa. Bom, mas se você tem 100% de variação… alguma coisa está realmente errada.

O primeiro detalhe sobre o nosso colega é que ele usa o PostgreSQL em Windows. Veja… usar o PostgreSQL em Windows funciona… mas fazer tuning em Windows não é tão eficiente. Não sou um profundo conhecedor do Windows, mas que eu saiba você tem muitas opções de ajustes de desempenho neste SO. O PostgreSQL, assim como outros grandes SGDBs, foram construídos para funcionar originalmente em sistemas operacionais Unix. Outro problema é que como o SO é uma caixa preta, você nunca sabe o que está rodando em paralelo exatamente e dificilmente tem controle sobre isso. Isto não quer dizer que não é possível ver o PostgreSQL rodando satisfatóriamente em Windows, significa que o Windows não é a opção número um de quem procura um SO robusto, confiável, seguro e rápido. Mas se você não está querendo levar o seu banco de dados ao limite, não há nada de errado em usar o Windows e ajustar o PostgreSQL para que ele tenha um desempenho melhor nele.

Vejamos alguns outros ítens que você deve se perguntar antes de avaliar os resultados:

1. Qual tipo de disco você usa? Os discos SATA são muito instáveis para testes, pois não conseguem manter um fluxo de dados em alta velocidade por muito tempo. Este não é um problema do disco físico em si e sim do protocolo do SATA. Usar discos SCSI ou SAS lhe trarão resultados mais confiáveis.
2. Você deixou os discos isolados para o banco? Deixou discos isolados para o Wall?? A questão aqui é saber se alguma outra aplicação pode estar competindo com o acesso aos discos.
3. Você tem certeza absoluta que não tem nenhuma outra aplicação rodando em segundo plano. O ideal é deixar uma instalação do SO o mais crua possível. O principio básico da experimentação científica é ter um ambiente controlado para que qualquer pessoa possa reproduzir exatamente os mesmos testes. Isto significa que você tem de documentar exatamente como o SO foi instalado, incluindo parâmetros de configuração.
4. Você tem que realizar testes mais longos para que ocorram checkpoints no meio. Se você parar para estudar o mecanismo de funcionamento interno do PostgreSQL, descobrirá que o momento em que o checkpoint ocorre há uma degradação de performance significativa. Realize testes que garantam a ocorrencia de pelo menos uns 10 checkpoints. Imagine testes que durem no mínimo uma hora cada.
5. Você precisa decidir como vai configurar o vacuum. Se usar o autovacuum, tem que utilizar testes longos para que a ocorrencia do autovacuum nas tabelas seja computado e ocorram um número de vacuuns semelhantes em cada testes.
6. O cache de memória é um ponto crítico. Cada vez que você roda um teste, uma parte dos dados fica em memória, seja o IPC, cache em userspace, kernespace, o cache da controladora de discos ou o cache dos discos em si. Resultado: para ter certeza que a memória está num estado idêntico entre os testes… reinicie o servidor entre cada testes. Após reiniciar o servidor, tenha o cuidado de realizar exatamente as mesmas operações na mesma órdem para minimizar as diferenças de quais dados entram no cache. Podem haver outras formas de controlar o cache do disco, mas eu só conheço esta.
7. Use uma quantidade gererosa de dados, usar menos de 1GB de dados dificilmente reproduz cenários realistas. A não ser que o seu alvo sejam bases realmente pequenas, é melhor fazer testes com diferentes tamanhos de bases. 1GB, 10GB e 100GB são um bom começo.
8. Se você pretende realizar os testes simulando uma grande quantidade de conexões, faça-o a partir de 1 ou mais clientes remotos e não a partir do servidor. Testes com 100, 200, 500, 1000 conexões simultâneas podem exigir uma estrutura considerável para os clientes. Você vai querer medir o impácto da rede no desempenho e ao mesmo tempo não vai querer que os clientes sejam um peso no desempenho do servidor, a não ser que a sua aplicação e o seu banco de dados fiquem no mesmo servidor. Se for esse o seu caso, prepare-se para um ajuste de desempenho bem mais complexo.

Antes de você achar que o PostgreSQL é um banco “instável”, “lento” ou pouco confiável, Recomendo enfáticamente que você estude mais sobre o funcionamento do PostgreSQL, realize testes em um SO Unixlike como Linux, Solaris e FreeBSD, e use outras ferramentas de testes como o DBT-2 e os testes do TPC como o TPC-C, TPC-E e TPC-H.

Em tempo, escrevi um pequeno texto para quem está começando: “12 dicas para aprender a ajustar a performance em bancos de dados“.

ou “Zen e a arte do tuning em banco de dados”

Nada mais fácil do que reclamar do desempenho do banco de dados. Algumas aplicações que se comportavam bem por anos começam a se tornar lentas sem aviso prévio. Aplicações que tinham um excelente desempenho num banco de dados ficam exorbitantemente lento ao adotar outro fornecedor. É comum alguns desacreditarem completamente um fornecedor por não conseguir o desempenho esperado. Vejo pessoas pedindo ajuda, ficando perdidos no meio do caminho, desistindo e colocando a culpa no banco de dados. O fato é que muita gente acha que sabe como fazer ajustes de desempenho (também conhecido como tuning) em bancos de dados, mas poucos realmente o fazem. Eu aqui como aprendiz de feiticeiro resolvi deixar algumas dicas para quem pretende se enveredar nesta mistura de arte, ciência e bruxaria.

1 - Entre no espírito do tuning

A primeira lição para os os jovens padawans do tuning é ter paciência, humildade e persistência. Você não desvendará tudo de uma hora para outra. Os seus segredos serão revelados aos poucos. Mesmo que você disponha da melhor bibliografia sobre o assunto, você só aprenderá de fato com o tempo. Seja humilde, ouça os desenvolvedores, os administradores de sistemas e redes, escute DBAs mais experientes e menos experientes também. Nunca cometa o erro de achar que você já domina bem o assunto. Sempre há mais para se aprender e o conhecimento pode vir das fontes menos esperadas. Seja persistente, não desista facilmente. Achar o ajuste ideal pode levar meses até mesmo para um DBA experiente. Você deve realmente se concentrar no assunto e se dispor a gastar muito tempo nele. Nunca espere ou prometa resultados rápidos. Alguns ajustes podem levar alguns minutos e terem resultados fantásticos enquanto outros podem levar meses para atingir uma melhoria de 10%. Lembre-se que em alguns casos, 10% pode significar a economia de milhões de dólares.

2 - Seja um aprendiz que realmente aprende

Se você tem a intenção de aprender, esteja disposto a estudar e experimentar. Estudar não significa ler alguns tutoriais e sair realizando baterias de testes. Estudar não significa pegar o Date, Tanenbaun ou qualquer outro clássico e achar que irá absorver tudo e depois estará craque no assunto. Tuning é um daqueles assuntos que exigem conhecimento genuíno que só pode ser obtido através da fusão entre teoria e prática. Portanto, estamos falando um processo de vários ciclos de ação, reflexão e revolução. Outra forma de encarar a mesma coisa seria dizer que não importa se você fez doutorado em banco de dados ou se você trabalha a 20 anos como DBA. Importam as oportunidades que você teve de dissecar casos reais e descobrir causas concretas dos problemas de performance.

3 - Concentre-se na Lei do 80/20 dos bancos de dados

Por mais que os desenvolvedores reclamem do servidor de banco de dados, em 80% dos casos o problema está na aplicação e em 20% no SGDB. Não que a aplicação seja ruim em si, mas ela pode não usar o SGDB de forma eficiente. Existem várias formas de se obter os mesmos dados de forma ineficiente. Pior, uma consulta que funcionava bem num SGDB pode ter uma performance catastrófica em outro SGDB. Infelizmente isto pode ocorrer também com migrações para outra versão do mesmo SGDB. Mesmo assim, a culpa ainda não é do SGDB. A aplicação que tem (infelizmente) de adaptar às especificidades de cada fornecedor e cada versão.
Na verdade, se formos levar a questão de forma mais ponderada, a lei não seria 80/20, seria 99,9/0,01 . Isto ocorre por um motivo simples, ajustes de SQL e modelagem podem resultar em ganhos de performance da ordem de 10, 100 ou até 1000 vezes. Enquanto isso, ajustes no SO e SGDB tem ganhos da ordem de até 10 vezes. Portanto, antes de mais nada aprenda a modelar bem e conheça as minúcias do SQL do seu SGDB. Depois aprenda a analisar com profundidade os planos de execução e reescreva consultas complexas a partir deste tipo de análise.

4 - Concentre-se na Lei do 80/20 da informática

20% dos parâmetros de configuração do seu SGDB são utilizados em 80% do tempo. Aprenda a manejar bem estes parâmetros antes de mais nada. A maioria dos outros parâmetros são utilizados em ocasiões específicas e não tem um impacto tão forte no desempenho. Normalmente o tuning do SGDB começa por estes parâmentros mais importantes, os demais só são utilizados se você conhece especificidades da sua aplicação que sugerem o seu uso, caso contrário são deixados em seus valores padrão.

5 - Conheça o perfil das suas aplicações

A forma como a sua aplicação utiliza o seu SGDB é de suma importante para o DBA. Existem alguns padrões que lhe indicam caminhos já trilhados por outros. Sendo assim identificar o tipo de carga que a aplicação projeta sobre o banco de dados é muito importante. Aqui alguns tipos clássicos:

• OLTP ou On Line Trasactional Processing consiste na maior parte da carga das aplicações corporativas atuais. É caracterizado por um grande volume de pequenas transações conconrretes e alto volume de gravações. As cargas OLTP são as que tem o desempenho de disco mais crítico.
• BI ou Business Inteligence consistem num universo paralelo com diversas tecnologias como Data Mining, OLAP, Data Warehouse, Data Mart, Balanced Scored Card, etc. A questão é que quando falamos em BI, o banco de dados sempre acaba de um jeito ou de outro, tendo que suportar consultas enormes, com grande quantidade de dados e cálculos complexos. As cargas de BI exigem muita velocidade em leitura de disco, memória para ordenações e uso intenso do processador.
• Sites Web Dinâmicos: os sites dinâmicos são aqueles que armazenam o seu conteúdo num banco de dados. Desta forma um único acesso numa página web pode representar uma dúzia de pequenas leituras no banco de dados. As cargas de sites web são caracterizadas por um enorme número de conexões simultâneas realizando pequenas leituras.
• Operações em lote ou bath são cargas de trabalho intensas no banco de dados que podem durar várias horas para se completar e tem um grande impacto na performance. São comuns tanto em aplicações OLTP (cálculo de uma folha de pagamento, fechamento de ano fiscal, etc), como em BI (carga de grandes tabelas vindas de fontes externas). As cargas em lote sempre são alvos de estudos cuidadosos tem o potencial de crescer rapidamente em tempo de execução até inviabilizar os negócios da empresa.

É claro que uma única aplicação pode ter todas estas características ao mesmo tempo. É óbvio também que tem-se o hábito de abrigar várias aplicações no mesmo banco de dados, tornando tudo muito mais divertido…

6 - Monitore hoje, monitore sempre

Estar sempre atento a fatos notáveis no seu banco de dados são atitudes de valor inestimável para quem se preocupa com desempenho. Quem participa da equipe de desenvolvimento tem sempre a oportunidade de ser mais proativo e detectar possíveis problemas antes deles ocorrerem. Mas quem cuida de um banco de dados já em produção acaba sendo mais reativo. É claro que se você conhece bem o seu banco de dados, você sabe em que momentos o desempenho se torna crítico e quando não. Ter isso bem documentado é fundamental. Você pode saber que uma determinada rotina demora normalmente 2 horas para ser executada. Um dia esta rotina começa a demorar 3 ou 4 horas e você quer saber o porquê. Se você tiver documentado o perfil da carga quando tudo funcionava bem, você poderá comparar isso com a nova situação e detectar rapidamente o que realmente mudou. Se você não tiver isto documentado, será muito mais difícil de saber porque o tempo de execução aumentou.

7 - Crie suas próprias métricas e guarde-as com cuidado

É muito fácil coletar zilhões de estatísticas através do SO, SGDB ou até mesmo através dea aplicação. No entanto elas não tem muito valor se você não souber o que elas realmente significam e se não puder inferir uma estatística a um dado evento. De nada adianta saber o volume de leituras do seu disco num dado momento. No entanto saber quantas leituras em disco uma determinada transação provoca, é muito mais útil. Conheça as estatísticas disponíveis. Saiba como extraí-las através das ferramentas disponíveis, saiba como extrair estas informações do forma a torna-las relevantes e saiba o que elas realmente significam. Para isto é preciso criar pequenos programas que gerem cargas padronizadas no seu banco de dados.

Quanto mais próxima a carga gerada se assemelhar da sua aplicação, maior será o valor da sua métrica. Há programas sofisticados que são capazes de gerar cargas idênticas a de um ambiente de produção. Estes sistemas simulam cargas concorrentes e até mesmo criam robôs que utilizam as suas aplicações como se fossem usuários normais. Como é possível imaginar, testes que simulam uma carga de stress muito próxima da realidade tem um custo exorbitante. Há ferramentas intermediárias que geram cargas como o TPC-H ou TPC-C que podem ser muito interessantes para medir a capacidade do servidor para gargas genéricas.

8 - Saia do geral e vá para o específico

A maioria dos SGDBs permite que você realize uma configuração geral do banco de dados para o uso cotidiano e uma configuração específica para situações especiais. É comum você ter uma carga específica que possui um perfil diferenciado das demais. Este tipo de operação pode receber uma configuração específica. Você pode fazer com que todo o banco de dados assuma uma determinada configuração em um determinado momento, pode isolar isso para um usuário específico, para uma sessão específica ou até para uma transação específica. Apesar de ser trabalhoso, operações em lote, grandes relatórios e outras cargas mais pesadas se beneficiam muito deste tipo de atenção do DBA. Outra questão a se analisar sempre é a de evitar ao máximo a concorrência junto a este tipo de operação agendando sempre que possível para um horário de menor demanda.

9 - Tenha rigor científico

Apesar de todo o ar de mistério e magia em torno do tuning, a melhor forma de você saber o que está fazendo é utilizar do rigor científico. Uma das premissas da ciência é a de que você só tem um experimento válido se puder reproduzi-lo. Isto significa que não adianta rodar uma determinada carga num banco de dados em produção para ver como ele se comporta. No ambiente em produção, há outras pessoas agindo sobre ele de forma que você nunca conseguirá reproduzir exatamente as mesmas condições de teste. O tamanho das tabelas, índices, estatísticas tem de ser todos idênticos. Não preciso dizer que o servidor tem que ter o mesmo hardware, o mesmo software e exatamente a mesma configuração, para que o teste seja válido.

Outra coisa importante e que dá trabalho é a entediante tarefa de lidar com apenas uma variável por vez. É comum alguém querer alterar diversas coisas de uma vez só para depois testar o seu impacto sobre a performance. Você nunca vai saber qual das suas alterações ajudou e qual atrapalhou na performance final se fizer assim. Há vezes em que se chega numa configuração muito boa assim. No entanto você nunca tem o conhecimento claro sobre a influência de cada variável. Assim, o êxito pode se transformar em fracasso miserável quando aplicado em outra situação.

10 - Saiba gerar e analisar métricas manualmente

Os bancos de dados proprietários como o Oracle e o DB2 levam uma enorme vantagem (talvez a mais importante delas) sobre os livres no que diz respeito a ferramentas de monitoramento, gerenciamento e autotuning. São recursos poderosos que coletam métricas pré-determinadas, fazem análises e recomendações. Estas ferramentas são excelentes para resolver problemas comuns do dia-a-dia. Em empresas de pequeno e médio porte, estas ferramentas podem diminuir bastante a dependência do DBA para as tarefas mais cotidianas. Mas existem 2 problemas intrínsecos a este tipo de ajuda:

• Você aprende menos, uma vez que se acomoda com os resultados já enlatados pela sua ferramenta. O uso constante dos conselheiros (ou ‘advisors’), assistentes (ou ‘wizards’) e outras tecnologias no estilo nnf (next, next, finish) embrutecem muito o profissional de informática que deixa de entender completamente o que se passa de fato nos bastidores do SGDB. Veja, não é porque existe calculadora que as pessoas deixaram de aprender a fazer as contas na mão.
• A ferramenta pode errar. Nem sempre as recomendações da sua ferramenta serão as melhores. É impossível para quem desenvolve uma ferramenta deste tipo prever todas as situações possíveis. Além disso, a ferramenta nunca tem todas as informações relevantes ao negócio para poder fazer uma avaliação realmente segura. O ser humano ainda não pode ser substituído na sua capacidade de julgamento. Existem muitas ações que podem ser conveniente automatizadas, outras não. É por isso que a figura do “conselheiro” apenas aconselha, não decide. Um DBA experiente saberá quando o conselho é útil e quando não. Mas para isso você não pode ter se acomodado com o uso apenas da sua ferramenta automatizada.

11 - Torne-se um ninja em discos

10 entre 10 DBAs se preocupam com discos mais do que qualquer outro componente do hardware. Não se trata apenas de ter espaço de armazenamento para seus dados. Trata-se de desempenho. Os custos de uma boa solução de storage podem ser estratosféricos, com um custo de mais de R$ 30 mil num único disco. E não se surpreenda se ouvir falar em bancos de dados com milhares de discos. Então este é um universo que o DBA precisa dominar. SATA, SCSI, SAS, Fibre Channel, iSCSI, RAID e outras tecnologias devem ser bem conhecidas. Não basta saber quais tipos de RAID existem, é preciso testa-los, saber quais as diferenças quando implementados via software, por uma controladora local ou em um storage externo. Os sistemas de arquivo também são motivos de intermináveis discussões. Conhecer as suas diferenças, seus parâmetros e seu desempenho e estabilidade são obrigações do DBA. Um detalhe capcioso aqui é que ferramentas de benchmarks para discos e sistemas de arquivo costumam ser de pouca utilidade para o DBA. A carga que estas ferramentas criam nos discos dificilmente equivale a carga que um banco de dados cria na prática. Assim, a melhor forma de testa-los, continua sendo através de programas que gerem carga diretamente no banco de dados. Baseie as suas métricas de disco neste tipo de teste.

12 - Não troque segurança por desempenho

É muito fácil conseguir aumentar o desempenho de um banco de dados abrindo mão da segurança. Lembre-se que as pessoas utilizam bancos de dados mais pela sua confiabilidade e versatilidade do que pelo seu desempenho. Rotinas de backup, tolerância a falhas, garantia de ACID e outras questões não podem ser jogadas pela janela de uma hora para outra, a não ser que você se permita ser jogado pela janela quando as coisas derem errado. Existem parâmetros no SO, no SGDB e configurações de hardware que fazem o seu SGDB decolar, mas o tornam muito vulnerável ao menor problema que acontecer.

É fundamental conhecer bem os mecanismos de logs de transação e rollback. Ajustes nestes mecanismos tem grande impacto nas operações de gravação. Ao mesmo tempo, qualquer falha nesta área compromete definitivamente a integridade do banco de dados.

O PostgreSQL 8.3 foi lançado. Demorou mais que o dobro do que se esperava. Sim… a expectativa inicial eram de 6 meses até o lançamento. Mas não há motivo nenhum para se ficar triste. 2007 foi um ano excepcional para o PostgreSQL.

Vamos começar simplificando as coisas, agora é correto chamar o PostgreSQL de Postgres. Segundo a documentação oficial:

Many people continue to refer to PostgreSQL as “Postgres” (now rarely in all capital letters) because of tradition or because it is easier to pronounce. This usage is widely accepted as a nickname or alias.

Particularmente eu me acostumei a falar e escrever “PostgreSQL”. Dá mais trabalho e é mais enrolado. Mas além disso, “Postgres” parece ter mais força, causa menos confusão (com pessoas que acabam usando “Postgre” ou “Postgree”). Comercialmente também parece soar melhor, :-).

Bom, antes de falar sobre a versão 8.3, eu gostaria de mostrar alguns detalhes interessantes sobre como o Postgres passou por 2007. A comunidade cresceu, não apenas em número, mas em qualidade. Após a explosão populacional causada pela versão do Postgres nativa para o Windows, houve um crescimento numérico em usuários do Postgres, mas a sua comunidade caiu notavelmente em termos de nível técnico. 2007 pareceu recuperar o fôlego. Veja alguns detalhes interessantes:

• O planet postgresql cresceu em número de participantes e número de artigos;
• No Brasil também surgiram alguns blogs novos sobre PostgreSQL como o PG Viável e o Meu Blog de PostgreSQL;
• O número de companhias contribuindo diretamente com código aumentou, como a Sun e a Skype;
• O número de projetos no Pg Foundry também aumentou muito (hoje conta com mais de 260 projetos). Eles estão mais ativos também, com varias novas versões de projetos sendo lançadas toda semana.
• O número de eventos em que o Postgres esteve presente ou de eventos dedicados exclusivamente ao Postgres também explodiu.
• Foi criado o Postgres OnLine Journal;

Tudo muito bonito… mas chegou a hora do “Show me the code”! Sim, sim, vamos ao que realmente interessa. Vou começar mostrando um teste de performance criado quando o PostgreSQL ainda estava na sua fase Beta.

Antes de dizer qualquer coisa, você precisa entender que o teste foi feito numa condição muito específica:

• Uso do pgbench que realiza uma carga semelhante ao TPC-B, que é uma carga transacional que visa simular um ambiente OLTP;
• As tabelas forma ajustada para um fator de escala de 100, o que equivale a um volume de 10 milhões de linhas;
• 100 conexões ativas simultâneas;
• 10 milhões de transações;

Isto demonstra um cenário bastante específico. Se você esperar um ganho de performance em pequenas aplicações, você certamente não encontrará ganhos de performance tão significativos. Mas o cenário utilizado é muito importante, pois é semelhante a um ambiente corporativo, onde temos aplicações com um grande volume de dados sendo alterado por muitos usuários simultaneamente. Este é um cenário bastante difícil de lidar. Bancos de dados menores certamente sofrerão amargamente neste cenário.

Quando a versão 8.0 foi lançada, uma série de novas funcionalidades importantes marcaram a transição da série 7.x para a série 8.x . Além das novas funcionalidades que tornam a vida do desenvolvedor mais fácil (ou funcionalidades que melhoram a usabilidade) notou-se um aumento significativo nas opções de segurança (PITR, Stand By, etc) e de desempenho (Tablespaces, Particionamento, AutoVacuum, etc). Com a versão 8.3, estas novas funcionalidades atingiram um bom grau de maturidade. Ao comparar a versão 7.4 com a versão 8.3, você verá um salto enorme atingido em apenas 4 anos. Veja a matriz de comparação de funcionalidades para ter uma idéia do que foi realizado.

Ao olhar a última versão do Oracle 11g, percebe-se um aprimoramento notável nas ferramentas de gerenciamento do SGBD. As ferramentas de gerenciamento do Oracle 11g são realmente invejáveis. No entanto, quando pensamos em desempenho, não vemos um aprimoramento significativo. O mesmo aconteceu na transição da versão 9i para o 10g.

Por outro lado, o Postgres tem dado saltos enormes em termos de desempenho e ainda possui ferramentas pouco sofisticadas. A questão é que o desempenho do Postgres deve ultrapassar o do Oracle rapidamente. Uma pergunta que muitos devem estar se fazendo é se ele já não passou com o lançamento do 8.3! De toda forma, isto deverá ser o suficiente para atrair novos investimentos em torno do Postgres, fazendo com que mais empresas ofereçam ferramentas de gerenciamento para o Posrgres. O time principal do Postgres não desenvolve nenhuma ferramenta de gerenciamento além do psql. O resto fica a cargo de outros projetos de software livre ou de empresas que oferecem ferramentas proprietárias.

O Postgres tem simplificado o trabalho para muita gente. Migrar para ele está cada vez mais fácil. Além de incorporar funcionalidades e sintaxes semelhantes as do Oracle, o mesmo tem acontecido com o MySQL, com a introdução do tipo de dados ENUM, por exemplo. Chegou a hora de começar a comparar o Postgres e o MySQL mais de perto. No entanto engana-se quem pensa que o Postgres adota padrões exóticos de vários SGDBs se tornando uma colcha de retalhos. O Postgres concentra a maior parte dos seus esforços em trazer funcionalidades de acordo com o padrão SQL. Isto significa que o Postgres é uma excelente opção para aqueles que estão preocupados com a portabilidade também.

Bom… se você não testou o Postgres 8.3, vá correndo testar. Se já testou, você já pode ir vendo o que está por vir na versão 8.4…

Após a publicação do resultado do Benckmark realizado pela Sun já comentado de passagem por aqui, comentários importantes se espalharam por aí. De olho no Blog do Sr. Josh Berkus, vi que ele publicou hoje comentários sobre o que foi publicado de notório sobre isso na Internet.

A primeira coisa interessante é notar o que a Information Week publicou sobre isso. Vale lembrar que o jornalista que escreveu o artigo não é um simpatizante do Software Livre. Aparentemente o pessoal do MySQL concordou graciosamente com a opinião do Josh, o que mostra que o pessoal que realmente entende disso no MySQL não fala mais bobagem sobre o assunto. É claro que estamos falando de um Benckmark focado em ambiente transacional, onde o uso do MyISAM é proibitivo. Vejamos como o MySQL vai se sair com o seu novo storage engine na próxima versão. Se eles conseguirem abandonar definitivamente o InnoDB (que pertence a Oracle, hoje) será realmente um feito notável e um grande avanço para os bancos de dados livres.

Já um blog sobre Oracle, questionou um pouco alguns resultados, mas de qualquer forma, isso não quer dizer que eles não sejam significativos. Seja como for… será interessante aguardar a disponibilização para download do Oracle 11g. Não postei nada sobre isso, por um motivo simples, a nova versão e sua documentação não estão disponíveis para download até a data deste post. De qualquer forma, o que vejo nas últimas versões do Oracle são grandes melhorias na parte de administração do Oracle e poucas melhorias na parte de performance. Já o PostgreSQL tem lançado uma versão por ano com um aumento de performance significativo. É claro que este aumento de performance a cada versão do PostgreSQL (e existem boas novidades previstas na versão 8.3) não poderão se manter neste ritmo por muitos anos, mas é esperado como certo que em alguns anos o PostgreSQL ultrapassará o Oracle, mantendo um TCO significativamente mais baixo.

Sem dúvida, teremos mais boas notícias em breve mostrando que as coisas mudam, e rapidamente!

O que se segue é a versão editada de um conjunto de conselhos que eu tenho dado ao time da Sun no redesenho de uma aplicação em C++ que foi construída para MySQL, portado para o PostgreSQL, e nunca otimizado para performance. Ocorreu que estes conselhos podem ser geralmente úteis para a comunidade, então aí vão eles.

Projeto de aplicações para performance no PostgreSQL

Escrevendo regras de consultas

Para todos os sistemas gerenciadores de bancos de dados (SGDBs), o tempo por rodada significativo. Este é o tempo que leva para uma consulta passar pelo analisador de sintaxe da linguagem, o driver, a interface da rede, o analisador de sintaxe do banco de dados, o planejador, o executor, o analisador de sintaxe novamente, voltar pela interface de rede, passar pelo manipulador de dados do driver e para o cliente da aplicação. SGDBs variam na quantidade de tempo e CPU que elas levam para processar este ciclo, e por uma variedade de razões, o PostgreSQL possui um alto consumo de tempo e recursos do sistema por rodada.

Contudo, o PostgreSQL tem um overhead significativo por transação, incluindo o log de saída e as regras de acesso que precisam ser ajustadas em cada transação. Enquanto você pode pensar que não está utilizando transações para um simples comando de leitura SELECT, de fato, cada simples comando no PostgreSQL é uma transação. Na ausência de uma transação explícita, o comando é por si mesmo implicitamente uma transação.

Passando por isto, o PostgreSQL é claramente o segundo depois do Oracle em processamento de consultas complexas e longas com transações com vários comandos com fácil resolução de conflitos de concorrência. Ele também suporta cursores, tanto rolável quanto não rolável.

Dica 1: Nunca use várias consultas pequenas quando uma grande consulta pode fazer o trabalho.

É comum em aplicações MySQL lidar com joins no código da aplicação, ou seja, consultando o ID de um registro relacionado e então iterando através dos registros filhos com aquele ID manualmente. Isto pode resultar em rodar centenas de consultas por tela de interface com o usuário. Cada uma destas consultas levam 2 a 6 milissegundos por rodada, o que significa que se você executar cerca de 1000 consultas, neste ponto você estárá perdendo de 3 a 5 segundos. Comparativamente, solicitando estes registros numa única consulta levará apenas algumas centenas de milissegundos, economizando cerca de 80% do tempo.

Dica 2: Agrupe vários pequenos UPDATEs, INSERTs ou DELETEs em um único comando ou, se não for possível, em uma longa transação.

Antes, a falta de subselects nas versões anteriores do MySQL fizeram com que os desenvolvedores de aplicação projetassem seus comandos de modificação de dados (DML) da mesma forma que as junções em middleware. Esta é uma má idéia para o PostgreSQL. Ao invés, você irá tirar vantagem de subselects e joins no seu comando UPDATE, INSERT, e DELETE para tentar realizar modificações em lote com um único comando. Isto reduz o tempo da rodada e o overhead da transação.

Em alguns casos, contudo, não há uma única consulta que consiga alterar todas as linhas que você deseja e você irá usar um grupo de comandos em série. Neste caso, você irá querer se assegurar de envolver a sua séria de comandos DML em uma transação explícita (ex. BEGIN; UPDATE; UPDATE; UPDATE; COMMIT;). Isto reduz o overhead de transação e corta o tempo de execução em até 50%.

Dica 3: Considere realizar cargas em lotes ao invés de INSERTs seriais.

O PostgreSQL prove um mecanismo de carga em lote chamado COPY, que pode pegar uma entrada de um arquivo ou pipe delimitado por tabulações ou CSV. Quando o COPY pode ser usado no lugar de centenas de INSERTs, ele pode cortar o tempo de execução em até 75%.

Dica 4: O DELETE é caro

É comum para um desenvolvedor de aplicação pensar que o comando DELETE é praticamente não tem custo. Você está apenas desligando alguns nós, correto? Errado. SGDBs não são sistemas de arquivo; quando você apaga uma linha, índices precisam ser atualizados, o espaço liberado precisa ser limpo, fazendo a exclusão de fato mais cara que a inserção. Assim, aplicações que habitualmente apagam todas as linhas de detalhe e repõe elas com novas toda vez que é realizada qualquer alteração estão economizando esforço no lado da aplicação e empurrando este dentro do banco de dados. Quando possível, isto deve ser substituído pela substituição mais discriminada das linhas, como atualizar apenas as linhas modificadas.

Além disso, quando for limpar toda uma tabela, sempre use o comando TRUNCATE TABLE ao invés de DELETE FROM TABLE. A primeira forma é até 100 vezes mais rápida que a posterior devido ao processamento da tabela como um todo ao invés de uma linha por vez.

Dica 5: Utilize o PREPARE/EXECUTE para iterações em consultas

Algumas vezes, mesmo tentando consolidar iterações de consultas semelhantes em um comando mais longo, nem sempre isto é possível de estruturar na sua aplicação. É para isto que o PREPARE … EXECUTE serve; ele permite que o motor do banco de dados pule o analizador de sintaxe e o planejador para cada iteração da consulta. Por exemplo:

Preparar:
query_handle = query(’SELECT * FROM TABLE WHERE id = ?’)(parameter_type = INTEGER)

Então inicie as suas iterações:
for 1..100
query_handle.execute(i);
end

Classes para a preparação de comandos no C++ são explicadas na documentação do libpqxx.

Isto irá reduzir o tempo de execução na direta proporção do tamanho do número de iterações.

Dica 6: Use pool de conexões efetivamente

Para uma aplicação web, você irá perceber que até 50% do seu potencial de performance pode ser controlado através do uso, e configuração apropriada de um pool de conexões. Isto é porque criar e destruir conexões no banco de dados leva um tempo significativo no sistema, e um excesso de conexões inativas continuarão a requerer RAM e recursos do sistema.

Há um número de ferramentas que você pode utilizar para fazer um pool de conexões no PostgreSQL. Uma ferramenta de terceiros de código aberto é o pgPool. Contudo, para uma aplicação em C++ com requisitos de alta disponibilidade, é provavelmente melhor utilizar a técnica de pseudo-pooling nativa do libpqxx chamada de “conexões preguiçosas”. Eu sugiro contatar a lista de de e-mail para mais informações sobre como utilizar isto.

Com o PostgreSQL, você irá querer ter quantas conexões persistentes (ou objetos de conexão) forem definidas no seu pico normal de uso de conexões concorrentes. Então, se o uso máximo normal (no início da manhã, digamos) é de 200 conexões concorrentes de agentes, usuários e componentes, então você irá querer que ter esta quantidade definida para que sua aplicação não tenha que esperar por novas conexões durante o pico onde será lenta na criação.