Geteiltes Leid ist halbes Leid

Bereits in der letzten ORDIX News wurde eine neue, interessante Funktion der MySQL-Version 5.1 vorgestellt: Zeitgesteuerte MySQL Jobs [4]. In dieser Ausgabe präsentieren wir Ihnen nun weitere Neuerungen: Die Partitionierungsmöglichkeiten von MySQL.

Trennungsgründe

Was bedeutet eigentlich Partitionierung? Unter einer partitionierten Tabelle versteht man die Trennung der hinter einer Tabelle liegenden Daten auf mehrere physikalische Einheiten (bei MySQL meist Dateien in einem oder mehreren Dateisystemen). Es handelt sich also um ein physikalisches Datenbankmodell. Im Wesentlichen gibt es zwei Gründe, diese Technik einzusetzen:

1. der Wunsch nach verbesserter Performance beim Zugriff auf Tabellen
2. die Möglichkeit, mehr Kontrolle über die physikalische Struktur der Datenbank/Tabelle zu erlangen

So lässt sich beispielsweise herausfinden, in welchem Dateisystem bzw. auf welchem Device die Daten liegen.

Horizontale und vertikale Partitionierung

Prinzipiell sind zwei Partitionierungsarten bekannt, von denen MySQL aktuell aber nur eine unterstützt. Die horizontale und die vertikale Partitionierung. Bei der horizontalen Partitionierung werden Tabellen zeilenweise separiert. Bei der vertikalen Partitionierung werden die Spalten einer Tabelle physikalisch getrennt.

Beispiel 1: Horizontale Partitionierung

Sie haben eine Tabelle mit Rechnungsdaten, die über mehrere Jahre angewachsen ist (siehe Abbildung 1). Eine Möglichkeit der horizontalen Partitionierung bestünde darin, die Daten auf Basis der Spalte „eingangsdatum" nach Jahren auf mehrere Partitionen zu verteilen. Diese Art der Partionierung wird von MySQL aktuell angeboten.

create table rechnungen
    (
    lfdnr int,
    kunde int,
    betrag decimal(10,2),
    faellig date,
    bezahl enum('JA', 'NEIN'),
    constraint pk_rechnungen primary key (lfdnr, faellig)
    ) engine=myisam
    partition by range (year(faellig))
    (
    partition p2005 values less than (2006) data directory '/usr/local/mysql/var/part/dev1/p2005/',
    partition p2006 values less than (2007) data directory '/usr/local/mysql/var/part/dev2/p2006/',
    partition p200x values less than maxvalue data directory '/usr/local/mysql/var/part/dev3/p200x/'
    );

Beispiel 2: Vertikale Partitionierung

Sie haben eine Tabelle mit Mitarbeiterstammdaten, welche zusätzlich eine Spalte „mitarbeiterfoto" vom Datentyp „blob" beinhaltet. Diese Spalte soll getrennt von den anderen Daten gespeichert werden. Diese vertikale Trennung von Spalten eines Datensatzes lässt sich aktuell noch nicht mit MySQL realisieren.

Ohne Regeln geht es nicht

Die horizontale Trennung von Datensätzen kann auf Grundlage mehrerer Prinzipien erfolgen. Aktuell gibt es fünf mögliche Wege:

RANGE Partitioning
Die Daten werden aufgrund der Wertausprägung einer Spalte den verschiedenen Partitionen zugeordnet. Beispiel: Rechnungen aus den Jahren 2000 - 2005 -> Partition A und Rechnungen aus den Jahren 2006 - 2009 -> Partition B

LIST Partitioning
Diese Form ist der RANGE-Partitionierung sehr ähnlich. Das Kriterium für die Zuordnung einer Partition muss hier jedoch nicht fortlaufend sein. Die Entscheidungsliste für jede Partition kann hier willkürlich zusammengestellt werden. Beispiel: Rechnungen aus den Jahren 00, 02, 04, 06, 08 -> Partion A und Rechnungen aus den Jahren 01, 03, 05, 07, 09 -> Partition B

HASH Partitioning
Diese Form wird verwendet, wenn eine möglichst gleichmäßige Verteilung der Daten indie Partitionen erreicht werden soll. Oftmals werden hier Moduloperationen verwendet, um eine Partitionsentscheidung zu treffen.

KEY Partitioning
Diese Form ist dem HASH Partitioning sehr ähnlich. Allerdings wird als Entscheidungsgrundlage immer der Primärschlüssel der Tabelle und als Operation die Funktion MD5 verwendet. Auch dieser Mechanismus verteilt die Daten gleichmäßig über die verfügbaren Partitionen.

Subpartitioning
Im Prinzip handelt es sich hierbei um keine eigenständige Partitionierungsform. Über das Subpartitioning lassen sich vielmehr Partitionen erneut in kleinere Einheiten aufteilen.

Ohne Fleiß, kein Preis

Was bringen nun partitionierte Tabellen konkret? Diese Frage ist nicht einfach zu beantworten. Die durch Partitionierung zu erzielenden Performance-Vorteile hängen stark von der Struktur der jeweiligen Tabelle, also von der Anzahl und der Art der Datentypen sowie von der vorhandenen Hardware (Kontroller und Platten) ab.

Als Anhaltspunkt sei hier ein Benchmark-Beispiel der Firma MySQL genannt: In einem von Robin Schumacher geschriebenen Artikel [1] konnten die Zugriffszeiten für ein SELECT-Statement durch den Einsatz von einigen wenigen Partitionen um den Faktor 10 optimiert werden. Dies sind natürlich beeindruckende Zahlen, die in der Realität nicht unbedingt zu erreichen sind. In der Regel sollten jedoch deutlich messbare Verbesserungen realisierbar sein.

Gut zu wissen!

Die Möglichkeit der Partitionierung kann bei allen Storage Engines von MySQL eingesetzt werden. Zu den bekanntesten Engines dürften wohl MyISAM [2] und InnoDB [3] gehören. Informationen über die Partitionen einer Tabelle können am einfachsten über die Meta-Datenbank „information_schema" ermittelt werden. In der Tabelle „partitions" finden sich alle relevanten und wissenswerten Parameter wieder.

Selbstverständlich können jederzeit Partitionen über die entsprechende Syntax hinzugefügt oder gelöscht werden. So könnten die in diesem Beispiel verwendeten Jahrespartitionen (siehe Abbildung 1) beispielsweise durch den MySQL Scheduler [4] automatisiert am Anfang eines Jahres angelegt werden.

Zu guter Letzt

Die Möglichkeit der Partitionierung ist unter mehreren Gesichtspunkten eine nützliche Funktion. Zum einen gibt sie dem Datenbankadministrator die Möglichkeit, das Tabellenwachstum gezielt zu steuern und zu kontrollieren. Zum anderen ist sie eine einfache und wirkungsvolle Methode, die Performance einer MySQL-Datenbank deutlich zu verbessern. Probieren Sie es doch einfach selbst aus.

Matthias Jung (info@ordix.de).

Seitenanfang

Seite drucken

Seite weiterempfehlen