Hohe Arbeitsspeichernutzung in Instanzen optimieren

Es ist ein häufiges Problem, dass Instanzen viel Arbeitsspeicher belegen oder dass bei ihnen Out of Memory (OOM)-Ereignisse auftreten. Eine Datenbankinstanz, die mit hoher Arbeitsspeicherauslastung ausgeführt wird, führt häufig zu Leistungsproblemen, Ausführungsunterbrechungen 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.

Der InnoDB-Pufferpool, der mit dem Flag innodb_buffer_pool_size konfiguriert wird, belegt einen erheblichen Teil des Arbeitsspeichers auf Ihrer Cloud SQL for MySQL-Instanz und dient als Cache zur Leistungsverbesserung. Um das Risiko von OOM-Ereignissen (Out-of-Memory) zu verringern, können Sie den verwalteten Zwischenspeicherpool aktivieren.

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

In den folgenden Abschnitten finden Sie einige Empfehlungen für eine optimale Speichernutzung.

Verwalteten Pufferpool aktivieren

Wenn Sie den verwalteten Pufferpool aktivieren, können Sie den Arbeitsspeicherverbrauch des InnoDB-Pufferpools (oder innodb_buffer_pool_size) reduzieren, wenn der Arbeitsspeicher der Instanz hoch ist. Dadurch wird Arbeitsspeicher freigegeben, der dann von anderen Datenbankprozessen genutzt werden kann. Wenn die Arbeitsspeichernutzung Ihrer Instanz hoch ist, kann es zu Ereignissen vom Typ „Nicht genügend Arbeitsspeicher“ (OOM) kommen. Wir empfehlen, den verwalteten Zwischenspeicherpool auf Ihrer Instanz zu aktivieren, um OOM-Ereignisse zu vermeiden.

Wenn sich die Speichernutzung für 10 Minuten oder länger auf einem niedrigeren Wert stabilisiert, erhöht der verwaltete Zwischenspeicherpool den Wert von innodb_buffer_pool_size schrittweise auf den ursprünglichen Wert. Sie können den Wert des Flags innodb_buffer_pool_size auch auf einen ausgewählten Wert erhöhen, nachdem sich die Speichernutzung stabilisiert hat.

Teilnahmevoraussetzungen

Sie können den verwalteten Zwischenspeicherpool nicht für Instanzen mit freigegebenem Kern oder für MySQL 5.6 oder MySQL 5.7 aktivieren.

Feature aktivieren

Wenn Sie den verwalteten Zwischenspeicherpool für Ihre Instanz aktivieren möchten, setzen Sie das Flag innodb_cloudsql_managed_buffer_pool auf on. Weitere Informationen zum Festlegen von Datenbank-Flags finden Sie unter Datenbank-Flag festlegen.

Das Ändern des Werts des Flags innodb_cloudsql_managed_buffer_pool erfordert keinen Neustart der Cloud SQL-Instanz.

Wenn Sie den verwalteten Pufferpool aktiviert haben und der Arbeitsspeicherverbrauch Ihrer Instanz den Standardgrenzwert des zugewiesenen Arbeitsspeichers überschreitet, beginnt Cloud SQL, die Größe des innodb_buffer_pool_size zu reduzieren. Dieser Standardprozentsatz variiert je nach RAM-Kapazität Ihrer Instanz zwischen 90 % und 97 %. Wenn Sie den Schwellenwert ändern möchten, setzen Sie das Flag innodb_cloudsql_managed_buffer_pool_threshold_pct auf einen anderen Prozentwert. Verwenden Sie beispielsweise den folgenden Befehl, um den Schwellenwert auf 97 % anzupassen:

gcloud sql instances patch INSTANCE_NAME \
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_threshold_pct=97

Sie können das Flag innodb_cloudsql_managed_buffer_pool_threshold_pct auf einen ganzzahligen Wert zwischen 50 und 99 festlegen. Wenn Sie den Wert des Schwellenwerts für die Arbeitsspeichernutzung ändern, ist kein Neustart der Cloud SQL-Instanz erforderlich.

Der verwaltete Zwischenspeicherpool erhöht das Flag innodb_buffer_pool_size, wenn sich die Arbeitsspeichernutzung nach vorherigen Reduzierungen für 10 Minuten oder länger stabilisiert. Standardmäßig muss die Speichernutzung der Datenbank bei maximal 70 % liegen, damit die Erhöhung ausgelöst wird. Wenn Sie den Schwellenwert ändern möchten, setzen Sie das Flag innodb_cloudsql_managed_buffer_pool_tuneup_pct auf einen anderen Prozentwert. Wenn Sie beispielsweise den Schwellenwert für die Erhöhung auf 80 % ändern möchten, verwenden Sie den folgenden Befehl:

gcloud sql instances patch INSTANCE_NAME \
   --database-flags=EXISTING_FLAGS,innodb_cloudsql_managed_buffer_pool=on,\
     innodb_cloudsql_managed_buffer_pool_tuneup_pct=80

Anpassungslogik

Der verwaltete Pufferpool verkleinert den innodb_buffer_pool_size nicht auf eine feste, vordefinierte Mindestgröße. Stattdessen wird die Größe iterativ und dynamisch reduziert, bis die Gesamtauslastung des Instanzspeichers wieder unter den konfigurierten Schwellenwertprozentsatz (innodb_cloudsql_managed_buffer_pool_threshold_pct) fällt. Der Pufferpool wird verkleinert, indem der Wert des Flags innodb_buffer_pool_size angepasst wird. Dabei wird die integrierte Funktion zur Größenänderung des Pufferpools von InnoDB genutzt.

Damit der innodb_buffer_pool_size nicht auf eine Größe schrumpft, die die Leistung stark beeinträchtigen würde, wenn die Speichernutzung trotz Reduzierung hoch bleibt, verwendet die Funktion eine interne Sicherheitsuntergrenze. Die Werte geben den Prozentsatz des gesamten Instanzspeichers an, der dem Pufferpool zugewiesen werden muss.

Wie stark innodb_buffer_pool_size abnimmt, hängt von der Speicherkapazität Ihrer Datenbankinstanz ab. In der folgenden Tabelle ist die prozentuale Verringerung für jede Containergröße aufgeführt:

Nach der Berechnung des neuen, reduzierten Werts rundet der verwaltete Zwischenspeicherpool innodb_buffer_pool_size auf das nächste Vielfache der Werte innodb_buffer_pool_instances und innodb_buffer_pool_chunk_size.

Wenn der verwaltete Zwischenspeicherpool Anpassungen am Wert von innodb_buffer_pool_size vornimmt, werden die Änderungen nicht in der Cloud de Confiance Console angezeigt. Wenn Sie den aktuellen Wert von innodb_buffer_pool_size bei aktiviertem verwaltetem Zwischenspeicherpool aufrufen möchten, können Sie den MySQL-Client verwenden:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

Beschränkungen

Eine Verringerung der Größe des Pufferpools kann OOM-Fehler nicht in allen Fällen verhindern. Beispielsweise können einige Arbeitslasten den Arbeitsspeicher übermäßig beanspruchen oder plötzlich ansteigen, einige Cloud SQL-Instanzen sind möglicherweise unterdimensioniert oder der Pufferpool ist möglicherweise nicht aufgewärmt. Cloud SQL kann den Arbeitsspeicher möglicherweise nicht schnell genug freigeben, um plötzliche Änderungen der Arbeitsspeicherauslastung zu bewältigen. Darüber hinaus kann Cloud SQL keine falsch konfigurierten Werte anderer Speicher-Flags verarbeiten.

Monitoring

Sie können den verwalteten Zwischenspeicherpool im MySQL-Fehlerlog überwachen. Im Log-Explorer von Cloud Logging können Sie das Log mysql.err nach Einträgen mit dem Präfix Managed Buffer Pool Plugin: oder Tuner Plugin: filtern, um die neuesten Anpassungsereignisse zu finden.

Wenn der verwaltete Zwischenspeicherpool zum ersten Mal gestartet wird, wird ein Log wie das folgende ausgegeben:

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: MySQL Instance memory limit: 29533, Current MySQL memory usage: 2663641088, Max Allowed MySQL memory usage: 30732730368 ...

Die folgenden Logs zeigen Beispiele für automatische Verringerungen des innodb_buffer_pool_size:

[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Decreasing InnoDB Buffer Pool Size.
[Note] [MY-000000] [Server] Managed Buffer Pool Plugin: Updated innodb_buffer_pool_size=805306368 bytes.

Sie können logbasierte Messwerte konfigurieren, um Anpassungsereignisse für verwaltete Zwischenspeicher im Zeitverlauf zu verfolgen.

Verwenden Sie den Metrics Explorer von Cloud Monitoring, um die Speichernutzung zu ermitteln.

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

Wenn in database/memory/components.cache und database/memory/components.free weniger als 10 % 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 in database/memory/components.usage einzurichten.

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

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.

Fehlerbehebung bei hohem Arbeitsspeicherverbrauch

• Führen Sie SHOW PROCESSLIST aus, um die laufenden Abfragen zu sehen, die Arbeitsspeicher belegen. Es werden alle verbundenen Threads und ihre 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 der Cloud SQL for MySQL-Instanz können Sie die Cloud de Confiance Console oder die gcloud CLI verwenden. Wenn Sie den Flag-Wert mit der Cloud de Confiance 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.

Nächste Schritte

• Cloud de Confiance by S3NS -Recommender