Freigeben über

Behandeln von Problemen mit nicht genügend Arbeitsspeicher in Azure Database for MySQL – Flexibler Server

  • Artikel

Damit eine Instanz von Azure Database for MySQL – Flexibler Server optimale Leistung erbringt, ist eine geeignete Speicherbelegung und -auslastung von hoher Bedeutung. Beim Erstellen einer Instanz von Azure Database for MySQL – Flexibler Server hängt der verfügbare physische Speicher standardmäßig von der Dienstebene und Größe ab, die Sie für Ihre Workload auswählen. Darüber hinaus wird Arbeitsspeicher für Puffer und Caches zugewiesen, um Datenbankvorgänge zu verbessern. Weitere Informationen finden Sie im Artikel zur Verwendung des Arbeitsspeichers durch MySQL.

Azure Database for MySQL – Flexibler Server verwendet Arbeitsspeicher, um so viele Cachetreffer wie möglich zu erzielen. Die Speicherbelegung kann daher oft zwischen 80 und 90 % des verfügbaren physischen Speichers einer Instanz zeigen. Solange es kein Problem mit der fortschreitenden Verarbeitung der Abfrageworkload gibt, ist dies kein Problem. Es können jedoch aus folgenden Gründen Probleme mit nicht genügend Arbeitsspeicher auftreten:

  • Sie haben zu große Puffer konfiguriert.
  • Es werden suboptimale Abfragen ausgeführt.
  • Abfragen führen Join-Vorgänge aus und sortieren große Datasets.
  • Die maximale Anzahl von Verbindungen für einen Datenbankserver ist zu hoch festgelegt.

Ein Großteil des Arbeitsspeichers eines Servers wird von den globalen Puffern und Caches von InnoDB verwendet, die Komponenten wie innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size und query_cache_size umfassen.

Der Wert des innodb_buffer_pool_size-Parameters gibt den Arbeitsspeicherbereich an, in dem InnoDB die Datenbanktabellen und indexbezogene Daten zwischengespeichert. MySQL versucht, so viele Tabellen- und Indexdaten wie möglich im Pufferpool unterzubringen. Bei einem größeren Pufferpool müssen weniger E/A-Vorgänge auf Datenträger ausgelagert werden.

Überwachen der Arbeitsspeichernutzung

Azure Database for MySQL – Flexibler Server stellt eine Reihe von Metriken bereit, mit denen die Leistung Ihrer Datenbankinstanz gemessen werden kann. Um die Speicherauslastung für Ihren Datenbankserver besser zu verstehen, sind die Metriken für die prozentuale Hostspeicherauslastung oder die prozentuale Arbeitsspeicherauslastung hilfreich.

Screenshot von „Metriken zur Speicherauslastung anzeigen“.

Wenn die Speicherauslastung plötzlich steigt und der verfügbare Arbeitsspeicher schnell abnimmt, sollten Sie andere Metriken wie die prozentuale Host-CPU-Auslastung, die Gesamtanzahl der Verbindungen und die prozentuale E/A-Auslastung überwachen, um festzustellen, ob ein plötzlicher Anstieg der Workload die Ursache des Problems ist.

Es ist muss unbedingt beachtet werden, dass für jede eingerichtete Verbindung mit dem Datenbankserver ein gewisser Arbeitsspeicher zugewiesen werden muss. Daher kann ein Anstieg der Datenbankverbindungen zu Arbeitsspeicherengpässen führen.

Ursachen für hohe Arbeitsspeicherauslastung

Sehen wir uns einige weitere Ursachen für eine hohe Arbeitsspeicherauslastung in MySQL an. Diese Ursachen hängen von den Eigenschaften der Workload ab.

Erhöhung der Anzahl temporärer Tabellen

MySQL verwendet „temporäre Tabellen“. Dabei handelt es sich um eine spezielle Art von Tabelle zum Speichern temporärer Ergebnisse. Temporäre Tabellen können während einer Sitzung mehrmals wiederverwendet werden. Da alle temporären Tabellen für eine Sitzung lokal sind, können verschiedene Sitzungen andere temporäre Tabellen verwenden. In Produktionssystemen mit vielen Sitzungen, die Kompilierungen für umfangreiche temporäre Ergebnisse ausführen, sollten Sie regelmäßig den globalen Statuszähler created_tmp_tables überprüfen, der die Anzahl der temporären Tabellen nachverfolgt, die in Spitzenlastzeiten erstellt werden. Eine Vielzahl von temporären Tabellen im Arbeitsspeicher kann schnell zu geringem verfügbaren Speicherplatz in einer Instanz von Azure Database for MySQL – Flexibler Server führen.

Bei MySQL wird die Größe temporärer Tabellen durch die Werte von zwei Parametern bestimmt (siehe folgende Tabelle).

Parameter Beschreibung
tmp_table_size Gibt die maximale Größe interner temporärer Tabellen im Arbeitsspeicher an.
max_heap_table_size Gibt die maximale Größe an, auf die von Benutzern erstellte MEMORY-Tabellen wachsen können.

Hinweis

Bei der Bestimmung der maximalen Größe einer internen temporären Tabelle im Arbeitsspeicher beachtet MySQL den kleineren Wert, der für die Parameter tmp_table_size und max_heap_table_size festgelegt ist.

Empfehlungen

Um Probleme mit nicht genügendem Arbeitsspeicher im Zusammenhang mit temporären Tabellen zu beheben, sollten Sie die folgenden Empfehlungen berücksichtigen.

  • Vergewissern Sie sich vor dem Erhöhen des tmp_table_size-Werts, dass Ihre Datenbank ordnungsgemäß indiziert ist, insbesondere für Spalten, die an Join-Vorgängen beteiligt sind und von Vorgängen gruppiert werden. Durch Verwendung der entsprechenden Indizes für zugrunde liegende Tabellen wird die Anzahl der temporären Tabellen beschränkt, die erstellt werden. Wenn Sie den Wert dieses Parameters und des max_heap_table_size-Parameters erhöhen, ohne die Indizes zu überprüfen, können ineffiziente Abfragen ohne Indizes ausgeführt und mehr temporäre Tabellen als erforderlich erstellt werden.
  • Optimieren Sie die Werte der Parameter max_heap_table_size und tmp_table_size, um die Anforderungen Ihrer Workload zu berücksichtigen.
  • Wenn die Werte, die Sie für die Parameter „max_heap_table_size“ und „tmp_table_size“ festlegen, zu klein sind, können temporäre Tabellen regelmäßig auf Datenträgerspeicher überlaufen, was Wartezeit zu Ihren Abfragen hinzufügt. Mit dem globalen Statuszähler created_tmp_disk_tables können Sie die Verlagerung temporärer Tabellen auf Datenträger nachverfolgen. Durch einen Vergleich der Werte der Variablen created_tmp_disk_tables und created_tmp_tables können Sie die Anzahl der erstellten internen temporären Tabellen auf Datenträger in Bezug auf die Gesamtzahl der erstellten internen temporären Tabellen ermitteln.

Tabellencache

Als Multithreadsystem verwaltet MySQL einen Cache von Tabellendateideskriptoren, sodass die Tabellen gleichzeitig von mehreren Sitzungen unabhängig geöffnet werden können. MySQL verwendet einige Arbeitsspeicher- und Betriebssystemdateideskriptoren, um diesen Tabellencache zu verwalten. Die table_open_cache-Variable definiert die Größe des Tabellencaches.

Empfehlungen

Um Probleme mit nicht genügendem Arbeitsspeicher im Zusammenhang mit dem Tabellencache zu beheben, sollten Sie die folgenden Empfehlungen berücksichtigen.

  • Der Parameter table_open_cache gibt die Anzahl der geöffneten Tabellen für alle Threads an. Durch Erhöhung dieses Werts wächst die Anzahl der Dateideskriptoren, die mysqld benötigt. Sie können überprüfen, ob Sie den Tabellencache vergrößern müssen, indem Sie die Statusvariable opened_tables im SHOW GLOBAL STATUS-Zähler überprüfen. Erhöhen Sie den Wert dieses Parameters schrittweise, um Ihre Workload zu bewältigen.
  • Das Festlegen eines zu tiefen Werts für „table_open_cache“ kann dazu führen, dass Azure Database for MySQL – Flexibler Server mehr Zeit zum Öffnen und Schließen von Tabellen benötigt, die für die Abfrageverarbeitung erforderlich sind.
  • Das Festlegen dieses Werts auf einen zu hohen Wert kann dazu führen, dass mehr Arbeitsspeicher verbraucht wird und das Betriebssystem Dateideskriptoren ausführt, sodass Verbindungen abgelehnt werde oder Abfragen nicht verarbeitet werden können.

Andere Puffer und der Abfragecache

Bei der Behandlung von Problemen mit ungenügendem Arbeitsspeicher kann die Verwendung von einigen Puffern mehr und einem Cache hilfreich sein.

Nettopuffer (net_buffer_length)

Der Nettopuffergröße setzt sich aus dem Verbindungs- und Threadpuffer für jeden Clientthread zusammen und kann auf den für max_allowed_packet angegebenen Wert wachsen. Bei einer umfangreichen Abfrageanweisung, wenn z. B. alle Einfügungen/Aktualisierungen sehr umfangreich sind, kann das Erhöhen des Werts des net_buffer_length-Parameters hilfreich sein, um die Leistung zu verbessern.

Join-Puffer (join_buffer_size)

Der Join-Puffer wird zum Zwischenspeichern von Tabellenzeilen zugewiesen, wenn ein Join-Vorgang keinen Index verwenden kann. Wenn Ihre Datenbank viele Join-Vorgänge ohne Indizes ausgeführt hat, sollten Sie Indizes für schnellere Join-Vorgänge hinzufügen. Wenn Sie keine Indizes hinzufügen können, sollten Sie den Wert des Parameters „join_buffer_size“ erhöhen, der die Größe des Arbeitsspeichers pro Verbindung angibt.

Sortierpuffer (sort_buffer_size)

Der Sortierpuffer wird zum Ausführen von Sortiervorgängen für einige ORDER BY- und GROUP BY-Abfragen verwendet. Wenn viele „Sort_merge_passes“ pro Sekunde in der Ausgabe „SHOW GLOBAL STATUS“ angezeigt werden, sollten Sie den Wert für „sort_buffer_size“ erhöhen, um ORDER BY- oder GROUP BY-Vorgänge zu beschleunigen, die nicht mithilfe der Abfrageoptimierung oder einer besseren Indizierung verbessert werden können.

Vermeiden Sie eine willkürliche Erhöhung des sort_buffer_size-Werts, sofern keine Informationen vorliegen, dass dies notwendig ist. Der Arbeitsspeicher für diesen Puffer wird pro Verbindung zugewiesen. In der MySQL-Dokumentation weist der Artikel zu Serversystemvariablen darauf hin, dass es unter Linux zwei Schwellenwerte (256 KB und 2 MB) gibt und die Verwendung größerer Werte die Speicherbelegung erheblich verlangsamen kann. Vermeiden Sie daher, den sort_buffer_size-Wert über 2 MB hinaus zu erhöhen, da die geringere Leistung alle Vorteile zunichte macht.

Abfragecache (query_cache_size)

Der Abfragecache ist ein Arbeitsspeicherbereich, der für die Zwischenspeicherung von Abfrageergebnissen verwendet wird. Der Parameter query_cache_size bestimmt die Größe des Arbeitsspeichers, der für die Zwischenspeicherung von Abfrageergebnissen zugewiesen wird. Der Abfragecache ist standardmäßig deaktiviert. Darüber hinaus ist der Abfragecache in MySQL-Version 5.7.20 veraltet und in MySQL-Version 8.0 nicht mehr vorhanden. Wenn der Abfragecache derzeit in Ihrer Lösung aktiviert ist, vergewissern Sie sich vor der Deaktivierung, dass keine Abfragen vorhanden sind, die ihn benötigen.

Berechnen der Puffercache-Trefferquote

Die Puffercache-Trefferquote ist in der Umgebung von Azure Database for MySQL – Flexibler Server wichtig, um zu verstehen, ob der Pufferpool die Workloadanforderungen erfüllen kann oder nicht. Als allgemeine Faustregel wird empfohlen, dass die Puffercache-Trefferquote immer größer als 99 % ist.

Um die Trefferquote des InnoDB-Pufferpools für Leseanforderungen zu berechnen, können Sie mit „SHOW GLOBAL STATUS“ die Zähler „Innodb_buffer_pool_read_requests“ und „Innodb_buffer_pool_reads“ abrufen und dann den Wert anhand der nachstehenden Formel berechnen.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

Betrachten Sie die folgenden Beispiele.

show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name | Value |
| +--------------------------+-------+ |
| Innodb_buffer_pool_reads | 197 |
| +--------------------------+-------+ |
| 1 row in set (0.00 sec) |

show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name | Value |
| +----------------------------------+----------+ |
| Innodb_buffer_pool_read_requests | 22479167 |
| +----------------------------------+----------+ |
| 1 row in set (0.00 sec) |

Mit den obigen Werten führt die Berechnung der InnoDB-Pufferpool-Trefferquote für Leseanforderungen zum folgenden Ergebnis:

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100
Buffer hit ratio = 99.99%

Zusätzlich zur Puffercache-Trefferquote für Select-Anweisungen werden Schreibvorgänge in den InnoDB-Pufferpool für alle DML-Anweisungen im Hintergrund ausgeführt. Wenn es jedoch notwendig ist, eine Seite zu lesen oder zu erstellen und keine freien Seiten verfügbar sind, muss auch darauf gewartet werden, dass die Seite zuerst geleert werden.

Der Innodb_buffer_pool_wait_free-Zähler zählt, wie oft dies passiert ist. Wenn Innodb_buffer_pool_wait_free größer als 0 ist, ist dies ein deutliches Anzeichen dafür, dass der InnoDB-Pufferpool zu klein ist und der Pufferpool oder die Instanz vergrößert werden muss, damit die in die Datenbank kommenden Schreibvorgänge bewältigt werden können.

Empfehlungen

  • Stellen Sie sicher, dass Ihre Datenbank über genügend Ressourcen verfügt, um Ihre Abfragen auszuführen. Manchmal müssen Sie die Instanzgröße hochskalieren, damit mehr physischer Speicher verfügbar ist, damit Puffer und Caches Ihre Workload bewältigen können.
  • Vermeiden Sie umfangreiche oder lange Transaktionen, indem Sie sie in kleinere Transaktionen aufteilen.
  • Verwenden Sie Benachrichtigungen zu „Host-Arbeitsspeicherprozent“, damit Sie Benachrichtigungen erhalten, wenn das System einen der angegebenen Schwellenwerte überschreitet.
  • Verwenden Sie Query Performance Insights oder Azure Workbooks, um problematische oder langsam ausgeführte Abfragen zu ermitteln und dann zu optimieren.
  • Erfassen Sie für Datenbankserver in der Produktion in regelmäßigen Abständen Diagnosedaten, um sicherzustellen, dass alles reibungslos ausgeführt wird. Beheben Sie andernfalls die festgestellten Probleme.

Nächster Schritt

Stack Overflow