Die InnoDB Storage Engine (Teil 1)

15
Die InnoDB Storage Engine Die wunderbare Welt von Isotopp Mittwoch, 30. Januar 2008 Die InnoDB Storage Engine Die Storage Engine InnoDB ist eine Storage Engine, die ACID konform betrieben werden kann, Transaktionen beherrscht und Foreign Key Constraints prüfen kann. Sie ist geeignet für alle Anwendungen, die Online Transaction Processing machen oder aus anderen Gründen eine hohe Rate von paralellen Schreibzugriffen haben. Hat mein MySQL InnoDB Support und ist dieser betriebsbereit? Mit dem Kommano SHOW ENGINES kann man sich die von einer Instanz unterstützten Engines anzeigen lassen. Wenn InnoDB enthalten und betriebsbereit ist, wird die Engine mit YES angezeigt. Wenn sie enthalten und nicht betriebsbereit ist, wird DISABLED gezeigt. Wenn sie nicht einmal im Code des Servers enthalten ist, wird NO gezeigt. CODE: root on mysql.sock [(none)]> show engines; ++++ | Engine | Support | Comment | ++++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, rowlevel locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and pagelevel locking | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE | YES | Example storage engine | | ARCHIVE | YES | Archive storage engine | | CSV | YES | CSV storage engine | | ndbcluster | NO | Clustered, faulttolerant, memorybased tables | | FEDERATED | YES | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine | ++++ 12 rows in set (0.00 sec) Eine minimale Konfiguration für InnoDB Für die folgenden Beispiele ist es notwendig, daß der MySQLServer mit einem funktionierenden InnoDB ausgestattet ist. Die folgende MinimalKonfiguration ist nicht für die Produktion geeignet, sollte aber ausreichen um von DISABLED auf YES zu kommen. Mit SHOW GLOBAL VARIABLES LIKE 'datadir' ist das Datenverzeichnis für diese Instanz von MySQL zu lokalisieren. In diesem Verzeichnis befinden sich unter Umständen ein Datenfile ibdata1 und zwei Logfiles, ib_logfile0 und ib_logfile1. CODE: root on mysql.sock [(none)]> show global variables like 'datadir'; ++ + | Variable_name | Value | + ++ | datadir | /export/data/rootforum/data/ | + ++ 1 row in set (0.00 sec) root on mysql.sock [(none)]> quit Bye linux:/export/data/rootforum # ls

Transcript of Die InnoDB Storage Engine (Teil 1)

Die InnoDB Storage EngineDie wunderbare Welt von Isotopp

Mittwoch, 30. Januar 2008Die InnoDB Storage EngineDie Storage Engine InnoDB ist eine Storage Engine, die ACID­konform betrieben werden kann, Transaktionen beherrscht undForeign Key Constraints prüfen kann. Sie ist geeignet für alleAnwendungen, die Online Transaction Processing machen oder ausanderen Gründen eine hohe Rate von paralellen Schreibzugriffen haben.Hat mein MySQL InnoDB Support und ist dieser betriebsbereit?

Mit dem Kommano SHOW ENGINES kann man sich die von einer Instanzunterstützten Engines anzeigen lassen. Wenn InnoDB enthalten und betriebsbereitist, wird die Engine mit YES angezeigt. Wenn sie enthalten und nicht betriebsbereitist, wird DISABLED gezeigt. Wenn sie nicht einmal im Code des Servers enthaltenist, wird NO gezeigt.CODE:root on mysql.sock [(none)]> show engines;

+­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+| Engine | Support | Comment |+­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance || MEMORY | YES | Hash based, stored in memory, useful for temporary tables || InnoDB | YES | Supports transactions, row­level locking, and foreign keys || BerkeleyDB | NO | Supports transactions and page­level locking || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) || EXAMPLE | YES | Example storage engine || ARCHIVE | YES | Archive storage engine || CSV | YES | CSV storage engine || ndbcluster | NO | Clustered, fault­tolerant, memory­based tables || FEDERATED | YES | Federated MySQL storage engine || MRG_MYISAM | YES | Collection of identical MyISAM tables || ISAM | NO | Obsolete storage engine |+­­­­­­­­­­­­+­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+12 rows in set (0.00 sec)

Eine minimale Konfiguration für InnoDB

Für die folgenden Beispiele ist es notwendig, daß der MySQL­Server mit einemfunktionierenden InnoDB ausgestattet ist. Die folgende Minimal­Konfiguration istnicht für die Produktion geeignet, sollte aber ausreichen um von DISABLED auf YESzu kommen.

Mit SHOW GLOBAL VARIABLES LIKE 'datadir' ist das Datenverzeichnis fürdiese Instanz von MySQL zu lokalisieren. In diesem Verzeichnis befinden sichunter Umständen ein Datenfile ibdata1 und zwei Logfiles, ib_logfile0 undib_logfile1.CODE:root on mysql.sock [(none)]> show global variables like 'datadir'; +­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+ | Variable_name | Value | +­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+ | datadir | /export/data/rootforum/data/ | +­­­­­­­­­­­­­­­+­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­+ 1 row in set (0.00 sec)root on mysql.sock [(none)]> quit Bye linux:/export/data/rootforum # ls ­

lh /export/data/rootforum/data/ib* ­rw­rw­­­­ 1 mysql mysql 5M Jan 9 17:51 /export/data/rootforum/data/ib_logfile0 ­rw­rw­­­­ 1 mysql mysql 5M Jan 9 17:51 /export/data/rootforum/data/ib_logfile1 ­rw­rw­­­­ 1 mysql mysql 10M Dec 13 14:34 /export/data/rootforum/data/ibdata1

Der Datenbankserver ist zu stoppen.Die o.a. drei Files sind, wenn vorhanden, zu löschen. WARNUNG! Dies löschtalle evtl. bereits vorhandenen Daten in InnoDB. Diesen Schritt nur danndurchführen, wenn die Engine vorher DISABLED war.In der Sektion [mysqld] der my.cnf ist die Anweisung skip­innodb zu finden,wenn vorhanden, und zu entfernen.Die folgenden Konfigurationsanweisungen sind stattdessen einzufügen. IhreBedeutung wird weiter unten erläutert.CODE:innodb innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 2

Dies ist ein minimales Setup, das zum Testen geeignet ist, aber keinePerformance bringen wird. Später gehen wir auch auf InnoDB PerformanceTuning ein.Der Server ist zu starten. Er legt die o.a. drei Files neu an, und hinterläßtentsprechende Nachrichten im Log. Die Engine InnoDB wird in SHOWENGINES jetzt mit YES angezeigt.

InnoDB Tabellen anlegen

Beim Anlegen einer Tabellenspezifikation kann durch das Nachstellen einerENGINE­Klausel festgelegt werden, mit welcher Storage Engine die Tabelle erzeugtwird. Dies kann innerhalb eines Schemas für jede Tabelle getrennt festgelegtwerden, und mit Hilfe von ALTER TABLE auch nachträglich ohne Datenverlustgeändert werden.CODE:root on mysql.sock [(none)]> create database innodemo;

Query OK, 1 row affected (0.32 sec)

root on mysql.sock [(none)]> use innodemo;Database changedroot on mysql.sock [innodemo]> create table kris ( id integer unsigned not null primary key auto_increment, d varchar(20) not null ) engine = innodb;Query OK, 0 rows affected (0.36 sec)

root on mysql.sock [innodemo]> insert into kris ( d ) values ( "eins"), ("zwei"), ("drei");Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0

root on mysql.sock [innodemo]> select * from kris;+­­­­+­­­­­­+| id | d |+­­­­+­­­­­­+| 1 | eins || 2 | zwei || 3 | drei |+­­­­+­­­­­­+3 rows in set (0.01 sec)

Mit Hilfe des Kommando SHOW CREATE TABLE oder mit SHOW TABLE STATUSkönnen wir sehen, welche Storage Engine eine Tabelle verwendet.CODE:root on mysql.sock [innodemo]> show create table kris\G

*************************** 1. row *************************** Table: krisCreate Table: CREATE TABLE `kris` ( `id` int(10) unsigned NOT NULL auto_increment, `d` varchar(20) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.00 sec)

root on mysql.sock [innodemo]> show table status like "kris"\G

*************************** 1. row ***************************

Name: kris

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 3

Avg_row_length: 5461

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 4

Create_time: 2008­01­09 18:04:00

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment: InnoDB free: 148480 kB

1 row in set (0.00 sec)

In der Ausgabe von SHOW TABLE STATUS sehen wir einige Besonderheiten: Ersteinmal ist die angezeigte Average Row Length von InnoDB­Tabellen mit sehrwenigen Datensätzen ungültig. Die Average Row Length unserer Tabelle ist, daswissen wir, ungefähr 4 (integer) + 5 (1 Längenbyte und vier Zeichenbytes) plus einwenig Verwaltungsoverhead (ca. 10­12 Byte pro Row). InnoDB vergibt Speicherplatzjedoch in Seiten zu 16KB, und daher ist die Data_length = 16384 Bytes. Die AverageRow Length wird von InnoDB als Statistik nicht gepflegt und an MySQL exportiertund wird daher als Data_length/Rows geschätzt. Für kleine Anzahlen von Zeilen istdieser Wert falsch.

Außerdem können wir sehen, daß die Index_length für diese Tabelle 0 ist, obwohl einPrimary Key definiert ist. Das wird uns später noch im Detail beschäftigen. Für daserste genügt es zu wissen, das der Primary Key in InnoDB zu den Daten gerechnetwird und auch sonst magisch ist. Data_free ist, anders als in MyISAM, immer 0.

Existierende Tabellen im Typ ändern

Mit den Kommando ALTER TABLE können wir eine existierende Tabelle von InnoDBnach MyISAM umwandeln oder zurück. Daten gehen dabei nicht verloren. ZumVergleich hier einmal die Ausgabe von SHOW TABLE STATUS für dieselbe Tabelle,wenn sie als MyISAM­Tabelle existiert. Man kann sehen, daß MyISAM den PrimaryKey NICHT zu den Daten rechnet und daß MyISAM im Index ebenfalls seitenbasiertarbeitet, nur sind die Seiten viel kleiner: Sie sind nur 1KB groß.CODE:root on mysql.sock [innodemo]> alter table kris engine=myisam;

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+

| id | d |

+­­­­+­­­­­­+

| 1 | eins |

| 2 | zwei |

| 3 | drei |

+­­­­+­­­­­­+

3 rows in set (0.00 sec)

root on mysql.sock [innodemo]> show table status like "kris"\G

*************************** 1. row ***************************

Name: kris

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 3

Avg_row_length: 20

Data_length: 60

Max_data_length: 281474976710655

Index_length: 2048

Data_free: 0

Auto_increment: 4

Create_time: 2008­01­09 18:11:57

Update_time: 2008­01­09 18:11:57

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

Wir können unsere Tabelle auch wieder zurück nach InnoDB wandeln. Wir könnensogar eine existierende InnoDB­Tabelle nach InnoDB wandeln. Das macht sogarSinn ­ es ist genau das, was OPTIMIZE TABLE in InnoDB macht.CODE:root on mysql.sock [innodemo]> alter table kris engine=innodb;

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

root on mysql.sock [innodemo]> alter table kris engine=innodb;

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+

| id | d |

+­­­­+­­­­­­+

| 1 | eins |

| 2 | zwei |

| 3 | drei |

+­­­­+­­­­­­+

3 rows in set (0.00 sec)

Ein ALTER TABLE t ENGINE=... erzeugt eine Kopie der Tabelle mit der neuenEngine als temporäre Tabelle. Danach wird die Originaltabelle gelöscht und dietemporäre Tabelle als permanente Tabelle installiert. Das hat eine Reihe vonKonsequenzen:

Die Operation kann keine Daten verlieren. Wenn die Operation scheitert oderdurch Benutzer abgebrochen wird, wird die temporäre Tabelle gelöscht und dieOriginaltabelle bleibt unverändert stehen.Vorübergehend wird sehr viel mehr Speicher gebraucht, denn es müssen jabeide Tabellen parallel existieren. Dabei ist zu beachten, daß InnoDB die Datenweniger dicht packt als MyISAM. Eine InnoDB­Tabelle braucht etwa 1.6 bis 2.2mal so viel Platz wie eine MyISAM­Tabelle.Während des ALTER TABLE sind beide Tabellen gelockt. Wenn das stört, kannman sich mit der Sequenz

CREATE TABLE b LIKE a;ALTER TABLE b ENGINE=InnoDBINSERT INTO b SELECT * FROM a;

behelfen. Hier ist a dann während des Kopiervorganges nur mit einem ReadLock gesperrt. Außerdem kann man mit einer LIMIT­Clause am Select dieDatenmenge begrenzen, etwa um zu experimentieren.

Transaktionen

InnoDB beherrscht Transaktionen. Das bedeutet, das Anweisungen an InnoDB­Tabellen gesammelt werden und erst durch ein COMMIT gesammelt auf alle Tabellenangewendet werden. Entweder gelingen alle Anweisungen, oder keine von denAnweisungen wird ausgeführt.

Per Default befindet sich die Datenbank im AUTOCOMMIT Modus. Das bedeutet,

nach jeder Anweisung "denkt" sich der Server automatisch ein COMMIT dazu. Mankann entweder Autocommit abschalten, oder mit der Anweisung BEGIN auch imAutocommit eine längere Transaktion beginnen.CODE:root on mysql.sock [innodemo]> begin;

Query OK, 0 rows affected (0.00 sec)

root on mysql.sock [innodemo]> insert into kris ( d ) values ( "vier" );

Query OK, 1 row affected (0.00 sec)

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+

| id | d |

+­­­­+­­­­­­+

| 1 | eins |

| 2 | zwei |

| 3 | drei |

| 4 | vier |

+­­­­+­­­­­­+

4 rows in set (0.00 sec)

root on mysql.sock [innodemo]> rollback;

Query OK, 0 rows affected (0.00 sec)

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+

| id | d |

+­­­­+­­­­­­+

| 1 | eins |

| 2 | zwei |

| 3 | drei |

+­­­­+­­­­­­+

3 rows in set (0.00 sec)

Hier wird mit BEGIN eine Transaktion bei aktiviertem Autocommit begonnen, dasAutocommit also vorübergehend unterbrochen. Dies ist die empfohleneVorgehensweise. Eine Zeile wird in die Tabelle eingefügt. Für uns selber ist dieseZeile auch erst einmal sichtbar. Die Transaktion wird jedoch nicht mit COMMITbeendet, sondern mit ROLLBACK rückgängig gemacht. Dadurch ist nach dem Endeder Transaktion die Zeile wieder weg.

Wie InnoDB auf der Platte aussieht

CODE:linux:/export/data/rootforum/data # ls ­lh ib* innodemo

­rw­rw­­­­ 1 mysql mysql 5M Jan 9 18:27 ib_logfile0

­rw­rw­­­­ 1 mysql mysql 5M Jan 9 18:28 ib_logfile1

­rw­rw­­­­ 1 mysql mysql 10M Jan 9 18:27 ibdata1

innodemo:

total 112K

­rw­rw­­­­ 1 mysql mysql 61 Jan 9 18:03 db.opt

­rw­rw­­­­ 1 mysql mysql 8.4K Jan 9 18:28 kris.frm

­rw­rw­­­­ 1 mysql mysql 96K Jan 9 18:28 kris.ibd

Jede InnoDB­Installation hat mindestens ein Datenfile ibdata1 und mindestens zweiRedo­Logiles ib_logfile0 und ib_logfile1. Position, Größe, Anzahl und Namen dieserDateien können jedoch relativ frei bestimmt werden.

Unser InnoDB arbeitet mit innodb_file_per_table = 1. Das bedeutet, unsere Datenliegen in einer .ibd­Datei neben der .frm­Datei im Schema­Directory, hier also in$datadir/innodemo/kris.ibd. Die Endung IBD steht Datei für InnoBase­Data. Obwohldie eigentlichen Daten in dieser Datei liegen, braucht InnoDB zwingend ein ibdata­File und mindestens zwei Redo­Logs. Im ibdata­File legt Innobase das DataDictionary ab, also eine Schattenkopie der Tabellendefinitionen aus dem .frm­Dateienin einer Innobase­internen Codierung und das Undo­Log. Im Redo­Log loggt InnoDballe datenverändernden Operationen.

Anders als bei MyISAM ist es NICHT möglich, .ibd­Dateien auf Dateisystemebene zukopieren, verschieben, umzubenennen oder einer anderen Instanz von MySQLeinfach so unterzuschieben. Jede dieser Operationen wird im günstigsten Fall vonInnoDB erkannt und zurückgewiesen und zerstört im schlimmsten Fall die Instanz.

Wenn wir unser InnoDB mit innodb_file_per_table = 0 betreiben, liegen auch alleTabellen in der zentralen ibdata­Datei, und es existieren im Schema­Directory nur.frm­Dateien.

ibdata­Dateien können intern nicht genutzten Platz frei geben und wieder verwenden,aber sie schrumpfen niemals. Wenn man eine Instanz nachträglich voninnodb_file_per_table = 0 auf 1 umstellt, werden existierende InnoDB­Tabellen durchein "ALTER TABLE t ENGINE=InnoDB" in einem externe .ibd­Datei umkopiert undder Platz in der ibdata­Datei freigegeben, aber die Datei wird niemals schrumpfen.Die einzige Methode, von der alten großen ibdata­Datei weg zu kommen ist einDump der Datenbank und ein Neuladen der Daten in eine andere Instanz.

Wieviel Platz in einer ibdata­ oder .ibd­Datei frei ist wird im Comment­Feld derAusgabe von SHOW TABLE STATUS für jede Tabelle angezeigt. Die Angaben sindnatürlich immer Vielfache von 16K, der InnoDB Seitengröße.

Es ist möglich, mehr als eine ibdata­Datei zu haben, aber es ist vollkommenunmöglich zu kontrollieren, welche Tabelle oder welcher Teil einer Tabelle in welcheribdata­Datei liegt. Bei innodb_file_per_table liegt jede Tabelle immer vollständig inihrer eigenen .ibd­Datei.

Das Undo­Log

Jede Tabelle hat in InnoDB zwei versteckte Spalten, eine Transaktionsnummer undeinen Zeiger auf die vorhergehende Version der Zeile im Undo­Log, den Rollback­Pointer.

Wenn wir eine Zeile in einer InnoDB­Tabelle verändern, wird die alte Version derZeile mit der alten Transaktionsnummer aus der Originaltabelle ins Undo­Log kopiertund die neue Version mit einer neuen Transaktionsnummer in die .IBD­Datei kopiert.Der Rollback­Pointer der neuen Version der Zeile zeigt dabei auf die alte Version derZeile im Undo­Log.

CODE:root on mysql.sock [innodemo]> begin;

Query OK, 0 rows affected (0.00 sec)

root on mysql.sock [innodemo]> update kris set d = "one" where id = 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+­­­­­­+ +­­­­+­­­­­­+­­­­­­+

| id | d | txn# | | id | d | txn# |

+­­­­+­­­­­­+­­­­­­+ +­­­­+­­­­­­+­­­­­­+

| 1 | one | 2 | ­­­­­­> | 1 | eins | 1 |

| 2 | zwei | 1 | +­­­­+­­­­­­+­­­­­­+

| 3 | drei | 1 |

+­­­­+­­­­­­+­­­­­­+

3 rows in set (0.00 sec)

In dem Beispiel oben habe ich diese verborgenen Zeilen und den Undo­Log Eintragzur Verdeutlichung manuell von Hand hereingefälscht.

Diese Verzeigerung von Zeilenversionen kann auch über mehr als eine Version

gehen, d.h. ein Eintrag im Undo­Log kann auf eine noch ältere Version einer Zeile

zeigen und so weiter. Löscht man das Undo­Log nie, bekommt man für jede Zeile

jeder Tabelle eine lineare Liste, die die vollständige Abfolge aller Versionen dieser

Zeile darstellt. Irgendwann einmal jedoch wird der Undo­Eintrag, der jetzt ja nicht

mehr gebraucht wird, von einer InnoDB­Aufräumkomponente, dem Purge­Thread,

gelöscht und der Eintrag im Undo­Log gelöscht, sodaß das Undo­Log in der Realität

nicht ins Unendliche wächst.

Das Kopieren von Versionen von Zeilen aus der Tabelle ins Undo­Log und die

Verzeigerung von Zeilenversionen untereinander nennt man MVCC, Multiversion

Concurrency Control.

MVCC ist, so wie InnoDB es implementiert, für das COMMIT optimiert. Wenn man

als Anwender ein COMMIT ausführt, ist nichts zu tun: Die Transaktion wird als

comitted markiert, die Verzeigerung der Einträge bleibt bestehen und alles ist gut.

CODE:

root on mysql.sock [innodemo]> rollback;

Query OK, 0 rows affected (0.01 sec)

+­­­­+­­­­­­+­­­­­­+ +­­­­+­­­­­­+­­­­­­+

| id | d | txn# | | id | d | txn# |

+­­­­+­­­­­­+­­­­­­+ ROLLBACK +­­­­+­­­­­­+­­­­­­+

| 1 | eins | 1 | <­­­­­­­ | | | |

| 2 | zwei | 1 | +­­­­+­­­­­­+­­­­­­+

| 3 | drei | 1 |

+­­­­+­­­­­­+­­­­­­+

3 rows in set (0.00 sec)

Wenn man als Anwender ein ROLLBACK ausführt, fällt dagegen Arbeit an: Die

Daten aus dem Undo­Log müssen rausgefischt und die Änderung in der

Originaltabelle rückgängig gemacht werden.

Das kann bei vielen Zeilen auch mal länger dauern, da das Undo­Log zeilenweise

und nicht seitenweise organisiert ist. Es ist günstig, seine Transaktionen nicht zu

groß zu machen: In den meisten Fällen wird eine Transaktionsgröße von 1.000 bis

10.000 Zeilen für das Massenladen von Daten ein guter Kompromiß sein (Zu klein ist

auch doof, wie wir noch sehen werden).

Transaction Isolation Level Read Uncomitted

Wenn wir die Transaktion von oben einmal von außen betrachten, also von einer

zweiten Verbindung aus, dann kann es sein, daß wir sie beobachten können oder

auch nicht. Auf der ersten Verbindung machen wir:

CODE:

root on mysql.sock [innodemo]> begin;

Query OK, 0 rows affected (0.00 sec)

root on mysql.sock [innodemo]> update kris set d = "one" where id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

und lassen dies nun hängen, ohne die Transaktion mit COMMIT oder ROLLBACK zu

beenden. In einem zweiten Fenster öffnen wir eine zweite Verbindung zur Datenbank

und schauen einmal, was wir sehen:

CODE:

root on mysql.sock [(none)]> use innodemo;

Database changed

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+

| id | d |

+­­­­+­­­­­­+

| 1 | eins |

| 2 | zwei |

| 3 | drei |

+­­­­+­­­­­­+

3 rows in set (0.00 sec)

Wir sehen erst einmal, daß unser Lesezugriff nicht hängt oder wartet, obwohl gerade

eine Transaktion im Gange ist. In MVCC können lesende und schreibende

Operationen einander niemals blockieren ­ das ist ein großer Unterschied zu

MyISAM und einer der Gründe warum InnoDB bei Szenarien mit hoher Parallelität

schneller ist als MyISAM, obwohl es ständig Kopien von den Daten machen muß.

Wir sehen hier den alten Wert der Zeile mit der id = 1. Wir wissen von weiter oben,

das in der ibd­Datei schon die neue Version steht. Also muß die Datenbank hier für

die Zeile id = 1 für unsere Verbindung dem Rollback­Pointer gefolgt sein und für uns

die alte Version der Zeile gefischt haben. Die erste Verbindung dagegen sieht, wie

noch weiter oben schon demonstriert, ihre eigenen Änderungen sofort, auch ohne

COMMIT.

Wir können auch unsere externe, zweite Verbindung diese uncomitteten Daten

sehen lassen. Das geht, indem wir den TRANSACTION ISOLATION LEVEL dieser

Verbindung auf READ UNCOMITTED stellen.

CODE:

root on mysql.sock [innodemo]> set transaction isolation level read uncommitted;

Query OK, 0 rows affected (0.00 sec)

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+

| id | d |

+­­­­+­­­­­­+

| 1 | one |

| 2 | zwei |

| 3 | drei |

+­­­­+­­­­­­+

3 rows in set (0.00 sec)

Wir merken uns: Eine schreibende Verbindung macht immer dasselbe: Sie kopiert

die zu verändernden Daten vor der Änderung ins Undo­Log und verzeigert sie

korrekt. Das muß sie tun, damit sie ein Rollback machen kann.

Eine lesende Verbindung kann sich nun aussuchen, welche Version der Daten sie

sehen will. Jede einzelne lesende Verbindung kann das unabhängig von allen

anderen Verbindungen tun und sich auch umentscheiden, denn es sind ja in jedem

Fall alle Versionen der Daten immer da.

In READ UNCOMITTED sehen wir immer die Daten aus dem ibd­File und folgen

dem Rollback­Pointer nie. Dadurch sehen wir Daten, die noch nicht comitted sind

und es auch vielleicht nie sein werden. Wir sehen eine Version der Realität, die noch

nicht existiert und es vielleicht nie tun wird. Für eine Anwendung ist das in den

meisten Fällen nicht das gewünschte Verhalten.

Transaction Level Read Comitted

Setzen wir dagegen den TRANSACTION ISOLATION LEVEL auf READ COMITTED,

dann bekommen wir für alle Zeilen, die nicht von einer Transaktion in Bearbeitung

sind, die Daten aus dem .ibd­File und für alle Zeilen, in denen noch nicht comittete

Daten stehen folgt die Datenbank dem Rollback­Pointer genau eine Ebene in das

Undo­Log und liefert uns die Daten von dort. Dadurch bekommen wir immer Daten

zu sehen, die "wirklich da sind" und sie hypothetischen Versionen der Wirklichkeit

von READ UNCOMITTED werden für uns ausgefiltert.

Jedoch besteht immer noch die Möglichkeit, daß wir sehen wir sich Daten verändern,

wenn wir sie zweimal lesen. Gegeben sei etwa eine Anwendung, die mit der

Anweisungssequenz "begin; update kris set d = d + 1 where id = 2; commit;" einen

Zähler für die Zeile id = 2 hochzählt. Wenn wir in einer zweiten Verbindung

wiederholt die Tabelle mit "READ COMMITTED" betrachten, bekommen wir

veränderliche Werte von kris.d für kris.id = 2 zurück. Das ist auch dann der Fall,

wenn unsere zweite Verbindung selbst eine Transaktion durchführt ­ die Sequenz

BEGIN­SELECT­SELECT­COMMIT, eine Read­Only­Transaktion, hat in READ

COMMITTED keine besondere Bedeutung.

CODE:

root on mysql.sock [innodemo]> set transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)

root on mysql.sock [innodemo]> begin;

Query OK, 0 rows affected (0.00 sec)

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+

| id | d |

+­­­­+­­­­­­+

| 1 | eins |

| 2 | zwei |

| 3 | drei |

+­­­­+­­­­­­+

3 rows in set (0.01 sec)

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+

| id | d |

+­­­­+­­­­­­+

| 1 | eins |

| 2 | 1 |

| 3 | drei |

+­­­­+­­­­­­+

3 rows in set (0.00 sec)

root on mysql.sock [innodemo]> select * from kris;

+­­­­+­­­­­­+

| id | d |

+­­­­+­­­­­­+

| 1 | eins |

| 2 | 2 |

| 3 | drei |

+­­­­+­­­­­­+

3 rows in set (0.00 sec)

root on mysql.sock [innodemo]> commit;

Query OK, 0 rows affected (0.00 sec)

Transaction Isolation Level Repeatable­Read

Genau dies ändert sich, wenn wir im Transaction Isolation Level REPEATABLE

READ arbeiten: In dem Moment, in dem wir mit BEGIN eine Transaktion starten,

sieht diese Verbindung einen Snapshot der Datenbank, der sich nicht mehr

verändert, bis die Transaktion beendet wird. In REPEATABLE READ hat also auch

eine Read­Only­Transaktion eine Bedeutung.

Intern wird dies so realisiert, daß beim Lesen einer Zeile nicht nur einen Schritt in die

Vergangenheit der Zeile im Undo­Log gegangen wird, sondern den untereinander

verbundenen Zeigern im Undo­Log so lange gefolgt wird bis die neuste Zeile

gefunden wird, die für diesen Leser noch sichtbar ist. Während also die schreibenden

Verbindungen die Daten in der Tabelle immer weiter und weiter ändern, wandern

mehr und mehr alte Versionen dieser Zeile ins Undo­Log, wo sie bis auf weiteres

archiviert werden.

Während bei READ COMITTED also immer nur ein Schritt von der Tabelle ins Undo­Log gemacht wird, kann es bei REPEATABLE READ vorkommen, daß für einenbestimmten Leser viele Schritte im Undo­Log in die Vergangenheit der Zeile gemachtwerden müssen. Dadurch ist die Lebensdauer einzelner Einträge im Undo­Log abernicht mehr einheitlich, sondern es kann sein, daß einmal mehr oder weniger Einträgeim Undo­Log aufbewahrt werden müssen. InnoDB hat einen globalen (für SHOWPROCESSLIST nicht sichtbaren) Purge­Thread, der schaut, was die ältesteTransaktion im System für eine Transaktionsnummer noch brauchen würde. DerPurge­Thread löscht dann alle Einträge im Undo­Log, die noch älter sind als dieseTransaktion.

Das bedeutet aber anders herum auch, daß eine lange laufende Transaktion denPurge­Thread effektiv still legt. Wenn zum Beispiel ein "mysqldump ­­single­transaction" eine große Datenbank exportiert, dann kann es sein, daß diese "singletransaction" viele Minuten oder je nach Datenmenge gar stundenlang stehen bleibtund damit auch der Purge Thread nichts löschen kann. Finden in dieser Zeit vieleSchreibzugriffe statt, wird das Undo­Log unter Umständen beträchtlich anwachsen.Wie weiter oben erklärt, liegt das Undo­Log immer im ibdata­File, auch dann, wenninnodb_file_per_table aktiviert ist. Das bedeutet, daß das ibdata­File auch bei einemServer, der innodb_file_per_table aktiviert hat, größer werden kann ­ 256M bis 1Gsind unter Umständen vollkommen normal.

Wenn ein ibdata­File zu klein ist und nicht wachsen kann, weil in derinnodb_data_file_path ohne "autoextend" definiert ist oder die Platte voll ist, kanndies unter Umständen zu schwer verständlichen Fehlermeldungen ("Table full",obwohl noch mächtig Platz da ist) oder Transaktionsabbrüchen führen.

Transaction Isolation Level SERIALIZEABLE und SELECT ... FOR UPDATE

Während REPEATABLE READ also alle unsere Leseprobleme löst, fehlt uns jetztnoch ein Mechanismus, mit dem wir READ­MODIFY­WRITE Zyklen korrekthandhaben können. Ein Read­Modify­Write­Zyklus ist ein Zugriff, bei dem eineAnwendung Daten liest, in der Anwendung verarbeitet und dann die geändertenDaten zurück schreibt. Damit dies konsistent geschehen kann, muß sichergestelltsein, daß die Daten im Datenbanksystem nicht mehr geändert werden nachdem dieAnwendung sie gelesen hat, ansonsten bekommen wir eine Race­Condition, weilzwei Verbindungen zeitgleich dieselben Daten lesen und sich gegenseitig dieÄnderungen überschreiben.

Wir erreichen dies durch eine Transaktion, in der die Daten mit einem SELECT ...FOR UPDATE­Statement gelesen werden. Dies ist eine Select­Anweisung, dienormal lesend zugreift, aber dabei Locks erzeugt wie ein Update­Statement. Inunserem Fall bedeutet dies, daß exclusive Locks auf allen Zeilen erzeugt werden, dievom Select­Statement über den Index zugegriffen wird. Die Locks bleiben bis zumEnde der Transaktion stehen. Das FOR UPDATE bewirkt also durch die Locks, daßzwei Änderungen an denselben Zeilen nacheinander, also serialisiert, erfolgen.

Dabei gilt es ein paar trickreiche Dinge zu beachten:

Zunächst einmal werden die Locks über den Index erzeugt. Wenn wir also einenEXPLAIN­Plan sehen, indem ein "using where" steht, dann heißt das in der Regel,daß mehr Zeilen über den Index selektiert werden als nachher im Result Set zusehen sind ­ es gibt weitere einschränkende Bedingungen, die den über den Indexgenerierten Result Set weiter verkleinern. Für die Locks bedeutet es aber, daß unterUmständen mehr Zeilen gelockt werden als wir möchten oder im Result Set sehen

können.

Wir können das demonstrieren, indem wir eine Tabelle mit zwei Spalten a und berzeugen. a sei Primary Key und b sei nicht indiziert. Wenn wir jetzt ein SELECT ...FOR UPDATE ausführen, das WHERE a = ... AND b = ... enthält, werden alle in dera­Bedingung gefundenen Zeilen gelockt, auch jene, bei denen die b­Bedingung nichtzutrifft.CODE:­­

­­ Tabelle anlegen

­­

root on mysql.sock [kris]> create table t (

a integer not null,

b integer not null

) engine = innodb;

Query OK, 0 rows affected (0.16 sec)

­­

­­ Daten generieren

­­

root on mysql.sock [kris]> insert into t values ( rand() * 100000, rand() * 10);

Query OK, 1 row affected (0.01 sec)

root on mysql.sock [kris]> insert into t select rand() * 100000, rand() * 10 from t;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

...

root on mysql.sock [kris]> insert into t select rand() * 100000, rand() * 10 from t;

Query OK, 4096 rows affected (0.26 sec)

Records: 4096 Duplicates: 0 Warnings: 0

­­ Da sind noch Duplikate drin, die das

­­ Anlegen eines Primary Key verhindern

root on mysql.sock [kris]> create table dup as select a from t group by a having count(a) > 1 ;

Query OK, 300 rows affected (0.08 sec)

Records: 300 Duplicates: 0 Warnings: 0

root on mysql.sock [kris]> delete from t where a in ( select a from dup );

Query OK, 608 rows affected (5.65 sec)

root on mysql.sock [kris]> alter table t add primary key (a);

Query OK, 7584 rows affected (0.40 sec)

Records: 7584 Duplicates: 0 Warnings: 0

root on mysql.sock [kris]> drop table dup;

Query OK, 0 rows affected (0.00 sec)

­­

­­ 68 Zeilen über a selektiert

­­

root on mysql.sock [kris]> select a, b from t where a > 99000;

...

| 99490 | 8 |

...

62 rows in set (0.00 sec)

­­

­­ Jetzt die Demo: In einer Transaktion

­­ ein SELECT ... FOR UPDATE fahren

­­

root on mysql.sock [kris]> begin;

Query OK, 0 rows affected (0.00 sec)

root on mysql.sock [kris]> select a, b from t where a > 99000 and b = 10;

+­­­­­­­+­­­­+

| a | b |

+­­­­­­­+­­­­+

| 99839 | 10 |

| 99970 | 10 |

+­­­­­­­+­­­­+

2 rows in set (0.00 sec)

In einer anderen Verbindung können wir nun versuchen, etwa das Paar ( 99490, 8 )zu ändern. Wir sehen: Das Statement hängt wegen eines X­Locks auf der Zeile.CODE:root on mysql.sock [kris]> update t set b = 502 where a = 99490;

... hang ...

Wenn wir jedoch einen weiteren INDEX (a,b) definieren und seine Benutzungerzwingen, werden nur die beiden Records (99839, 10) und (99970, 10) gelockt und

unser paralleles Update auf (99490) geht ohne Warten durch:

CODE:

root on mysql.sock [kris]> begin;

Query OK, 0 rows affected (0.00 sec)

root on mysql.sock [kris]> select * from t force index (a) where a > 99000 and b = 10;

+­­­­­­­+­­­­+

| a | b |

+­­­­­­­+­­­­+

| 99839 | 10 |

| 99970 | 10 |

+­­­­­­­+­­­­+

2 rows in set (0.00 sec)

root on mysql.sock [kris]> explain select * from t force index (a) where a > 99000 and b = 10\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t

type: range

possible_keys: a

key: a

key_len: 4

ref: NULL

rows: 63

Extra: Using where; Using index

1 row in set (0.00 sec)

In der anderen Verbindung:

CODE:

root on mysql.sock [kris]> update t set b = 503 where a = 99490;

Query OK, 1 row affected (0.09 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Dieses Lockingverhalten hat also weitreichende Auswirkungen: Wir müssen beim

Schreiben von SQL unbedingt darauf achten, daß die Querypläne von SELECT ...

FOR UPDATE­Statements die korrekten Indizes benutzen. Ein ALL oder INDEX in

der Type­Spalte des EXPLAIN würde hier zum Beispiel einen Index­Scan andeuten ­

da der ganze Index überstrichen wird, bekommen wir so effektiv ein sehr teures, aus

Zeilenlocks zusammengesetztes Table­Lock.

Das andere trickreiche und unerwartete Verhalten ist, daß InnoDB nicht nur Zeilen

lockt, sondern auch die Lücke hinter den Zeilen. Dieses Next­Key Locking

vereinfacht die Implementierung von REPEATABLE­READ. Dieses Verhalten ist

abschaltbar, der Schalter hat den unerwarteten Namen

"innodb_locks_unsafe_for_binlog" ­ stellt man ihn auf "ON", werden von InnoDB

einfach Zeilenlocks erzeugt ohne die Lücke hinter der Zeile auch zu sperren.

Auf den Transaction Isolation Level SERIALIZEABLE verhält sich das System genau

wie auf dem Level REPEATABLE­READ, führt aber jedes einzelne SELECT so aus,

als sei es als SELECT ... FOR UPDATE geschrieben worden. Das führt dazu, daß

jedes SELECT Locks erzeugt als wäre es in UPDATE­Statement, jeder Lesezugriff

lockt also wie ein Schreibzugriff. Dies führt effektiv dazu, daß sich selbst Lesezugriffe

(die ja Schreiblocks erzeugen) gegenseitig in die Quere kommen, wenn sie zugleich

dieselben Daten lesen wollen. Dies ist noch schlechteres Verhalten als in MyISAM!

Der Transaction Isolation Level SERIALIZEABLE ist unnötig: Er wird nie gebraucht,

wenn der SQL­Code in der Anwendung korrekt mit ... FOR UPDATE lockt. Nur

Anwendungen, die dies nicht korrekt tun und bei denen außerdem das SQL nichtkorrigierbar ist, brauchen den Isolation Level SERIALIZEABLE.

Geschrieben von Kristian Köhntopp in MySQL um 10:17 | Kommentare (8) | Trackbacks (4)

Trackbacks

Trackback­URL für diesen Eintrag

links for 2008­01­31Die InnoDB Storage Engine ­ Die wunderbare Welt von Isotopp (tags: Kris about theInnoDB Engine of Mysql)Weblog: c0t0d0s0.orgAufgenommen: Jan 31, 11:18

Die InnoDB Storage Engine: KonfigurationLinks: Strukturen im Speicher, Rechts: Strukturen auf Disk. Oben: Log­Strukturen,Unten: Tablespace­Strukturen. Wie eine Transaktion physikalisch organisiert wirdWenn in InnoDB eine neue Transaktion begonnen und erzeugt wird, ist sie ja nochWeblog: Die wunderbare Welt von IsotoppAufgenommen: Feb 03, 11:54

An InnoDB tutorialMySQL offers a variety of storage engines giving you a lot of flexibility in managingyour storage and data access needs. Still I encounter customers who are not usingthis flexibility when they should, because they lack information about the advantagesoWeblog: MySQL­dumpAufgenommen: Feb 07, 08:48

Transactions ­ An InnoDB TutorialThis is an english translation of the second part of an article in my german languageblog. Transactions ­ An InnoDB tutorial InnoDB does transactions. Meaning: It collectsstatements working on InnoDB tables and applies them on COMMIT to all tablesWeblog: MySQL­dumpAufgenommen: Feb 07, 08:53

KommentareAnsicht der Kommentare: (Linear | Verschachtelt)

"a sei Primary Key und b sei nicht indiziert. Wenn wir jetzt ein SELECT ... FORUPDATE ausführen, das WHERE a = ... AND b = ... enthält, werden alle in der a­Bedingung gefundenen Zeilen gelockt, auch jene, bei denen die b­Bedingung nichtzutrifft."Wenn a ein Primary Key ist, ist er dann nicht auch Unique ­ d.h. gibt es nicht einfachnur genau einen Datensatz, für den "WHERE a = ..." gilt? Im weiteren benutzt Du jaauch nicht "=" sondern ">" im WHERE...#1 Christian Kirsch am 30.01.2008 11:36

Ja, Du hast Recht, es muß auch im ersten Beispiel IN oder sonst eine Range­Bedingung sein.

Andererseits ist es für das, was zu zeigen ist, unwesentlich. Punkt ist lediglich, daßdie Locks auf den Index gelegt werden und zwar beim Überstreichen desselben.Wenn der Query­Plan schlecht ist, ist es das Lockschema auch. Daher ist es vonzentraler Bedeutung beim Entwurf der Anwendung, alle Querypläne von SELECT ...FOR UPDATE noch einmal gesondert zu checken, weil man sonst beimDeployment Krieg hat.#1.1 Kristian Köhntopp (Link) am 30.01.2008 13:05

Ist es generell von Vorteil innodb_file_per_table=1 zu setzen? Wenn ja, was fürein Vorteil? Ist es nicht egal ob die Tabellen in einer zentralen ibdata Datei liegenoder einzeln verteilt sind? Wenn ich sie eh nicht kopieren kann...

Und, da du es erwähnst, was bringt myisam_recover FORCE,BACKUP?

Prima Artikel!#1.1.1 Stephan Wentz am 30.01.2008 19:41

Gibt es eigentlich einen Grund, warum innodb_file_per_table nicht per Default denWert 1 hat?#2 Guido am 30.01.2008 12:02

AUs demselben Grund, warum sql_mode = "" ist und weshalb myisam_recovernicht auf FORCE,BACKUP steht ­ das war früher nicht so und die Defaults zuändern bringt unter Umständen unerwünschte Kompatibilitätsprobleme.#2.1 Kristian Köhntopp (Link) am 30.01.2008 13:06

Dankeschön, wieder einmal ein Top­Beitrag! Ich habe aktuell zwar keine Innodb­Datenbank, habe es aber trotzdem sehr aufmerksam gelesen.#3 Peter am 30.01.2008 14:48

Mich würde interessieren für welche Anwendungsgebiete sich InnoDB besser eignetals MyISAM. Das mit den Transaktionen habe ich verstanden. Wie sieht es mit derPerformance aus? Ein Anwendungsgebiet was mich besonders interessiert sind großeDatenbanken von Onlineforen (PHP­basiert)?#4 Manuel am 30.01.2008 16:03

MyISAM eignet sich eigentlich immer dann, wenn man (fast) ausschließlich Lese­oder (fast) ausschließlich Schreiboperationen hat und man zusätzlich auf ACIDverzichten kann.

InnoDB bietet sich an, wenn man ein ausgeglichen(er)es Verhältnis von Lese­ zuSchreiboperationen hat bzw. wenn man ACID braucht.#4.1 Sebastian Bergmann (Link) am 05.02.2008 12:46

Kommentar schreibenName

E­Mail

Homepage

Antwort zu

Umschließende Sterne heben ein Wort hervor (*wort*), per _wort_ kannein Wort unterstrichen werden.

Kommentar Um maschinelle und automatische Übertragung von Spamkommentaren

zu verhindern, bitte die Zeichenfolge im dargestellten Bild in der

Eingabemaske eintragen. Nur wenn die Zeichenfolge richtig eingegeben

wurde, kann der Kommentar angenommen werden. Bitte beachten Sie,

dass Ihr Browser Cookies unterstützen muss, um dieses Verfahren

anzuwenden.

Hier die Zeichenfolge der Spamschutz­Grafik eintragen:

BBCode­Formatierung erlaubt

Daten merken?