Come posso risolvere i problemi relativi a un'istanza RDS per MySQL o MariaDB che indica lo spazio di archiviazione pieno?

7 minuti di lettura

Desidero risolvere i problemi relativi a un'istanza Amazon Relational Database Service (Amazon RDS) per MySQL o MariaDB che indica lo spazio di archiviazione pieno.

Breve descrizione

Per risolvere i problemi di un'istanza Amazon RDS per MySQL o MariaDB che indica lo spazio di archiviazione pieno, controlla lo spazio totale utilizzato nell'istanza database per identificare cosa utilizza lo spazio. Lo spazio dell'istanza database può essere utilizzato per i seguenti oggetti:

Database creati dall'utente
Tablespace temporaneo
Log binari o relay log delle istanze di standby MySQL (se si utilizza una replica di lettura)
Spazio tabelle InnoDB
Log generali, log delle query lente e log degli errori

Dopo aver controllato lo spazio di archiviazione e aver identificato cosa lo sta utilizzando, puoi recuperare spazio. Dopodiché puoi monitorare la metrica FreeStorageSpace per prevenire ulteriori problemi di spazio di archiviazione.

Nota: se si verifica un'improvvisa diminuzione dello spazio di archiviazione disponibile, esegui il comando SHOW FULL PROCESSLIST per controllare le query a livello di istanza database. Il comando SHOW FULL PROCESSLIST fornisce informazioni su tutte le connessioni attive e le query eseguite da ciascuna connessione. Per esaminare le transazioni attive da molto tempo, prima esegui il comando INFORMATION_SCHEMA.INNODB_TRX o SHOW ENGINE INNODB STATUS. Quindi rivedi l'output.

Risoluzione

Per risolvere i problemi relativi a un'istanza Amazon RDS per MySQL o MariaDB che indica lo spazio di archiviazione pieno, completa i seguenti passaggi:

Controlla lo spazio totale utilizzato nell'istanza database MySQL

Identifica le dimensioni di ogni database creato dall'utente

SELECT SUBSTRING_INDEX(TABLESPACE_NAME,"/",1) AS DATABASE_NAME, ROUND((DATA_FREE/1024/1024/1024),3) AS 'REUSABLE (GB)', ROUND(SUM((TOTAL_EXTENTS * EXTENT_SIZE)/1024/1024/1024),3) AS 'TOTAL (GB)' FROM INFORMATION_SCHEMA.FILES GROUP BY DATABASE_NAME ORDER BY 'TOTAL (GB)'  DESC;

Verifica le dimensioni di ogni tabella per un determinato database utente:
Nota: sostituisci example-database-name con il nome del tuo database.

SELECT SUBSTRING_INDEX(TABLESPACE_NAME,"/",-1) as 'TABLE_NAME', ROUND((total_extents * extent_size)/1024/1024/1024,3) AS "TableSizeinGB" from information_schema.files WHERE FILE_NAME LIKE 'example-database-name';

Controlla lo spazio totale utilizzato nell'istanza MariaDB

Identifica la dimensione di ogni database creato dall'utente:

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;

Verifica le dimensioni di ogni tabella per un determinato database utente:
Nota: sostituisci example-database con il nome del database e example-table con il nome della tabella.

mysql> SELECT table_schema "example-database", example-table,(data_length + index_length)/1024/1024/1024 AS "TableSizeinGB" from information_schema.tables where table_schema='database_name';

Controlla le tabelle temporanee

Le tabelle temporanee create da un utente InnoDB e le tabelle temporanee interne su disco vengono create in un file tablespace temporaneo denominato ibtmp1. I file tablespace temporanei possono estendersi a ibtmp2 nella directory dei dati MySQL. Se la tabella temporanea ibtmp1 utilizza uno spazio di archiviazione eccessivo, riavvia l'istanza database per liberare spazio.

Nota: è possibile utilizzare solo MySQL versione 5.7 e successive o MySQL versione 8.0 e successive per eseguire query sulle dimensioni dei file tablespace di InnoDB.

Identifica il tablespace temporaneo di InnoDB:

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

Per recuperare spazio su disco occupato da un file di dati tablespace temporaneo globale, riavvia il server MySQL o riavvia l'istanza database. Per ulteriori informazioni, consulta The temporary tablespace (Il tablespace temporaneo) sul sito web MySQL.

Controlla il tablespace di InnoDB

MySQL potrebbe creare tabelle temporanee interne che non possono essere rimosse a causa di una query. Queste tabelle temporanee non fanno parte della tabella denominata tables all'interno di information\ _schema. Per ulteriori informazioni, consulta Internal temporary table use in MySQL (Utilizzo delle tabelle temporanee interne in MySQL) sul sito web MySQL.

Identifica le tabelle temporanee interne:

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

Identifica il tablespace di sistema di InnoDB:

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

Nota: la query precedente è supportata in MySQL versione 5.7 e successive o in MySQL versione 8.0 e successive.

Quando la dimensione del tablespace di sistema aumenta, non è possibile ridurla. Tuttavia, puoi scaricare tutte le tabelle InnoDB e importarle in una nuova istanza database MySQL. Per evitare tablespace di sistema di grandi dimensioni, utilizza tablespace file-per-table. Per ulteriori informazioni, consulta File-per-table tablespaces (Tablespace file-per-table) sul sito web MySQL.

Se attivi Innodb_file_per_table, ogni tabella memorizza i dati e l'indice nel proprio file tablespace. Per recuperare lo spazio, esegui OPTIMIZE TABLE. Per ulteriori informazioni, consulta OPTIMIZE TABLE statement (Istruzione OPTIMIZE TABLE) sul sito web MySQL.

Nota: il comando OPTIMIZE TABLE utilizza l'algoritmo COPY per creare tabelle temporanee della stessa dimensione di quella originale. Assicurati di avere spazio su disco disponibile prima di eseguire OPTIMIZE TABLE.

Per ottimizzare la tabella, esegui questo comando:
Nota: sostituisci example-table-name con la tabella che desideri ottimizzare.

mysql> OPTIMIZE TABLE example-table-name;

(Facoltativo) Per ricostruire la tabella, esegui questo comando:
Nota: sostituisci example-table-name con la tabella che desideri ottimizzare.

mysql> ALTER TABLE example-table-name ENGINE=INNODB;

Controlla i log binari

Se attivi i backup automatici su un’istanza Amazon RDS, i log binari vengono attivati automaticamente sull’istanza database. I log binari vengono archiviati su disco e occupano spazio di archiviazione, ma vengono eliminati a ogni configurazione della conservazione dei log binari. Il valore di conservazione binlog predefinito per l'istanza è impostato su Null e i file vengono rimossi immediatamente.

Per evitare problemi di spazio di archiviazione insufficiente, imposta il valore appropriato per il periodo di conservazione dei log binari in Amazon RDS per MySQL.

Per visualizzare il numero di ore di conservazione di un log binario, esegui il comando mysql.rds_show_configuration:

CALL mysql.rds_show_configuration;

Per ridurre la quantità di spazio utilizzata dai log binari, riduci il numero di ore di conservazione di un log binario. Un valore NULL rimuove immediatamente i log.

Se è presente un'istanza di standby per l'istanza attiva, monitora il parametro ReplicaLag sull'istanza di standby. Il parametro ReplicaLag indica eventuali ritardi che si verificano durante l'elaborazione del log binario sull'istanza attiva o i relay log sull'istanza di standby.

In caso di problemi di eliminazione o replica, i log binari possono accumularsi nel tempo, consumando ulteriore spazio su disco. Per verificare il numero di log binari su un'istanza e la dimensione del file, utilizza il comando SHOW BINARY LOGS. Per ulteriori informazioni, consulta SHOW BINARY LOGS statement (Istruzione SHOW BINARY LOGS) sul sito web MySQL.

Se l'istanza database funge da istanza di standby di replica, seleziona Relay_Log_Space per visualizzare la dimensione dei relay log:

SHOW SLAVE STATUS\G

Controlla i log MySQL (log generali, log delle query lente e log degli errori)

Per verificare la dimensione dei log delle query lente, dei log generali del tipo FILE e dei log degli errori, visualizza ed elenca i file di log del database. Se le tabelle dei log delle query lente e dei log generali utilizzano uno spazio di archiviazione eccessivo, ruota manualmente le tabelle dei log per gestire i log MySQL basati su tabelle.

Per rimuovere i vecchi dati e recuperare spazio su disco, esegui questi comandi due volte di seguito:

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

Nota: le tabelle non forniscono una dimensione accurata dei file di log. Modifica il valore del parametro log_output in File per slow_log e general_log.