45 modi per velocizzare il database Firebird

Qui puoi trovare l’elenco dei suggerimenti sulle prestazioni per il database Firebird in diverse aree - dall’hardware/SO e l’ottimizzazione della configurazione di Firebird fino 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. Mettere il database su SSD

Metti il tuo database su SSD. L’unità SSD fornisce un IO casuale molto migliore rispetto alle unità tradizionali. L’IO casuale è fondamentale per la lettura e la scrittura di dati distribuiti in un grande file di database - la maggior parte delle operazioni del database richiede un intenso IO casuale parallelo.

2. Usare RAID 10

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

3. Verificare la BBU

Se stai usando 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, persino più lentamente delle normali unità SATA. Di solito, puoi verificare lo stato della BBU nello strumento di configurazione RAID.

4. Impostare la cache di scrittura su write-back

Se stai usando un controller RAID con BBU installata (e un 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. Abilitare la cache di lettura

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

6. Verificare il sottosistema dischi

Verifica le tue unità per blocchi danneggiati e altri problemi hardware (incluso il surriscaldamento). I problemi hardware possono ridurre significativamente le prestazioni IO e portare a corruzioni del database.

7. Usare 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. Usare SuperServer 3.0 in Firebird 3.

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

9. Aumentare la cache dei page buffers

Aumenta la dimensione della cache dei page buffers (parametro DefaultDBCachePages) rispetto ai 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 suo costo, e l’idea di mettere tutto il database in RAM regolando questo valore non funzionerà. Usa i file di configurazione di Firebird pre-ottimizzati qui: /it/optimized-firebird-configuration/

10. Aumentare la dimensione della 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. Anche in questo caso, usa i file di configurazione ottimizzati del punto #9.

11. Impostare Forced Writes su Off (con cautela!)

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

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

Aumenta il valore del parametro LockHashSlots per Classic e SuperClassic dal valore predefinito 1009 a un numero primo più grande (30011, per esempio), ridurrà le code nel meccanismo di locking interno.

13. Usare 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 core CPU diversi per elaborare le richieste per determinati database.

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

Imposta la prima parte del parametro TempDirectory in firebird.conf sul disco veloce - SSD o RAM drive. Ridurrà il tempo delle grandi operazioni di ordinamento - per esempio quando il database viene ripristinato.

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

Memorizza i backup del database su un’unità fisica dedicata (RAID). Separarà l’IO di lettura e scrittura durante il backup, e aumenterà la velocità del backup e ridurrà il carico sull’unità principale. È particolarmente importante quando i backup vengono eseguiti mentre gli utenti stanno lavorando con il database. Maggiori dettagli sulla configurazione hardware per Firebird possono essere trovati nella " Firebird Hardware Guide".

16. Disattivare gli indici per inserimenti massivi

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. Usare le Global Temporary Tables per inserimenti veloci

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

18. Evitare indici non necessari

Usa meno indici per tabelle con inserimenti e aggiornamenti intensi. Ogni indice aggiunge un significativo sovraccarico per le operazioni di inserimento, aggiornamento, cancellazione e garbage collection - potrebbero esserci 3-4 letture e scritture di pagina aggiuntive quando il singolo record viene inserito/aggiornato/cancellato/pulito per ogni indice.

19. Sostituire le UDF con chiamate a funzioni embedded

Sostituisci le chiamate UDF con chiamate a funzioni embedded. Molte funzioni embedded 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 embedded funzionano fino a 3 volte più velocemente delle UDF.

20. Usare transazioni read-only per le operazioni di lettura

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

21. Usare transazioni di scrittura brevi e liberarsi di TUTTE quelle di lunga durata

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

Più breve è la transazione di scrittura, meglio è. Le transazioni brevi trattengono proporzionalmente un numero inferiore di versioni dei record dalla garbage collection rispetto a quelle di lunga durata. Sfortunatamente, anche la singola transazione di lunga durata (lasciata aperta dallo strumento di sviluppo, per esempio) può rovinare il buon effetto di tutte le altre transazioni di scrittura brevi. Ecco perché devi monitorare le transazioni di lunga durata 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 avvio), e lo strumento HQbird MonLogger per vedere l’elenco completo delle transazioni attive di lunga durata e le loro statistiche IO. Inoltre, se stai usando componenti/librerie di accesso al database che possono memorizzare nella cache i set di record, usa gli aggiornamenti in cache.

22. Evitare catene di record lunghe

Evita situazioni in cui un record ha molte versioni - Firebird funziona molto più lentamente con catene di record lunghe. (per vedere quante versioni dei 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 la combinazione di inserimenti e cancellazione pianificata dei vecchi record invece di molteplici aggiornamenti dello stesso record.

23. Usare PREPARE correttamente

Usa statement preparati per eseguire query SQL in cui cambiano solo i parametri - per esempio, fai la prepare prima del ciclo di tali query. La prepare può richiedere un tempo significativo (specialmente per tabelle grandi), e preparare la query una sola volta aumenterà notevolmente le prestazioni complessive.

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

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

25. “Disattivare” gli indici se stai usando IN con molte costanti

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

26. Sostituire IN con JOIN

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

27. Usare LEFT JOIN nel modo corretto

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

28. Limitare il fetch delle query SELECT

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

29. Specificare un numero inferiore di 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 delle clausole SELECT e ORDER BY/GROUP BY e le ordina in memoria (o, se la memoria non è sufficiente, su disco). Quindi, se c’è un VARCHAR lungo nella 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. Usare 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. Memorizzare le stringhe corte in VARCHAR, quelle lunghe in BLOB

Per memorizzare dati carattere corti, usa i VARCHAR, per memorizzare testi lunghi, usa i BLOB. I varchar sono più veloci per i piccoli pezzi di dati perché sono memorizzati nel record, e l’intero record viene letto nello stesso ciclo IO, 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 al di fuori del record, e richiedono un ulteriore ciclo IO per essere letti, e mostrano il loro vantaggio con la lettura e scrittura di stringhe lunghe.

32. Escludere le colonne BLOB dalle SELECT grandi

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

33. Usare BIGINT per chiavi primarie e univoche

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

34. Non usare VARCHAR per le chiavi

Non usare VARCHAR per gli identificatori a meno che non sia davvero 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/uniche 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 massicce con il comando SET STATISTICS; ciò consente all’optimizer di Firebird di scegliere piani SQL migliori. HQbird Firebird DataGuard può eseguire automaticamente questo ricalcolo delle statistiche degli indici secondo la pianificazione desiderata (solitamente una volta a settimana).

36. Usare il connection pool

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

37. Usare l’opzione LINGER in Firebird 3.0

Se le connessioni al database sono brevi e stai usando Firebird 3+, usa l’opzione LINGER per mantenere la cache attiva per il periodo di tempo specificato; ciò manterrà le pagine usate 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. Usare gli HASH JOIN

In Firebird 3.0, nel caso di join tra tabelle grandi e piccole, HASH JOIN potrebbe essere molto più veloce del join normale che usa il «nested loop» con indice. Per far usare all’optimizer di Firebird l’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. Contrassegnare le funzioni PSQL appropriate come DETERMINISTIC

Contrassegna 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 messe in cache nell’ambito della query corrente.

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

Se esegui una SELECT con l’output simultaneo di una colonna e della funzione aggregata per essa, usa le funzioni window (analitiche): è più veloce della sottoquery o di 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. Usare lo switch -se per gbak

Usa lo switch -se per aumentare fino al 20% la velocità di backup e/o restore di gbak, 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 unica.

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. Consigliamo di eseguire le query MON$ non più spesso di una volta al minuto. Per il monitoraggio continuo di query/transazioni/connessioni di Firebird, usa lo strumento HQbird PerfMon che supporta la Trace API (vedi pagina 66 della HQbird User Guide per i dettagli).

44. Usare l’opzione NO_AUTO_UNDO per inserimenti/aggiornamenti massivi

Se esegui 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 massive, avvia la transazione con l’opzione «NO AUTO UNDO», in modo da non unire gli undo-log di ogni comando con l’undo-log della transazione.

45. Non usare l’autenticazione SRP in Firebird 3 se non ti serve

Non usare l’autenticazione degli utenti SRP (Firebird 3.0+) se non ti serve davvero: la connessione con autenticazione SRP viene stabilita più lentamente rispetto alla connessione regolare.

Invece del riepilogo

L’ottimizzazione delle prestazioni richiede di considerare molteplici fattori e può essere davvero complicata. Se hai provato tutte le cose sopra, valuta la possibilità di affidarti a un servizio professionale di ottimizzazione delle prestazioni del database.

Contattaci

Hai domande? Non esitare a contattarci via email!