Como soluciono problemas de uma instância do RDS para MySQL ou MariaDB que mostra armazenamento cheio?

Breve descrição

Para solucionar problemas em uma instância do Amazon RDS para MySQL ou MariaDB que mostra espaço de armazenamento cheio, verifique o espaço total usado na sua instância de banco de dados para identificar o que usa o espaço. É possível usar o espaço em sua instância de banco de dados para os seguintes objetos:

• Bancos de dados criados pelo usuário
• Tabelas temporárias
• Registros binários ou registros de retransmissão de instância standby do MySQL (se usar uma réplica de leitura)
• Espaço de tabela InnoDB
• Logs gerais, logs de consultas lentas e logs de erros

Depois de verificar seu espaço de armazenamento e identificar o que está usando o espaço, é possível recuperar espaço. Em seguida, é possível monitorar a métrica FreeStorageSpace para evitar mais problemas de espaço de armazenamento.

Observação: se houver uma diminuição repentina no armazenamento disponível, verifique as consultas no nível da instância de banco de dados executando o comando SHOW FULL PROCESSLIST. O comando SHOW FULL PROCESSLIST fornece informações sobre todas as conexões e consultas ativas que são realizadas por cada conexão. Para revisar as transações que estão ativas há muito tempo, primeiro execute o comando INFORMATION_SCHEMA.INNODB_TRX ou SHOW ENGINE INNODB STATUS. Em seguida, analise a saída.

Resolução

Para solucionar problemas em uma instância do Amazon RDS para MySQL ou MariaDB que mostra armazenamento cheio, conclua as seguintes etapas:

Verifique o espaço total usado em sua instância de banco de dados MySQL

Identifique o tamanho de cada banco de dados criado pelo usuário

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;

Verifique o tamanho de cada tabela para um banco de dados de usuário que você especificar:
Observação: substitua example-database-name pelo nome do seu banco de dados.

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

Verifique o espaço total usado na sua instância do MariaDB

Identifique o tamanho de cada banco de dados criado pelo usuário:

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;

Verifique o tamanho de cada tabela para um banco de dados de usuário que você especificar:
Observação: substitua example-database pelo nome do banco de dados e example-table pelo nome da tabela.

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

Verifique suas tabelas temporárias

As tabelas temporárias criadas pelo usuário do InnoDB e as tabelas temporárias internas em disco são criadas em um arquivo tablespace temporário chamado ibtmp1. Os arquivos de tablespace temporários podem se estender até ibtmp2 no diretório de dados do MySQL. Se a tabela temporária ibtmp1 usar armazenamento excessivo, reinicialize a instância de banco de dados para liberar o espaço.

Observação: é possível usar somente as versões 5.7 e posteriores do MySQL ou as versões 8.0 e posteriores do MySQL para consultar os tamanhos dos arquivos na tablespace do InnoDB.

Identifique a tablespace temporária do 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%';

Para recuperar o espaço em disco ocupado por um arquivo de dados de tablespace temporário global, reinicie o servidor MySQL ou reinicie sua instância de banco de dados. Para obter mais informações, consulte Tablespace temporária no site do MySQL.

Verifique sua tablespace do InnoDB

O MySQL pode criar tabelas temporárias internas que não podem ser removidas por causa de uma consulta. Essas tabelas temporárias não fazem parte da tabela chamada tabelas dentro de information_schema. Para obter mais informações, consulte Uso interno de tabelas temporárias no MySQL no site do MySQL.

Identifique as tabelas temporárias internas:

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

Identifique a tablespace do 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%';

Observação: a consulta anterior tem suporte nas versões 5.7 e posteriores do MySQL ou nas versões 8.0 e posteriores do MySQL.

Quando o tamanho da tablespace do seu sistema aumenta, não é possível diminuí-lo. No entanto, é possível despejar todas as suas tabelas do InnoDB e importá-las para uma nova instância de banco de dados MySQL. Para evitar grandes tablespaces do sistema, use tablespaces file-per-table. Para obter mais informações, consulte tablespaces file-per-table no site do MySQL.

Se você ativar o Innodb_file_per_table, cada tabela armazenará os dados e o índice em seu próprio arquivo de tablespace. Para recuperar o espaço, execute OPTIMIZE TABLE. Para mais informações, consulte a instrução OPTIMIZE TABLE no site do MySQL.

Observação: o comando OPTIMIZE TABLE usa o algoritmo COPY para criar tabelas temporárias do mesmo tamanho da tabela original. Verifique se você tem espaço em disco disponível antes de executar OPTIMIZE TABLE.

Para otimizar sua tabela, execute o seguinte comando:
Observação: substitua example-table-name pela tabela que você deseja otimizar.

mysql> OPTIMIZE TABLE example-table-name;

(Opcional) Para reconstruir a tabela, execute o seguinte comando:
Observação: substitua example-table-name pela tabela que você deseja otimizar.

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

Verifique seus logs binários

Se você ativar backups automatizados em sua instância do Amazon RDS, os logs binários também serão ativados automaticamente em sua instância de banco de dados. Logs binários são armazenados no disco e consomem espaço de armazenamento, mas são eliminados em cada configuração de retenção de logs binários. O valor padrão de retenção do binlog para sua instância é definido como Null e os arquivos são removidos imediatamente.

Para evitar problemas de pouco espaço de armazenamento, defina um valor apropriado para o período de retenção de log binário no Amazon RDS para MySQL.

É possível visualizar o número de horas em que um log binário é retido executando o comando mysql.rds_show_configuration:

CALL mysql.rds_show_configuration;

Para reduzir a quantidade de espaço que os logs binários usam, reduza o número de horas em que um log binário é retido. Um valor de NULL remove os logs imediatamente.

Se houver uma instância em espera para a instância ativa, monitore a métrica ReplicaLag na instância em espera. A métrica ReplicaLag indica quaisquer atrasos que ocorrem durante o processamento do log binário na instância ativa ou dos logs de réplica na instância em espera.

Se houver problemas de limpeza ou replicação, os logs binários podem se acumular com o tempo, consumindo mais espaço em disco. Para verificar o número de logs binários em uma instância e o tamanho do arquivo, use o comando SHOW BINARY LOGS. Para obter mais informações, consulte declaração SHOW BINARY LOGS no site do MySQL.

Se a instância de banco de dados atuar como uma instância em espera de replicação, marque Relay_Log_Space para ver o tamanho dos logs de réplica:

SHOW SLAVE STATUS\G

Verifique seus logs do MySQL (logs gerais, logs de consultas lentas e logs de erros)

Para verificar o tamanho dos logs de consultas lentas, gerais do tipo FILE e logs de erros, visualize e liste os arquivos de log do banco de dados. Se o log de consultas lentas e as tabelas de logs gerais estiverem usando armazenamento excessivo, gerencie os logs do MySQL baseados em tabelas alterne manualmente as tabelas de log.

Para remover os dados antigos e recuperar seu espaço em disco, execute os seguintes comandos duas vezes consecutivas:

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

Observação: as tabelas não fornecem um tamanho de arquivo preciso dos logs. Modifique o valor do parâmetro log_output para File para slow_log e general_log.

Monitore e escale sua instância de banco de dados Amazon RDS

Para monitorar e escalar sua instância do Amazon RDS, execute as seguintes ações:

• Use o Amazon CloudWatch para monitorar sua instância de banco de dados Amazon RDS.
• Configure os alarmes do CloudWatch na métrica FreeStorageSpace para receber alertas quando seu espaço de armazenamento diminuir para um valor limite especificado. Para obter mais informações, consulte Como criar alarmes do CloudWatch para monitorar o espaço de armazenamento gratuito do Amazon RDS e evitar problemas de armazenamento cheio?
• Usar o atributo de escalonamento automático de armazenamento do Amazon RDS para gerenciar a capacidade automaticamente.