Ich arbeite als Wissenschaftler und habe MySQL als Speicher für die Ergebnisse meiner numerischen Simulationen verwendet. Normalerweise habe ich eine Reihe von Daten, die durch mein Experiment und einen Kontrollsatz erhalten wurden. Diese zwei Datensätze werden in einer Tabelle gespeichert. Ein Indikatorfeld sagt mir, ob ein Datensatz von einem Experiment oder von einem Kontrollsatz stammt. Diese Tabelle hat normalerweise ~ 100 Millionen Datensätze. 50 Millionen Experimente und 50 Millionen Kontrollen.
Wenn ich meine Daten nachbearbeite, besteht meine typische Aufgabe darin, zuerst die folgenden zwei Fragen zu stellen:
%Vor%und
%Vor%Ich habe einen mehrspaltigen Index für RC, df. Diese Abfragen benötigen viel Zeit und die Abfragen verbringen die meiste Zeit "Daten senden"
Ich betreibe dies auf 8core MacPro mit 12 GB RAM. Ich bin ein einzelner Benutzer dieser Maschine und diese Aufgabe ist die primäre Aufgabe, deshalb kann ich den ganzen RAM zu MySQL widmen. Alle Tabellen sind MyISAM (ich kann sie konvertieren, wenn das die Geschwindigkeit meiner Abfragen erhöhen würde).
Ich würde mich über Empfehlungen freuen, wie Sie diese Abfragen beschleunigen können. Sollte ich einige Einstellungen ändern, Indizes, Abfragen ....
In jeder dieser Abfragen erwarte ich, ~ 50 Millionen Datensätze zurückzubekommen. Beachten Sie, dass die Aufteilung der Tabelle in zwei Tabellen, die eine experimentelle und eine Kontrollbeobachtung enthalten, aus administrativen Gründen nicht möglich ist.
Hier ist die Ausgabe von:
%Vor%Hier ist die Ausgabe von:
%Vor%Hier ist die Ausgabe von:
%Vor%Es muss etwas ernsthaft falsch sein, dass Ihre Anfrage 2 Stunden dauert, wenn ich dieselbe Aufgabe in weniger als 60 Sekunden auf ähnlicher Hardware ausführen kann.
Einige der folgenden könnten sich als hilfreich erweisen ...
Überprüfen Sie Ihre Serverkonfiguration und optimieren Sie sie entsprechend. Einige der folgenden Ressourcen sollten nützlich sein.
Nun zu den weniger offensichtlichen ...
Warum verarbeiten Sie nicht alle Daten innerhalb von MySQL, sodass Sie keine großen Datenmengen an Ihre Anwendungsschicht senden müssen? Das folgende Beispiel verwendet einen Cursor zum Schleifen und Verarbeiten von 50 Millionen Zeilen serverseitig in weniger als 2 Minuten. Ich bin kein großer Fan von Cursorn, besonders in MySQL, wo sie sehr begrenzt sind, aber ich schätze, Sie würden die Ergebnismenge loopen und irgendeine Form der numerischen Analyse machen, so dass die Verwendung eines Cursors in diesem Fall vertretbar ist / p>
Vereinfachte myisam-Ergebnistabelle - Schlüssel basierend auf Ihren.
%Vor%Ich habe 100M Datenzeilen generiert, wobei die Schlüsselfelder ungefähr die gleiche Kardinalität haben wie in Ihrem Beispiel:
%Vor%Gespeicherte Prozedur
Ich habe eine einfache gespeicherte Prozedur erstellt, die die erforderlichen Daten abruft und verarbeitet (verwendet dieselbe, wenn die Bedingung Ihr Beispiel ist)
%Vor%Die folgenden Laufzeiten wurden beobachtet:
%Vor%Hmmmm, Leistung ein bisschen enttäuschend, so auf die nächste Idee.
Warum innodb ?? weil es Clustered-Indizes gibt! Sie werden das Einfügen mit Hilfe von innodb langsamer finden, aber hoffentlich wird es schneller zu lesen sein, also ist es ein Kompromiss, der sich lohnen könnte.
Der Zugriff auf eine Zeile durch den gruppierten Index erfolgt schnell, da sich die Zeilendaten auf derselben Seite befinden, auf der die Indexsuche führt. Wenn eine Tabelle groß ist, speichert die Clustered-Index-Architektur häufig eine Datenträger-E / A-Operation im Vergleich zu Speicherorganisationen, die Zeilendaten unter Verwendung einer anderen Seite als den Indexdatensatz speichern. Zum Beispiel verwendet MyISAM eine Datei für Datenzeilen und eine weitere für Indexdatensätze.
Weitere Informationen hier:
Vereinfachte innodb-Ergebnistabelle
%Vor%Ein Problem mit innodb besteht darin, dass auto_increment-Felder, die Teil eines zusammengesetzten Schlüssels sind, nicht unterstützt werden. Daher müssten Sie den inkrementierenden Schlüsselwert mithilfe eines Sequenzgenerators, Triggers oder einer anderen Methode selbst bereitstellen Ergebnistabelle selbst ??
Auch hier habe ich 100 Millionen Datenzeilen generiert, wobei die Schlüsselfelder ungefähr die gleiche Kardinalität haben wie in Ihrem Beispiel. Machen Sie sich keine Sorgen, wenn diese Zahlen nicht mit dem Myisam-Beispiel übereinstimmen, da innodb die Kardinalitäten schätzt, so dass sie nicht genau gleich sind. (aber sie sind - der gleiche Datensatz verwendet)
%Vor%Gespeicherte Prozedur
Die gespeicherte Prozedur entspricht genau dem obigen myisam-Beispiel, wählt aber stattdessen Daten aus der innodb-Tabelle aus.
%Vor%Die Ergebnisse lauten wie folgt:
%Vor%ca. <2-3> schneller als die Implementierung der myisam-Engine! (innodb FTW)
Die Idee besteht also darin, Datenmengen an eine Anwendungsschicht (C #, was auch immer) zurückzugeben, die dann die Ergebnisse zu einer sammlungsbasierten Datenstruktur hinzufügen und dann die Daten intern verarbeiten können.
Gespeicherte Prozedur
Die gespeicherte Prozedur benötigt 3 Parameter rc, df_low und df_high, mit denen Sie einen Datenbereich wie folgt auswählen können:
%Vor%offensichtlich, je höher der df-Bereich, desto mehr Daten werden Sie extrahieren.
%Vor%Ich habe auch eine Myisam-Version gefunden, die bis auf die verwendete Tabelle identisch ist.
%Vor%Basierend auf dem obigen Cursor-Beispiel würde ich erwarten, dass die innodb-Version die myisam-Version übertrifft.
Ich habe eine schnelle und dreckige multi-threaded C # -Anwendung entwickelt, die die gespeicherte Prozedur aufruft und die Ergebnisse zu einer Sammlung für die Post-Abfrageverarbeitung hinzufügt. Sie müssen keine Threads verwenden, derselbe Batched-Abfrage-Ansatz könnte sequenziell ohne großen Leistungsverlust durchgeführt werden.
Jeder Thread (QueryThread) wählt einen Bereich von df-Daten aus, führt eine Schleife in der Ergebnismenge durch und fügt jedes Ergebnis (Zeile) zur Ergebnissammlung hinzu.
%Vor%Laufzeit beobachtet wie folgt:
%Vor%So wurden 50 Millionen Zeilen abgerufen und zu einer Sammlung in weniger als 60 Sekunden hinzugefügt.
Ich habe dasselbe mit der gespeicherten Prozedur von myisam versucht, die 2 Minuten dauerte.
%Vor%In meinem vereinfachten System funktioniert die Myisam-Tabelle nicht so schlecht, so dass es sich möglicherweise nicht lohnt, zu innodb zu migrieren. Wenn Sie sich entschieden haben, Ihre Ergebnisdaten in eine innodb-Tabelle zu kopieren, tun Sie dies wie folgt:
%Vor%Wenn Sie das Ergebnis von der innodb-PK bestellen, bevor Sie das Ganze in eine Transaktion einfügen und verpacken, beschleunigt sich die Sache.
Ich hoffe, dass einige davon hilfreich sind.
Viel Glück
Tags und Links mysql performance