45 modi per velocizzare il database Firebird

Qui puoi trovare l’elenco dei suggerimenti per le prestazioni del database Firebird in diversi ambiti - dall’hardware/OS e la configurazione di Firebird alle raccomandazioni per l’ottimizzazione SQL. Questo elenco non è il riferimento completo su come ottimizzare Firebird e presuppone che tu comprenda le basi del funzionamento di Firebird, come i piani di esecuzione, la gestione delle transazioni e le statistiche sulle prestazioni delle query.

Ti preghiamo di applicare questi suggerimenti con cautela e di verificarne l’effetto prima di metterli in produzione.

La nostra azienda (IBSurgeon) offre il servizio completo di ottimizzazione delle prestazioni del database.

1. Metti il database su SSD

Metti il tuo database su SSD. Le unità SSD forniscono un I/O casuale molto migliore rispetto alle unità tradizionali. L’I/O casuale è fondamentale per leggere e scrivere dati distribuiti in un file di database di grandi dimensioni - la maggior parte delle operazioni del database richiede un intenso I/O casuale parallelo.

2. Usa RAID 10

Se usi RAID1 o RAID5, considera RAID10 - è più veloce del 15-25%.

3. Controlla la BBU

Se usi un controller RAID, verifica che abbia una Backup Battery Unit (BBU) installata e funzionante - alcuni fornitori non forniscono la BBU di default. Senza BBU, il controller disabilita la cache e il RAID funziona molto lentamente, anche più lentamente delle normali unità SATA. Di solito, puoi controllare lo stato della BBU nello strumento di configurazione RAID.

4. Imposta la cache di scrittura su write-back

Se usi un controller RAID con BBU installata (e server con UPS), verifica che la sua cache sia impostata su write-back (non write-through). «Write-back» abilita la cache di scrittura del controller.

5. Abilita la cache di lettura

Se usi un controller RAID, verifica che abbia la cache di lettura abilitata.

6. Controlla il sottosistema disco

Controlla le tue unità per blocchi danneggiati e altri problemi hardware (incluso il surriscaldamento). I problemi hardware possono diminuire significativamente le prestazioni I/O e portare a corruzioni del database.

7. Usa SuperClassic o Classic in Firebird 2.5

Se usi Firebird 2.5 SuperServer con molte connessioni, prova a usare SuperClassic o Classic, possono scalare meglio utilizzando tutti i core della CPU.

8. Usa SuperServer 3.0 in Firebird 3.

Se usi Classic o SuperClassic in 2.5, considera la migrazione a Firebird 3.0 SuperServer, ora può usare più core e combinarli con i vantaggi della cache condivisa.

9. Aumenta la cache dei page buffers

Aumenta la dimensione della cache dei page buffers (parametro DefaultDBCachePages) dai valori predefiniti. Per 2.5 SuperServer raccomandiamo 10000 pagine, per 3.0 SuperServer - 50000 pagine, per Classic e SuperClassic - da 256 a 2048 pagine. Tuttavia, non impostare il valore della cache dei page buffers troppo alto - la sincronizzazione della cache ha un costo, e l’idea di mettere tutto il database in RAM regolando questo valore non funzionerà. Usa i file di configurazione Firebird pre-ottimizzati qui: /it/optimized-firebird-configuration/

10. Aumenta la memoria per le operazioni di ordinamento

Aumenta il valore del parametro TempCacheLimit in firebird.conf - specifica la dimensione della cache dello spazio temporaneo per l’ordinamento. I valori predefiniti sono troppo bassi (8Mb per Classic e 64Mb per SuperServer), usa almeno 64Mb per Classic e 1Gb per SuperServer e SuperClassic. Di nuovo, usa i file di configurazione ottimizzati dal punto #9.

11. Imposta Forced Writes Off (con cautela!)

Se hai un’attività intensiva di inserimento o aggiornamento (puoi verificarlo con HQbird MonLogger, per i dettagli vedi pagina 60 della Guida Utente HQbird), e se hai UPS e replica installati per proteggerti da guasti hardware, considera di impostare le impostazioni Forced Writes su OFF, può aumentare la velocità delle operazioni di scrittura fino a 3 volte.

12. Aumenta il numero di hash slots per Classic/SuperClassic

Aumenta il valore del parametro LockHashSlots per Classic e SuperClassic dal default 1009 a un numero primo grande (30011, per esempio), diminuirà le code nel meccanismo di blocco interno.

13. Usa CPU Affinity per Super Server 2.5

Se usi SuperServer 2.5, imposta il parametro CPUAffinity a un valore uguale al numero di database in uso: SuperServer in 2.5 può usare diversi core della CPU per elaborare le richieste per determinati database.

14. Usa un’unità veloce per lo spazio temporaneo

Imposta la prima parte del parametro TempDirectory in firebird.conf su un disco veloce - SSD o RAM drive. Diminuirà il tempo dei grandi ordinamenti - per esempio quando il database viene ripristinato.

15. Conserva i backup del database su un’altra unità

Conserva i backup del database su un’unità fisica dedicata (RAID). Separerà l’I/O di lettura e scrittura durante il backup, aumenterà la velocità di backup e diminuirà il carico sull’unità principale. È particolarmente importante quando i backup vengono eseguiti mentre gli utenti lavorano con il database. Maggiori dettagli sulla configurazione hardware per Firebird si trovano nella " Guida Hardware Firebird".

16. Disattiva gli indici per inserimenti di massa

Se inserisci o aggiorni molti record (più del 25% della tabella), disattiva gli indici per la tabella in cui vengono inseriti i record e riattivali dopo l’inserimento o l’aggiornamento. L’operazione di ricostruzione dell’indice può essere più veloce di molti aggiornamenti dell’indice.

17. Usa le Global Temporary Tables per inserimenti rapidi

Per velocizzare inserimenti e aggiornamenti, usa le Global Temporary Tables per inserimenti di massa di grandi recordset, e poi trasferisci i record nella tabella permanente. Può essere molto efficace inserire record in GTT, pre-elaborarli e poi spostarli nella tabella persistente.

18. Evita indici non necessari

Usa meno indici per le tabelle con inserimenti e aggiornamenti intensivi. Ogni indice aggiunge un overhead significativo per le operazioni di inserimento, aggiornamento, eliminazione e garbage collection - potrebbero esserci 3-4 letture e scritture di pagine aggiuntive quando un singolo record viene inserito/aggiornato/eliminato/pulito per ogni indice.

19. Sostituisci le UDF con chiamate a funzioni incorporate

Sostituisci le chiamate UDF con chiamate a funzioni incorporate. Molte funzioni incorporate sono state aggiunte nelle versioni recenti di Firebird, che offrono funzionalità precedentemente disponibili solo nelle librerie UDF. Sostituisci tali funzioni dove possibile, poiché le funzioni incorporate funzionano fino a 3 volte più velocemente delle UDF.

20. Usa transazioni di sola lettura per le operazioni di lettura

Usa transazioni di sola lettura per le operazioni che non modificano record (cioè SELECT) con modalità di isolamento = read committed. Tali transazioni non trattengono versioni di record dalla garbage collection e possono essere eseguite indefinitamente: non influiscono sulle prestazioni del database.

21. Usa transazioni di scrittura brevi e sbarazzati di TUTTE le transazioni a lunga esecuzione

Usa transazioni di scrittura brevi (per le operazioni INSERT/UPDATE/DELETE).

Più breve è la transazione di scrittura, meglio è. Le transazioni brevi trattengono proporzionalmente meno versioni di record dalla garbage collection rispetto a quelle a lunga esecuzione. Sfortunatamente, anche una singola transazione a lunga esecuzione (da uno strumento di sviluppo lasciato aperto, per esempio) può rovinare il buon effetto di tutte le altre transazioni di scrittura brevi. Ecco perché devi monitorare le transazioni a lunga esecuzione e correggere i punti appropriati nel codice sorgente. Usa lo strumento HQbird DataGuard per ricevere avvisi sulla transazione attiva più vecchia nel database Firebird (quali applicazioni l’hanno avviata, quale indirizzo IP, il timestamp del suo inizio), e lo strumento HQbird MonLogger per vedere l’elenco completo delle transazioni attive a lunga esecuzione e le loro statistiche I/O. Inoltre, se usi componenti/librerie di accesso al database che possono memorizzare nella cache i recordset, usa gli aggiornamenti memorizzati nella cache.

22. Evita lunghe catene di record

Evita situazioni in cui un record ha molte versioni di record - Firebird lavora molto più lentamente con lunghe catene di record. (per vedere quante versioni di record hanno alcune tabelle e qual è la catena di record più lunga puoi usare lo strumento HQbird IBAnalyst, scheda Tables, ordina su “Max Version”). Usa una combinazione di inserimenti e cancellazioni programmate di vecchi record invece di più aggiornamenti dello stesso record.

23. Usa PREPARE correttamente

Usa istruzioni preparate per eseguire query SQL dove cambiano solo i parametri - per esempio, fai prepare prima del ciclo di tali query. Prepare può richiedere tempo significativo (specialmente per tabelle grandi), e preparare la query solo una volta aumenterà notevolmente le prestazioni complessive.

24. Non fare COMMIT troppo spesso durante operazioni di inserimento/aggiornamento di massa

Nel caso di operazioni di massa INSERT/UPDATE/DELETE, non fare commit della transazione dopo ogni modifica (può accadere se stai usando l’opzione auto commit nel tuo driver di database) - fai commit della transazione almeno dopo 1000 operazioni o più. Ogni commit di transazione esegue diverse operazioni I/O di lettura/scrittura sul database, ecco perché i commit frequenti diminuiscono le prestazioni del database.

25. “Disattiva” gli indici se usi IN con molte costanti

Se usi la costruzione WHERE fieldX IN (Constant1, Constant2,… ConstantN), e c’è un indice su fieldX, Firebird userà l’indice tante volte quante sono le costanti nella lista IN. Disabilita la ricerca tramite indice trasformando fieldX in espressione +0: WHERE fieldX+0 IN (Constant1, Constant2,… ConstantN), o, per le stringhe, usa fieldX||''

26. Sostituisci IN con JOIN

Evita di usare query con WHERE IN annidato (SELECT… WHERE IN (SELECT.. WHERE IN() )), può confondere l’ottimizzatore di Firebird. Trasforma gli IN annidati in join.

27. Usa LEFT JOIN nel modo corretto

Se usi LEFT OUTER join, metti esplicitamente le tabelle nel join dalla più piccola alla più grande.

28. Limita il fetch delle query SELECT

Cerca sempre di limitare l’output grande per le query SELECT con clausole FIRST… SKIP o ROWS. Se la query non è progettata specificamente come report (che richiede tutti i record da stampare/esportare), di solito è sufficiente mostrare i primi 10-100 record. Recupera solo i record necessari.

29. Specifica meno colonne in SELECT con ORDER BY/GROUP BY

Riduci il numero di colonne e la loro larghezza complessiva nelle query con ORDER BY/GROUP BY sia nella parte SELECT (cioè i campi da mostrare) sia nella clausola ORDER BY. Firebird unisce le colonne da SELECT e le clausole ORDER BY/GROUP BY e le ordina in memoria (o, se la memoria non è sufficiente, su disco). Quindi, se c’è un VARCHAR lungo in SELECT, la dimensione dei file di ordinamento può essere davvero grande (molti gigabyte). Ridurre il numero di campi solo a quelli che devono essere ordinati e fare un join tardivo con i campi grandi da mostrare può aumentare notevolmente (x3-x10) la velocità di una query con ORDER BY/GROUP BY.

30. Usa tabelle derivate per ottimizzare SELECT con ORDER BY/GROUP BY

Un altro modo per ottimizzare una query SQL con ordinamento è usare tabelle derivate per evitare operazioni di ordinamento non necessarie. Invece di

SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_NFROM TORDER BY FIELD2

usa la seguente modifica:

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_KEY

31. Conserva stringhe corte in VARCHAR, grandi in BLOB

Per conservare dati carattere corti, usa VARCHAR, per conservare testi lunghi, usa BLOB. I VARCHAR sono più veloci per piccoli pezzi di dati perché sono memorizzati nel record, e l’intero record viene letto durante lo stesso ciclo I/O, e se la dimensione del record è inferiore a 2/3 della dimensione della pagina del database, l’intero record è memorizzato sulla stessa pagina del database. I BLOB sono memorizzati fuori dal record e richiedono un ulteriore ciclo I/O per leggerli, e mostrano il loro vantaggio con la lettura e scrittura di stringhe lunghe.

32. Escludi le colonne BLOB dalle SELECT grandi

Escludi le colonne BLOB dalle SELECT grandi. Usa una sorta di binding tardivo con sub-select per mostrare selettivamente informazioni dai BLOB (per esempio, mostra il contenuto del documento).

33. Usa BIGINT per chiavi primarie e uniche

Usa il tipo BIGINT per chiavi primarie e uniche auto-incrementali e per identificatori di tutti i tipi. Le operazioni con BIGINT sono le più veloci, e BIGINT ha capacità sufficiente per memorizzare quasi tutti gli intervalli di dati.

34. Non utilizzare VARCHAR per le chiavi

Non utilizzare VARCHAR per gli identificatori a meno che non sia realmente necessario - le operazioni con essi sono molto meno efficienti rispetto alle colonne intere. Evita in particolare i GUID come identificatori - a causa della distribuzione casuale dei valori GUID, le operazioni INSERT/UPDATE con chiavi primarie/univoche GUID possono essere 20 volte più lente rispetto agli interi.

35. Ricalcolare le statistiche degli indici

Ricalcola regolarmente le statistiche degli indici. Aggiorna le statistiche degli indici per le tabelle con modifiche frequenti o massive con il comando SET STATISTICS; questo consente all’ottimizzatore di Firebird di scegliere piani SQL migliori. HQbird Firebird DataGuard può eseguire automaticamente questo ricalcolo delle statistiche degli indici secondo la pianificazione desiderata (di solito una volta a settimana).

36. Utilizzare il connection pool

Se le connessioni al database Firebird sono brevi (tipico per i siti web), utilizza il connection pool - ad esempio, in PHP usa la funzione ibase_pconnect invece di ibase_connect.

37. Utilizzare l’opzione LINGER in Firebird 3.0

Se le connessioni al database sono brevi e stai usando Firebird 3+, utilizza l’opzione LINGER per mantenere attiva la cache per un periodo di tempo specificato; questo manterrà le pagine utilizzate di frequente nella cache anche se non ci saranno altre connessioni. Ad esempio, ALTER DATABASE SET LINGER TO 60 manterrà la cache per 60 secondi dopo la fine dell’ultima connessione.

38. Utilizzare HASH JOIN

In Firebird 3.0, nel caso di join tra tabelle grandi e piccole, HASH JOIN può essere molto più veloce del join normale che utilizza «nested loop» con indice. Per far sì che l’ottimizzatore di Firebird utilizzi HASH join, usa +0 nella condizione di join: T1 JOIN T2 ON T1.FIELD1+0 = T2.FIELD2+0. Verifica il risultato dell’ottimizzazione prima di metterlo in produzione!

39. Marcare le funzioni PSQL appropriate come DETERMINISTIC

Marca le tue funzioni PSQL (in Firebird 3+) che non hanno parametri e restituiscono valori costanti con la parola chiave DETERMINISTIC. Le funzioni deterministiche vengono calcolate e memorizzate nella cache nell’ambito della query corrente.

40. Utilizzare le funzioni analitiche (window) in Firebird 3.0

Se stai eseguendo una SELECT con output simultaneo di una colonna e della sua funzione aggregata, usa le funzioni window (analitiche) - è più veloce rispetto alla subquery o a 2 query. Ad esempio:

Select id, department, salary, salary / (select sum(salary) from employee) percentagefrom employee

sostituisci con

Select id, department, salary, salary / sum(salary) OVER () percentage from employee

41. Utilizzare l’opzione -se per gbak

Usa l’opzione -se per aumentare la velocità di backup e/o ripristino di gbak fino al 20%, ad esempio

gbak -b -g -se service_mgr c:\db\data.fdb e:\backup\data.fbk

42. WHERE CURRENT OF

Il modo più veloce per elaborare i record recuperati dal cursore in PSQL è la clausola ‘where current of <>’. È più veloce di ‘where rb$db_key = :v_db_key’ e molto più veloce della ricerca con una chiave primaria o univoca.

43. Evitare query frequenti alle tabelle di monitoraggio

Non eseguire query alle tabelle di monitoraggio di Firebird (MON$) troppo spesso - tali query consumano risorse significative e possono ridurre notevolmente le prestazioni della logica di business principale. Raccomandiamo di eseguire query MON$ non più di una volta al minuto. Per il monitoraggio continuo di query/transazioni/attachments di Firebird, utilizza lo strumento HQbird PerfMon che supporta Trace API (vedi pagina 66 della Guida utente HQbird per i dettagli).

44. Utilizzare l’opzione NO_AUTO_UNDO per inserimenti/aggiornamenti bulk

Se stai eseguendo molti comandi DML (Update/Insert/Delete) nell’ambito della stessa transazione, Firebird unisce l’undo-log di ogni comando con l’undo-log della transazione. Per velocizzare le operazioni DML bulk, avvia la transazione con l’opzione «NO AUTO UNDO», per non unire gli undo-log di ogni comando con l’undo-log della transazione.

45. Non utilizzare l’autenticazione SRP in Firebird 3 se non ne hai bisogno

Non utilizzare l’autenticazione utenti SRP (Firebird 3.0+) se non ne hai realmente bisogno - la connessione con autenticazione SRP viene stabilita più lentamente rispetto alla connessione normale.

Invece del riepilogo

L’ottimizzazione delle prestazioni richiede di considerare molteplici fattori e può essere davvero complessa. Se hai provato tutte le cose sopra, considera di assumere un servizio professionale di ottimizzazione delle prestazioni del database.

Contattaci

Hai domande? Non esitare a contattarci via email!