Perché la mia istanza Amazon RDS per MySQL o MariaDB viene visualizzata con lo spazio di archiviazione pieno?

Breve descrizione

Per risolvere il problema dello spazio di archiviazione pieno, devi prima analizzare lo spazio totale utilizzato nell'istanza database. Lo spazio sull'istanza database viene utilizzato per quanto segue:

• Database creati dagli utenti
• 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 identificato cosa sta utilizzando lo spazio di archiviazione, puoi recuperarlo. Quindi, monitora il parametro FreeStorageSpace per evitare di esaurire nuovamente la memoria.

Nota: se si verifica un'improvvisa diminuzione dello spazio di archiviazione disponibile, controlla le query in corso a livello di istanza database eseguendo il comando SHOW FULL PROCESSLIST. 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, esegui il comando INFORMATION_SCHEMA.INNODB_TRX o SHOW ENGINE INNODB STATUS. Quindi, rivedi l'output.

Risoluzione

Analizza lo spazio totale utilizzato sull'istanza database (database creati dall'utente)

Per trovare la dimensione di ogni database creato dall'utente, esegui la seguente query:

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;

Per verificare la dimensione di ogni tabella di un particolare database (nell'istanza database), esegui la seguente query:

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';

Per ottenere dimensioni più accurate delle tabelle in MySQL versione 5.7 e successive o MySQL 8.0 e successive, usa la seguente query:
Nota: la query information_schema.files non è applicabile ai motori MariaDB.

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/%';

Per ottenere dettagli completi sullo spazio di archiviazione e uno spazio frammentato approssimativo a livello di database e tabella, esegui la seguente query:
Nota: questa query non è applicabile alle tabelle che risiedono in un tablespace condiviso.

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;

Registra le dimensioni del database acquisite da queste due query e confrontale con i parametri di Amazon CloudWatch in Amazon RDS. È quindi possibile verificare se l'archiviazione completa è causata dall'utilizzo dei dati.

Tablespace temporaneo

Le tabelle temporanee create dagli utenti di InnoDB e le tabelle temporanee interne su disco vengono create in un file tablespace temporaneo denominato ibtmp1. A volte, il file tablespace temporaneo può persino estendersi a ibtmp2 nella directory dei dati MySQL.

Suggerimento: se la tabella temporanea (ibtmp1) utilizza uno spazio di archiviazione eccessivo, riavvia l'istanza database per liberare spazio.

Le operazioni DDL online utilizzano file di log temporanei per quanto segue:

• Registrazione simultanea di DML
• Creazione di file di ordinamento temporanei quando viene creato un indice
• Creazione di file temporanei di tabelle intermedie quando le tabelle vengono ricostruite (in modo che le tabelle temporanee possano occupare spazio di archiviazione)

Nota: le dimensioni dei file tablespace InnoDB possono essere interrogate solo utilizzando MySQL versione 5.7 e successive o MySQL 8.0 e successive.

Per trovare il tablespace temporaneo di InnoDB, esegui la seguente query:

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 dati tablespace temporaneo globale, riavvia il server MySQL o riavvia l'istanza database. Per ulteriori informazioni, consulta The temporary tablespace sul sito web di MySQL.

Spazio tabella InnoDB

A volte MySQL crea tabelle temporanee interne che non possono essere rimosse perché è in corso una query. Queste tabelle temporanee non fanno parte della tabella denominata "tables" all'interno di information_schema. Per ulteriori informazioni, vedi Internal temporary table use in MySQL sul sito web di MySQL.

Esegui la seguente query per trovare queste tabelle temporanee interne:

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

Il tablespace di sistema InnoDB è l'area di archiviazione per il dizionario dati InnoDB. Insieme al dizionario dati, nel tablespace di sistema InnoDB sono presenti anche il doublewrite buffer, il change-buffer e i log di annullamento. Inoltre, il tablespace potrebbe contenere dati degli indici e delle tabelle se queste vengono create nel tablespace di sistema (anziché nei tablespace tipo file-per-table o generici).

Esegui la seguente query per trovare il tablespace del sistema 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: questa query viene eseguita su MySQL versione 5.7 e successive o MySQL 8.0 e successive.

Dopo aver aumentato le dimensioni dello spazio nella tabella di sistema, non è possibile ridurlo. Tuttavia, puoi scaricare tutte le tue tabelle InnoDB e importarle in una nuova istanza database MySQL. Per evitare tablespace di sistema di grandi dimensioni, prendi in considerazione l'uso di tablespace di tipo file-per-table. Per ulteriori informazioni, consulta File-per-table tablespaces sul sito web di MySQL.

Se abiliti Innodb_file_per_table, ogni tabella memorizzerà i dati e l'indice nel proprio file tablespace. È possibile recuperare lo spazio (dalla frammentazione su database e tabelle) eseguendo OPTIMIZE TABLE su quella tabella. Il comando OPTIMIZE TABLE crea una nuova copia vuota della tabella. Quindi, i dati della vecchia tabella vengono copiati riga per riga nella nuova tabella. Durante questo processo, viene creato un nuovo tablespace .ibd e viene recuperato spazio. Per ulteriori informazioni su questo processo, consulta OPTIMIZE TABLE statement sul sito web di MySQL.

Importante: il comando OPTIMIZE TABLE utilizza l'algoritmo COPY per creare tabelle temporanee delle stesse dimensioni di quella originale. Prima di eseguire questo comando, verifica di disporre di abbastanza spazio su disco.

Per ottimizzare la tabella, esegui la seguente sintassi di comando:

mysql> OPTIMIZE TABLE <tablename>;

In alternativa, puoi ricostruire la tabella eseguendo il comando seguente:

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

Log binari

Se attivi i backup automatici sull’istanza Amazon RDS, i log binari vengono attivati automaticamente anche sull’istanza database. Questi log binari vengono archiviati sul disco e occupano spazio di archiviazione, ma vengono eliminati a ogni configurazione della conservazione dei log binari. Anche il valore di conservazione del binlog predefinito per l'istanza è impostato su "Null", il che significa che il file viene rimosso immediatamente.

Per evitare problemi di spazio di archiviazione insufficiente, imposta il periodo di conservazione dei log binari appropriato in Amazon RDS per MySQL. È possibile verificare il numero di ore di conservazione di un log binario con la sintassi del comando mysql.rds_show_configuration:

CALL mysql.rds_show_configuration;

È inoltre possibile ridurre questo valore per conservare i log per un periodo più breve e ridurre la quantità di spazio utilizzata dagli stessi. Un valore NULL indica che i log vengono eliminati il prima possibile. 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.

Se è presente un'istanza di standby per l'istanza attiva, monitora il parametro ReplicaLag sull'istanza di standby. Il parametro ReplicaLag indica eventuali ritardi durante l'eliminazione del log binario sull'istanza attiva e del relay log sull'istanza di standby. In caso di problemi di eliminazione o replica, questi 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, utilizzare il comando SHOW BINARY LOGS. Per ulteriori informazioni, consulta SHOW BINARY LOGS statement sul sito Web di MySQL.

Se l'istanza database funge da istanza di standby di replica, controlla la dimensione del valore dei relay log (Relay_Log_Space) utilizzando il seguente comando:

SHOW SLAVE STATUS\G

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

Amazon RDS per MySQL fornisce log (come log generali, log delle query lente e log degli errori) che possono essere utilizzati per monitorare il database. I log degli errori sono attivi per impostazione predefinita. Tuttavia, i log generali e i log delle query lente possono essere attivati utilizzando un gruppo di parametri personalizzato sull'istanza RDS. Una volta attivati i log delle query lente e i log generali, vengono automaticamente archiviati nelle tabelle slow_log e general_log tables all'interno del database MySQL. Per verificare le dimensioni di eventuali query lente, log generali (di tipo "FILE") e log degli errori, visualizza ed elenca i file di log del database.

Se il log delle query lente e le tabelle di log generali utilizzano uno spazio di archiviazione eccessivo, gestisci i log MySQL basati su tabelle ruotando manualmente le tabelle dei log. Per rimuovere completamente i vecchi dati e recuperare spazio su disco, chiama i seguenti 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 parametro in modo che il valore di log_output per slow_log e general_log sia "File" anziché "Table".

È inoltre consigliabile monitorare l'istanza database di Amazon RDS utilizzando Amazon CloudWatch. Puoi impostare gli allarmi di CloudWatch sul parametro FreeStorageSpace per ricevere avvisi ogni volta che lo spazio di archiviazione scende al di sotto di un determinato valore soglia. Infine, monitora il parametro FreeStorageSpace impostando un allarme CloudWatch per ricevere notifiche ogni volta che l'istanza database ha poco spazio libero. Per maggiori informazioni, consulta Come posso creare avvisi CloudWatch per monitorare lo spazio di archiviazione libero su Amazon RDS e prevenire problemi relativi allo spazio di archiviazione completo?

Inoltre, puoi utilizzare la funzionalità di scalabilità automatica per lo storage di Amazon RDS per gestire automaticamente la capacità. Con la scalabilità automatica dello spazio di archiviazione, non è necessario aumentare verticalmente lo spazio di archiviazione del database manualmente. Per ulteriori informazioni sulla scalabilità automatica dello spazio di archiviazione di Amazon RDS, consulta Working with storage for Amazon RDS DB instances.

---

Informazioni correlate

Perché la mia istanza database Amazon RDS per MySQL utilizza più spazio di archiviazione del previsto?