Fehlerbehebung bei MySQL-Arbeitsspeicherproblemen

Eine Situation mit unzureichendem Arbeitsspeicher tritt auf, wenn ein Prozess versucht, Arbeitsspeicher zuzuweisen, und schlägt fehl, weil auf dem Server kein Arbeitsspeicher verfügbar ist. Dies führt häufig zum Linux OOM Killer, einem Prozess, der vom Linux-Kernel verwendet wird, wenn das System sehr wenig Arbeitsspeicher hat, und den Datenbankprozess beendet.

Ein Datenbankabsturz aufgrund einer OOM-Bedingung ist nicht ideal, da der Datenbankprozess mit der SIGKILL-Anweisung beendet wird, wodurch die Anwendung keine Chance auf ein normales Herunterfahren von MySQL hat. Dies verursacht die folgenden Probleme:

• Der MySQL-Server ist ausgefallen (während der Absturzwiederherstellung, die einige Minuten dauern kann), was sich auf Nutzer und Unternehmen auswirkt.
• Der MySQL-Server benötigt zusätzliche Zeit, um die Zwischenspeicher und Caches vollständig aufzuwärmen, um die Spitzenleistung zu erreichen
• Möglichkeit der Datenbeschädigung durch einen unerwarteten Absturz

MySQL fügt in performance_schema eine Speicherinstrumentierung hinzu, die das Verständnis der MySQL-Arbeitsspeicherzuweisung erleichtert. In diesem Artikel erfahren Sie, wie Sie MySQL-Arbeitsspeicherprobleme mithilfe von „performance_schema“ beheben.

Jede Cloud SQL-Instanz wird von einer virtuellen Maschine (VM) betrieben, die auf einem Google Cloud-Hostserver ausgeführt wird. Jede VM betreibt das Datenbankprogramm, z. B. MySQL Server und Dienst-Agents, die unterstützende Dienste wie Logging und Monitoring bereitstellen. Cloud SQL reserviert einen Teil des Arbeitsspeichers für das Betriebssystem und die Dienst-Agents und weist den verbleibenden Arbeitsspeicher für Datenbankprogramme wie mysqld zu.

Die folgende Tabelle zeigt die Arbeitsspeicherzuweisung für den mysqld-Prozess und die Standardkonfiguration des InnoDB-Zwischenspeicherpools für jeden Maschinentyp.

Bei Instanzen mit 10 GB RAM und mehr ist der mysqld-Prozess auf 92% des Instanz-RAM beschränkt. Der InnoDB-Pufferpool ist auf 75 bis 80% des für den mysqld-Prozess verfügbaren Arbeitsspeichers konfiguriert. Neben dem InnoDB-Pufferpool weist MySQL mehrere Puffer und Caches zu, um die Leistung zu verbessern. Weitere Informationen zur Arbeitsspeicherzuweisung in MySQL finden Sie in diesem Artikel.

Wenn die Arbeitsspeichernutzung von mysqld das konfigurierte Limit des mysqld-Prozesses überschreitet, beendet der Linux-OOM-Killer den mysqld-Prozess, da er der größte Arbeitsspeichernutzer der Instanz ist.

performance_schema ist ein MySQL-Feature zur Überwachung der Serverausführung auf niedriger Ebene. Mit Cloud SQL for MySQL kann „performance_schema“ auf Instanzen mit einer RAM-Größe von mindestens 3 GB aktiviert werden. Ab MySQL 8.0 ist es in Instanzen mit mindestens 15 GB RAM standardmäßig aktiviert.

Performance_schema ist für MySQL 5.6 und 5.7 standardmäßig deaktiviert. Es kann mithilfe von Datenbank-Flags aktiviert werden.

Das Aktivieren von „performance_schema“ ist mit einem gewissen Leistungsaufwand sowie Auswirkungen auf den Arbeitsspeicher verbunden. Informationen zur Arbeitsspeicherzuweisung finden Sie im Artikel zum Arbeitsspeicher-Zuordnungsmodell des Leistungsschemas.

Performance_schema hilft bei der Überwachung der Speichernutzung in MySQL. Speicherinstrumente haben Namen in Form von memory/code_area/instrument_name, wobei code_area ein Wert wie sql oder Innodb ist und instrument_name das Instrumentendetail ist.

MySQL 5.7 aktiviert einige Speicherinstrumente standardmäßig, aber nicht alle. Sie können sie aktivieren, indem Sie in der Tabelle performance_schema.setup_instruments die Spalte AKTIVIERT aktualisieren.

mysql> select version();

+-------------------+

| version()    |

+-------------------+

| 5.7.39-google-log |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   70 |

| NO   |   306 |

+---------+----------+

2 rows in set (0.00 sec)

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Query OK, 306 rows affected (0.00 sec)

Rows matched: 376 Changed: 306 Warnings: 0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   376 |

+---------+----------+

1 row in set (0.00 sec)

Wenn Sie zur Laufzeit Speicherinstrumente aktivieren, werden nur die Speicherobjekte gezählt, die nach der Aktivierung der Instrumentierung erstellt wurden. Zur Fehlerbehebung wird daher empfohlen, zu warten, bis der Server genügend Daten gesammelt hat.

MySQL 8.0 aktiviert standardmäßig alle Speicherinstrumente.

mysql> select version();

+---------------+

| version()  |

+---------------+

| 8.0.28-google |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| YES  |   490 |

+---------+----------+

1 row in set (0.00 sec)

Das Sys-Schema enthält viele Ansichten, in denen „performance_schema“-Daten in leicht verständlichen Formen zusammengefasst werden. Die meisten dieser Aufrufe sind paarweise möglich. Sie haben den gleichen Namen und einer von ihnen hat das Präfix x$. Die folgenden Ansichten sind in MySQL 5.7 und 8.0 verfügbar, um die Arbeitsspeicherzuweisung auf verschiedenen Ebenen anzusehen.

MySQL use sys;

Datenbank geändert

mysql> zeigt Tabellen wie '%memory%' an;

+-------------------------------------+

| Tables_in_sys (%memory%)      |

+-------------------------------------+

| memory_by_host_by_current_bytes  |

| memory_by_thread_by_current_bytes |

| memory_by_user_by_current_bytes  |

| memory_global_by_current_bytes   |

| memory_global_total        |

| x$memory_by_host_by_current_bytes |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes |

| x$memory_global_by_current_bytes  |

| x$memory_global_total       |

+-------------------------------------+

10 rows in set (0.02 sec)

Die Ansicht sys.memory_global_total fasst die gesamte Arbeitsspeichernutzung in einem lesbaren Format zusammen.

mysql> select * from sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB   |

+-----------------+

1 row in set (0.44 sec)

sys.x$memory_global_total zeigt die gleichen Informationen an, jedoch in Byte.

mysql> select * from sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|  13351482992 |

+-----------------+

1 row in set (1.39 sec)

Die Ansicht ohne das Präfix x$ soll benutzerfreundlicher und leichter lesbar sein. Die Ansicht mit dem Präfix x$, die dieselben Werte im Rohformat anzeigt, ist eher für die Verwendung mit anderen Tools vorgesehen, die die Daten selbst verarbeiten.

Die Ansicht „sys.memory_global_by_current_bytes“ zeigt die aktuelle Arbeitsspeichernutzung, gruppiert nach dem Zuweisungstyp (event_name), z. B. „memory/innodb/buf_buf_pool“ (InnoDB-Zwischenspeicherpool). Standardmäßig werden Zeilen absteigend nach dem verwendeten Arbeitsspeicher sortiert.

Bevor wir uns die einzelnen Zuweisungstypen genauer ansehen, ist es empfehlenswert, einen Überblick über die Arbeitsspeichernutzung für jeden Codebereich wie Innodb, SQL, performance_schema usw. zu haben.

Die folgende Abfrage aggregiert den aktuell zugewiesenen Speicher nach dem Codebereich.

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',2)

ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area        | current_alloc |

+---------------------------+---------------+

| memory/innodb      | 11.28 GiB  |

| memory/performance_schema | 520.02 MiB  |

| memory/temptable     | 514.00 MiB  |

| memory/sql        | 106.12 MiB  |

| memory/mysys       | 43.25 MiB  |

| memory/vio        | 372.15 KiB  |

| memory/myisam      | 696 Byte  |

| memory/csv        | 88 Byte  |

| memory/blackhole     | 88 Byte  |

+---------------------------+---------------+

9 rows in set (1.02 sec)

Diese Ausgabe zeigt, dass innodb der größte Arbeitsspeicherverbraucher ist, gefolgt von performance_schema und temporären Tabellen.

Wenn Sie jetzt „sys.memory_global_by_current_bytes“ abfragen, wird die Arbeitsspeichernutzung für interne MySQL-Strukturen angezeigt, wie in der Spalte „event_name“ beschrieben. Wenn Sie die ungewöhnliche Speichererweiterung beheben möchten, können Sie die Ursache anhand der Ausgabe dieser Ansicht identifizieren.

mysql> select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name          | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool  | 10.72 GiB  | 10.72 GiB |

| memory/temptable/physical_ram | 514.00 MiB  | 515.00 MiB |

| memory/innodb/hash0hash   | 292.69 MiB  | 292.69 MiB |

| memory/innodb/memory     | 191.77 MiB  | 197.94 MiB |

| memory/sql/TABLE       | 60.39 MiB  | 62.35 MiB |

+-------------------------------+---------------+------------+

5 rows in set (0.45 sec)

Nachfolgend finden Sie Details zu den einzelnen Zuweisungstypen.

Diese Ansicht fasst die Arbeitsspeichernutzung gruppiert nach Nutzer zusammen. Hat ein Nutzer mehrere Verbindungen, wird die Arbeitsspeichernutzung für alle Instanzen des Nutzers addiert.

mysql> select user,current_allocated from memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user      | current_allocated |

+-----------------+-------------------+

| sbtest     | 60.30 MiB    |

| background   | 2.68 MiB     |

| root      | 1.52 MiB     |

| event_scheduler | 16.38 KiB    |

+-----------------+-------------------+

4 rows in set (1.16 sec)

Wenn Sie für jede Anwendung oder jeden Job einen anderen Nutzer verwenden (empfohlen), kann diese Ansicht dabei helfen, herauszufinden, ob eine bestimmte Anwendung oder ein bestimmter Job viel Arbeitsspeicher verbraucht.

In dieser Ansicht wird die Arbeitsspeichernutzung gruppiert nach Hostnamen zusammengefasst.

mysql> select host,current_allocated from memory_by_host_by_current_bytes;

+------------+-------------------+

| host   | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB    |

| background | 2.70 MiB     |

| 127.0.0.1 | 311.01 KiB    |

| localhost | 16.38 KiB    |

+------------+-------------------+

4 rows in set (1.58 sec)

Diese Ansicht ist nützlich, um die Arbeitsspeichernutzung pro Clienthost zu ermitteln. Mehrere Client-/Anwendungsserver sind heutzutage sehr üblich. Mithilfe dieser Ansicht können Sie ermitteln, ob ein bestimmter Client oder eine bestimmte Arbeitslast die Arbeitsspeicherauslastung verursacht. Sie können die Änderung auch zuerst auf einem Client-/Anwendungsserver implementieren, ihre Auswirkungen auf den Arbeitsspeicher überwachen (durch Vergleichen der Arbeitsspeichernutzung mit anderen Client-Hosts) und fundierte Entscheidungen treffen.

Diese Ansicht fasst die Arbeitsspeichernutzung nach Thread gruppiert zusammen und hilft, die Threads zu finden, die den meisten Arbeitsspeicher belegen.

mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user       | current_allocated |

+-----------+-------------------+-------------------+

|  12999 | sbtest@10.128.0.8 | 3.80 MiB     |

|  12888 | sbtest@10.128.0.8 | 3.75 MiB     |

|  12760 | sbtest@10.128.0.8 | 3.68 MiB     |

|  13128 | sbtest@10.128.0.8 | 3.67 MiB     |

|  13221 | sbtest@10.128.0.8 | 3.47 MiB     |

+-----------+-------------------+-------------------+

5 rows in set (2.29 sec)

Wenn Sie feststellen, dass ein bestimmter Thread viel Arbeitsspeicher verbraucht, können Sie die langsamen MySQL-Abfragelogs oder die allgemeinen Logs mithilfe der „thread_id“ filtern und die Sitzung/Abfragen identifizieren, die zum Anstieg des Arbeitsspeichers geführt haben.

Die Fehlerbehebung bei hoher Arbeitsspeichernutzung ist eine schwierige Aufgabe. Glücklicherweise hilft die Speicherinstrumentierung performance_schema Nutzern dabei zu verstehen, wie der Speicher von internen MySQL-Strukturen verwendet wird. Die Sys-Schemaansichten erleichtern die Interpretation der Informationen. Wenn speicherbezogene Probleme auftreten, hilft Ihnen diese Funktion, die Ursache des Problems zu finden. Sie können das Problem beheben, indem Sie die Konfigurations-/Abfrageänderungen vornehmen.

Beispiel:

1. Wenn interne temporäre Tabellen viel Arbeitsspeicher benötigen, können Sie entweder die Größe der temporären Tabellen mit tmp_table_size begrenzen oder den Wert innodb_buffer_pool_size reduzieren, um Platz für Zwischenspeicher pro Sitzung zu schaffen.
2. Wenn ein bestimmter Thread viel Arbeitsspeicher verbraucht, können Sie langsame Abfragelogs mithilfe von „thread_id“ filtern, um die problematischen Abfragen zu identifizieren und sie zu optimieren, um den Arbeitsspeicherbedarf für diesen Thread/diese Sitzung zu reduzieren.

Wenn eine MySQL-Instanz den Arbeitsspeicher höher nutzt als vorgesehen und die Arbeitsspeichernutzung kontinuierlich zunimmt, bis dem Server der Arbeitsspeicher ausgeht, und „performance_schema“ entweder eine Abweichung beim gesamten Arbeitsspeicher im Vergleich zum instrumentierten Arbeitsspeicher anzeigt für was der Speicher verwendet wird, könnte das ein Hinweis auf ein Speicherleck sein. Wenn Sie ein Speicherleck vermuten, können Sie die folgenden Schritte ausführen:

• Überprüfen Sie die MySQL-Konfiguration sorgfältig und achten Sie darauf, dass keine Puffer/Caches überbelegt sind. In diesem Artikel erfahren Sie, wie MySQL Arbeitsspeicher für verschiedene Zwischenspeicher/Caches zuweist.
• Identifizieren Sie die Funktion/Konfiguration/Abfrage, die das Speicherproblem verursacht, und versuchen Sie, das Problem zu reproduzieren.
• Lesen Sie die MySQL-Versionshinweise der nächsten Nebenversionen in derselben Reihe, um zu prüfen, ob Fehler mit ähnlichem Verhalten behoben wurden.
• Führen Sie ein Upgrade von MySQL auf die neueste Nebenversion durch, um zu sehen, ob das Speicherproblem dadurch behoben wird.
• Durchsuchen Sie die MySQL-Datenbank bugs, um zu prüfen, ob ein ähnliches Problem von anderen Community-Nutzern gemeldet wurde.
• Erstellen Sie einen neuen Fehler mit einem reproduzierbaren Testfall, wenn für dasselbe Problem kein Fehler vorhanden ist.