Basi di Dati

1. Basi di Dati Progettazione Fisica e “Tuning”: Concetti Avanzati versione 2.0 Questo lavoro è concesso in uso secondo i termini di una licenza Creative Commons (vedi ultima pagina) G. Mecca – mecca@unibas.it – Università della Basilicata

2. Progettazione Fisica e Tuning >> Sommario Concetti Avanzati • Introduzione • Ottimizzazione delle Interrogazioni • Un Esempio • Messa a Punto (“Tuning”) • Carico Applicativo • Strutture di Accesso • Modifiche allo Schema Logico • Parametri Architetturali G. Mecca - mecca@unibas.it - Basi di Dati

3. Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione Introduzione • Processo di Progettazione della BD • “progettazione logica”: dallo schema concettuale viene derivato: • uno schema logico standard • gli schemi esterni necessari • “progettazione fisica”: a partire dallo schema logico viene derivato: • uno schema fisico (strutture di accesso) • uno schema logico ottimizzato G. Mecca - mecca@unibas.it - Basi di Dati

4. Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione Introduzione • Progettazione Logica • l’obiettivo è fare in modo che la base di dati sia normalizzata (priva di anomalie) • algoritmo sistematico • Progettazione Fisica • l’obiettivo è fare in modo che la operazioni sulla base di dati siano efficienti • problema poco sistematizzabile G. Mecca - mecca@unibas.it - Basi di Dati

5. Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione Progettazione Fisica • L’obiettivo sono le prestazioni • si interviene su parametri che le influenzano • Parametri che influenzano le prestazioni • organizzazione dei file e strutture di accesso • schema logico • operazioni (query e transazioni) • parametri dell’architettura (buffer, dischi ecc.) • Questi aspetti sono difficili da progettare G. Mecca - mecca@unibas.it - Basi di Dati

6. Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione Messa a Punto (“Tuning”) • Tipicamente • si comincia con lo schema standard • le strutture di accesso standard • si accumula informazione sul funzionamento e si valutano le prestazioni • sulla base dei dati raccolti si procede alla messa a punto dei parametri • è un’attività condotta periodicamente G. Mecca - mecca@unibas.it - Basi di Dati

7. Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione Messa a Punto (“Tuning”) • In questa lezione • panoramica sulla progettazione fisica e il tuning • discuteremo le principali classi di parametri • descriveremo delle linee guida • non c’è la pretesa di essere esaustivi • Punto di partenza • valutazione e ottimizzazione delle interrogazioni in un DBMS relazionale G. Mecca - mecca@unibas.it - Basi di Dati

8. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Ottimizzazione delle Interrogazioni • Processo di valutazione di una query • la query viene inviata al DBMS interattivamente o da un’applicazione • il DBMS effettua l’analisi sintattica del codice SQL • il DBMS effettua le verifiche sulle autorizzazioni di accesso • il DBMS esegue il processo di ottimizzazione della query G. Mecca - mecca@unibas.it - Basi di Dati

9. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Ottimizzazione delle Interrogazioni • Processo di ottimizzazione • scelta di una strategia efficiente per la valutazione della query • Piano di esecuzione di una query • scelta dell’ordine di applicazione degli operatori algebrici necessari • strategia di calcolo del risultato di ciascun operatore algebrico attraverso le strutture di accesso disponibili G. Mecca - mecca@unibas.it - Basi di Dati

10. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Ottimizzazione delle Interrogazioni • Per effettuare l’ottimizzazione • vengono valutati molti diversi piani di esecuzione alternativi • l’ottimizzatore dispone di statistiche sul contenuto della base di dati (dimensione delle tabelle, dimensione dei record, dimensione degli indici, selettività ecc.) • sulla base delle statistiche viene stimato il costo di ciascun piano di esecuzione (numero di accessi ai blocchi su disco) G. Mecca - mecca@unibas.it - Basi di Dati

11. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio • Consideriamo la base di dati universitaria CREATE TABLE Docente ( codice char(4) PRIMARY KEY, cognome varchar(20) NOT NULL, nome varchar(20) NOT NULL, qualifica char(15), facolta char(10) ); CREATE TABLE Studente ( matricola integer PRIMARY KEY, cognome varchar(20) NOT NULL, nome varchar(20) NOT NULL, ciclo char(20), anno integer, relatore char(4) REFERENCES Docente(codice) ); G. Mecca - mecca@unibas.it - Basi di Dati

12. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio • Studiamo la seguente interrogazione: “Nomi e cognomi dei tesisti di Christian Vieri iscritti alla laurea specialistica” SELECT Studente.nome, Studente.cognome FROM Docente, Studente WHERE Docente.codice=Studente.relatore AND Studente.ciclo = ‘laurea sp.’ AND Docente.cognome = ‘Vieri’; G. Mecca - mecca@unibas.it - Basi di Dati

13. p S.nome, S.cognome Albero degli operatori della query s D.codice=S.relatore AND S.ciclo=‘laurea sp.’ AND D.cognome=‘Vieri’ X D S p S.nome, S.cognome ( s D.codice=S.relatore AND S.ciclo=‘laurea sp.’ AND D.cognome=‘Vieri’ (S X D) ) Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio • Forma standard SELECT S.nome, S.cognome FROM Docente AS D, Studente AS S WHERE D.codice=S.relatore AND S.ciclo = ‘laurea sp.’ AND D.cognome = ‘Vieri’; G. Mecca - mecca@unibas.it - Basi di Dati

14. p S.nome, S.cognome p S.nome, S.cognome s S.ciclo=‘laurea sp.’ AND D.cognome=‘Vieri’ s D.codice=S.relatore AND S.ciclo=‘laurea sp.’ AND D.cognome=‘Vieri’ X D.codice=S.relatore D S D S Piano B Piano A Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio • Non è l’unico possibile G. Mecca - mecca@unibas.it - Basi di Dati

15. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio • In generale un ottimizzatore dovrebbe preferire il piano B al piano A • i prodotti cartesiani sono costosi • In altri casi però • l’albero degli operatori da solo non basta a stabilire se una strategia è migliore di un’altra • è necessario considerare le strutture di accesso disponibili G. Mecca - mecca@unibas.it - Basi di Dati

16. p S.nome, S.cognome p S.nome, S.cognome s S.ciclo=‘laurea sp.’ s S.ciclo= ‘laurea sp.’ s D.cognome =‘Vieri’ s D.cognome =‘Vieri’ D.codice=S.relatore D.codice=S.relatore D S D S Piano D Piano C Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Altri Piani di Esecuzione G. Mecca - mecca@unibas.it - Basi di Dati

17. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio • In generale • il piano C è migliore di A e di B • in alcune condizioni il piano D è migliore di C • Dipende dalla strategia utilizzata per valutare gli operatori • in particolare dall’organizzazione dei file • e dalle strutture di accesso (indici) G. Mecca - mecca@unibas.it - Basi di Dati

18. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Operatore Algebrico • Tre tecniche principali • Scansione lineare del file • inefficiente, applicabile solo a file piccoli • Accesso attraverso indici • assume la presenza di indici (aggiornamenti) • Raggruppamento temporaneo • creazione di strutture aggiuntive per raggruppare le ennuple (es: ordinamenti, tabelle di hash in memoria centrale) G. Mecca - mecca@unibas.it - Basi di Dati

19. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di una Selezione con Ug. • File non ordinato, nessun indice rilevante • scansione lineare del file • File ordinato per l’attributo, nessun indice • ricerca binaria nel file (ennuple cons.) • Indice B+-tree sull’attributo • ricerca nell’indice • Indice di Hash sull’attributo • accesso diretto con la funzione di hashing G. Mecca - mecca@unibas.it - Basi di Dati

20. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Join • Strategia elementare • cicli nidificati • abbastanza inefficiente • Esempio: S JOIN D ON S.codice=D.rel. • per ogni ennupla di S • per ogni ennupla di D • se S.codice=D.relatore allora produci una ennupla del risultato G. Mecca - mecca@unibas.it - Basi di Dati

21. accesso basato sull’indice Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Join • Cicli nidificati con indice • sfrutto un indice su uno degli attributi di join • Esempio: S JOIN D ON S.relatore=D.cod • supponiamo di avere un indice su D.codice • per ogni ennupla di S • per ogni ennupla di D tale che D.cod=S.relatore • produci una ennupla del risultato G. Mecca - mecca@unibas.it - Basi di Dati

22. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Join • “Sort-Merge” Join • idea: se entrambe le tabelle sono ordinate sull’attributo di join, il join è lineare • Strategia • creo una copia ordinata delle tabelle • genero il risultato per scansione lineare • particolarmente efficiente se una delle tabelle è già ordinata G. Mecca - mecca@unibas.it - Basi di Dati

23. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Join • Hash Join • idea: hash sull’attributo di join per entrambe le tabelle • Strategia • costruisco in memoria centrale una tabella di hash per entrambe le tabelle sull’attr. di join • scandisco una tabella e per ciascun valore uso la funzione di hash per localizzare i bucket di ennuple corrispondenti G. Mecca - mecca@unibas.it - Basi di Dati

24. p S.nome, S.cognome s S.ciclo= ‘laurea sp.’ s D.cognome =‘Vieri’ D S D.codice=S.relatore Piano C Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Piano C • Supponiamo che: • non ci siano indici rilevanti • Selezioni • scansione lineare • Join • sort-merge • Piano completo G. Mecca - mecca@unibas.it - Basi di Dati

25. p S.nome, S.cognome s S.ciclo=‘laurea sp.’ s D.cognome =‘Vieri’ D.codice=S.relatore D S Piano D Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Piano D • Supponiamo che: • indice di hash sul nome docente • indice di hash sul relatore • Selezione • hash • Join • cicli con indice G. Mecca - mecca@unibas.it - Basi di Dati

26. p S.nome, S.cognome s S.ciclo=‘laurea sp.’ s D.cognome =‘Vieri’ D.codice=S.relatore D S Piano D Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Piano D Completo Calcolata al volo sul risultato del Join Ciclo nidificato con indice (uso l’indice secondario su S.relatore) Calcolata usando l’indice di hash (non è necessaria la materializzazione) G. Mecca - mecca@unibas.it - Basi di Dati

27. p S.nome, S.cognome s S.ciclo= ‘laurea sp.’ s D.cognome =‘Vieri’ D S D.codice=S.relatore Piano C Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Piano C, Altra Ipotesi • Supponiamo che: • indice di hash sul nome docente • indice secondario sul ciclo dello studente • Non molti benefici rispetto a D • il numero di valori di ciclo è basso • bassa selettività dell’indice G. Mecca - mecca@unibas.it - Basi di Dati

28. Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Ottimizzazione • Tutti i DBMS di fascia alta consentono di consultare i piani di esecuzione scelti • Comando EXPLAIN • sintassi tipica: EXPLAIN <select> • illustra il piano di esecuzione e fornisce la stima di costo da parte dell’ottimizzatore • utilizzabile sia con PgSQL che con MySQL G. Mecca - mecca@unibas.it - Basi di Dati

29. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Messa a Punto (“Tuning”) • Scenario tipico • dopo una fase iniziale, la base di dati viene sottoposta a valutazione delle prestazioni • le prestazioni sono inadeguate • è necessario intervenire per migliorare le prestazioni mettendo a punto i parametri • Punto di partenza • carico applicativo G. Mecca - mecca@unibas.it - Basi di Dati

30. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Carico Applicativo (“Workload”) • La messa a punto non è possibile per tutte le possibili interrogazioni • Si considerano le operazioni più frequenti e rilevanti • Carico applicativo • lista di interrogazioni • lista di aggiornamenti • prestazioni attese per ciascuna (es: <2s, oppure numero di transazioni al minuto) G. Mecca - mecca@unibas.it - Basi di Dati

31. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Attività del Tuning • Scelta delle strutture di accesso • organizzazione dei file, indici, clustering • Interventi sulle operazioni • riscritture, livelli di isolamento • Interventi sullo schema logico • partizionamenti, aggregazioni, denormalizzazioni • Interventi sui parametri architetturali • buffer, dischi, RAM G. Mecca - mecca@unibas.it - Basi di Dati

32. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso • Principale forma di intervento • aggiunta di indici • Attenzione • gli indici migliorano le prestazioni • ma rallentano gli aggiornamenti • richiedono spazio su disco • è necessario un compromesso G. Mecca - mecca@unibas.it - Basi di Dati

33. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso • Il caso estremo: base di dati di sola lettura • Esempio (Shasha, “Database Tuning”) • il sistema informativo di Ellis Island • archivio degli immigrati in USA tra l’800 e i primi del 900 (milioni di ennuple) • ricerche per cognome, nome e anno di arrivo • E’ possibile utilizzare molti indici • cognome, nome, anno, cognome e anno, cognome e nome G. Mecca - mecca@unibas.it - Basi di Dati

34. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso • Linea guida n.1 • è opportuno introdurre un indice solo se contribuisce a migliorare le prestazioni di più di una interrogazione del carico applicativo • Attenzione: • non sempre l’ottim. riesce ad usare un indice • es: select * from Impiegato where stipendioAnnuo/12>3000 • verificare il piano di esecuzione prima e dopo G. Mecca - mecca@unibas.it - Basi di Dati

35. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso • Linea guida n.2 • gli attributi su cui intervenire sono quelli che compaiono in join e selezioni • per condizioni di uguaglianza (es: stipendio=5000) sono da preferirsi indici hash • per condizioni su intervalli (es: stipendio>5000 and stipendio <10000) sono da preferirsi B+-tree G. Mecca - mecca@unibas.it - Basi di Dati

36. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso • Linea guida n.3 • è opportuno introdurre un indice solo se il numero di valori dell’attributo è sufficientemente alto • Esempio: • select * from impiegatiwhere stipendio = 10000 • l’indice su stipendio potrebbe non servire se sono molti ad avere uno stipendio uguale G. Mecca - mecca@unibas.it - Basi di Dati

37. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso • Linea guida n.4 • attenzione ai colli di bottiglia • Esempio n.1: • relazione disordinata con inserimenti frequenti • l’ultimo blocco è un collo di bottiglia • Esempio n.2: • modifiche allo schema (lock in scrittura sul catalogo) G. Mecca - mecca@unibas.it - Basi di Dati

38. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Interventi sulle Operazioni • Due forme • riscrittura delle operazioni • scelta del livello di isolamento per le trans. • Esempio: • select * from Impiegatowhere stipendioAnnuo/12>3000 • select * from Impiegatowhere stipendioAnnuo>3000*12 G. Mecca - mecca@unibas.it - Basi di Dati

39. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Interventi sulle Operazioni • Altre forme di riscrittura • limitare l’uso di nidificazione (difficilmente ottimizzabile) • Livello di isolamento • il livello standard è SERIALIZABLE • in molti casi READ COMMITTED è adeguato • in generale, è opportuno separare interrogazioni interattive e aggiornamenti G. Mecca - mecca@unibas.it - Basi di Dati

40. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico • Non è detto che lo schema normalizzato sia il più efficiente • Quattro forme di intervento principali • partizionamenti di tabelle • aggregazioni di tabelle • denormalizzazione di tabelle • aggiunta di informazione ridondante G. Mecca - mecca@unibas.it - Basi di Dati

41. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico • Attenzione • modificare lo schema logico impedisce alle applicazioni di lavorare correttamente • Due possibili soluzioni • le modifiche allo schema logico vanno decise molto presto (subito dopo la progettazione logica) • oppure, se è possibile, si crea uno schema esterno uguale al vecchio schema logico G. Mecca - mecca@unibas.it - Basi di Dati

42. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico • Partizionamenti di tabelle • tabelle con molti attributi possono essere spezzate in due • Esempio: la tabella “Studente” • chiave primaria (matricola) • attributi anagrafici (nome, cognome, codice fiscale, indirizzo, reddito del padre ecc.) • attributi accademici (ciclo, anno di corso, relatore, tirocinio, tutor ecc.) G. Mecca - mecca@unibas.it - Basi di Dati

43. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico • Posso dividere la tabella in due tabelle • DatiAnagraficiStudente: matricola e tutti gli attributi anagrafici • DatiUniversitariStudente: matricola e tutti gli attributi universitari • Conviene se • le operazioni richiedono di accedere raramente a tutti i dati • in questo caso è necessario un join G. Mecca - mecca@unibas.it - Basi di Dati

44. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico • Attenzione • questo è un esempio di ristrutturazione dello schema che deve essere effettuato molto presto • le viste non servono • definire la vista “Studente” corrispondente al join delle tabelle partizionate non darebbe nessun beneficio in termini di prestazioni G. Mecca - mecca@unibas.it - Basi di Dati

45. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico • Aggregazioni di tabelle • può essere utile per evitare join • Esempio: Studente e Tirocinio • chiave esterna “matricola” di tirocinio • se l’accesso ai dati del tirocinio è frequente, conviene riunirli in un’unica tabella • si evitano i join • aumentano i valori nulli • posso definire due viste per pres. lo schema G. Mecca - mecca@unibas.it - Basi di Dati

46. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico • Denormalizzazioni di tabelle • la normalizzazione evita le anomalie ma spesso costringe a fare troppi join • Esempio: Docente e Numeri • Numeri(numero, docente FK) • se devo frequentemente stampare l’elenco di nomi e numeri posso aggiungendo il nome del docente alla tabella Numeri • aumenta la complessità degli aggiornamenti G. Mecca - mecca@unibas.it - Basi di Dati

47. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico • In questo caso • si generano (modeste) anomalie di aggiornamento • esempio: ogni volta che cambio il cognome di un docente devo intervenire tanto su Docente che su Numeri • per evitare di creare istanze inconsistenti della base di dati è necessario utilizzare transazioni G. Mecca - mecca@unibas.it - Basi di Dati

48. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico • Aggiunta di informazione ridondante • in alcuni casi può evitare interrogazioni complesse • Esempio: numero di esami sostenuti • derivabile per aggregazione dal join di studenti ed esami • può essere memorizzato esplicitamente come attributo di Studente • costringe ad utilizzare le transazioni G. Mecca - mecca@unibas.it - Basi di Dati

49. Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Parametri Architetturali • Buffer • aumentare il buffer aumenta l’”hit ratio” • non è opportuno andare oltre un certo limite • Dischi • disporre i file su più dischi aumenta le prestazioni • es: disco per il log (il log è un tipico collo di bottiglia) G. Mecca - mecca@unibas.it - Basi di Dati

50. Progettazione Fisica e Tuning >> Sommario Concetti Avanzati • Introduzione • Ottimizzazione delle Interrogazioni • Un Esempio • Messa a Punto (“Tuning”) • Carico Applicativo • Strutture di Accesso • Modifiche allo Schema Logico • Parametri Architetturali G. Mecca - mecca@unibas.it - Basi di Dati