Hohen Arbeitsspeicherverbrauch in Instanzen optimieren

Vorschau

Diese Funktion unterliegt den „Nutzungsbedingungen für Pre-GA-Angebote“ im Abschnitt „Allgemeine Nutzungsbedingungen“ der dienstspezifischen Nutzungsbedingungen. Pre-GA-Funktionen sind „wie besehen“ verfügbar und werden möglicherweise nur eingeschränkt unterstützt. Weitere Informationen finden Sie in den Beschreibungen der Startphase.

Es ist ein häufiges Problem, dass Instanzen viel Arbeitsspeicher belegen oder dass bei ihnen Probleme mit unzureichendem Arbeitsspeicher auftreten. Eine Datenbankinstanz mit hoher Speicherauslastung führt häufig zu Leistungsproblemen, Abbrüchen oder sogar Datenbankausfällen.

Einige MySQL-Arbeitsspeicherblöcke werden global verwendet. Dies bedeutet, dass sich alle Abfragearbeitslasten Arbeitsspeicherstandorte teilen, die immer belegt sind und nur freigegeben werden, wenn der MySQL-Prozess beendet wird. Einige Arbeitsspeicherblöcke sind sitzungsbasiert. Das heißt, wenn eine Sitzung geschlossen wird, wird der von dieser Sitzung verwendete Arbeitsspeicher auch wieder für das System freigegeben.

Wenn eine Cloud SQL for MySQL-Instanz eine hohe Arbeitsspeichernutzung aufweist, empfiehlt Cloud SQL, dass Sie die Abfrage oder den Prozess identifizieren, der viel Arbeitsspeicher beansprucht, und diesen freigeben. Der MySQL-Arbeitsspeicherverbrauch ist in drei Hauptteile unterteilt:

• Threads und Prozessarbeitsspeicherverbrauch
• Zwischenspeicher-Arbeitsspeicherverbrauch
• Cache-Arbeitsspeicherverbrauch

Threads und Prozessarbeitsspeicherverbrauch

Jede Nutzersitzung belegt Arbeitsspeicher in Abhängigkeit von den ausgeführten Abfragen, dem Zwischenspeicher oder dem Cache, der von dieser Sitzung verwendet wird, und wird von den Sitzungsparametern von MySQL gesteuert. Zu den wichtigsten Parametern gehören:

• thread_stack
• net_buffer_length
• read_buffer_size
• read_rnd_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size

Wenn zu einem bestimmten Zeitpunkt eine Anzahl von N Abfragen ausgeführt wird, verbraucht jede Abfrage Arbeitsspeicher gemäß diesen Parametern während der Sitzung.

Zwischenspeicher-Arbeitsspeicherverbrauch

Dieser Teil des Arbeitsspeichers ist für alle Abfragen gleich und wird durch Parameter wie Innodb_buffer_pool_size, Innodb_log_buffer_size und key_buffer_size gesteuert.

Cache-Arbeitsspeicherverbrauch

Der Cache-Arbeitsspeicher enthält einen Abfrage-Cache, mit dem die Abfragen und ihre Ergebnisse für einen schnelleren Datenabruf der gleichen nachfolgenden Abfragen gespeichert werden. Er enthält auch den binlog-Cache, der die Änderungen am binären Log während der Ausführung der Transaktion enthält, und wird von binlog_cache_size gesteuert.

Anderer Arbeitsspeicherverbrauch

Der Arbeitsspeicher wird auch für Join- und Sortiervorgänge verwendet. Wenn Ihre Abfragen Join- oder Sortiervorgänge verwenden, verwenden diese Abfragen Arbeitsspeicher basierend auf join_buffer_size und sort_buffer_size.

Wenn Sie abgesehen davon das Leistungsschema aktivieren, wird Arbeitsspeicher beansprucht. Verwenden Sie die folgende Abfrage, um die Arbeitsspeichernutzung durch das Leistungsschema zu prüfen:

SELECT *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

In MySQL können Sie auf viele Instrumente zugreifen, um die Arbeitsspeichernutzung über das Leistungsschema zu überwachen. Weitere Informationen finden Sie in der MySQL-Dokumentation.

Der MyISAM-bezogene Parameter für das Einfügen von Daten im Bulk ist bulk_insert_buffer_size.

Informationen zur Arbeitsspeichernutzung durch MySQL finden Sie in der MySQL-Dokumentation.

Empfehlungen

Verwenden Sie den Metrics Explorer, um die Speichernutzung zu ermitteln

Sie können die Speichernutzung einer Instanz mit dem Messwert database/memory/components.usage im Metrics Explorer prüfen.

Wenn in database/memory/components.cache und database/memory/components.free weniger als 5 % Arbeitsspeicher verfügbar sind, ist das Risiko eines OOM-Ereignisses hoch. Zur Überwachung der Arbeitsspeichernutzung und zur Vermeidung von OOM-Ereignissen empfehlen wir Ihnen, eine Benachrichtigungsrichtlinie mit einer Messwertschwellenwertbedingung von mindestens 95 % in database/memory/components.usage einzurichten.

Die folgende Tabelle zeigt die Beziehung zwischen dem Instanzarbeitsspeicher und dem empfohlenen Schwellenwert für Benachrichtigungen:

Instanzarbeitsspeicher	Empfohlener Schwellenwert für Benachrichtigungen
Bis zu 100 GB	95 %
100 GB bis 200 GB	96 %
200 GB bis 300 GB	97 %
Mehr als 300 GB	98 %

Arbeitsspeicherverbrauch berechnen

Berechnen Sie die maximale Arbeitsspeichernutzung durch Ihre MySQL-Datenbank, um den geeigneten Instanztyp für Ihre MySQL-Datenbank auszuwählen. Verwenden Sie die folgende Formel:

Maximale MySQL-Arbeitsspeichernutzung = innodb_buffer_pool_size +innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size + key_buffer_size + (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) x max_connections

In der Formel werden folgende Parameter verwendet:

• innodb_buffer_pool_size: Die Größe (in Byte) des Zwischenspeicherpools, des Arbeitsspeicherbereichs, in dem InnoDB Tabellen- und Indexdaten im Cache speichert.
• innodb_additional_mem_pool_size: Die Größe (in Byte) eines Arbeitsspeicherpools, den InnoDB zum Speichern von Datenwörterbuchinformationen und anderen internen Datenstrukturen verwendet.
• innodb_log_buffer_size: Die Größe (in Byte) des Zwischenspeichers, den InnoDB zum Schreiben in die Logdateien auf dem Laufwerk verwendet.
• tmp_table_size: Die maximale Größe der internen arbeitsspeicherinternen temporären Tabellen, die von der MEMORY-Speicher-Engine und ab MySQL 8.0.28 von der TempTable-Speicher-Engine erstellt wurden.
• Key_buffer_size: Die Größe des Zwischenspeichers, der für Indexblöcke verwendet wird. Indexblöcke für MyISAM-Tabellen werden zwischengespeichert und von allen Threads gemeinsam genutzt.
• Read_buffer_size: Jeder Thread, der einen sequenziellen Scan für eine MyISAM-Tabelle durchführt, weist jeder Tabelle, die gescannt wird, einen Zwischenspeicher dieser Größe (in Byte) zu.
• Read_rnd_buffer_size: Diese Variable wird für das Lesen aus MyISAM-Tabellen, für beliebige Speicher-Engines und für die Optimierung von Lesevorgängen in mehreren Bereichen verwendet.
• Sort_buffer_size: Jede Sitzung, die eine Sortierung ausführen muss, weist einen Zwischenspeicher dieser Größe zu. "sort_buffer_size" ist nicht spezifisch für Speicher-Engines und gilt allgemein für die Optimierung.
• Join_buffer_size: Die Mindestgröße des Zwischenspeichers, der für einfache Index-Scans, Bereichsindex-Scans und Joins verwendet wird, die keine Indexe verwenden und daher vollständige Tabellenscans ausführen.
• Max_connections: Die maximal zulässige Anzahl an gleichzeitigen Clientverbindungen.

Hinweis: Es gibt abhängig von den Abfragen viele andere kleine Komponenten, die Arbeitsspeicher verbrauchen. Daher sollten diese Komponenten ebenfalls Platz haben.

Fehlerbehebung bei hohem Arbeitsspeicherverbrauch

• Führen Sie SHOW PROCESSLIST aus, um die laufenden Abfragen zu sehen, die derzeit Arbeitsspeicher belegen. Es werden alle verbundenen Threads und ihre derzeit ausgeführten SQL-Anweisungen angezeigt und es wird versucht, sie zu optimieren. Achten Sie auf die Spalten für den Status und die Dauer.

  mysql> SHOW [FULL] PROCESSLIST;
  
  

• Prüfen Sie SHOW ENGINE INNODB STATUS im Abschnitt BUFFER POOL AND MEMORY, um die aktuelle Zwischenspeicherpool- und Arbeitsspeichernutzung zu ermitteln, was dabei helfen kann, die Größe des Zwischenspeicherpools festzulegen.

  mysql> SHOW ENGINE INNODB STATUS \G
  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  Total memory allocated 398063986; in additional pool allocated 0
  Dictionary memory allocated 12056
  Buffer pool size 89129
  Free buffers 45671
  Database pages 1367
  Old database pages 0
  Modified db pages 0
  

• Verwenden Sie den MySQL-Befehl SHOW variables, um die Zählerwerte zu prüfen, die Informationen wie die Anzahl temporärer Tabellen, die Anzahl der Threads, die Anzahl der Tabellen-Caches, schmutzige Seiten, offene Tabellen und die Zwischenspeicherpoolnutzung angeben.

  mysql> SHOW variables like 'VARIABLE_NAME'
  

Änderungen übernehmen

Nachdem Sie die Arbeitsspeichernutzung durch verschiedene Komponenten analysiert haben, legen Sie das entsprechende Flag in Ihrer MySQL-Datenbank fest. Zum Ändern des Flags in Cloud SQL for MySQL können Sie die Google Cloud Console oder die gcloud CLI verwenden. Wenn Sie den Flag-Wert mit der Google Cloud Console ändern möchten, bearbeiten Sie den Abschnitt Flags, wählen Sie das Flag aus und geben Sie den neuen Wert ein.

Wenn die Arbeitsspeichernutzung weiterhin hoch ist und ausgeführte Abfragen und Flag-Werte bereits optimiert wurden, sollten Sie eventuell die Instanzgröße erhöhen, um Arbeitsspeichermangel zu vermeiden.