Analisando e melhorando o desempenho do banco de dados Firebird com o Sinática Monitor

por Eduardo Rocha - Publicado em 04/05/10

Recentemente dois clientes me pediram para fazer uma análise no banco de dados Firebird a fim de melhorar o desempenho dos sistemas.

No mercado existem alguns softwares para monitoramento de banco de dados Firebird, optei por testar o Sinática Monitor desenvolvido pelo Douglas Tosi, pois ele já havia escrito um artigo sobre este na revista Active Delphi.

Analisando o software, achei bastante simples e ao mesmo tempo muito eficaz. Você apenas indica as configurações de acesso ao banco e o software fica monitorando e lhe dando diversas informações muito importantes, tais como:

- alertas de comandos lentos
- alertas de transações pendentes há muito tempo
- alertas de querys que fazem full scan (varre a tabela toda)
- querys executadas no banco (tempo, quantidade, % de scan, etc)
- número de transações abertas
- atividades de I/O
- uso de memória
- e muitas outras informações

Tudo isso é possível, pois o Sinática Monitor utiliza as novas tabelas de estatísticas do Firebird para resgatar estas informações, por isso funciona apenas a partir da versão 2.1 do Firebird, pois somente a partir desta versão é que temos estas tabelas disponíveis.

Ao ver a eficiência do Sinática Monitor, logo recomendei aos meus clientes. A decisão deles foi imediata, fizeram a compra e com isso pude usar o software dentro da empresa fazendo a análise do banco durante alguns dias. Com as informações que o Sinática Monitor mostrou, foi possível identificar diversos pontos críticos nos sistemas que deixavam o banco lento, tais como:

- querys executadas muitas vezes trazendo muitas informações
No Sinática Monitor temos um grid que exibe as querys executadas, informando inclusive a quantidade de vezes. Notei que algumas eram executadas mais de 250 vezes em menos de uma hora. Portanto, foquei nestes casos e ajustei o sistema para manter os dados em memória depois de aberta uma vez. Isso reduziu drasticamente o tráfego em rede.

- querys trazendo TODOS campos com "select *"
Com a análise do Sinática Monitor, percebi que haviam muitas querys trazendo todos os campos da tabela desnecessariamente. Utilizar "select *" é um grande problema, pois além de dar trabalho ao banco para "descobrir" quais são os campos, gera um grande tráfego em rede caso a tabela tenha muitos campos, principalmente se tiver campos blobs. Portanto, ajustei diversas querys pra trazer somente os campos necessários e isso ajudou a reduzir o tráfego em rede.

- querys fazendo full scan, não utilizando índices
Este é um cenário comum, pois quando estamos desenvolvendo, devido a falta de tempo, as vezes não testamos o desempenho das querys para ver se é necessário criar índices, até por que durante o desenvolvimento normalmente usamos um banco de teste pequeno, então sempre será rápido. Somente num ambiente de produção é que podemos enxergar o problema. Com o Sinática Monitor pude observar as querys que faziam FULL SCAN, sendo assim criei os devidos índices, desta forma querys que demoravam 10 segundos, puderam ser executadas em apenas 1 segundo.

- querys lentas
As vezes não basta criar um índice para melhorar o desempenho de uma query, é necessário também modificar a forma como ela é feita. O Sinática Monitor me exibiu as querys lentas na "aba" de alertas e pude trabalhar nestas querys. Foi necessário um ajuste na própria construção da query. Um exemplo muito comum foi que em alguns casos troquei operadores "in" que faziam SELECT em tabelas grandes por uma construção utilizando "joins", com isso pude ganhar bastante desempenho em algumas querys.

- querys fazendo full scan quando usado LIKE 'alguma coisa%'
Para quem não sabe, quando o "%" está no final da string o banco aproveita o índice (caso haja), caso contrário é feito um FULL SCAN na tabela. O Sinática Monitor me mostrou muitas querys fazendo FULL SCAN mesmo usando o "%" no final. Fazendo alguns debugs na aplicação, notei que isso ocorria somente quando usava parâmetro para informar o valor do LIKE, já usando um valor fixo o banco aproveitava o índice. Recorri ao meu amigo Carlos H. Cantu e questionei sobre este fato, ele me disse que é isso mesmo, pois ao usar parâmetros, o banco não sabe o valor que você informará, portanto, o Firebird fará o plano de acesso assumindo o "pior" caso. Isto foi um "choque", pois sinceramente eu não sabia, portanto, ajustei todo sistema para não usar parâmetros no operador LIKE, usei somente valores fixos. Uma alternativa que o Cantu me deu foi usar o operador "STARTING WITH" ao invés do LIKE, claro que nos casos em que for utilizar o "%" no final, exemplo: select nome from cliente where nome starting with 'Eduardo'. Desta forma poderá utilizar parâmetros tranquilamente e o banco aproveitará o índice.

Ainda não acabou

Quando eu achava que tinha terminado minha análise e que os problemas estavam resolvidos, surgiu outro. O Sinática Monitor estava me alertando que haviam muitas transações abertas há muito tempo. Analisei o número de conexões e notei que a quantidade era igual à de transações abertas. Logo pensei:

- Será que toda instância da aplicação está mantendo uma transação aberta?

Bingo, era isso mesmo!

Isso que vou falar pode desanimar muita gente, mas infelizmente, e para minha surpresa, aquele driver antigo da DBExpress para Firebird possui um bug que deixa uma transação aberta após um SELECT realizado, a transação só é encerrada quando encerramos a conexão com o banco de dados, ou seja, na prática só será encerrada quando sairmos da aplicação. Não se preocupe que não é uma transação a cada SELECT, é apenas uma, porém fica aberta durante o tempo que ficar com a conexão aberta. Então imagine 250 usuários utilizando seu sistema, logo serão 250 transações abertas o dia todo até saírem do sistema. Isso é extremamente prejudicial ao servidor, pois o Firebird tem de manter o versionamento dos registros para estas transações e isso influencia diretamente no processo do Garbage Collection, além claro do trabalho em manter estas transações abertas por muito tempo, ou seja, é um efeito cascata!

Vale lembrar que notei este problema utilizando o Delphi 7 + a dbexpint.dll que acompanha a DBExpress, não sei se nas novas versões o problema continua. Portanto, testei diversos drivers de terceiros para ver qual solucionava este problema. Testei o driver da UIB, InterXpress (Upscene) e DevArt. Destes três, somente o driver da DevArt resolveu o problema, ou seja, ele não mantém a transação aberta. Ao fazer um SELECT ele abre e fecha a transação imediatamente. Portanto, atualizei a aplicação do cliente com este driver e fazendo o monitoramente pelo Sinática Monitor pude notar que o número de transações abertas ficou ZERADO. Para saber mais sobre este driver, visite o site:
http://www.devart.com

Outra dica para ganhar desempenho

Sempre soube que a nova versão do Firebird 2.1 trouxe melhorias no protocolo de comunicação, chegando a ganhar 40% de desempenho em alguns casos. Na época não havia acreditado, mas fazendo os testes pude comprovar! Realmente houve um melhora muito significativa, com os testes que realizei, dados que levavam 25 segundos para serem trafegados, puderem ser trafegados em apenas 7 segundos. Para obtermos este desempenho, é necessário que o servidor seja no mínimo FB 2.1 e os clients também.

Um pequeno problema ao usar o FB 2.1.3 com SQLDataSets

Acredito que com esta dica acima, você já vai querer usar o Firebird 2.1.3, mas antes disso, deixe-me lhe dar outra dica. Existe um problema no driver antigo da DBExpress quando usamos justamente o componente SQLDataSet com o Firebird Client 2.1.3. Ao rodar uma query notará um erro do tipo dbExpress Error: Unknown Error Code '65535'. Vale lembrar que este cenário aconteceu usando o Delphi 7 + a dbexpint.dll, não sei se o problema ocorre em outras versões do Delphi.

- E sabe qual foi a solução?

Usar o driver da DevArt! Usando este driver o problema foi solucionado, portanto, está aqui outra dica, vale a pena usá-lo para substituir aquele antigo da Borland.

Uma "última" dica

A "última" dica que gostaria de passar para vocês é que desliguem o "sweep" automático que o Firebird Super Server faz de tempos em tempos. Este recurso vem ligado por padrão e pode ser disparado a qualquer momento, imagine sendo disparado num horário de pico onde todos estão acessando o banco de dados, o desempenho da sua aplicação pode cair drasticamente. Esta é uma outra situação que o Sinática Monitor me ajudou a observar, pois logo que liguei o monitoramente o software já me alertou que este recurso estava ligado e me aconselhou a desligar. No caso do meu cliente desliguei este processo e coloquei no crontab do linux para ser executado somente de madrugada. Para quem não conhece este recurso, recomendo ler o artigo a seguir da Firebase para entender mais sobre Sweep:
http://www.firebase.com.br/fb/artigo.php?id=2047

Conclusão

O Sinática Monitor contribuiu muito para me informar dos problemas destas aplicações, sem este software ficaria quase impossível detectar estes problemas. Já imaginou ter de entrar em cada componente que contém as querys e testar cada uma para analisar o desempenho, o tempo que leva, quantas vezes foi executada, se a transação foi encerrada? Enfim, seria muito trabalhoso, portanto, não deixe de conhecer o Sinática Monitor, certamente você terá resultados satisfatórios.

Eu mesmo pude comprovar na prática a eficiência deste software. Ambos os clientes ficaram muitos satisfeitos, pois com a análise do Sinática Monitor, ajustei todas querys problemáticas, atualizei o driver da DBExpress, fiz um upgrade para o Firebird 2.1.3 e no final o sistema ganhou um bom desempenho.

Confira os detalhes do Sinática Monitor no link a seguir:
http://www.sinatica.com/index.php/br/beneficios/aumente-desempenho-firebird

Ganhe descontos na aquisição do Sinática Monitor

Pedi ao Douglas Tosi (responsável pelo produto) para conceder descontos para vocês, pois vale muito a pena adquiri-lo, portanto, se for comprar basta utilizar os links abaixo (de acordo com o número de bancos que quer monitorar simultaneamente) e ganhará 10% de desconto:

Licença para analisar um banco de cada vez
https://sites.fastspring.com/sinatica/instant/sinaticamonitor1database?coupon=edudelphipage

Licença para analisar até cinco bancos simultaneamente
https://sites.fastspring.com/sinatica/instant/sinaticamonitor5databases?coupon=edudelphipage

Licença para analisar até dez bancos simultaneamente
https://sites.fastspring.com/sinatica/instant/sinaticamonitor10databases?coupon=edudelphipage

5 Comentários

  • Akira - 05/05/10 09:22

    fala Edu, tudo bem meu caro?!

    Cara, adquirimos o Sinática aqui na empresa e identificamos vários problemas, assim como os que você relatou. A ferramenta é formidável.

    Um ponto interessante da ferramenta é que você pode derrubar um comando se ele estiver sendo o ofensor da lentidão em seu banco. Isso ajudou bastante na identificação de alguns selects que estavam mal feitos na aplicação.

    Parabéns pelo artigo!

    Abraços

  • Caique - 05/05/10 12:29

    Error: Unknown Error Code '65535'.

    Basta passar a propriedade GetMetaData ( default True ) do SQLDataSet para FALSE.

    alem de funcionar ... ganha-se muito em performance mesmo quando o erro não ocorre.

    O SQLQuery por padrão tem esta propriedade setada para FALSE.

  • Eduardo Rocha - 06/05/10 16:05

    Valew pela dica Caique, não havia tentado desligar esta propriedade, e acabei de testar, realmente desligando resolve.

    Obrigado

    Abs

  • Agnaldo - 19/05/10 11:53

    Você não testou com ZeusLib ?

  • Eduardo Rocha - 19/05/10 12:00

    Agnaldo,

    Não testei, caso alguém tenha testado, fique a vontade para relatar.

    Obrigado

    Abs

 

"Ouvindo" Opiniões (as mais lidas)

Em breve, aguarde!!!

Pharetra Sed Tempus

Morbi sit amet mauris Nam vitae nibh eu sapien dictum pharetra. Vestibulum elementum neque vel lacus. Lorem ipsum dolor sit dolore phasellus pede lorem proin auctor dolor loremmassa phasellus sit. More…

Outras edições da Revista Active Delphi