Ich habe ein Softwareprojekt, an dem ich arbeite, das mich verrückt gemacht hat. Hier ist unser Problem: Wir haben eine Reihe von Datenkontakten, die jede Sekunde protokolliert werden müssen. Es muss Zeit, Peilung (Array von 360-1080 Bytes), Bereich und einige andere Felder enthalten. Unser System benötigt außerdem die Fähigkeit, diese Daten für bis zu 30 Tage zu speichern. In der Praxis kann es bis zu 100 verschiedene Kontakte geben, also maximal zwischen 150.000.000 Punkten und etwa 1.000.000.000 verschiedenen Punkten innerhalb von 30 Tagen.
Ich versuche, über die beste Methode nachzudenken, um all diese Daten zu speichern und später wiederzufinden. Mein erster Gedanke war, einige RDBMS wie MySQL zu verwenden. Da ich ein embedded C / C ++ Programmierer bin, habe ich sehr wenig Erfahrung mit MySQL mit solch großen Datensätzen. Ich habe mich mit kleinen Datensätzen beschäftigt, aber nicht annähernd so groß. Ich habe das folgende Schema für zwei Tabellen generiert, die einige der Daten speichern:
%Vor% Die Header-Tabelle enthält nur 10 Zeilen und ist statisch. Es gibt nur an, aus welchem Sensor die Rohdaten stammen und wie viele Bytes dieser Sensortyp ausgibt. Die RAW_DATA_TABLE speichert im Wesentlichen die rohen Peilungsdaten (ein Array von 360-1080 Bytes, es repräsentiert bis zu drei Abtastungen pro Grad). Die rel_RASTER_TABLE enthält Metadaten für die RAW_DATA_TABLE. Es können mehrere Kontakte vorhanden sein, die sich auf dieselbe Rohdatenzeile beziehen. Das in rel_RASTER_TABLE gefundene data_id zeigt auf das internal_id einer Zeile in der RAW_DATA_TABLE, um die Anzahl der benötigten Schreibvorgänge zu verringern.
Wie Sie wahrscheinlich feststellen können, habe ich Probleme beim Lesen und Löschen von dieser Datenbank. Ein Bediener unserer Software kann Echtzeitdaten sehen, wenn sie auftauchen, und auch in den Wiederherstellungsmodus gehen und einen Datenbereich aus der Vergangenheit, beispielsweise der vergangenen Woche, überlagern. Unser Backend-Logging-Server greift die History-Zeilen und sendet sie über eine CORBA-Schnittstelle an eine Anzeige. Während all dies passiert, habe ich einen Worker-Thread, der gleichzeitig 1000 Zeilen für Daten löscht, die länger als 30 Tage sind. Dies ist für den Fall, dass eine Sitzung länger als 30 Tage dauert, was passieren kann.
Das System, das wir zur Zeit implementiert haben, funktioniert gut für kleinere Datenmengen, aber nicht für große Mengen. Unsere Select- und Delete-Anweisungen können mehr als 2 Minuten dauern, um Ergebnisse zurückzugeben. Das tötet die Leistung unseres Echtzeit-Consumer-Threads vollständig. Ich vermute, dass wir unsere Schemas nicht korrekt entwerfen, die falschen Schlüssel auswählen, unsere SQL-Abfragen nicht richtig optimieren oder nur eine Teilmenge davon. Unsere Schreibvorgänge werden nicht beeinträchtigt, wenn die anderen Vorgänge nicht zu lange dauern.
Hier ist eine Beispiel-SQL-Abfrage, die wir zum Abrufen von Verlaufsdaten verwenden:
%Vor%Ich entschuldige mich im Voraus dafür, dass dies eine so lange Frage ist, aber ich habe andere Ressourcen ausgeschöpft, und dies ist meine letzte Zuflucht. Ich denke, ich würde versuchen, so beschreibend wie möglich zu sein. Seht ihr, dass ich auf den ersten Blick unser Design verbessern kann? Oder können wir unsere Select- und Delete-Anweisungen für solche großen Datenmengen optimieren? Wir betreiben derzeit RHEL als Betriebssystem und können leider unsere Hardware-Konfiguration auf dem Server nicht ändern (4 GB RAM, Quad Core). Wir verwenden C / C ++ und die MySQL API. Jegliche Geschwindigkeitsverbesserungen wären EXTREM vorteilhaft. Wenn Sie mich brauchen, um etwas zu klären, lassen Sie es mich bitte wissen. Danke!
EDIT: Übrigens, wenn Sie keine spezifische Hilfe anbieten können, können Sie mich vielleicht mit einigen exzellenten Tutorials verbinden, die Sie für die Optimierung von SQL-Abfragen, Schemadesign oder MySQL-Tuning gefunden haben?
Als Erstes könnten Sie versuchen, die Daten zu de-normalisieren. Bei einem Datensatz dieser Größe erfordert das Ausführen eines Joins selbst bei Indizes sehr intensive Berechnungen. Verwandle diese drei Tabellen in 1 Tabelle. Sicher, es wird doppelte Daten geben, aber ohne Joins wird es viel einfacher sein mit zu arbeiten. Zweitens, sehen Sie, ob Sie eine Maschine mit genügend Speicher bekommen können, um die ganze Tabelle in den Speicher zu passen. Es kostet nicht viel (1000 $ oder weniger) für eine Maschine mit 24 GB RAM. Ich bin mir nicht sicher, ob das Ihren gesamten Datensatz enthält, aber es wird enorm hilfreich sein. Holen Sie sich auch eine SSD. Für alles, was nicht im Speicher gespeichert ist, sollte eine SSD Ihnen helfen, mit hoher Geschwindigkeit darauf zuzugreifen. Und drittens, schauen Sie sich andere Datenspeichertechnologien wie BigTable an, die entwickelt wurden, um mit sehr großen Datenmengen umzugehen.
>Ich würde sagen, Partitionierung ist ein absolutes Muss in einem Fall wie diesem:
Lesen Sie dies für mySQL .
Ich schaue auf die select stmt (die Filter auf Zeit), werde ich Partition in der Zeitspalte sagen.
Natürlich möchten Sie vielleicht ein paar Indizes basierend auf den häufigen Abfragen hinzufügen, die Sie verwenden möchten.
- bearbeiten -
Ich sehe, dass viele Indizes vorgeschlagen haben. Meine Erfahrungen waren, dass ein Index für eine Tabelle mit einer wirklich großen Anzahl von Zeilen entweder die Performance (irgendwann) zunichte macht oder viele Ressourcen (CPU, Speicher, ...) benötigt, um die Indizes aktuell zu halten. Obwohl ich auch die Hinzufügung von Indizes vorschlage, beachten Sie bitte, dass es absolut nutzlos ist, wenn Sie die Tabelle nicht zuerst partitionieren. Schließlich folgen Sie den Anweisungen von symcbean (optimieren Sie Ihre Indizes in Zahl und Schlüssel), wenn Sie Indizes hinzufügen.
- Ende bearbeiten -
Ein Quickie auf Partitionierung, wenn Sie neu sind.
Ich habe nicht viel Erfahrung mit MySQL, aber hier sind einige a priori Gedanken, die mir in den Sinn kommen.
Wird in einer gespeicherten Prozedur ausgewählt?
Das Prädikat des Auswahlelements wird normalerweise in der Reihenfolge durchsucht, in der es abgefragt wurde. Wenn die Daten auf dem Datenträger neu angeordnet werden, damit sie mit dem Primärschlüssel übereinstimmen, ist die erste Ausführung der Raster-ID in Ordnung. Sie würden die Kosten für die Nachbestellung für jeden Einsatz bezahlen. Wenn die Daten in zeitlicher Reihenfolge auf der Festplatte gespeichert werden, sollten Sie wahrscheinlich nach time_sec vor raster_id suchen.
Ihre Indizes folgen nicht den Suchprädikaten.
Es wird in der Regel Indizes basierend auf den Schlüsseln erstellt.
%Vor% Der primäre Index wird möglicherweise nicht verwendet, weil Sie internal_id nicht verwenden. Möglicherweise möchten Sie internal_id als Primärschlüssel festlegen und basierend auf Ihren Suchparametern einen separaten Index erstellen. Zumindest am raster_id und time_sec .
Sind die Joins zu locker?
Dies mag meine Unerfahrenheit mit MySQL sein, aber ich erwarte Bedingungen für die Joins. Verwendet FROM hier einen natürlichen Join? Ich sehe keine Fremdschlüssel angegeben, daher weiß ich nicht, wie sie diese Tabellen rational verbinden würde.
%Vor%Wenn ich so etwas entwickle, würde ich normalerweise mit einem kleineren Satz arbeiten und Prädikate entfernen, um sicherzustellen, dass jeder Schritt dem entspricht, was ich erwarte. Wenn Sie aus Versehen ein weites Netz nach vorne werfen, dann können Sie später einige Ineffizienzen maskieren.
Die meisten Abfrageoptimierer haben eine Möglichkeit, wie das Optimierte auszugeben, stellen Sie sicher, dass es Ihren Erwartungen entspricht. Einer der Kommentare erwähnt Pläne zu erklären, ich nehme an, dass es so heißt.
Ohne zu wissen, um was für alle Abfragen es sich handelt, ist es schwierig, spezifische Ratschläge zu geben. Wenn Sie jedoch die einzelne Abfrage betrachten, die Sie zur Verfügung gestellt haben, gibt es keine Indizes, die sich dafür eignen.
Tatsächlich ist die Struktur ein bisschen unordentlich - wenn internal_id ein Auto-Inkrement-Wert ist, dann ist es einzigartig - warum fügen Sie andere Dinge in den Primärschlüssel ein? Es sieht so aus, als wäre eine sinnvollere Struktur für rel_RASTER_TABLE:
Und wie bei RAW_DATA_TABLE sollte es offensichtlich sein, dass seine Indizes weit vom Optimum entfernt sind. Und sollte wahrscheinlich sein:
%Vor%Beachten Sie, dass das Entfernen redundanter Indizes die Einfügung / Aktualisierung beschleunigt. Das Erfassen langsamer Abfragen sollte helfen - und lernen, wie man 'explain' verwendet, um zu sehen, welche Indizes redundant sind / benötigt werden.
Sie können auch eine Leistungssteigerung erzielen, indem Sie die mysql-Instanz optimieren - insbesondere die Sortier- und Join-Puffer erhöhen - und versuchen, mysqltuner auszuführen / p>
Zuerst würde ich versuchen, eine Ansicht mit nur den notwendigen Informationen zu erstellen, die zwischen den verschiedenen Tabellen ausgewählt werden müssen.
Übrigens ist MySQL nicht unbedingt das am besten optimierte Datenbanksystem für das, was Sie erreichen möchten ... Suchen Sie nach anderen Lösungen wie Oracle, Microsoft SQL, PostgreSQL usw. Auch die Leistung hängt vom Server ab verwendet.
Tags und Links c++ mysql performance