Spezifischen MySQL-bulk-Einfügung performance-tuning
Ich weiß, diese Frage wurde gebeten, über und über. Dies ist jedoch eine sehr spezifische Frage für ein sehr spezifischen Szenario. Hoffentlich werden Sie in der Lage, mir zu helfen.
Betreibe ich eine Datenbank für die Protokollierung, mit über 10 Tabellen. Die Haupt-Tabelle, welche den aktuellen log-Einträge hat etwa 30 Felder, von denen 5 durchsucht werden. Ich würde sagen, die Datenbank ist seit kurzem mittlerer Größe, wie erreichen wir 200 Millionen Einträge in dieser Tabelle. Andere Tabellen speichern gemeinsamer Daten, von denen der größte hat 4 Felder, die alle durchsucht werden, ist mit fast 1 million Einträge. Alle anderen Tabellen enthalten weniger als 100 tausend Datensätze, die jeweils.
Einsätze kommen in spikes. Ich bekomme die logs vom Vortag in (ganz schlecht formatierte csv-Dateien jeden Tag um 2 UHR, und ich habe bis 8 UHR und legen Sie (etwa 20 Dateien, 100 tausend Zeilen jeder) in die Datenbank. Dann bekomme ich nur sehr wenige wählt (vielleicht einige 1000 pro Tag) während der Arbeitszeit. Dann Spülen und wiederholen.
Den SELECT-Abfragen sind Recht einfach, denn Sie bestehen meist aus einem oder zwei joins mit einer oder zwei Gruppen VON Aussagen. Die Menschen, die suchen Sie in dieser Datenbank wollen sofortige Ergebnisse, so habe ich 5 multi-Spalten-Indizes in der Haupttabelle, die helfen, die genauen Recherchen, die ich habe, und derzeit WÄHLEN Sie Leistung ist sehr gut. Keine Abfrage hat mehr als 0,1 Sekunden so weit. Es gibt einige Berichte, aber diese dauert etwa 10 Sekunden, zu erzeugen, und das ist akzeptabel.
Derzeit habe ich ein C Programm, das ich schrieb zu Lesen der Daten aus der CSV-Dateien, reinigen Sie es, und legen Sie Sie in Chargen von 1000 Zeilen pro INSERT-Abfrage. Diese Einsätze sind nicht ganz dumm, weil ich auf die gemeinsamen Daten zu sehen, wenn er schon auf die anderen Tische, legen Sie es, wenn es nicht, und cache, wenn es ist. Es gibt mir auch die performance-Daten in form von, wie viele Datensätze es einsetzen pro Sekunde. Dieses Programm ist sehr schnell, und ohne das senden der Daten in der Datenbank, erhalte ich rund 100 tausend Zeilen pro Sekunde. Natürlich, dieses Programm und die Datenbank liegen auf dem selben physischen Rechner.
Nun, die Daten, die ich bekommen jeden Tag wächst linearily, und die Leistung der Einlagen sinkt logarithmisch. Die gestrigen Daten dauerte 5 Stunden und eine Hälfte zu legen, auf etwa 400 Zeilen-Einträge pro Sekunde.
Habe ich einige benchmark-Daten durch einfügen der ersten 1 million Zeilen mit unterschiedlichen Konfigurationen in eine leere Datenbank, und das ist ziemlich viel, was ich habe:
MyISAM-Tabellen: beginnt bei 1500 Zeilen pro Sekunde, verringert sich logarithmisch auf rund 700 Zeilen, die pro Sekunde durch die Zeit, es wird die 1-millionste Zeile
InnoDB-Tabellen: wie MyISAM, nur rund 100 Zeilen pro Sekunde schneller
InnoDB mit allen Indizes deaktiviert, auf die Haupt-Tabelle: beginnt bei 2100 Zeilen pro Sekunde, sinkt bis auf 1000 Zeilen pro Sekunde.
InnoDB Indizes, die mit dem Dateisystem montiert mit Rückschreiben von Daten (ext3): das gleiche wie InnoDB, nur leicht aber fast unmerklich schneller.
innodb_buffer_pool_size ist festgelegt 1000MB
Vermeidung von index-Erstellung keine option, aber es ist offensichtlich, dass es eine große Wirkung in der Leistung. Allerdings brauche ich viel schneller Einsätze. Wie die Daten zeigen, dass Einsätze länger dauern, da die Datenbank wächst, so wie die Daten, die ich bekomme, ist jeden Tag größer, ich brauche einen gewaltigen Sprung in der performance von Gewindeeinsätzen. Wenn ich könnte es 10000 inserts pro Sekunde oder mehr sind, wäre es wirklich toll.
System monitor sagt mir meine wichtigste Ressource Verbrauch ist die disk-I/O, die geht fast bis zu 100% beim einsetzen. Weil das so ist, brauche ich eine extrem schnelle Möglichkeit zum einfügen von Daten. Meine theoretische Grenze ist, dass der SATA-bus, aber das ist noch ziemlich weit Weg. Die Speicherauslastung scheint es nicht zu sein, dass hoch bei 20% (oder MySQL ist nicht mit Speicher korrekt)
Um dies zu erreichen, ist es akzeptabel, zu erstellen Sie die Datenbank, die im Laufe von mehreren Tagen, und dann hot-swap aus der reader-Anwendung, ist es akzeptabel, zum ändern einer Einstellung im OS und MySQL, ist es akzeptabel, um Arbeitsspeicher zu erweitern, wenn erforderlich. Es ist auch akzeptabel, ändern Sie die Struktur der Datenbank, wenn nötig.
So, ich bin wirklich offen für Ideen hier. Niemand weiß von irgendetwas, was mir helfen könnte?
Edit: ich bin derzeit daran, das einfügen der neuen Zeilen in eine Tabelle im SPEICHER, und führen Sie dann ein WÄHLEN Sie IN der realen Tabelle. Hoffentlich, es wird nur ein update und Spülen Sie den index, nachdem alle Zeilen eingefügt wurden. Ich werde versuchen, dies am Montag. Hat jemand versucht, so etwas wie dies vor?
Du musst angemeldet sein, um einen Kommentar abzugeben.
2 Millionen Zeilen, die in 6,5 Stunden?
Wie groß ist der Datensatz, den Sie speichern?
Verwende ich die folgenden back-of-the-envelope Berechnung kommt man zu einer etwas nützlich Abbildung:
Vorausgesetzt
1
einzige beschissene Festplatte, die schluckt35
mb pro Sekunde, Sie sollten in der Lage sein zu schreiben (35 * 6,5 * 3600) = ~800 gb
in diesem Zeitrahmen. Die Berechnung rückwärts (800 gb /2 mrows), gibt eine Durchschnittliche Zeilengröße400
kb.Wenn diese zahlen scheinen über die Rechte, die Sie brauchen, um Rindfleisch-up Ihrer hardware, um die Geschwindigkeit zu erhöhen. Wenn Sie sind völlig aus, es ist wahrscheinlich ein anderes problem.
Außerdem haben Sie einen Blick auf Vergleiche der disk-i/o für einen dedizierten MySQL-server auf ServerFault, für eine Art des Messens I/O.
Hier sind einige zufällige Vorschläge (falls Sie vermuten, ist ein anderes problem)
Bearbeitet
Korrigierte Berechnung (400 Kb)
Nach einem ganzen Tag zu tun, viele kleine Dinge, die ich baute eine riesige Sache. Die Quintessenz ist, dass ich verbesserte meine Einfügung Leistung um das 8-fache, auf fast 10000 Datensätze pro Sekunde.
Hier sind die Dinge, die ich habe:
Schreiben Sie die laden-Programm. Ich sagte, es war in C, aber es war eigentlich in C++. Ändern von string zu char*, fstream sind mit mmap, und andere Dinge wie, dass ich fast die doppelte Leistung. (Und viele Leute immer noch behaupten, C++ ist genauso schnell oder schneller als C würde ich noch nicht einmal versuchen wollen, diese in C#/Java)
Fand ich diese Seite:
http://kevin.vanzonneveld.net/techblog/article/improve_mysql_insert_performance/
Dies ist eine großartige Ressource (ich bin auch keine Verbindung mit Ihnen), dass erklärt so ziemlich alles, was ich im Begriff war zu versuchen, mit all den verschiedenen Ergebnissen. So ziemlich, die einzige Sache, die ultraboost einlegen Leistung ist mit LOAD DATA INFILE. Tweaking meine Tabelle übernommen, so konnte ich einfügen, wie dieser fast vervierfacht! die Leistung meiner Einfügungen.
Schrieb ich die Einfügungen, die nicht durchgeführt werden können, mit LOAD DATA INFILE, um Masse-Einsätze (mehrere Zeilen per insert-Befehl) unter Verwendung komplexer Ausdrücke innerhalb des ON DUPLICATE KEY UPDATE, anstatt das zu tun, WÄHLEN Sie EINFÜGEN/für jede Zeile. Dieser hielt auch eine sehr gute Leistung zu steigern. Das erforderte auch einige änderungen an den Tabellenstrukturen.
Beim Neuerstellen der Datenbank, die ging schon über 2 Milliarden Zeilen, die Tabellen erstellen, die die LOAD DATA INFILE-Insertionen ohne index, und erstellen Sie, wenn Sie fertig sind. Alle meine benchmarks zeigten, dass die Zeit zu legen, ohne Indizes, plus die Zeit, um Sie zu erstellen ist kürzer als die Zeit, die zum einfügen in Tabellen mit Indizes. Der Unterschied ist nicht riesig, aber es ist spürbar (rund 1.2-mal schneller). Ich nehme an der B-Bäume werden auch besser ausbalanciert ist in dieser Weise.
Verwendung Von MyISAM. Meine bisherigen benchmarks waren nicht schlüssig, aber bei der Verwendung von LOAD DATA INFILE, InnoDB verliert jedes mal. Testen lokal, bekam ich rund 16000 Datensätze/s in MyISAM/keine Indizes, 12000 Datensätze/s in MyISAM/Indizes, 9000 Datensätze/s in InnoDB/keine Indizes, und rund 7500 InnoDB/Indizes. Die MySQL-version 5.1.47.
Für die Dateien, für LOAD DATA INFILE, legen Sie Sie in ein tmpfs gemountet partition. Dies ist auch einen enormen performance-Schub, vor allem da Sie schreiben müssen, die eine Datei schreiben und Spülen Sie es auf der Festplatte, so kann MySQL dann Lesen Sie es. Wenn das tmpfs nicht möglich ist, sollte es möglich sein, dies zu tun, die named pipes verwenden.
Lektion gelernt: Wenn MySQL langsam ist, ist es sehr wahrscheinlich, dass Sie mehr tun können, indem Sie Ihre code, als durch die immer leistungsfähigere hardware.
Glück, und ich danke Ihnen allen für Ihre Hilfe.
Schlagen Sie es bei der Nennung von disk-I/O. Wenn die Festplatte ausgereizt wird mit Einlagen, man wird nicht schneller, es sei denn, Sie aktualisieren. Sie hat nicht erwähnt, wenn es akzeptabel war zu tun, Festplatten-upgrades, aber ich würde schauen mit SCSI-oder flash-basierte Festplatten. Obwohl Sie nicht schlagen SATA-bus-limit, deine Festplatte ist definitiv Engpässen.
Ich würde versuchen, erhöhen Sie die innodb-Puffer-pool-Größe, und sehen, was passiert. Für Innodb, würde ich auch deaktivieren Sie die permanente Spülung mit innodb_flush_log_at_trx_commit=0 (oder =2). Standardeinstellung ist 1, das ist ein Flaschenhals für schreibintensive workloads. 0 oder 2 geben Sie 1 Sekunde Verzögerung zwischen den Leerungen. Sie können auch größere Chargen mit Transaktionen (wenn Sie nicht ausdrücklich die Verwendung von Transaktionen, dann ist jeder Einsatz ist eigene Transaktion).
Wie bereits erwähnt, pre-Sortierung Eingang (durch Primärschlüssel) könnte helfen, reduzieren die Menge der Daten in den bufferpool durch die Beseitigung Zufall in die Seite geladen ist.
Alle der oben genannten ist innodb-verwandt.