Reindexar Firebird

[sesistemas]

Existe uma maneira de regerar completamente todos os índices em um banco de dados FireBird

sem ser através do backup/restore?

 

Ao longo de meses ou anos, o banco de dados está ficando cada vez mais lento,

mas ao fazer um backup e restaurar a correção é imediata. Gostaria de obter o mesmo resultado sem ter 

parar a execução do sistema.

 

O que eu tentei até agora:

 

- Recalcular estatísticas sobre todos os índices(

     FOR SELECT rdb$index_name

     FROM rdb$indices

     INTO :name DO

    SET STATISTICS INDEX :name).

Este parecia ter um efeito pequeno, então eu comecei a executá-lo automaticamente a cada mês,

mas o efeito é muito pequeno e, eventualmente, um backup / restore é

novamente necessário.

 

- Backup e restauração. Isso funciona muito bem, mas requer um acesso exclusivo

ao banco de dados. E meus usuários não estão muito felizes em parar o serviço para fazer Backup / Restore.

 

Eu estou usando o Firebird versão 2.5

 

Alguém pode me apontar para alguma solução com gbak ou gfix que eu desconheça,

ou alguma outra solução que possa ser utilizada em tempo de execução?

 

Att.

 

Antar Ferreira

 

 

[adilsonpazzini]

Voce precisa mesmo e fazer somente o Backup .. pois o Backup e quem faz a limpesa do Lixo de dados do Firebird ... nao precisando fazer o Restore . pode tentar agendar isso em um horario que ninguem esta usando pra vc rola so o backup .

[EMBarbosa]

Você deve estar com algum problema. Normalmente não é necessário ficar fazendo backup/restore num BD Firebird.

Você já detectou exatamente o quê faz o BD ficar lento?

[sesistemas]

adilsonpazzini

 

Eu testei fazer apenas o Backup e não resolveu, acredito que a reindexação é feita apenas no Restore

 

EMBasrbosa

 

A princípio também desconfiei ser algum outro problema, mas não consegui encontrar outra solução melhor (fora o SET STATISTICS INDEX conforme descrevi acima, apesar de ser uma solução paliativa)

Também encontrei em alguns fóruns outras pessoas com o mesmo problema, mas igualmente sem solução.

A propósito, em outros clientes que usam o mesmo sistema não acontece este erro.

 

Meu banco atualmente tem 790 MB e a tabela que está travando tem 90 mil registros. 

[adilsonpazzini]

E esta travando em que momento ?

[EMBarbosa]

Cerca de 90 mil registros é pouco. Mas você tem que entender que não dá pra ler todos os registros sem ter uma demora.

790 MB o backup? Porque se for o BD, ele não é grande...

 

Já tive problemas quando peguei o sistema atual da empresa para mexer. Em geral era a forma como o programa trabalhava com o BD.

Por isso eu lhe perguntei se já mediu os tempos e detectou exatamente o quê fica lento.

Não há como ajudar sem saber exatamente o que é lento.

[sesistemas]

O Problema acontece num determinado INSERT que faço na tabela que falei

Demora uns 40 segundos na minha máquina, na do cliente então... 

Ai quando eu faço um Backup/Restore o problema some, e a inserção acontece instantaneamente.

 

Eu já excluí a aplicação como problema pois o mesmo INSERT sendo executado diretamente pelo IBExpert fica lento do mesmo jeito.

 

Pesquisei bastante e descobri que o FB tem alguns problemas com índices quando o banco começa a ficar maior.

Só que eu não posso ficar parando o serviço do meu cliente para fazer backup e restaurar toda vez que começar a ficar lento.

Editado 15 Maio, 2015 por sesistemas

[adilsonpazzini]

qual o tamanho de pagina que vc utiliza Page Size?

[sesistemas]

Estou enviando as estatísticas do meu banco em questão.

Até gostaria de uma "luz" na interpretação destes dados pois que não entendo muito bem não.

 

Database header page information:

        Flags                   0

        Checksum                12345

        Generation              53246782

        Page size               16384

        ODS version             11.2

        Oldest transaction      52759983

        Oldest active           52759984

        Oldest snapshot         52759984

        Next transaction        52759985

        Bumped transaction      1

        Sequence number         0

        Next attachment ID      486764

        Implementation ID       26

        Shadow count            0

        Page buffers            0

        Next header page        0

        Database dialect        3

        Creation date           Jun 19, 2012 13:44:11

        Attributes              force write

 

    Variable header data:

        *END*

[adilsonpazzini]

Olha qnto maior o Page Size , mais tempo pra incluir excluir e tambem no update , porem mais rapido fica o SELECT . ja qnto menor o SELECT mais lento e ja na inclusao e exclusao e update mais rapido . 

 

agora olhando ai , seu problema parece ser o Gabage Collections . .

 

Oldest transaction      52759983

        Oldest active           52759984

        Oldest snapshot         52759984

        Next transaction        52759985

 

esta muito alto . isso ai as transacoes que ficaram com copias de registros alteradas . qndo isso cresce muito . vc vai ter que fazer o Backup  , pra limpar . o Correto seria vc ajustar isso pra fazer

toda semana . se nao me engano so fazendo o Backup ja faz a limpeza do Garbage Collection e zerando as transacoes .limpando esse versionamento de registros ai . deixando mais rapido .

[adilsonpazzini]

Vou postar algumns emails que recebi do pessoal do Firebird la da Firebase .

 

Backup/Restore rotineiro pode ser necessário caso o número de
transações seja muuuito grande, a fim de resetar o contador antes
de extrapolar o limite do contador de transações.

Pode haver algum ganho tb se o arquivo resultante ficar menos
fragmentado que o original.

[]s
Carlos H. Cantu
www.FireBase.com.br - www.firebirdnews.org
www.warmboot.com.br - blog.firebase.com.br

GS> Não há ganho em backup seguido de um restore, ao contrário, você perderia
GS> suas estatísticas e levaria algum tempo para o banco de dados obter a
GS> performance que conseguiu.
GS> O unico ganho que backup seguido dum restore poderia trazer é se houvesse
GS> um indices cujo depth esteja elevado demais (acima de 3), mas isso se
GS> detecta através de estatisticas de banco de dados ou de ferramentas de
GS> terceiros e então atuamos na solução do problema e não ficar realizando
GS> backup/restore. As vezes a solução está em apenas aumentar o tamanho de
GS> página ou desativar/reativar o indice.

[adilsonpazzini]

Continando .....

 

Para isso que falou sobre transações, não é necessário um restore, só o
backup ou sweeping manual(não recomendo).
Se eventualmente estiver falando das transações ainda em atividade, o custo
de parar o banco para backup e depois fazer um restore não é compensatório,
além do tempo perdido, há as estatísticas que se perderão. Numa base de
dados com muitos indices, acho fundamental não perde-las.

A questão da fragmentação é controversa, mas entendo que sistemas windows
se fragmentam demais, especialmente se o sistema não é dedicado ao FB,
ainda assim, seria melhor estudar o quanto o arquivo de dados está
fragmentado antes de propor backup seguido dum restore, alguns programas
desfragmentadores podem mostrar a fragmentação de um único arquivo e talvez
isso revele a necessidade ou não do procedimento. Se a base for gigantesca
com fragmentação recorrente, talvez seja melhor usar arquivos de dados com
tamanhos fixos ou arbitrar valores maiores para o growing.

Minha sugestão é tratar do problema depois de saber qual é, algumas
ferramentas como ibsurgeon tá aí pra isso.
Não recomendo fazer backup seguido dum restore sistematicamente como se
fosse um extintor que deva ser trocado no vencimento tendo usado ou não.

[]´s Gladiston Santana -  [email protected]

[EMBarbosa]

O Problema acontece num determinado INSERT que faço na tabela que falei

Demora uns 40 segundos na minha máquina, na do cliente então... 

Ai quando eu faço um Backup/Restore o problema some, e a inserção acontece instantaneamente.

 

Eu já excluí a aplicação como problema pois o mesmo INSERT sendo executado diretamente pelo IBExpert fica lento do mesmo jeito.

 

Pesquisei bastante e descobri que o FB tem alguns problemas com índices quando o banco começa a ficar maior.

Só que eu não posso ficar parando o serviço do meu cliente para fazer backup e restaurar toda vez que começar a ficar lento.

Desconheço o problema do FB com índices. Poderia anexar os links que você encontrou?

O que sei é que dependendo do tamanho e da quantidade dos índices, então realmente vai haver uma demora. Mas isso é natural, afinal a ideia dos índices é justamente consumir tempo antes fazendo o índices para diminuir o tempo da pesquisa depois.

 

Poderia executar um "gstat -a" no BD e postar o resultado? Talvez eu possa encontrar alguma coisa para lhe dar alguma ideia. Se puder também, nesse caso, coloque também o SQL que causa a demora.

 

EDIT: Desculpe, esqueci de pedir para fazer o acima com o BD em funcionamento. Não faça um backup/restore antes. Execute o gstat no BD em uso pelo cliente.

[sesistemas]

Encontrei várias informações do mesmo problema que o meu neste link, 

inclusive o Carlos H. Cantu e a Ann Harrison respondem algumas das questões debatidas.

 

https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/113456

 

Segue o resultado do gstat -a sobre o meu banco antes do Backup/Restore conforme pedido:

 

Database header page information:

        Flags                   0

        Checksum                12345

        Generation              53246264

        Page size               16384

        ODS version             11.2

        Oldest transaction      52758700

        Oldest active           52758701

        Oldest snapshot         52758701

        Next transaction        52759521

        Bumped transaction      1

        Sequence number         0

        Next attachment ID      486652

        Implementation ID       26

        Shadow count            0

        Page buffers            0

        Next header page        0

        Database dialect        3

        Creation date           Jun 19, 2012 13:44:11

        Attributes              force write

 

    Variable header data:

        *END*

 

Neste caso EMBarbosa, eu estou usando um ClienteDataSet, o sistema trava quando eu dou um "post" nos meus dados inseridos no CDS.

Mas quando eu faço um insert comum com dados aleatórios no próprio IBExpert, seja qual for os dados inseridos, o banco fica lento do mesmo jeito.

 

Conforme AdilsonPazzini me falou, o número de Transições está mais alto que o normal, porém eu não entendo como é feito esta estatística.

Este número é o resultado de todas as transações feitas no banco até o momento, ou são as transações em aberto ?

E qual seria um número de transações considerado normal para um aplicação deste tamanho ?

[EMBarbosa]

Essas estatísticas aí são só o cabeçalho (gstat -h). Eu queria o restante para que eu pudesse relacionar ao insert que você faz.

Acredito que você deva saber que esteja você utilizanado um ClientDataSet ou não, não faz tanta diferença. Por traz provavelmente é executado um SQL do mesmo jeito. As vezes mais ou menos eficiente.

 

Eu lembro deste post que você citou aí. A Ann H. é quem sempre tirou minhas dúvidas sobre as partes internas do FB. Você analisou o gstat -a -r como ela diz pra fazer? Chegou a conclusão que realmente são as versões anteriores dos registros que estavam atrapalhando os índices?

 

 

Com respeito ao número de transações, não estou vendo nada anormal até o momento. O limite para o número de transações é +/- 2³¹-1. Assim, quando você chegar perto de 2147483648 então você precisa ficar preocupado. Por exemplo, quanto tempo esse BD aí estava sendo executado após o Backup/restore?

 

O que eu notei é que o Sweep Interval está desabilitado. Isso significa que o garbage collection não vai executar automaticamente. Você o executa manualmente? Quando?

Esse artigo aqui explica um pouco mais sobre o assunto, que é importantíssimo para administradores de BD Firebird.

[adilsonpazzini]

Eu ate comentei ref ao numero de Oldest de transações , justamente porque o firebird quando e instalado , por default essa opção de Sweep Interval fica ativada . ai existe um parâmetro do tamanho desses oldest pra que ele seja executado , e se não me engano qndo não é concluído esse processo de limpeza do garbage collection , ai o firebird  fica la todo dia tentando fazer a limpeza , deixando muito lento . ai SO resolve no backup restore . eu ate falei pois já tive esse problema . onde me pediram justamente pra desabilitar o Sweep interval automático .

Editado 18 Maio, 2015 por adilsonpazzini

[EMBarbosa]

Eu ate comentei ref ao numero de Oldest de transações , justamente porque o firebird quando e instalado , por default essa opção de Sweep Interval fica ativada . ai existe um parâmetro do tamanho desses oldest pra que ele seja executado , e se não me engano qndo não é concluído esse processo de limpeza do garbage collection , ai o firebird  fica la todo dia tentando fazer a limpeza , deixando muito lento . ai SO resolve no backup restore . eu ate falei pois já tive esse problema . onde me pediram justamente pra desabilitar o Sweep interval automático .

Acredito que esse "parâmetro" que fala é o Sweep Interval. O Sweep Interval vem habilitado pois é normal em 90% dos BDs. O último link que eu passei explica boa parte dos conceitos e funcionamento do garbage collector.

 

Em via de regra, só se deve desabilitar o Sweep Interval quando há um número muito grande de transações (mais de 20 mil) ocorrendo num período curto (algumas horas ou minutos). Em geral basta configurá-lo para um número maior... E claro, se você o desabilita, então terá que manualmente fazer garbage collection.

Enfim... Não lembro de ter lido sobre o seu caso, então não vou entrar em mérito se o que lhe informaram estava certo ou não... Mas pelo que o sesistemas passou até agora, ainda não vi motivos para o Sweep Interval dele estar zero. Pode ser que depois dele me responder as várias coisas que perguntei eu mude de ideia.

[sesistemas]

EMBarbosa, estou enviando os dados estatísticos das duas tabelas que faço inserção.

Obrigado pelas dicas e pelo link, vou estudar detalhadamente aqui. 

 

OP_ENCAUTCLA (222)

    Primary pointer page: 363, Index root page: 364

    Average record length: 58.23, total records: 91753

    Average version length: 0.00, total versions: 0, max versions: 0

    Data pages: 550, data page slots: 550, average fill: 77%

    Fill distribution:

         0 - 19% = 1

        20 - 39% = 0

        40 - 59% = 0

        60 - 79% = 549

        80 - 99% = 0

 

    Index FK_OP_ENCAUTCLA_1 (2)

        Depth: 2, leaf buckets: 41, nodes: 91753

        Average data length: 0.00, total dup: 91748, max dup: 23664

        Fill distribution:

             0 - 19% = 0

            20 - 39% = 0

            40 - 59% = 35

            60 - 79% = 2

            80 - 99% = 4

 

    Index FK_OP_ENCAUTCLA_3 (3)

        Depth: 2, leaf buckets: 23, nodes: 91753

        Average data length: 0.00, total dup: 91752, max dup: 91752

        Fill distribution:

             0 - 19% = 0

            20 - 39% = 0

            40 - 59% = 0

            60 - 79% = 1

            80 - 99% = 22

 

    Index FK_OP_ENCAUTCLA_OP_CADPNEU (4)

        Depth: 2, leaf buckets: 130, nodes: 91753

        Average data length: 6.22, total dup: 141, max dup: 2

        Fill distribution:

             0 - 19% = 5

            20 - 39% = 0

            40 - 59% = 121

            60 - 79% = 4

            80 - 99% = 0

 

    Index FK_OP_ENCAUTCLA_OP_ENCAUTO_T (1)

        Depth: 2, leaf buckets: 28, nodes: 91753

        Average data length: 0.65, total dup: 83219, max dup: 31

        Fill distribution:

             0 - 19% = 1

            20 - 39% = 0

            40 - 59% = 0

            60 - 79% = 0

            80 - 99% = 27

 

    Index PK_OP_ENCAUTCLA (0)

        Depth: 2, leaf buckets: 132, nodes: 91753

        Average data length: 18.12, total dup: 0, max dup: 0

        Fill distribution:

             0 - 19% = 0

            20 - 39% = 1

            40 - 59% = 0

            60 - 79% = 0

            80 - 99% = 131

 

OP_ENCAUTO_T (223)

    Primary pointer page: 365, Index root page: 366

    Average record length: 52.58, total records: 8568

    Average version length: 0.00, total versions: 0, max versions: 0

    Data pages: 49, data page slots: 49, average fill: 74%

    Fill distribution:

         0 - 19% = 0

        20 - 39% = 0

        40 - 59% = 0

        60 - 79% = 49

        80 - 99% = 0

 

    Index FK_OP_ENCAUTO_T_OP_AUTOCLAVE (1)

        Depth: 2, leaf buckets: 3, nodes: 8568

        Average data length: 0.00, total dup: 8563, max dup: 2662

        Fill distribution:

             0 - 19% = 0

            20 - 39% = 0

            40 - 59% = 1

            60 - 79% = 1

            80 - 99% = 1

 

    Index FK_OP_ENCAUTO_T_OP_INIAUTO (2)

        Depth: 2, leaf buckets: 7, nodes: 8568

        Average data length: 6.72, total dup: 271, max dup: 249

        Fill distribution:

             0 - 19% = 1

            20 - 39% = 0

            40 - 59% = 0

            60 - 79% = 0

            80 - 99% = 6

 

    Index PK_OP_ENCAUTO_T (0)

        Depth: 2, leaf buckets: 7, nodes: 8568

        Average data length: 6.91, total dup: 0, max dup: 0

        Fill distribution:

             0 - 19% = 0

            20 - 39% = 1

            40 - 59% = 0

            60 - 79% = 0

            80 - 99% = 6

 

 

AdilsonPazzini, Já até tentei fazer o sweep manual, mas os únicos recursos que já tive resultado foi o Backup/Restore e o comando SET STATISTICS INDEX, porém os dois são recomendados a serem executados exclusivamente.

Caindo no mesmo problema. tendo que parar o sistema para fazer a correção.

[sesistemas]

EMBarbosa

 

Respondendo as suas perguntas, também estou a entender a situação ocorrida em meu cliente, pois até então este sistema em especial era responsabilidade de outro programador aqui da empresa.

Porém após sua saída, a responsabilidade deste sistema passou para minha mão, e não fui detalhadamente instruído sobre os detalhes deste sistema ou BD em especial.

 

Desde então começou a acontecer este problema no cliente. Então eu também estou a entender a situação. Não sei por que o sweep foi configurado para ser executado manualmente.

[EMBarbosa]

EMBarbosa

 

Respondendo as suas perguntas, também estou a entender a situação ocorrida em meu cliente, pois até então este sistema em especial era responsabilidade de outro programador aqui da empresa.

Porém após sua saída, a responsabilidade deste sistema passou para minha mão, e não fui detalhadamente instruído sobre os detalhes deste sistema ou BD em especial.

 

Desde então começou a acontecer este problema no cliente. Então eu também estou a entender a situação. Não sei por que o sweep foi configurado para ser executado manualmente.

Bom, então o melhor é tentar entender mesmo. Deixa ver se faltou alguma pergunta sem resposta...

 

 

Eu lembro deste post que você citou aí. A Ann H. é quem sempre tirou minhas dúvidas sobre as partes internas do FB. Você analisou o gstat -a -r como ela diz pra fazer? Chegou a conclusão que realmente são as versões anteriores dos registros que estavam atrapalhando os índices?

Quanto tempo esse BD aí estava sendo executado após o Backup/restore?

 

E mais uma agora: Essas estatísticas que mandou por último, sobre os índices e tabelas, foi depois de um backup/restore?

[sesistemas]

Foi antes

[EMBarbosa]

Ainda ficaram duas perguntas pra trás. o.o''