IBAnalyst: Suggerimenti e Trucchi

Questo testo è stato scritto originariamente nel 2012, è valido per le versioni 1.0 - 2.5, nelle versioni 3.0-5.0 ci sono stati molti cambiamenti, che non hanno potuto essere riflessi. Si prega di leggere la documentazione o contattarci per supporto: [email protected].

Alcune domande che non hanno risposta nei Consigli e/o nell’Aiuto di IBAnalyst:

1. Come ricostruire gli indici su vincoli PRIMARY, FOREIGN o UNIQUE?

R: Per le versioni di Firebird 1.0-2.5. Sì, non è possibile usare ALTER INDEX xxx INACTIVE/ACTIVE sugli indici dei vincoli. Se vedi un indice profondo o frammentato su questo vincolo, puoi usare un trucco speciale (usato da gbak al ripristino):

RDB$INDICES ha il flag RDB$INDEX_INACTIVE che è nullo o 0 se l’indice è attivo (dopo CREATE INDEX o ALTER INDEX ACTIVE). 1 significa che l’indice è inattivo (dopo ALTER INDEX INACTIVE). Ma c’è anche il valore 3 usato per indicare indici inattivi sui vincoli. Quindi, puoi impostare RDB$INDEX_INACTIVE=3 per quell’indice, fare COMMIT, e poi riportare il valore a 0 e fare di nuovo commit - l’indice verrà ricostruito.

Per Firebird 3.0-5.0 - semplicemente esegui ALTER INDEX nomeindice ACTIVE

2. Ho usato tutte le raccomandazioni di IBAnalyst ma questo non aiuta a velocizzare le query.

R: Questo è un problema separato, dove IBAnalyst non può aiutare. Qui possono esserci 2 cause del problema:

1. Gli indici hanno statistiche obsolete. Puoi aggiornare le statistiche degli indici con il comando SET STATISTICS INDEX xxx (vedi maggiori dettagli http://www.ibase.ru/proc_selectivity/).

2. Semplicemente non esiste un indice adatto per qualche condizione usata nella query

3. Le query sono molto complesse, o l’ottimizzatore non può ottimizzare la query, quindi è necessario rifattorizzare la query.

4. In alcuni casi vedrai “tabelle frammentate” subito dopo il ripristino.

Normalmente Firebird e InterBase (senza il parametro -use_all_space) riservano circa il 25% di spazio sulle pagine dati per futuri inserimenti, aggiornamenti o eliminazioni (per posizionare le versioni dei record). Ma, con qualsiasi dimensione di pagina del database (1, 2, 4 o 8 k) vedrai circa il 50% di frammentazione per le tabelle che hanno una dimensione del record piccola (circa ~12-20 byte, per esempio, una tabella con 2 campi interi ha una dimensione media del record = 12 byte).

Questo è normale, consideralo come un numero magico del server (o comportamento).

Quindi, se hai tabelle con record così piccoli, puoi

a) ignorare l’avviso “frammentata” per quelle tabelle

b) abbassare la “% frammentazione” al 45%, per esempio, nella finestra Opzioni di IBAnalyst.

4. Versioni dei record per una tabella che non deve essere aggiornata

Se vedi versioni di record su una tabella che non deve essere aggiornata (per esempio, una tabella con un log di eventi) - non preoccuparti, queste versioni sono generate dalle eliminazioni.

Quindi, saprai quanti record correnti ci sono nella tabella, e quanti record sono stati eliminati.

Questo è vero solo se MaxVer = 1. Se è > 1, allora questa tabella viene aggiornata da qualche applicazione. Se sei davvero sicuro che questa tabella non debba mai essere aggiornata, è meglio impostare un trigger “before update” con un’eccezione per scoprire quale applicazione fa gli aggiornamenti.

5. I BLOB possono causare frammentazione delle tabelle.

Il motore memorizza i BLOB in 3 modi diversi:

1. 1. se il contenuto del BLOB entra nella pagina dati (spazio libero sufficiente), verrà memorizzato su quella pagina dati vicino al suo record (o versione).
2. 2. se il contenuto del BLOB non entra nella pagina dati, verrà memorizzato su una pagina separata
3. 3. se nel caso 2 il BLOB non entra in una pagina dati, viene creata una pagina puntatore per puntare alle pagine BLOB appropriate.

Il caso 1 accade a seconda della dimensione del BLOB memorizzato e della dimensione della pagina del database. Per esempio, se hai una dimensione di pagina di 4K e BLOB con dimensione media ~5K, questi non vengono memorizzati nelle pagine dati, ma in pagine BLOB aggiuntive.

Ma se fai un backup del database e lo ripristini con dimensione di pagina di 8K, i BLOB entreranno nella pagina dati, e verranno memorizzati con i record, causando un’alta frammentazione dei record.

IBAnalyst segna queste tabelle come Pallide (colonna Records) e il suggerimento mostra i record stimati per quella tabella (basati sul conteggio delle pagine dati) e il valore medio reale di riempimento (%).

Se la tua query legge qualsiasi campo tranne i BLOB da quella tabella, la scansione naturale, il join o l’aggregazione saranno molto lenti.

L’unica soluzione per evitarlo: creare una tabella aggiuntiva (collegata 1-1 alla tabella originale) e spostare tutte le colonne BLOB che hanno dimensione media inferiore alla dimensione della pagina in essa.

In quel caso non cercare di fare backup/ripristino con dimensione di pagina maggiore! Questo farà sì che i BLOB che non potevano entrare nelle pagine dati con la dimensione di pagina corrente, vengano posizionati nelle pagine dati durante il ripristino con dimensione di pagina maggiore. Quindi, le tue tabelle con BLOB saranno più frammentate di prima.

Inoltre non è raccomandato ripristinare con dimensione di pagina minore, perché può diminuire le prestazioni per gli indici e le tabelle senza BLOB.

Inoltre non dovresti cercare di cambiare i campi BLOB in campi VARCHAR - i campi VARCHAR sono sempre memorizzati come parte di un record, quindi il record può avere 2 o più frammenti (essere posizionato su 2 o più pagine dati) se non entra nella pagina dati.

p.s. IBAnalyst può segnalare queste tabelle “per errore”, per esempio, la tabella aveva campi BLOB con dati, ma sono stati rimossi dalla struttura della tabella. Sfortunatamente non c’è un’opzione configurabile per questo avviso, perché lo calcoliamo esattamente dai dati riportati dal server (statistiche).

6. Relazione VerLen e RecLength

a) VerLen >= 90% di RecLength: le versioni che vedi nella colonna Version sono per lo più eliminazioni di record. Più record vengono eliminati, minore sarà RecLength (fino a 0 byte). Inoltre VerLen può essere maggiore di RecLen se aggiorni la tua tabella con dati stringa più grandi di quelli memorizzati nei record originali.

b) VerLen <= 80% di RecLength: le versioni sono per lo più aggiornamenti di record.

Non possiamo distinguere questi casi più precisamente perché le statistiche mostrano la dimensione media del record e della versione per l’intera tabella, mentre il numero di versioni visibili per le transazioni concorrenti può variare.

7. Perché IBAnalyst definisce alcuni indici come “cattivi”?

Gli indici con valore di selettività inferiore a 0.01 sono marcati come “cattivi” in IBAnalyst (vedi l’aiuto della vista Index). Ci sono diverse cause per definire un particolare indice come cattivo:

1. La selettività di quell’indice è inferiore a 0.01. Teoricamente l’ottimizzatore non dovrebbe usare quell’indice, ma lo fa se non esistono altri indici (per where, order by o clausola join, almeno)

2. Un tale indice causa una raccolta dei rifiuti (garbage collection) molto lenta. Questo problema non esiste in InterBase 7.1/7.5, e sarà risolto in Firebird 2.0

3. Questo indice rende il processo di ripristino molto lento, e viene creato molto lentamente (create/alter index active). Questo perché la catena dei numeri di record è grande per una chiave di indice.

4. Se questo indice è usato nella clausola where, l’uso della memoria dipenderà dal valore cercato (dimensione della bitmask). Poiché la catena di record può essere grande (molti duplicati di chiave), anche il consumo di memoria sarà grande.

5. Se quell’indice è usato in “order by”, e ci sono molti duplicati soprattutto nei valori di chiave inferiori (a seconda dell’ordine di ordinamento dell’indice), ci saranno molte letture di pagine di indice, che rallenteranno la query.

Questo perché IBAnalyst non può ignorare l’esistenza di tali indici.

Il caso peggiore per un indice è quando ha la colonna Uniques = 1, cioè tutti i valori per la colonna indicizzata sono gli stessi. Questi indici sono elencati in “Indici inutili” nella pagina Riepilogo.

Naturalmente, per la tua applicazione un tale indice può essere “buono”. Per esempio, se i record hanno un flag “archivio” in qualche colonna, e la tua applicazione cerca tramite indice su quella colonna solo per i dati correnti, non archiviati. Quindi, sta a te decidere se abbiamo ragione a definire quell’indice “cattivo” o no.

8. Cosa succede se l’indice “cattivo” è creato da un vincolo Foreign Key?

Bene, il paragrafo precedente mostra che è meglio eliminare gli indici “cattivi” (se non li usi per cercare chiavi che hanno meno duplicati di altre chiavi). Ma, se un tale indice è creato da una chiave esterna, puoi eliminarlo solo eliminando la chiave esterna. Eliminare la chiave esterna disabiliterà il vincolo di controllo della relazione, che può essere inaccettabile.

Puoi sostituire la FK con trigger, ma con alcune restrizioni. La FK controlla le relazioni dei record usando l’indice, e l’indice “vede” tutte le chiavi per tutti i record indipendentemente dallo stato delle transazioni. Ma i trigger funzionano solo nel contesto della transazione del client. Quindi, sostituendo la FK con trigger, devi essere sicuro che:

• I record non verranno eliminati dalla tabella master, o verranno eliminati in modalità “snapshot table reserving”
• La colonna, usata dalla PK nella tabella master, non verrà mai modificata. Puoi limitare questo con un trigger before update.

Se manterrai queste condizioni, puoi eliminare la particolare chiave esterna. Naturalmente, non creare manualmente un indice su quella colonna.

9. Perché nella riga percentuale della versione dati ci sono solo 12 megabyte di dati, ma ho un database di 140 megabyte?

1. IBAnalyst qui mostra il volume “puro” dei dati, senza contare altre strutture del database (indici, metadati…) e la frammentazione delle pagine.

2. Dopo il ripristino, InterBase e Firebird lasciano dello spazio libero (15-25%) nelle pagine dati per rendere più veloci futuri aggiornamenti/eliminazioni.

3. C’è un comportamento specifico del server quando lascia le pagine dati frammentate di circa il 50%, se la dimensione del record di quella tabella è bassa, circa 11-22 byte.

10. Come migliorare le prestazioni dell’ottimizzatore in caso di aggiornamenti frequenti

Le statistiche degli indici sono memorizzate nella colonna RDB$INDICES.RDB$STATISTICS, e vengono aggiornate in 3 modi:

1. SET STATISTICS INDEX

2. ALTER INDEX ACTIVE, o CREATE INDEX …

3. processo di ripristino (tutti gli indici vengono ricostruiti così come “ALTER INDEX ACTIVE”)

L’ottimizzatore usa queste informazioni statistiche per preparare le query. Usando i valori delle statistiche, l’ottimizzatore può decidere che l’indice è “abbastanza buono” o “non utile” per recuperare i record.

Se le statistiche non vengono aggiornate per molto tempo, l’ottimizzatore può produrre un piano pessimo perché i valori delle statistiche esistenti non corrispondono allo stato attuale delle cose, poiché i dati della tabella possono essere cambiati significativamente (per esempio, la quantità di record è aumentata di 5-10 volte, o viceversa, tutti i record sono stati eliminati).

Puoi sostituire il piano di query automatico pessimo con un PLAN esplicito per una particolare query, ma questo non è un buon approccio, perché i dati possono cambiare significativamente dopo che il piano è stato sviluppato.

Il modo alternativo (e corretto) è aggiornare periodicamente le statistiche applicando l’istruzione SET STATISTICS per tutti gli indici. Puoi programmare l’esecuzione di uno script SQL per aggiornare le statistiche usando ISQL o lo strumento già pronto gidx (solo Windows).

Se hai alcune tabelle con record diversi ricaricati periodicamente, questo approccio non aiuterà. Consideriamo l’esempio:

• La tabella A viene caricata con dati 4-5 volte al giorno.
• Dopo l’elaborazione dei dati caricati, tutti i record nella tabella A vengono eliminati

In questo caso possiamo vedere 2 valori corretti di statistiche per gli indici sulla tabella A - quando è caricata con dati, e quando è vuota. Quindi, le statistiche ricalcolate sulla tabella caricata saranno inutili quando la tabella è vuota, e viceversa.

Per evitare questo, devi ricalcolare le statistiche per gli indici sulla tabella A solo quando la tabella è piena di dati. Il momento migliore è prima che le query su quella tabella vengano eseguite.

Dalla versione 1.91, IBAnalyst mostra la differenza delle statistiche degli indici e ti permette di ricalcolarle in qualsiasi momento. Prima devi guardare le informazioni sul record della tabella - è il solito numero medio di record o no. Se sì, puoi ricalcolare la selettività dell’indice con certezza. Se no - forse è meglio non toccare le statistiche degli indici, perché potrebbe causare all’ottimizzatore di produrre piani di query ancora peggiori.