paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL e l'InnoDB engine

In questo articolo illustro in maniera teorica, cos'e' l'InnoDB engine e come MySQL lo gestisce. Essendo appunti presi mentre studiavo per la certificazione MySQL DBA I, mi scuso in anticipo se incontrerete un misto di inglese ed italiano non proprio perfetto. :)
Gli argomenti saranno divisi in questi gruppi:

  • Data, Tablespace e log files
  • Transaction Model
  • Row Level locking
  • Isolation levels, Multi-versioning and concurrency
  • Foreign Key
  • Configuring InnoDB Buffers and Logs

Se correttamente impostati i parametri che vedremo in seguiro, l'InnoDB engine garantisce l'ACID compliance, ovvero Atomic, Consistency, Isolation, Durability e partendo da questa importante considerazione, iniziamo a scendere nei dettagli delle sue caratteristiche principali.

Data e Tablespace files
Data File
.frm:come per tutti gli altri engine, salvato nella database directory.
Tablespace
a) Di default tutte le tabelle vengono archiviate logicamente in una sola shared tablespace
b) Opzione --innodb_file_per_table: Permette di assegnare ad ogni tabella una propria tablespace (.idb). Nota: Questa opzione non influisce su eventuali tabelle, che prima del cambio della configurazione, condividevano una shared tablespace.
Una tablespace, contiene queste informazioni:
a) data rows
b) indexes
c) rollback segment, che consiste in un segmento in un undo log record delle transaction attive, cosi' da permettere il loro eventuale ripristino.
E' formata fisicamente da uno o piu' file che vengono archiviati in una directory del server e possono essere autoextend.
Di default viene creato un solo file di 10MB, auto-extending, chiamato ibdata1 nella directory dei dati di MySQL.
Per modificare questi parametri occorre usare queste opzioni

innodb_data_file_path
Si possono definire uno piu' file e la loro dimensione (minimo 10MB). Ogni parametro deve essere separa da ; e questi sono alcuni degli esempi validi

[mysqld]
innodb_data_file_path= innodata1:100M

[mysqld]
innodb_data_file_path= innodata1:100M:autoextend

[mysqld]
innodb_data_file_path= innodata1:100M:autoextend:max:500M

[mysqld]
innodb_data_file_path= innodata1:100M; innodata2:200M


innodb_data_home_dir
Indica dove verra' salvato fisicamente il file della tablespace

[mysqld]
innodb_data_home_dir = /dev/hdc6
innodb_data_file_path= innodata1:100M

[mysqld]
innodb_data_home_dir = 	<- nessun valore!!!
innodb_data_file_path= /dev/hdc5/innodata1:100M; /dev/hdc6/innodata1:200M


Caso raw partition
In questo caso occorre procedere nel seguente modo, usando l'opzione newraw, ovvero
Prima dello startup di MySQL modificare cosi

[mysqld]
innodb_data_home_dir = 	<- nessun valore!!!
innodb_data_file_path= /dev/hdc6:10Gnewraw

Startup del server ed attendere che InnoDB inizializzi la tablespace
Stop del server e modificare in questo modo la configurazione

[mysqld]
innodb_data_home_dir = 
innodb_data_file_path= /dev/hdc6:10Graw


Startup del server

caso di una tablespace per tabella
Per creare una tablespace per ogni tabella, occorre usare questa opzione

[mysqld]
innodb_data_file_path= innodata1:100M
innodb_file_per_table

Nota: Verra' comunque creata la shared partition, per contenere il data dictionary e il rollback segment

Transaction model
Piu' client possono eseguire transaction simultaneamente e per default, ognuna di queste e' sequenziale, ovvero avviene un autocommit appena terminata una singola istruzione, per ogni client
Per raggruppare le transaction, esistono due metodi
Disabilitare esplicitamente l'autocommit

SET AUTOCOMMIT = 0;
… statement for transaction 1
COMMIT;

Nota: rimane disabilitato fino a quando non si esegue SET AUTOCOMMIT = 1
Sospendere l'autocommit

START TRANSACTION;
BEGIN;
BEGIN WORK;
...statement for transaction 1
COMMIT;

Nota: l'istruzione BEGIN in questo caso, non ha niente a che vedere con la BEGIN/END delle stored procedure (chiamate anche compound statments)

Savepoint
Mentre l'autocommit e' abilitato, il commit ed il rollback sono automatici e quindi il savepoint non ha senso, mentre si usa nel caso si voglia eseguire il rollback di solo una parte di una transaction.

SAVEPOINT savepoint_name

e per eseguire il rollback

ROLLBACK TO SAVEPOINT savepoint_name

Nota: si possono definire piu' savepoint all'interno di una singola transaction e tutti i save point che sono stati definiti dopo quello in rollback, verranno eliminati.

Row Level locking
Il lock avviene a livello di row.
Innodb permette due locking modifier da aggiungere alla fine di una istruzione di SELECT

LOCK IN SHARED MODE

In questo caso tutte le altre transaction possono vedere i dati della ROW ma non possono modificarli ne' cancellarli, fino a quando la transaction che ha eseguito la prima select non rilascia il lock.

UPDATE

Esegue un exclusive lock sulle righe selezionate. E' comodo nel caso si voglia selezionare e poi modificare un set di righe, senza che altre transaction influiscano. Cio' che invece accadrebbe nel caso del tipo di lock precedente

Isolation levels, Multi-versioning and concurrency
Il transaction isolation level, indica il livello di isolamento tra differenti transaction, ovvero la maniera in cui differenti transaction possono interagire sugli stessi dati.

Questo meccanismo, presenta tre possibili problemi.
Dirty read: la lettura di dati non commited da parte di un'altra transaction. Ad esempio se T1 fa rollback, i cambiamenti non sono stati ancora effettuati e T2 legge gli stessi dati.
Non-repeatable read: la lettura degli stessi dati risulta differente in due estrazioni successive della stessa transaction. Ovvero non consistency dei dati. T1 legge dei dati, T2 li modifica, T1 legge di nuovo gli stessi dati e questi sono diversi.
Phantom: quando appare una row che non era visibile in precedenza. Ad esempio T1 e T2 attive. T1 legge delle rows, T2 inserisce una nuova row, T1 legge di nuovo e trova una row che prima non c'era. Questa viene identificata come un phantom.

Innodb implementa 4 isolation levels
READ UNCOMMITTED: permette ad una transaction di vedere le modifiche non commited di un'altra transaction (questo permette che si possano produrre i 3 problemi problemi precedenti.
READ COMMITTED: permette ad una transaction di vedere le modifiche di un'altra transaction, solo se queste sono state commited. (questo si possano produrre 2 problemi: non-repeatable read e phantom)
REPEATABLE READ (di default): Permette di leggere gli stessi dati se eseguita la select due volte. Ovvero una transaction non puo' modificare i dati che un'altra transaction ha gia' modificato. (non pemette nemmeno che si verifichi il problema del phantom)
SERIALIZABLE: Isola completamente gli effetti di una transaction rispetto ad un'altra. E' simile alla precedente, con la differenza che le righe selezionata da una transaction non possono essere modificate da una seconda transaction, fino a quando la seconda non termina.

Il Multi-versioning permette di avere piu' versioni della stessa riga, una per ogni transaction che la sta modificando/selezionando. Ovvero ogni transaction vede una view del contenuto del DB appropriato al suo isolation level.
Per modificare l'isolation level utilizzare questa istruzione

[mysqld]
transaction-isolation= READ-COMMITTED (oppure 	READ-UNCOMMITTED
						REPETEABLE-READ
						SERIALIZABLE)

Nota: puo' essere gestito anche quando MySQL server e' attivo, usando questi tre modalita'

SET GLOBAL TRANSACTION ISOLATION LEVEL isolation_level;

applicato a tutte le prossime operazioni di tutti i client. Nota: necessari privilegi di root.

SET SESSION TRANSACTION ISOLATION LEVEL isolation_level 

Limitato a tutte le successive transactions del client che ha eseguito il comando

SET TRANSACTION TRANSACTION ISOLATION LEVEL isolation_level 

Limitato a tutte le successive transactions della transaction che ha eseguito il comando

Foreign KEY
Sulle FK non entro in dettaglio, perche' e' un argomento noto, ma ricordo l'importanza delle opzioni UPDATE e DELETE CASCADE che permettono, nel caso si modificho o si cancelli il valore padre di una FK, di aggiornare tutti i relativi valori in cascata, sulle tabelle figlie. Vanno definite al momento del CREATE TABLE, in questo modo

CREATE TABLE table01
(
	...
        ...
	FOREIGN KEY(campo01)
  		REFERENCE yyyy (campo02)
		ON UPDATE CASCADE
		ON DELETE CASCADE
) ENGINE = InnoDB;


Configuring InnoDB Buffers and Logs
Buffer poll: viene usato per archiviare in memoria le letture sul DB e quindi limitare l'uso del di disco. Di default e' impostato a 8MB e per modificarlo, l'opzione e' --innodb_buffer_poll_size

Buffer log: Quando una transaction esegue il commit, questo viene flushed su disco e la relativa l'opzione e' innodb_buffer_poll_size e tipicamente il valore e' tra 1 e 8MB.

innodb_log_file_in_group e innodb_log_file_size
Di default InnoDB crea 2 file di log da 5MB ciascuno, chiamati id_logfile0e ib_logfile1. Per modificare queste opzioni, modificare in questo modo

[mysqld]
innodb_log_file_in_group = 2
innodb_log_file_size = 50MB

L'informazione viene scritta in maniera circolare. Quindi e' importante che il file sia grande, perche' cosi' si evita di dover rigenerare spesso il puntatore al file e quindi l'accesso al disco.

innodb_flush_log_at_trx_commit
Influisce su come InnoDB trasferisce le informazioni dal
Buffer memory ---> filesystem cache ---> log file disk
InnoDB esegue di default il flush circa ogni secondo, e questo parametro permette di definire quanti log writing e flushing possono avvenire in aggiunta.
Questi i valori, da scegliere a seconda delle esigenze di ACID.
1 : Per ogni commit. Garantisce che non vengano persi i dati in caso di crash (ACID durability). Pero' rallenta il sistema.
0 : Ogni secondo per NON per ogni commit. Riduce considerevolmente l'attivita' del disco, ma e' rischioso per eventuali perdite di dati.
2: Ogni commit dal log buffer ---> log filesystem cache
Ogni secondo dal log filesystem cache ---> disk

Questo comporta che in caso di crash di MySQL server, il server/macchina, che rimane comunque attiva, permette di fare il flush delle informazioni presenti nella cache del filesystem verso il filesytem, e quindi non verranno perse.