Fehlerbehebung bei MySQL-Arbeitsspeicherproblemen

Eine OOM-Bedingung (Out-of-Memory) 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, den der Linux-Kernel verwendet, wenn der Arbeitsspeicher des Systems kritisch niedrig ist und der Datenbankprozess beendet wird. 

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

• Der MySQL-Server ist ausgefallen (während die Wiederherstellung nach einem Absturz abgeschlossen wird). Dies kann einige Minuten dauern. Dies hat Auswirkungen auf Nutzer und Unternehmen.

• Der MySQL-Server benötigt zusätzliche Zeit, um die Puffer und Caches vollständig aufzuwärmen, um die Spitzenleistung zu erreichen
• Möglichkeit einer Datenbeschädigung aufgrund eines unerwarteten Absturzes

MySQL hat in performance_schema ab Version 5.7 eine Speicherinstrumentierung hinzugefügt, die das Verständnis der MySQL-Arbeitsspeicherzuweisung erleichtert. In diesem Artikel erfahren Sie, wie Sie MySQL-Speicherprobleme mit 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 führt das Datenbankprogramm aus, 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 Dienst-Agents und weist den verbleibenden Arbeitsspeicher für Datenbankprogramme wie MySQLd zu. 

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

Bei Instanzen mit 10 GB RAM und mehr ist der MySQL-Prozess auf 92% des Instanz-RAM beschränkt und der InnoDB-Pufferpool ist im Bereich von 75 bis 80% des für den MySQL-Prozess verfügbaren Arbeitsspeichers konfiguriert. Zusätzlich zum 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 MySQL-Speicherauslastung das konfigurierte Limit des MySQL-Prozesses überschreitet, bricht der Linux OOM-Killer den MySQL-Prozess ab, da er der größte Arbeitsspeichernutzer auf der Instanz ist.

performance_schema ist eine MySQL-Funktion zum Überwachen 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. Es ist standardmäßig in Instanzen mit mindestens 15 GB RAM aktiviert, ab der MySQL 8.0-Version. 

„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 verbunden und hat auch Auswirkungen auf den Arbeitsspeicher. Informationen zur Arbeitsspeicherzuweisung finden Sie unter Arbeitsspeicherzuweisungsmodell für das Leistungsschema. 

Die Arbeitsspeicherinstrumentierung „performance_schema“ hilft beim Überwachen der Arbeitsspeichernutzung 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 standardmäßig einige, aber nicht alle Speicherinstrumente. Sie können sie aktivieren, indem Sie die Spalte AKTIVIERT in der Tabelle performance_schema.setup_instruments 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 Speicherinstrumente während der Laufzeit aktivieren, werden nur die Arbeitsspeicherobjekte gezählt, die nach der Aktivierung der Instrumentierung erstellt wurden. Daher empfiehlt es sich zu warten, bis der Server genügend Daten für die Fehlerbehebung 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, die performance_schema-Daten in leicht verständlichen Formen zusammenfassen. Die meisten dieser Ansichten werden paarweise dargestellt. Sie haben denselben Namen und eine 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> verwenden 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 eine Ausgabe liefern, die nutzerfreundlicher und leichter lesbar ist. Die Ansicht mit dem Präfix x$, die dieselben Werte in Rohform anzeigt, ist eher für die Verwendung mit anderen Tools vorgesehen, die eine eigene Verarbeitung der Daten durchführen.

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

Bevor wir auf die einzelnen Zuweisungstypen eingehen, sollten Sie sich einen Überblick über die Arbeitsspeichernutzung für jeden Codebereich wie Innodb, sql, performance_schema und mehr verschaffen. 

Die folgende Abfrage aggregiert den aktuell zugewiesenen Arbeitsspeicher 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 bytes    |

| memory/csv                |   88 bytes    |

| memory/blackhole          |   88 bytes    |

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

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.

Jetzt zeigt die Abfrage von sys.memory_global_by_current_bytes direkt die Arbeitsspeichernutzung für interne MySQL-Strukturen an, wie in der Spalte event_name beschrieben. Wenn Sie Probleme mit der ungewöhnlichen Erhöhung des Arbeitsspeichers beheben, können Sie die Quelle anhand der Ausgabe dieser Ansicht ermitteln.

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.

In dieser Ansicht wird die nach Nutzer gruppierte Arbeitsspeichernutzung zusammengefasst. Wenn ein Nutzer mehrere Verbindungen hat, 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 bzw. jeden Job einen anderen Nutzer verwenden (empfohlen), können Sie mit dieser Ansicht ermitteln, ob eine bestimmte Anwendung oder ein bestimmter Job viel Arbeitsspeicher belegt.

In dieser Ansicht wird die Arbeitsspeichernutzung nach Hostnamen gruppiert.

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. Heutzutage ist es sehr üblich, mehrere Client-/Anwendungsserver zu haben. Mit dieser Ansicht können Sie feststellen, ob ein bestimmter Client oder eine bestimmte Arbeitslast zu einer hohen Arbeitsspeichernutzung führt. Sie können die Änderung auch zuerst auf einem Client/Anwendungsserver bereitstellen, ihre Auswirkungen auf den Arbeitsspeicher überwachen (durch Vergleich der Arbeitsspeichernutzung mit anderen Clienthosts) und fundierte Entscheidungen treffen.

In dieser Ansicht wird die Arbeitsspeichernutzung nach Thread gruppiert. So finden Sie die Threads, die den meisten Arbeitsspeicher benötigen.

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 allgemeinen Logs mithilfe von „thread_id“ filtern und die Sitzungen/Abfragen ermitteln, die zum Arbeitsspeicherwachstum geführt haben.

Die Fehlerbehebung bei hoher Arbeitsspeichernutzung ist eine schwierige Aufgabe. Glücklicherweise hilft die Speicherinstrumentierung „performance_schema“ Nutzern, zu verstehen, wie der Arbeitsspeicher von internen MySQL-Strukturen genutzt wird. Die Sysschema-Ansichten erleichtern die Interpretation der Informationen. Wenn Speicherprobleme auftreten, hilft Ihnen diese Funktion dabei, die Ursache des Problems zu finden. Sie können sie auch beheben, indem Sie die Konfiguration oder Abfrage ändern.

Beispiele:

1. Wenn interne temporäre Tabellen viel Arbeitsspeicher benötigen, können Sie entweder die Größe temporärer Tabellen mit „tmp_table_size“ begrenzen oder „innodb_buffer_pool_size“ reduzieren, um Platz für Zwischenspeicher pro Sitzung zu schaffen.
2. Wenn ein bestimmter Thread viel Arbeitsspeicher belegt, können Sie langsame Abfragelogs mit „thread_id“ filtern, um problematische Abfragen zu ermitteln und sie zu optimieren, um den Arbeitsspeicherbedarf für diesen Thread bzw. 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:

• Prüfen Sie die MySQL-Konfiguration sorgfältig und achten Sie darauf, dass keine Puffer/Caches zu viel zugewiesen sind. In diesem Artikel erfahren Sie, wie MySQL Arbeitsspeicher für verschiedene Puffer/Caches zuweist.
• Ermitteln Sie die Funktion, Konfiguration oder Abfrage, die das Speicherproblem verursacht, und versuchen Sie, das Problem zu reproduzieren.
• Sehen Sie sich die MySQL-Versionshinweise der nächsten Nebenversionen derselben Reihe an, um festzustellen, ob Fehler mit einem ähnlichen Verhalten behoben wurden. 
• Führen Sie ein Upgrade von MySQL auf die neueste Nebenversion durch, um festzustellen, ob das Speicherproblem dadurch behoben wird.
• Suchen Sie in der MySQL-Datenbank für Bugs, um herauszufinden, ob ein ähnliches Problem von anderen Community-Nutzern gemeldet wurde.
• Erstellen Sie einen neuen Fehler mit einem reproduzierbaren Testlauf, wenn für dasselbe Problem kein Fehler vorhanden ist.