45 Maneiras de Acelerar o Banco de Dados Firebird
Aqui você encontra a lista de dicas de desempenho para o banco de dados Firebird em diferentes áreas - desde hardware/SO e ajuste da 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ê entenda os fundamentos do funcionamento do Firebird, como planos de execução, gerenciamento de transações e estatísticas de desempenho de consultas.
Aplique estas dicas com cautela e verifique seu efeito antes de colocá-las 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 proporciona IO aleatório muito melhor do que as unidades tradicionais. O IO aleatório é crítico para leitura e gravação de dados distribuídos por um grande arquivo de banco de dados - a maioria das operações de banco de dados exige IO aleatório paralelo intensivo.
2. Use RAID 10
Se você usa RAID1 ou RAID5, considere RAID10 - é 15-25% mais rápido.
3. Verifique a BBU
Se você está usando um controlador RAID, verifique se ele tem uma Backup Battery Unit (BBU) instalada e operacional - alguns fornecedores não fornecem BBU por padrão. Sem BBU, o controlador desabilita 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 como write-back
Se você está usando um controlador RAID com BBU instalada (e servidor com UPS), verifique se seu cache está definido como write-back (não write-through). «Write-back» habilita o cache de gravação do controlador.
5. Habilite o cache de leitura
Se você usa controlador RAID, verifique se ele tem o cache de leitura habilitado.
6. Verifique o subsistema de disco
Verifique suas unidades em busca de blocos defeituosos e outros problemas de hardware (incluindo superaquecimento). Problemas de hardware podem diminuir significativamente o desempenho de IO e levar a corrupções do 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 a migração 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 page buffers
Aumente o tamanho do cache de page buffers (parâmetro DefaultDBCachePages) em relação aos 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 page buffers 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 ordenação
Aumente o valor do parâmetro TempCacheLimit em firebird.conf - ele especifica o tamanho do cache do espaço temporário para ordenaçã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 intensa de inserção ou atualização (você pode verificá-la com o HQbird MonLogger, para detalhes veja a página 60 do HQbird User Guide), e se você tem UPS e replicação instaladas para proteção contra falhas de hardware, considere definir a configuração 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 hash slots 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 CPU Affinity para Super Server 2.5
Se você usa SuperServer 2.5, defina o parâmetro CPUAffinity com o 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 requisições para determinados bancos de dados.
14. Use uma unidade rápida para espaço temporário
Defina a primeira parte do parâmetro TempDirectory em firebird.conf para o disco rápido - SSD ou unidade RAM. Isso diminuirá o tempo de grandes ordenações - por exemplo, quando o banco de dados está sendo restaurado.
15. Armazene backups do banco de dados em outra unidade
Armazene backups do banco de dados em uma unidade física dedicada (RAID). Isso separará o IO de leitura e gravação durante o backup, e 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 a configuração de hardware para Firebird podem ser encontrados no " Firebird Hardware Guide".
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 são 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 Global Temporary Tables para inserções rápidas
Para acelerar inserções e atualizações, use Global Temporary Tables 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 em 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 intensas. Cada índice adiciona sobrecarga significativa para operações de inserção, atualização, exclusão e coleta de lixo - pode haver 3-4 leituras e gravações adicionais de páginas quando um único registro é 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 funcionalidades anteriormente disponíveis apenas em bibliotecas UDF. Substitua tais funções onde 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 livre-se de 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 pela ferramenta de desenvolvimento, por exemplo) pode arruinar 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 aplicações 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 IO. 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 longas de registros
Evite situações em que um registro tenha muitas versões de registro - o Firebird funciona muito mais lentamente com cadeias longas de registros. (para ver quantas versões de registro algumas tabelas têm, e qual é a cadeia de registros mais longa, você pode usar a ferramenta HQbird IBAnalyst, aba Tables, ordenar por “Max Version”). Use a combinação de inserções e exclusão agendada 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 de INSERT/UPDATE/DELETE em massa, 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 driver do seu 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 IO de leitura/gravação no 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á um índice tantas vezes quantas constantes houver na lista IN. Desabilite 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 aninhados (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 as cláusulas FIRST… SKIP ou ROWS. Se a consulta não foi projetada especificamente como um relatório (que requer que todos os registros sejam impressos/exportados), geralmente é suficiente mostrar os top 10-100 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 mostrados) quanto na cláusula ORDER BY. O Firebird mescla as colunas das cláusulas SELECT e ORDER BY/GROUP BY e as ordena na memória (ou, se a memória não for suficiente, no disco). Assim, se houver um VARCHAR longo no SELECT, o tamanho dos arquivos de ordenação pode ser realmente grande (muitos gigabytes). Reduzir o número de campos apenas para aqueles que precisam ser ordenados e fazer o join tardio com campos grandes a serem mostrados 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 ordenação é usar tabelas derivadas para evitar operações de ordenaçã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, longas 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 IO, 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 IO para lê-los, e mostram vantagem na leitura e gravação de strings longas.
32. Exclua colunas BLOB dos grandes SELECTs
Exclua colunas BLOB dos grandes SELECTs. Use uma espécie de late binding com sub-selects 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 - as operações com eles são muito menos eficazes do que com colunas inteiras. Evite especialmente GUIDs como identificadores - devido à distribuição aleatória dos valores GUID, as operações INSERT/UPDATE com chaves primárias/únicas GUID podem ser 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 as tabelas com alterações frequentes ou massivas com o comando SET STATISTICS; isso permite que o otimizador do Firebird escolha planos SQL melhores. O HQbird Firebird DataGuard pode realizar esse recálculo das estatísticas dos í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 (é o típico para 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 de tempo especificado; isso manterá as páginas usadas com frequência 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, no caso de junção de tabelas grandes e pequenas, o HASH JOIN pode ser muito mais rápido do que a junção normal que usa «nested loop» com índice. Para fazer o otimizador do Firebird usar HASH join, use +0 na condição de junção: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Verifique o resultado da otimização antes de colocá-la em produção!
39. Marque as funções PSQL apropriadas como DETERMINISTIC
Marque suas funções PSQL (no Firebird 3+) que não têm 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 (de janela) no Firebird 3.0
Se você estiver executando um 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 a 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 restore 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 uma 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 com mais frequência 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 a Trace API (consulte 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», a fim de 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 dela
Não use a autenticação de usuários SRP (Firebird 3.0+) se você realmente não precisar dela - 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
Você tem alguma dúvida? Não hesite em nos contatar por e-mail!