Go to the first, previous, next, last section, table of contents.


8 MySQL-Tabellentypen

AB MySQL-Version 3.23.6 können Sie unter drei grundlegenden Tabellenformaten (ISAM, HEAP und MyISAM wählen. Neuere MySQL-Versionen können zusätzliche Tabellentypen unterstützen (InnoDB, oder BDB), abhängig davon, wie Sie sie kompilieren.

Beim Erzeugen einer neuen Tabelle können Sie MySQL mitteilen, welcher Tabellentyp dafür benutzt werden soll. MySQL erzeugt immer eine .frm-Datei, die die Tabellen- und Spaltendefinitionen enthält. Abhängig vom Tabellentyp werden Index und Daten in anderen Dateien gespeichert.

Beachten Sie, dass Sie für die Benutzung von InnoDB-Tabellen zumindest die innodb_data_file_path-Startoption benötigen. See section 8.5.2 Mit InnoDB anfangen - Optionen.

Der vorgabemäßige Tabellentyp in MySQL ist MyISAM. Wenn Sie versuchen, einen Tabellentyp zu benutzen, der nicht einkompiliert oder aktiviert ist, erzeugt MySQL statt dessen eine Tabelle vom Typ MyISAM. Das ist ein sehr nützliches Feature, wenn Sie Tabellen zwischen unterschiedlichen SQL-Servern kopieren wollen, die unterschiedliche Tabellentypen unterstützten (zum Beispiel Tabellen zu einem Slave kopieren, der für Geschwindigkeit optimiert ist, aber keine transaktionalen Tabellen hat). Dieses automatische Ändern des Tabellentyps kann andererseits für neue MySQL-Benutzer sehr verwirrend sein. Wir planen für MySQL 4.0, das zu beheben, indem eine Warnung ausgegeben wird, wenn ein Tabellentyp automatisch geändert wird.

Sie können Tabellen zwischen unterschiedlichen Typen mit dem ALTER TABLE-Statement umwandeln. See section 7.5.4 ALTER TABLE-Syntax.

MySQL unterstützt zwei unterschiedliche Arten von Tabellen: transaktionssichere Tabellen (InnoDB und BDB) und nicht transaktionssichere Tabellen (HEAP, ISAM, MERGE und MyISAM).

Vorteile transaktionssicherer Tabellen (TST):

Vorteile nicht transaktionssicherer Tabellen (NTST):

Sie können TST- and NTST-Tabellen in denselben Statements kombinieren, um das Beste aus beiden Welten zu bekommen.

8.1 MyISAM-Tabellen

MyISAM ist der vorgabemäßige Tabellentyp in MySQL-Version 3.23. Er basiert auf dem ISAM-Code und hat viele nützliche Erweiterungen.

Der Index wird in einer Datei mit der Endung .MYI (MYIndex) gespeichert, die Daten in einer Datei mit der Endung .MYD (MYData). Sie können MyISAM-Tabellen mit dem myisamchk-Dienstprogramm überprüfen und reparieren. See section 5.4.6.9 Wie Tabellen repariert werden. Sie können MyISAM-Tabellen mit myisampack komprimieren, damit sie viel weniger Speicherplatz benötigen. See section 5.7.4 myisampack, MySQL-Programm zum Erzeugen komprimierter Nur-Lese-Tabellen.

Folgende Neuerungen gibt es bei MyISAM:

MyISAM unterstützt ausserdem die folgenden Dinge, die MySQL in naher Zukunft benutzen können wird:

Beachten Sie, dass Index-Dateien bei MyISAM üblicherweise viel kleiner sind als bei ISAM. Das bedeutet, dass MyISAM normalerweise weniger Systemressourcen verbraucht als ISAM, allerdings mehr Prozessorleistung beim Einfügen von Daten in einen komprimierten Index.

Folgende Optionen für mysqld können benutzt werden, um das Verhalten von MyISAM-Tabellen zu ändern. See section 5.5.5.4 SHOW VARIABLES.

Option Beschreibung
--myisam-recover=# Automatische Wiederherstellung beschädigter Tabellen.
-O myisam_sort_buffer_size=# Der beim Wiederherstellen von Tabellen benutzte Puffer.
--delay-key-write-for-all-tables Keine Schlüsselpuffer zwischen Schreibvorgängen auf jedwede MyISAM-Tabelle zurückschreiben (flush).
-O myisam_max_extra_sort_file_size=# Wird benutzt, um MySQL bei der Entscheidung zu helfen, wann die langsame, aber sichere Schlüssel-Cache-Index-Erzeugungsmethode benutzt werden sollte. Hinweis: Dieser Parameter wird in Megabytes angegeben!
-O myisam_max_sort_file_size=# Die schnelle Index-Sortiermethode beim Erzeugen eines Indexes nicht benutzen, wenn die temporäre Datei größer als dieser Wert werden würde. Hinweis: Dieser Parameter wird in Megabytes angegeben! megabytes!--
-O myisam_bulk_insert_tree_size=# Die Größe des Baum-Caches, der bei der Optimierung von Massen-Einfügeoperationen benutzt wird. Hinweis: Das ist die Begrenzung pro Thread!

Die automatische Wiederherstellung wird aktiviert, wenn Sie mysqld mit --myisam-recover=# starten. See section 5.1.1 mysqld-Kommandozeilenoptionen. Beim Öffnen wird geprüft, ob die Tabelle als beschädigt gekennzeichnet ist oder ob die Zählvariable für die Tabelle nicht 0 ist und Sie mit --skip-locking laufen lassen. Wenn eine dieser Bedingungen erfüllt ist, geschieht folgendes:

Wenn die Wiederherstellung nicht in der Lage ist, alle Zeilen aus einem vorher abgeschlossenen Statement wiederherzustellen, und Sie nicht FORCE als Option für myisam-recover angegeben haben, wird die automatische Reparatur mit einer Fehlermeldung in der Fehlerdatei abgebrochen:

Error: Couldn't repair table: test.g00pages

Hätten Sie in diesem Fall die FORCE-Option benutzt, würden Sie statt dessen in der Fehlerdatei eine Warnung erhalten:

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Wenn Sie automatisches Wiederherstellung mit der BACKUP-Option laufen lassen, beachten Sie, dass Sie ein Cron-Skript haben sollten, dass automatisch Dateien mit Namen wie `tabellenname-datetime.BAK' aus den Datenbank-Verzeichnissen auf ein Sicherungsmedium verschiebt.

See section 5.1.1 mysqld-Kommandozeilenoptionen.

8.1.1 Für Schlüssel benötigter Speicherplatz

MySQL unterstützt unterschiedliche Index-Typen, doch der normale Typ ist ISAM oder MyISAM. Diese benutzen einen B-Baum-Index, und Sie können die Größe der Index-Datei grob als (schluessel_laenge+4)/0.67 kalkuliert, summiert über alle Schlüssel. (Das ist der schlechteste Fall, bei dem alle Schlüssel in sortierter Reihenfolge eingeordnet werden und es keinerlei Schlüssel-Komprimierung gibt.)

Zeichenketten-Indexe werden Leerzeichen-komprimiert. Wenn der erste Index-Teil eine Zeichenkette ist, wird er zusätzlich Präfix-komprimiert. Leerzeichen-Kompression macht die Index-Datei kleiner als in den obigen Zahlen dargestellt, wenn die Zeichenkettenspalte viele Leerzeichen am Ende hat oder eine VARCHAR-Spalte ist, die nicht immer in voller Länge genutzt wird. Präfix-Kompression wird bei Schlüsseln benutzt, die mit einer Zeichenkette beginnen. Präfix-Kompression hilft, wenn es viele Zeichenketten mit identischem Präfix gibt.

Bei MyISAM-Tabellen können Sie auch Zahlen Präfix-komprimieren, indem Sie beim Erzeugen der Tabelle PACK_KEYS=1 angeben. Das hilft, wenn Sie viele Ganzzahl-Schlüssel mit identischem Präfix haben, wenn die Zahlen mit dem hohen Byte zuerst gespeichert werden.

8.1.2 MyISAM-Tabellenformate

MyISAM unterstützt 3 verschiedene Tabellentypen. Zwei von ihnen werden automatisch gewählt, abhängig vom Spaltentyp, den Sie benutzen. Der dritte, komprimierte Tabellen, kann nur mit dem myisampack-Dienstprogramm erzeugt werden.

Wenn Sie eine Tabelle erzeugen (CREATE) oder ändern (ALTER), können Sie bei Tabellen, die kein BLOB enthalten, ein dynamisches (DYNAMIC) oder festes (FIXED) Tabellenformat mit der ROW_FORMAT=#-Tabellenoption erzwingen. Zukünftig werden Sie in der Lage sein, Tabellen zu komprimieren / dekomprimieren, indem Sie ROW_FORMAT=compressed | default für ALTER TABLE angeben. See section 7.5.3 CREATE TABLE-Syntax.

8.1.2.1 Kennzeichen statischer (Festlängen-) Tabellen

Das ist das vorgabemäßige Format. Es wird benutzt, wenn die Tabelle keine VARCHAR-, BLOB- oder TEXT-Spalten enthält.

Dieses Format ist das einfachste und sicherste Format. Es ist auch das schnellste der Formate auf Platte. Die Geschwindigkeit ergibt sich aus der einfachen Weise, wie Daten auf der Platte gefunden werden können. Wenn man etwas mit einem Index und statischem Format nachschlägt, ist es sehr einfach. Man multipliziert einfach die Zeilennummer mit der Zeilenlänge.

Wenn eine Tabelle gescannt wird, ist es ausserdem sehr einfach, mit jedem Plattenzugriff eine konstante Anzahl von Datensätzen zu lesen.

Die Sicherheit zeigt sich, wenn Ihr Computer beim Schreiben in eine MyISAM-Datei fester Länge abstürzt. In diesem Fall kann myisamchk leicht herausfinden, wo jede Zeile anfängt und aufhört. Daher kann es üblicherweise alle Datensätze mit Ausnahme desjenigen, in den nur teilweise geschrieben wurde, wieder herstellen. Beachten Sie, dass in MySQL alle Indexe in jedem Fall wiederhergestellt werden können:

8.1.2.2 Kennzeichen dynamischer Tabellen

Dieses Format wird benutzt, wenn die Tabelle irgend welche VARCHAR-, BLOB- oder TEXT-Spalten enthält, oder wenn die Tabelle mit ROW_FORMAT=dynamic erzeugt wurde.

Dieses Format ist etwas komplexer, weil jede Zeile einen Header haben muss, der aussagt, wie lang sie ist. Ein Datensatz kann ausserdem an mehr als einem Speicherplatz enden, wenn er bei einer Aktualisierung verlängert wird.

Sie können OPTIMIZE table oder myisamchk benutzen, um eine Tabelle zu defragmentieren. Wenn Sie statische Daten haben, auf die Sie oft zugreifen oder die Sie in derselben Tabelle oft ändern, als VARCHAR- oder BLOB-Spalten haben, ist es eine gute Idee, die dynamischen Spalten in andere Tabellen zu verschieben, einfach um Fragmentierung zu vermeiden:

8.1.2.3 Kennzeichen komprimierter Tabellen

Das ist ein Nur-Lese-Typ, der mit dem optionalen myisampack-Dienstprogramm (pack_isam für ISAM-Tabellen) erzeugt wird:

8.1.3 MyISAM-Tabellenprobleme

Das Dateiformat, das MySQL benutzt, um Daten zu speichern, wurde ausgiebig getestet, aber es gibt immer Umstände, die dazu führen können, dass Datenbanktabellen beschädigt werden.

8.1.3.1 Beschädigte MyISAM-Tabellen

Obwohl das MyISAM-Tabellenformat sehr zuverlässig ist (alle Änderungen an einer Tabelle werden geschrieben, bevor das SQL-Statement zurückkehrt), können Sie dennoch beschädigte Tabellen bekommen, wenn eines der folgenden Dinge passiert:

Typische Symptome einer beschädigten Tabelle sind:

Sie können mit dem Befehl CHECK TABLE prüfen, ob eine Tabelle in Ordnung ist. See section 5.4.4 CHECK TABLE-Syntax.

Sie können eine beschädigte Tabelle mit REPAIR TABLE reparieren. See section 5.4.5 REPAIR TABLE-Syntax. Wenn mysqld nicht läuft, können Sie eine Tabelle auch mit dem myisamchk-Befehl reparieren. myisamchk-Syntax.

Wenn Ihre Tabellen oft beschädigt werden, sollten Sie versuchen, den Grund dafür herauszufinden! See section A.4.1 Was zu tun ist, wenn MySQL andauernd abstürzt.

In diesem Fall ist es am wichtigsten zu wissen, ob die Tabelle durch einen Absturz von mysqld beschädigt wurde (das können Sie leicht feststellen, wenn es eine aktuelle Zeile restarted mysqld in der mysqld-Fehlerdatei gibt). Wenn das nicht der Fall ist, sollten Sie versuchen, daraus einen Testfall zu machen. See section E.1.6 Einen Testfall herstellen, wenn Sie Tabellenbeschädigung feststellen.

8.1.3.2 Client benutzt Tabelle oder hat sie nicht korrekt geschlossen

Jede MyISAM-.MYI-Datei hat im Header einen Zähler, der benutzt werden kann, um zu prüfen, ob die Tabelle korrekt geschlossen wurde.

Wenn Sie folgende Warnmeldung von CHECK TABLE oder myisamchk erhalten:

# client is using or hasn't closed the table properly

heißt das, dass der Zähler nicht mehr synchron ist. Das bedeutet nicht, dass die Tabelle beschädigt ist, aber Sie sollten zumindest eine Überprüfung vornehmen, um sicherzustellen, dass die Tabelle in Ordnung ist.

Der Zähler funktioniert wie folgt:

Mit anderen Worten kann der Zähler nur in folgenden Fällen nicht mehr synchron sein:

8.2 MERGE-Tabellen

MERGE-Tabellen sind neu seit MySQL-Version 3.23.25. Der Code ist noch Gamma, sollte aber ausreichend stabil sein.

Eine MERGE-Tabelle (auch bekannt als MRG_MyISAM-Tabelle) ist eine Sammlung identischer MyISAM-Tabellen, die wie eine benutzt werden können. Sie können auf dieser Sammlung von Tabellen nur SELECT, DELETE und UPDATE ausführen. Wenn Sie eine MERGE-Tabelle löschen (DROP), löschen Sie nur die MERGE-Spezifikation.

Beachten Sie, dass DELETE FROM merge_tabelle ohne WHERE nur das Mapping für die Tabelle löscht, nicht alles in den gemappten Tabellen. (Geplant ist, das in Version 4.1 zu beheben.)

Mit identischen Tabellen ist gemeint, dass alle Tabellen mit identischen Spalten- und Schlüsselinformationen erzeugt wurden. Sie können kein MERGE auf Tabellen ausführen, deren Spalten unterschiedlich komprimiert sind, nicht genau dieselben Spalten oder die Schlüssel in unterschiedlicher Reihenfolge haben. Einige der Tabellen können jedoch mit myisampack komprimiert sein. See section 5.7.4 myisampack, MySQL-Programm zum Erzeugen komprimierter Nur-Lese-Tabellen.

Wenn Sie eine MERGE-Tabelle erzeugen, erhalten Sie eine .frm-Tabellendefinitionsdatei und eine .MRG-Tabellenlistendatei. Die .MRG enthält lediglich eine Liste der Index-Dateien (.MYI-Dateien), die wie eine benutzt werden sollen. Alle benutzten Tabellen müssen in derselben Datenbank wie die MERGE-Tabelle selbst sein.

Momentan benötigen Sie SELECT-, UPDATE- und DELETE-Berechtigungen für die Tabellen, die Sie auf eine MERGE-Tabelle mappen.

MERGE-Tabellen können bei der Lösung folgender Probleme helfen:

Die Nachteile von MERGE-Tabellen sind:

Wenn Sie eine MERGE-Tabelle erzeugen, müssen Sie mit UNION(liste-von-tabellen) angeben, welche Tabellen Sie wie eine benutzen wollen. Optional können Sie mit INSERT_METHOD angeben, ob Sie wollen, dass Einfügungen in die MERGE-Tabelle in der ersten oder der letzten Tabelle in der UNION-Liste geschehen sollen. Wenn Sie keine INSERT_METHOD oder NO angeben, geben alle INSERT-Befehle auf die MERGE-Tabelle einen Fehler zurück.

Folgendes Beispiel zeigt, wie Sie MERGE-Tabellen benutzen:

CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, nachricht CHAR(20));
CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, nachricht CHAR(20));
INSERT INTO t1 (nachricht) VALUES ("test"),("tabelle"),("t1");
INSERT INTO t2 (nachricht) VALUES ("test"),("tabelle"),("t2");
CREATE TABLE gesamt (a INT NOT NULL, nachricht CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Beachten Sie, dass wir keinen UNIQUE- oder PRIMARY KEY-Schlüssel in der gesamt-Tabelle angegeben haben, weil der Schlüssel in der gesamt-Tabelle nicht eindeutig sein wird.

Beachten Sie auch, dass Sie die .MRG-Datei direkt von ausserhalb des MySQL-Servers manipulieren können:

shell> cd /mysql-data-verzeichnis/aktuelle-datenbank
shell> ls -1 t1.MYI t2.MYI > gesamt.MRG
shell> mysqladmin flush-tables

Jetzt können Sie Dinge wie folgendes tun:

mysql> select * from gesamt;
+---+-----------+
| a | nachricht |
+---+-----------+
| 1 | test      |
| 2 | table     |
| 3 | t1        |
| 1 | test      |
| 2 | table     |
| 3 | t2        |
+---+-----------+

Um eine MERGE-Tabelle neu zu mappen, können Sie folgendes tun:

8.2.1 MERGE-Tabellenprobleme.

Folgende Probleme sind bei MERGE-Tabellen bekannt:

8.3 ISAM-Tabellen

Sie können auch den veralteten ISAM-Tabellentyp benutzen. Dieser wird recht bald verschwinden (wahrscheinlich in MySQL 4.1), weil MyISAM eine bessere Implementation derselbe Sache ist. ISAM benutzt einen B-tree-Index. Der Index wird in einer Datei mit der Endung .ISM gespeichert, und die Daten in einer Datei mit der Endung .ISD. Sie können ISAM-Tabellen mit dem isamchk-Dienstprogramm prüfen / reparieren. See section 5.4 Katastrophenschutz und Wiederherstellung.

ISAM hat folgende Features / Eigenschaften:

Die meisten Dinge, die für MyISAM-Tabellen gelten, gelten auch für ISAM-Tabellen. See section 8.1 MyISAM-Tabellen. Die größten Unterschiede im Vergleich zu MyISAM sind:

Wenn Sie eine ISAM-Tabelle in eine MyISAM-Tabelle umwandeln wollen, können Sie Dienstprogramme wie mysqlcheck oder ein ALTER TABLE-Statement benutzen:

mysql> ALTER TABLE tabelle TYPE = MYISAM;

Die eingebetteten (embedded) MySQL-Versionen unterstützen keine ISAM-Tabellen.

8.4 HEAP-Tabellen

HEAP-Tabellen benutzen eine gehashten Index und werden im Arbeitsspeicher gespeichert. Das macht sie sehr schnell, aber wenn MySQL abstürzt, verlieren Sie alle darin gespeicherten Daten. HEAP ist sehr nützlich für temporäre Tabellen.

Die MySQL-internen HEAP-Tabellen benutzen 100% dynamisches Hashen ohne Overflow-Bereiche. Es wird kein zusätzlicher Platz für freie Listen benötigt. HEAP-Tabellen haben auch keine Probleme mit Löschen plus Einfügen, was normalerweise bei gehashten Tabellen häufig vorkommt:

mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) as down
        FROM log_tabelle GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

Einige Dinge sollten Sie bei der Benutzung von HEAP-Tabellen in Betracht ziehen:

Der für eine Zeile in einer HEAP-Tabelle benötigte Speicher ist:

SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
+ ALIGN(length_of_row+1, sizeof(char*))

sizeof(char*) ist 4 auf 32-Bit-Maschinen und 8 auf 64-Bit-Maschinen.

8.5 InnoDB-Tabellen

8.5.1 Überblick über InnoDB-Tabellen

InnoDB stellt MySQL einen transaktionssicheren (ACID-kompatiblen) Tabellen-Handler mit Fähigkeiten für Commit, Rollback und Reparatur nach Absturz zur Verfügung. InnoDB beherrscht Sperren auf Zeilenebene sowie ein konsistentes, nicht sperrendes Lesen in der Art von Oracle bei SELECTs. Diese Features steigern die Handhabung gleichzeitiger Verbindungen und die Performance. Es gibt bei InnoDB keine Notwendigkeit für Sperr-Eskalation, weil die Sperren auf Zeilenebene bei InnoDB in sehr wenig Speicherplatz passen. InnoDB-Tabellen unterstützen als erster Tabellentyp in MySQL FOREIGN KEY-Beschränkungen.

InnoDB wurde für maximale Performance bei der Bearbeitung großer Datenmengen entworfen. Seine Prozessor-Effizienz wird wahrscheinlich von keiner anderen Festplatten-basierenden relationalen Datenbank-Engine erreicht.

Technisch gesehen ist InnoDB ein komplettes Datenbank-Backend, das unter MySQL platziert ist. InnoDB hat seinen eigenen Puffer-Pool, um Daten und Indexe im Hauptspeicher zu cachen. InnoDB speichert seine Tabellen und Indexe in einem Tabellenplatz (Tablespace), der aus mehreren Dateien bestehen kann. Das unterscheidet sich beispielsweise von MyISAM-Tabellen, bei denen jede Tabelle als separate Datei gespeichert ist. InnoDB-Tabellen können jede beliebige Größe annehmen, sogar auf Betriebssystemen, deren Dateigröße auf 2 GB beschränkt ist.

Die neuesten Informationen über InnoDB finden Sie unter http://www.innodb.com/. Die aktuellste Version des InnoDB-Handbuchs ist immer dort zu finden, und Sie können auch kommerzielle Lizenzen und kommerziellen Support für InnoDB bestellen.

InnoDB wird momentan (Oktober 2001) für die Produktion auf mehreren großen Datenbank-Sites benutzt, die hohe Performance benötigen. Die bekannte Internet-Newssite Slashdot.org läuft auf InnoDB. Mytrix Inc. speichert über 1 TB an Daten in InnoDB, und eine andere Site handhabt eine durchschnittliche Last von 800 Einfüge- und Update-Operationen pro Sekunde mit InnoDB.

InnoDB-Tabellen sind in der MySQL-Quelldistribution ab Version 3.23.34a enthalten und in der MySQL-Max-Binärversion aktiviert. Für Windows sind die Max-Binärdateien in der Standarddistribution enthalten.

Wenn Sie eine Binärversion von MySQL herunter geladen haben, die Unterstützung für InnoDB enthält, folgen Sie einfach den Anweisungen im Handbuch für die Installation einer Binärversion von MySQL. Wenn Sie bereits MySQL-3.23 installiert haben, können Sie MySQL-Max am einfachsten installieren, indem Sie die ausführbare Datei für den Server (`mysqld') durch die entsprechende ausführbare Datei in der Max-Distribution ersetzen. MySQL and MySQL-Max unterscheiden sich nur in Bezug auf die ausführbare Datei für den Server. See section 3.2.6 MySQL-Binärdistributionen, die von MySQL AB kompiliert wurden. See section 5.7.5 mysqld-max, ein erweiterter mysqld-Server.

Um MySQL mit InnoDB-Unterstützung zu kompilieren, laden Sie MySQL-3.23.34a oder neuer von http://www.mysql.com/ herunter und konfigurieren Sie MySQL mit der --with-innodb-Option. Sehen Sie im Handbuch unter section 3.3 Installation der Quelldistribution nach.

cd /pfad/zur/quelldistribution/von/mysql-3.23.37
./configure --with-innodb

Um InnoDB zu benutzen, müssen Sie InnoDB init in Ihrer `my.cnf'- oder `my.ini'-Datei angeben. In dieser Datei müssen Sie mindestens folgenden Zeile im [mysqld]-Abschnitt hinzufügen:

innodb_data_file_path=ibdata:30M

Für eine gute Performance ist es jedoch am besten, Optionen wie die unten im Abschnitt section 8.5.2 Mit InnoDB anfangen - Optionen empfohlenen anzugeben.

InnoDB wird unter der GNU-GPL-Lizenz Version 2 (vom Juni 1991) vertrieben. In den Quelldistributionen von MySQL erscheint InnoDB als Unterverzeichnis.

8.5.2 Mit InnoDB anfangen - Optionen

Um InnoDB-Tabellen in MySQL-Max-3.23 zu benutzen, MÜSSEN Sie Konfigurationsparameter im [mysqld]-Abschnitt der MySQL-Konfigurationsdatei `my.cnf' angeben. See section 5.1.2 my.cnf-Optionsdateien.

Der einzige erforderliche Parameter, um InnoDB in MySQL-Max-3.23 benutzen zu können, ist innodb_data_file_path. In MySQL-4.0 müssen Sie nicht einmal innodb_data_file_path angeben. Vorgabemäßig wird eine 64 MB große Daten-Datei `ibdata1' im datadir von MySQL erzeugt.

Um jedoch eine gute Performance zu erzielen, MÜSSEN Sie explizit die unten in Beispielen aufgeführten InnoDB-Parameter setzen.

Der Vorgabewert für innodb_data_home_dir ist das datadir von MySQL. Wenn Sie innodb_data_home_dir nicht angeben, können Sie in innodb_data_file_path keine absoluten Pfade benutzen.

Nehmen wir an, Sie haben eine Windows-NT-Maschine mit 128 MB RAM und einer einzelnen 10 GB großen Festplatte. Unten steht ein Beispiel von möglichen Konfigurationsparametern in `my.cnf' für InnoDB:

[mysqld]
# Hier können Ihre sonstigen MySQL-Serveroptionen stehen
# ...
#
innodb_data_home_dir = c:\ibdata
# Die Daten-Dateien müssen in der Lage sein,
# Ihre Daten und Indexe aufzunehmen
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M
# Puffer-Poolgröße auf 50% bis 80%
# des Arbeitsspeichers Ihres Computers setzen
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
innodb_log_group_home_dir = c:\iblogs
# .._log_arch_dir muss dasselbe sein wie
# .._log_group_home_dir
innodb_log_arch_dir = c:\iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
# Die Log-Dateigröße auf ungefähr 15%
# der Puffer-Poolgröße setzen
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_buffer_size=8M
# ..flush_log_at_trx_commit auf 0 setzen,
# wenn Sie es sich leisten können,
# ein paar der letzten Transaktionen zu verlieren
innodb_flush_log_at_trx_commit=1
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

Beachten Sie, dass die Daten-Dateien bei einigen Betriebssystemen kleiner als 2 GB sein müssen! Die Gesamtgröße von Daten-Dateien muss größer oder gleich 10 MB sein. Die Gesamtgröße der Log-Dateien MUSS auf 32-Bit-Computern kleiner als 4 GB sein.

InnoDB legt keine Verzeichnisse an. Diese müssen Sie selbst erzeugen! Stellen Sie auch sicher, dass der MySQL-Server Rechte hat, Dateien in den Verzeichnissen anzulegen, die Sie angeben. Wenn Sie zum ersten Mal eine InnoDB-Datenbank erzeugen, sollten Sie den MySQL-Server am besten von der Kommandozeilen-Eingabeaufforderung starten. InnoDB gibt dann Informationen über die Datenbank-Erzeugung auf dem Bildschirm aus und Sie sehen, was passiert. Unten in Abschnitt 3 sehen Sie, wie die Ausgaben aussehen sollten. Unter Windows können Sie `mysqld-max.exe' so starten:

ihr-pfad-zu-mysqld>mysqld-max --standalone --console

Nehmen wir an, Sie haben einen Linux-Computer mit 512 MB RAM und drei Festplatten mit jeweils 20 GB (in Verzeichnispfaden `/', `/dr2' and `/dr3'). Unten ist ein Beispiel möglicher Konfigurationsparameter in `my.cnf' für InnoDB:

[mysqld]
# Hier können Ihre sonstigen MySQL-Serveroptionen stehen
# ...
#
innodb_data_home_dir = /
# Die Daten-Dateien müssen in der Lage sein,
# Ihre Daten und Indexe aufzunehmen
innodb_data_file_path = ibdata/ibdata1:2000M;dr2/ibdata/ibdata2:2000M
# Puffer-Poolgröße auf 50% bis 80%
# des Arbeitsspeichers Ihres Computers setzen
set-variable = innodb_buffer_pool_size=350M
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
# .._log_arch_dir muss dasselbe sein wie
# .._log_group_home_dir
innodb_log_arch_dir = /dr3/iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
# Die Log-Dateigröße auf ungefähr 15%
# der Puffer-Poolgröße setzen
set-variable = innodb_log_file_size=50M
set-variable = innodb_log_buffer_size=8M
# ..flush_log_at_trx_commit auf 0 setzen,
# wenn Sie es sich leisten können,
# ein paar der letzten Transaktionen zu verlieren
innodb_flush_log_at_trx_commit=1
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
#innodb_flush_method=fdatasync
#innodb_fast_shutdown=1
#set-variable = innodb_thread_concurrency=5

Beachten Sie, dass die beiden Daten-Dateien auf unterschiedliche Platten platziert wurden. Der Grund für den Namen innodb_data_file_path ist, dass Sie auch Pfade zu Ihren Daten-Dateien angeben können und dass innodb_data_home_dir nur textlich mit Ihren Daten-Datei-Pfaden verkettet wird, wobei ein möglicher Schrägstrich oder Backslash dazwischen hinzugefügt wird. InnoDB füllt den Tabellenplatz (Tablespace), der durch die Daten-Dateien gebildet wird, von unten nach oben. In manchen Fällen verbessert es die Performance der Datenbank, wenn nicht alle Daten auf derselben physikalischen Festplatte platziert sind. Es verbessert häufig die Performance, Log-Dateien auf anderen Platten als die Daten zu platzieren.

Die Bedeutung der Konfigurationsparameter ist wie folgt:

Option Beschreibung
innodb_data_home_dir Der allgemeine Teil des Verzeichnispfads für alle InnoDB-Daten-Dateien. Die Vorgabe für diesen Parameter ist das datadir von MySQL.
innodb_data_file_path Pfade zu individuellen Daten-Dateien und ihre Größen. Der volle Verzeichnispfad zu jeder Daten-Datei wird durch Verkettung von innodb_data_home_dir mit den hier angegebenen Pfaden hergestellt. Die Dateigrößen werden in Megabytes angegeben, daher das 'M' nach der obigen Angabe. InnoDB versteht auch die Abkürzung 'G', 1G bedeutet 1024M. Ab 3.23.44 können Sie die Dateigröße auf mehr als 4 GB setzen, wenn das Betriebssystem große Dateien unterstützt. Auf einige Betriebssystemen müssen Dateien kleiner als 2 GB sein. Die Summe der Dateigrößen muss mindestens 10 MB betragen.
innodb_mirrored_log_groups Anzahl identischer Kopien von Log-Gruppen, die für die Datenbank gehalten werden. Momentan sollte dieser Parameter auf 1 gesetzt werden.
innodb_log_group_home_dir Verzeichnispfad zu den InnoDB-Log-Dateien.
innodb_log_files_in_group Anzahl von Log-Dateien in der Log-Gruppe. InnoDB schreibt in zirkulärer Weise in die Dateien. Hier wird ein Wert 3 empfohlen.
innodb_log_file_size Größe jeder Log-Datei in einer Log-Gruppe in Megabytes. Sinnvolle Werte reichen von 1 MB bis 1/n-tel der Größe des Puffer-Pools, die unten angegeben wird, wobei n die Anzahl der Log-Dateien in der Gruppe ist. Je größer der Wert, desto weniger Checkpoint-Flush-Aktivität wird im Puffer benötigt, was Festplatten-Ein- und -Ausgaben erspart. Größere Log-Dateien bedeutet jedoch auch, dass die Wiederherstellung im Fall eines Absturzes langsamer ist. Die Gesamtgröße aller Log-Dateien muss auf 32-Bit-Computern kleiner als 4 GB sein.
innodb_log_buffer_size Die Größe des Puffers, den InnoDB benutzt, um in die Log-Dateien auf Platte zu schreiben. Sinnvolle Werte liegen im Bereich von 1 MB bis zur Hälfte der Gesamtgröße der Log-Dateien. Ein großer Log-Puffer erlaubt, dass große Transaktionen laufen können, ohne dass die Notwendigkeit besteht, das Log auf Platte zu schreiben, bis die Transaktion abgeschickt (commit) wird. Wenn Sie daher große Transaktionen haben, sparen Sie Festplatten-Ein- und Ausgaben, wenn Sie den Log-Puffer Groß machen.
innodb_flush_log_at_trx_commit Normalerweise wird dieser Parameter auf 1 gesetzt, was bedeutet, dass beim Abschicken (commit) einer Transaktion das Log auf Platte geschrieben wird (flush) und die durch die Transaktion gemachten Änderungen permanent werden und einen Datenbankabsturz überleben. Wenn Sie willens sind, in Bezug auf diese Sicherheit Kompromisse einzugeben und eher kleine Transaktionen laufen lassen, können Sie diesen Wert auf 0 setzen, um Festplatten-Ein- und -Ausgaben in Bezug auf die Log-Dateien zu verringern.
innodb_log_arch_dir Das Verzeichnis, in dem komplett geschriebene Log-Dateien archiviert werden, wenn Archivierung benutzt wird. Der Wert dieses Parameters sollte momentan derselbe sein wie innodb_log_group_home_dir.
innodb_log_archive Dieser Wert sollte momentan auf 0 gesetzt werden. Weil MySQL die Wiederherstellung aus einer Datensicherung unter Benutzung seiner eigenen Log-Dateien durchführt, gibt es momentan keine Notwendigkeit, InnoDB-Log-Dateien zu archivieren.
innodb_buffer_pool_size Die Größe des Speicherpuffers, den InnoDB benutzt, um Daten und Indexe seiner Tabellen zu cachen. Je größer Sie diesen Wert setzen, desto weniger Festplatten-Ein- und -Ausgaben werden für den Zugriff auf Daten in Tabellen benötigt. Auf einem dedizierten Datenbank-Server können Sie diesen Parameter auf bis zu 80% des physikalischen Arbeitsspeichers der Maschine setzen. Setzen Sie ihn allerdings nicht zu hoch, weil bei manchen Betriebssystemen der Wettbewerb um Arbeitsspeicher zu Paging führt.
innodb_additional_mem_pool_size Die Größe des Speicher-Pools, den InnoDB für die Speicherung von Daten-Wörterbuchinformationen und anderen internen Datenstrukturen benutzt. Ein sinnvoller Wert hierfür könnte 2 MB sein. Je mehr Tabellen Sie jedoch in Ihrer Applikation haben, desto mehr müssen Sie hier zuweisen. Wenn InnoDB in diesem Pool keinen Speicherplatz mehr hat, läßt es sich Speicherplatz vom Betriebssystem zuweisen und schreibt Warnmeldungen in die MySQL-Fehler-Log-Datei.
innodb_file_io_threads Die Anzahl der Datei-Ein- und -Ausgabe-Threads in InnoDB. Normalerweise sollte dieser Wert 4 sein, aber Windows-Festplatten könnten von einer höheren Zahl profitieren.
innodb_lock_wait_timeout Timeout in Sekunden. Solange wartet eine InnoDB-Transaktion auf eine Sperre, bevor sie abgebrochen (Rollback) wird. InnoDB erkennt automatisch Transaktionsblockierungen in seiner eigenen Sperr-Tabelle und bricht die Transaktion ab (Rollback). Wenn Sie den LOCK TABLES-Befehl oder andere transaktionssichere Tabellen-Handler als InnoDB in derselben Transaktion benutzen, kann eine Blockierung auftreten, die InnoDB nicht erkennen kann. In solchen Fällen ist ein Timeout nützlich, um die Situation zu bereinigen.
innodb_flush_method (Verfügbar ab Version 3.23.40.) Der Vorgabewert hierfür ist fdatasync. Ein andere Option ist O_DSYNC.

8.5.3 InnoDB-Tabellenplatz (Tablespace) erzeugen

Angenommen, Sie haben MySQL installiert und `my.cnf' so editiert, dass sie die notwendigen InnoDB Konfigurationsparameter enthält. Bevor Sie MySQL starten, sollten Sie überprüfen, dass die für InnoDB-Daten- und Log-Dateien angegebenen Verzeichnisse existieren und dass Sie auf diese Zugriffsrechte haben. InnoDB kann keine Verzeichnisse anlegen, nur Dateien. Überprüfen Sie auch, ob Sie auf der Festplatte genug Platz für Daten- und Log-Dateien haben.

Wenn Sie jetzt MySQL starten, fängt InnoDB an, Ihre Daten- und Log-Dateien zu erzeugen. InnoDB gibt dabei etwas wie das folgende aus:

~/mysqlm/sql > mysqld
InnoDB: The first specified data file /home/stefan/data/ibdata1 did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file /home/stefan/data/ibdata1 size to 134217728
InnoDB: Database physically writes the file full: wait...
InnoDB: Data file /home/stefan/data/ibdata2 did not exist: new to be created
InnoDB: Setting file /home/stefan/data/ibdata2 size to 262144000
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file /home/stefan/data/logs/ib_logfile0 did not exist: new to be created
InnoDB: Setting log file /home/stefan/data/logs/ib_logfile0 size to 5242880
InnoDB: Log file /home/stefan/data/logs/ib_logfile1 did not exist: new to be created
InnoDB: Setting log file /home/stefan/data/logs/ib_logfile1 size to 5242880
InnoDB: Log file /home/stefan/data/logs/ib_logfile2 did not exist: new to be created
InnoDB: Setting log file /home/stefan/data/logs/ib_logfile2 size to 5242880
InnoDB: Started
mysqld: ready for connections

Jetzt wurde eine neue InnoDB-Datenbank erzeugt. Sie können sich mit den üblichen MySQL-Client-Programmen wie mysql mit dem MySQL-Server verbinden. Wenn Sie den MySQL-Server mit `mysqladmin shutdown' herunter fahren, gibt InnoDB etwa wie das folgende aus:

010321 18:33:34  mysqld: Normal shutdown
010321 18:33:34  mysqld: Shutdown Complete
InnoDB: Starting shutdown...
InnoDB: Shutdown completed

Wenn Sie jetzt einen Blick auf die Daten-Dateien und Log-Verzeichnisse werfen, sehen Sie die erzeugten Dateien. Das Log-Verzeichnis enthält auch eine kleine Datei namens `ib_arch_log_0000000000'. Diese Datei resultiert aus der Datenbank-Erzeugung, nach der InnoDB die Log-Archivierung ausgeschaltet hat. Wenn MySQL noch einmal gestartet wird, sieht die Ausgabe etwa wie folgt aus:

~/mysqlm/sql > mysqld
InnoDB: Started
mysqld: ready for connections

8.5.3.1 Falls etwas bei der Datenbank-Erzeugung schiefgeht

Falls etwas bei der Datenbank-Erzeugung schiefgeht, sollten Sie alle durch InnoDB erzeugten Dateien löschen. Das heißt alle Daten-Dateien, alle Log-Dateien, die kleine archivierte Log-Datei und - falls Sie bereits InnoDB-Tabellen erzeugt haben, auch die entsprechenden `.frm'-Dateien für diese Tabellen in den MySQL-Datenbankverzeichnissen. Danach können Sie die InnoDB-Datenbankerzeugung erneut versuchen.

8.5.4 InnoDB-Tabellen erzeugen

Angenommen, Sie haben den MySQL-Client mit dem Befehl mysql test gestartet. Um eine Tabelle im InnoDB-Format zu erzeugen, müssen Sie im SQL-Befehl zur Tabellenerzeugung TYPE = InnoDB angeben:

CREATE TABLE kunde (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

Dieser SQL-Befehl erzeugt eine Tabelle und einen Index auf die Spalte A im InnoDB-Tabellenplatz (Tablespace), der aus den Daten-Dateien besteht, die Sie in `my.cnf' angegeben haben. MySQL erzeugt zusätzlich eine Datei `kunde.frm' im MySQL-Datenbankverzeichnis `test'. Intern fügt InnoDB seinem eigenen Datenwörterbuch einen Eintrag für die Tabelle 'test/kunde' hinzu. Wenn Sie daher eine Tabelle namens kunde in einer anderen Datenbank von MySQL erzeugen, kollidieren die Tabellennamen innerhalb InnoDB nicht.

Sie können den freien Speicherplatz im InnoDB-Tabellenplatz (Tablespace) mit dem Tabellen-Status-Befehl von MySQL für jede Tabelle, die Sie mit TYPE = InnoDB erzeugt haben, abfragen. Die Menge freien Platzes im Tabellenplatz (Tablespace) erscheint im Kommentar-Abschnitt der Tabelle in der Ausgabe von SHOW. Beispiel:

SHOW TABLE STATUS FROM test LIKE 'kunde'

Beachten Sie, dass die Statistiken, die SHOW über InnoDB-Tabellen ausgibt, nur Näherungswerte sind: Sie werden für die SQL-Optimierung benutzt. Die für Tabelle und Indexe reservierten Größen in Bytes sind allerdings genau.

8.5.4.1 MyISAM-Tabellen in InnoDB-Tabellen umwandeln

InnoDB hat keine spezielle Optimierung für separate Index-Erzeugung. Daher lohnt es sich nicht, die Tabelle zu exportieren und importieren und die Indexe danach zu erzeugen. Die schnellste Art, eine Tabelle in InnoDB zu ändern, ist, die Einfügungen direkt in eine InnoDB-Tabelle vorzunehmen, das heißt, ALTER TABLE ... TYPE=INNODB zu benutzen oder eine leere InnoDB-Tabelle mit identischen Definitionen zu nehmen und die Zeilen mit INSERT INTO ... SELECT * FROM ... einzufügen.

Um eine bessere Kontrolle über den Einfügeprozess zu erhalten, kann es besser sein, große Tabellen in Teilstücken einzufügen:

INSERT INTO neue_tabelle SELECT * FROM alte_tabelle WHERE schluessel > etwas
                                             AND schluessel <= etwas_anderes;

Nachdem alle Daten eingefügt wurden, können Sie die Tabellen umbenennen.

Während der Umwandlung großer Tabellen sollten Sie den InnoDB-Puffer-Pool hoch setzen, um Festplatten-Ein- und -Ausgaben zu verringern, allerdings nicht höher als 80% des physikalischen Arbeitsspeichers. Sie sollten die InnoDB-Log-Dateien Groß machen und auch den Log-Puffer.

Stellen Sie sicher, dass Sie genug Tabellenplatz (Tablespace) haben! InnoDB-Tabellen benötigen viel mehr Platz als MyISAM-Tabellen. Wenn ein ALTER TABLE nicht mehr genug Platz hat, wird ein Rollback gestartet, das Stunden dauern kann, wenn es auf der Festplatte stattfindet. Bei Einfügeoperationen verwendet InnoDB den Einfügepuffer, um sekundäre Index-Datensätze mit Indexen in Stapeln zu vermischen. Das spart eine Menge an Festplatten-Ein- und -Ausgaben. Beim Rollback wird kein solcher Mechanismus benutzt, weshalb das Rollback bis zu 30 mal länger als das Einfügen dauern kann.

Falls Sie keine wertvollen Daten in Ihren InnoDB-Dateien haben, ist es im Fall eines 'festgefahrenen' Rollback besser, den Datenbank-Prozess zu killen und alle InnoDB-Daten- und Log-Dateien sowie alle InnoDB-Tabellen (`.frm'-Dateien) zu löschen und noch einmal anzufangen, statt darauf zu warten, dass Millionen von Festplatten-Ein- und -Ausgaben beendet werden.

8.5.4.2 Fremdschlüssel-(Foreign Key)-Beschränkungen

InnoDB-Version 3.23.44 hat Fremdschlüssel-(Foreign Key)-Beschränkungen. InnoDB ist der erste MySQL-Tabellentyp, der die Definition von Fremdschlüssel-Beschränkungen zuläßt, um die Integrität Ihrer Daten zu überwachen.

Die Syntax einer Fremdschlüsseldefinition in InnoDB:

FOREIGN KEY (index_spalten_name, ...) REFERENCES tabellen_name (index_spalten_name, ...)

Beispiel:

CREATE TABLE eltern(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE kind(id INT, eltern_id INT, INDEX par_ind (eltern_id),
           FOREIGN KEY (eltern_id) REFERENCES eltern(id)) TYPE=INNODB;

Beide Tabellen müssen vom Typ InnoDB sein und es muss einen Index geben, bei dem der Fremdschlüssel und der referenzierte Schlüssel als erste Spalten aufgeführt sind. Jegliches ALTER TABLE entfernt momentan alle Fremdschlüsselbeschränkungen, die für die Tabelle definiert wurden, aber nicht die Beschränkungen, die die Tabelle referenzieren. Korrespondierende Spalten im Fremdschlüssel und dem referenzierten Schlüssel müssen ähnliche interne Datentypen innerhalb InnoDB sein, so dass sie ohne Typumwandlung verglichen werden können. Die Längen von Zeichenkettentypen müssen nicht dieselben sein. Die Größe und Vorzeichen / kein Vorzeichen von Ganzzahltypen müssen dieselben sein.

Beim Prüfen von Fremdschlüsseln setzt InnoDB gemeinsame Sperren auf Zeilenebene auf kind- und eltern-Datensätze, die es betrachten muss. InnoDB prüft Fremdschlüssel-(Foreign Key)-Beschränkungen sofort: Die Prüfung wird nicht bis zu einem Transaktions-Commit verschoben.

InnoDB läßt zu, dass jegliche Tabelle gelöscht wird, selbst wenn das die Fremdschlüssel-(Foreign Key)-Beschränkungen durchbrechen würde, die die Tabelle referenzieren. Wenn Sie eine Tabelle löschen, werden die Beschränkungen, die in ihrem CREATE-Statement definiert wurden, ebenfalls gelöscht.

Wenn Sie eine gelöschte Tabelle neu erzeugen, muss sie eine Definition haben, die mit den Fremdschlüssel-(Foreign Key)-Beschränkungen konform ist, die sie referenzieren. Sie muss die richten Spaltennamen und -typen haben, und sie muss - wie oben angegeben - Indexe auf die referenzierten Schlüssel haben.

Sie können die Fremdschlüssel-(Foreign Key)-Beschränkungen für eine Tabelle wie folgt auflisten: T with

SHOW TABLE STATUS FROM ihr_datenbank_name LIKE 'T';

Die Fremdschlüssel-(Foreign Key)-Beschränkungen werden im Tabellen-Kommentar der Ausgabe aufgelistet.

InnoDB unterstützt noch kein CASCADE ON DELETE oder andere spezielle Optionen für diese Beschränkungen.

8.5.5 Hinzufügen und Entfernen von InnoDB-Daten- und -Log-Dateien

Sie können die Größe einer InnoDB-Daten-Datei nicht vergrößern. Um Ihrem Tabellenplatz (Tablespace) mehr hinzuzufügen, müssen Sie eine neue Daten-Datei hinzufügen. Um das zu tun, müssen Sie Ihre MySQL-Datenbank herunter fahren, die `my.cnf'-Datei editieren und eine neue Datei zu innodb_data_file_path hinzufügen. Dann starten Sie MySQL erneut.

Momentan können Sie keine Daten-Datei aus InnoDB entfernen. Um die Größe Ihrer Datenbank zu verringern, müssen Sie `mysqldump' benutzen, um alle Ihre Tabellen zu dumpen, eine neue Datenbank erzeugen und Ihre Tabellen in die neue Datenbank importieren.

Wenn Sie die Anzahl oder die Größe Ihrer InnoDB-Log-Dateien ändern wollen, müssen Sie MySQL herunter fahren und sicher stellen, dass er ohne Fehler herunter fuhr. Dann kopieren Sie die alten Log-Dateien an eine sichere Stelle, falls etwas beim Herunterfahren schiefging und Sie die Datenbank wiederherstellen müssen. Löschen Sie die alten Log-Dateien aus dem Log-Datei-Verzeichnis, editieren Sie `my.cnf' und starten Sie MySQL noch einmal. InnoDB meldet beim Starten, dass es neue Log-Dateien anlegt.

8.5.6 Datensicherung und Wiederherstellung einer InnoDB-Datenbank

Der Schlüssel zur sicheren Datenbankverwaltung sind regelmäßige Datensicherungen. Im eine 'binäre' Sicherung Ihrer Datenbank zu machen, tun Sie folgendes:

Momentan gibt es kein Online- oder inkrementelles Datensicherungsprogramm für InnoDB, obwohl diese auf der TODO-Liste sind.

Zusätzlich zu den beschriebenen Binär-Datensicherungen sollten Sie ausserdem regelmäßig Dumps Ihrer Tabellen mit `mysqldump' machen. Der Grund ist, dass eine Binärdatei beschädigt sein kann, ohne dass Sie das bemerken. Gedumpte Tabellen werden in Textdateien gespeichert, die Menschen-lesbar und viel einfacher als binäre Datenbankdateien sind. Aus gedumpten Dateien läßt sich Tabellenbeschädigung leichter erkennen und da ihr Format einfacher ist, ist das Risiko ernsthafter Datenbeschädigung in ihnen geringer.

Es ist eine gute Idee, Dumps zur gleichen Zeit zu machen wie die binäre Datensicherung Ihrer Datenbank. Sie müssen alle Clients aus Ihrer Datenbank ausschließen, um konsistente Schnappschüsse aller Ihrer Tabellen im Dump zu bekommen. Danach können Sie die binäre Datensicherung machen, so dass Sie einen konsistenten Schnappschuss Ihrer Datenbank in zwei Formaten haben.

Um in der Lage zu sein, Ihre InnoDB-Datenbank aus den beschriebenen binären Datensicherungen wiederherzustellen, müssen Sie Ihre MySQL-Datenbank mit allgemeinem Loggen und angeschalteter Log-Archivierung von MySQL laufen lassen. Mit allgemeinem Loggen ist hier der Log-Mechanismus des MySQL-Servers gemeint, der unabhängig von den InnoDB-Logs ist.

Zum Wiederherstellen nach einem Absturz des MySQL-Serverprozesses ist es lediglich nötig, diesen erneut zu starten. InnoDB prüft automatisch die Log-Dateien und führt ein Roll-Forward der Datenbank bis zum aktuellen Stand durch. InnoDB macht ein automatisches Rollback nicht abgeschlossener (committed) Transaktionen, die zur Zeit des Absturzes anhängig waren. Während der Wiederherstellung gibt InnoDB etwa folgendes aus:

~/mysqlm/sql > mysqld
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

Wenn Ihre Datenbank beschädigt wird oder Ihre Festplatte Fehler hat, müssen Sie eine Wiederherstellung aus einer Datensicherung durchführen. Im Falle der Beschädigung sollten Sie zunächst eine Datensicherung finden, die nicht beschädigt ist. Machen Sie aus der Datensicherung eine Wiederherstellung aus den allgemeinen Log-Dateien von MySQL unter Beachtung der Anleitungen im MySQL-Handbuch.

8.5.6.1 Checkpoints

InnoDB hat einen Checkpoint-Mechanismus implementiert, der sich Fuzzy Checkpoint nennt. InnoDB schreibt veränderten Datenbankseiten aus dem Puffer-Pool in kleinen Stapeln (Batch) auf Platte (flush), daher besteht keine Notwendigkeit, den Puffer-Pool in einem einzelnen Stapel zurückzuschreiben, was in der Praxis dazu führen würde, dass SQL-Statements von Benutzern für eine Weile angehalten würden.

Bei der Reparatur nach Abstürzen sucht InnoDB nach einem Checkpoint-Label in den Log-Dateien. Es weiß, dass alle Änderungen an der Datenbank vor dem Label bereits im Platten-Image der Datenbank enthalten sind. InnoDB scannt anschließend die Log-Dateien ab dem Checkpoint vorwärts und wendet die geloggten Änderungen auf die Datenbank an.

InnoDB schreibt in zirkulärer Art in die Log-Dateien. Alle abgeschickten (committed) Änderungen, die dazu führen, dass sich die Datenbankseiten im Puffer-Pool vom Image auf der Platte unterscheiden, müssen in den Log-Dateien verfügbar sein, für den Fall, dass InnoDB eine Wiederherstellung durchführen muss. Das heißt, wenn InnoDB anfängt, eine Log-Datei auf zirkuläre Weise wieder zu benutzen, muss es sicherstellen, dass die Datenbankseiten-Images auf der Festplatte bereits die Änderungen enthalten, die in der Log-Datei mitgeschrieben sind, die InnoDB benutzen wird. Mit anderen Worten muss InnoDB einen Checkpoint machen, was oft das Zurückschreiben auf Platte (flush) geänderter Datenbankseiten beinhaltet.

Das erklärt, warum es Festplatten-Ein- und -Ausgaben sparen kann, wenn man die Log-Dateien sehr Groß macht. Es kann sinnvoll sein, die Gesamtgröße der Log-Dateien so Groß wie den Puffer-Pool oder sogar noch größer zu machen. Der Nachteil großer Log-Dateien ist, dass eine Reparatur nach Absturz länger dauern kann, weil mehr Log-Einträge auf die Datenbank angewendet werden müssen.

8.5.7 Eine InnoDB-Datenbank auf eine andere Maschine verschieben

InnoDB-Daten- und Log-Dateien sind auf allen Plattformen binärkompatibel, wenn das Fließkommazahlenformat auf den Maschinen dasselbe ist. Sie können eine InnoDB-Datenbank einfach verschieben, indem Sie alle relevanten Dateien kopieren, die im vorherigen Abschnitt über Datensicherung erwähnt wurden. Wenn sich das Fließkommaformat auf den Maschinen unterscheidet, sie aber keine FLOAT- oder DOUBLE-Datentypen in Ihren Tabellen benutzt haben, ist die Prozedur dieselbe: Kopieren Sie einfach die relevanten Dateien. Wenn die Formate unterschiedlich sind und Ihre Tabellen Fließkomma-Daten enthalten, müssen Sie `mysqldump' und `mysqlimport' benutzen, um diese Tabellen zu verschieben.

Ein Tipp zur Performance: Schalten Sie Auto-Commit aus, wenn Sie Daten in Ihre Datenbank importieren (unter der Annahme, dass Ihr Tabellenplatz (Tablespace) genug Platz für das große Rollback-Segment enthält, den die große Import-Transaktion erzeugen wird). Machen Sie das Commit erst nach dem Import einer ganzen Tabelle oder eines Segments einer Tabelle.

8.5.8 InnoDB-Transaktionsmodell

Im InnoDB-Transaktionsmodell war das Ziel, die besten Eigenschaften einer multiversionsfähigen Datenbank mit dem traditionellen Zwei-Phasen-Sperren zu verbinden. InnoDB führt Sperren auf Zeilenebene durch und läßt Anfragen vorgabemäßig als nicht sperrende konsistente Leseoperationen laufen, im Stil von Oracle. Das Tabellensperren ist in InnoDB so platzsparend gespeichert, dass keine Sperr-Eskalation benötigt wird: Typischerweise dürfen mehrere Benutzer jede Zeile in der Datenbank oder eine beliebige Teilmenge der Zeilen sperren, ohne dass InnoDB keinen Speicher mehr hat.

Bei InnoDB findet jede Benutzeraktivität innerhalb von Transaktionen statt. Wenn der Auto-Commit-Modus in MySQL benutzt wird, stellt jedes SQL-Statement eine einzelne Transaktion dar. Wenn der Auto-Commit-Modus ausgeschaltet wird, kann man sich vorstellen, dass ein Benutzer stets eine Transaktion offen hat. Wenn er das SQL-COMMIT- oder ROLLBACK-Statement absetzt, beendet das die aktuelle Transaktion und eine neue beginnt. Beide Statements heben alle InnoDB-Sperren auf, die während der aktuellen Transaktion gesetzt wurden. Ein COMMIT bedeutet, dass die in der aktuellen Transaktion gemachten Änderungen permanent und sichtbar für andere Benutzer gemacht werden. Auf der anderen Seite bricht ein ROLLBACK alle Änderungen ab, die in der aktuellen Transaktion gemacht wurden.

8.5.8.1 Konsistentes Lesen

Konsistentes Lesen bedeutet, dass InnoDB seine Multiversionsfähigkeiten nutzt, um einer Anfrage einen Schnappschuss der Datenbank zu einem bestimmten Zeitpunkt zu zeigen. Die Anfrage sieht genau die Änderungen, die von Transaktionen durchgeführt wurden, die bis zu diesem Zeitpunkt abgeschlossen wurden (committed), und keine Änderungen, die später gemacht wurden oder die noch nicht abgeschlossen sind. Die Ausnahme von der Regel ist, dass die Anfrage die Änderungen sieht, die durch die Transaktion selbst durchgeführt wurde, die die Anfrage absetzt.

Wenn eine Transaktion ihr erstes Konsistentes Lesen durchführt, weist InnoDB den Schnappschuss oder Zeitpunkt zu, den jedes Konsistente Lesen in derselben Transaktion benutzen wird. Im Schnappschuss sind alle Transaktionen enthalten, die vor der Zuweisung zum Schnappschuss abgeschlossen (committed) wurden. Daher ist Konsistentes Lesens innerhalb derselben Transaktion auch untereinander konsistent. Sie können einen frischeren Schnappschuss für Ihre Anfragen erhalten, indem Sie die aktuelle Transaktion beenden (commit) und danach neue Anfragen absetzen.

Konsistentes Lesen ist der vorgabemäßige Modus, in dem InnoDB SELECT-Statements abarbeitet. Konsistentes Lesen setzt keinerlei Sperren auf die Tabellen, auf die es zugreift. Daher können andere Benutzer zur selben Zeit, wie Konsistentes Lesen auf die Tabelle durchgeführt wird, diese verändern.

8.5.8.2 Lesevorgänge sperren

Unter manchen Umständen ist Konsistentes Lesen nicht wünschenswert. Angenommen, Sie wollen eine neue Zeile in die Tabelle kind einfügen und dabei sicherstellen, dass das Kind bereits Eltern in der Tabelle eltern hat.

Wenn Sie Konsistentes Lesen benutzen, um die Tabelle eltern zu lesen und in der Tat die Eltern des Kindes in der Tabelle sehen, können Sie dann sicher die Kind-Zeile zur Tabelle kind hinzufügen? Nein, denn es kann sein, dass zwischenzeitlich jemand anderes die Eltern-Zeile aus der Tabelle eltern gelöscht hat und Sie das nicht sehen.

Die Lösung besteht darin, das SELECT im Sperrmodus durchzuführen. LOCK IN SHARE MODE.

SELECT * FROM eltern WHERE NAME = 'Hinz' LOCK IN SHARE MODE;

Wenn Sie ein Lesen im Share-Modus durchführen, heißt das, dass die letzten verfügbaren Daten gelesen werden und eine Shared-Modus-Sperre auf die Zeile gesetzt wird, die gelesen wird. Wenn die letzten Daten zu einer noch nicht abgeschlossenen Transaktion eines anderen Benutzers gehören, wird gewartet, bis die Transaktion abgeschlossen (committed) ist. Eine Shared-Modus-Sperre verhindert, dass andere die Zeile aktualisieren oder löschen, die gerade gelesen wurde. Nachdem festgestellt wurde, dass die obige Anfrage die Eltern 'Hinr' zurückgibt, kann das Kind sicher zur Tabelle kind hinzugefügt und die Transaktion abgeschlossen werden. Dieses Beispiel zeigt, wie Sie in Ihren Applikations-Code referentielle Integrität integrieren können.

Sehen wir uns ein weiteres Beispiel an. Wir haben ein ganzzahliges Zählerfeld in einer Tabelle kind_codes, was benutzt wird, um jedem Kinde, das wir der Tabelle kind hinzufügen, eine eindeutige Kennung zuzuweisen. Es ist offensichtlich, dass Konsistentes Lesen oder Shared-Modus-Lesen kein geeignetes Mittel ist, um den aktuellen Wert des Zählers zu ermitteln, weil nämlich zwei Benutzer der Datenbank denselben Wert des Zählers sehen können und wir daher einen Fehler wegen doppelter Schlüsseleinträge erhalten, wenn wir zwei Kinder mit derselben Kennung in die Tabelle einfügen.

In diesem Fall gibt es zwei geeignete Möglichkeiten, das Lesen und Heraufzählen des Zählers zu implementieren: (1) Zuerst den Zähler um eins erhöhen und erst danach lesen. (2) Zuerst den Zähler im Sperr-Modus FOR UPDATE lesen und danach heraufzählen:

SELECT COUNTER_FIELD FROM kind_codes FOR UPDATE;
UPDATE kind_codes SET COUNTER_FIELD = COUNTER_FIELD + 1;

SELECT ... FOR UPDATE liest die letzten verfügbaren Daten und setzt exklusive Sperren auf jede Zeile, die es liest. Daher setzt es dieselben Sperren, die ein gesuchtes SQL-UPDATE auf die Zeilen setzen würde.

8.5.8.3 Nächsten Schlüssel sperren: Wie das Phantom-Problem vermieden wird

Beim Sperren auf Zeilenebene benutzt InnoDB einen Algorithmus, der Nächsten-Schlüssel-Sperren genannt wird. InnoDB führt das Sperren auf Zeilenebene so durch, dass es beim Suchen oder Scannen eines Indexes auf eine Tabelle gemeinsam genutzte (shared) oder exklusive Sperren auf die Index-Datensätze setzt, die es findet. Daher werden die Sperren auf Zeilenebene genauer Index-Datensatz-Sperren genannt.

Die Sperren, die InnoDB auf Index-Datensätze setzt, betreffen auch die 'Lücke' vor diesem Index-Datensatz. Wenn ein Benutzer eine gemeinsam benutzte (shared) oder exklusive Sperre auf den Datensatz R in einem Index hat, kann ein anderen Benutzer keinen Datensatz direkt vor R (in der Index-Reihenfolge) einfügen. Dieses Sperren von Lücken wird durchgeführt, um das so genannte Phantom-Problem zu vermeiden. Angenommen, man will alle Kinder aus der Tabelle kind lesen und sperren, die eine Kennung größer 100 haben, und irgend ein Feld in der ausgewählten Zeile aktualisieren:

SELECT * FROM kind WHERE ID > 100 FOR UPDATE;

Angenommen, es gibt einen Index auf der Tabelle kind auf der Spalte ID. Unsere Anfrage scannt diesen Index ab dem ersten Datensatz, bei dem ID größer als 100 ist. Wenn jetzt die auf den Index-Datensatz gesetzten Sperren nicht Einfügeoperationen sperren würden, die in die Lücken ausgeführt würden, könnte zwischenzeitlich ein neues Kind in die Tabelle eingefügt werden. Wenn jetzt unsere Transaktion noch einmal folgendes ausführen würde:

SELECT * FROM kind WHERE ID > 100 FOR UPDATE;

Sehen wir ein neues Kind in der Ergebnismenge, die die Anfrage zurückgibt. Das verstößt gegen das Isolationsprinzip von Transaktionen: Eine Transaktion sollte in der Lage sein, so abzulaufen, dass die Daten, die sie gelesen hat, sich nicht während der Transaktion ändern. Wenn wir einen Satz von Zeilen als Daten-Posten betrachten, würde das neue 'Phantom'-Kind dieses Isolationsprinzip durchbrechen.

Wenn InnoDB einen Index scannt, kann es auch die Lücke nach dem letzten Datensatz im Index sperren. Genau das passiert im vorherigen Beispiel: Die Sperren, die von InnoDB gesetzt werden, verhindert jedes Einfügen in die Tabelle an Stellen, wo ID größer als 100 ist.

Sie können Nächsten-Schlüssel-Sperren dazu benutzen, eine Eindeutigkeitsprüfung in Ihre Applikation zu implementieren: Wenn Sie Ihre Daten im Share-Modus lesen und kein Duplikat für eine Zeile sehen, die Sie einfügen werden, können Sie Ihre Zeile sicher einfügen und wissen, dass das Nächsten-Schlüssel-Sperren verhindern wird, dass zwischenzeitlich jemand eine Duplikatzeile Ihrer Zeile einfügt. Daher gestattet Ihnen das Nächsten-Schlüssel-Sperren, die Nicht-Existenz von irgend etwas in Ihrer Tabelle zu 'sperren'.

8.5.8.4 Sperren, die in InnoDB durch unterschiedliche SQL-Statements gesetzt werden

8.5.8.5 Blockierungserkennung und Rollback

InnoDB erkennt automatisch eine Blockierung von Transaktionen und rollt die Transaktion zurück, deren Sperranforderung diejenige war, die die Blockierung aufbaute, also einen Kreis im Warte-Diagramm von Transaktionen. InnoDB kann keine Blockierungen erkennen, bei denen eine Sperre im Spiel ist, die durch ein MySQL-LOCK TABLES-Statement verursacht wurde, oder wenn eine Sperre durch einen anderen Tabellen-Handler als InnoDB gesetzt wurde. Solche Situationen müssen Sie mit innodb_lock_wait_timeout, das in `my.cnf' gesetzt wird.

Wenn InnoDB ein komplettes Rollback einer Transaktion durchführt, werden alle Sperren der Transaktion aufgehoben. Wenn jedoch nur ein einzelnes SQL-Statement als Ergebnis eines Fehlers zurückgerollt wird, können einige der Sperren, die durch das SQL-Statement gesetzt wurde, verbleiben. Das liegt daran, dass InnoDB Zeilensperren in einem Format speichert, die ihm unmöglich machen, im Nachhinein zu erkennen, welche Sperre durch welches SQL-Statement gesetzt wurde.

8.5.8.6 Ein Beispiel, wie konsistentes Lesen bei InnoDB funktioniert

Wenn Sie ein Konsistentes Lesen ausführen, also ein gewöhnliches SELECT-Statement, gibt InnoDB Ihrer Transaktion einen Zeitpunkt (Timepoint), gemäß dem Ihre Anfrage die Datenbank sieht. Wenn daher Transaktion B eine Zeile löscht und das wirksam wird (commit), nachdem Ihr Zeitpunkt zugewiesen wurde, werden Sie die Zeile nicht als gelöscht sehen. Gleiches gilt für Einfüge- und Aktualisierungsoperationen.

Sie können Ihren Zeitpunkt 'vorstellen', indem Sie Ihre Transaktion abschicken (commit) und dann ein weiteres SELECT ausführen.

Das nennt sich Multiversioned Concurrency Control (multiversionierte Gleichzeitigkeitskontrolle):

                  Benutzer A             Benutzer B

              set autocommit=0;      set autocommit=0;
zeit
|             SELECT * FROM t;
|             empty set
|                                    INSERT INTO t VALUES (1, 2);
|
v             SELECT * FROM t;
              empty set
                                     COMMIT;

              SELECT * FROM t;
              empty set;

              COMMIT;

              SELECT * FROM t;
              ---------------------
              |    1    |    2    |
              ---------------------

Daher sieht Benutzer A die durch B eingefügte Zeile erst, wenn B das Einfügen und A seine eigene Transaktion abgeschickt hat (commit), so dass der Zeitpunkt hinter das Commit von B 'vorgestellt' ist.

Wenn Sie den 'frischsten' Zustand der Datenbank sehen wollen, sollten Sie ein sperrendes Lesen (Locking Read) benutzen:

SELECT * FROM t LOCK IN SHARE MODE;

8.5.9 Tipps zur Performance-Steigerung

1. Wenn das Unix-`top' oder der Windows-`Task-Manager' zeigen, dass die CPU-Auslastung weniger als 70% beträgt, ist Ihre Auslastung wahrscheinlich Platten-gebunden. Das kann daran liegen, dass Sie zu viele Transaktionen abschicken (commit) oder dass der Puffer-Pool zu klein ist. Dann kann es helfen, den Puffer-Pool zu vergrößern. Setzen Sie ihn aber nicht höher als 80% des physikalischen Arbeitsspeichers.

2. Packen Sie mehrere Änderungen in eine Transaktion. InnoDB muss das Log jedes Mal auf Platte zurückschreiben (flush), wenn eine Transaktion abgeschickt wird (commit), wenn diese Transaktion irgend welche Änderungen an der Datenbank vorgenommen hat. Weil die Rotationsgeschwindigkeit einer Platte typischerweise höchsten 167 Umdrehungen pro Sekunde beträgt, beschränkt das die Anzahl von Commits auf eben diese Zahl pro Sekunde, wenn die Festplatte nicht das Betriebssystem täuscht.

3. Wenn Sie es sich leisten können, einige der zuletzt abgeschickten (committed) Transaktionen zu verlieren, können Sie den `my.cnf'-Parameter innodb_flush_log_at_trx_commit auf 0 setzen. InnoDB versucht dann trotzdem, das Log einmal pro Sekunde auf Platte zurückzuschreiben (flush), doch dieses Zurückschreiben ist nicht garantiert.

4. Machen Sie Ihre Log-Dateien Groß, selbst so Groß wie den Puffer-Pool. Wenn InnoDB seine Log-Dateien vollgeschrieben hat, muss es die veränderten Inhalte des Puffer-Pools in einem Checkpoint auf Platte schreiben. Kleine Log-Dateien verursachen daher unnötige Festplatten-Schreibzugriffe. Der Nachteil großer Log-Dateien liegt darin, dass die Wiederherstellungszeit länger wird.

5. Ausserdem sollte der Log-Puffer recht Groß sein, sagen wir 8 MB.

6. (Relevant from 3.23.39 up.) In einigen Versionen von Linux und Unix ist das Zurückschreiben von Dateien auf Platte (flush) mit dem Unix-fdatasync und anderen ähnlichen Methoden überraschend langsam. InnoDB benutzt vorgabemäßig die fdatasync-Funktion. Wenn Sie mit der Datenbank-Schreib-Performance nicht zufrieden sind, können Sie versuchen, die innodb_flush_method in `my.cnf' auf O_DSYNC zu setzen, obwohl O_DSYNC auf den meisten Systemen langsamer zu sein scheint.

7. Wenn Sie Daten in InnoDB importieren, stellen Sie sicher, dass MySQL autocommit=1 nicht angeschaltet hat, denn dann benötigt jedes Einfügen ein Zurückschreiben des Logs auf Platte (flush). Setzen Sie vor Ihre SQL-Importdatei die Zeile

set autocommit=0;

und danach

commit;

Wenn Sie die `mysqldump'-Option --opt benutzen, erhalten Sie Dump-Dateien, die sich sehr schnell auch in eine InnoDB-Tabelle importieren lassen, selbst ohne sie in die oben erwähnten set autocommit=0; ... commit;-Wrapper zu verpacken.

8. Hüten Sie sich vor großen Rollbacks beim Einfügen von Massendaten: InnoDB benutzt den Einfüge-Puffer, um beim Einfügen Festplatten-Ein- und -Ausgaben zu sparen, doch beim entsprechenden Rollback wird kein solcher Mechanismus benutzt. Ein Festplatten-gebundenes Rollback kann die 30-fache Zeit des entsprechenden Einfügevorgangs in Anspruch nehmen. Es hilft nicht, den Datenbankprozess zu killen, weil der Rollback erneut starten wird, wenn die Datenbank hochfährt. Die einzige Möglichkeit, ein aus dem Ruder gelaufenes Rollback loszuwerden, besteht darin, den Puffer-Pool zu erhöhen, so dass das Rollback CPU-gebunden wird und damit schnell läuft, oder indem die gesamte InnoDB-Datenbank gelöscht wird.

9. Seien Sie auch vor anderen großen Festplatten-gebundenen Operationen auf der Hut. Benutzen Sie DROP TABLE oder TRUNCATE (ab MySQL-4.0), um eine Tabelle zu löschen, nicht DELETE FROM tabelle.

10. Benutzen Sie das mehrzeilige INSERT, um den Kommunikations-Overhead zwischen Client und Server zu verringern, wenn Sie viele Zeilen einfügen müssen:

INSERT INTO tabelle VALUES (1, 2), (5, 5);

Dieser Tipp gilt natürlich für jeden Tabellentyp, nicht nur für InnoDB.

8.5.9.1 Der InnoDB-Monitor

Ab Version 3.23.41 beinhaltet InnoDB den InnoDB-Monitor, der Informationen über den internen Zustand von InnoDB ausgibt. Wenn er angeschaltet ist, veranlasst der InnoDB-Monitor den MySQL-Server `mysqld', etwa alle 15 Sekunden Daten an die Standardausgabe auszugeben (Hinweis: der MySQL-Client gibt nichts aus). Diese Daten sind nützlich, um die Performance zu tunen. Unter Windows müssen Sie mysqld-max von einer DOS-Kommandozeile aus mit --standalone --console starten, um die Ausgabe auf das DOS-Fenster umzuleiten.

Es gibt einen separaten innodb_lock_monitor, der dieselben Informationen ausgibt wie innodb_monitor, aber zusätzlich Informationen über Sperren, die durch jede Transaktion gesetzt werden.

Die ausgegebene Information enthält Daten über:

Sie können den InnoDB-Monitor mit folgendem SQL-Befehl starten:

CREATE TABLE innodb_monitor(a int) type = innodb;

Und ihn mit folgendem Befehl anhalten:

DROP TABLE innodb_monitor;

Die CREATE TABLE-Syntax ist nur eine Möglichkeit, einen Befehl durch den MySQL-SQL-Parser an die InnoDB-Engine durchzureichen. Wenn Sie die Datenbank herunter fahren, während der Monitor läuft, und Sie den Monitor erneut starten wollen, müssen Sie die Tabelle löschen, bevor Sie ein erneutes CREATE TABLE absetzen können, um den Monitor zu starten. Diese Syntax wird sich in zukünftigen Releases möglicherweise ändern.

Beispiel für die Ausgabe des InnoDB-Monitors:

================================
010809 18:45:06 INNODB MONITOR OUTPUT
================================
--------------------------
LOCKS HELD BY transactions
--------------------------
LOCK INFO:
Number of locks in the record hash table 1294
LOCKS FOR TRANSACTION ID 0 579342744
TABLE LOCK table test/tabelle trx id 0 582333343 lock_mode IX

RECORD LOCKS space id 0 page no 12758 n bits 104 table test/tabelle index
PRIMARY trx id 0 582333343 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 74; 1-byte offs FALSE;
info bits 0
 0: len 4; hex 0001a801; asc ;; 1: len 6; hex 000022b5b39f; asc ";; 2: len 7;
hex 000002001e03ec; asc ;; 3: len 4; hex 00000001;
...
-----------------------------------------------
CURRENT SEMAPHORES RESERVED AND SEMAPHORE WAITS
-----------------------------------------------
SYNC INFO:
Sorry, cannot give mutex list info in non-debug version!
Sorry, cannot give rw-lock list info in non-debug version!
-----------------------------------------------------
SYNC ARRAY INFO: reservation count 6041054, signal count 2913432
4a239430 waited for by thread 49627477 op. S-LOCK file NOT KNOWN line 0 
Mut ex 0 sp 5530989 r 62038708 sys 2155035; rws 0 8257574 8025336; rwx 0 1121090 1848344
-----------------------------------------------------
CURRENT PENDING FILE I/O'S
--------------------------
Pending normal aio reads:
Reserved slot, messages 40157658 4a4a40b8
Reserved slot, messages 40157658 4a477e28
...
Reserved slot, messages 40157658 4a4424a8
Reserved slot, messages 40157658 4a39ea38
Total of 36 reserved aio slots
Pending aio writes:
Total of 0 reserved aio slots
Pending insert buffer aio reads:
Total of 0 reserved aio slots
Pending log writes or reads:
Reserved slot, messages 40158c98 40157f98
Total of 1 reserved aio slots
Pending synchronous reads or writes:
Total of 0 reserved aio slots
-----------
BUFFER POOL
-----------
LRU list length 8034 
Free list length 0 
Flush list length 999 
Buffer pool size in pages 8192
Pending reads 39 
Pending writes: LRU 0, flush list 0, single page 0
Pages read 31383918, created 51310, written 2985115
----------------------------
END OF INNODB MONITOR OUTPUT
============================
010809 18:45:22 InnoDB starts purge
010809 18:45:22 InnoDB purged 0 pages

Einige Anmerkungen zur Ausgabe:

8.5.10 Implementation des Multiversionings

Weil InnoDB eine multiversionierte Datenbank ist, muss es Informationen über alte Versionen von Zeilen im Tabellenplatz (Tablespace) aufbewahren. Diese Informationen werden in einer Datenstruktur gespeichert, die wir in Anlehnung an eine analoge Struktur in Oracle Rollback-Segment nennen.

InnoDB fügt jeder Zeile, die in der Datenbank gespeichert wird, intern zwei Felder hinzu. Ein 6 Byte großes Feld enthält den Transaktions-Identifikator der letzten Transaktion, die die Zeile eingefügt oder aktualisiert hat. Ein Löschen wir intern als eine Aktualisierung behandelt, wobei ein spezielles Bit in die Zeile eingefügt wird, um sie als gelöscht zu markieren. Jede Zeile enthält ausserdem ein 7 Byte großes Feld, das Roll-Zeiger genannt wird. Der Roll-Zeiger zeigt auf einen Rückgängig-Log-Datensatz, der in das Rollback-Segment geschrieben wird. Wenn die Zeile aktualisiert wurde, enthält der Rückgängig-Log-Datensatz die Informationen, die notwendig sind, um den Inhalt der Zeile wieder herzustellen, bevor sie aktualisiert wurde.

InnoDB benutzt die Informationen im Rollback-Segment, um die Rückgängig-Operationen durchzuführen, die bei einem Transaktions-Rollback notwendig sind. Diese Informationen benutzt es auch dafür, um frühere Informationen einer Zeile beim Konsistenten Lesen aufzubauen.

Rückgängig-Logs im Rollback-Segment lassen sich in Logs für Einfügen und für Aktualisieren unterteilen. Einfüge-Rückgängig-Logs werden nur für Transaktions-Rollbacks benötigt und können verworfen werden, sobald die Transaktion abgeschickt ist (commit). Aktualisierungs-Rückgängig-Logs werden auch für Konsistentes Lesens benutzt und können daher erst verworfen werden, wenn keine Transaktion mehr vorhanden ist, für die InnoDB einen Schnappschuss zugewiesen hat, dessen Informationen beim Konsistenten Lesen benötigt werden könnten, um daraus eine frühere Version der Datenbank-Zeile aufzubauen.

Sie müssen daran denken, Ihre Transaktionen regelmäßig abzuschicken (commit), auch die Transaktionen, die nur Konsistentes Lesens ausführen. Ansonsten kann InnoDB Daten aus dem Aktualisierungs-Rückgängig-Log nicht verwerfen und das Rollback-Segment könnte zu Groß werden und Ihren Tabellenplatz (Tablespace) komplett füllen.

Die physikalische Größe eines Rückgängig-Log-Datensatzes im Rollback-Segment ist typischerweise kleiner als die entsprechende eingefügte oder aktualisierte Zeile. Sie können diese Informationen benutzen, um den Platzbedarf für Ihr Rollback-Segment zu berechnen.

In diesem multiversionierten Schema wird eine Zeile nicht unmittelbar physikalisch aus der Datenbank entfernt, wenn Sie sie mit einem SQL-Statement löschen. Erst wenn InnoDB den Datensatz des Aktualisierungs-Rückgängig-Logs löschen kann, der für das Löschen geschrieben wurde, kann es die entsprechende Zeile und ihre Index-Datensätze auch physikalisch aus der Datenbank entfernen. Diese Entfernungsoperation wird Purge genannt und ist recht schnell, wobei sie überschlägig dieselbe Zeit benötigt wie das SQL-Statement, das das Löschen ausführte.

8.5.11 Tabellen- und Index-Strukturen

MySQL speichert seine Daten-Wörterbuch-Informationen über Tabellen in `.frm'-Dateien in den Datenbank-Verzeichnissen. Jedoch hat auch jede Tabelle vom Typ InnoDB ihren eigenen Eintrag, in InnoDB-internen Daten-Wörterbüchern innerhalb des Tabellenplatzes (Tablespace). Wenn MySQL eine Tabelle oder Datenbank löscht, muss er sowohl eine oder mehrere `.frm'-Datei(en) als auch die entsprechenden Einträge im InnoDB-Daten-Wörterbuch löschen. Das ist der Grund, warum Sie InnoDB-Tabellen nicht einfach zwischen Datenbanken verschieben können, indem Sie die `.frm'-Dateien verschieben und warum DROP DATABASE bei InnoDB-Tabellen in MySQL-Versionen bis 3.23.43 nicht funktionierte.

Jede InnoDB-Tabelle hat einen speziellen Index, der Cluster-Index genannt wird, in dem die Daten der Zeilen gespeichert sind. Wenn Sie auf Ihre Tabelle einen PRIMARY KEY definieren, ist der Index des Primärschlüssels der Cluster-Index.

Wenn Sie für Ihre Tabelle keinen Primärschlüssel definieren, erzeugt InnoDB intern einen Cluster-Index, bei dem die Zeilen nach der Zeilen-Kennung (ID) geordnet sind, die InnoDB Zeilen in einer solchen Tabelle zuweist. Die Zeilen-Kennung ist ein 6 Byte großes Feld, das monoton erhöht wird, wenn neue Zeilen eingefügt werden. Daher liegen nach der Zeilen-Kennung geordnete Zeile physikalisch in der Einfüge-Reihenfolge vor.

Der Zugriff auf eine Zeile über den Cluster-Index ist schnell, weil die Zeilendaten auf derselben Seite sind, auf die die Index-Suche führt. In vielen Datenbanken werden die Daten traditionell auf einer anderen Seite als derjenigen, wo sich der Index-Datensatz befindet, gespeichert. Wenn die Tabelle Groß ist, spart die Cluster-Index-Architektur im Vergleich zur traditionellen Lösung auf Festplatten-Ein- und -Ausgaben.

In InnoDB enthalten die Datensätze in Nicht-Cluster-Indexen (die wir auch sekundäre Indexe nennen) den Primärschlüsselwert für die Zeile. InnoDB benutzt diesen Primärschlüsselwert, um vom Cluster-Index aus nach der Zeile zu suchen. Beachten Sie, dass die sekundären Indexe mehr Platz benötigen, wenn der Primärschlüssel lang ist.

8.5.11.1 Physikalische Struktur eines Indexes

Alle Indexe in InnoDB sind B-Bäume, in denen die Index-Datensätze in den Blätter-Seiten des Baums gespeichert sind. Die vorgabemäßige Größe einer Index-Seite ist 16 KB. Wenn neue Datensätze eingefügt werden, versucht InnoDB, 1/16 der Seite für zukünftige Einfügungen und Aktualisierungen des Index-Datensatzes freizuhalten.

Wenn Index-Datensätze in sequentieller (aufsteigender oder absteigender) Reihenfolge eingefügt werden, sind die resultierenden Index-Seiten ungefähr zu 15/16 gefüllt. Wenn der Füllfaktor einer Index-Seite unter 1/12 fällt, versucht InnoDB, den Index-Baum zusammenzuziehen, um die Seite freizugeben.

8.5.11.2 Einfügepufferung

Häufig wird der Primärschlüssel in Datenbank-Applikationen als eindeutiger Identifizierer benutzt und neue Zeilen in aufsteigender Reihenfolge des Primärschlüssels eingefügt. Daher erfordern Einfügungen in den Cluster-Index keine wahlfreien (random) Lesezugriffe auf die Platte.

Sekundäre Indexe auf der anderen Seite sind üblicherweise nicht eindeutig und Einfügungen in sekundäre Indexe erfolgen in einer relativ wahlfreien Reihenfolge. Wenn InnoDB keinen speziellen Mechanismus hierfür benutzen würde, würden diese viele wahlfreie Festplatten-Ein- und -Ausgaben verursachen.

Wenn ein Index-Datensatz in einen nicht eindeutigen sekundären Index eingefügt werden soll, prüft InnoDB, ob die sekundäre Index-Seite bereits im Puffer-Pool ist. Wenn das der Fall ist, führt InnoDB das Einfügen direkt in die Index-Seite durch. Wenn die Index-Seite aber nicht im Puffer-Pool gefunden wird, fügt InnoDB den Datensatz in eine spezielle Einfüge-Puffer-Struktur ein. Der Einfüge-Puffer wird so klein gehalten, dass er komplett in den Puffer-Pool passt, so dass Einfügungen sehr schnell durchgeführt werden können.

Der Einfüge-Puffer wird periodisch mit den sekundären Index-Bäumen in der Datenbank vermengt. Oft können mehrere Einfügeoperationen auf derselben Seite im Index-Baum zusammengefasst werden, so dass Festplatten-Ein- und -Ausgaben eingespart werden. Messungen ergaben, dass der Einfüge-Puffer Einfügungen in eine Tabelle bis zu 15 mal schneller machen kann.

8.5.11.3 Anpassungsfähige Hash-Indexe

Wenn eine Datenbank fast komplette in den Hauptspeicher passt, können Anfragen am schnellsten unter Verwendung von Hash-Indexen ausgeführt werden. InnoDB hat einen automatischen Mechanismus, der Index-Suchen beobachtet, die auf den Indexen durchgeführt werden, die für eine Tabelle definiert wurden. Wenn InnoDB bemerkt, dass Anfragen vom Aufbauen eines Hash-Indexes profitieren könnten, wird ein solcher Index automatisch aufgebaut.

Beachten Sie aber, dass der Hash-Index immer auf der Grundlage eines bestehenden B-Baum-Indexes auf die Tabelle aufgebaut wird. InnoDB kann einen Hash-Index auf einem Präfix beliebiger Länge des Schlüssels aufbauen, der für den B-Baum definiert wurde, abhängig vom Suchmuster, das InnoDB auf dem Index-Baum beobachtet. Ein Hash-Index kann partiell sein: Es ist nicht erforderlich, dass der gesamte Index-Baum im Puffer-Pool zwischengespeichert ist. InnoDB baut Hash-Indexe bei Bedarf automatisch für die Index-Seiten auf, auf die oft zugegriffen wird.

In gewisser Hinsicht kommt InnoDB durch den anpassungsfähigen Hash-Index-Mechanismus (wobei sich InnoDB üppig verfügbarem Hauptspeicher anpasst) der Architektur von Hauptspeicher-Datenbanken nahe.

8.5.11.4 Physikalische Datensatzstruktur

8.5.11.5 Wie eine Auto-Increment-Spalte in InnoDB funktioniert

Wenn der Benutzer nach einem Datenbankstart zuerst einen Datensatz in eine Tabelle T einfügt, in der eine Auto-Increment-Spalte definiert wurde, und er keinen expliziten Wert für die Spalte angibt, führt InnoDB SELECT MAX(auto-inc-column) FROM T aus und weist den um 1 hochgezählten Wert der Spalte und dem Auto-Increment-Zähler der Tabelle zu. Wir sagen dazu, dass der Auto-Increment-Zähler für Tabelle T initialisiert wurde.

InnoDB führt dieselbe Prozedur der Initialisierung des Auto-Increment-Zählers für eine frisch erzeugte Tabelle durch.

Wenn Sie für die Auto-Increment-Spalte einen Wert von 0 angeben, beachten Sie, dass InnoDB die Zeile so behandelt, als hätten Sie den Wert nicht angegeben.

Wenn nach der Initialisierung des Auto-Increment-Zählers der Benutzer eine Zeile eingibt, in der er explizit den Spaltenwert angibt, und dieser größer als der aktuelle Zählerwert ist, wird der Zähler auf den angegebenen Spaltenwert gesetzt. Wenn der Benutzer nicht explizit einen Wert angibt, zählt InnoDB den Zähler um 1 hoch und weist der Spalte diesen neuen Wert zu.

Der Auto-Increment-Mechanismus umgeht beim Zuweisen von Werten vom Zähler Sperren und Transaktionshandhabung. Daher können Lücken in der Nummernfolge entstehen, wenn Sie Transaktionen zurückrollen (Rollback), die Nummern vom Zähler erhalten haben.

Das Verhalten von Auto-Increment ist für die Fälle undefiniert, in denen ein Benutzer der Spalte einen negativen Wert gibt oder wenn der Wert größer als die größte Ganzzahl wird, die im festgelegten Ganzzahl-Typ gespeichert werden kann.

8.5.12 Verwaltung von Datei-Speicherplatz und Festplatten-Eingaben / -Ausgaben

8.5.12.1 Festplatten-Ein- und -Ausgaben

Bei Festplatten-Ein- und -Ausgaben benutzt InnoDB asynchrone Ein- und Ausgaben. Unter Windows NT benutzt es die nativen Ein- und Ausgaben, die vom Betriebssystem zur Verfügung gestellt werden. Unter Unix benutzt InnoDB simulierte asynchrone Ein- und Ausgaben, die in InnoDB eingebaut sind: InnoDB erzeugt eine Reihe von Ein-/Ausgabe-Threads, die sich um Ein- und Ausgabeoperationen kümmern, zum Beispiel Vorwärts-Lesen (Read-Ahead). Zukünftig werden wir auch für Windows NT simulierte Ein-/Ausgaben unterstützen sowie für die Unix-Versionen, die so etwas besitzen, native Ein-/Ausgaben.

Unter Windows NT benutzt InnoDB ungepufferte Ein- und Ausgaben. Das heißt, dass die Festplatten-Seiten, die InnoDB liest oder schreibt, nicht im Datei-Cache des Betriebssystems gepuffert werden. Das spart einiges an Arbeitsspeicher-Bandbreite.

Ab Version 3.23.41 benutzt InnoDB eine neuartige Datei-Flush-Technik, die Doublewrite heißt. Sie erhöht die Sicherheit bei Reparaturen nach Absturz, wenn ein Betriebssystemabsturz oder ein Stromausfall aufgetreten sind, und verbessert auf den meisten Unix-Versionen die Performance, indem die Notwendigkeit von Fsync-Operationen verringert wird.

Doublewrite bedeutet, dass InnoDB zuerst in einen zusammenhängenden Tabellenplatz (Tablespace) namens Doublewrite-Puffer schreibt, bevor Seiten in eine Daten-Datei geschrieben werden. Erst nachdem das Schreiben und Zurückschreiben (Flush) in den Doublewrite-Puffer fertig sind, schreibt InnoDB die Seiten an ihre korrekten Positionen in der Daten-Datei. Wenn das Betriebssystem mitten in einem Seiten-Schreiben abstürzt, findet InnoDB bei der Wiederherstellung eine gute Kopie der Seite im Doublewrite-Puffer.

Ab Version 3.23.41 können Sie auch eine Raw-Disk-Partition als Daten-Datei benutzen, obwohl das bisher noch nicht getestet wurde. Wenn Sie eine neue Daten-Datei erzeugen, müssen Sie das Schlüsselwort newraw unmittelbar nach der Daten-Datei-Größe in innodb_data_file_path angeben. Die Partition muss größer oder gleich der Größe sein, die Sie angeben. Beachten Sie, dass in InnoDB 1 MB 1024 x 1024 Bytes ist, während 1 MB in Festplatten-Spezifikationen üblicherweise 1.000.000 Bytes bedeutet.

innodb_data_file_path=hdd1:5Gnewraw;hdd2:2Gnewraw

Wenn Sie die Datenbank wieder starten, müssen -sue das Schlüsselwort in raw ändern. Ansonsten schreibt InnoDB über Ihre Partition!

innodb_data_file_path=hdd1:5Graw;hdd2:2Graw

Wenn Sie Raw-Disk benutzen, können Sie unter einigen Unixen ungepufferte Ein- und Ausgaben ausführen.

Es gibt zwei Vorwärts-Lesen-(Read-Ahead-)Heuristiken in InnoDB: sequentielles Vorwärts-Lesen und wahlfreies (random) Vorwärts-Lesen. Beim sequentiellen Vorwärts-Lesen bemerkt InnoDB, dass das Zugriffsschema auf ein Segment im Tabellenplatz (Tablespace) sequentiell ist. InnoDB schickt dann vorab einen Stapel von Lesevorgängen von Datenbankseiten an das Ein-/Ausgabesystem. Beim wahlfreien Vorwärts-Lesen bemerkt InnoDB, dass ein bestimmter Bereich im Tabellenplatz (Tablespace) im Zustand des vollständig Eingelesenwerdens in den Puffer-Pool zu sein scheint. Dann schickt InnoDB die verbleibenden Lesevorgänge an das Ein-/Ausgabesystem.

8.5.12.2 Speicherplatzverwaltung

Die Daten-Dateien, die Sie in der Konfigurationsdatei definieren, formen den Tabellenplatz (Tablespace) von InnoDB. Die Dateien werden einfach verkettet, um den Tabellenplatz (Tablespace) zu formen, es wird kein Striping benutzt. Momentan können Sie nicht direkt angeben, wo der Platz für Ihre Tabellen zugewiesen werden soll, ausser wenn Sie folgende Tatsache benutzen: InnoDB weist Speicherplatz von einem neu erzeugten Tabellenplatz (Tablespace) vom niedrigen Ende ausgehend zu.

Der Tabellenplatz (Tablespace) besteht aus Datenbankseiten, deren vorgabemäßige Größe 16 KB beträgt. Diese Seiten werden bis zu einer Ausdehnung von 64 aufeinander folgenden Seiten gruppiert. Die 'Dateien' innerhalb eines Tabellenplatzes (Tablespace) werden in InnoDB Segmente genannt. Der Name des Rollback-Segments ist in gewisser Hinsicht irreführend, weil dieses tatsächlich viele Segmente im Tabellenplatz enthält.

Für jeden Index in InnoDB werden zwei Segmente zugewiesen: eins für die Nicht-Blätter-Knoten (Non-Leaf-Nodes) des B-Baum, das andere für die Blätter-Knoten. Die Idee dahinter ist, für die Blätter-Knoten, die die Daten enthalten, bessere Sequentialität zu erzielen.

Wenn ein Segment innerhalb des Tabellenplatzes anwächst, weist ihm InnoDB die ersten 32 Seiten individuell zu. Danach fängt InnoDB an, dem Segment ganze Ausdehnungen zuzuweisen. InnoDB kann einem großen Segment bis zu vier Ausdehnungen auf einmal hinzufügen, um gute Sequentialität für die Daten sicherzustellen.

Einige Seiten im Tabellenplatz enthalten Bitmaps anderer Seiten. Daher können einige Ausdehnungen in einem InnoDB-Tabellenplatz (Tablespace) nicht Segmenten als Ganzes zugewiesen werden, sondern nur als individuelle Seiten.

Wenn Sie eine Anfrage SHOW TABLE STATUS FROM ... LIKE ... ausführen, um den verfügbaren freien Platz im Tabellenplatz festzustellen, berichtet InnoDB den Platz, der in völlig freien Ausdehnungen im Tabellenplatz sicher benutzt werden kann. InnoDB reserviert immer einige Ausdehnungen für Säuberungs- und interne Zwecke. Diese Ausdehnungen werden nicht in den freien Platz einbezogen.

Wenn Sie Daten aus einer Tabelle löschen, zieht InnoDB die entsprechenden B-Baum-Indexe zusammen. Es hängt vom Schema der Löschvorgänge ab, ob das individuelle Seiten oder Ausdehnungen im Tabellenplatz freigibt, so dass der freigegebene Platz anderen Benutzern zur Verfügung steht. Wenn eine Tabelle gelöscht wird oder alle Zeilen aus ihr gelöscht werden, gibt das garantiert Platz frei für andere Benutzer, aber denken Sie daran, dass gelöschte Zeile physikalisch nur durch eine Purge-Operation entfernt werden können, nachdem Sie nicht mehr für ein Transaktions-Rollback oder für Konsistentes Lesen benötigt werden.

8.5.12.3 Eine Tabelle defragmentieren

Wenn es wahlfreie (random) Einfüge- oder Löschvorgänge in die Indexe einer Tabelle gibt, können die Indexe fragmentiert werden. Unter Fragmentierung verstehen wird, dass die physikalische Reihenfolge der Index-Seiten auf der Platte der alphabetischen Reihenfolge der Datensätze auf den Seiten nicht nahe kommt oder dass es viele unbenutzte Seiten in den 64-Seiten-Blöcken gibt, die dem Index zugewiesen wurden.

Index-Scans können beschleunigt werden, wenn Sie von Zeit zu Zeit mysqldump benutzen, um die Tabelle in eine Textdatei zu dumpen, dann die Tabelle zu löschen und sie aus dem Dump neu aufzubauen. Eine weitere Möglichkeit zur Defragmentierung besteht darin, den Tabellentyp in MyISAM zu ändern (ALTER) und danach wieder in InnoDB zurück. Beachten Sie, dass die MyISAM-Tabelle auf Ihrem Betriebssystem in eine einzige Datei passen muss.

Wenn die Einfügungen in einen Index immer aufsteigend sind und Datensätze nur vom Ende gelöscht werden, garantiert der Speicherplatzverwaltungs-Algorithmus von InnoDB, dass keine Fragmentierung im Index auftritt.

8.5.13 Fehlerbehandlung

Die Fehlerbehandlung in InnoDB ist nicht immer so, wie es die ANSI-SQL-Standards festlegen. Nach ANSI-Standard sollte jeder Fehler während eines SQL-Statements ein Rollback des Statements verursachen. InnoDB rollt manchmal nur Teile des Statements oder auch die gesamte Transaktion zurück. Folgende Liste gibt die Fehlerbehandlung von InnoDB an:

8.5.14 Beschränkungen von InnoDB-Tabellen

8.5.15 InnoDB-Kontaktinformationen

Kontaktinformationen von Innobase Oy, Hersteller der InnoDB-Engine: Website: http://www.innodb.com/. E-Mail: Heikki.Tuuri@innodb.com

Telefon: 358-9-6969 3250 (Büro) 358-40-5617367 (mobil)
Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finnland

8.6 BDB- oder Berkeley_db-Tabellen

8.6.1 Überblick über BDB-Tabellen

Unterstützung für BDB-Tabellen ist in der MySQL-Quelldistribution seit Version 3.23.34 enthalten und in der MySQL-Max-Binärdistribution aktiviert.

BerkeleyDB, erhältlich unter http://www.sleepycat.com/, stattet MySQL mit einem transaktionalen Tabellen-Handler aus. Wenn Sie BerkeleyDB-Tabellen benutzen, haben Ihre Tabellen eine höhere Chance, Abstürze zu überleben. Zusätzlich stehen COMMIT und ROLLBACK für Transaktionen zur Verfügung. Die MySQL-Quelldistribution enthält eine BDB-Distribution, die eine Reihe kleiner Patches hat, damit sie glatter mit MySQL zusammen arbeitet. Sie können keine nicht gepatchte BDB-Version für MySQL verwenden.

Wir bei MySQL AB arbeiten in enger Kooperation mit Sleepycat, um die hohe Qualität der MySQL-/BDB-Schnittstelle zu halten.

Was den Support für BDB-Tabellen angeht, sehen wir uns in der Pflicht, unseren Benutzern zu helfen, Probleme zu lokalisieren und Ihnen zu helfen, einen reproduzierbaren Testfall für jegliche Probleme mit BDB-Tabellen zu erstellen. Solche ein Fall wird an Sleepycat weiter geleitet, die sich dann an uns wenden, um uns zu helfen, das Problem zu finden und zu beheben. Weil das also in zwei Schritten abläuft, kann es bei jeglichen Problemen mit BDB-Tabellen etwas länger dauern, diese zu lösen, als das bei anderen Tabellen-Handlern der Fall ist. Weil jedoch der BerkeleyDB-Code selbst auch von vielen sonstigen Applikationen benutzt wird, sind hierbei keine großen Probleme zu erwarten. See section 2.4.1 Support den MySQL AB anbietet.

8.6.2 BDB installieren

Wenn Sie eine Binärdistribution von MySQL herunter geladen haben, die Unterstützung für BerkeleyDB enthält, folgen Sie einfach den Anweisungen zur Installation einer Binärversion von MySQL. See section 3.2.6 MySQL-Binärdistributionen, die von MySQL AB kompiliert wurden. See section 5.7.5 mysqld-max, ein erweiterter mysqld-Server.

Um MySQL mit BerkeleyDB-Unterstützung zu kompilieren, laden Sie MySQL-Version 3.23.34 oder neuer herunter und konfigurieren Sie MySQL mit der --with-berkeley-db-Option. See section 3.3 Installation der Quelldistribution.

cd /pfad/zur/quelle/von/mysql-3.23.34
./configure --with-berkeley-db

Bitte sehen Sie wegen aktuellerer Informationen im Handbuch nach, das mit der BDB-Distribution mitgeliefert wird.

Obwohl BerkeleyDB selbst sehr gut getestet und zuverlässig ist, wird die MySQL-Schnittstelle noch als Beta-Qualität erachtet. Wir verbessern diese aktiv und optimieren sie, um sie sehr bald stabil zu bekommen.

8.6.3 BDB-Startoptionen

Wenn Sie mit AUTOCOMMIT=0 fahren, werden Ihre Änderungen in BDB-Tabellen erst aktualisiert, wenn Sie COMMIT ausführen. Statt dessen können Sie ROLLBACK ausführen, um Ihre Änderungen zu verwerfen. See section 7.7.1 BEGIN/COMMIT/ROLLBACK-Syntax.

Wenn Sie mit AUTOCOMMIT=1 fahren (der Vorgabe), werden Ihre Änderungen sofort abgeschickt. Sie können eine ausgedehnte Transaktion mit dem SQL-Befehl BEGIN WORK starten. Danach werden Ihre Änderungen solange nicht abgeschickt, bis Sie COMMIT ausführen (oder sich für ROLLBACK entscheiden, um Ihre Änderungen zu verwerfen).

Folgende Optionen für mysqld können benutzt werden, um das Verhalten von BDB-Tabellen zu ändern:

Option Beschreibung
--bdb-home=directory Base Verzeichnis für BDB-Tabellen. Das sollte dasselbe Verzeichnis sein, das Sie für --datadir benutzen.
--bdb-lock-detect=# Berkeley-Sperr-Erkennung. # steht für DEFAULT, OLDEST, RANDOM oder YOUNGEST.
--bdb-logdir=Verzeichnis BerkeleyDB-Log-Datei-Verzeichnis.
--bdb-no-sync Flush-Logs nicht synchronisieren.
--bdb-no-recover BerkeleyDB nicht im Wiederherstellungsmodus starten.
--bdb-shared-data BerkeleyDB im Multi-Prozess-Modus starten (DB_PRIVATE bei der Initialisierung von BerkeleyDB nicht verwenden).
--bdb-tmpdir=verzeichnis Name der temporären Datei von BerkeleyDB.
--skip-bdb BerkeleyDB nicht benutzen.
-O bdb_max_lock=1000 Setzt die höchste Anzahl möglicher Sperren. See section 5.5.5.4 SHOW VARIABLES.

Wenn Sie --skip-bdb benutzen, initialisiert MySQL nicht die BerkeleyDB-Bibliothek und spart deshalb viel Speicher. Natürlich können Sie BDB-Tabellen nicht benutzen, wenn Sie diese Option verwenden.

Normalerweise sollten Sie mysqld ohne --bdb-no-recover starten, wenn Sie vorhaben, BDB-Tabellen zu verwenden. Das kann allerdings zu Problemen führen, wenn Sie mysqld starten und die BDB-Log-Dateien beschädigt sind. See section 3.4.2 Probleme mit dem Start des MySQL-Servers.

Mit bdb_max_lock können Sie die maximale Anzahl von Sperren festlegen (vorgabemäßig 10.000), die auf einer BDB-Tabelle aktiv sein können. Sie sollten diesen Wert herauf setzen, wenn Sie Fehler vom Typ bdb: Lock table is out of available locks oder Got error 12 from ... erhalten, wenn Sie lange Transaktionen ausführen oder wenn mysqld viele Zeilen untersuchen muss, um die Anfrage zu berechnen.

Sie könnten auch binlog_cache_size und max_binlog_cache_size ändern, wenn Sie große, vielzeilige Transaktionen benutzen. See section 7.7.1 BEGIN/COMMIT/ROLLBACK-Syntax.

8.6.4 Kennzeichen von BDB-Tabellen

8.6.5 Was in naher Zukunft bei BDB in Ordnung gebracht werden muss

8.6.6 Betriebssysteme, die von BDB unterstützt werden

Wenn Sie MySQL mit Unterstützung für BDB-Tabellen gebaut haben und folgenden Fehler in der Log-Datei sehen, wenn Sie mysqld starten:

bdb: architecture lacks fast mutexes: applications cannot be threaded
Can't init databases

Bedeutet das, dass BDB-Tabellen für Ihre Architektur nicht unterstützt werden. In diesem Fall müssen Sie MySQL erneut bauen, ohne Unterstützung für BDB-Tabellen.

HINWEIS: Folgende Liste ist nicht komplett. Sie wird aktualisiert, sobald wir mehr Informationen darüber haben.

Momentan wissen wir, dass BDB-Tabellen auf folgenden Betriebssystemen laufen:

Auf folgenden Betriebssystemen läuft BDB nicht:

8.6.7 Fehler, die bei der Benutzung von BDB-Tabellen auftreten können


Go to the first, previous, next, last section, table of contents.