Effizientes MySQL-Schema mit Partitionierung für riesige Datensätze (7.300.000.000 Zeilen und ungefähr 80 GB an Daten)

Question

Dies ist eine Fortsetzung meiner Frage "Effizientes Speichern von 7.300.000.000 Zeilen" (Effizientes Speichern von 7.300.000.000 Zeilen).

Ich habe mich entschieden, MySQL mit Partitionierung zu verwenden, und das vorläufige Schema sieht wie folgt aus:

CREATE TABLE entity_values (
  entity_id MEDIUMINT UNSIGNED DEFAULT 0 NOT NULL, # 3 bytes = [0 .. 16.777.215]
  date_id SMALLINT UNSIGNED DEFAULT 0 NOT NULL, # 2 bytes = [0 .. 65.535]
  value_1 MEDIUMINT UNSIGNED DEFAULT 0 NOT NULL, # 3 bytes = [0 .. 16.777.215]
  value_2 MEDIUMINT UNSIGNED DEFAULT 0 NOT NULL, # 3 bytes = [0 .. 16.777.215]
  UNIQUE KEY (entity_id, date_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PARTITION BY HASH(entity_id) PARTITIONS 25;

Dies ergibt:

• Zeilen = 7.300.000.000 Zeilen (wie in der vorherigen Post-Anforderung angegeben)
• Größe/Zeile = 11 Bytes (3+2+3+3)
• Gesamtgröße = 7.300.000.000 Zeilen * 11 Bytes = 80.300.000.000 Bytes = 80,3 GB
• Partitionen = 25 (3,2 GB / Partition, die Größe der Partition ist etwas willkürlich)

Bitte beachten Sie, dass ich den Primärschlüssel aus dem ursprünglichen Design fallen gelassen habe, da die Spalte "id" nicht verwendet wird.

Nun zu meiner Frage - basierend auf den Anforderungen, die in meinem vorherigen Post dargelegt wurden, und dem oben genannten Schema, haben Sie irgendwelche Vorschläge für weitere Optimierungen/Anpassungen, die vorgenommen werden können? Oder ist das obige Schema "optimal", wenn ich mich für MySQL entschieden habe?

Aktualisierung: Ich habe versucht, den aktuellen Datensatz in das oben genannte Schema zu laden, und die 8.570.532 Zeilen belegten 212.000.000 Bytes Speicherplatz, was ungefähr 24,7 Bytes pro Zeile entspricht.

Aktualisierung: Bitte beachten Sie, dass der Index, der entity_id+date_id abdeckt, auch für Abfragen verwendet wird, die nur entity_id als Ziel haben.

Answer 1

Etwas, das ich nicht ganz verstehe, ist, wie Sie planen, Ihre Daten zu bereinigen. Sie haben täglich 2 Millionen Zeilen, aber Sie haben nicht angegeben, wie viele Daten Sie behalten möchten. Irgendwann werden Sie wahrscheinlich Daten nach Alter ablaufen lassen möchten.

Zu diesem Zeitpunkt möchten Sie dies wahrscheinlich durch das Löschen von Partitionen durchführen, NICHT durch das Ausführen eines Löschvorgangs, der jede einzelne Partition für eine unglaublich lange Zeit sperrt (da er einen vollständigen Tabellenscan durchführen muss, um die zu löschenden Zeilen zu finden), und dann Ihre Tabelle nicht kleiner macht, da die Partitionen voller Löcher sind.

Das Partitionieren nach Hash des entity_id mag für die Suche sinnvoll erscheinen, aber das Partitionieren nach Zeit könnte die Konflikte erleichtern, wenn Sie alte Daten bereinigen, und wird definitiv eine gute Sache sein.

MyISAM hat eine Funktion namens "concurrent insert", die Sie fast sicher die ganze Zeit nutzen müssen, um Gleichzeitigkeit und Leistung zu erreichen; dies verlangt eine Regel "keine Löschungen", was bedeutet, dass Sie Löschungen nur durch Löschen von Partitionen durchführen können.

Aber das Löschen von Partitionen ist auch gut, weil Sie den Festplattenspeicher zurückbekommen können.

Nach alldem gesagt zu haben, 80G ist nicht so groß und ich wäre versucht gewesen, alles in einer einzigen Tabelle zu speichern und InnoDB zu verwenden, um gleichzeitigen Zugriff zu ermöglichen.

Ach ja, und wenn Sie InnoDB verwenden würden, könnten Sie einen Primärschlüssel von entity_id, date_id haben, was bedeutet, dass es Zeilen mit derselben entity_id gruppieren würde. Sie möchten wahrscheinlich einen sekundären Index auf date_id haben, um effiziente Bereinigungen zu ermöglichen.

Bitte testen Sie dies mit Ihren Produktionsdatengrößen und lassen Sie uns wissen, was Sie herausfinden!

Answer 2

Wenn Sie in der Regel alle (oder die meisten) Daten für eine Entity-ID abrufen, sollten Sie erwägen, den Index nur für die Entity-ID und nicht (entity_id, date_id) zu erstellen - es sei denn, Sie benötigen, dass die Datenbank eindeutige Überprüfungen durchführt.

Der Effekt besteht darin, den Index kleiner zu machen, damit mehr davon im Speicher zur Verfügung steht. Ihr Ziel sollte es sein, den Index im Speicher zu haben. Selbst wenn Sie SELECT .. ORDER BY DATE ausführen müssen, werden Sie feststellen, dass MySQL 3650 Werte in Bruchteilen von Sekunden ohne Index sortieren kann. Dieses Problem betrifft die Zeit, um die Zeilen von der Festplatte zu lesen.

Ihr Hauptleistungsproblem ist jedoch, dass die INSERTs Daten für eine Entität über die Festplatte verteilen, was für jeden (Entität, Datum) einen Zugriff auf die Festplatte erfordert und Ihre Abfrage nur einige hundert Zeilen pro Sekunde ausführen lässt. Ihre Partitionierung wird dieses Problem nicht beheben, da sich jede Entität in einer einzelnen Partition befindet und die Zeilen über die Festplatte verteilt sind. (RAID0 auf den Datenträgern wird etwas helfen).

Um eine effiziente Abfrage zu erhalten, müssen die Daten für eine Entität auf der Festplatte kontinuierlich angeordnet werden, was bedeutet, dass die Daten aus der Reihenfolge des Einfügens neu angeordnet werden müssen. Dies können Sie mit MySQL ALTER TABLE.. ORDER BY ... tun, aber es dauert ewig. Ich habe eine 182M-Zeilen-Tabelle, bei der seit den letzten 2 Wochen ein ALTER TABLE.. ORDER BY läuft und es ist noch nicht abgeschlossen.

Deshalb habe ich einen benutzerdefinierten Speicher-Engine geschrieben!

Übrigens bin ich mir nicht sicher, ob Sie durch Partitionierung irgendetwas gewinnen, es sei denn, Sie partitionieren über mehrere Server - oder zumindest über mehrere Datenträger. Die harte Arbeit, die MySQL erledigen muss, wird durch Partitionierung nicht vereinfacht. Es geht alles um die Zugriffszeiten auf die Festplatte.

Das Platzieren jeder Partition auf einem anderen Datenträger könnte hilfreich sein. Ich würde nicht mehr als doppelt so viele Partitionen haben, wie Sie physische Datenträger haben. 2-mal, anstatt 1-mal, würde einige Warteschlangenvorteile bringen, aber ich bezweifle, dass dies viel bewirken würde. Ich bezweifle, dass Sie viel besser abschneiden als bei einer einzelnen nicht partitionierten Tabelle, die über so viele Datenträger wie möglich RAID0 verwendet.

Die Leistung dieser Anwendung wird durch die Anzahl der Zugriffe auf die Festplatte bestimmt und daher unterstützt, wenn Sie mehr Zugriffe pro Sekunde ausführen können.

Bei der Partitionierung erhalten Sie etwas Verarbeitungsparallelität (vorausgesetzt, Sie haben mehrere Prozessoren), aber Ihr System wird I/O-gebunden sein und nicht prozessorgebunden. Wenn Sie die Prozessorauslastung auf 2% erhöhen, tun Sie wahrscheinlich etwas, das Sie nicht tun müssen (oder etwas, das nicht Teil Ihrer Anwendung ist).

Ich schreibe, optimiere und betreibe diese Art von Anwendung seit neun Jahren mit MySQL ... und ich habe alle Narben, die Sie von dieser Erfahrung erwarten können. Sobald Ihre Daten um ein Vielfaches größer sind als der Speicherplatz Ihres Speichers (was meine Definition von "riesig" ist), ist das gesamte Leistungsproblem Festplatten-E/A, was bedeutet, vor allem die Anzahl der Festplattenzugriffe. Viel Glück!

Answer 3

Sie haben in Ihrer vorherigen Frage angegeben, dass Sie alle Zeilen für eine entity_id abrufen werden; Wenn Sie jedoch Datumsgrenzen für bestimmte Entitäten abrufen möchten, könnten Sie Sub-Partitionierung (auch bekannt als Verbund-Partitionierung) verwenden. Je nach Verwendungszweck könnte Ihre Hauptpartition entity_id sein und die Unterpartition als Jahr oder anderen Datumsbereich. Sie können das auch umkehren, wenn es in Ihrem System sinnvoll ist.