Warum wird meine Amazon RDS für MySQL- oder MariaDB-Instance als voll angezeigt?

Kurzbeschreibung

Um ein Problem mit vollem Speicher zu beheben, müssen Sie zunächst den gesamten auf Ihrer DB-Instance verwendeten Speicherplatz analysieren. Der Speicherplatz auf Ihrer DB-Instance wird für Folgendes verwendet:

• Vom Benutzer erstellte Datenbanken
• Temporäre Tabellen
• Binäre Protokolle oder MySQL-Standby-Instance-Relay-Protokolle (wenn Sie eine Read Replica verwenden)
• InnoDB-Tablespace
• Allgemeine Protokolle, langsame Abfrageprotokolle und Fehlerprotokolle

Nachdem Sie festgestellt haben, was Speicherplatz belegt, können Sie Speicherplatz regenerieren. Überwachen Sie dann die FreeStorageSpace-Metrik, um zu verhindern, dass Ihnen erneut der Speicherplatz ausgeht.

Hinweis: Wenn der verfügbare Speicherplatz plötzlich abnimmt, überprüfen Sie die laufenden Abfragen auf DB-Instance-Ebene, indem Sie den Befehl SHOW FULL PROCESSLIST ausführen. Der Befehl SHOW FULL PROCESSLIST liefert Informationen über alle aktiven Verbindungen und Abfragen, die von jeder Verbindung ausgeführt werden. Um die Transaktionen zu überprüfen, die schon lange aktiv sind, führen Sie den Befehl INFORMATION_SCHEMA.INNODB_TRX oder SHOW ENGINE INNODB STATUS aus. Überprüfen Sie dann die Ausgabe.

Behebung

Analysieren des gesamten auf der DB-Instance verwendeten Speicherplatzes (vom Benutzer erstellte Datenbanken)

Führen Sie die folgende Abfrage aus, um die Größe jeder vom Benutzer erstellten Datenbank zu ermitteln:

mysql> SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB" FROM information_schema.tables GROUP BY 1 ORDER BY 2 DESC;

Um die Größe jeder Tabelle für eine bestimmte Datenbank (in Ihrer DB-Instance) zu überprüfen, führen Sie die folgende Abfrage aus:

mysql> SELECT table_schema "DB Name", table_name,(data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" from information_schema.tables where table_schema='database_name';

Um genauere Tabellengrößen in MySQL Version 5.7 und höher oder MySQL 8.0 und höher zu erhalten, verwenden Sie die folgende Abfrage:
Hinweis: Die Abfrage information_schema.files gilt nicht für MariaDB-Engines.

mysql> SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" from information_schema.files where file_name like '%/database_name/%';

Führen Sie die folgende Abfrage aus, um vollständige Speicherdetails und den ungefähren fragmentierten Speicherplatz auf Datenbank- und Tabellenebene zu erhalten:
Hinweis: Diese Abfrage gilt nicht für Tabellen, die sich in einem gemeinsam genutzten Tablespace befinden.

mysql> SELECT table_schema AS "DB_NAME", SUM(size) "DB_SIZE", SUM(fragmented_space) APPROXIMATED_FRAGMENTED_SPACE_GB FROM (SELECT table_schema, table_name, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) AS size, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2)
    AS fragmented_space FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ) AS TEMP GROUP BY DB_NAME ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

mysql> SELECT table_schema DB_NAME, table_name TABLE_NAME, ROUND((data_length+index_length+data_free)/1024/1024/1024,2) SIZE_GB, ROUND((data_length - (AVG_ROW_LENGTH*TABLE_ROWS))/1024/1024/1024,2) APPROXIMATED_FRAGMENTED_SPACE_GB from information_schema.tables
    WHERE table_type='BASE TABLE' AND table_schema NOT IN ('performance_schema', 'mysql', 'information_schema') ORDER BY APPROXIMATED_FRAGMENTED_SPACE_GB DESC;

Notieren Sie die aus diesen beiden Abfragen gewonnenen Datenbankgrößen und vergleichen Sie sie mit den Amazon CloudWatch-Metriken in Amazon RDS. Sie können dann überprüfen, ob der volle Speicher auf die Datennutzung zurückzuführen ist.

Temporäre Tabellen

Von InnoDB-Benutzern erstellte temporäre Tabellen und interne temporäre Tabellen auf der Festplatte werden in einer temporären Tablespace-Datei namens ibtmp1 erstellt. Manchmal kann die temporäre Tablespace-Datei sogar auf ibtmp2 im MySQL-Datenverzeichnis erweitert werden.

Tipp: Wenn die temporäre Tabelle (ibtmp1) zu viel Speicherplatz belegt, starten Sie die DB-Instance neu, um den Speicherplatz freizugeben.

Die Online-DDL-Operationen verwenden temporäre Protokolldateien für Folgendes:

• Aufnehmen gleichzeitiger DML
• Erstellen temporärer Sortierdateien, wenn ein Index erstellt wird
• Erstellen temporärer Zwischentabellendateien, wenn Tabellen neu erstellt werden (damit temporäre Tabellen Speicherplatz belegen können)

Hinweis: Dateigrößen des InnoDB-Tablespaces können nur mit MySQL Version 5.7 und höher oder MySQL 8.0 und höher abgefragt werden.

Führen Sie die folgende Abfrage aus, um den temporären InnoDB-Tablespace zu finden:

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibtmp%';

Um Speicherplatz zu regenerieren, der von einer globalen temporären Tablespace-Datendatei belegt wird, starten Sie den MySQL-Server oder Ihre DB-Instance neu. Weitere Informationen finden Sie unter Der temporäre Tablespace auf der MySQL-Website.

InnoDB-Tablespace

Manchmal erstellt MySQL interne temporäre Tabellen, die nicht entfernt werden können, weil eine Abfrage dazwischenkommt. Diese temporären Tabellen sind nicht Teil der Tabelle mit dem Namen „tables“ in information_schema. Weitere Informationen finden Sie unter Interne Verwendung temporärer Tabellen in MySQL auf der MySQL-Website.

Führen Sie die folgende Abfrage aus, um diese internen temporären Tabellen zu finden:

mysql> SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%#%';

Der InnoDB-System-Tablespace ist der Speicherbereich für das InnoDB-Datenwörterbuch. Zusammen mit dem Datenwörterbuch sind auch die Doublewrite-Buffer-, Change Buffer- und Undo-Protokolle im InnoDB-System-Tablespace vorhanden. Darüber hinaus kann der Tablespace Index- und Tabellendaten enthalten, wenn Tabellen im System-Tablespace erstellt werden (anstelle von Dateien pro Tabelle oder allgemeinen Tablespaces).

Führen Sie die folgende Abfrage aus, um den InnoDB-System-Tablespace zu finden:

mysql> SELECT file_name, tablespace_name, table_name, engine, index_length, total_extents, extent_size from information_schema.files WHERE file_name LIKE '%ibdata%';

Hinweis: Diese Abfrage läuft auf MySQL Version 5.7 und höher oder MySQL 8.0 und höher.

Nachdem die Größe Ihres Systemtabellenraums erhöht wurde, können Sie ihn nicht mehr reduzieren. Sie können jedoch alle Ihre InnoDB-Tabellen speichern und die Tabellen in eine neue MySQL-DB-Instance importieren. Um große System-Tablespaces zu vermeiden, sollten Sie die Verwendung von Tablespaces pro Tabelle in Betracht ziehen. Weitere Informationen finden Sie unter File-per-Table-Tablespaces auf der MySQL-Website.

Wenn Sie Innodb_file\ _per_table aktivieren, speichert jede Tabelle die Daten und den Index in einer eigenen Tablespace-Datei. Sie können den Speicherplatz (aufgrund der Fragmentierung von Datenbanken und Tabellen) regenerieren, indem Sie OPTIMIZE TABLE für diese Tabelle ausführen. Der Befehl OPTIMIZE TABLE erstellt eine neue leere Kopie Ihrer Tabelle. Dann werden Daten aus der alten Tabelle Zeile für Zeile in die neue Tabelle kopiert. Während dieses Vorgangs wird ein neuer .ibd-Tablespace erstellt und Speicherplatz zurückgewonnen. Weitere Informationen zu diesem Prozess finden Sie unter OPTIMIZE TABLE-Anweisung auf der MySQL-Website.

Wichtig: Der Befehl OPTIMIZE TABLE verwendet den Algorithmus COPY, um temporäre Tabellen zu erstellen, die dieselbe Größe wie die Originaltabelle haben. Stellen Sie sicher, dass ausreichend Speicherplatz verfügbar ist, bevor Sie diesen Befehl ausführen.

Führen Sie die folgende Befehlssyntax aus, um Ihre Tabelle zu optimieren:

mysql> OPTIMIZE TABLE <tablename>;

Sie können die Tabelle auch neu erstellen, indem Sie den folgenden Befehl ausführen:

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

Binäre Protokolle

Wenn Sie automatische Backups auf Ihrer Amazon RDS-Instance aktivieren, werden die binären Protokolle auch automatisch auf Ihrer DB-Instance aktiviert. Diese binären Protokolle werden auf der Festplatte gespeichert und verbrauchen Speicherplatz, werden jedoch bei jeder Konfiguration zur Aufbewahrung von binären Protokollen gelöscht. Der standardmäßige Binlog-Aufbewahrungswert für Ihre Instance ist ebenfalls auf „Null“ gesetzt, was bedeutet, dass die Datei sofort entfernt wird.

Um Probleme mit wenig Speicherplatz zu vermeiden, legen Sie in Amazon RDS for MySQL den entsprechenden Aufbewahrungszeitraum für binäre Protokolle fest. Sie können die Anzahl der Stunden, für die ein binäres Protokoll aufbewahrt wird, mit der Befehlssyntax mysql.rds_show\ _configuration überprüfen:

CALL mysql.rds_show_configuration;

Sie können diesen Wert auch reduzieren, um Protokolle für einen kürzeren Zeitraum aufzubewahren und so den Speicherplatz zu reduzieren, den die Protokolle belegen. Ein Wert von NULL bedeutet, dass Protokolle so schnell wie möglich gelöscht werden. Wenn es eine Standby-Instance für die aktive Instance gibt, überwachen Sie die ReplicaLag-Metrik auf der Standby-Instance. Die ReplicaLag-Metrik gibt alle Verzögerungen an, die während der Verarbeitung von binären Protokollen auf der aktiven Instance oder von Relay-Protokollen auf der Standby-Instance auftreten.

Wenn es eine Standby-Instance für die aktive Instance gibt, überwachen Sie die ReplicaLag-Metrik auf der Standby-Instance. Die ReplicaLag-Metrik gibt alle Verzögerungen an, die bei der Verarbeitung von binären Protokollen auf der aktiven Instance oder von Relay-Protokollen auf der Standby-Instance auftreten. Wenn es Probleme beim Löschen oder bei der Replikation gibt, können sich diese binären Protokolle im Laufe der Zeit ansammeln und zusätzlichen Speicherplatz beanspruchen. Verwenden Sie den Befehl SHOW BINARY LOGS, um die Anzahl der binären Protokolle auf einer Instance und die Dateigröße zu überprüfen. Weitere Informationen finden Sie unter SHOW BINARY LOGS-Anweisung auf der MySQL-Website.

Wenn die DB-Instance als Replikations-Standby-Instance fungiert, überprüfen Sie die Größe des Relay-Protokoll-Werts (Relay_Log_Space) mit dem folgenden Befehl:

SHOW SLAVE STATUS\G

MySQL-Protokolle (allgemeine Protokolle, langsame Abfrageprotokolle und Fehlerprotokolle)

Amazon RDS for MySQL stellt Protokolle (z. B. allgemeine Protokolle, langsame Abfrageprotokolle und Fehlerprotokolle) bereit, die zur Überwachung Ihrer Datenbank verwendet werden können. Fehlerprotokolle sind standardmäßig aktiv. Die allgemeinen Protokolle und langsamen Abfrageprotokolle können jedoch mithilfe einer benutzerdefinierten Parametergruppe auf der RDS-Instance aktiviert werden. Nachdem die langsamen Abfrageprotokolle und allgemeinen Protokolle aktiviert wurden, werden sie automatisch in den Tabellen slow_log und general_log in der MySQL-Datenbank gespeichert. Um die Größe von langsamen Abfragen, allgemeinen Protokollen (vom Typ „FILE“) und Fehlerprotokollen zu überprüfen, sehen Sie sich die Datenbankprotokolldateien an und listen Sie sie auf.

Wenn das langsame Abfrageprotokoll und die allgemeinen Protokolltabellen zu viel Speicherplatz belegen, verwalten Sie die tabellenbasierten MySQL-Protokolle, indem Sie die Protokolltabellen manuell rotieren. Um die alten Daten vollständig zu entfernen und Speicherplatz zu regenerieren, rufen Sie die folgenden Befehle zweimal hintereinander auf:

mysql> CALL mysql.rds_rotate_slow_log;
mysql> CALL mysql.rds_rotate_general_log;

Hinweis: Die Tabellen geben keine genaue Dateigröße der Protokolle an. Ändern Sie den Parameter so, dass der Wert von log_output für slow_log und general_log „File“ statt „Table“ ist.

Es ist auch eine bewährte Methode, Ihre Amazon RDS-DB-Instance mithilfe von Amazon CloudWatch zu überwachen. Sie können CloudWatch-Alarme für die FreeStorageSpace-Metrik einrichten, um Benachrichtigungen zu erhalten, wenn Ihr Speicherplatz unter einen bestimmten Schwellenwert fällt. Abschließend überwachen Sie die FreeStorageSpace-Metrik, indem Sie einen CloudWatch-Alarm einrichten, der Benachrichtigungen empfängt, wenn Ihre DB-Instance nur noch wenig freien Speicherplatz hat. Weitere Informationen finden Sie unter Wie kann ich CloudWatch-Alarme erstellen, um den freien Amazon RDS-Speicherplatz zu überwachen und Probleme mit vollem Speicher zu vermeiden?

Sie können auch die Amazon RDS-Speicher-Autoscaling-Funktion verwenden, um die Kapazität automatisch zu verwalten. Mit der automatischen Speicherskalierung müssen Sie den Datenbankspeicher nicht manuell hochskalieren. Weitere Informationen zur automatischen Skalierung von Amazon RDS-Speicher finden Sie unter Arbeiten mit Speicher für Amazon RDS-DB-Instances.

---

Verwandte Informationen

Wie löse ich Probleme mit meiner Amazon RDS für MySQL-DB-Instance, die mehr Speicherplatz als erwartet belegt?