Basi di Dati Progettazione Fisica e Tuning Concetti

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

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione m 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 3

Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione m Progettazione Logica ðl’obiettivo è fare in modo che la base di dati sia normalizzata (priva di anomalie) ðalgoritmo sistematico m 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 4

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione Messa a Punto (“Tuning”) m 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 6

Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione Messa a Punto (“Tuning”) m 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 m Punto di partenza ðvalutazione e ottimizzazione delle interrogazioni in un DBMS relazionale G. Mecca - mecca@unibas. it - Basi di Dati 7

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione delle Interrogazioni m 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 8

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione delle Interrogazioni m Processo di ottimizzazione ðscelta di una strategia efficiente per la valutazione della query m 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 9

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione delle Interrogazioni m 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 10

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio m 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 12

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio m Forma Albero degli operatori della query 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’; p S. nome, S. cognome 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 (S X D) ) AND S. ciclo=‘laurea sp. ’ AND D. cognome=‘Vieri’ G. Mecca - mecca@unibas. it - Basi di Dati 13

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio m In generale un ottimizzatore dovrebbe preferire il piano B al piano A ði prodotti cartesiani sono costosi m 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 15

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Un Esempio m In generale ðil piano C è migliore di A e di B ðin alcune condizioni il piano D è migliore di C m 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 17

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Operatore Algebrico m Tre tecniche principali m Scansione lineare del file ðinefficiente, applicabile solo a file piccoli m Accesso attraverso indici ðassume la presenza di indici (aggiornamenti) m 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 18

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Join m Strategia elementare ðcicli nidificati ðabbastanza inefficiente m 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 20

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Join m Cicli nidificati con indice ðsfrutto un indice su uno degli attributi di join m 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 accesso basato sull’indice G. Mecca - mecca@unibas. it - Basi di Dati 21

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Join m “Sort-Merge” Join ðidea: se entrambe le tabelle sono ordinate sull’attributo di join, il join è lineare m 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 22

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Esecuzione di un Join m Hash Join ðidea: hash sull’attributo di join per entrambe le tabelle m 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 23

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

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

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Ottimizzazione Piano C, Altra Ipotesi m Supponiamo che: p S. nome, S. cognome ðindice di hash sul nome docente ðindice secondario sul ciclo dello studente m 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 D. codice=S. relatore s S. ciclo= s D. cognome ‘laurea sp. ’ S Piano C =‘Vieri’ D 27

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Messa a Punto (“Tuning”) m 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 m Punto di partenza ðcarico applicativo G. Mecca - mecca@unibas. it - Basi di Dati 29

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

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso m Principale forma di intervento ðaggiunta di indici m 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 32

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso m Il caso estremo: base di dati di sola lettura m 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 m E’ possibile utilizzare molti indici ðcognome, anno, cognome e nome G. Mecca - mecca@unibas. it - Basi di Dati 33

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso m 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 35

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Strutture di Accesso m Linea guida n. 3 ðè opportuno introdurre un indice solo se il numero di valori dell’attributo è sufficientemente alto m Esempio: ðselect * from impiegati where 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 36

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

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

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Interventi sulle Operazioni m Altre forme di riscrittura ðlimitare l’uso di nidificazione (difficilmente ottimizzabile) m 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 39

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico m Non è detto che lo schema normalizzato sia il più efficiente m 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 40

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico m Attenzione ðmodificare lo schema logico impedisce alle applicazioni di lavorare correttamente m 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 41

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico m Partizionamenti di tabelle ðtabelle con molti attributi possono essere spezzate in due m 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 42

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico m Posso dividere la tabella in due tabelle ðDati. Anagrafici. Studente: matricola e tutti gli attributi anagrafici ðDati. Universitari. Studente: matricola e tutti gli attributi universitari m 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 43

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico m 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 44

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico m Aggregazioni di tabelle ðpuò essere utile per evitare join m 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 45

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico m Denormalizzazioni di tabelle ðla normalizzazione evita le anomalie ma spesso costringe a fare troppi join m 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 46

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico m 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 47

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Modifiche allo Schema Logico m Aggiunta di informazione ridondante ðin alcuni casi può evitare interrogazioni complesse m 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 48

Progettazione Fisica e Tuning >> Concetti Avanzati >> Tuning Parametri Architetturali m Buffer ðaumentare il buffer aumenta l’”hit ratio” ðnon è opportuno andare oltre un certo limite m 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 49

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

Termini della Licenza m This work is licensed under the Creative Commons Attribution. Share. Alike License. To view a copy of this license, visit http: //creativecommons. org/licenses/by-sa/1. 0/ or send a letter to Creative Commons, 559 Nathan Abbott Way, Stanford, California 94305, USA. m Questo lavoro viene concesso in uso secondo i termini della licenza “Attribution-Share. Alike” di Creative Commons. Per ottenere una copia della licenza, è possibile visitare http: //creativecommons. org/licenses/by-sa/1. 0/ oppure inviare una lettera all’indirizzo Creative Commons, 559 Nathan Abbott Way, Stanford, California 94305, USA. G. Mecca - mecca@unibas. it - Basi di Dati 51