1 / 51

Basi di Dati

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). Progettazione Fisica e Tuning >> Sommario. Concetti Avanzati. Introduzione

kerry
Download Presentation

Basi di Dati

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related