Behandeln von Problemen mit geringem Arbeitsspeicher in der Azure-Datenbank für MySQL – Flexibler Server

  • Artikel

GILT FÜR: Azure Database for MySQL – Single Server Azure Database for MySQL – Flexible Server

Wichtig

Azure Database for MySQL single server is on the retirement path. Es wird dringend empfohlen, ein Upgrade auf azure Database for MySQL flexiblen Server durchzuführen. Weitere Informationen zum Migrieren zu Azure Database for MySQL flexible Server finden Sie unter Was geschieht mit Azure Database for MySQL Single Server?

Um sicherzustellen, dass eine flexible Azure-Datenbank für MySQL-Serverinstanz optimal ausgeführt wird, ist es sehr wichtig, über die entsprechende Speicherzuweisung und -auslastung zu verfügen. Wenn Sie eine Instanz der Azure-Datenbank für MySQL flexiblen Server erstellen, hängt der verfügbare physische Arbeitsspeicher standardmäßig von der Ebene 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.

Beachten Sie, dass azure Database for MySQL flexible Server Speicher verbraucht, um so viel 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 gibt jedoch mehrere Gründe für Probleme, weil nicht genügend Arbeitsspeicher vorhanden ist:

  • 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

Der flexible Azure Database for MySQL-Server bietet eine Reihe von Metriken, um die Leistung Ihrer Datenbankinstanz zu messen. Um die Speicherauslastung für Ihren Datenbankserver besser zu verstehen, sind die Metriken für die prozentuale Hostspeicherauslastung oder die prozentuale Arbeitsspeicherauslastung hilfreich.

Viewing memory utilization metrics.

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 große Anzahl temporärer In-Memory-Tabellen kann schnell zu geringem verfügbaren Speicher in einer Instanz von Azure Database für MySQL flexiblen 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 verlagert werden, sodass die Latenz von Abfragen steigt. 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 table_open_cache zu niedrig kann dazu führen, dass azure Database for MySQL flexible Server mehr Zeit beim Öffnen und Schließen von Tabellen verbringt, die für die Abfrageverarbeitung erforderlich sind.
  • Das Festlegen dieses Werts auf einen zu großen Wert kann dazu führen, dass mehr Arbeitsspeicher verwendet wird und im Betriebssystem zu viele Dateideskriptoren verwendet werden, 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 verwendet, 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 join_buffer_size-Parameters 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 SHOW GLOBAL STATUS-Ausgabe angezeigt werden, sollten Sie den sort_buffer_size-Wert 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. Vergewissern Sie sich, dass keine Abfragen vorhanden sind, die den Abfragecache benötigen, bevor Sie ihn deaktivieren, wenn der Abfragecache derzeit in Ihrer Lösung aktiviert ist.

Berechnen der Puffercache-Trefferquote

Das Puffercachetrefferverhältnis ist in der Azure-Datenbank für MySQL-flexible Serverumgebung wichtig, um zu verstehen, ob der Pufferpool die Workloadanforderungen aufnehmen kann oder nicht, und als allgemeine Faustregel empfiehlt es sich, immer ein Pufferpoolcachetrefferverhältnis von mehr als 99 % zu haben.

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 das folgende Beispiel.

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

mysql> 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 Arbeitsspeicher verfügbar ist, damit Puffer und Caches die Workload bewältigen können.
  • Vermeiden Sie umfangreiche oder lange Transaktionen, indem Sie sie in kleinere Transaktionen aufteilen.
  • Verwenden Sie Warnungen zur prozentualen Hostspeicherauslastung, damit Sie benachrichtigt werden, 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ächste Schritte

Um Antworten anderer Benutzer auf Ihre wichtigsten Fragen zu erhalten oder eine Frage zu veröffentlichen oder zu beantworten, besuchen Sie Stack Overflow.