MySQL stürzt ab, wenn ich den Wert max_heap_table_size erhöhe

Question 1

Bei 120 GB Arbeitsspeicher scheint es möglich, dass Ihr Computer auch über mehrere physische CPUs und eine NUMA-Architektur (Non-Uniform Memory Access) verfügt. Wenn dies der Fall ist, verfügen Sie möglicherweise über reichlich verfügbaren Arbeitsspeicher, aber dennoch, entgegen Ihrer Intuition, nicht über genügend verfügbaren Arbeitsspeicher.

Wenn ich bis hierhin richtig liege, könnte dieses Problem auftreten, wenn MySQL versucht, den der MEMORYTabelle zugewiesenen Speicher zu vergrößern, und dabei auf einen Fehler stößt, der mit der Art und Weise zusammenhängt, wie Linux bei dieser Architektur mit Speicherzuweisungen umgeht. Dabei wird nur der verfügbare Speicher auf den Speichersticks berücksichtigt, die direkt mit einer bestimmten CPU verbunden sind, die etwas willkürlich als „diejenige“ ausgewählt wurde, von der Speicheranforderungen bedient werden – obwohl anderer physischer Speicher anderswo auf der Hauptplatine frei ist.

NUMA ist in der Theorie gut, aber möglicherweise nicht ideal für einzelne Prozesse geeignet, die große Mengen an Speicher benötigen, wie es bei MySQL der Fall ist … aber es gibt einen Workaround.

Die Lösung besteht darin, das mysqld_safeSkript durch Hinzufügen dieser Zeile zu ändern:

cmd="/usr/bin/numactl --interleave all $cmd"

...sofortnachdiese Linie...

cmd="$NOHUP_NICENESS"

Die Gründe dafür werden hier in einem ausgezeichneten Artikel erläutert, der ursprünglich geschrieben wurde, um zu erklären, warum MySQL-Server mit dieser Architektur trotz freiem Speicher stark swappten; das war jedoch ein Symptom eines größeren Problems, das der Autoranschließendwies darauf hin, dass es sich „nicht ausschließlich um ein Swapping-Problem“ handele, das, selbst bei deaktiviertem Swap, auch „fehlgeschlagene Speicherzuweisungen“ umfassen könne.

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Ich spekuliere natürlich, ob dies auf Ihr System zutrifft, aber es scheint eine lohnende Möglichkeit zu sein. Ich habe genau diesen Fix verwendet, als ich auf einem neuen 128-GB-Rechner auf ein Problem stieß, als ich versuchte, einen 64-GB-InnoDB-Pufferpool bereitzustellen, und MySQL keine 64 GB frei auf einem Rechner mitNichtssonst läuft. Als ich erkannte, dass der höchste Wert, den ich erfolgreich verwenden konnte, etwas weniger als 1/4 des gesamten Speichers in einer 16-Kern-Maschine mit 4 warphysischProzessoren … ungefähr so, wie Sie es anscheinend tun … das war, als ich die Puzzleteile zusammenfügte und die Natur des Problems herausfand.

Answer

Bei 120 GB Arbeitsspeicher scheint es möglich, dass Ihr Computer auch über mehrere physische CPUs und eine NUMA-Architektur (Non-Uniform Memory Access) verfügt. Wenn dies der Fall ist, verfügen Sie möglicherweise über reichlich verfügbaren Arbeitsspeicher, aber dennoch, entgegen Ihrer Intuition, nicht über genügend verfügbaren Arbeitsspeicher.

Wenn ich bis hierhin richtig liege, könnte dieses Problem auftreten, wenn MySQL versucht, den der MEMORYTabelle zugewiesenen Speicher zu vergrößern, und dabei auf einen Fehler stößt, der mit der Art und Weise zusammenhängt, wie Linux bei dieser Architektur mit Speicherzuweisungen umgeht. Dabei wird nur der verfügbare Speicher auf den Speichersticks berücksichtigt, die direkt mit einer bestimmten CPU verbunden sind, die etwas willkürlich als „diejenige“ ausgewählt wurde, von der Speicheranforderungen bedient werden – obwohl anderer physischer Speicher anderswo auf der Hauptplatine frei ist.

NUMA ist in der Theorie gut, aber möglicherweise nicht ideal für einzelne Prozesse geeignet, die große Mengen an Speicher benötigen, wie es bei MySQL der Fall ist … aber es gibt einen Workaround.

Die Lösung besteht darin, das mysqld_safeSkript durch Hinzufügen dieser Zeile zu ändern:

cmd="/usr/bin/numactl --interleave all $cmd"

...sofortnachdiese Linie...

cmd="$NOHUP_NICENESS"

Die Gründe dafür werden hier in einem ausgezeichneten Artikel erläutert, der ursprünglich geschrieben wurde, um zu erklären, warum MySQL-Server mit dieser Architektur trotz freiem Speicher stark swappten; das war jedoch ein Symptom eines größeren Problems, das der Autoranschließendwies darauf hin, dass es sich „nicht ausschließlich um ein Swapping-Problem“ handele, das, selbst bei deaktiviertem Swap, auch „fehlgeschlagene Speicherzuweisungen“ umfassen könne.

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Ich spekuliere natürlich, ob dies auf Ihr System zutrifft, aber es scheint eine lohnende Möglichkeit zu sein. Ich habe genau diesen Fix verwendet, als ich auf einem neuen 128-GB-Rechner auf ein Problem stieß, als ich versuchte, einen 64-GB-InnoDB-Pufferpool bereitzustellen, und MySQL keine 64 GB frei auf einem Rechner mitNichtssonst läuft. Als ich erkannte, dass der höchste Wert, den ich erfolgreich verwenden konnte, etwas weniger als 1/4 des gesamten Speichers in einer 16-Kern-Maschine mit 4 warphysischProzessoren … ungefähr so, wie Sie es anscheinend tun … das war, als ich die Puzzleteile zusammenfügte und die Natur des Problems herausfand.

Question 2

Die Dokumentation fürtemporäre_Tabellengrößebesagt, dass diese Einstellung die maximale Größe einer temporären Tabelle istin Erinnerung. Das Überschreiten dieser Größe löst keine „Tabelle ist voll“-Fehler aus, sondern führt zu einem Wechsel von der Verwendung der Tabelle im Speicher zur Verwendung einer MyISAM-Tabelle auf der Festplatte.

Das System stürzt ab, weil Sie zulassen, dass temporäre Tabellen viel mehr Speicher verbrauchen als nötig. Dasselbe gilt für die Speichertabellen.

Sie sollten die tmp_table_size löschenWegdown. Denken Sie daran, dass diese Einstellung kein Gesamtmaximum ist. Es ist ein Maximum pro temporärer Tabelle. Wenn Sie 5 Abfragen haben, die eine enorm große temporäre Tabelle erstellen (sagen wir, jede davon knapp 20 GB), haben Sie jetzt temporäre Tabellen, die 100 GB Ihres RAM verbrauchen. Fügen Sie eine 6. hinzu, und Sie haben mehr RAM verbraucht, als der Server insgesamt hat.

Wenn Ihre Skripte tatsächlich die Speicher-Engine MEMORY verwenden, sollten Sie das ändern, nachdem Sie die Daten in eine MyISAM-Datei geschrieben haben. Wenn diese schnelle Leistung für eine so große temporäre Tabelle wirklich erforderlich ist, sollten Sie sich einen schnelleren Speicher zulegen (z. B.Fusion-io,Viridentet al.). Wenn diese übertrieben oder zu teuer sind, würde ich zumindest SSDs für den Verbraucherbereich in Betracht ziehen.

MyISAM funktioniert am besten, wenn seine Daten im Systemspeicher (ungenutzter RAM) zwischengespeichert werden. Wenn Ihre Skripte den Cache jedes Mal leeren, wenn sie eine große Abfrage durchführen (mit der MEMORY-Engine), wird die Leistung von MyISAM darunter leiden.

Ich vermute, dass das Skript die MEMORY-Speicher-Engine verwendet, um die Leistung zu „verbessern“, während es sie tatsächlich beendet. Wenn es sich um eine temporäre Tabelle handelt, sollte es temporäre Tabellen verwenden und Ihre tmp_table_size sollte etwas viel kleiner sein, sodass es auf die Festplatte gezwungen wird, nachdem es tmp_table_size überschreitet.

Die Standardwerte für max_heap_table_size und tmp_table_size betragen 16 MB. Wenn möglich, würde ich vorschlagen, die Einstellungen wieder auf die Standardwerte zurückzusetzen. Passen Sie die Werte in kleinen Schritten nach oben an, während Sie die Ressourcennutzung (Festplatten-E/A, Gesamtspeichernutzung, CPU-Auslastung usw.) überwachen, bis Sie Einstellungen finden, die für Ihren Datensatz geeignet sind.

Answer