So verwendet MySQL Arbeitsspeicher
Ein häufiges Problem sind MySQL-Instanzen, die viel Arbeitsspeicher verbrauchen oder bei denen Probleme mit unzureichendem Arbeitsspeicher (OOM) auftreten. Eine Datenbankinstanz, die mit Arbeitsspeicherdruck ausgeführt wird, führt häufig zu Leistungsproblemen, Verzögerungen oder sogar Ausfallzeiten der Anwendung.
Bevor Sie Arbeitsspeicher für MySQL-Instanzen zuweisen, ist es wichtig zu verstehen, wie MySQL Arbeitsspeicher verwendet. In diesem Artikel werden Features von MySQL beschrieben, die Arbeitsspeicher verbrauchen. Dies führt häufig zu OOM-Problemen. Der Artikel erörtert auch, wie das vollständig verwaltete Angebot von Google Cloud, Cloud SQL for MySQL, für die Arbeitsspeicherverwaltung bereits eingerichtet ist.
Globale Puffer
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.
InnoDB-Pufferpools
Der InnoDB-Pufferpool ist in der Regel der größte Arbeitsspeicherverbraucher in einer MySQL-Instanz. Die Konfiguration erfolgt mit dem Parameter innodb_buffer_pool_size. 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 … |
InnoDB-Logpuffer
Der InnoDB-Logpuffer enthält die Änderungen, die in die InnoDB-Rückgängig-Logdateien auf dem Laufwerk geschrieben werden sollen. Die Konfiguration erfolgt mit innodb_log_buffer_size. Der Standardwert der MySQL-Community ist 16 MB und Cloud SQL for MySQL verwendet denselben Wert.
Schlüsselpuffergröße
Der Schlüsselpuffer wird von MySQL verwendet, um die MyISAM-Indexe im Arbeitsspeicher im Cache zu speichern. Er wird mit key_buffer_size konfiguriert. Der Standardwert der 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.
Abfrage-Cache-Größe
Die Konfigurationsvariable query_cache_size definiert die Menge des Arbeitsspeichers, der für das Caching von Abfrageergebnissen zugewiesen ist. Sie ist in MySQL-Community 5.7 und in Cloud SQL for MySQL 5.7 standardmäßig deaktiviert.
Der Abfragecache war für schwerwiegende Skalierbarkeitsprobleme bekannt. Er wurde daher in MySQL 5.7.20 verworfen 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.
Globale Caches
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.
Tabellencache
MySQL verwendet den Tabellen-Cache, 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.
Der table_open_cache ist ein Speichercache, in dem der Dateideskriptor der offenen Tabellen von allen 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. Sorgen Sie dafür, dass Ihr Betriebssystem die Anzahl der offenen 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 2.000 und 1.400 als Standard für table_open_cache und table_definition_cache.
Cloud SQL for MySQL 8.0 verwendet 4.000 und 2.000 als Standard für table_open_cache und table_definition_cache.
Thread-Cache
Für jede Clientverbindung weist MySQL einen dedizierten Thread zu, der alle Abfragen ausführt und das Ergebnis an den Client zurückgibt, bis der Client die Verbindung getrennt hat. 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.
Cloud SQL for MySQL verwendet 48 als Standard für thread_cache_size.
InnoDB-Datenwörterbuch-Cache
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? Das 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 belegen. 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 |
Sitzungszwischenspeicher
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.
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 verschwendetem Arbeitsspeicher führen.
Die Standardwerte sind in der MySQL-Community und in Cloud SQL for MySQL identisch.
Binärlog-Cache
MySQL verwendet den binären Log-Cache, um die Änderungen zu speichern, 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.
Temporäre Tabellen
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ärer Tabellen wird als Mindestwert der Variablen tmp_table_size und max_heap_table_size festgelegt.
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. Es wird empfohlen, diese Werte nicht zu hoch festzulegen. Experimentieren Sie, um den besten Wert für Ihre Arbeitslast zu finden.
Verbindungsspezifischer Arbeitsspeicher
Jeder Thread benötigt ein wenig Arbeitsspeicher, um die Clientverbindung zu verwalten. Die folgenden Variablen steuern gewisse Größen.
- thread_stack: Die Stackgröße für jeden Thread, 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.
Performance_schema
Wenn performance_schema aktiviert ist, hilft es, die Ausführung des MySQL-Servers auf niedriger Ebene zu beobachten. 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 die Versionen 5.6, 5.7 und 8.0.18 von MySQL standardmäßig deaktiviert und kann mit Datenbank-Flags aktiviert werden.
Fazit
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.
Ähnliche Produkte und Dienste
Google Cloud bietet eine verwaltete MySQL-Datenbank, die auf Ihre geschäftlichen Anforderungen zugeschnitten ist – von der Stilllegung Ihres lokalen Rechenzentrums über die Ausführung von SaaS-Anwendungen bis hin zur Migration von Kerngeschäftssystemen.
