45 maneiras de acelerar o banco de dados Firebird
Aqui você encontra a lista de dicas de desempenho para banco de dados Firebird em diferentes áreas - desde hardware/SO e ajustes de configuração do Firebird até recomendações de otimização de SQL. Esta lista não é a referência completa de como otimizar o Firebird, e pressupõe que você entende os fundamentos do funcionamento do Firebird, como planos de execução, gerenciamento de transações e estatísticas de desempenho de consultas.
Por favor, aplique estas dicas com cautela e verifique seu efeito antes de colocar em produção.
Nossa empresa (IBSurgeon) oferece o serviço abrangente de otimização de desempenho de banco de dados.
1. Coloque o banco de dados em SSD
Coloque seu banco de dados em SSD. A unidade SSD fornece E/S aleatória muito melhor do que unidades tradicionais. A E/S aleatória é crítica para ler e gravar dados distribuídos por um arquivo de banco de dados grande - a maioria das operações de banco de dados exige E/S aleatória paralela intensiva.
2. Use RAID 10
Se você usa RAID1 ou RAID5, considere RAID10 - é 15-25% mais rápido.
3. Verifique a BBU
Se você estiver usando um controlador RAID, verifique se ele possui uma Unidade de Bateria de Backup (BBU) instalada e operacional - alguns fornecedores não fornecem BBU por padrão. Sem BBU, o controlador desativa o cache, e o RAID funciona muito lentamente, até mais lento que unidades SATA comuns. Normalmente, você pode verificar o status da BBU na ferramenta de configuração do RAID.
4. Defina o cache de gravação para write-back
Se você estiver usando um controlador RAID com BBU instalada (e servidor com UPS), verifique se o cache está definido como write-back (não write-through). «Write-back» ativa o cache de gravação do controlador.
5. Ative o cache de leitura
Se você usa controlador RAID, verifique se ele tem o cache de leitura ativado.
6. Verifique o subsistema de disco
Verifique suas unidades quanto a blocos defeituosos e outros problemas de hardware (incluindo superaquecimento). Problemas de hardware podem diminuir significativamente o desempenho de E/S e levar a corrupções no banco de dados.
7. Use SuperClassic ou Classic no Firebird 2.5
Se você usa Firebird 2.5 SuperServer com muitas conexões, tente usar SuperClassic ou Classic, eles podem escalar melhor usando todos os núcleos da CPU.
8. Use SuperServer 3.0 no Firebird 3.
Se você usa Classic ou SuperClassic no 2.5, considere migrar para o Firebird 3.0 SuperServer, agora ele pode usar múltiplos núcleos e combinar isso com as vantagens do cache compartilhado.
9. Aumente o cache de páginas de buffer
Aumente o tamanho do cache de páginas de buffer (parâmetro DefaultDBCachePages) dos valores padrão. Para 2.5 SuperServer recomendamos 10000 páginas, para 3.0 SuperServer - 50000 páginas, para Classic e SuperClassic - de 256 a 2048 páginas. No entanto, não defina o valor do cache de páginas de buffer muito alto - a sincronização do cache tem seu custo, e a ideia de colocar todo o banco de dados na RAM ajustando esse valor não funcionará. Use arquivos de configuração do Firebird pré-otimizados aqui: /br/optimized-firebird-configuration/
10. Aumente o tamanho da memória para operações de classificação
Aumente o valor do parâmetro TempCacheLimit no firebird.conf - ele especifica o tamanho do cache do espaço temporário para classificação. Os valores padrão são muito baixos (8Mb para Classic e 64Mb para SuperServer), use pelo menos 64Mb para Classic e 1Gb para SuperServer e SuperClassic. Novamente, use os arquivos de configuração otimizados do item #9.
11. Defina Forced Writes como OFF (com cautela!)
Se você tem atividade intensiva de inserção ou atualização (você pode verificar com o HQbird MonLogger, para detalhes veja a página 60 do Guia do Usuário do HQbird), e se você tem UPS e replicação instalados para proteger contra falhas de hardware, considere definir as configurações de Forced Writes como OFF, isso pode aumentar a velocidade das operações de gravação em até 3 vezes.
12. Aumente o número de slots de hash para Classic/SuperClassic
Aumente o valor do parâmetro LockHashSlots para Classic e SuperClassic do padrão 1009 para algum número primo grande (30011, por exemplo), isso diminuirá as filas no mecanismo interno de bloqueio.
13. Use Afinidade de CPU para Super Server 2.5
Se você usa SuperServer 2.5, defina o parâmetro CPUAffinity com um valor igual ao número de bancos de dados em uso: o SuperServer no 2.5 pode usar diferentes núcleos de CPU para processar solicitações para determinados bancos de dados.
14. Use uma unidade rápida para o espaço temporário
Defina a primeira parte do parâmetro TempDirectory no firebird.conf para um disco rápido - SSD ou unidade RAM. Isso diminuirá o tempo de grandes classificações - por exemplo, quando o banco de dados está sendo restaurado.
15. Armazene os backups do banco de dados em outra unidade
Armazene os backups do banco de dados em uma unidade física dedicada (RAID). Isso separará a E/S de leitura e gravação durante o backup, aumentará a velocidade do backup e diminuirá a carga na unidade principal. Isso é especialmente importante quando os backups são feitos enquanto os usuários estão trabalhando com o banco de dados. Mais detalhes sobre configuração de hardware para Firebird podem ser encontrados no " Guia de Hardware do Firebird".
16. Desative índices para inserções em massa
Se você insere ou atualiza muitos registros (mais de 25% da tabela), desative os índices da tabela onde os registros estão sendo inseridos e reative-os após a inserção ou atualização. A operação de reconstrução do índice pode ser mais rápida do que muitas atualizações do índice.
17. Use Tabelas Temporárias Globais para inserções rápidas
Para acelerar inserções e atualizações, use Tabelas Temporárias Globais para inserções em massa de grandes conjuntos de registros, e depois transfira os registros para a tabela permanente. Pode ser muito eficaz inserir registros na GTT, pré-processá-los e depois movê-los para a tabela persistente.
18. Evite índices desnecessários
Use menos índices para tabelas com inserções e atualizações intensivas. Cada índice adiciona uma sobrecarga significativa para operações de inserção, atualização, exclusão e coleta de lixo - pode haver 3-4 leituras e gravações de páginas adicionais quando um único registro está sendo inserido/atualizado/excluído/limpo para cada índice.
19. Substitua UDFs por chamadas de funções embutidas
Substitua chamadas de UDF por chamadas de funções embutidas. Muitas funções embutidas foram adicionadas nas versões recentes do Firebird, que oferecem funcionalidade anteriormente disponível apenas em bibliotecas UDF. Substitua tais funções quando possível, pois funções embutidas funcionam até 3 vezes mais rápido que UDFs.
20. Use transações somente leitura para operações de leitura
Use transações somente leitura para operações que não alteram registros (ou seja, SELECTs) com modo de isolamento = read committed. Tais transações não retêm versões de registros da coleta de lixo, e podem ser executadas indefinidamente: elas não afetam o desempenho do banco de dados.
21. Use transações de gravação curtas e elimine TODAS as de longa duração
Use transações de gravação curtas (para operações INSERT/UPDATE/DELETE).
Quanto mais curta a transação de gravação, melhor. As transações curtas retêm proporcionalmente menos versões de registros da coleta de lixo do que as de longa duração. Infelizmente, até mesmo uma única transação de longa duração (deixada aberta por uma ferramenta de desenvolvimento, por exemplo) pode prejudicar o bom efeito de todas as outras transações de gravação curtas. É por isso que você precisa monitorar transações de longa duração e corrigir os locais apropriados no código-fonte. Use a ferramenta HQbird DataGuard para receber alertas sobre a transação ativa mais antiga no banco de dados Firebird (quais aplicativos a iniciaram, qual endereço IP, o timestamp de seu início), e a ferramenta HQbird MonLogger para ver a lista completa das transações ativas de longa duração e suas estatísticas de E/S. Além disso, se você estiver usando componentes/bibliotecas de acesso a banco de dados que podem armazenar em cache conjuntos de registros, use atualizações em cache.
22. Evite cadeias de registros longas
Evite situações em que um registro tem muitas versões - o Firebird trabalha muito mais lentamente com cadeias de registros longas. (para ver quantas versões de registros algumas tabelas têm, e qual é a cadeia de registros mais longa, você pode usar a ferramenta HQbird IBAnalyst, aba Tables, classifique em “Max Version”). Use a combinação de inserções e exclusão programada de registros antigos em vez de múltiplas atualizações do mesmo registro.
23. Use PREPARE corretamente
Use instruções preparadas para executar consultas SQL onde apenas os parâmetros são alterados - por exemplo, faça o prepare antes do loop de tais consultas. O prepare pode levar um tempo significativo (especialmente para tabelas grandes), e preparar a consulta apenas uma vez aumentará muito o desempenho geral.
24. Não faça COMMIT com muita frequência durante operações de inserção/atualização em massa
No caso de operação em massa de INSERT/UPDATE/DELETE, não faça commit da transação após cada alteração (isso pode acontecer se você estiver usando a opção auto commit no seu driver de banco de dados) - faça commit das transações pelo menos após 1000 operações ou mais. Cada commit de transação executa várias operações de E/S de leitura/gravação contra o banco de dados, por isso commits frequentes diminuem o desempenho do banco de dados.
25. “Desligue” índices se você estiver usando IN com muitas constantes
Se você está usando a construção WHERE fieldX IN (Constant1, Constant2,… ConstantN), e há um índice em fieldX, o Firebird usará o índice tantas vezes quantas constantes houver na lista IN. Desative a busca por índice transformando fieldX em expressão +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), ou, para strings, use fieldX||''
26. Substitua IN por JOIN
Evite usar consultas com WHERE IN aninhado (SELECT… WHERE IN (SELECT.. WHERE IN() )), isso pode confundir o otimizador do Firebird. Transforme INs aninhados em joins.
27. Use LEFT JOIN da maneira correta
Se você está usando LEFT OUTER joins, coloque explicitamente as tabelas no join da menor para a maior.
28. Limite a busca de consultas SELECT
Sempre tente limitar a grande saída de consultas SELECT com cláusulas FIRST… SKIP ou ROWS. Se a consulta não for projetada especificamente como um relatório (que requer todos os registros para serem impressos/exportados), geralmente é suficiente mostrar os 10-100 primeiros registros. Busque apenas os registros necessários.
29. Especifique menos colunas em SELECT com ORDER BY/GROUP BY
Reduza o número de colunas e sua largura total em consultas com ORDER BY/GROUP BY tanto na parte SELECT (ou seja, campos a serem exibidos) quanto na cláusula ORDER BY. O Firebird mescla colunas de SELECT e cláusulas ORDER BY/GROUP BY e as classifica em memória (ou, se a memória não for suficiente, no disco). Então, se houver um VARCHAR longo no SELECT, o tamanho dos arquivos de classificação pode ser realmente grande (muitos gigabytes). Reduzir o número de campos apenas para aqueles que devem ser classificados e fazer um join tardio com os campos grandes a serem exibidos pode aumentar muito (x3-x10) a velocidade de uma consulta com ORDER BY/GROUP BY.
30. Use tabelas derivadas para otimizar SELECT com ORDER BY/GROUP BY
Outra maneira de otimizar consultas SQL com classificação é usar tabelas derivadas para evitar operações de classificação desnecessárias. Em vez de
SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2use a seguinte modificação:
SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_NFROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2JOIN T ON T.FIELD_KEY = T2.FIELD_KEY31. Armazene strings curtas em VARCHAR, grandes em BLOBs
Para armazenar dados de caracteres curtos, use VARCHARs; para armazenar textos longos, use BLOBs. Varchars são mais rápidos para pequenos pedaços de dados porque são armazenados no registro, e o registro inteiro é lido durante o mesmo ciclo de E/S, e se o tamanho do registro for menor que 2/3 do tamanho da página do banco de dados, o registro inteiro é armazenado na mesma página do banco de dados. BLOBs são armazenados fora do registro e exigem uma rodada adicional de E/S para lê-los, e eles mostram vantagem na leitura e gravação de strings longas.
32. Exclua colunas BLOB de SELECTs grandes
Exclua colunas BLOB de SELECTs grandes. Use uma espécie de ligação tardia com sub-consultas para mostrar seletivamente informações de BLOBs (por exemplo, mostrar o conteúdo do documento).
33. Use BIGINT para chaves primárias e únicas
Use o tipo BIGINT para chaves primárias e únicas auto-incrementadas e para identificadores de todos os tipos. Operações com BIGINT são as mais rápidas, e BIGINT tem capacidade suficiente para armazenar quase todas as faixas de dados.
34. Não use VARCHARs para chaves
Não use VARCHAR para identificadores, a menos que seja realmente necessário - operações com eles são muito menos eficientes do que com colunas inteiras. Evite especialmente GUIDs como identificadores - devido à distribuição aleatória dos valores GUID, operações INSERT/UPDATE com chaves Primary/Unique GUID podem ser até 20 vezes mais lentas do que com inteiros.
35. Recalcule as estatísticas dos índices
Recalcule as estatísticas dos índices regularmente. Atualize as estatísticas dos índices para tabelas com alterações frequentes ou massivas usando o comando SET STATISTICS; isso permite que o otimizador do Firebird escolha melhores planos SQL. O HQbird Firebird DataGuard pode realizar esse recálculo de estatísticas de índices automaticamente de acordo com a programação desejada (geralmente uma vez por semana).
36. Use pool de conexões
Se as conexões com o banco de dados Firebird forem curtas (típico em sites), use pool de conexões - por exemplo, em PHP use a função ibase_pconnect em vez de ibase_connect.
37. Use a opção LINGER no Firebird 3.0
Se as conexões com o banco de dados forem curtas e você estiver usando Firebird 3+, use a opção LINGER para manter o cache ativo durante o período especificado; isso manterá as páginas frequentemente usadas no cache, mesmo que não haja outras conexões. Por exemplo, ALTER DATABASE SET LINGER TO 60 manterá o cache por 60 segundos após o término da última conexão.
38. Use HASH JOINs
No Firebird 3.0, ao unir tabelas grandes e pequenas, o HASH JOIN pode ser muito mais rápido do que o join normal que usa «nested loop» com índice. Para fazer o otimizador do Firebird usar HASH join, use +0 na condição do join: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Verifique o resultado da otimização antes de colocá-lo em produção!
39. Marque funções PSQL apropriadas como DETERMINISTIC
Marque suas funções PSQL (no Firebird 3+) que não possuem parâmetros e retornam valores constantes com a palavra-chave DETERMINISTIC. As funções determinísticas são calculadas e armazenadas em cache no escopo da consulta atual.
40. Use funções analíticas (janela) no Firebird 3.0
Se você estiver executando SELECT com saída simultânea de alguma coluna e função agregada para ela, use funções de janela (analíticas) - é mais rápido do que subconsulta ou 2 consultas. Por exemplo:
Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employeesubstitua por
Select id, department, salary, salary / sum(salary) OVER () percentage from employee41. Use a opção -se para gbak
Use a opção -se para aumentar a velocidade de backup e/ou restauração do gbak em até 20%, por exemplo
gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk42. WHERE CURRENT OF
A maneira mais rápida de processar registros buscados pelo cursor em PSQL é a cláusula ‘where current of <>’. É mais rápida do que ‘where rb$db_key = :v_db_key’ e muito mais rápida do que a busca com chave primária ou única.
43. Evite consultas frequentes às tabelas de monitoramento
Não execute consultas às tabelas de monitoramento do Firebird (MON$) com muita frequência - tais consultas consomem recursos significativos e podem diminuir muito o desempenho da lógica de negócios principal. Recomendamos executar consultas MON$ não mais do que uma vez por minuto. Para monitoramento contínuo de consultas/transações/conexões do Firebird, use a ferramenta HQbird PerfMon que suporta Trace API (veja a página 66 do HQbird User Guide para detalhes).
44. Use a opção NO_AUTO_UNDO para inserções/atualizações em massa
Se você estiver executando muitos comandos DML (Update/Insert/Delete) no âmbito da mesma transação, o Firebird mescla o undo-log de cada comando com o undo-log da transação. Para acelerar operações DML em massa, inicie a transação com a opção «NO AUTO UNDO», para não mesclar os undo-logs de cada comando com o undo-log da transação.
45. Não use autenticação SRP no Firebird 3 se você não precisar
Não use autenticação de usuários SRP (Firebird 3.0+) se você realmente não precisar - a conexão com autenticação SRP é estabelecida mais lentamente do que a conexão regular.
Em vez de resumo
A otimização de desempenho exige considerar múltiplos fatores e pode ser realmente complicada. Se você tentou todas as coisas acima, considere contratar um serviço profissional de otimização de desempenho de banco de dados.
Fale conosco
Tem alguma dúvida? Não hesite em nos contatar por e-mail!