IBAnalyst: comprendere il tuo database

Dmitri Kuzmenko, [email protected], ultimo aggiornamento 31 marzo 2014

Lavoro con InterBase dal 1994. Allora, la maggior parte dei database erano piccoli e non richiedevano alcuna ottimizzazione. Naturalmente, ci sono state occasioni in cui ho dovuto modificare ibconfig su un server e riconfigurare hardware o sistema operativo, ma era quasi tutto ciò che potevo fare per ottimizzare le prestazioni.

Quattro anni fa, la nostra azienda ha iniziato a fornire supporto tecnico e formazione agli utenti InterBase. Lavorare con molti database di produzione mi ha anche insegnato molte cose diverse. Tuttavia, la maggior parte di ciò che ho imparato riguardava le applicazioni: utilizzo dei parametri delle transazioni, ottimizzazione di query e set di risultati.

Naturalmente, sapevo da tempo di gstat, lo strumento che fornisce informazioni statistiche sul database. Se hai mai guardato l’output di gstat o letto opguide.pdf al riguardo, sapresti che l’output statistico sembra solo un mucchio di numeri e nient’altro. Ok, puoi scoprire informazioni sulla frammentazione per una particolare tabella o indice, ma quali altre informazioni utili si possono ottenere?

Per fortuna, prima di lavorare con InterBase, ero interessato a diverse strutture dati, a come vengono memorizzate e a quali algoritmi usano. Questo mi ha aiutato a interpretare l’output di gstat. In quel momento ho deciso di scrivere uno strumento che potesse analizzare l’output di gstat per aiutare nell’ottimizzazione del database o almeno per identificare la causa dei problemi di prestazioni.

Per farla breve, il risultato è stato la creazione di IBAnalyst. Nonostante la mia esperienza, mi permette ancora di trovare cose molto interessanti o problemi di prestazioni in diversi database.

I sistemi reali hanno prestazioni di runtime che fluttuano come un’onda. L’ampiezza di tali ‘onde’ può essere bassa o alta, quindi puoi vedere come le prestazioni differiscono di giorno in giorno (o ora per ora). Le prestazioni effettive dipendono da molti fattori, inclusi il design dell’applicazione, la configurazione del server, la concorrenza delle transazioni, il garbage delle versioni nel database e così via. Per scoprire cosa sta succedendo in un database (sia aspetti positivi che negativi delle prestazioni), dovresti almeno dare un’occhiata alle statistiche del database di tanto in tanto.

Diamo un’occhiata alle capacità di IBAnalyst. IBAnalyst può prendere statistiche da gstat o dall’API dei Servizi e compilarle in un report che ti fornisce informazioni complete sul database, le sue tabelle e i suoi indici. Ha avvisi in-place disponibili durante la navigazione delle statistiche; include anche commenti suggeriti e report di raccomandazioni.

Informazioni sul Database

Figura 1 Riepilogo delle statistiche del database

Il riepilogo mostrato nella Figura 1 fornisce informazioni generali sul tuo database. Gli avvisi o i commenti mostrati si basano su conoscenze accuratamente raccolte da un gran numero di database di produzione reali.

Nota: Tutte le figure in questo articolo contengono statistiche gstat prese da un database di produzione reale (con il permesso dei suoi proprietari).

Come ho detto prima, le statistiche grezze del database sembrano criptiche e sono difficili da interpretare. IBAnalyst evidenzia chiaramente eventuali problemi potenziali in giallo o rosso e il dettaglio del problema può essere letto semplicemente posizionando il cursore sulla voce pertinente e leggendo il suggerimento visualizzato.

Successivamente, possiamo vedere che il parametro Forced Write è impostato su OFF ed è marcato in rosso. InterBase 4.x e 5.x per impostazione predefinita avevano questo parametro su ON. Forced Writes stesso è un metodo di cache di scrittura: quando è ON, scrive i dati modificati immediatamente su disco, ma OFF significa che le scritture saranno memorizzate per un tempo sconosciuto dal sistema operativo nella sua cache di file. InterBase 6 crea database con Forced Writes OFF.

Perché è marcato in rosso nel report di IBAnalyst? La risposta è semplice: l’uso di scritture asincrone può causare corruzione del database in caso di guasti di alimentazione, sistema operativo o server.

Suggerimento: È interessante notare che le moderne interfacce HDD (ATA, SATA, SCSI) non mostrano alcuna grande differenza di prestazioni con Forced Write impostato su On o Off(1).

Successivamente nel report c’è il misterioso “intervallo di sweep”. Se positivo, imposta la dimensione del divario tra la transazione più vecchia (2) e la transazione snapshot più vecchia, al quale il motore viene avvisato della necessità di avviare una raccolta automatica del garbage. Su alcuni sistemi, il raggiungimento di questa soglia causerà un effetto di “improvvisa perdita di prestazioni”, e di conseguenza a volte si raccomanda di impostare l’intervallo di sweep a 0 (disabilitando completamente lo sweep automatico). Qui, l’intervallo di sweep è marcato in giallo, perché il valore del divario di sweep è negativo, cosa che può essere nelle statistiche di InterBase 6.0, Firebird e Yaffil ma non in InterBase 7.x. Quando il valore del divario di sweep è maggiore dell’intervallo di sweep (se l’intervallo di sweep non è 0), la voce del report per l’intervallo di sweep sarà marcata in rosso con un suggerimento appropriato.

Esamineremo le successive 8 righe come gruppo, poiché mostrano tutte aspetti dello stato delle transazioni del database:

La transazione più vecchia è la transazione non committata più vecchia. Qualsiasi numero di transazione inferiore è per transazioni committate, e non sono disponibili versioni di record per tali transazioni. I numeri di transazione superiori alla transazione più vecchia sono per transazioni che possono essere in qualsiasi stato. Questa è anche chiamata la “transazione interessante più vecchia”, perché si blocca quando una transazione termina con rollback, e il server non può annullare le sue modifiche in quel momento.
Lo snapshot più vecchio - la transazione attiva (cioè non ancora committata) più vecchia che esisteva all’inizio della transazione che è attualmente la transazione “interessante” più vecchia. Indica il numero di transazione snapshot più basso che è interessato alle versioni dei record.
La più vecchia attiva - la transazione attualmente attiva più vecchia (3).
La prossima transazione - il numero di transazione che sarà assegnato a una nuova transazione.
Transazioni attive - IBAnalyst darà un avviso se il numero della transazione attiva più vecchia è il 30% inferiore al conteggio giornaliero delle transazioni. Le statistiche non dicono se ci sono altre transazioni attive tra la più vecchia attiva e la prossima transazione, ma possono esserci tali transazioni. Di solito, se la più vecchia attiva si blocca, ci sono due possibili cause: a) qualche transazione è attiva per molto tempo o b) il design dell’applicazione permette alle transazioni di durare a lungo. Entrambe le cause impediscono la raccolta del garbage e consumano risorse del server.
Transazioni al giorno - questo è calcolato dalla prossima transazione, diviso per il numero di giorni trascorsi dalla creazione del database al punto in cui vengono recuperate le statistiche. Questo può essere corretto solo per database di produzione, o per database che vengono periodicamente ripristinati da backup, causando il reset della numerazione delle transazioni.

Come hai già imparato, se ci sono avvisi, vengono mostrati come righe colorate, con suggerimenti chiari e descrittivi su come risolvere o prevenire il problema.

Va notato che le statistiche del database non sono sempre utili. Le statistiche raccolte durante operazioni di lavoro e manutenzione possono essere prive di significato.

Non raccogliere statistiche se:

Hai appena ripristinato il tuo database
Hai eseguito un backup (gbak -b db.gdb) senza l’opzione -g
Hai recentemente eseguito uno sweep manuale (gfix -sweep)

Le statistiche che ottieni in tali occasioni saranno praticamente inutili. È anche vero che durante il normale lavoro ci possono essere momenti in cui il database è in uno stato perfetto, ad esempio, quando le applicazioni fanno meno carico del solito sul database (gli utenti sono a pranzo o è un momento tranquillo nella giornata lavorativa).

Come puoi capire quando c’è qualcosa che non va nel database?

Le tue applicazioni possono essere progettate così bene da lavorare sempre correttamente con transazioni e dati, senza creare divari di sweep, senza accumulare molte transazioni attive, senza mantenere snapshot a lunga durata e così via. Di solito non succede (scusate, colleghi).

Il motivo più comune è che gli sviluppatori testano le loro applicazioni con solo due o tre utenti simultanei. Quando l’applicazione viene poi utilizzata in un ambiente di produzione con quindici o più utenti simultanei, il database può comportarsi in modo imprevedibile. Naturalmente, la modalità multiutente può funzionare bene perché la maggior parte dei conflitti multiutente può essere testata con due o tre applicazioni in esecuzione contemporaneamente. Tuttavia, con un numero maggiore di utenti, possono sorgere problemi di raccolta del garbage. Tali potenziali problemi possono essere individuati se raccogli le statistiche del database nei momenti giusti.

Informazioni sulle Tabelle

Diamo un’occhiata a un altro esempio di output da IBAnalyst.

![](/images/article_IBAnalyst (1).jpg)

Figura 2 Statistiche delle tabelle

La vista delle statistiche delle tabelle di IBAnalyst è anche molto utile. Può mostrare quali tabelle hanno molte versioni di record, dove sono stati fatti un gran numero di aggiornamenti/cancellazioni, tabelle frammentate, con frammentazione causata da aggiornamenti/cancellazioni o da blob, e così via. Puoi vedere quali tabelle vengono aggiornate frequentemente e qual è la dimensione della tabella in megabyte. La maggior parte di questi avvisi è personalizzabile.

In questo esempio di database ci sono diversi problemi. Prima di tutto, il colore giallo nella colonna VerLen avverte che lo spazio occupato dalle versioni dei record è più grande di quello occupato dai record stessi. Questo può risultare dall’aggiornamento di molti campi in un record o da cancellazioni di massa. Vedi le righe in cui la colonna MaxVers è marcata in blu. Questo mostra che viene memorizzata solo una versione per record e di conseguenza che il problema è dovuto a cancellazioni di massa. Il valore nella colonna Versions mostra quanti record sono stati cancellati.

Le transazioni attive a lunga durata che impediscono la raccolta del garbage sono la ragione principale del degrado delle prestazioni. Per alcune tabelle possono esserci molte versioni ancora “in uso”. Il server non può decidere se sono realmente in uso, perché le transazioni attive potenzialmente necessitano di una o tutte queste versioni. Di conseguenza, il server non considera queste versioni come garbage, e ci vuole sempre più tempo per costruire un record corretto da molte versioni ogni volta che una transazione lo legge. Nella Figura 2 puoi vedere due tabelle che hanno un conteggio di versioni tre volte superiore al conteggio dei record. Usando queste informazioni puoi anche verificare se il fatto che le tue applicazioni aggiornino queste tabelle così frequentemente è per design o a causa di un errore.

La vista degli Indici

Gli indici sono usati dal motore del database per applicare vincoli di chiave primaria, chiave esterna e unicità. Accelerano anche il recupero dei dati. Gli indici univoci sono i migliori per il recupero dei dati, ma il livello di beneficio dagli indici non univoci dipende dalla diversità dei dati indicizzati.

Ad esempio, guarda ADDR_ADDRESS_IDX6. Prima di tutto, il nome dell’indice stesso suggerisce che è stato creato manualmente. Se le statistiche sono state prese dall’API dei Servizi con informazioni sui metadati, puoi vedere quali colonne sono indicizzate (in IBAnalyst 1.83 e successive). Per l’indice in esame puoi vedere che ha 34999 chiavi, TotalDup è 34995 e MaxDup è 25056. Entrambe le colonne dei duplicati sono marcate in rosso. Questo perché ci sono solo 4 valori di chiave univoci tra tutte le chiavi in questo indice, come si può vedere dalla colonna Uniques. Inoltre, la catena di duplicati più grande (chiave che punta a record con lo stesso valore di colonna) è 25056 - cioè quasi tutte le chiavi memorizzano uno dei quattro valori univoci. Di conseguenza, questo indice potrebbe:

Ridurre la velocità del processo di ripristino. Ok, trentacinquemila chiavi non sono un grosso problema per i database e l’hardware moderni, ma l’impatto dovrebbe comunque essere notato.
Rallentare la garbage collection. Gli indici con un basso numero di valori unici possono ostacolare la garbage collection fino a dieci volte rispetto a un indice completamente unico. Questo problema è stato risolto in InterBase 7.1/7.5 e Firebird 2.0.
Produrre letture di pagine non necessarie quando l’ottimizzatore legge l’indice. Dipende dal valore cercato in una particolare query: cercare tramite un indice che ha un valore più alto per MaxDup sarà più lento. Cercare per valore su una colonna con meno valori duplicati sarà più veloce, ma solo tu sai che la colonna è indicizzata.

Ecco perché IBAnalyst attira la tua attenzione su tali indici, contrassegnandoli in rosso e giallo e includendoli nel report Raccomandazioni. Sfortunatamente, la maggior parte degli indici “cattivi” viene creata automaticamente per applicare i vincoli di chiave esterna. In alcuni casi, questo problema può essere risolto impedendo, tramite trigger, eliminazioni o aggiornamenti della chiave primaria nelle tabelle di lookup. Ma se non è possibile implementare tali modifiche, IBAnalyst mostrerà gli indici “cattivi” sulle chiavi esterne ogni volta che visualizzi le statistiche.

Report

Non è necessario esaminare l’intero report ogni volta, individuando il colore delle celle e leggendo i suggerimenti per nuovi avvisi. Informazioni più dirette e dettagliate possono essere ottenute utilizzando la funzionalità Raccomandazioni di IBAnalyst. Basta caricare le statistiche e andare al menu Report/Visualizza Raccomandazioni. Questo report fornisce un’analisi passo-passo, inclusi avvisi descrittivi più dettagliati su scritture forzate, intervallo di sweep, attività del database, stato delle transazioni, dimensione delle pagine del database, sweep, pagine di inventario delle transazioni, tabelle frammentate, tabelle con molte versioni di record, eliminazioni/aggiornamenti massivi, indici profondi, indici non ottimizzati per l’ottimizzatore, indici inutili e persino tabelle vuote. Tutte queste informazioni e i suggerimenti associati vengono creati dinamicamente in base alle statistiche caricate.

Come esempio dell’output del report, diamo un’occhiata a un report generato per le statistiche del database che hai visto prima in questo articolo:

“La dimensione complessiva delle pagine di inventario delle transazioni (TIP) è grande - 94 kilobyte o 23 pagine. La transazione Read_committed utilizza il TIP globale, ma le transazioni snapshot creano copie proprie del TIP in memoria. Una dimensione TIP grande può rallentare le prestazioni. Prova a eseguire lo sweep manualmente (gfix -sweep) per ridurre la dimensione del TIP.”

Ecco un’altra citazione dalla parte tabelle/indici del report:

“Conteggio tabelle versionate: 8. Una grande quantità di versioni di record di solito rallenta le prestazioni. Se ci sono molte versioni di record in una tabella, allora la garbage collection non funziona, o i record non vengono letti da alcuna istruzione select. Puoi provare a eseguire select count(*) su quelle tabelle per forzare la garbage collection, ma questo può richiedere molto tempo (se ci sono molte versioni e indici non unici) e può non avere successo se c’è almeno una transazione interessata a queste versioni.

Ecco l’elenco delle tabelle con un rapporto versioni/record maggiore di 3:


Tabella	Record	Versioni	Dim. Rec/Vers
CLIENTS_PR	3388	10944	92%
DICT_PRICE	30	1992	45%
DOCS	9	2225	64%
N_PART	13835	72594	83%
REGISTR_NC	241	4085	56%
SKL_NC	1640	7736	170%
STAT_QUICK	17649	85062	110%
UO_LOCK	283	8490	144%

Riepilogo

IBAnalyst è uno strumento prezioso che assiste l’utente nell’esecuzione di un’analisi dettagliata delle statistiche del database Firebird o InterBase e nell’identificazione di possibili problemi con un database in termini di prestazioni, manutenzione e interazione dell’applicazione con il database. Prende statistiche criptiche del database e le visualizza in modo grafico facile da comprendere e formula automaticamente suggerimenti sensati per migliorare le prestazioni del database e semplificare la manutenzione del database.

1 InterBase 7.5 e Firebird 1.5 hanno funzionalità speciali che possono scaricare periodicamente le pagine non salvate se le Scritture Forzate sono disattivate.

2 La transazione più vecchia è la stessa transazione interessante più vecchia, menzionata ovunque. L’output di Gstat non mostra questa transazione come “interessante”.

3 Ann Harrison dice che la transazione attiva più vecchia è la transazione più vecchia che era attiva quando è iniziata la transazione attiva più vecchia corrente. Per le applicazioni, qui non c’è una grande differenza.