Wie kann ich Probleme mit zu wenig verfügbarem Speicher in einer Datenbank von Amazon RDS für MySQL beheben?

Kurzbeschreibung

In Amazon RDS für MySQL können Sie vier Speicherzustände überwachen:

• Aktiv: Der Speicher, der aktiv von Datenbankprozessen oder Threads beansprucht wird.
• Puffer: Ein Puffer ist ein temporärer Speicherplatz im Speicher, der zum Speichern eines Datenblocks verwendet wird.
• Freier Speicher: Speicher, der zur Verwendung verfügbar ist.
• Zwischenspeicher: Zwischenspeichern ist eine Technik, bei der Daten vorübergehend im Speicher festgehalten werden, um ein schnelleres Abrufen von Daten zu ermöglichen.

Wenn Sie eine Instance in Amazon RDS für MySQL erstellen, werden standardmäßig Puffer und Zwischenspeicher zugewiesen, um den Datenbankbetrieb zu verbessern. Amazon RDS für MySQL verfügt auch über eine interne Speicherkomponente (z. B. key_buffers_size oder query_cache_size), die interne temporäre Tabellen erstellt, um bestimmte Operationen auszuführen.

Wenn Sie Amazon RDS für MySQL verwenden, sollten Sie verstehen, wie MySQL Speicher nutzt und zuweist. Nachdem Sie die Komponenten identifiziert haben, die Speicher beanspruchen, können Sie auf Instance- und Datenbankebene nach Engpässen suchen. Überwachen Sie dann diese spezifischen Messwerte und konfigurieren Sie Ihre Sitzungen für eine optimale Leistung.

Behebung

Wie MySQL den Speicher nutzt

In Amazon RDS für MySQL werden 80 bis 90 % des verfügbaren Speichers auf einer Instance mit den Standardparametern zugewiesen. Diese Zuweisung ist für die Leistung optimal. Wenn Sie jedoch Parameter festlegen, die mehr Speicher beanspruchen, sollten Sie auch andere Parameter ändern, um dies zu kompensieren, sodass weniger Speicher belegt wird.

Sie können den ungefähren Speicherverbrauch für eine DB Instance von RDS für MySQL wie folgt berechnen:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

Pufferpools

Globale Puffer und Zwischenspeicher umfassen Komponenten wie Innodb_buffer_pool_size, Innodb_log_buffer_size, key_buffer_size und query_cache_size. Der Parameter innodb_buffer_pool_size ist der Speicherbereich für RAM, in dem Innodb die Datenbanktabellen und indexbezogenen Daten zwischenspeichert. Ein größerer Pufferpool erfordert weniger I/O-Operationen, die zurück auf die Festplatte umgeleitet werden. Standardmäßig belegt innodb_buffer_pool_size maximal 75 % des verfügbaren Speichers, der einer DB Instance von Amazon RDS zugewiesen ist:

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

Stellen Sie sicher, dass Sie diesen Parameter zuerst überprüfen, um die Quelle der Speicherbelegung zu ermitteln. Erwägen Sie dann, den Wert für innodb_buffer_pool_size zu verringern, indem Sie den Parameterwert in Ihrer benutzerdefinierten Parametergruppe ändern.

Beispielsweise kann der standardmäßige DBInstanceClassMemory*3/4 auf *5/8 oder *1/2 reduziert werden. Stellen Sie sicher, dass der Wert der BufferCacheHitRatio der betreffenden Instance nicht zu niedrig ist. Wenn der Wert BufferCacheHitRatio niedrig ist, müssen Sie möglicherweise die Größe der Instance erhöhen, um mehr RAM zu erhalten. Weitere Informationen finden Sie unter Bewährte Methoden für die Konfiguration von Parametern für Amazon RDS für MySQL, Teil 1: Mit der Leistung zusammenhängende Parameter.

MySQL Threads

Speicher wird auch für jeden MySQL Thread zugewiesen, der mit einer MySQL DB Instance verbunden ist. Die folgenden Threads benötigen zugewiesenen Speicher:

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

Zusätzlich erstellt MySQL interne temporäre Tabellen, um einige Operationen auszuführen. Diese Tabellen werden zunächst als speicherbasierte Tabellen erstellt. Wenn die Tabellen die von tmp_table_size oder max_heap_table_size angegebene Größe erreichen (je nachdem, welcher Wert niedriger ist), wird die Tabelle in eine festplattenbasierte Tabelle konvertiert. Wenn mehrere Sitzungen interne temporäre Tabellen erstellen, kann es zu einer Erhöhung der Speicherbelegung kommen. Um die Speicherbelegung zu verringern, sollten Sie die Verwendung temporärer Tabellen in Ihren Abfragen vermeiden.

**Hinweis:**Wenn Sie die Grenzwerte tmp_table_size und max_heap_table_size erhöhen, können größere temporäre Tabellen im Speicher festgehalten werden. Um zu überprüfen, ob eine implizite temporäre Tabelle erstellt wurde, verwenden Sie die Variable created_tmp_tables. Weitere Informationen zu dieser Variablen finden Sie unter created_tmp_tables auf der MySQL Website.

JOIN- und SORT-Operationen

Die Speicherbelegung erhöht sich, wenn während eines JOIN- oder SORT-Operation mehrere Puffer desselben Typs wie join_buffer_size oder **sort_buffer_size ** zugewiesen werden. Beispielsweise weist MySQL einen JOIN-Puffer zu, um JOIN zwischen zwei Tabellen auszuführen. Wenn eine Abfrage JOINs mehrerer Tabellen betrifft und alle Abfragen einen JOIN-Puffer benötigen, weist MySQL einen JOIN-Puffer weniger zu als die Gesamtzahl der Tabellen. Wenn Sie Ihre Sitzungsvariablen mit einem zu hohen Wert konfigurieren, kann dies zu Problemen führen, wenn die Abfragen nicht optimiert sind. Sie können Variablen auf Sitzungsebene wie join_buffer_size und sort_buffer_size einen Mindestspeicher zuweisen. Weitere Informationen finden Sie unter Arbeiten mit DB-Parametergruppen.

Wenn Sie Masseneinfügungen in MYISAM-Tabellen durchführen, werden bulk_insert_buffer_size Bytes Speicherplatz genutzt. Weitere Informationen finden Sie unter Bewährte Methoden für die Arbeit mit MySQL-Speicher-Engines.

Das Leistungsschema

Speicher kann von Performance Schema beansprucht werden, wenn Sie Performance Schema für Performance Insights auf Amazon RDS für MySQL aktiviert haben. Wenn Performance Schema aktiviert ist, weist MySQL beim Starten der Instance und während des Serverbetriebs interne Puffer zu. Weitere Informationen darüber, wie Performance Schema Speicher nutzt, finden Sie in der MySQL-Dokumentation zum Speicherzuweisungsmodell von Performance Schema .

Neben den Tabellen von Performance Schema können Sie auch das sys-Schema von MySQL verwenden. Sie können beispielsweise das Ereignis performance_schema nutzen, um zu sehen, wie viel Speicher internen Puffern zugewiesen ist, die von Performance Schema genutzt werden. Oder Sie können eine Abfrage wie diese ausführen, um zu sehen, wie viel Speicher zugewiesen ist:

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

Speicherinstrumente werden in der Tabelle setup_instruments im Format „memory/code_area/instrument_name“ aufgeführt. Um die Speicherinstrumentierung zu aktivieren, aktualisieren Sie die Spalte ENABLED der entsprechenden Instrumente in der Tabelle setup_instruments:

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

Überwachung der Speichernutzung auf Ihrer Instance

Messwerte von Amazon CloudWatch

Überwachen Sie die Messwerte von Amazon CloudWatch für DatabaseConnections, CPUUtilization, ReadIOPS und WriteIOPS, wenn der verfügbare Speicher knapp wird.

Bei DatabaseConnections muss beachtet werden, dass jede Verbindung zur Datenbank eine gewisse Menge an zugewiesenem Speicher erfordert. Daher kann ein Anstieg der Datenbankverbindungen zu einem Abfall des verfügbaren Speichers führen. In Amazon RDS wird der weiche Grenzwert für max_connections wie folgt berechnet:

{DBInstanceClassMemory/12582880}

Überwachen Sie, ob Sie diesen weichen Grenzwert überschreiten, indem Sie die Messwerte der DatabaseConnections in Amazon CloudWatch überprüfen.

Überprüfen Sie, ob der Speicherplatz ausgelastet ist, indem Sie die Messwerte von CloudWatch für SwapUsage zusätzlich zu FreeableMemory überwachen. Wenn Sie feststellen, dass eine große Menge an Swap genutzt wird und nur noch wenig FreeableMemory zur Verfügung steht, steht Ihre Instance möglicherweise unter einer hohen Speicherauslastung. Eine hohe Speicherauslastung beeinträchtigt die Datenbankleistung. Es hat sich bewährt, die Speicherauslastung unter 95 % zu halten. Weitere Informationen finden Sie unter Warum verwendet meine Instance in Amazon RDS Swap-Speicher, obwohl ich über ausreichend Speicher verfüge?

Enhanced Monitoring

Um die Ressourcenauslastung auf einer DB Instance zu überwachen, aktivieren Sie Enhanced Monitoring. Stellen Sie dann eine Auflösung von einer oder fünf Sekunden ein (die Standardeinstellung ist 60 Sekunden). Mit Enhanced Monitoring können Sie den freien und aktiven Speicher in Echtzeit überwachen.

Sie können auch die Threads überwachen, die maximale CPU-Leistung und maximalen Speicher beanspruchen, indem Sie die Threads für Ihre DB Instance auflisten:

mysql> select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

Ordnen Sie dann thread_OS_ID der thread_ID zu:

select p.* from information_schema.processlist p, performance_schema.threads t
where p.id=t.processlist_id and t.thread_os_id=<Thread ID from EM processlist>;

Problembehandlung bei zu wenig verfügbarem Speicher

Wenn Sie Probleme mit wenig verfügbarem Speicher haben, sollten Sie die folgenden Tipps zur Problembehandlung beachten:

• Stellen Sie sicher, dass Ihrer Datenbank genügend Ressourcen zugewiesen sind, um Ihre Abfragen auszuführen. Bei Amazon RDS hängt die Menge der zugewiesenen Ressourcen vom Instance-Typ ab. Darüber hinaus können bestimmte Abfragen, wie z. B. gespeicherte Verfahren, während der Ausführung eine unbegrenzte Menge an Speicher beanspruchen.
• Vermeiden Sie lang andauernde Transaktionen, indem Sie große Abfragen in kleinere Abfragen aufteilen.
• Verwenden Sie den Befehl SHOW FULL PROCESSLIST, um alle aktiven Verbindungen und Abfragen in Ihrer Datenbank anzuzeigen. Wenn Sie eine lang andauernde Abfrage mit JOIN- oder SORT-Operationen beobachten, benötigen Sie genügend RAM, damit der Optimierer den Plan berechnen kann. Wenn Sie eine Abfrage erkennen, die eine temporäre Tabelle benötigt, benötigen Sie darüber hinaus zusätzlichen Speicher, den Sie der Tabelle zuweisen können.
• Verwenden Sie den Befehl SHOW ENGINE INNODB STATUS, um Transaktionen mit langer Laufzeit, Statistiken zur Speichernutzung und Sperren anzuzeigen. Überprüfen Sie die Ausgabe und die Einträge in BUFFER POOL AND MEMORY. Der Eintrag BUFFER POOL AND MEMORY enthält Informationen über die Speicherzuweisung für InnoDB, z. B. „Gesamtspeicherzuweisung“, „Interne Hashtabellen“ und „Pufferpoolgröße“. Der InnoDB-Status hilft auch dabei, zusätzliche Informationen zu Latches, Locks und Deadlocks zu liefern.
• Wenn Ihr Workload häufig auf Deadlocks stößt, ändern Sie den Parameter innodb_lock_wait_timeout in Ihrer benutzerdefinierten Parametergruppe. InnoDB stützt sich auf die Einstellung innodb_lock_wait_timeout, um Transaktionen rückgängig zu machen, wenn ein Deadlock auftritt.
• Um die Datenbankleistung zu optimieren, stellen Sie sicher, dass Ihre Abfragen richtig abgestimmt sind. Andernfalls kann es zu Leistungsproblemen und längeren Wartezeiten kommen.
• Verwenden Sie Amazon RDS Performance Insights, um DB Instances zu überwachen und problematische Abfragen zu erkennen.
• Überwachen Sie die Messwerte von Amazon CloudWatch wie CPU-Auslastung, IOPS, Speicher und Swap-Auslastung, damit die Instance nicht gedrosselt wird.
• Stellen Sie einen CloudWatch-Alarm für den Messwert FreeableMemory ein, sodass Sie eine Benachrichtigung erhalten, wenn der verfügbare Speicherplatz 95 % erreicht. Es hat sich bewährt, mindestens 5 % des Instance-Speichers frei zu halten.
• Aktualisieren Sie Ihre Instance regelmäßig auf eine neuere Nebenversion von MySQL. Ältere Nebenversionen enthalten mit größerer Wahrscheinlichkeit Fehler im Zusammenhang mit Speicherverlusten.

---

Weitere Informationen

Überblick über die Überwachung von Amazon RDS

Warum verwendet eine Instance in Amazon RDS DB Swap-Speicher, obwohl ich über ausreichend Speicher verfüge?