G. Mecca – [email protected] – Università della Basilicata Basi di Dati Progettazione Fisica e...

G. Mecca – [email protected] – Università della BasilicataG. Mecca – [email protected] – Università della Basilicata

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)

2G. Mecca - [email protected] - Basi di DatiG. Mecca - [email protected] - Basi di Dati

Concetti Avanzati

Introduzione Ottimizzazione delle Interrogazioni

Un Esempio Messa a Punto (“Tuning”)

Carico ApplicativoStrutture di AccessoModifiche allo Schema LogicoParametri Architetturali

Progettazione Fisica e Tuning >> Sommario


Introduzione

Processo di Progettazione della BD“progettazione logica”: dallo schema

concettuale viene derivato: uno schema logico standardgli schemi esterni necessari

“progettazione fisica”: a partire dallo schema logico viene derivato:uno schema fisico (strutture di accesso)uno schema logico ottimizzato

Progettazione Fisica e Tuning >> Concetti Avanzati >> Introduzione


Introduzione

Progettazione Logical’obiettivo è fare in modo che la base di dati

sia normalizzata (priva di anomalie) algoritmo sistematico

Progettazione Fisical’obiettivo è fare in modo che la operazioni

sulla base di dati siano efficientiproblema poco sistematizzabile



Progettazione Fisica

L’obiettivo sono le prestazionisi interviene su parametri che le influenzano

Parametri che influenzano le prestazioniorganizzazione dei file e strutture di accessoschema logicooperazioni (query e transazioni)parametri dell’architettura (buffer, dischi ecc.)

Questi aspetti sono difficili da progettare



Messa a Punto (“Tuning”)

Tipicamentesi comincia con lo schema standardle strutture di accesso standardsi accumula informazione sul funzionamento

e si valutano le prestazionisulla base dei dati raccolti si procede alla

messa a punto dei parametriè un’attività condotta periodicamente




In questa lezionepanoramica sulla progettazione fisica e il

tuningdiscuteremo le principali classi di parametridescriveremo delle linee guidanon c’è la pretesa di essere esaustivi

Punto di partenzavalutazione e ottimizzazione delle

interrogazioni in un DBMS relazionale



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

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 queryscelta dell’ordine di applicazione degli

operatori algebrici necessaristrategia di calcolo del risultato di ciascun

operatore algebrico attraverso le strutture di accesso disponibili



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)



Un Esempio

Consideriamo la base di dati universitaria


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) );

CREATE TABLE Docente ( codice char(4) PRIMARY KEY, cognome varchar(20) NOT NULL, nome varchar(20) NOT NULL, qualifica char(15), facolta char(10) );


Un Esempio

Studiamo la seguente interrogazione: “Nomi e cognomi dei tesisti di Christian Vieri iscritti alla laurea specialistica”


SELECT Studente.nome, Studente.cognomeFROM Docente, StudenteWHERE Docente.codice=Studente.relatore AND Studente.ciclo = ‘laurea sp.’ AND Docente.cognome = ‘Vieri’;


Un Esempio

Forma standard


SELECT S.nome, S.cognomeFROM Docente AS D, Studente AS SWHERE D.codice=S.relatore AND S.ciclo = ‘laurea sp.’ AND D.cognome = ‘Vieri’;

S D

S.nome, S.cognome

D.codice=S.relatore AND S.ciclo=‘laurea sp.’ AND D.cognome=‘Vieri’

X

S.nome, S.cognome D.codice=S.relatore AND S.ciclo=‘laurea sp.’ AND D.cognome=‘Vieri’

(S X D) )

Albero degli operatori della query


Un Esempio

Non è l’unico possibile


S D

S.nome, S.cognome

D.codice=S.relatore AND S.ciclo=‘laurea sp.’ AND D.cognome=‘Vieri’

X

Piano A S D

S.ciclo=‘laurea sp.’ AND D.cognome=‘Vieri’

S.nome, S.cognome

D.codice=S.relatore

Piano B


Un Esempio

In generale un ottimizzatore dovrebbe preferire il piano B al piano Ai 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



Altri Piani di Esecuzione


S D

S.ciclo= ‘laurea sp.’

S.nome, S.cognome

D.codice=S.relatore

Piano C

D.cognome =‘Vieri’

S

S.ciclo=‘laurea sp.’

S.nome, S.cognome

D

D.codice=S.relatore

Piano D



Un Esempio

In generaleil piano C è migliore di A e di Bin alcune condizioni il piano D è migliore di C

Dipende dalla strategia utilizzata per valutare gli operatoriin particolare dall’organizzazione dei filee dalle strutture di accesso (indici)



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)



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



Esecuzione di un Join

Strategia elementarecicli nidificatiabbastanza 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




Cicli nidificati con indicesfrutto un indice su uno degli attributi di join

Esempio: S JOIN D ON S.relatore=D.codsupponiamo di avere un indice su D.codiceper ogni ennupla di S

per ogni ennupla di D tale che D.cod=S.relatore– produci una ennupla del risultato


accesso basato sull’indice



“Sort-Merge” Joinidea: se entrambe le tabelle sono ordinate

sull’attributo di join, il join è lineare Strategia

creo una copia ordinata delle tabellegenero il risultato per scansione lineareparticolarmente efficiente se una delle

tabelle è già ordinata




Hash Joinidea: 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



Piano C

Supponiamo che:non ci siano

indici rilevanti Selezioni

scansione lineare Join

sort-merge Piano completo


S D

S.ciclo=

‘laurea sp.’

S.nome, S.cognome

D.codice=S.relatore

Piano C

D.cognome

=‘Vieri’


Piano D

Supponiamo che:indice di hash sul

nome docenteindice di hash sul

relatore Selezione

hash Join

cicli con indice


S


S.nome, S.cognome

D

D.codice=S.relatore

Piano D



Piano D Completo


Calcolata usando l’indicedi hash (non ènecessaria la materializzazione)

Ciclo nidificato con indice(uso l’indice secondariosu S.relatore)

Calcolata al volosul risultato del Join

S


S.nome, S.cognome

D

D.codice=S.relatore

Piano D



Piano C, Altra Ipotesi

Supponiamo che:indice di hash sul

nome docenteindice secondario sul

ciclo dello studente Non molti benefici

rispetto a Dil numero di valori di

ciclo è bassobassa selettività

dell’indice


S D

S.ciclo=

‘laurea sp.’

S.nome, S.cognome

D.codice=S.relatore

Piano C

D.cognome

=‘Vieri’


Ottimizzazione

Tutti i DBMS di fascia alta consentono di consultare i piani di esecuzione scelti

Comando EXPLAINsintassi tipica: EXPLAIN <select>illustra il piano di esecuzione e fornisce la

stima di costo da parte dell’ottimizzatoreutilizzabile sia con PgSQL che con MySQL




Scenario tipicodopo una fase iniziale, la base di dati viene

sottoposta a valutazione delle prestazionile prestazioni sono inadeguateè necessario intervenire per migliorare le

prestazioni mettendo a punto i parametri Punto di partenza

carico applicativo

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 applicativolista di interrogazionilista di aggiornamentiprestazioni attese per ciascuna (es: <2s,

oppure numero di transazioni al minuto)



Attività del Tuning

Scelta delle strutture di accessoorganizzazione dei file, indici, clustering

Interventi sulle operazioniriscritture, livelli di isolamento

Interventi sullo schema logicopartizionamenti, aggregazioni, denormalizzazioni

Interventi sui parametri architetturalibuffer, dischi, RAM



Strutture di Accesso

Principale forma di interventoaggiunta di indici

Attenzionegli indici migliorano le prestazioni ma rallentano gli aggiornamentirichiedono spazio su discoè necessario un compromesso




Il caso estremo: base di dati di sola lettura Esempio (Shasha, “Database Tuning”)

il sistema informativo di Ellis Islandarchivio 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 indicicognome, nome, anno, cognome e anno,

cognome e nome




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 indicees: select * from Impiegato

where stipendioAnnuo/12>3000verificare il piano di esecuzione prima e

dopo




Linea guida n.2gli attributi su cui intervenire sono quelli che

compaiono in join e selezioniper condizioni di uguaglianza (es:

stipendio=5000) sono da preferirsi indici hashper condizioni su intervalli (es:

stipendio>5000 and stipendio <10000) sono da preferirsi B+-tree




Linea guida n.3è opportuno introdurre un indice solo se il

numero di valori dell’attributo è sufficientemente alto

Esempio:select * from impiegati

where stipendio = 10000l’indice su stipendio potrebbe non servire se

sono molti ad avere uno stipendio uguale




Linea guida n.4attenzione ai colli di bottiglia

Esempio n.1:relazione disordinata con inserimenti

frequentil’ultimo blocco è un collo di bottiglia

Esempio n.2:modifiche allo schema (lock in scrittura sul

catalogo)



Interventi sulle Operazioni

Due formeriscrittura delle operazioniscelta del livello di isolamento per le trans.

Esempio:select * from Impiegato

where stipendioAnnuo/12>3000select * from Impiegato

where stipendioAnnuo>3000*12



Interventi sulle Operazioni

Altre forme di riscritturalimitare l’uso di nidificazione (difficilmente

ottimizzabile) Livello di isolamento

il livello standard è SERIALIZABLEin molti casi READ COMMITTED è adeguatoin generale, è opportuno separare

interrogazioni interattive e aggiornamenti



Modifiche allo Schema Logico

Non è detto che lo schema normalizzato sia il più efficiente

Quattro forme di intervento principalipartizionamenti di tabelleaggregazioni di tabelledenormalizzazione di tabelleaggiunta di informazione ridondante




Attenzionemodificare 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




Partizionamenti di tabelletabelle 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.)




Posso dividere la tabella in due tabelleDatiAnagraficiStudente: matricola e tutti gli

attributi anagraficiDatiUniversitariStudente: matricola e tutti gli

attributi universitari Conviene se

le operazioni richiedono di accedere raramente a tutti i dati

in questo caso è necessario un join




Attenzionequesto è un esempio di ristrutturazione dello

schema che deve essere effettuato molto presto

le viste non servonodefinire la vista “Studente” corrispondente al

join delle tabelle partizionate non darebbe nessun beneficio in termini di prestazioni




Aggregazioni di tabellepuò essere utile per evitare join

Esempio: Studente e Tirociniochiave esterna “matricola” di tirociniose l’accesso ai dati del tirocinio è frequente,

conviene riunirli in un’unica tabellasi evitano i joinaumentano i valori nulliposso definire due viste per pres. lo schema




Denormalizzazioni di tabellela 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




In questo casosi generano (modeste) anomalie di

aggiornamentoesempio: 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




Aggiunta di informazione ridondantein 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



Parametri Architetturali

Bufferaumentare il buffer aumenta l’”hit ratio”non è opportuno andare oltre un certo limite

Dischidisporre i file su più dischi aumenta le

prestazionies: disco per il log (il log è un tipico collo di

bottiglia)



Concetti Avanzati

Introduzione Ottimizzazione delle Interrogazioni

Un Esempio Messa a Punto (“Tuning”)

Carico ApplicativoStrutture di AccessoModifiche allo Schema LogicoParametri Architetturali

Progettazione Fisica e Tuning >> Sommario


Termini della Licenza

Termini della Licenza

This work is licensed under the Creative Commons Attribution-ShareAlike 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.

Questo lavoro viene concesso in uso secondo i termini della licenza “Attribution-ShareAlike” 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 – [email protected] – Università della Basilicata Basi di Dati Progettazione Fisica e...

Documents

Transcript of G. Mecca – [email protected] – Università della Basilicata Basi di Dati Progettazione Fisica e...