UNIVERSITÀ DEGLI STUDI DI CATANIA
FACOLTÀ DI SCIENZE MATEMATICHE FISICHE E NATURALI
CORSO DI LAUREA DI 1° LIVELLO IN INFORMATICA
ROBERTO MIRABELLA
DATABASE TUNING
Tesi di Laurea
Relatrice:
Chiar.ma Prof.ssa ROSALBA GIUGNO
ANNO ACCADEMICO 2010 - 2011
Ai miei genitori.
2
SOMMARIO
PREMESSA ...................................................................................... 6
1. DATABASE E DATABASE TUNING .................................... 9
1.1. Cenni storici sui database. ............................................................ 9
1.2. I database ai giorni nostri. .......................................................... 11
1.3. Che cos‟è il database tuning? ..................................................... 13
1.4. I principi fondamentali ............................................................... 14
1.5. Livelli di intervento .................................................................... 18
2. TUNING DEL SISTEMA ....................................................... 24
2.1. Tuning dell‟Hardware ................................................................ 24
2.1.1.
Tuning del sottosistema di archiviazione ................................. 25
2.1.2.
Modifica e aggiunta di componenti Hardware ......................... 32
2.2. Tuning del Sistema Operativo .................................................... 36
2.2.1.
Gestione dei Threads ................................................................ 37
2.2.2.
Database buffer ........................................................................ 41
2.2.3.
File System ............................................................................... 43
2.3. Tuning del DBMS ...................................................................... 45
2.3.1.
Locking Tuning ........................................................................ 45
2.3.2.
Logging Tuning ....................................................................... 48
3
2.4. Tuning Applicativo ..................................................................... 50
2.4.1.
Modello Client-Server .............................................................. 51
2.4.2.
Tuning delle Interfacce Applicative ......................................... 52
3. TUNING DEL DATABASE ................................................... 53
3.1. Tuning a Livello Logico ............................................................. 53
3.1.1.
Normalizzazione....................................................................... 55
3.1.2.
Denormalizzazione ................................................................... 61
3.1.3.
Overnormalizzazione ............................................................... 63
3.2. Tuning a Livello Fisico .............................................................. 64
3.2.1.
Tuning degli indici ................................................................... 64
3.2.2.
Tuning delle query ................................................................... 74
4. APPLICAZIONE PRATICA DEL TUNING ......................... 78
4.1. I Benchmark ............................................................................... 78
4.2. Sistemi di Archiviazione MySQL .............................................. 80
4.2.1.
MyISAM .................................................................................. 80
4.2.2.
InnoDB ..................................................................................... 80
4.3. Implementazione ........................................................................ 81
4.4. I Risultati: Analisi Sintetica ....................................................... 83
4.4.1.
Query per secondo .................................................................... 84
4.4.2.
Byte per secondo ...................................................................... 85
4.4.3.
Tempi di risposta minimi ......................................................... 86
4.4.4.
Tempi di risposta massimi ....................................................... 88
4
4.5. I risultati: Analisi Analitica ........................................................ 90
4.5.1.
Tempi di esecuzione New-Order ............................................. 90
4.5.2.
Tempi di esecuzione Payment .................................................. 92
4.5.3.
Tempi di esecuzione Delivery .................................................. 94
4.5.4.
Tempi di esecuzione Order-Status ........................................... 96
4.5.5.
Tempi di esecuzione Stock-Level ............................................ 98
4.6. Conclusioni ............................................................................... 100
BIBLIOGRAFIA .......................................................................... 102
5
PREMESSA
Al giorno d‟oggi, con l‟evoluzione sempre più veloce e prodigiosa dei mezzi
di comunicazione, la società si è abituata ad avere accesso, in pochi secondi,
quando vuole, alle informazioni che vuole. Basti pensare ai sistemi di gestione
della sanità, alle banche dati delle forze dell‟ordine, alla gestione dei mezzi di
trasporto o anche, a livello più piccolo, alla gestione delle attività di
un‟azienda o, perché no, ai social network, per capire come tutti gli aspetti
importanti della vita di ciascuno di noi siano condizionati da questa premessa.
Se nel piccolo, garantire velocità e portabilità delle informazioni è abbastanza
semplice e chiunque, con un minimo di conoscenze informatiche, è ormai in
grado di creare un database in Access che soddisfi tali esigenze, quando,
invece, le informazioni da gestire diventano milioni o più, ottenere lo stesso
risultato non è altrettanto scontato. Se un ritardo più o meno lungo
nell‟accesso all‟informazione può essere in certi casi
tollerato, come ad
esempio nella ricerca di un libro in una biblioteca, in altri casi, come
l‟elaborazione in tempo reale di dati scientifici, la velocità diventa di
fondamentale importanza. Proprio per venire incontro a queste necessità nasce
il Database Tuning.
Per la stesura di questo lavoro, inizialmente è stata condotta una ricerca di
articoli, testi, linee guida e schematizzazioni, reperiti attraverso internet,
6
insieme alla revisione di letteratura cartacea disponibile. Successivamente, è
stata effettuata una valutazione del materiale raccolto e, infine, si è passati alla
stesura della tesi.
Cercando di non trascurare nessun aspetto relativo a questa tematica
l‟argomento è stato suddiviso in quattro macroaree.
Nella prima, viene introdotto l‟argomento partendo da una serie di accenni
storici sull‟evoluzione dei database così da evidenziare come sono nate le
esigenze degli utenti che il tuning cerca di soddisfare. Poi è stata data una
definizione formale del database tuning e si è entrati nel merito di quali sono i
principi fondamentali a cui gli amministratori devono attenersi. Per finire,
vista la complessità dell‟argomento, si è cercato di schematizzare la trattazione
suddividendola in vari livelli di intervento.
La seconda macroarea ha come tema principale la trattazione analitica di tutti i
livelli di intervento non legati a operazioni dirette sul database, ovvero a tutte
quelle scelte che riguardano l‟hardware, il sistema operativo, il DBMS e i
programmi applicativi del sistema.
La terza macroarea riguarda invece tutti i livelli le cui operazioni modificano
direttamente il database, concentrandosi soprattutto sui concetti di
normalizzazione, denormalizzazione, gestione degli indici e ottimizzazione
delle query.
7
La quarta macroarea infine è il risultato della realizzazione di un esperimento
di tuning pratico realizzato in MySQL. Dopo una breve introduzione vengono
presentati i dati raccolti, le loro rielaborazioni, e le conclusioni da essi derivate
a conferma di quanto esposto nell‟intero elaborato.
8
1. DATABASE E DATABASE TUNING
Prima di iniziare a parlare delle tecniche di tuning è importante soffermarsi un
attimo a riflettere sul passato per capire quale è stata, fino ad oggi,
l‟evoluzione dei database e cosa ha portato alla nascita del database tuning.
Molte sono le domande che possiamo farci in proposito. Come erano
strutturati i primi database? Chi ha ideato i modelli su cui si basa oggi la
progettazione di una base di dati? Come si sono evolute le esigenze degli
utenti negli anni? In che modo interviene il tuning per ottimizzare le
prestazioni di un database?
1.1. CENNI STORICI SUI DATABASE.
La nascita del concetto di database o base di dati risale a molti anni prima
dell‟avvento dei calcolatori, quando le informazioni venivano scritte e
memorizzate in forma cartacea. Archivi, schedari, rubriche possono
tranquillamente essere considerate le antenate delle moderne basi di dati.
Con il passare degli anni e con l‟evolversi della tecnologia, il termine
database ha assunto un significato sempre più legato all‟aspetto
informatico. Le prime forme di database elettronici risalgono agli anni
Sessanta, quando il miglioramento dei calcolatori permise la creazione di
una serie di “raccolte dati” destinate a varie applicazioni. Negli anni
9
seguenti, gli informatici si concentrarono a perfezionare e ottimizzare i
programmi di gestione e non apportarono nessun miglioramento alla
struttura usata per organizzarli.
Nel 1968 l‟IBM sviluppò il primo ambiente di gestione soprannominato
“IMS”. I dati erano strutturati in forma gerarchica. Al caricamento veniva
visualizzato il primo dato e un collegamento al dato successivo. Per
selezionare un gruppo di informazioni o trovarne una in particolare,
l‟utente doveva scorrere l‟intero archivio in sequenza fino a trovare
l‟informazione desiderata. Non esistevano query di ricerca. Anche le query
più semplici richiedevano, per le risorse dell‟epoca, notevoli quantità di
memoria e di tempo.
Qualche anno dopo, nel 1971, per opera di Charles W. Bachman, fu
lanciato sul mercato un altro standard, che prese il nome di "Approccio
Codasyl”. L‟Approccio Codasyl supportava una struttura dati a rete ma
per il resto era molto simile all‟IMS. I database di entrambi gli standard
furono classificati, per il loro funzionamento, con il nome di database
navigazionali.
La vera idea innovativa, nacque solo più tardi, nel 1973. Un dipendente
dell‟IBM, Edgar F. Codd durante il suo lavoro di ricerca per il prototipo
del nascente harddisk, si rese conto che l‟approccio Codasyl era
inefficiente se applicato alla nuova tecnologia. Codd capì che per
migliorare le prestazioni era necessario concentrarsi non solo sui
10
programmi di gestione ma anche, soprattutto, sull‟organizzazione dei dati
da gestire. Formulò diverse teorie, scrisse trattati, distinse la struttura
logica del database dalla sua realizzazione fisica, definì le regole per la
creazione del Modello Relazionale e per la sua normalizzazione. In pratica
gettò le basi per la nascita dei database relazionali e del linguaggio SQL.
Le sue idee ebbero un esito positivo nella comunità scientifica: non solo
l‟IBM ma anche diverse università italiane, puntarono su di lui e crearono
il “System R”, predisposto per la gestione dei dati in una monotabella e
successivamente “SQL/DS”, “DB2”, “INGRES” ed un precursore di
Oracle. Questi nuovi sistemi permisero per la prima volta di suddividere i
dati in tabelle separate.
L‟idea di Codd fu talmente buona che, tre anni dopo, Peter Chen la
perfezionò proponendo il suo Modello E/R (Entity-Relationship), valido
ancora oggi. Questo modello prevede tre livelli di progettazione dei
database (a differenza dei due definiti da Codd): il livello concettuale, il
livello logico e il livello fisico. Chen introdusse anche il concetto di Entità
(oggetti astratti o concreti) e Relazioni (legami tra le entità).
1.2. I DATABASE AI GIORNI NOSTRI.
Dagli anni Ottanta in poi, con la diffusione dei personal computer e la
standardizzazione del linguaggio SQL, i programmi di gestione dei
11
database relazionali (RDBMS) sono proliferati: Oracle, MySql, Microsoft
Access, DB2 ne sono solo alcuni esempi.
L‟uso recente di internet ha poi contribuito a centrare l‟attenzione della
comunità su nuove problematiche. Il modello di Chen è stato, negli anni
Novanta, affiancato dal “Modello Client/Server” che permette, non solo
una migliore ripartizione delle funzioni tra database e applicativo ma anche
la possibilità di gestire il sistema attraverso la rete. L‟uso dei database in
remoto, prima con le pagine ASP, poi, soprattutto, con il linguaggio PHP,
ha consentito ai dipendenti delle aziende di accedere a informazioni prima
inaccessibili ed ha anche permesso alle persone comuni di poter usufruire
di servizi prima inesistenti. Si potrebbero citare tantissimi esempi:
l‟accesso ai dati di una filiale dagli uffici della sede centrale di un‟azienda,
la creazione di siti e-commerce, i sistemi di pagamento online, i social
network, i forum di discussione, i guestbook personali. Dietro ognuna di
queste cose c‟è un server che interroga una base di dati e soddisfa le
richieste dell‟utente.
Quelli che erano i requisiti fondamentali di un database (l‟affidabilità, la
struttura semplice, la gestione di dati alfanumerici, le transazioni brevi, la
condivisione e la gestione delle query più complesse) si sono rivelati, in
diversi campi di applicazione, insufficienti.
Ispirati dall‟evoluzione delle tecniche di programmazione, gli informatici
hanno, quindi, ideato un nuovo modo di concepire le basi di dati: il
12
modello a oggetti. Questo nuovo tipo di database, ancora oggi poco
diffuso, permette agli utenti di creare e gestire direttamente non solo
informazioni alfanumeriche, ma qualsiasi altro tipo di oggetto come foto,
video, brano musicale, ecc.
Durante tutto questo processo di innovazione, gli utenti si sono
velocemente abituati ad avere le informazioni che vogliono in pochi
secondi, indipendentemente dalla quantità di dati che il database deve
gestire. L‟efficienza è tornata ad essere quindi il fattore di principale
importanza e di conseguenza anche il database tuning.
1.3. CHE COS’È IL DATABASE TUNING?
“Database tuning is the activity of making a database application run more
quickly. „More quickly‟ usually means higher throughput though it may mean
lower response time for some applications. To make a system run more quickly,
the database tuner may have to change the way applications are constructed, the
data structures and parameters of a database system, the configuration of the
operating system, or the hardware” (Shasha/Bonnet 2004, XIX).
Con database tuning si intende, come sopracitato, una serie di operazioni
pianificate a medio o lungo termine, con le quali si cerca di migliorare al
massimo le prestazioni di un database in un ben predefinito ambiente di
lavoro.
13
Ciò significa che, per ottenere le massime prestazioni, non basta operare sul
database, ma bisogna andare ad agire anche sul DBMS, sul sistema
operativo e sull‟hardware in uso.
Molte persone, condizionate dal significato inglese di tuning, lo associano
alla semplice ottimizzazione ma, in realtà, si tratta di due concetti distinti.
L‟ottimizzazione è un‟operazione che viene svolta nella fase finale della
progettazione logica o direttamente sulla struttura fisica del database. Serve
a migliorare il suo funzionamento a prescindere dal contesto in cui dovrà
poi funzionare.
Il tuning invece è qualcosa di più. Si tratta di un insieme di attività che
coprono l‟intera vita del database, dallo studio del contesto iniziale
all‟attività costante di monitoraggio dei flussi di dati. Le migliorie apportate
al database tramite il tuning non risultano sempre efficaci se applicate in
altri contesti.
1.4. I PRINCIPI FONDAMENTALI
Proprio per come è stato definito, studiare il tuning diventa estremamente
complicato. Non ci sono soluzioni decontestualizzate che producono
risultati ottimali, stabilire delle dettagliate regole di comportamento per
situazioni predefinite è difficile. L‟efficacia di ogni scelta va valutata caso
per caso. Per questo motivo, ciascun amministratore deve sempre tenere
presente cinque principi cardine e agire di conseguenza.
14
Il primo principio è stato riassunto, con la frase: “pensare globalmente e
agire a livello locale (Trad. Shasha/Bonnet 2004, 2)”. Ciò significa che, un
buon amministratore, pur avendo ben presente la situazione complessiva del
database, per fare un buon tuning deve essere in grado di individuare i
piccoli cambiamenti necessari alla base di dati per ottenere grossi
miglioramenti prestazionali. Un intervento che stravolge la natura del
database, andando a riprogrammare anche parte degli aspetti prima
funzionanti, non è considerato un buon tuning. Tale intervento, non solo
vanificherebbe lo studio del problema ma ne produrrebbe di nuovi e
imprevisti.
Il tipico problema con cui si ha a che fare quando si lavora con i database e,
in generale con l‟informatica, è quello soprannominato bottleneck o “collo
di bottiglia”. In pratica, qualsiasi progetto strutturato in più componenti è
considerato efficiente nella misura in cui lo è il suo componente peggiore.
Nel database non serve cambiare tutto, ma solo migliorare o sostituire i
componenti difettosi.
Il secondo principio, “Il partizionamento per risolvere i colli di bottiglia
(Trad. Shasha/Bonnet 2004, 2)”, ci dà una guida su come agire in queste
particolari situazioni.
Se una componente è satura, si possono scegliere due strade diverse: o si
suddividono le informazioni smistandole in più risorse così da riuscire a
15
gestire tutte le partizioni senza che nessuna crei sovraccarico, o si
suddividono le informazioni frazionando la loro gestione nel tempo.
Prima di procedere al partizionamento è sempre bene provare l‟alternativa
più semplice: controllare la struttura e il codice del componente difettoso ed
eseguire, se possibile, le opportune correzioni per eliminare alla radice il
collo di bottiglia. Ad esempio, se abbiamo una query che non usa gli indici
o li usa male è sempre meglio riscrivere la query che partizionare la tabella
a cui fa riferimento.
Il partizionamento, infatti, è una di quelle scelte che non sempre produce un
aumento di prestazioni. Va valutato il singolo caso. Potrebbe succedere che
i costi accessori dovuti alla gestione delle partizioni siano maggiori del
costo, in termini di prestazioni, del bottleneck.
Il terzo principio fondamentale da seguire riguarda un aspetto tipicamente
umano. Davanti ad un obiettivo da raggiungere, il primo passo è sempre il
più ‘costoso’. Forse consapevolmente o forse inconsciamente, l‟essere
umano trasmette questa caratteristica anche alle sue creazioni. Un esempio
esaustivo potrebbe essere il processo produttivo di un‟azienda di manufatti
in vetro. La realizzazione del primo manufatto, con l‟accensione iniziale del
forno e il raggiungimento della temperatura adatta, richiede un costo di
risorse e di tempo molto più alto rispetto alla produzione dei manufatti
successivi.
16
Gli elaboratori non fanno eccezione. Si pensi al processo di bootstrap, alla
lettura di un‟informazione dal disco o anche all‟attivazione di una
connessione di rete. La prima fase è sempre la più costosa in termini di
risorse. Lo stesso principio vale per i database.
E ancora, l‟amministratore deve “dare al server quello che è del server.
(Trad. Shasha/Bonnet 2004, 2)”.
Una buona progettazione tiene accuratamente distinto il carico di lavoro del
database (il server), da quello che invece spetta al programma applicativo
(il client).
Prendiamo un‟interfaccia grafica che visualizza un dato inserito nella base
di dati e permette all‟utente di modificarlo e salvarlo. Caricare il server
delle funzioni di ricerca, visualizzazione, interazione e salvataggio
comporterebbe un inutile sovraccarico. La soluzione più giusta sarebbe
invece quella di affidare la visualizzazione e l‟interazione con l‟utente
all‟applicativo e coinvolgere il database solo per le restanti operazioni.
L‟ultimo principio, che un amministratore di database deve tenere
costantemente presente è che “deve essere pronto ad accettare dei
compromessi (Trad. Shasha/Bonnet 2004, 2)”.
Non esiste una soluzione efficace senza controindicazioni. Se progettiamo
un database che fa grande ricorso agli indici per migliorare l‟accesso alle
sue tabelle, dovremo tener conto che ogni indice richiederà un costo
aggiuntivo sia al processore sia alla memoria di buffer e fisica.
17
Trovare il giusto equilibrio non è mai facile ma è proprio il compito che si
ci prefigge con il tuning del database.
1.5. LIVELLI DI INTERVENTO
Le operazioni di ottimizzazione compiute da un amministratore che opera
tuning non solo devono tener conto di conoscenze che vanno oltre lo studio
dei database, abbracciando altri temi come la gestione dei buffer di
memoria o il sistema di archiviazione dei dati sul supporto fisico, ma
coinvolgono l‟intero sistema.
Cercando di organizzare e semplificare i vari aspetti coinvolti, è possibile
definire i seguenti livelli di intervento:
Hardware;
Sistema Operativo;
DBMS;
Applicativo
Modello Logico del Database
Modello Fisico del Database
Saper scomporre il problema e individuare i giusti livelli di intervento è
fondamentale se si vogliono ottenere dei miglioramenti reali e a lungo
termine.
Un intervento a livello hardware, consiste nel compiere tutte quelle scelte
che prevedono la modifica dei componenti fisici del sistema.
18
Principalmente tali modifiche riguardano l‟aggiornamento del processore,
l‟aggiunta di memoria principale e secondaria nonché l‟ottimizzazione dei
bus di sistema. La difficoltà nell‟operare le scelte di tuning su questo
livello consistono nella ricerca del miglior compromesso tra costo
hardware ed efficienza.
Per quanto riguarda il livello del Sistema Operativo, sarebbe necessario
fare una lunga premessa specificando, per ogni Sistema Operativo, i
relativi parametri di configurazione inerenti le procedure di tuning.
Premesso però che non è mai possibile eliminare tutti i colli di bottiglia
presenti nel sistema, alcuni strettamente legati alle limitazioni hardware, è
necessario sottolineare che proprio questo numero di questi parametri può
a sua volta rappresentare un bottleneck.
Considerando tale argomento meritevole di un più attento approfondimento
e impossibile da concentrare in poche righe, in questa trattazione si è
preferito affrontarlo con le dovute astrazioni. Si parlerà quindi di gestione
ottimizzata dei Threads, dell‟organizzazione del buffer di database e del
file system.
Intervenire con operazioni di tuning significa ottimizzare il software che si
occupa della gestione del database: il DBMS. Tali interventi vertono
principalmente su due aspetti della gestione: i locking delle transazioni e
l‟ottimizzazione del sistema di logging.
19
Altro aspetto importante è la configurazione dei programmi posti tra gli
utenti finali e il DBMS. Considerando che gli applicativi di norma vengono
eseguiti su macchine diverse da quelle che gestiscono il database server,
non solo sarà importante ottimizzare le interfacce eseguite dai client ma
anche la rete di connessione tra le macchine. Demandati i problemi inerenti
la scelta del giusto protocollo, dei giusti canali di comunicazione,
dell‟instradamento migliore dei pacchetti, dell‟ottimizzare degli algoritmi
che gestiscono le collisioni, ecc. ai livelli del modello ISO/OSI o TCP/IP
relativi alle reti, si ci concentrerà, in questo elaborato, sulle tematiche più
legate ai database quali i vantaggi e gli svantaggi dell‟utilizzo del modello
Client/Server inteso come modello Applicativo/Database Server e
l‟ottimizzazione del codice dei programmi applicativi.
Iniziare il tuning del database in fase di progettazione, può rappresentare
un notevole vantaggio per il sistema. Partire da un modello logico
ottimizzato ed efficiente significa creare un database predisposto ad
affrontare le situazioni critiche che potranno verificarsi durante il suo
funzionamento e persino evitarle.
Gli interventi sul database possono essere effettuati sui diversi modelli di
progettazione. Quando si interviene sul modello logico, in genere, si parla
di normalizzazione (ottimizzazione dei dati),
(creazione
di
ridondanza
per
20
migliorarementi
denormalizzazione
funzionali)
e
overnormalizzazione. (creazione di ridondanza per snellire le strutture
dati).
Gli interventi di questo tipo sono consigliati solo durante la fase di
progettazione anche se, l‟amministratore, può naturalmente ritenere farlo
anche successivamente. In questi casi deve però tenere conto che essendo
la struttura dati già funzionante, operare modifiche al livello logico
comporta dei costi aggiuntivi e alto rischio di perdita d‟integrità del
database.
Pratica più comune vuole, che il tuning si concentri sul modello fisico del
database, ovvero su interventi al codice SQL delle query a alle strutture di
database già funzionanti. Si può intervenire sul modello fisico in tanti
modi:
a) Attraverso l‟utilizzo di strutture distinte per la memorizzazione dei
dati;
b) Con l‟uso dei raw devices (se il database opera su sistemi UNIX);
c) Con l‟aggiunta di indici e informazioni temporanee alle tabelle;
d) Attraverso la valutazione della frequenza d‟accesso delle singole
strutture;
e) Attraverso il controllo e l‟eliminazione di indici inutili o inutilizzati;
f) Attraverso il controllo delle colonne degli indici affinché
mantengano dimensioni contenute;
21
g) Attraverso il controllo della quantità di dati di ogni tabella e, se
necessario, il partizionamento.
Ottimizzare il linguaggio SQL non è per niente facile. Una grossa parte di
bottleneck presenti nel sistema spesso è legata ad una programmazione
SQL superficiale e non ottimizzata. Ecco una veloce panoramica delle
situazioni da valutare e che si presentano più frequentemente ad un
amministratore:
a) l‟esistenza di selezioni generiche come “SELECT * FROM ..." in
quanto SQL impiega ulteriori risorse per decodificare il significato
dell‟ „*‟ in base alle tabelle inserite nel „FROM‟;
b) il mancato uso di indici nelle query che coinvolgono una notevole
quantità di dati, la presenza di indici disattivati di importanza
rilevante o l‟uso di indici non opportuni;
c) l‟ordine delle tabelle e delle condizioni di selettività nelle query;
d) l‟uso di elaborazioni statistiche mediante cicli al posto di sfruttare
le funzioni di gruppo standard;
e) l‟uso dei valori null e la loro corretta gestione;
f) la presenza e l‟efficacia delle selezioni annidate;
g) la presenza di operazioni che richiedano grandi quantità di memoria
(ad esempio ORDER BY, DISTINCT e GROUP BY in tabelle
molto voluminose);
22
h) la presenza, in generale, di codici la cui esecuzione prevede lunghe
transazioni.
In generale, bisogna comunque tenere presente che, la suddivisione a
livelli è una schematizzazione logica personale che è volta esclusivamente
a dare un ordine all‟esposizione di concetti che sono profondamente
interconnessi tra loro. Stabilire, pertanto, a quale livello appartenga una
specifica operazione, non è sempre facile ai fini del tuning vero e proprio.
23
2. TUNING DEL SISTEMA
Aver visto cos‟è il Database Tuning e quali sono i principi
fondamentali su cui esso si basa non basta se si vuole operare
concretamente tuning. Nei paragrafi seguenti vengono esposte,
sinteticamente, le nozioni di tuning fondamentali relative al livello
hardware, del Sistema Operativo, del DBMS e dei programmi
applicativi.
2.1. TUNING DELL’HARDWARE
La prima cosa che viene in mente, quando il funzionamento del
sistema sembra rallentato e poco efficiente, è di intervenire a livello
hardware per aumentare le risorse disponibili. Premettendo che non
sempre un intervento del genere comporta un effettivo miglioramento
di prestazioni, è evidente che la scelta del componente del sistema su
cui intervenire è di cruciale importanza per evitare costi inutili.
Preso ad esempio, un sistema che soffre di saturazione del buffer di
memoria, l‟acquisto e l‟aggiunta di RAM nel sistema rappresenta una
buona strategia di tuning? Supposto che il costo della RAM coincida a
15 €/Mb (costo non eccessivamente oneroso), non sappiamo se la
saturazione è effettivamente legata alla scarsa capienza di memoria o
24
alla larghezza di banda del suo bus. L‟aggiunta di RAM, mentre nel
primo caso rappresenta una buona soluzione di tuning, nel secondo
comporta un completo spreco di soldi. In realtà, un calo di prestazioni
non può essere mai attribuito esclusivamente all‟hardware. Per questo
motivo è difficile distinguere in maniera netta la necessità di tuning
sull‟hardware da quella di tuning sul software.
2.1.1. TUNING DEL SOTTOSISTEMA DI ARCHIVIAZIONE
Quando si parla di sistemi su larga scala, la gestione della memoria
secondaria viene implementata attraverso l‟uso di un array di dischi
(RAID) così da sfruttare le sue due principali caratteristiche: da un lato
il RAID crea nel sistema una certa tolleranza d‟errore, fault tollerance,
dovuta alla ridondanza dei dati introdotta nei dischi multipli; dall‟altro
aumenta il throughput del sistema perché consente l‟accesso simultaneo
a più dischi.
Un array di dischi insieme al software per configurare e gestire le
periferiche di archiviazione che lo compongono, costituiscono uno
Storage Subsystem.
Le operazioni di tuning sullo Storage Subsystem prevedono:
1. La configurazione dell‟array di dischi
2. L‟ottimizzazione della cache del controller
3. La ricerca di equilibrio nello Storage Subsystem
25
Esistono diversi livelli di RAID, ognuno con particolari vantaggi e
svantaggi. Per ottenere prestazioni ottimali, dobbiamo assicurarci, per
ogni tipo di file, di adottare il livello di RAID che soddisfi meglio le
esigenze ad esso associate.
2.1.1.1.
LIVELLI DI RAID
I livelli di RAID più importanti, ai fini del tuning, sono:
RAID 0: che divide i dati equamente tra due o più dischi con
nessuna informazione di parità o ridondanza (Striping). Le
operazioni di I/O vengono divise in blocchi di dimensioni
uguali e apportate equamente su tutti i dischi. Per questo
l'affidabilità del sistema è uguale all'affidabilità media dei
dischi diviso il numero di dischi presenti. Se un drive si guasta,
il file system non è in grado di gestire la perdita dei dati e la
sostituzione del componente danneggiato comporta la perdita di
tutti i dati del sistema. I vantaggi sono: il costo economico di
implementazione basso, le alte prestazioni in scrittura e lettura
grazie al parallelismo delle operazioni I/O dei dischi
concatenati. Gli svantaggi sono: la scarsa affidabilità e la non
fault tolerant.
RAID 1: che crea una copia esatta (mirror) di tutti i dati su due
o più dischi. È utile nei casi in cui mantenere la ridondanza è
più importante che usare i dischi alla loro massima capacità
26
complessiva. Il sistema ha come capacità massima quella del
disco più piccolo. Poiché ogni disco presenta la copia esatta
degli altri dischi, può essere gestito autonomamente in caso di
guasti e l'affidabilità aumenta linearmente rispetto al numero di
dischi presenti. RAID-1 incrementa allo stesso modo le
prestazioni in lettura, visto che le transazioni possono accedere
ai dati da un disco mentre gli altri sono occupati. I vantaggi
sono: l‟aumento lineare di affidabilità in base ai mirror
implementati, la fault tolerant, la velocità di lettura che dipende
dal disco più veloce. Gli svantaggi sono: l‟overhead legato ai
mirror, l‟alto costo hardware legato all‟acquisto di numerosi
dischi dedicati interamente alla ridondanza e la velocità di
scrittura che dipende sia dal disco più lento sia dal numero di
dischi collegati.
RAID 5: una delle implementazioni più popolari in hardware e
in software, che divide i dati in blocchi e stripe e distribuisce le
informazioni di parità, per rotazione, in tutti i dischi
appartenenti al RAID. Il blocco di parità non viene considerato
durante le operazioni di lettura e viene usato solo in caso di
errore per ricostruire le parti danneggiate. Se a danneggiarsi è
un intero disco, RAID 5 permette, attraverso tutti gli altri
blocchi (di dati e di parità) il recupero integrale del contenuto
27
del disco guasto. Il sistema esterno può comunque continuare a
operare in lettura e scrittura anche se con un calo di prestazioni.
Il numero di blocchi di parità per ogni stripe è direttamente
proporzionale al valore di fault tollerance. Se un blocco di
parità per ogni stripe permette il recupero del contenuto di un
intero disco in caso di guasto, più blocchi di parità permettono
il recupero dei dati in caso di guasti simultanei di più dischi. Le
implementazioni con doppia parità sono dette anche RAID 6. I
vantaggi sono: la distribuzione dei blocchi di parità e le
operazioni di lettura più veloci. Lo svantaggio consiste nelle
operazioni di scrittura più lente dovute al calcolo del blocco di
parità.
RAID 10: è un sistema RAID 1+0 ovvero un insieme di
RAID1 gestiti in RAID0. Poiché i dischi fisici sono organizzati
in una serie di gruppi RAID 1, il danneggiamento di un disco
all‟interno di un singolo gruppo RAID 1 non comporta nessuna
perdita di dati. In caso però tutti i dischi di un gruppo si
danneggino, anche i dati negli altri gruppi sono inutilizzabili.
Il sistema di database deve gestire tre tipi di file:
File di Log
File Temporanei
File di Indici o Dati
28
Nel caso dei file di log, è consigliabile utilizzare RAID 1 perché
questa struttura garantisce fault tollerance mantenendo un alto
livello di throughput in scrittura. Se i log richiedono scritture brevi
e frequenti può essere più appropriato anche l‟uso di RAID 10.
Per i file temporanei è consigliato, invece, l‟uso di RAID 0. Il
sistema può operare in lettura e in scrittura velocemente senza che,
per la natura di questi file, l‟eventuale perdita di informazioni
influisca sulle performance del sistema.
Per i file di dati o di indici la fault tollerance è fondamentale. Le
prestazioni migliori si ottengono utilizzando RAID 5. Per questo
tipo di informazioni, infatti, la frequenza di lettura è molto più alta
rispetto a quella di scrittura e le scarse performance di RAID 5 per
questo tipo di operazioni, possono essere mascherate attraverso un
buon utilizzo della cache del controller e non influiscono
sensibilmente sul funzionamento globale.
Fig. 1: Struttura RAID0 a due dischi.
29
Fig. 2: Struttura RAID1 a due dischi
Fig. 3: Struttura RAID5 a quattro dischi
Fig. 4: Struttura RAID 1+0 a sei dischi
2.1.1.2.
CHACHE DEL CONTROLLER
L‟ottimizzazione della cache del controller si basa sui principi di
read-ahead e write-back.
Read-ahead considera che il tempo necessario a leggere un blocco
dati dipende più dal tempo impiegato dal braccio meccanico del
30
disco per raggiungerlo che dal tempo di lettura vero e proprio.
Read-ahead prova a ridurre lo spreco dovuto a questo limite
hardware implementando l‟idea di non interrompere il processo di
lettura non appena si raggiunge la fine del blocco richiesto ma di
continuare a leggere i blocchi successivi inserendoli nella memoria
cache. Se questa tecnica risulta efficace per l‟accesso sequenziale ai
dati, diventa invece inefficiente quando si tratta di accessi random.
Write-back, ipotizza invece conclusa a buon fine l‟operazione di
scrittura su disco con l‟inserimento dell‟informazione nella cache.
Spetta al controller garantire che l‟operazione vada effettivamente a
buon fine. Le eventuali saturazioni possono essere gestite attraverso
una serializzazione delle richieste e una distribuzione delle
medesime in più intervalli di tempo.
2.1.1.3.
IL TUNING DELLO STORAGE SUBSYSTEM
Premesse tutte le considerazioni esposte fino a questo momento in
materia, l‟amministratore di un sistema di database, durante il
tuning hardware deve trovare il miglior bilanciamento possibile tra
le dimensioni dello Storage Subsystem e le sue prestazioni. Per
farlo dovrà tenere presente che:
Nonostante l‟uso dello Storage Subsystem permetta di
considerare un insieme di dischi come un unico disco dalle
prestazioni elevate, aumentare il numero dei componenti del
31
sottosistema comporta un aumento del flusso di dati legato
alla sua gestione (log e index files).
Ogni componente avrà le sue caratteristiche hardware quali
seek time, rotational latency, cache size, ecc che vanno a
influire sulle prestazioni complessive del sottosistema.
Il bus di interconnessione SCSI, scelto in tutti i grossi
sistemi di database perché permette l‟aggiunta e la rimozione
dei dischi a sistema funzionante, ha comunque dei limiti per
quanto riguarda la tolleranza d‟errore.
2.1.2. MODIFICA E AGGIUNTA DI COMPONENTI HARDWARE
Ci sono tre principali fattori su cui intervenire quando si parla di
aggiunta di nuovo hardware: la memoria, i dischi e i processori.
2.1.2.1.
LA MEMORIA
Aggiungere memoria al sistema, oltre a essere l‟operazione, a
livello hardware più economica, comporta un aumento del buffer
pool1 e delle prestazioni di sistema. La memoria aggiuntiva riduce
il carico sui dischi e aumenta l‟ hit ratio2 mantenendo la stessa
paginazione. Si potrebbe intervenire direttamente sulla memoria di
cache del disco, ma in questo caso, la memoria extra verrebbe
1
Buffer pool: area di memoria in cui le pagine del database vengono lette, modificate e gestite durante
l'elaborazione.
2
Hit ratio: indice ottenuto dalla formula (# accessi logici – # accessi fisici) / (# accessi logici)
32
utilizzata esclusivamente per velocizzare le operazioni di quel disco
e non di tutto il sistema.
2.1.2.2.
I DISCHI
L‟aggiunta di dischi, in linea di principio, è indicata quando i dati
da gestire superano la capacità dei dischi in uso. In realtà i database
considerano la larghezza di banda un fattore più rilevante rispetto
alla capacità di archiviazione. Per migliorare la larghezza di banda
si può intervenire in due modi: o si aumenta la memoria di cache
dei singoli dischi o si acquistano nuovi dischi da inserire nello
Storage Subsystem. Quest‟ultima ipotesi comporta una serie di
vantaggi aggiuntivi, quali:
a) la possibilità di gestire i log su dischi separati garantendone
l‟accesso sequenziale in scrittura e lettura.
b) la possibilità di modificare il livello RAID in uso.
c) La possibilità di partizionare tabelle o applicazioni di grandi
dimensioni su diversi dischi logici per non sovraccaricare un
unico disco.
2.1.2.3.
I PROCESSORI
Che un sistema di database riesca a sovraccaricare un processore
moderno sembra a primo impatto quasi impossibile. Eppure si tratta
di qualcosa di estremamente concreto. Anche se un sistema di
database non opera sui dati come può ad esempio fare un
33
applicativo audio-visivo, le risorse di elaborazione necessarie al
database sono direttamente proporzionali ai dati da gestire. Quando
la mole di dati supera l‟ordine di grandezza del terabyte, l‟ipotesi di
aggiungere altri processori al sistema non appare più così insensata.
Il modo più economico per creare un sistema multiprocessore è
quello di collegare più sistemi informatici indipendenti tra loro con
una rete ad alta velocità. Questa soluzione può andare bene quando:
1. Una parte dell'applicazione può essere demandata ad un altro
sistema per essere lavorata separatamente.
2. Le applicazioni possono essere suddivise tra applicazioni di
elaborazione e di supporto. In questo caso si demandano le
applicazioni di supporto (che non modificano il database) agli
altri sistemi che, scaricato periodicamente il database dalla
rete (backend), eseguono in locale i compiti a loro assegnati.
3. Nel sistema ci sono molte transazioni di sola lettura e poche
transazioni in scrittura. E‟ difficile, infatti, garantire
l‟atomicità delle transazioni in scrittura ed evitare che si
creino sovraccarichi legati agli aggiornamenti.
4. Sia possibile il partizionamento dei dati. L'obiettivo da
perseguire è semplice: ogni grande struttura dovrebbe essere
partizionata in base al carico di lavoro ad essa associata e poi
suddivisa uniformemente su tutti i sistemi. In alternativa al
34
carico di lavoro è possibile considerare come parametro di
partizionamento il fattore tempo3.
Per il partizionamento delle tabelle in particolare, si parlerà di
Hash Partitioning o di Range Partitioning. Il primo metodo
partiziona i dati suddividendoli in base al valore di un record
di riferimento A e attraverso il calcolo di una funzione h detta
funzione di hash; il secondo metodo smista i dati della
struttura dividendoli tra quelli che contengono l‟attributo di
riferimento e quelli che non lo contengono.
5. E‟ possibile inserire una memoria condivisa con cui gestire i
dati necessari alle applicazioni che, a causa della loro
interconnessione, non è possibile partizionare.
Ci sono tre diversi tipi di sistemi multiprocessore:
1. Sistemi Tightly Coupled: che dispongono di diversi
processori ma di un'unica memoria logica principale e un
unico set di dischi.
2. Sistemi Shared Nothing: in cui ogni processore è dotato di
propria memoria principale e proprio set di dischi. Il lavoro
viene distribuito uniformemente e gli applicativi suddivisi in
una serie di sottoapplicativi indipendenti tra loro.
3
fattore tempo: ogni sistema dovrebbe contenere i dati legati ad un intervallo di tempo specifico.
35
3. Sistemi Shared Disk: in cui ogni processore ha la propria
memoria principale, ma set di dischi condiviso. Questa
architettura andrebbe bene al posto dei sistemi tightly coupled
quando gli applicativi tendono a produrre sovraccarichi ai
singoli sistemi.
Fig. 5: Rappresentazione schematica dei tre tipi di sistemi multiprocessore
2.2. TUNING DEL SISTEMA OPERATIVO
Per parlare di tuning a livello di sistema operativo è necessario fare una
panoramica dei concetti fondamentali riguardanti il suo funzionamento e
la multiprogrammazione.
Le performance di un sistema di database sono strettamente legate a quelle
del sistema operativo ospitante. Da qui la necessità di ottimizzare i threads
36
e il loro sistema di gestione e la necessità di un corretto utilizzo del buffer
del database e del file system.
2.2.1. GESTIONE DEI THREADS
Qualsiasi processo del sistema operativo viene suddiviso in più threads
che vengono poi eseguiti in ordine sequenziale o contemporaneo. Se
fino a qualche anno fa il multithreading si basava esclusivamente su
tecniche a divisione di tempo e scheduling ogni thread veniva
eseguito per un certo periodo di tempo, poi passava il controllo ad un
altro thread con l‟avvento dei sistemi multicore il parallelismo ha
assunto una connotazione reale. In termini di prestazioni, il tempo
necessario ad ogni thread è dato dal tempo di esecuzione delle sue
istruzioni, sommato al tempo necessario a passare il controllo al thread
successivo, sommato a quello legato ai tempi di attesa per eventuali
lock delle risorse comuni.
Il tuning del sistema operativo si concentra sull‟ottimizzazione dello
Scheduling e delle Priorità per ridurre appunto il tempo di esecuzione di
ogni thread.
2.2.1.1.
TUNING DELLO SCHEDULING
Per ottimizzare lo Scheduling ci sono due strategie:
1. Scegliere un sistema operativo che usi tecniche di switching
(dei thread) poco costose in termini di prestazioni;
37
2. Ridurre al minimo il numero di switches. Uno switch è
inevitabile quando l'applicazione esegue una richiesta di I/O;
per tutti gli altri casi, in cui il suo uso sarebbe opzionale, è
meglio evitarli. Questo vuol dire ridurre al minimo gli
interrupts e dare priorità all‟aumento del throughput
piuttosto che ai tempi di risposta. Il compromesso usato nei
moderni microprocessori consiste nell‟assegnare a ogni
thread un tempo di esecuzione massimo di un secondo,
valore sufficiente, nella maggior parte dei casi, per terminare
correttamente l‟esecuzione di un thread.
2.2.1.2.
GESTIONE DELLE PRIORITÀ
In un sistema che fa uso di priorità, le prestazioni del database
risultano influenzate negativamente quando:
1. I processi del database vengono eseguiti con una priorità più
bassa rispetto agli altri processi. Se le risorse disponibili
diminuiscono, il valore delle prestazioni del database precipita
a zero.
2. I threads vengono eseguiti tutti con la stessa priorità
generando i cosiddetti fairness4.
3. Si verificano casi di inversione di priorità. Dati tre thread con
priorità decrescente T1, T2 e T3, ipotizzato che al tempo t1
4
fairness: situazione che si verifica quando un thread pronto all‟esecuzione non può essere avviato
perché alcune risorse necessarie sono bloccate da altri thread.
38
venga avviata l‟esecuzione di T3 e che T3 richieda
immediatamente il lock di una risorsa in comune con T1, al
tempo t2 l‟esecuzione di T1 è pronta per essere avviata ma la
risorsa a lui necessaria è ancora bloccata. T1 e T3 vengono
deschedulizzati T3 per la sua bassa priorità e T1 per
mancanza di risorse e viene avviato ed eseguito il thread T2.
In definitiva, la superiore priorità di T1 non viene rispettata e
avviene uno “scavalcamento” da parte di T2.
Fig. 6: Schema che illustra il fenomeno dell‟inversione di priorità
In definitiva si avrà che, per evitare l‟inversione si dovrebbero
eseguire i threads tutti con la stessa priorità, per evitare i fairness, si
dovrebbe diversificarla il più possibile. Ecco nuovamente la
39
necessità di trovare un compromesso: la priorità dinamica. L‟idea
è quella di assegnare, ad ogni thread in avvio, priorità massima e
poi decrementarla ogni volta che il thread blocca una risorsa.
2.2.1.3.
Molti
LA MULTIPROGRAMMAZIONE
amministratori
credono
che
un
alto
livello
di
multiprogrammazione sia la soluzione migliore per i loro sistemi. È
vero che l‟aumento del numero di thread riduce i cicli inattivi dei
processori e quindi gli sprechi, ma è anche vero che provoca un
aumento della possibilità che il sistema risenta di un calo
prestazionale. Le cause di questo calo dipendono da:
la quantità di RAM usata dagli utenti. Se supera la quantità
di memoria reale il sistema compensa con la paginazione
dell‟area riservata ai processi, il buffer e la memoria
secondaria.
i conflitti di Lock derivati da un eccessivo numero di
transazioni eseguite contemporaneamente.
Un metodo valido per prevenire entrambi i casi consiste
nell‟incrementazione progressiva della concorrenza. Il metodo è
strutturato in tre fasi distinte:
1. Viene avviato il sistema utilizzando il minimo di transazioni
simultanee consentite.
40
2. Viene aumentato tale limite di uno e misurato l‟andamento
delle prestazioni del sistema.
3. Se le prestazioni subiscono un miglioramento, si procede ad
ulteriori aumenti del limite di concorrenza, altrimenti il
limite trovato è il massimo consentito senza calo di
prestazioni.
La grossa limitazione del metodo appena esposto è che non può
essere applicato ai database se non a fronte di un continuo
monitoraggio del sistema. Gerhard Weikum e i suoi studenti, sono
riusciti a trovare un metodo meno oneroso attraverso il calcolo
dell‟indice di criticità del sistema. Si tratta del rapporto tra il
numero di lock delle transazioni e il numero di lock totali. Quando
questo indice supera il 23% la possibilità di crash del sistema
diventa abbastanza concreta. Secondo Gerhard spetterebbe al
DBMS gestire questo indice ma, attualmente, nessun DBMS
implementa al suo interno le funzioni necessarie.
2.2.2. DATABASE BUFFER
E‟ stato visto che l‟accesso dei dati dal disco richiede, per
configurazione dell‟hardware, molto più tempo rispetto all‟accesso
degli stessi dati sulla RAM. Per minimizzare le prestazioni, il database
deve cercare di ridurre al minimo il numero di accessi alla memoria
secondaria. Ricordando la teoria sulle transazioni simultanee, si può
41
destinare una certa porzione di memoria virtuale, conosciuta come
buffer, database buffer o database cache, alla condivisione dei dati in
comune. L'impatto del buffer sul numero di accessi fisici dipende da tre
fattori:
1. Letture e scritture logiche: Quando le operazioni di lettura e
scrittura del sistema operano sulle pagine di memoria che fanno
parte del buffer.
2. Sostituzione delle pagine: A seguito di un‟operazione di scrittura
su disco può verificarsi la necessità di ricaricare alcune pagine
nel buffer. E‟ possibile minimizzare questa situazione attraverso
una buona gestione delle scritture da parte del database tuner5.
3. La paginazione del Sistema Operativo: Se lo spazio dedicato al
buffer supera lo spazio disponibile della memoria RAM il
sistema operativo integra lo spazio mancante procedendo alla
paginazione della memoria secondaria. Il tuner deve assicurarsi
che questo non succeda.
L‟aspetto importante consiste dunque nel capire come la presenza del
buffer influisce sul numero di accessi logici e fisici al disco. Il valore
del parametro Hit Ratio, presentato nel paragrafo 2.1.2.1, non solo
serve da indicatore del livello di ottimizzazione del sistema, ma aiuta
l‟amministratore nella scelta della dimensione di buffer più opportuna.
5
database tuner: inteso come i moduli software del DBMS che si occupano dell‟ottmizzazione
automatizzata della base di dati e, in caso di errori o rallentamenti, aiutano ad individuarne le cause.
42
Fig. 7: Schema di un Database Buffer
In generale, per raggiungere le prestazioni ottimali, esistono due
strategie:
1. Aumentare la dimensione del buffer fino ad ottenere un
appiattimento dell‟incremento di Hit Ratio mantenendo al
contempo basso il numero di sostituzioni delle pagine e la
paginazione dei dischi.
2. Acquistare e aggiungere RAM.
2.2.3. FILE SYSTEM
I File System permettono agli utenti di creare, eliminare, leggere e
scrivere flussi di informazioni organizzati in file. I principali parametri
regolabili per quanto riguarda il file System sono:
43
La dimensione dei chunks6 del disco. Alcuni file System li
chiamano extent. Poiché molte query tendono a scansire solo
parti di file, per migliorare le prestazioni è bene specificare le
dimensioni della traccia relativa a ogni extent. Se si tratta di file
di grosse dimensioni è consigliato l'uso di extents grandi.
Quando invece l'accesso ad un file è completamente casuale un
piccolo extent riduce lo spreco di spazio.
I fattori d‟uso sulle pagine del disco. Più alto è il fattore di
utilizzo, più completa può essere la pagina al momento in cui si
verificano degli inserimenti. Se si tratta di oggetti, ad esempio
tabelle, che prevedono numerosi aggiornamenti con a seguito un
aumento di dimensioni è bene mantenere un fattore di utilizzo
basso, in caso contrario un valore alto aiuta le scansioni da parte
delle query.
Il numero di pagine che possono essere precaricate. Il
prefetching è utile per le query che scansionano i file. A meno di
una piccola quantità di RAM è bene che il numero di pagine
precaricate coincida quasi interamente a una traccia del disco.
Il numero di livelli di back-link per accedere a una determinata
pagina. Questo punto assume particolare importanza per i
6
chunks: blocchi di memoria contigua in un file system riservati per un file. Quando si inizia a
scrivere un file, viene allocato un intero chunk. Quando si scrive di nuovo sul file, i dati vengono
scritti a partire dagli ultimi. Questo riduce la frammentazione dei file.
44
sistemi basati su Unix perché la struttura indicizzata dei file
prevede l‟inserimento di molti più back-link nelle pagine finali
piuttosto che nelle pagine iniziali di un file.
2.3. TUNING DEL DBMS
Le applicazioni di database dividono il loro lavoro in transazioni. Le
transazioni, come i threads, possono essere eseguite in parallelo o in modo
sequenziale purchè venga rispettata la loro proprietà di atomicità. Anche
per le transazioni valgono quindi le considerazioni esposte per il
multithreads e la multiprogrammazione: operare tuning equivale a trovare
il punto di incontro tra performance e concorrenza. Il tuning del DBMS si
basa su due principali aspetti: la gestione dei lock delle transazioni ed il
sistema di logging e recovery del database.
2.3.1. LOCKING TUNING
Per prima cosa è necessaria una breve panoramica su come funzionano
i lock nelle transazioni. Ci sono due tipi di lock: i write lock, che
garantiscono a una transazione l‟accesso esclusivo alla risorsa con la
possibilità di modificarne il contenuto, e i read lock che non bloccano
l‟accesso in lettura sulla risorsa alle altre transazioni ma bloccano
qualsiasi write lock.
Il sistema di gestione si basa poi su due semplici regole:
45
1. Una transazione deve ottenere il lock della risorsa X prima di
accedervi.
2. Se una transazione vuole ottenere il lock della risorsa Y deve
prima rilasciare la risorsa X.
Per quanto riguarda il tuning, l‟intervento dell‟amministratore può
consistere in:
Operare la cosiddetta Transaction Chopping, ovvero scomporre
una transazione articolata in più transazioni semplici;
Ridurre i lock eliminando quelli non necessari;
Favorire i lock condivisi piuttosto che i lock esclusivi;
Ridurre il tempo di ogni lock per migliorare i tempi di attesa tra
le transazioni.
Definire la granularità dei lock;
2.3.1.1.
TRANSACTION CHOPPING
La prima domanda che deve farsi l‟amministratore è: quanto deve
essere lunga una transazione? Per rispondere a questa domanda si
deve tener presente che:
1. Più lunga è la transazione, più sono le risorse necessarie alla
sua esecuzione e più facilmente può capitare che un‟altra
transazione rimanga in attesa di qualche risorsa bloccata;
46
2. Più lunga è l‟esecuzione della transazione A più tempo
un‟altra transazione dovrà aspettare per il rilascio delle
risorse bloccate da A.
Transazioni corte sono quindi migliori di transazioni lunghe in
termini di prestazioni. Ecco perché la necessità di cercare di
scomporre le transazioni più corpose in transazioni atomiche senza
perdere il livello di isolamento originale. A volte, per riuscirci, può
essere necessario riformulare l‟intera transazione.
2.3.1.2.
RIDURRE I LOCK
E‟ importante eliminare i lock superflui nel sistema. A questa
categoria fanno parte i lock generati quando viene eseguita una
transazione alla volta e quelli generati nonostante tutte le
transazioni siano di sola lettura.
2.3.1.3.
LA GRANULARITÀ DEI LOCK
I moderni sistemi di database permettono di distinguere e
specificare la granularità dei lock. In ordine decrescente di
granularità ci sono: i record-level lock, i page-level lock e i tablelevel lock. Un record-level lock impedirà l‟accesso simultaneo di
più transazioni a un determinato record, un page-level lock
impedirà l‟accesso a tutti i record che fanno parte di una
determinata pagina; un table-level lock opererà allo stesso modo
47
riferendosi però a tutte le pagine che compongono una specifica
tabella e quindi a tutti i record di quella tabella.
Utilizzare lock con granularità più alta, basandosi su una prima
impressione, sembrerebbe la scelta migliore. Un record lock,
permettendo a più transazioni che operano su record della stessa
pagina di essere eseguite contemporaneamente, dovrebbe, infatti,
produrre un miglioramento di prestazioni. In realtà, ogni
transazione difficilmente richiede l‟accesso a singoli record
all‟interno di una pagina e la scelta di usare record lock porterebbe,
al sistema, un inutile aumento di richieste di lock da gestire con
tutte le relative conseguenze in termini di tempi d‟attesa delle
singole transazioni.
In definitiva, per evitare i deadlock7 e di ridurre l‟overhead, è
consigliato l‟uso di page-level lock per transazioni brevi e tablelevel lock per transazioni più lunghe.
2.3.2. LOGGING TUNING
All‟interno del sistema possiamo classificare le transazioni in base al
loro stato. Una volta attivata una transazione, questa potrà o essere
eseguita o interrotta. A livello teorico gli effetti delle transazioni
eseguite dovrebbero essere permanenti mentre le transazioni interrotte
7
Deadlock: situazione di stallo tra più transazioni in cui ognuna aspetta il verificarsi di qualcosa per
sbloccare il proprio lock.
48
non dovrebbero lasciare traccia. Questo principio, apparentemente
facile da rispettare, in realtà dipende dal momento in cui la transazione
viene interrotta. Basta pensare a una transazione che prevede la
modifica di una serie di record e che viene interrotta a pochi istanti
dalla fine. Gli effetti di tale transazione restano nel sistema che, a
seguito dell‟interruzione, presenta record incongruenti e modificati
irreversibilmente. Tali record potrebbero diventare la causa di ulteriori
errori e interruzioni.
In generale si distinguono le interruzioni hardware, causate da un
malfunzionamento fisico, e le interruzioni software, causate da errori di
programma.
E‟ stato visto che, la risoluzione dei problemi legati alle interruzioni
hardware, prevede l‟uso di sistemi fault tollerance. Per quanto riguarda
invece le interruzioni software, la soluzione è il sistema di logging e
recovery. In pratica, quando si presenta un‟interruzione, il sistema deve
poter ripristinare il dato originale e poi decidere se riavviare o meno la
transazione che ha generato l‟interruzione. Per farlo si serve di appositi
file di log.
Si tratta di file che contengono una serie di azioni organizzate in record
di tipo REDO e UNDO a cui sono associati i valori dei dati originali e
dei dati modificati a seguito di una transazione. Lo scopo del tuning è
ottimizzare il processo di creazione e gestione di questi file.
49
In particolare l‟amministratore deve assicurarsi che:
1. La scrittura dei file di log avvenga con accesso sequenziale al
disco (ovvero i file di log vengano creati su dischi separati dai
dati)
2. Le informazioni contenute in ogni record di log siano minime
così da permettere l‟inserimento di più aggiornamenti nella
stessa pagina.
3. Venga rispettato il protocollo Write-Ahead Logging (WAL).,
ossia che tutte le modifiche prima di essere apportate fisicamente
devono essere inserite in un record di log.
4. Siano garantiti i principi di atomicità e durabilità delle
transazioni.
2.4. TUNING APPLICATIVO
La maggior parte delle applicazioni che interagiscono con i sistemi
database, lo fanno in due modi: attraverso l‟utilizzo di un linguaggio
specifico di quarta generazione o mediante l‟utilizzo di un linguaggio di
programmazione come PHP, Java, Visual Basic, C, ecc. che si basano su
un'interfaccia a livello di chiamata. Diversi fornitori offrono numerosi
software proprietari di quarta generazione che risolvono le query
ottenendo i risultati desiderati con esecuzioni di alto livello prestazionale
a costo della portabilità di tali applicazioni. I programmi che
50
interagiscono, invece, con il database mediante interfaccia a livello di
chiamata, si basano su librerie di funzioni che consentono loro di
connettersi ad un database server, eseguire istruzioni SQL, recuperare i
risultati della query ed eseguire o interrompere le transazioni.
2.4.1. MODELLO CLIENT-SERVER
Poichè l‟esecuzione dell‟applicativo dei client è asincrona rispetto al
funzionamento del database server, la connessione tra i due può essere
possibile solo mediante un buffer di memoria. L‟utilizzo di questo
modello di interconnessione offre due principali vantaggi:
Se l‟interazione con il client è molto lenta e, ad esempio, i tempi
per la lettura delle risposte di una query sono eccessivamente
lunghi, l‟utilizzo del buffer di memoria permette al database
server di memorizzare all‟interno del buffer l‟intero risultato
della query e svincolare subito le proprie risorse senza risentire
delle basse prestazioni del client.
I dati vengono inviati dal server al client non appena il buffer di
comunicazione lato server si riempie o non appena termina
l‟esecuzione del processo attivo. L‟uso di buffer grandi avrà
quindi l‟effetto di allungare i tempi di risposta percepiti dal
client riducendo però il numero di pacchetti che attraversano la
rete e quindi il rischio di errori. L‟uso di un piccolo buffer,
51
d‟altra parte, avrà l‟effetto opposto. Come sempre la parola
chiave è „equilibrio‟.
Fig. 8: Modello Client Server con sistema di buffer
2.4.2. TUNING DELLE INTERFACCE APPLICATIVE
Analizzare l‟interfaccia applicativa utilizzata dall‟utente per interagire
con il DBMS alla ricerca di codici dal basso rendimento spesso è di
cruciale importanza ai fini delle prestazioni. I principali principi che
l‟interfaccia deve rispettare sono:
1. Evitare l‟interazione con l‟utente all‟interno di una transazione.
2. Ridurre al minimo il numero di passaggi d‟esecuzione tra
applicazione e database server.
3. Recuperare solo le righe e le colonne necessarie.
4. Minimizzare il numero di query da compilare.
5. Utilizzare lock ad alta granularità.
52
3. TUNING DEL DATABASE
Se fino ad ora sono stati presentati una serie di interventi mirati
all‟ottimizzazione del sistema su cui il database opera, adesso verranno
analizzate le operazioni che l‟amministratore può compiere direttamente sulla
struttura dati per migliorarne al massimo l‟efficienza. In particolare, dando per
assodata la conoscenza delle progettazione del modello concettuale del
database (modello E/R), vedremo quali sono i possibili interventi di tuning
prima sul livello logico e poi sul livello fisico della base di dati.
3.1. TUNING A LIVELLO LOGICO
La progettazione logica si occupa di definire l‟organizzazione dei dati e di
stabilire:
1. gli attributi relativi alle entità già definite a livello concettuale
2. le chiavi primarie ed esterne per la concretizzazione delle relazioni
emerse dal modello E/R.
In generale, si definisce attributo un‟informazione relativa ad un‟entità che
rispetti le seguenti regole:
contiene un‟unica informazione.
Non assume valori costanti.
53
Non si tratta di un campo il cui valore può essere calcolato tramite
operazioni su altri campi.
Una volta definiti in questo modo gli attributi del modello logico, si
procedere alla concretizzazione delle relazioni del modello E/R e alla
creazione di speciali attributi definiti come chiavi primarie ed esterne.
Per potere due attributi rappresentare una relazione, ed essere quindi
chiave primaria ed esterna, devono soddisfare le seguenti condizioni:
1. Devono avere la stessa natura. Se un attributo è di tipo intero e
l‟altro è di tipo testo i due attributi insieme non possono mai
rappresentare una relazione tra entità.
2. Devono avere sempre valori “not null”
3. Almeno uno dei due attributi deve essere univoco.
L‟utilizzo delle chiavi permette di rappresentare fisicamente le relazioni di
cardinalità uno a molti ma, da sole, non sono sufficienti alla concretizzazione
delle relazioni con cardinalità molti a molti. Per risolvere questo problema,
rendere la struttura più flessibile, ridurre la ridondanza e il rischio di
incoerenza, occorre procedere a una serie di operazioni dette di
normalizzazione. La normalizzazione aiuta a mantenere alte le prestazioni
anche se, in certi casi, si possono raggiungere risultati migliori con l‟aggiunta
di ridondanza e quindi la parziale denormalizzazione del modello.
54
3.1.1. NORMALIZZAZIONE
Dopo aver stabilito le entità, gli attributi e le chiavi che andranno a
comporre il modello fisico del database al fine di raggiungere lo scopo
previsto dalla base di dati, per ottenere il massimo dell‟efficienza è
necessario procedere alla normalizzazione dello schema realizzato. Tale
processo si fonda su un unico semplice criterio: se una relazione
presenta più concetti tra loro indipendenti, la si decompone in relazioni
più piccole, una per ogni concetto.
Questo tipo di processo non è sempre applicabile in tutte le tabelle. In
taluni casi potrebbe comportare una perdita d'informazioni. Esistono
vari livelli di normalizzazione detti anche forme normali che certificano
la qualità del modello di un database.
In genere si ritiene che l‟applicazione delle prime tre forme normali
permetta di raggiungere un adeguato compromesso tra efficienza e
ridondanza.
Un modello logico si dice:
in Prima Forma Normale, quando ogni tabella non presenta
gruppi di attributi ripetuti ciascun attributo è definito su un
dominio con valori atomici e contiene un attributo che funge
da chiave primaria
in Seconda Forma Normale, quando il modello è in Prima Forma
Normale e, in più, per ogni tabella tutti i campi non chiave
55
dipendono funzionalmente dalla chiave primaria o, in caso di
chiave composta, da tutti gli attributi che ne fanno parte.
In Terza Forma Normale, quando il modello è in Seconda Forma
Normale e la dipendenza tra gli attributi è basata solo sulla
chiave primaria e non esistono dipendenze tra attributi.
Sono state formalizzate altre forme normali quali: la forma normale
Boyce/Codd, la quarta forma normale, la quinta, ecc. ma raramente
vengono
utilizzate,
in
quanto
ad
un
incremento
di
rigore
nell'eliminazione della ridondanza corrisponde un degrado delle
prestazioni. Per capire bene la normalizzazione può essere utile un
esempio.
Di seguito è preso in considerazione lo schema, attualmente
funzionante, del database dei docenti di religione di una Diocesi le cui
funzioni principali consistono nel calcolo della graduatoria e
nell‟assegnazione delle cattedre, definite come insieme di ore di
servizio distribuite in un massimo di tre scuole diverse, ai singoli
insegnanti.
56
Fig. 9: Elenco attributi della tabella Insegnanti.
La prima cosa che notiamo dalla struttura della tabella Insegnanti è che
il suo secondo attributo (COGNOME_NOME) viola una delle
caratteristiche di definizione degli attributi. Esso non rappresenta infatti
un‟informazione univoca bensì l‟unione del cognome e del nome
dell‟insegnate. Anche correggendo questo errore, si ci rende conto che
la struttura ottenuta (Fig. 10) non rispetta le regole della prima forma
normale.
Fig. 10 Struttura della tabella dopo la correzione dell‟attributo COGNOME_NOME.
57
Pur esistendo il campo ID_ASPIRANTE che funge da chiave primaria,
sono presenti gruppi di attributi ripetuti. Applicare la prima forma
normale significherebbe eliminare tutti gli attributi sovrabbondanti e
semplificare la consultazione dei dati che appare confusionaria e di
difficile consultazione (Fig. 11).
Fig. 11: Visualizzazione dei dati della tabella non normalizzata
Gli effetti della normalizzazione di primo livello (Fig. 12) sono i
seguenti:
Effetti Positivi: si riducono gli sprechi di spazio legati alle tuple
lasciate incomplete, si semplificano le interrogazioni e
soprattutto si rende la struttura più elastica permettendole di
memorizzare per ogni insegnante un numero massimo indefinito
di scuole al posto delle tre che prevedeva inizialmente
Effetti Negativi: aumentano le tuple inserite nella tabella e la
ridondanza dei dati in relazione all‟anagrafica degli insegnanti;
58
Fig. 12: I dati della tabella insegnanti a seguito della normalizzazione di primo livello.
Per semplificare e rimuovere la nuova ridondanza, ci viene incontro il
principio della Seconda Forma Normale. In questo caso possiamo
notare che non tutti gli attributi della tabella dipendono funzionalmente
dalla chiave primaria già definita bensì è possibile scomporre gli
attributi in due gruppi: quelli legati all‟anagrafica e quelli che invece
dipendono dalla scuola di insegnamento. Si procedere, quindi, alla
normalizzazione di secondo livello aggiungendo una chiave primaria ID
legata alla scuola di riferimento, una chiave esterna ID_INSEGNANTE
e spezzando la struttura in due strutture distinte.
59
Fig. 13: I dati nelle due strutture distinte.
Dallo studio di questa nuova situazione iniziale si ci rende conto che,
mentre la tabella insegnanti risulta adesso normalizzata al secondo
livello, la nuova tabella Scuole continua a non essere normalizzata in
quanto l‟attributo PER_ORE e ANNO non sono legate esplicitamente
alla chiave primaria ID_SCUOLA. Procedendo come già esposto e
normalizzando ulteriormente in secondo livello si avrà alla fine la
seguente struttura:
60
Fig. 14: I dati dopo la normalizzazione di secondo livello.
Se da un lato la Seconda Forma Normale ha rimosso quasi interamente
le ridondanze, dall‟altro ha complicato le query che ora devono tenere
conto delle nuove relazioni tra le tre strutture. Analizzando
ulteriormente l‟esempio proposto e trascurando la ridondanza del
campo DI della tabella Scuole per cui sarebbe necessaria un‟ulteriore
normalizzazione di secondo livello, si ci rende conto che la struttura è
già in terza forma normale perché nessun campo attributo dipende da
un altro campo attributo.
3.1.2. DENORMALIZZAZIONE
Denormalizzazione significa violare la normalizzazione. Tenuto conto
di tutti gli svantaggi di una tale mossa, l‟uso della denormalizzazione è
61
funzionale esclusivamente ad un aumento di prestazioni che va
comunque valutato da caso a caso. Il miglioramento dell‟esecuzione di
certe query con l‟inserimento di ridondanza produce, infatti, un
peggioramento rispetto alle operazioni di modifica e inserimento dei
record. Come regola generale, denormalizzare fa male alle prestazioni
per le relazioni che vengono aggiornate spesso ma può aiutare in
situazioni di basso aggiornamento.
Eseguire la denormalizzazione consiste o nell‟aggiunta di un campo
ridondante specifico o nella trasformazione delle relazioni gerarchiche
Throughput (Queries/sec)
di tipo uno a molti in un'unica relazione e quindi in un'unica tabella.
0.002
0.0015
0.001
0.0005
0
normalized
denormalized
Fig. 15: Normalizzazione e Denormalizzazione.
La figura 15, tratta da Shasha/Bonnet 2004, pag. 136, è un
grafico dei potenziali vantaggi di denormalizzazione relativi
all‟esecuzione di una query che mostra tutti gli oggetti venduti da
62
un fornitore in Europa. La versione normalizzata richiede un join a
quattro vie tra Oggetti, Fornitori, Nazioni e Regioni. Aggiungendo
gli opportuni campi di ridondanza è possibile rimuovere i quattro
join e trasformare la query in una semplice selezione sugli oggetti.
La denormalizzazione fornisce, in questo caso, un miglioramento
del 30% del throughput.
3.1.3. OVERNORMALIZZAZIONE
Quando una tabella diventa eccessivamente voluminosa, soprattutto nei
sistemi di gestione dati delle grosse imprese, può essere utile ai fini
delle prestazioni, spezzare i dati contenuti in due tabelle distinte. La
ricerca di un campo in un set di cinquanta milioni di record è infatti
molto più veloce di una ricerca su un set di cento milioni di record.
Naturalmente, anche qui bisogna valutare attentamente i pro e i contro
perché se da un lato l‟overnormalizzazione può migliorare le
prestazioni, dall‟altro complica le operazioni di modifica e inserimento.
Sdoppiare una tabella equivale a sdoppiare gli indici e tutte le
problematiche inerenti alla gestione della tabella originale con una
quantità di elementi però dimezzata. La vera domanda è: ne vale la
pena?
63
3.2. TUNING A LIVELLO FISICO
Il tuning del database a livello fisico è forse la parte di tuning più delicata
e rilevante di tutte. Se un errore sugli altri livelli comporta generalmente
solo un calo di prestazioni, uno sbaglio sul livello fisico del database può
portare al crash dell‟intero sistema. I principali aspetti trattati riguardano
la creazione e la gestione degli indici e l‟ottimizzazione delle query SQL.
3.2.1. TUNING DEGLI INDICI
Un indice può essere visto come un insieme di coppie del tipo (K,P)
dove:
K è un valore chiave del campo su cui l‟indice è costruito;
P è un puntatore al record con valore di chiave K.
Ci sono due tipi di chiavi K: chiavi sequenziali, che assumono un
valore monotono in base all‟ordine di inserimento del record, e chiavi
non-sequenziali il cui valore non dipende dall‟ordine di inserimento. Il
vantaggio nell‟uso degli indici nasce dal fatto che la chiave è solo parte
dell‟informazione contenuta in un record e pertanto, occupando uno
spazio minore rispetto al file dati, permette di velocizzare le operazioni
di ricerca.
Per entrare in merito al tuning degli indici è bene fare prima una
panoramica dei tipi di query in cui gli indici saranno poi usati.
64
Equality Query: sono query che selezionano un certo numero di
record basandosi su un‟operazione di confronto di uguaglianza.
Si distinguono le point query che restituiscono un solo record,
dalle multipoint query, che ne restituiscono diversi. Ecco degli
esempi:
POINT QUERY
MULTIPOINT QUERY
SELECT Cognome, Nome
FROM Insegnanti
WHERE ID_Aspirante=1
SELECT Nome
FROM Insegnanti
WHERE Cognome=”Rossi”
La point query restituisce sempre e solo un record, la multipoint
query restituisce tutti i nomi delle persone che fanno di cognome
Rossi.
Range Query: sono query che selezionano i record il cui attributo
X assume valori compresi in un predefinito intervallo. Ad
esempio:
SELECT *
FROM Insegnamenti
WHERE PER_ORE<18
Prefix Query: si tratta di una query che, basandosi su un attributo
o una sequenza di attributi X, seleziona tutti i record che hanno
un predefinito prefisso in X. Ad esempio:
SELECT Cognome
FROM Insegnanti
WHERE Cognome=„RO%‟
65
Extremal Query: si tratta di una query che visualizza tutti i
record il cui attributo o insieme di attributi X assume valore di
minimo o di massimo. Ad esempio:
SELECT *
FROM Insegnamenti
WHERE Per_Ore = MAX ( SELECT Per_Ore FROM Insegnamenti )
Ordering Query: si tratta di query che visualizzano un set di
record ordinandoli in modo Crescente o Decrescente rispetto ad
un attributo o ad un insieme di attributi X. Ad esempio:
SELECT *
FROM Insegnanti
ORDER BY Cognome, Nome
Grouping Query: si tratta di query che raggruppano i risultati
trovati rispetto a un determinato attributo o gruppo di attributi X.
Ad esempio:
SELECT Anno, SUM(Per_Ore)
FROM Insegnamenti
GROUP BY Anno
Join Query: si tratta di query che collegano due o più tabelle. Le
Join Query sono anche chiamate Equality Join Query perché la
fusione è ottenuta ponendo l‟uguaglianza tra chiavi primarie e
chiavi esterne. Ad esempio:
SELECT Cognome, Nome, Per_Ore
FROM Insegnanti, Insegnamenti
WHERE Insegnanti.ID_Aspirante=Insegnamenti.ID_Aspirante
66
Le prestazioni di ogni tipo di query variano comunque in base al tipo di
indice
definito
sulla
struttura
dati.
Gli
indici
differiscono
essenzialmente per il modo con cui organizzano l‟insieme di coppie
(K,P). I database supportano diversi tipi di organizzazioni strutturali.
Le principali sono: gli alberi binari e le tabelle di hash.
Tutte e due queste strutture, indipendentemente dalla loro concreta
implementazione, possono essere viste a loro volta come strutture ad
albero. Ci saranno nodi dell‟albero posizionati nella memoria RAM e
altri, in genere le foglie, posizionate nella memoria disco. Le
prestazioni della struttura dati saranno inversamente proporzionali al
numero di livelli dell‟albero. Una possibile tecnica per ridurre al
minimo tale numero è quello di massimizzare il numero di indici di
ciascun nodo figlio dell‟albero (fanout). Per migliorare le prestazioni
delle range query, ed eliminare eventuali bottleneck, è infine
consigliato implementare la struttura ad albero con l‟aggiunta di link
nei nodi foglia (ed eventualmente anche nei nodi interni) verso il nodo
alla loro destra.
3.2.1.1.
INDICI CON STRUTTURA B-TREE
Un B-Tree è un albero bilanciato le cui foglie contengono una serie
di coppie chiave/puntatore. Tali indici sono ordinati in base al
valore delle chiavi. Valutazione: I B-Tree sono, in generale la
migliore struttura per l‟organizzazione degli indici del database in
67
quanto raramente richiedono di essere riorganizzati, e possono
supportare diversi tipi di query diverse. Sono ad esempio
particolarmente appropriati per le range query, le prefix query e le
extremal query mentre sono poco adatti per le equality query. Con
inserimenti non sequenziali frequenti, la struttura B-tree avrà tempi
di risposta più coerenti rispetto alle strutture di hash in quanto è in
grado di gestire catene di overflow di lunghezza illimitata. Essendo
l‟efficienza legata al numero di livelli e quindi legata al valore di
fanout, all‟aumentare della lunghezza della chiave si riduce nel
nodo lo spazio per la memorizzazione di altri indici, aumentano i
livelli e si riducono le prestazioni.
3.2.1.2.
INDICI CON STRUTTURA HASH
Le strutture di Hash si basano su un metodo che prevede
l‟archiviazione degli indici in riferimento al valore assunto da una
funzione chiamata funzione di hash. Data una chiave, la funzione
hash restituisce la posizione del record sottoforma di indirizzo della
pagina che lo contiene o della prima pagina di una catena di
overflow. Valutazione: la struttura Hash è particolarmente utile per
tutti i tipi di equality query. Nel caso delle point query migliora
ulteriormente le prestazioni se non sono presenti catene di
overflow; per le multipoint query, invece, il miglioramento è legato
all‟uso degli indici clustering. Le prestazioni di una struttura di
68
hash variano sensibilmente in base alla scelta della funzione hash,
ma i moderni sistemi di database riescono a valutare e scegliere
automaticamente tale funzione in base alle necessità. L‟uso di
questa struttura è, ad ogni modo, sconsigliata per tutte le query che
richiedono la consultazione di grandi quantità di dati.
Gli indici si distinguono anche in: Sparse e Dense Index, Clustering
Index, Nonclustering Index e Composite Index.
3.2.1.3.
STRUTTURE A INDICI DENSI E SPARSI
Un struttura ad indici si dice sparsa quando i puntatori della
struttura sono associati alle pagine dei dati; una struttura si dice
invece a indici densi quando ogni puntatore è associato a uno
specifico record della tabella.
In generale diremo che il numero di puntatori negli indici densi
saranno uguali al numero di puntatori negli indici sparsi per il
numero dei record di una pagina. Un vantaggio significativo delle
strutture ad indici densi è che possono eseguire query di lettura più
velocemente sfruttando informazioni interne.
3.2.1.4.
CLUSTERING INDEX
Un clustering index su un attributo o un insieme di attributi X
(chiamato anche indice primario) è un indice che alloca
69
contemporaneamente tutti i record in cui valori di X sono "vicini"
tra loro. Il concetto di vicinanza dipende dalla struttura dati. Gli
alberi binari considerano due valori X vicini basandosi sul loro
ordinamento (A e B, ad esempio, sono vicini. A e G non lo sono).
Le strutture di hash considerano invece vicini due valori solo se
sono identici. E‟ per questo motivo che le strutture clustering B-tree
sono più funzionali per le query di tipo: partial, range, multipoint,
point e join. Viceversa le strutture di hash sono più utili per le
query equijoin, point e multipoint. Visto che un clustering index
implica una certa organizzazione strutturale, è possibile definirne al
massimo uno per ogni tabella.
Un indice clustering offre i seguenti vantaggi rispetto ad un indice
nonclustering:
Se l'indice cluster è sparso, memorizzerà meno puntatori
rispetto a un indice denso.
Un indice cluster è buono per le query multipoint e per
eseguire un equijoin su un attributo con pochi valori.
Un indice cluster basato su una struttura B_Tree è indicato
per le range, le prefix e le ordering query.
Lo svantaggio principale dell‟uso di questo tipo di indice è che i
suoi benefici diminuiscono all‟aumentare degli overflow.
70
3.2.1.5.
NONCLUSTERING INDEX
Un indice noncluster su un attributo o un insieme di attributi Y
(chiamato anche indice secondario) è un indice che non pone
nessun vincolo sull'organizzazione della tabella. La struttura può
essere quindi raggruppata in riferimento a un qualsiasi suo attributo
X. Un indice nonclustering è sempre di tipo denso. In ogni tabella
possono esere definiti molteplici indici nonclustering. L‟effetto
della presenza di uno o più di questi indici consiste nella possibilità
di eliminare la necessità di accedere alla tabella risolvendo
direttamente la query con le informazioni contenute negli indici
stessi. In genere avremo quindi che:
Un indice nonclustering migliora le prestazioni se permette
di evitare del tutto l‟accesso alle pagine dei dati.
Un indice nonclustering migliora tutte le point query.
Un
indice
nonclustering
produrrà
un
miglioramento
prestazionale nelle multipoint query, quando il numero dei
record risultanti saranno esiguo.
Per stabilire se conviene l‟utilizzo di un indice nonclustering su una
query multipoint, si può verificare se il numero di valori distinti
della chiave è maggiore di C per il numero di record in ogni pagina,
dove C rappresenta il numero di pagine che il sistema è in grado di
precaricare dal disco (prefetching).
71
3.2.1.6.
COMPOSITE INDEX
Un Indice Composto (o indice concatenato) è un indice basato su
più di un attributo. E‟ possibile definire indici composti di tipo
clustering o nonclustering.
Una struttura di dati con un Composite Index denso sugli attributi
(A, B, C,...) archivierà un insieme di puntatori su tutti i record con
il valore A all‟interno del quale sarà già stato archiviato l‟insieme
dei puntatori ai record di valore B, ecc.
Gli Indici Compositi offrono i seguenti vantaggi:
Una query su tutti gli attributi di un indice composto
restituirà un insieme di record molto più piccolo rispetto a
una query eseguita individualmente sugli stessi attributi.
Un indice composto è un mezzo efficace per garantire
l'unicità di un insieme di attributi. Usando l‟indice composto
gli attributi individuali che compongono l‟indice possono
non essere unici purchè siano unici i valori ottenuti dalla
loro combinazione.
Un indice composto semplifica le query di natura geografica,
perché permette una facile indicizzazione delle posizioni
geografiche (definite come coppia di valori latidune e
longitudine).
72
Gli svantaggi legati all‟uso di indici composti sono invece i
seguenti:
Gli indici tendono ad occupare molto spazio per la
memorizzazione della chiave. Si pensi infatti alle strutture
B-Tree: memorizzare chiavi grandi equivale ad avere molti
livelli i cui nodi più bassi occupano la memoria disco e
quindi richiedono più tempo per la lettura. Implementare un
indice composto attraverso una struttura di tipo hash risolve
il problema delle dimensioni ma non permette l‟uso di
questo tipo di indice per le prefix e range query.
Poiché un indice composto comprende diversi attributi,
l‟aggiornamento anche di uno solo di questi causerà la
modifica dell'indice.
Riassumendo quanto è stato detto sugli indici, ai fini del tuning
possiamo concludere che:
1. La scelta di una struttura hash è la più appropriata per quanto
riguarda le equality query, per il resto le prestazioni migliori si
ottengono da una struttura B-Tree.
2. La scelta di utilizzare indici clustering o nonclustering dipende
dal numero di record che si ci aspetta di ottenere dalle query. Per
73
gruppi di pochi record è quindi consigliabile l‟uso di indici
nonclustering.
3. E‟ meglio non usare nessun indice se il tempo che si perde per il
suo inserimento nella struttura scelta o per il suo aggiornamento
supera il tempo impiegato dalla query senza indice.
3.2.2. TUNING DELLE QUERY
In aggiunta all‟uso degli indici, il tuning prevede una serie di
accorgimenti, nel codice SQL delle query, che migliorano le
prestazioni. In particolare l‟amministratore dovrà cercare di:
1. Minimizzare l‟uso della clausola DISTINCT
2. Usare le query annidate
3. Usare i Trigger.
3.2.2.1.
L’USO DI DISTINCT
La clausola Distinct è utile in due casi: quando i risultati della
SELECT contengono duplicati e quando i campi restituiti non
dipendono dalla chiave definita dalle clausole FROM e WHERE.
In realtà capita spesso che DISTINCT venga usato anche quando
non è necessario, comportando uno spreco di risorse di
elaborazione. In generale si può dire che, definendo privilegiata la
74
tabella T se i campi restituiti dalla query contengono una chiave di
T, l‟uso di DISTINCT è superfluo quando:
1. Tutte le tabelle menzionate dalla query sono privilegiate;
2. Ogni tabella non privilegiata è direttamente collegata a una
tabella privilegiata.
3.2.2.2.
L’USO DI QUERY ANNIDATE
Anche se può sembrare impossibile, l‟uso di query annidate
produce prestazioni migliori alle corrispondenti versioni non
annidate. Esistono quattro tipi di query annidate:
1. Sottoquery non correlata8 con operatori aggregati nella query
interna
2. Sottoquery non correlata senza operatori aggregati
3. Sottoquery correlata con operatori aggregati
4. Sottoquery correlata senza operatori aggregati
Le sottoquery usate più frequentemente sono le prime tre. Se per
quanto riguarda la prima non ci sono particolari considerazioni in
merito alle prestazioni, quando si usano sottoquery del secondo e
del terzo tipo bisogna stare attenti agli indici e alla forma sintattica
usata valutando il codice migliore dell‟intera query da caso a caso.
8
Sottoquery non correlata: una sottoquery si dice non correlata se non contiene riferimenti alle
tabelle menzionate nella query principale.
75
3.2.2.3.
I TRIGGER
Il Trigger è una procedura che viene eseguita in maniera automatica
in coincidenza di un determinato evento, come ad esempio la
cancellazione di un record di una tabella. In questo modo si ha a
disposizione una tecnica per specificare e inserire vincoli di
integrità anche complessi.
In generale non ci sono particolari operazioni di tuning volte a
migliorare le prestazioni del sistema dovute ai trigger. La scelta
dell‟amministratore consiste solo nel loro uso o meno.
Il vantaggio legato all‟uso è appunto quello che, poiché i trigger si
verificano solo dopo una certa combinazione di operazioni, è
possibile usare queste informazioni per definire strategie speciali
volte a ridurre l‟eventuale calo di prestazioni che segue determinati
eventi.
Gli svantaggi sono:
1. In presenza di trigger, nessun aggiornamento può essere
analizzato e valutato isolatamente perché tale aggiornamento
può causare a sua volta trigger che eseguono ulteriori
modifiche.
2. La presenza di numerosi trigger può creare confusione. Non
esiste un vero e proprio sistema di gestione. Diversi trigger
possono essere definiti in relazione al verificarsi dello stesso
evento o essere sequenziali gli uni agli altri. Toccherebbe al
76
sistema scegliere, in casi di trigger multipli, quali eseguire
per prima e se eseguirli tutti o meno ma non è chiaro cosa
accadrebbe se molti trigger provassero a modificare gli stessi
dati.
77
4. APPLICAZIONE PRATICA DEL TUNING
Dopo aver visto e approfondito i vari livelli di tuning, nasce spontaneo il
desiderio di studiare e quantificare quali siano gli effetti concreti
dell‟applicazione dei concetti teorici finora esposti.
Prima di descrivere il progetto pratico e i risultati ottenuti, sembra doverosa
una breve premessa.
4.1. I BENCHMARK
Con il termine benchmark si intende un insieme di test software volti a
fornire una misura delle prestazioni di un sistema in riferimento a diverse
operazioni. Nell‟ambito dei database, a causa dell‟espansione e della
diversificazione del mercato informatico, è stato istituito un organismo
internazionale denominato Transaction Processing Performance Council
(TPC) che si occupa di definire benchmark standard e di omologare i
risultati ottenuti al fine di rendere comparabile la misurazione delle
prestazioni di un sistema rispetto agli altri. I benchmark del TPC sono
ritenuti i più importanti e significativi ai fini della valutazione di sistemi
che ospitano DBMS. I principali benchmark in uso sono:
78
TPC-C, benchmark standard per la misurazione delle prestazioni di
un database in una rete aziendale. Utilizza tipicamente una struttura
di nove tabelle e un mix di cinque transazioni. Il test è piuttosto
complesso. Per ogni transazione vengono misurati i tempi specifici
nell‟ipotesi di un elevato numero di “client”.
TPC-H, benchmark standard per i Dataware House. Si tratta di una
base di dati costituita da otto tabelle che rappresentano i dati
gestionali di un'azienda. I test vengono eseguiti generalmente su un
carico di 10.000.000 di record per una dimensione totale di 1GB
circa. Per analizzare le prestazioni del sistema vengono utilizzate
22 transazioni piuttosto complesse lanciate in streams differenti.
Ogni benchmark viene definito con un documento di specifiche molto
ampio e completo che ne definisce ogni aspetto. Per essere pubblicati i
risultati dei benchmark debbono essere validati da un Advisor e vanno
accompagnati da un documento che riporta tutti i dettagli della
configurazione e delle prove svolte. L'impegno per effettuare un
benchmark ufficiale è molto oneroso e in genere viene richiesto e
sostenuto solo dai principali venditori di hardware e software a fronte del
rilascio di nuovi e significativi prodotti.
79
4.2. SISTEMI DI ARCHIVIAZIONE MYSQL
MySQL permette di utilizzare numerosi tipi diversi di tabelle, ovvero
diversi "storage engine" (motori di archiviazione) per la memorizzazione
dei dati. La distinzione più importante fra i diversi sistemi è quella fra
transazionali e non transazionali. I motori transazionali offrono il
vantaggio di essere fault tollerance, quelli non transazionali sono però più
veloci, richiedono meno utilizzo di spazio su disco e meno memoria per
gli update.
MySQL implementa i due motori attraverso due principali standard:
MyISAM e InnoDB.
4.2.1. MYISAM
È lo storage engine di default di MySQL. Si tratta di un sistema non
transazionale e prevede l‟utilizzo di tre file: un file .frm che contiene la
definizione della tabella, più un file .MYD per i dati e un file .MYI per
gli indici. È possibile indicizzare anche colonne di tipo BLOB e TEXT
permette di gestire una colonna di tipo AUTO_INCREMENT per ogni
tabella, incrementando il suo valore per ogni record inserito
automaticamente.
4.2.2. INNODB
È uno storage engine transazionale ottimizzato per l'uso concorrente dei
dati fra molti utenti. Supporta l‟utilizzo delle FOREIGN KEY e si
differenzia da MyISAM per le seguenti caratteristiche:
80
riesce a riparare una tabella dopo un crash di sistema
rieseguendo le ultime istruzioni registrate nei file di log.
MyISAM deve invece eseguire una scansione completa della
tabella per poi ripararla, ed eventualmente ricostruire gli indici;
gestisce in maniera autonoma la cache mentre MyISAM si affida
al sistema operativo per il caching delle letture e delle scritture
sulle tabelle;
immagazzina i nuovi record seguendo l‟ordine della chiave
primaria mentre MyISAM generalmente immagazzina i record
nell'ordine in cui sono inseriti;
comprime i record molto meno rispetto a MyISAM. Questo
significa che richiede più memoria RAM e più spazio su disco;
non supporta le ricerche fulltext.
4.3. IMPLEMENTAZIONE
Lo scopo del progetto è quello di comparare le prestazioni di una base di
dati di grandi dimensioni al variare del suo sistema di archiviazione in
presenza e assenza di indici. Per fare questo, si è scelto di appoggiarsi al
benchmark standard TPC/C.
Il suo utilizzo garantisce la veridicità dei valori ottenuti dai test e da
concretezza al progetto.
81
Per prima cosa è stato definito il database tpccprova in MySQL e,
seguendo le specifiche riportate dallo standard TPC/C, è stata definita la
struttura del database. In seguito sono stati generati e caricati, con un
apposito script SQL, una quantità di dati fittizi tali da omologare il
database come da specifiche TPC/C su scala 16.
TABELLA
NUM. RECORD
BYTE/RECORD
TOTALE BYTE
16
89
1,39 Kb
160
95
14,84 Kb
C_Customer
480.000
655
299,84 Mb
C_History
480.000
46
21,06 Mb
C_New_Order
144.000
8
1,10 Mb
C_Order
480.000
24
10,99 Mb
4.800.000
54
274,19 Mb
100.000
82
7,82 Mb
1.600.000
306
466,92 Mb
C_Warehouse
C_District
C_Order_Line
C_Item
C_Stock
La seconda fase del progetto ha previsto, attraverso l‟utilizzo del software
“Benchmark Factory for Databases”, l‟esecuzione da parte di un numero
crescente di utenti di un mix pesato di query TPC/C e il conseguente
rilevamento dei tempi di risposta del sistema.
Le query TPC/C, per come è stato definito lo standard, sono:
82
1. New-order, ovvero l‟inserimento di un nuovo ordine da parte di un
cliente. Questa query, statisticamente, viene richiesta con una
frequenza del 45%;
2. Payment, ovvero l‟aggiornamento del saldo di un cliente a seguito
di un pagamento. La frequenza di questa query è pari al 43%;
3. Delivery: aggiornamento dati relativi alla consegna di un ordine.
La frequenza di questa query è pari al 4%;
4. Order-status: recupera lo stato degli ordini più recenti di un
cliente. La frequenza di questa query è pari al 4%;
5. Stock-level: monitoraggio dell‟inventario di una magazzino. La
frequenza di questa query è pari al 4%.
4.4. I RISULTATI: ANALISI SINTETICA
I dati raccolti e rielaborati dai vari test, in seguito alle opportune
modifiche della struttura del database, riguardano i seguenti casi:
Sistema InnoDB Indicizzato;
Sistema MyISAM Indicizzato;
Sistema InnoDB Non Indicizzato;
Sistema MyISAM Non Indicizzato.
Il ciclo di test, della durata media di un‟ora e mezza ciascuno, ha fornito i
risultati che seguono.
83
4.4.1. QUERY PER SECONDO
Utenti
1
2
4
6
8
10
12
14
16
18
20
22
24
InnoDB Ind.
0,05
0,09
0,22
0,31
0,43
0,53
0,65
0,72
0,83
0,9
1,01
1,11
1,2
MyISAM Ind.
0,05
0,09
0,22
0,32
0,44
0,53
0,64
0,73
0,84
0,9
1,03
1,1
1,2
InnoDB Non Ind.
MyISAM Non Ind.
0
0,01
0
0,01
0
0
0,01
0
0
0
0
0,01
0,01
0,05
0,07
0,13
0,11
0,12
0,14
0,09
0,27
0,08
0,03
0,12
0,04
0,04
Tabella 4.4.1: Query per secondo all‟aumento del numero di utenti connessi al database.
Grafico 4.4.1: Grafico basato sui dati della tabella 4.4.1.
84
Dal grafico e dai valori trovati si ci rende conto che il numero di query per
secondo eseguite dal sistema dipende prevalentemente dalla presenza o
assenza degli indici piuttosto che dal sistema di archiviazione dei dati. Mentre
infatti in presenza di indici le prestazioni dei sistemi InnoDB e MyISAM in
media si equivalgono, in caso di sistemi che non fanno utilizzo di indici,
queste si riducono drasticamente. Un altro fattore su cui prestare attenzione è
l‟andamento delle curve. In una struttura TPC/C che utilizza gli indici il
numero di query eseguite in simultanea aumenta proporzionalmente
all‟aumentare degli utenti. Questo garantisce che, l‟aumento delle utenze non
incida negativamente sulle prestazioni del sistema (nel range di utenti
esaminato). Basandosi sui risultati riportati sopra, in caso di utilizzo degli
indici, sarebbe quindi consigliabile l‟utilizzo del sistema InnoDB perché esso,
grazie alle transazioni, garantisce l‟integrità referenziale dei dati.
4.4.2. BYTE PER SECONDO
Utenti
1
2
4
6
8
10
12
14
16
18
20
22
24
InnoDB Ind.
128,48
133,42
440,62
628,22
779,22
1.038,27
1.304,68
1.292,76
1.525,88
1.829,44
1.950,58
2.122,51
2.417,85
MyISAM Ind.
127,49
134,27
429,62
623,73
797,06
1.017,3
1.289,98
1.308,38
1.554,82
1.848,03
2.047,74
2.110,86
2.296,82
InnoDB Non Ind.
MyISAM Non Ind.
0,15
2,98
11,81
14,04
11,96
0
10,55
0,25
0
0,2
0,02
9,33
0,02
73,76
97,65
167,76
253,77
278,81
291,02
207,2
327,41
200,07
73,51
104,73
58,46
83,97
Tabella 4.4.2: Byte per secondo all‟aumento del numero di utenti connessi al database.
85
Grafico 4.4.2: Grafico basato sui dati della tabella 4.4.2.
Essendo il numero di byte gestiti dal database legato alle query eseguite, il
grafico ottenuto e le considerazioni coincidono, inevitabilmente, con quelle
del paragrafo precedente.
4.4.3. TEMPI DI RISPOSTA MINIMI
Utenti
1
2
InnoDB Ind.
0,027
0,027
MyISAM Ind.
0,003
0,003
86
InnoDB Non Ind.
MyISAM Non Ind.
258,177
40,232
0,534
0,529
4
6
8
10
12
14
16
18
20
22
24
0,018
0,006
0,027
0,006
0,003
0,003
0,003
0,003
0,003
0,004
0,003
0,002
0,002
0,002
0,002
0,002
0,002
0,002
0,002
0,002
0,002
0,002
318,651
57,774
308,994
96,131
40,145
82,105
72,255
69,155
96,692
Tabella 4.4.3: Tempi di risposta minimi relativi all‟esecuzione delle query.
Grafico 4.4.3: Grafico basato sui dati della tabella 4.4.3.
87
0,514
0,672
0,707
0,691
1,451
0,714
1,261
86,356
0,837
32,108
1,251
Durante l‟analisi dei risultati si ci è resi conto che i tempi di risposta del
DBMS in caso di strutture non indicizzate sono mediamente molto superiori
rispetto a quelli dei sistemi indicizzati. Per questo si è optato per rappresentare
i dati raccolti nei vari casi attraverso un grafico a scale sovrapposte. A sinistra
è espressa la scala dei valori delle strutture indicizzate, a destra la scala dei
valori delle strutture non indicizzate. In relazione ai tempi di risposta la
struttura MyISAM si conferma come più efficiente della struttura InnoDB se il
database non fa ricorso agli indici. Parlando invece di strutture indicizzate,
all‟aumento del numero di utenze, la differenza tra i tempi di risposta InnoDB
e quelli MyISAM si riduce fino quasi a coincidere. InnoDB indicizzato
potrebbe rappresentare, per questo, la soluzione migliore riguardo i tempi
minimi di risposta del DBMS per via delle sue funzionalità aggiuntive.
4.4.4. TEMPI DI RISPOSTA MASSIMI
Utenti
1
2
4
6
8
10
12
14
16
18
20
22
24
InnoDB Ind.
0,605
0,41
0,91
0,473
0,315
0,956
2,029
0,389
0,308
1,653
0,284
1,501
0,243
MyISAM Ind.
0,028
0,027
0,051
0,067
0,079
0,074
0,107
0,153
0,158
0,196
0,187
0,832
0,246
InnoDB Non Ind.
MyISAM Non Ind.
258,177
40,232
318,651
333,37
308,994
161,137
177,409
40,145
67,393
82,105
72,255
323,143
96,692
29,289
24,176
48,001
66,669
107,231
124,219
183,415
201,387
217,475
162,591
180,325
208,261
181,353
Tabella 4.4.4: Tempi di risposta massimi relativi all‟esecuzione delle query.
88
Grafico 4.4.4: Grafico basato sui dati della tabella 4.4.4.
Per quanto riguarda i tempi di risposta massimi, i dati in nostro possesso non
ci permettono di definire in maniera sicura quale dei due sistemi di
memorizzazione sia più efficacie in assoluto. Viene comunque ribadita la
profonda differenza tra strutture che utilizzano gli indici e quelle che non li
utilizzano. Dai dati emersi la struttura MyISAM indicizzata rimane comunque
la più veloce. Se questa velocità implica una migliore prestazione o meno del
database non è però possibile stabilirlo senza fare delle supposizioni
89
aggiuntive sui dati del database quali l‟importanza dell‟integrità referenziale
dei dati e la percentuale di errori che si verificano nel sistema.
4.5. I RISULTATI: ANALISI ANALITICA
Mentre i risultati riportati nel paragrafo precedente rappresentavano le
prestazioni del sistema in linea generica, di seguito vengono analizzati i
risultati dei test TPC/C relativi alle singole query.
4.5.1. TEMPI DI ESECUZIONE NEW-ORDER
Utenti
1
2
4
6
8
10
12
14
16
18
20
22
24
InnoDB Ind.
463
327
274
184
170
166
140
133
135
144
165
150
156
MyISAM Ind.
23
18
22
26
21
27
37
44
40
46
46
59
44
InnoDB Non Ind.
MyISAM Non Ind.
206.766
298.347
318.651
333.370
308.994
246.973
177.409
198.236
207.983
239.421
273.916
323.143
352.764
19.200
15.068
24.243
42.055
62.341
73.617
118.605
128.535
148.893
133.869
139.856
187.355
152.551
Tabella 4.5.1: Tempi di esecuzione della query New-Order.
90
Grafico 4.5.1: Grafico basato sui dati della tabella 4.5.1.
91
Eccetto per il sistema InnoDB Indicizzato, dal grafico e dai valori ottenuti dai
test, si ci rende conto che, l‟aumento del numero di utenti, corrisponde a un
aumento del tempo necessario all‟esecuzione di questa query. Nel range di
utenti analizzati è evidente che la struttura MyISAM indicizzata è quella che
esegue la query più velocemente. Se si ci basa sull‟andamento dei grafici,
tuttavia, si può presumere che, con l‟aumento del numero di utenti, la velocità
di InnoDB (grafico logaritmico decrescente) arriverà a superare quella di
MyISAM (grafico lineare crescente) rendendolo quindi il sistema dalle
prestazioni migliori in assoluto. Per quanto riguarda le strutture non
indicizzate bisogna invece valutare se la velocità di MyISAM compensi la sua
mancanza di integrità referenziale.
4.5.2. TEMPI DI ESECUZIONE PAYMENT
Utenti
1
2
4
6
8
10
12
14
16
18
20
22
24
InnoDB Ind.
MyISAM Ind.
95
69
51
83
60
104
57
52
43
45
83
76
71
3
3
3
3
3
3
3
3
3
3
3
3
3
InnoDB Non Ind.
MyISAM Non Ind.
37.953
40.232
46.123
57.774
63.259
74.361
96.131
103.241
126.487
164.382
187.693
204.972
223.459
924
993
1.120
1.257
1.236
1.185
1.451
1.230
2.272
2.483
837
1.059
1.341
Tabella 4.5.2: Tempi di esecuzione della query Payment.
92
Grafico 4.5.2: Grafico basato sui dati della tabella 4.5.2.
93
Nel range selezionato questa query ha tempi di risposta migliori quando la
struttura si basa sul sistema di archiviazione MyISAM. Mantenere l‟integrità
referenziale dei dati attraverso InnoDB, in questo caso comporta un caro
prezzo in termini di tempi di esecuzione. Da notare che l‟uso degli indici
abbatte comunque notevolmente tali tempi.
4.5.3. TEMPI DI ESECUZIONE DELIVERY
Utenti
1
2
4
6
8
10
12
14
16
18
20
22
24
InnoDB Ind.
356
311
282
833
241
291
300
277
442
485
283
249
359
MyISAM Ind.
38
40
40
40
38
39
41
41
41
41
41
41
41
InnoDB Non Ind.
MyISAM Non Ind.
258.177
208.150
227.345
258.230
236.127
253.761
259.435
209.493
254.647
222.750
270.103
291.429
288.238
26.187
26.354
23.702
29.520
29.131
29.840
30.813
30.868
30.875
30.872
31.350
32.108
32.236
Tabella 4.5.3: Tempi di esecuzione della query Delivery.
94
Grafico 4.5.3: Grafico basato sui dati della tabella 4.5.3.
95
In base ai dati ottenuti dai test nel range di utenze simultanee su cui sono stati
effettuati, con MyISAM la query richiede un tempo quasi costante di
esecuzione decisamente inferiore ai tempi richiesti dal database con sistema di
archiviazione InnoDB che, invece, presenta valori oscillanti e, in media, dieci
volte superiori rispetto ai valori MyISAM.
4.5.4. TEMPI DI ESECUZIONE ORDER-STATUS
Utenti
1
2
4
6
8
10
12
14
16
18
20
22
24
InnoDB Ind.
92
99
103
145
162
104
104
98
157
136
130
110
150
MyISAM Ind.
14
16
16
16
17
15
15
15
14
14
15
15
15
InnoDB Non Ind.
MyISAM Non Ind.
23.464
20.031
26.128
29.465
31.872
25.029
22.106
40.145
59.346
82.105
79.625
70.497
83.051
1.810
1.789
1.793
1.790
1.785
1.796
1.789
1.781
1.773
1.798
1.782
1.797
1.789
Tabella 4.5.4: Tempi di esecuzione della query Order-Status.
96
Grafico 4.5.4: Grafico basato sui dati della tabella 4.5.4.
97
L‟andamento di questa query rispecchia quello delle query precedenti. Unica
considerazione aggiuntiva consiste nell‟evidenziare che l‟uso di InnoDB senza
indici porta ad un incremento più veloce dei tempi di esecuzione della query
rispetto ad InnoDB indicizzato.
4.5.5. TEMPI DI ESECUZIONE STOCK-LEVEL
Utenti
1
2
4
6
8
10
12
14
16
18
20
22
24
InnoDB Ind.
7
9
224
5
5
828
379
3
3
588
4
3
4
MyISAM Ind.
4
4
5
4
4
5
4
4
4
11
5
5
5
InnoDB Non Ind.
MyISAM Non Ind.
36885
33467
39579
42931
45353
38525
35617
53671
71887
85661
72255
69155
96692
1785
1779
1798
1810
1820
1846
1854
1861
1868
1908
1907
1937
1944
Tabella 4.5.5: Tempi di esecuzione della query Stock-Level.
98
Grafico 4.5.5: Grafico basato sui dati della tabella 4.5.5.
99
Analizzando i valori raccolti dai test e il grafico da essi ottenuto, si nota che,
con qualche eccezione, le strutture indicizzate richiedono, tra loro, lo stesso
tempo d‟esecuzione. I dati ottenuti dal test del sistema di archiviazione
InnoDB presentano però picchi di tempo che si avvicinano alle tempistiche
legate alla struttura MyISAM Non Indicizzata. Bisogna quindi valutare se il
rischio di incorrere in questi picchi (30% di possibilità) valga l‟utilizzo di una
struttura che garantisca l‟integrità referenziale dei dati in essa contenuti.
4.6. CONCLUSIONI
Dall‟analisi fatta possiamo concludere che, quando utilizziamo strutture
indicizzate, la soluzione migliore sul database è InnoDB perché, anche se i
tempi di risposta a volte sono leggermente più alti di quelli ottenuti della
struttura MyISAM, supporta le chiavi esterne e l‟esecuzione delle query
tramite transazioni. L‟importanza dell‟integrità referenziale dei dati
compensa abbondantemente i ritardi riscontrati. Quando invece si
utilizzano strutture non indicizzate la differenza tra le prestazioni InnoDB
e MyISAM sale vertiginosamente. InnoDB presenta tempi di risposta dieci
volte superiori a quelli di MyISAM. Per stabilire quale delle strutture non
indicizzate conviene utilizzare l‟amministratore dovrà quindi valutare
attentamente se le proprietà aggiuntive di InnoDB valgano o meno il suo
costo supplementare.
100
In realtà i valori dei dati raccolti sono un esempio di come la gestione e il
tuning di una base di dati dipenda da una serie di scelte motivate. Non è
possibile definire una struttura migliore dell‟altra in assoluto o dire che
l‟uso degli indici è la soluzione ideale. Ogni aspetto analizzato, ha i suoi
pro e i suoi contro. Ogni soluzione può essere perfetta per un contesto e
completamente sbagliata per gli altri.
Queste considerazioni, che richiamano i principi fondamentali del tuning,
ci invitano ad approfondire ulteriormente questa tematica e a riflettere
sulla sua importanza in un mondo dove l‟informazione assume un ruolo
sempre più rilevante sia in campo informatico che sociale.
101
BIBLIOGRAFIA
DENNIS SHASHA (1992): “Database Tuning - A Principled Approach”,
Prentice-Hall
DENNIS SHASHA E PHILIPPE BONNET (2003): “Database Tuning:
Principles, Experiments, and Troubleshooting Techniques”, Morgan
Kaufman
SITANSU S. MITTRA (2002): “Database Performance Tuning and
Optimization”, Springer Verlag
MICHAEL J. COREY, MICHAEL ABBEY, DANIEL J. DECHICHIO (2003):
“Oracle Database: Performance Tuning Guide”, Oracle Press
IBM (2008): “Tuning Database Performance”, IBM
ATZENI, CERI, PARABOSCHI, TORLONE (2002): “Basi di dati, Modelli e
linguaggi di interrogazione”, McGraw-Hill
LOREDANA VIGLIANO (2009): Set di Slide “Basi di Dati”, Università di
Roma.
102
ANTONELLA POGGI (2003): Set di Slide “Strutture Fisiche di Accesso”,
Università “La Sapienza” di Roma
MARCO PATELLA (2011): Set di Slide “Strutture di Indicizzazione”,
Università di Bologna
DENNIS SHASHA (1996): Articolo “Tuning Databases for High
Performance”, ACM Computing Surveys, Vol. 28, No. 1, March 1996
JULIAN STUHLER (2010), Articolo “A Perspective on Database
Performance Tuning”, Database Journal 16 Aprile 2010
http://www.databasetuning.org/, DENNIS SHASHA, “Database Tuning”
consultato il 14/08/2011
http://www.databasetuning.org/, [email protected], “Ottimizzazione
e tuning” consultato il 14/08/2011
http://it.wikipedia.org/wiki/RAID,
consultato il 14/08/2011
103
Autore
Sconosciuto,
“RAID”
