45 maneiras de acelerar o FB

Prévia do material em texto

45 maneiras de acelerar o FB
Data da última atualização: 23/05/2016
A seguir temos uma lista de dicas de desempenho para o banco de dados Firebird, em diferentes áreas ­ desde hardware/sistema operacional, tuning da
configuração do Firebird até recomendações de otimização de SQL. Esta lista não é uma referência completa de como otimizar o Firebird, e assume que você
tenha noções básicas do funcionamento do SGBD, como planos de execução, gerenciamento de transações e estatísticas de desempenho das consultas.
Aplique essas dicas com cautela e verifique o efeito antes de coloca­las em produção.
A FireBase é parceira da IBSurgeon no Brasil, e oferece suporte na otimização de bancos de dados e recuperação de bases de dados corrompidas.  
1. Colocar o banco de dados em um SSD
Deixe seu banco de dados em um drive SSD. O drive SSD fornece I/O randômica muito mais rápida do que os drives tradicionais. A I/O randômica é fator
crítico ao ler/gravar dados distribuídos em grandes bases de dados ­ a maioria das operações de bancos de dados fazem uso intensivo de I/O randômica.
2. Use RAID 10
Se você usa RAID1 ou RAID5, considere migrar para RAID10 – geralmente é 15­25% mais rápida.
3. Verifique a BBU
Se estiver usando um controlador RAID, verifique se ele tem uma Unidade de Bateria de Backup (BBU) instalada e operacional – alguns fornecedores não
fornecem BBU por padrão, ou enviam a bateria desconectada (para evitar descarga). Sem BBU, o controlador desabilita o cache de escrita, e a RAID
funciona de forma lenta, mais lenta do que os habituais drives SATA. Geralmente, pode­se verificar o status da BBU na ferramenta de configuração de
RAID.
4. Definir o cache de gravação para write­back
Se você estiver usando um controlador RAID com BBU (e o servidor tiver no­break), verifique se o cache está definido como write­back (não write­through).
«O write­back» permite cache de gravação.
5. Habilitar cache de leitura
Ao usar um controlador RAID, certifique­se de que o cache de leitura está habilitado.
6. Verifique o subsistema de disco
Verifique os discos a procura de blocos defeituosos e outros problemas de hardware (incluindo superaquecimento). Problemas de hardware podem diminuir
significativamente o desempenho de I/O, e levar a corrupções de banco de dados.
7. Use o SuperClassic ou Classic com o Firebird 2.5
Se você usar o Firebird 2.5 SuperServer com muitas conexões, experimente trocar para o SuperClassic ou Classic, pois eles escalam melhor usando todos os
núcleos da máquina.
8. Use o SuperServer com o Firebird 3
Se estiver usando o Firebird 2.5 Classic ou SuperClassic, considere migrar para o Firebird 3 SuperServer, pois ele pode usar vários núcleos da máquna, com
a vantagem do cache compartilhado.
9. Aumentar o cache de páginas
Aumente o tamanho do cache páginas (parâmetro DefaultDBCachePages) em relação ao valor padrão. Para o SuperServer, no Firebird 2.5, recomendamos
10.000 páginas, para o Firebird 3, no SuperServer – 50.000 páginas, para o Classic/SuperClassic – de 256 a 2.048 páginas. No entanto, não defina buffers
muito altos pois a sincronização de cache tem seu custo. Uma possível idéia de carregar todo o banco de dados na memória RAM, alocando um buffer alto,
não vai funcionar como esperado. Acesse os arquivos de configuração do Firebird pré­otimizados aqui: ib­aid.com/en/optimized­firebird­configuration/
10. Aumentar o tamanho da memória para operações de ordenação
Aumente o valor do parâmetro TempCacheLimit no firebird.conf – ele especifica o tamanho do cache temporário para ordenação. Os valores padrão são
demasiadamente baixos (8Mb para Classic e 64Mb para SuperServer). Use pelo menos 64Mb para Classic e 1Gb para o SuperServer e SuperClassic.
Novamente, use os arquivos de configuração otimizados (item #9).
11. Desligue o forced writes (com cuidado!)
Se você faz inserções ou atualizações de dados de forma intensiva (você pode verificar isso com HQbird MonLogger, para detalhes, consulte a página 60
do Manual de utilizador do HQbird), e tiver no­break e replicação instalada de forma a proteger contra as falhas de hardware, considere desligar o forced
writes, aumentando a velocidade das operações de gravação em até 3 vezes.
12. Aumente o número de slots no Classic/SuperClassic
Aumente o valor do parâmetro LockHashSlots para o CS e SC, do padrão 1009, para algum número primo maior (30011, por exemplo), diminuindo as filas
do mecanismo interno de controle de travas.
13. Use o CPUAffinity para o SuperServer no FB 2.5
Se você usa o SuperServer 2.5, defina o parâmetro CPUAffinity para utilizar tantos cores quanto for o 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 bases de dados diferentes.
14. Use um drive rápido para arquivos temporários
Definir a primeira parte do parâmetro TempDirectory no firebird.conf para o disco mais rápido disponível – SSD ou RAM. Isso diminuirá o tempo de grandes
ordenações – por exemplo, quando um backup está sendo restaurado.
15. Armazenar os backups de banco de dados em outra unidade
Armazene os backups de bases de dados em uma unidade física dedicada (RAID). Isso irá separar a leitura/escrita durante o backup, agilizando o processo e
diminuindo a carga na unidade principal. É especialmente importante quando são realizados backups enquanto há usuários trabalhando nos bancos de
dados. Mais detalhes sobre configuração de hardware podem ser encontrados no "Guia do Hardware Firebird".
16. Desativar os índices para inserções em massa
Se você inserir ou atualizar mais de 25% dos registros de uma tabela, desative os índices para a tabela onde os registros são inseridos, e reative­os depois
da inserção/atualização. A operação de reconstrução de índices pode ser mais rápida do que atualiza­los constantemente.
17. Use tabelas temporárias globais para inserções rápidas
Para acelerar as inserções e atualizações, use tabelas temporárias globais (GTT) para inserção em massa de grande volume de registros, transferindo depois
para a tabela permanente. Isso pode ser muito eficaz quando há necessidade de processar os dados antes do armazenamento definitivo na tabela
persistente.
18. Evite índices desnecessários
Defina a menor quantidade possível de índices nas tabelas que sofrem inserções/atualizações intensivas. Cada índice adiciona uma sobrecarga significativa
nos inserts, updates, e deletes e nas operações de coleta de lixo – pode haver de 3 a 4 leituras/gravações adicionais, quando um registro está sendo
inserido/atualizado/excluído/limpo para cada índice.
19. Substituir UDFs por funções internas
Muitas funções internas foram adicionadas nas versões mais recentes do Firebird, e oferecem funcionalidades anteriormente disponíveis apenas em
bibliotecas UDF. Substitua, sempre que possível, as UDFs por funções internas, pois geralmente rodam até 3 vezes mais rápido que as UDFs.
20. Usar transações readonly para operações de leitura
Usar transações readonly para operações que não alteram registros (ou seja, selects) com o modo de isolamento ReadCommited. Tais operações não
bloqueiam a coleta de lixo, e podem ficar abertas indefinidamente sem afetar o desempenho do banco de dados.
21. Use transações de gravação curtas
Use transações de gravações curtas (para operações INSERT/UPDATE/DELETE). Quanto mais curta, melhor. Transações curtas bloqueiam
proporcionalmente menos versões de registros, impedindo a coleta de lixo. Infelizmente, até mesmo uma única transação de longa duração (deixada
aberta, por exemplo, pela ferramenta de desenvolvimento) pode estragar todo o efeito benéfico das outras transações de curta duração. Monitore as
transações de longa duração e corrija 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 iniciou, o endereçoIP, data/hora do seu início) e o HQbird MonLogger para ver a lista
completa de tranções longas e suas estatísticas de I/O. Além disso, verifique se seu componente de acesso permite o uso de cached updates.
22. Evitar cadeias longas de registros
Evite situações quando um registro tem muitas versões – o Firebird funciona muito mais lento com cadeias de registros muito longas. Para ver quantas
versões de registros estão presentes em algumas tabelas, e qual é a maior cadeia de registros, use o HQbird IBAnalyst, na guia Tabelas, e ordene pela
coluna "Max version". Pense em usar uma combinação de inserts e deletes agendados dos registros antigos, ao invés de várias atualizações de um mesmo
registro.
23. Prepare os comandos corretamente
Use instruções preparadas para executar consultas SQL onde apenas o valor dos parâmetros são alterados – por exemplo, certifique­se de preparar a query
antes do loop de tais consultas. A preparação pode levar um tempo significativo (especialmente para grandes tabelas), e preparando a consulta uma única
vez, aumentará significativamente o desempenho geral.
24. Não dê commits constantes durante operações de inserção/atualização em massa
No caso de uma grande operação de INSERT/UPDATE/DELETE, não fique commitando a transação após cada operação (comum se você estiver usando a
opção de autocommit em seu componente de acesso) ­ faça o commit a cada 1.000 ou mais operações. Cada commit da transação executa várias
leituras/gravações no banco de dados, diminuindo o desempenho.
25. "Desligue" o uso de índices quando estiver usando IN com várias constantes
Se estiver usando a construção WHERE fieldX IN (Constant1, Constant2,… ConstantN), e houver um índice definido para o fieldX, o Firebird usará o índice
quantas vezes for o número de constantes na cláusula IN. Desative o uso do índice transformando fieldX em uma expressão do tipo WHERE fieldX+0 IN
(Constant1, Constant2,… ConstantN), ou, se o campo for do tipo string, use fieldX || ''.
26. Substitua IN por JOIN
Evite usar queries com IN aninhados, por exemplo WHERE IN(SELECT... WHERE IN (SELECT.. WHERE IN() )), pois pode confundir o otimizador. Transforme
os aninhamentos em JOINs.
27. Use LEFT JOIN corretamente
Se estiver usando LEFT OUTER joins, liste os joins na sequência da menor tabela para a maior.
28. Limite o resultado dos SELECTs
Procure limitar o número de registros retornados por um SELECT usando FIRST… SKIP ou ROWS. Se a query não é usada para relatórios (o que geralmente
requer recuperar todo o resultado), geralmente é suficiente retornar os 10­100 primeiros registros. Recupere apenas os registros necessários.
29. Especifique o menor número de colunas no SELECTs com ORDER BY / GROUP BY
Reduza o número de colunas em consultas com ORDER BY / GROUP, tanto na parte do SELECT (isto é, campos que serão visualizados) como na cláusula
ORDER BY. O Firebird mescla colunas do SELECT e do ORDER BY/GROUP BY e as ordena em memória (ou, se não houver memória suficiente, em disco).
Portanto, se houver um VARCHAR muito grande no SELECT, o tamanho do arquivo de sort pode ficar muito grande (vários gigabytes). Reduzindo o número
de campos que serão ordenados, e juntando­os (join) posteriormente com os campos maiores pode ser até 10x mais rápido.
30. Use tabelas derivadas para otimizar SELECTs com ORDER BY / GROUP BY
Outra maneira de otimizar a consulta 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_N  FROM T  ORDER BY FIELD2
Use o seguinte:
SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N  FROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2  JOIN T ON T.FIELD_KEY = T2.FIELD_KEY
31. Use VARCHAR para strings pequenos e BLOBs para os grandes
Para armazenar strings curtas, use VARCHARs. Para armazenar textos longos, use BLOBs. Varchars são mais rápidos para textos curtos pois eles ficam
armazenados dentro do registro, e o registro inteiro é lido em um único ciclo de I/O, e se o tamanho do registro for inferior a 2/3 do tamanho da página do
banco de dados, o registro todo é armazenado na mesma página. BLOBs são armazenados fora do registro, e exigem uma rodada adicional de I/O para lê­
los, mas mostram vantagens na leitura/gravação de strings longos.
32. Exclua os campos BLOBs de grandes SELECTs
Exclua colunas BLOB de SELECTs grandes. Recupere o conteúdo do blob posteriormente, somente quando for necessário (por exemplo, o usuário quer
recuperar um documento específico que está armazenada em um blob).
33. Use BIGINT para chaves primárias/únicas
Use o tipo BIGINT para chaves primárias, auto­incrementos e identificadores em geral. Operações com BIGINT são as mais rápidas, e o BIGINT tem
capacidade suficiente para armazenar praticamente todos os intervalos de dados.
34. Não utilize VARCHARs para chaves
Não use VARCHAR para identificadores, a menos que seja realmente necessário – operações com eles são menos eficazes do que com as colunas de tipo
inteiro. Evite especialmente o uso de GUIDs como identificadores – devido a distribuição aleatória dos valores GUID, operações de INSERT/UPDATE com
GUIDs em chaves primárias/únicas podem ser 20 vezes mais lentas do que com números inteiros.
35. Recalcule as estatísticas dos índices
Recalcule as estatísticas dos índices regularmente. Atualize as estatísticas dos índices nas tabelas atualizadas frequentemente através do comando SET
STATISTICS, permitindo ao otimizador do Firebird escolher os melhores planos SQL. O HQbird DataGuard pode executar tal recálculo das estatísticas de
índices automaticamente, no horário desejado (geralmente uma vez por semana).
36. Use pool de conexão
Se as conexões com o Firebird forem curtas (típico em servidores web), use pool de conexão – por exemplo, no PHP use a função ibase_pconnect em vez
da ibase_connect.
37. Use a opção LINGER no Firebird 3.0
Se conexões de banco de dados são curtas e você estiver usando o Firebird 3, use a opção LINGER para manter o cache ativo durante um período específico
de tempo, fazendo com que as páginas permaneçam no cache, mesmo não havendo outra conexão. 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, quando juntar tabelas grandes e pequenas, HASH joins podem ser muito mais rápidos do que um join tradicional, que usa «loops
aninhados» com índices. Para fazer com que o otimizador do Firebird use HASH joins, use + 0 na condição de junção:  T1 JOIN T2 ON T1.FIELD1+0 =
T2.FIELD2+0. Teste o resultado da otimização antes de colocá­la em produção!
39. Marque funçoes específicas em PSQL como DETERMINISTIC
Marque suas funções PSQL (no Firebird 3) que não tenham parâmetros e retornem 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 (window functions) no Firebird 3.0
Se você estiver executando SELECTs que retornam dados de colunas e dados agregados, use as Window Functions – é mais rápido do que uma subconsulta
ou duas consultas. Por exemplo:
Select id, department, salary, salary / (select sum(salary) from employee) percentage  from employee
Substitua por
Select id, department, salary, salary / sum(salary) OVER () percentage from employee 
  
41. Use ­se com o gbak
Use o gbak com o parâmetro ­se para acelerar as operações de backup/restore em até 20%, por exemplo:
gbak ‐b ‐g ‐se service_mgr c:\db\data.fdb e:\backup\data.fbk
42. WHERE CURRENT OF
A maneira mais rápida de processar registros retornados por cursores PSQL é a cláusula WHERE CURRENT OF. É mais rápido do que where rb$db_key =
:v_db_key e muito mais rápido do que pesquisar com uma chave primária/única.
43. Evite consultar as tabelas de monitoramento frequentemente
Não execute consultasem tabelas de monitoramento (MON$) muitas vezes – tais consultas consomem recursos significativos e podem diminuir
consideravelmente o desempenho. Recomendamos não executar consultas nas tabelas MON$ em intervalos menores que 1 minuto. Para monitoramento
contínuo do Firebird, use o HQbird PerfMon que suporta a TraceAPI (ver página 66 do Manual do utilizador de HQbird para obter detalhes).
44. Use a opção NO_AUTO_UNDO para inserções/atualizações em massa
Se você estiver executando muitos comandos DML (Insert/Update/Delete)em uma mesma transação, o Firebird mescla o undo­log de cada operação com o
undo­log da transação. Para acelerar essas operações DML em massa, inicie a transação com a opção NO_AUTO_UNDO, a fim de não mesclar o undo­log de
cada operação com o da transação.
45. No Firebird 3, use autenticação SRP só quando necessário
Estabelecer uma conexão com autenticação SRP demora mais do que usando o método legado. No entanto, conexões SRP são mais seguras.
CONSIDERE QUE...
O maior impacto no desempenho do Firebird é causado por suas consultas SQL. Aprender a ler e analisar os planos de consulta é a chave para um melhor
desempenho. Você pode obter um curso online sobre otimização de consultas em ib­aid.com/firebird­training