Ich habe mich auf MySQL 5.1 schon länger gefreut, vor allem, weil Merged Tables und Partitionen auf dem Programm standen. Irgendwie habe ich allerdings nie die Zeit gefunden, diese neuen Features auszuprobieren. Nun ist es soweit, sich den Partitionen zu widmen. Sind sie wirklich so viel schneller als ein Index?

Die Idee des Partitioning ist es, Tabellen in physisch getrennte Einheiten zu zerschneiden, um entweder auf verschiedene Laufwerke zu schreiben oder oft benötigte Aggregatfunktionen gewissermaßen schon vor der Abfrage zu modellieren. Zum Beispiel könnte eine Logging-Daemon seine Einträge pro Monat in eine andere physische Tabellen-Partition schreiben. Bei einer Abfrage des aktuellen Monats muss dann nur ein kleiner Teil der Gesamttabelle gelesen werden. Auch eine Summen-Abfrage, wie die Anzahl der Log-Einträge pro Monat, kann dann von MySQL parallel auf den verschiedenen Tabellen ausgeführt und später leicht die Summen zusammengeführt werden.

Diesen Performance-Boost einmal selbst zu verifizieren, begab ich mich an mein Debian Testing mit einer MySQL-Sever-Version 5.1.41. Zunächst baute ich zwei kleine Testtabellen auf, beide in einer Datenbank, beide mit der MyISAM-Storage-Engine:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE `KeinePartition` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Monat` mediumint(8) unsigned NOT NULL,
`entry` varchar(32) DEFAULT NULL,
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `Monat` (`Monat`)
);
 
CREATE TABLE `MitPartition` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`Monat` mediumint(8) unsigned NOT NULL,
`entry` varchar(32) DEFAULT NULL,
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) PARTITION BY HASH ( Monat) PARTITIONS 12;

CREATE TABLE `KeinePartition` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Monat` mediumint(8) unsigned NOT NULL,
`entry` varchar(32) DEFAULT NULL,
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `Monat` (`Monat`)
);

CREATE TABLE `MitPartition` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`Monat` mediumint(8) unsigned NOT NULL,
`entry` varchar(32) DEFAULT NULL,
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) PARTITION BY HASH ( Monat) PARTITIONS 12;

Hier kommt ein erster Wermutstropfen ins Spiel: Alle Spalten, die man für eine Partitionierung benutzt, müssen Teil des Primary Key bzw. eines Unique-Indexes sein. Wahrscheinlich kann MySQL keinen Index über verschiedene Partitionen verteilen; jede Partition scheint also eigene Index-Strukturen zu besitzen. Dazu änderte ich die zweite Tabellendefinition wie folgt ab:

1
2
3
4
5
6
7

CREATE TABLE `MitPartition` (
`Monat` mediumint(8) unsigned NOT NULL,
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`entry` varchar(32) DEFAULT NULL,
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Monat`,`id`)
) PARTITION BY HASH ( Monat) PARTITIONS 12;

CREATE TABLE `MitPartition` (
`Monat` mediumint(8) unsigned NOT NULL,
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`entry` varchar(32) DEFAULT NULL,
`Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Monat`,`id`)
) PARTITION BY HASH ( Monat) PARTITIONS 12;

Nun gut. Wir müssen also hier aufpassen, dass wir immer den zusammengesetzen Schlüssel verwenden. Das ist nicht wirklich schön. Aber für einen Test sollte es reichen. Noch ein Wort zur gewählten Partitionierung: Der HASH liefert eine positive Ganzzahl, diese wird modulo der Anzahl der Partitionen gerechnet. Also landet jeder 12. Monat wieder in der gleichen Partition. Egal welcher Monat also in der ersten Partition liegt, Januar liegt auf Januar, Februar auf Februar und so weiter. Der Monat schien mir grob genug, um über die Zeit zu erfassende Werte übersichtlich und performant zu gruppieren.

Im Gegensatz zur Partitionierung ist nun in der Tabelle KeinePartition der Monat als Index geführt, um vergleichbare Aufgaben zu erledigen. Wollen wir nun sehen, welche Vorgehensweise die bessere ist. Nach dem Aufbau der Tabellendefinition meldet phpmyadmin eine verbrauchte Größe von 1K der Tabelle KeinePartition gegenüber 12K ihrer Schwester MitPartition.

Performance bei INSERT-Befehlen

Ein kleines Script lieferte mir dazu eine Million Einträge, wobei der Monat reihum aus 8 Monaten gewählt wurde (Januar bis August 2009 im Format 200901-200908). Der entry-Wert wurde aus MD5 aus der laufenden Nummer errechnet, also MD5(1) bis MD5(999999). Dadurch sind die Einträge zwar individuell, aber auch deterministisch, und so konnte das gleiche Skript nacheinander beide Tabellen befüllen. Dabei kam auf meinem Ottonormalverbraucher-Laptop ein ca. 40%-iger Performance-Vorteil für den Index heraus.

Natürlich kann das Ergebnis leicht von Hardware zu Hardware differieren, weil unterschiedliche Ansätze entweder mehr CPU oder doch etwas mehr Festplatte beanspruchen. Dennoch würde ein hochgezüchteter Server wohl mehr RAM, schnellere Platten als auch eine schnellere CPU als mein Laptop besitzen; der Vergleich der beiden Methoden wäre also in meinen Augen zumindest ähnlich geartet. Insofern würde ich sagen, dass der Index hier performanter arbeitet.

Performance von SELECT-Befehlen

Die große Hoffnung der Partitionen liegt wohl in den dafür optimierten Abfragen. Da die Monate 1:1 auf die Partition abgebildet wurden (es gibt keine zwei Jahre pro Partition), könnte also eine Abfrage nach einem Monat den Vorteil am besten herauskehren. Dazu wählte ich eine Query der Art

1

SELECT MIN(entry), SUM(id) FROM  GROUP BY Monat ORDER BY Monat ASC;

SELECT MIN(entry), SUM(id) FROM  GROUP BY Monat ORDER BY Monat ASC;

Doch Pustekuchen! Der Indez war der Paritionierung immer immer eine Nasenlänge voraus und verbrauchte circa 10% Laufzeit weniger. Das überrascht! Ist die Paritionierung vielleicht nicht optimal gewählt? Doch welche Wahl würde besser passen als dieser, der in die Query 1:1 übertragbare Schnitt? Hat die Community Edition hier etwas weniger Pflege abbekommen als die Enterprise Edition des MySQL-Servers? Ich hoffe doch nicht.

Ganz anders bei der COUNT()-Aggregatfunktion, die aus dem Index bedient werden können. Dort zahlt sich die Teilung der Indizes richtig aus. Wenn man also mal schnell die Anzahl der Einträge pro Monat braucht, ohne Tabelleninhalte selbst zu lesen, dann punktet die Partitionierung hier mit einem Performance-Vorteil von fast 6:1 (0,5s gegenüber 3s mit dem Index). Aber wer braucht immer nur diese Abfragen?

Performance von UPDATE-Befehlen

Ein kurzes UPDATE auf 3 gewählten Monaten (set Timestamp=NOW() WHERE Monat IN (…)” zeigt die Partitionierung als ca. 10% schneller. Hmm, kommt jetzt erst richtig Schwung in den Partitionen auf? Wohl kann hier das Locking etwas besser angesetzt werden also mit der Mega-Tabelle, oder ist es ein anderer Grund? Schade, dass es beim SELECT ausblieb.

Performance von DELETE-Befehlen

Im Prinzip ist hier der große Test bereits gelaufen – mit INSERT, SELECT und UPDATE hat man das Gros erschlagen. Doch zuletzt noch ein kleiner Blick auf ein DELETE-Statement, vielleicht möchte man ja mal alte Log-Einträge aufräumen. Dazu habe ich einfach mal einen Monat gelöscht. Auch hier zeigt sich der geteilte Index wohl vorteilhaft; während der Monat als eigenständige Partition recht schnell verschwindet, verbraucht eine DELETE aus dem Index ca. 30% mehr Zeit.

Sicherlich würde dieses Verhältnis schrumpfen, wären mehrere Jahre pro Partition angesammelt und es wäre auch hier ein Neuaufbau des Index nach den DELETE-Statements nötig. Es scheint vielleicht auch bessere Methoden des Partitions-Managements (sogenanntes Pruning) zu geben als ein DELETE, aber wer will schon seine Applikation auf ein physikalisches Speichermodell hin ausrichten? Da ginge doch glatt der Abstraktionsvorteil verloren.

Platzverbrauch des Index und der Partition

Nach dem DELETE von 125000 Datensätzen sind in beiden Tabellen noch jeweils 875000 Einträge gespeichert. Etwa 6MB wurden freigegeben, liegen aber noch unnütz herum. Also optimierte ich beide Tabellen wieder mit dem MySQL-Befehl OPTIMIZE TABLE. Die Tabellen nehmen nun etwa 59,6MB für die nicht-partitionierte Variante und 54,6MB für die partitionierte Variante ein. Das heißt, die Tabelle mit dem zusätzlichen Index für den Monat ist ca. 10% größer als die Partitionen mit dem zusammengesetzten Schlüssel. Das finde ich etwas merkwürdig und ich kann eigentlich keinen triftigen Grund erkennen.

Fazit

Die Partitionierung ist insgesamt eine gute Sache, denkt man an die leicht schnelleren Updates und das wirklich sehr viel schnellere Löschen. Das leicht verzögerte Lesen und die spürbar langsameren INSERTs könnten auch vom zusammengesetzten PRIMARY KEY her stammen. Wer jedoch zu 99% mit INSERT und SELECT auskommt, der ist wohl weiterhin mit einem einfachen Index besser beraten. Es scheint wirklich nötig, die eigene Applikation genau unter die Lupe zu nehmen und optimierbare Bereiche für Partitionierung zu entdecken – schwer wird dies allemal, weil der Performance-Schub, falls tatsächlich vorhanden, wohl kaum messbar sein wird.