17/12/2019 23 novas formas de acelerar o Firebird

23 novas formas de acelerar o Firebird

Data da última atualização: 10/01/2019

Autor: Alexey Kovyazin, [email protected], 07-Jan-2019 - Tradução: Carlos H. Cantu

Porque 23 novas formas?

Alguns de vocês devem ter lido o artigo «45 maneiras de acelerar o Firebird», publicado em maio de 2016. Agora é hora de mais algumas dicas e
truques, adquiridas principalmente com base na experiência de otimização e manutenção de bancos de dados Firebird e servidores com grande
número de conexões (1000+).

1. Definir as opções de energia para Alto desempenho, no Windows Server 2016 e 2019
Por padrão, o Windows Server tem o plano de energia definido como equilibrado, que não é indicado para servidores de banco de dados de alto
desempenho. Defina-o como «Alta Performance» e ganhe cerca de +20% de desempenho de CPU. O parâmetro pode ser alterado sem
necessidade de reiniciar o computador. A foto abaixo mostra o gráfico de CPU que demonstra a questão.

2. Ligue a opção de «Interação com o desktop » se usar o Classic no Windows

Se você estiver usando Firebird Classic no Windows, habilite o flag «Permitir que o serviço interaja com a área de trabalho».
Sem essa configuração, o desktop heap é limitado pelo Windows, e o Firebird não poderá abrir mais que 250-300 conexões (varia de acordo com
os metadados do banco de dados, e o consumo de memória relacionado) – haverá erro de falta de memória.

Para mais detalhes, leia o artigo disponível aqui.

https://www.firebase.com.br/artigo.php?id=3098 1/5
17/12/2019 23 novas formas de acelerar o Firebird

3. Cuidado: Controladores de domínio primário

O problema é que quando o Windows é configurado como Controlador de Domínio, ele desabilita o cache de gravação no disco contendo o banco
de dados do Active Directory. Isso afeta o Firebird de várias maneiras (assim como outros aplicativos e bancos de dados, é claro), e prejudica de
forma significativa a performance do servidor. Esse problema afeta versões populares do Windows, como o Windows Small Business Server 2011,
além de outras que estejam com o recurso de controlador de domínio ativo.

4. Aumente o limite de número máximo de arquivos abertos, no Linux

Se estiver usando o Linux no seu servidor de banco de dados, não se esqueça de tunar os limites para o Firebird.

Verifique os limites para o processo do Firebird (SuperServer ou SuperClassic) com o seguinte comando:

cat /proc/<firebird_proc_id>/limits

E preste atenção na linha com o parâmetro de número máximo de arquivos abertos. O Firebird pode usar até 4 handles por conexão, e se você
encontrar algo como:

Max open files            4096                 4096                 files

A linha acima significa que o número de conexões servidas pelo Firebird será limitado em torno de 1.000. Se você tem, por exemplo, 4 bancos de
dados no servidor, as conexões de todos eles são consideradas.

Aumente o valor para 65535.

Após reiniciar o Firebird, certifique-se de checar novamente para ver se as mudanças foram aplicadas. Para o Classic, é necessário checar e
aumentar os limites para o usuário “firebird”.

5. Use um Linux atualizado

Ok, parece ser um conselho bobo, mas presenciei muitas vezes um bom aumento de performance depois de migrar do CentOS 6 para o 7, Ubuntu
12 para o 16, mantendo o mesmo hardware, portanto, considero essa recomendação como mandatória para qualquer servidor com mais de 250
conexões. As versões/distribuições recomendadas do Linux são: CentOS 7.x e Ubuntu 16, 18.

6. Reserve 40% de RAM para o cache no Windows

O gerenciador de memória dos sistemas operacionais tem implicações relacionadas a alocação de memória e, por padrão, o Windows requisita
40% da RAM para cache de arquivos. Infelizmente, o pobre Gerenciador de Tarefas do Windows mostra a memória usada para o cache de
arquivos como sendo “memória livre”, e alguns administradores tentam fazer com que o Firebird consuma toda essa memória, configurando o
parâmetro DefaultDBCachePage no firebird.confpara valores altíssimos, o que geralmente acaba gerando swap no disco.

Sendo assim, use o RAMMap para ver o real consumo de memória no Windows. A regra empírica para o Windows Server (usado como servidor
dedicado ao Firebird) é:

Firebird memory (Working Set) deve ser menor que 40% do total de RAM. Se o total dos working sets para todos os processos for mais que 50%,
o Windows poderá começar a fazer swap.

Caso se interesse em saber mais detalhes, existe um Webinar (em inglês) sobre gerenciamento de memória:
Parte 1. https://www.youtube.com/watch?v=ZBmLgbYt4aM
Parte 2, https://www.youtube.com/watch?v=-1_DF2FnU-A

7. Reserve 30% de RAM para o cache de arquivos no Linux

O Linux trabalha com o cache de arquivos de forma diferente do Windows e, em geral, o montante de memória usada para o cache de arquivos
pode ser bem menor que o do Windows, sem que se note uma degradação de performance. No entanto, para garantir uma alta performance em
um sistema com um grande número de conexões, especialmente no Classic e no SuperClassic, o recomendado é "reservar" 30% da RAM para o
cache de arquivos.

8. Use o irqbalance no Linux

O uso do irqblalnce geralmente melhora a performance do Firebird e o balanceamento de uso de CPU em servidores com muitos cores.

9. Para máquinas virtuais – cuidado com o Overcommit de Memória

As VMs podem ser configuradas de forma a ter mais memória do que existe fisicamente na máquina host – um recurso chamado de Memory
Overcommit (o nome pode mudar dependendo do sistema de virtualização utilizado).

Isso significa que nos picos de uso de memória (tanto na VM com o Firebird, como em qualquer outra rodando no mesmo host) poderá
ocorrer swap, ocasionando uma grande degradação de performance. Para VMs em servidores de banco de dados de alta performance, a
configuração de memória deve ser estática.

10. Verifique os limites das Virtual Machines

Normalmente as VMs são criadas com limites padrões de CPU e I/O, que podem ser muito baixos, como 50 IOPS e 10% CPU. Verifique a
configuração da máquina virtual e remova todos os limites – servidores de bancos de dados de alta performance devem utilizar toda a capacidade
de CPU, bandwidth e I/O.

11. Apague os arquivos temporários do Firebird

O Firebird cria vários arquivos temporários para diversas operações: ordenação, processamento de BLOBs, tracing, etc. Esses arquivos são
armazenados nos seguintes locais: no Windows C:\ProgramData\firebird, no Linux /tmp/firebird
Normalmente, esses arquivos são removidos automaticamente, no entanto, algumas vezes isso não acontece (por exemplo, se o servidor for
reiniciado).

Verifique esses diretórios periodicamente e apague os arquivos antigos – pode haver vários gigabytes e apaga-los irá liberar espaço no drive do
sistema.

https://www.firebase.com.br/artigo.php?id=3098 2/5
17/12/2019 23 novas formas de acelerar o Firebird

12. Tenha certeza que o cache de arquivos está sendo usado

Como você sabe, o cache do Firebird (também conhecido como page buffers) é definido pelo
parâmetro DefaultDBCachePages no firebird.conf/databases.conf, ou diretamente no header da base de dados. No Firebird 3, o valor desse
parâmetro pode ser bem alto, mas é importante lembrar de outro parâmetro:FileSystemCacheThreshold.

Se o FileSystemCacheThreshold for menor que o DefaultDBCachePages ou que o page buffers, o cache de arquivos do sistema operacional não
será usado, levando a problemas de performance. Em 99% das vezes, é melhor que o cache de arquivos seja usado. Para ter certeza que isso
acontecerá, use a seguinte regra para configurar os parâmetros:

DefaultDBCachePages = X
FileSystemCacheThreshold = X+ N, N>1

Existem casos raros onde desativar o cache de arquivos melhora a performance – se isso acontecer com você, entre em contato comigo pois tenho
interesse em saber - [email protected]!

13. Acelere o banco de dados de segurança

Toda conexão com um banco de dados Firebird estabelece também uma conexão com a base de dados de segurança (security3.fdb, no caso do
Firebrid 3), e realiza diversas operações de leitura e escrita (transaction pages, header page, etc). Se você tem conexões frequentes, a
performance da base de dados de segurança pode se tornar um problema.

No mínimo, você deve fazer o seguinte:

Aumente o buffers do security”n”.fdb (o valor empírico máximo é 256)

Mova o security”n”.fdb para o drive mais rápido do sistema (a operação é trivial no Firebird 3, mas no Firebird 2.5 exigirá uma reinstalação
do Firebird no disco especificado)

Então, você poderá configurar a base de segurança com Forced Writes desligado – a pequena chance de corrupção não seria um problema aqui. A
solução mais radical é tornar a base de dados de segurança ReadOnly, o que eliminaria qualquer escrita nela. Se você raramente manipula
usuários na base de segurança, é a melhor solução.

14. Experimente o SuperClassic no Firebird 3

A arquitetura SuperServer foi amplamente divulgada e recomendada no Firebird 3, mas existem alguns tipos de uso/carga que demonstraram
melhor performance com o SuperClassic (não com o Classic, que sempre funciona de forma mais lenta do que o SuperServer/SuperClassic). Para
realizar esse experimento de forma segura, siga os passos abaixo:

Para experimentar o SuperClassic:

Configure no firebird.conf
ServerMode=SuperClassic
DefaultDbCachePages=1024
gfix –buff 0 <database>
Reinicie o Firebird

Para reverter para o SuperServer

Configure no firebird.conf
ServerMode=SuperServer
DefaultDbCachePages=N # N*page size*databases_count < 25% RAM
FileSystemCacheThreshold = N+1
gfix –buff 0 <database>
Reinicie o Firebird

Ficarei grato se me escrever reportando o resultado dos testes: ([email protected]).

15. Bases grandes? Aumente o tamanho da página

Por padrão, uma base de dados no Firebird é criada com os seguintes tamanhos de página:

FB 2.5 – 4096 bytes

FB 3.0 - 8192 bytes

No entanto, o maior tamanho suportado é 16K (no FB 4 será de 32K). Para bases de dados com mais de 100GB, em 95% dos casos é melhor
configurar o tamanho da página para o maior valor suportado, de forma que:

Diminua a profundidade dos índices (index depth). O depth deve ser <=3. Depth >= 4 terão uma performance muito ruim.
Aumentar o consumo de RAM. O cache do Firebird é configurado em número de páginas, 1.000 páginas de cache, em uma base com
páginas de 8K, significa o uso de 8MB de memória, e 16MB com páginas de 16K.
Diminuir o número de páginas na base. Aumentará a performance de acesso a registros de tabelas enormes (menos “pulos” entre páginas
de ponteiros e de dados), e ajuda na preparação de comandos SQLs extensos.

Para mudar o tamanho da página de uma base de dados, deve ser feito um backup com o gbak, e restaura-lo com o parâmetro –page (ex: gbak –
c –page 16384).
Note que, se você tiver uma base de dados com muitos blobs pequenos, aumentar o tamanho da página poderá tanto aumentar como diminuir a
fragmentação dos dados na base.

16. Não use o recurso “no_reserve”

O flag “no_reserve”, quando ativo,faz com que o Firebird deixe de reservar 30% de espaço nas páginas de dados para armazenar versões
temporárias de registros, que são criadas após um update ou um delete. Esse flag permite que os dados sejam armazenados de forma mais
compacta (diminuindo o tamanho do arquivo da base de dados), mas no caso de um UPDATE/DELETE, todas as mudanças ocorrerão em uma nova
página de dados, fazendo com que essas operações se tornem mais lentas.

Sendo assim, só ligue esse flag se sua base de dados for ReadOnly (somente leitura).

Verifique se o flag está ativo usando o gstat –h base_de_dados e verificando a linha “Attributes”.

https://www.firebase.com.br/artigo.php?id=3098 3/5
17/12/2019 23 novas formas de acelerar o Firebird
Desligue o flag com o comando:

gfix  -use reserve database

Depois desse comando, novas páginas de dados serão criadas com espaço reservado. No entanto, para se beneficiar completamente da mudança,
é necessário fazer um backup/restore da base de dados com o gbak, para que todas as páginas sejam criadas com reserva de espaço.
Obviamente, o tamanho da base de dados irá aumentar após remover o flag no_reserve e fazer o backup/restore.

17. Configure um valor alto para a lock table

A Lock table é um mecanismo do Firebird utilizado para sincronizar o acesso a engine do SGBD. A lock table aumenta de tamanho
automaticamente, mas esse aumento de tamanho é uma operação lenta, que pode resultar em micro-congelamentos. O tamanho da Lock
table nunca diminui, e é iniciado com o valor definido no firebird.conf.
Para prevenir aumentos constantes de tamanho da lock table, é recomendável observar o tamanho da mesma no final de um ciclo de trabalho
(dia, semana, etc.), e configurar esse tamanho como sendo seu tamanho inicial no firebird.conf:

LockMemSize=99999999

Apenas para referência, o LockMemSize em um sistema com cerca de 1.000 usuários é geralmente menor que 200MB.

18. Use o fb_lock_print para contar o número de usuários conectados ao banco

Saber a quantidade de conexões em uma base de dados é uma tarefa frequente para os desenvolvedores, como por exemplo, para controlar
número de licenças, etc. Não é raro que o desenvolvedor use um SELECT count(*) FROM MON$ATTACHMENTS para obter esse valor, mas essa
não é a melhor forma de fazê-lo: a execução frequente de queries nas tabelas MON$ pode se tornar um fardo para o banco de dados, portanto,
melhor usar uma alternativa:

Execute o fb_lock_print –d path_para_o_BD | alias e verifique o valor de Owners – ele mostrará o número atual de conexões na base de dados.

19. Evite LEFT JOINs desnecessários

Frequentemente vejo queries com a construção parecida com:
T1 LEFT JOIN T2 ON (…) WHERE T2.Field_condition

Essencialmente, a condição do where na T2 exclui os NULLs do resultado do LEFT JOIN T2, portanto, é possível trocar o LEFT JOIN para um INNER
JOIN, sem alterar o resultado da query.

Um INNER JOIN dá mais liberdade para o otimizador do Firebird e, nas versões mais recentes, é otimizado geralmente de forma mais eficiente do
que um LEFT JOIN.

A troca é especialmente recomendada nos casos em que:

A clausula where não tem uma condição para T1

T2 é uma tabela pequena

20. Evite contar registros desnecessariamente

Outro engano comum em queries complexas e/ou stored procedures é usar um count() no select, apenas para verificar a existência de um
registro.

A query abaixo terá que fazer a leitura de todos os registros retornados pela condition1:
(select count(*)…. where condition1) >0

Muito melhor seria usar a construção abaixo

Exists(select first 1 id where condition1)

Se a condition1 retornar mais que 1 registro, a solução proposta será executada de forma muito mais rápida.

21. Evite ordenações desnecessárias em stored procedures

A ordenação de queries usadas dentro de stored procedures deve ser justificada por regras de negócio. Por exemplo, na procedure abaixo, o
ORDER BY é inútil do ponto de vista de regra de negócio, e adiciona uma etapa de ordenação desnecessária:

create or alter procedure NEW_PROCEDURE

returns (
SUMX double precision)
as
declare variable _amount double precision;
begin
for select T1.amount from Table1 t1 where ....
order by id
into :_amount
do
begin
sumx=sumx+_amount
end;
suspend;
end

22. Não deixe queries preparadas sem necessidade

Frequentemente encontramos 500-1.000 comandos preparados em cada conexão (pode ser checado nas tabelas MON$). A grande maioria deles é
executado apenas uma vez, permanecendo preparado na RAM, deixando o working set do Firebird maior e as queries nas tabelas MON$ mais
lentas.

A recomendação é que se deixe os SQLs preparados apenas nos casos onde eles serão executados várias vezes, ou se o tempo de preparação for
muito grande (como em queries complexas com vários joins acessando tabelas enormes).

https://www.firebase.com.br/artigo.php?id=3098 4/5
17/12/2019 23 novas formas de acelerar o Firebird

23. Sempre feche as queries que fazem grandes ordenações

Enquanto a query que realizou a ordenação (ORDER BY, GROUP BY, UNION, distinct) não é fechada, o Firebird mantem os registros ordenados na
memória. O tamanho máximo para o uso de RAM para ordenações é definido pelo parâmetro TempCacheLimit no firebird.conf, sendoo padrão
64MB.

Mesmo com um valor maior para o TempCacheLimit, as queries com maior tempo de execução e grande número de registros ordenados irão
consumir eventualmente toda a memória permitida e, como resultado, a ordenação passará a ser feita em arquivos temporários no disco,
deixando o processo mais lento. A recomendação é fechar essas queries rotineiramente.

https://www.firebase.com.br/artigo.php?id=3098 5/5