Arbeitsspeichernutzung in MySQL

MySQL weist beim Start des Servers globale Puffer zu und diese werden von allen Verbindungen gemeinsam genutzt. Der Großteil des MySQL-Arbeitsspeichers wird von den globalen Puffern verbraucht, z.B. innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size.

Der InnoDB-Pufferpool ist in der Regel der größte Arbeitsspeicherverbraucher in einer MySQL-Instanz. Sie wird mit dem Parameter innodb_buffer_pool_size konfiguriert. Er wird verwendet, um die Tabellendaten und -indexe, den Änderungs-Puffer, den adaptiven Hash-Index und andere interne Strukturen im Cache zu speichern. Cloud SQL for MySQL, das verwaltete MySQL-Angebot von Google Cloud, konfiguriert innodb_buffer_pool_size auf bis zu 72% des Instanzarbeitsspeichers, je nach Größe der Instanz. 

InnoDB reserviert zusätzlichen Arbeits-Speicher für Puffer und die zugehörigen Datenstrukturen. Der gesamte zugewiesene Arbeitsspeicher ist etwa 10% größer als die angegebene Pufferpoolgröße. Sie können die Nutzung des InnoDB-Pufferpool-Arbeitsspeichers in der Ausgabe „show engine innodb status\G“ prüfen.

mysql> show engine innodb status\G

…

----------------------

PUFFER-POOL UND ARBEITSSPEICHER

----------------------

Insgesamt zugewiesener großer Arbeitsspeicher 11511349248

…

Der InnoDB-Logpuffer enthält die Änderungen, die in die InnoDB-Rückgängig-Logdateien auf dem Laufwerk geschrieben werden sollen. Sie wird mit innodb_log_buffer_size konfiguriert. Der Standardwert für die MySQL-Community ist 16 MB und Cloud SQL for MySQL verwendet denselben Wert.

Der Schlüsselzwischenspeicher wird von MySQL verwendet, um die MyISAM-Indexe im Arbeitsspeicher zwischenzuspeichern. Er wird mit key_buffer_size konfiguriert. Der Standardwert für die MySQL-Community ist 8 MB und Cloud SQL for MySQL verwendet denselben Wert. Cloud SQL for MySQL unterstützt keine MyISAM-Tabellen. Daher können Sie die Standardeinstellung beibehalten.

Die Konfigurationsvariable query_cache_size definiert die Speichermenge, die für das Caching von Abfrageergebnissen zugewiesen wird. Sie ist in MySQL-Community 5.7 und in Cloud SQL for MySQL 5.7 standardmäßig deaktiviert. 

Der Abfrage-Cache war für schwerwiegende Skalierbarkeitsprobleme bekannt. Daher wurde er in MySQL 5.7.20 eingestellt und in MySQL 8.0 entfernt. Wenn Sie ihn weiterhin für Ihre MySQL 5.7-Instanz verwenden, prüfen Sie, ob er für Ihre Arbeitslast wirklich hilfreich ist. Weitere Informationen finden Sie in diesem Blog. 

MySQL weist globale Caches zu, die von allen Verbindungen gemeinsam genutzt werden. Diese Caches werden dynamisch zugewiesen und die Konfigurationsvariablen definieren die maximale Limit für sie.

MySQL verwendet den Tabellencache, um das Öffnen von Tabellen zu beschleunigen. Der MySQL-Tabellen-Cache besteht aus zwei Teilen, einem Cache für offene Tabellen und einem Cache für Tabellendefinitionen, die mithilfe von table_open_cache bzw. table_definition_cache konfiguriert werden. 

„table_open_cache“ ist ein Speichercache, in dem die Dateibeschreibung der geöffneten Tabellen durch alle verbundenen Threads gespeichert wird. Durch Erhöhen dieses Werts wird die Anzahl der Dateideskriptoren erhöht, die für das mysqld-Programm, auch genannt MySQL-Server, erforderlich sind. Stellen Sie sicher, dass Ihr Betriebssystem die Anzahl der geöffneten Dateideskriptoren verarbeiten kann, die durch die Einstellung „table_open_cache“ impliziert werden.

Mehrere Clientsitzungen können gleichzeitig auf die angegebene Tabelle zugreifen und die Tabelle wird von jeder gleichzeitigen Clientsitzung einzeln geöffnet. Aus diesem Grund ist die Anzahl der geöffneten Tabellen möglicherweise höher als die Anzahl der Tabellen auf dem Server. Wenn der Tabellen-Cache voll ist, gibt der Server die derzeit nicht verwendeten Tabellen frei. Er beginnt dabei mit der am längsten nicht verwendeten Tabelle.

Der „table_definition_cache“ ist ein Speichercache zum Speichern der Tabellendefinitionen. Er ist global und wird von allen Verbindungen gemeinsam verwendet.

Cloud SQL for MySQL 5.7 verwendet 2000 und 1400 als Standardwerte für table_open_cache und table_definition_cache.

Cloud SQL for MySQL 8.0 verwendet 4.000 und 2.000 als Standardwerte für table_open_cache und table_definition_cache.

MySQL weist jeder Clientverbindung einen dedizierten Thread zu, der alle Abfragen ausführt und das Ergebnis an den Client zurückgibt, bis der Client die Verbindung trennt. MySQL speichert die Threads im Cache, sodass es nicht für jede Verbindung Threads erstellen und löschen muss. Die Anzahl der Threads im Thread-Cache wird mit der Variablen thread_cache_size konfiguriert.

CloudSQL for MySQL verwendet 48 als Standard für thread_cache_size.

InnoDB hat einen eigenen Cache zum Speichern von Tabellendefinitionen. Er unterscheidet sich vom table_open_cache und vom table_definition_cache. Sie können den für das InnoDB-Datenwörterbuch zugewiesenen Arbeits-Speicher in der Ausgabe "show engine innodb status\G" prüfen.

----------------------

PUFFER-POOL UND ARBEITSSPEICHER

----------------------

…

Wörterbuch-Arbeitsspeicher zugewiesen 65816817

Mit der Einstellung table_definition_cache wird ein weiches Limit für die Anzahl der Tabelleninstanzen im InnoDB-Datenwörterbuch-Cache festgelegt; wenn die Anzahl der Tabelleninstanzen im InnoDB-Datenwörterbuch-Cache das Limit table_definition_cache überschreitet, beginnt der LRU-Mechanismus, Tabelleninstanzen zur Bereinigung zu markieren und entfernt sie schließlich aus diesem Cache.

Bedeutet das, dass die Anzahl der Tabelleninstanzen im InnoDB-Datenwörterbuch-Cache immer kleiner sein wird als das Limit table_definition_cache? Dies ist nicht der Fall. Tabelleninstanzen mit Fremdschlüsselbeziehungen werden nicht in die LRU-Liste aufgenommen. Sie bleiben im Cache gespeichert und führen dazu, dass die Tabelleninstanzen über das Limit von table_definition_cache hinaus wachsen. Dies führt zu zusätzlicher Arbeits-Speichernutzung. Der Arbeitsspeicher, der von Tabellen mit Fremdschlüsselbeziehungen verbraucht wird, wird nur beim Herunterfahren/Neustarten von MySQL freigegeben. Dieses Problem tritt sowohl in MySQL 5.7 als auch 8.0 auf und ist daher ein bekannter bestätigter Programmfehler.

Wenn Ihre MySQL-Instanz eine große Anzahl von Tabellen mit Fremdschlüsselbeziehungen hat, kann der InnoDB-Datenwörterbuch-Cache mehrere GB Arbeitsspeicher verbrauchen. Beim Konfigurieren von MySQL-Puffern und -Caches wird das jedoch häufig nicht bedacht und kann einer der Gründe für unerwartete Probleme hinsichtlich hoher Arbeitsspeichernutzung oder unzureichendem Arbeitsspeicher sein.

Eine Beispielausgabe, die eine kleine Instanz zeigt, die 4,16 GB für den InnoDB-Datenwörterbuch-Cache verbraucht.

$ mysql-e "show engine innodb status\G" | grep -i memory

PUFFER-POOL UND ARBEITSSPEICHER

Insgesamt zugewiesener großer Arbeitsspeicher 7696023552

Zugewiesener Wörterbuch-Arbeitsspeicher 4465193358

Ein weiteres Feature von MySQL, das Arbeitsspeicher verbraucht, sind die Sitzungspuffer. Diese Puffer werden auf Sitzungsbasis zugewiesen. In einigen Fällen können mehrere Instanzen von ihnen für eine einzelne Abfrage zugewiesen werden, insbesondere join_buffer_size.

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

Diese Puffer werden nur zugewiesen, wenn sie von einer Abfrage benötigt werden (z. B. zum Sortieren, für Joins, für Index-/vollständige Tabellenscans), aber wenn sie benötigt werden, werden sie auch dann in Originalgröße zugewiesen wenn nur ein sehr kleiner Teil erforderlich ist. Das Festlegen dieser Puffer auf einen hohen Wert kann zu Arbeitsspeicherverschwendung führen.

Die Standardwerte sind in der MySQL-Community und in Cloud SQL for MySQL identisch.

MySQL verwendet den binären Log-Cache zum Speichern der Änderungen, die während der Ausführung einer Transaktion am binären Log vorgenommen wurden. Er wird mit binlog_cache_size konfiguriert. Er wird jedem Client zugeordnet, wenn binäres Logging aktiviert ist (log_bin=ON).

Der Standardwert für binlog_cache_size ist in der MySQL Community und in Cloud SQL for MySQL identisch.

MySQL erstellt interne temporäre Tabellen, um das Zwischenergebnis zu speichern, während einige Abfragetypen wie GROUP BY, ORDER BY, DISTINCT und UNION verarbeitet werden. Diese internen temporären Tabellen werden zuerst im Arbeitsspeicher erstellt und dann in Tabellen auf dem Laufwerk umgewandelt, wenn die maximale Größe erreicht ist. Die maximale Größe interner temporären Tabellen ist das Minimum der Variablen tmp_table_size und max_heap_table_size. 

Die Standardwerte für tmp_table_size und max_heap_table_size sind in der MySQL-Community und in Cloud SQL for MySQL identisch.

Hinweis: Da sitzungsspezifische Puffer und temporäre Tabellen im Arbeitsspeicher den einzelnen Verbindungen separat Arbeits-Speicher zuweisen, kann die Gesamtarbeitspeichernutzung sehr hoch sein, wenn eine große Zahl von Verbindungen sie brauchen. Wir empfehlen, diese Werte nicht zu hoch anzusetzen. Experimentieren Sie, um den besten Wert für Ihre Arbeitslast zu ermitteln.

Jeder Thread benötigt ein wenig Arbeitsspeicher, um die Clientverbindung zu verwalten. Die folgenden Variablen steuern gewisse Größen.

• thread_stack: Die Stapelgröße für jeden Thread beträgt standardmäßig 256 KB.
• net_buffer_length: Jeder Client ist einem Verbindungs- und Ergebnispuffer der Größe net_buffer_length zugeordnet. Dies kann bis zu einer Größe von max_allowed_packet weiter ansteigen.

Wenn performance_schema aktiviert ist, hilft dies bei der Überwachung der MySQL-Serverausführung auf niedriger Ebene. Performance_schema weist Arbeitsspeicher dynamisch zu und er wird nur beim Herunterfahren/Neustart von MySQL freigegeben.

Cloud SQL for MySQL ermöglicht die Aktivierung von performance_schema auf Instanzen mit einer RAM-Größe von mindestens 15 GB. Es ist standardmäßig aktiviert ab der MySQL 8.0.26-Version. Performance_schema ist für MySQL 5.6, 5.7 und 8.0.18 standardmäßig deaktiviert und kann mit Datenbank-Flags aktiviert werden.

Cloud SQL for MySQL konfiguriert automatisch arbeitsspeicherbezogene Parameter für eine gute sofort verfügbare Leistung. Es können dennoch unzureichender-Arbeitsspeicher-Probleme für Ihre Instanz auftreten, wenn für die Arbeitslast größere Caches erforderlich sind, um eine große Anzahl von Tabellen und/oder Verbindungen zu unterstützen. Wenn viele Threads Sitzungspuffer gleichzeitig zuweisen, kann das ebenfalls zu arbeitsspeicherbezogenen Problemen führen. Je mehr Arbeitsspeicher für globale Puffer/Caches zugewiesen ist, desto weniger Arbeitsspeicher ist für Verbindungen, Sitzungspuffer verfügbar und umgekehrt. Eine gute Balance zu finden ist hier der Schlüssel.

Wenn Ihre Arbeitslast größere Caches/Sitzungspuffer erfordert, können Sie die Größe des globalen Puffers mit innodb_buffer_pool_size verringern. Sie können die Konfigurationsparameter Ihrer Cloud SQL for MySQL-Instanzen mithilfe von Datenbank-Flags ändern. Wenn weiterhin Probleme mit unzureichendem Arbeitsspeicher oder der Leistung auftreten, können Sie ein Upgrade der Instanzgröße durchführen, um den Arbeitsspeicher zu erhöhen.