Welche Granularität ist für die Partitionierung von Datenbanktabellen zu wählen?

Question

Ich habe eine Tabelle mit 20 Millionen Datensätzen in einer MySQL-Datenbank. SELECTs funktionieren sehr schnell, weil ich gute Indizes eingerichtet habe, aber INSERT- und UPDATE-Vorgänge werden sehr langsam. Die Datenbank ist das Backend einer stark belasteten Webanwendung. INSERTs und UPDATEs sind wirklich langsam, weil es etwa 5 Indizes in dieser Tabelle gibt und die Indexgröße jetzt etwa 1 GB beträgt - ich schätze, dass die Berechnung zu viel Zeit in Anspruch nimmt.

Um dieses Problem zu lösen, habe ich beschlossen, eine Tabelle zu partitionieren. Ich verwende MySQL 4 und kann nicht aktualisieren (keine direkte Kontrolle über den Server), also werde ich eine manuelle Partitionierung vornehmen - für jeden Abschnitt eine eigene Tabelle erstellen.

Der Datensatz setzt sich aus etwa 18000 verschiedenen logischen Slices zusammen, die völlig getrennt abgefragt werden können. Daher könnte ich 18000 Tabellen mit den Namen (maindata1, maindata2, etc.) erstellen. Ich bin mir jedoch nicht sicher, ob dies der optimale Weg ist? Abgesehen von der offensichtlichen Tatsache, dass ich jedes Mal, wenn ich etwas manuell tun möchte, 18000 Einträge im Verwaltungstool durchsuchen muss, mache ich mir Sorgen um die Leistung des Dateisystems. Das Dateisystem ist ext3. Ich bin mir nicht sicher, wie schnell es beim Auffinden von Dateien in einem Verzeichnis mit 36000 Dateien ist (es gibt eine Datendatei und eine Indexdatei).

Wenn dies ein Problem ist, könnte ich einige Datenabschnitte in derselben Tabelle zusammenfassen. Zum Beispiel: maindata10, maindata20, usw., wobei maindata10 die Slices 1, 2, 3...10 enthalten würde. Wenn ich mich für "Gruppen" von 10 entscheiden würde, hätte ich nur 1800 Tabellen. Wenn ich 20 gruppieren würde, hätte ich 900 Tabellen.

Ich frage mich, was die optimale Größe dieser Gruppierung wäre, d. h. die Anzahl der Dateien in einem Verzeichnis im Vergleich zur Größe der Tabelle?

Edita: Ich frage mich auch, ob es eine gute Idee wäre, mehrere separate Datenbanken zu verwenden, um Dateien zusammenzufassen. Selbst wenn ich also 18000 Tabellen hätte, könnte ich sie in, sagen wir, 30 Datenbanken mit je 600 Tabellen gruppieren. Das scheint mir sehr viel einfacher zu sein. Ich weiß nicht, ob die Verwendung mehrerer Datenbanken die Leistung oder den Speicherplatzbedarf erhöhen oder verringern würde (es würde allerdings die Sicherung und Wiederherstellung erschweren).

Answer 1

Es gibt einige Taktiken, die Sie anwenden können, um die Leistung zu steigern. Mit "Partitionen" meinen Sie vermutlich "Versionen von Tabellen mit demselben Spaltenlayout, aber unterschiedlichen Dateninhalten".

Besorgen Sie sich einen Server, auf dem mySQL 5 läuft, wenn Sie können. Er ist schneller und besser in diesem Bereich, so dass Sie nach dem Upgrade keine Probleme mehr haben werden.

Verwenden Sie InnoDB? Wenn ja, können Sie zu myISAM wechseln? (Wenn Sie starre transaktionale Integrität benötigen, können Sie möglicherweise nicht wechseln).

Für die Partitionierung können Sie versuchen, herauszufinden, welche Kombination von Daten-Slice zu ungefähr gleich großen Partitionen führt (nach Zeilenzahl). An Ihrer Stelle würde ich nicht mehr als 20 Partitionen anstreben, es sei denn, Sie können sich selbst beweisen, dass Sie das brauchen.

Wenn nur wenige Ihrer Daten-Slices aktiv aktualisiert werden (z. B. wenn es sich um "Daten dieses Monats" und "Daten des letzten Monats" handelt), würde ich erwägen, diese in kleinere Slices aufzuteilen. So könnten Sie beispielsweise die Daten dieser Woche, der letzten Woche und der Vorwoche in eigenen Partitionen speichern. Wenn sich die Partitionen dann abkühlen, kopieren Sie die Daten und fassen sie zu größeren Gruppen wie "vorletztes Quartal" zusammen. Dies hat den Nachteil, dass es routinemäßige Wartungsarbeiten wie am Sonntagabend erfordert. Es hat aber den Vorteil, dass die meisten oder alle Aktualisierungen nur auf einem kleinen Teil Ihrer Tabelle stattfinden.

Answer 2

Sie sollten sich die Merge-Engine ansehen, wenn Sie myISAM verwenden. Auf diese Weise erhalten Sie so ziemlich die gleiche Funktionalität wie bei einer Partitionierung von mysql5, Sie können den gleichen Select ausführen, den Sie jetzt ausführen.