Por que minha instância do Amazon RDS para MySQL ou MariaDB está sendo exibida como armazenamento cheio?

Breve descrição

Para solucionar um problema de armazenamento completo, primeiro você deve analisar o espaço total usado em sua instância de banco de dados. O espaço em sua instância de banco de dados é usado para o seguinte:

• 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
• Registros gerais, registros de consultas lentas e registros de erros

Depois de identificar o que está usando espaço de armazenamento, você pode recuperar espaço de armazenamento. Em seguida, monitore a métrica FreeStorageSpace para evitar ficar sem espaço novamente.

Observação: se houver uma diminuição repentina no armazenamento disponível, verifique as consultas em andamento 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, execute o comando INFORMATION_SCHEMA.INNODB_TRX ou SHOW ENGINE INNODB STATUS. Em seguida, analise a saída.

Resolução

Analise o espaço total usado na instância de banco de dados (bancos de dados criados pelo usuário)

Para encontrar o tamanho de cada banco de dados criado pelo usuário, execute a seguinte consulta:

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;

Para verificar o tamanho de cada tabela de um banco de dados específico (em sua instância de banco de dados), execute a seguinte consulta:

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

Para obter tamanhos de tabelas mais precisos no MySQL versão 5.7 e superior, ou no MySQL 8.0 e superior, use a seguinte consulta:
Observação: a consulta information\ _schema.files não é aplicável aos mecanismos 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/%';

Para obter detalhes completos do armazenamento e aproximar o espaço fragmentado no nível do banco de dados e da tabela, execute a seguinte consulta:
Observação: essa consulta não se aplica às tabelas que residem em um espaço de tabela compartilhado.

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;

Registre os tamanhos dos bancos de dados adquiridos com essas duas consultas e compare-os com as métricas do Amazon CloudWatch no Amazon RDS. Em seguida, você pode confirmar se o armazenamento total é causado pelo uso de dados.

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 de espaço de tabela temporário chamado ibtmp1. Às vezes, o arquivo de espaço de tabela temporário pode até mesmo se estender para ibtmp2 no diretório de dados do MySQL.

Dica: se a tabela temporária (ibtmp1) usar armazenamento excessivo, reinicialize a instância de banco de dados para liberar o espaço.

As operações DDL on-line usam arquivos de log temporários para o seguinte:

• Gravação simultânea de DML
• Criação de arquivos de classificação temporários quando um índice é criado
• Criação de arquivos temporários de tabelas intermediárias quando as tabelas são reconstruídas (para que tabelas temporárias possam ocupar espaço de armazenamento)

Observação: os tamanhos de arquivo do espaço de tabela do InnoDB só podem ser consultados usando o MySQL versão 5.7 e superior ou o MySQL 8.0 e superior.

Para encontrar o espaço de tabela temporário do InnoDB, execute a seguinte consulta:

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 espaço de tabela temporário global, reinicie o servidor MySQL ou reinicie sua instância de banco de dados. Para obter mais informações, consulte Espaço de tabela temporário no site do MySQL.

Espaço de tabela InnoDB

Às vezes, o MySQL cria tabelas temporárias internas que não podem ser removidas porque uma consulta está intervindo. 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.

Execute a consulta a seguir para encontrar essas tabelas temporárias internas:

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

O espaço de tabela do sistema InnoDB é a área de armazenamento do dicionário de dados InnoDB. Junto com o dicionário de dados, o buffer de gravação dupla, o buffer de alteração e os registros de desfazer também estão presentes no espaço de tabela do sistema InnoDB. Além disso, o espaço de tabela pode conter dados de índice e tabela se as tabelas forem criadas no espaço de tabela do sistema (em vez de arquivos por tabela ou espaços de tabela gerais).

Execute a consulta a seguir para encontrar o espaço de tabela 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: essa consulta é executada no MySQL versão 5.7 e superior ou no MySQL 8.0 e superior.

Depois que o tamanho do espaço na tabela do sistema for aumentado, você não poderá reduzi-lo. No entanto, você pode despejar todas as suas tabelas do InnoDB e importá-las para uma nova instância de banco de dados MySQL. Para evitar grandes espaços de tabela do sistema, considere usar espaços de tabela de arquivo por tabela. Para obter mais informações, consulte Espaços de tabela de arquivo por tabela no site do MySQL.

Se você habilitar Innodb_file_per_table, cada tabela armazenará os dados e o índice em seu próprio arquivo de espaço de tabela. Você pode recuperar o espaço (da fragmentação em bancos de dados e tabelas) executando OPTIMIZE TABLE nessa tabela. O comando OPTIMIZE TABLE cria uma nova cópia vazia da sua tabela. Em seguida, os dados da tabela antiga são copiados linha por linha para a nova tabela. Durante esse processo, um novo espaço de tabela .ibd é criado e o espaço é recuperado. Para obter mais informações sobre esse processo, consulte a instrução OPTIMIZE TABLE no site do MySQL.

Importante: o comando OPTIMIZE TABLE usa o algoritmo COPY para criar tabelas temporárias do mesmo tamanho da tabela original. Confirme se você tem espaço em disco suficiente antes de executar esse comando.

Para otimizar sua tabela, execute a seguinte sintaxe de comando:

mysql> OPTIMIZE TABLE <tablename>;

Ou você pode reconstruir a tabela executando o seguinte comando:

mysql> ALTER TABLE <table_name> ENGINE=INNODB;

Registros 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. Esses registros 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 registros binários. O valor padrão de retenção do log binário para sua instância também está definido como “Nulo”, o que significa que o arquivo é removido imediatamente.

Para evitar problemas de pouco espaço de armazenamento, defina o período apropriado de retenção de log binário no Amazon RDS para MySQL. Você pode revisar o número de horas em que um log binário é retido com a sintaxe do comando mysql.rds_show_configuration:

CALL mysql.rds_show_configuration;

Também é possível reduzir esse valor para reter os registros por um período mais curto para reduzir a quantidade de espaço que os registros usam. Um valor de NULL significa que os registros são eliminados o mais rápido possível. 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 os logs de retransmissão na instância em espera.

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 qualquer atraso durante a eliminação do log binário na instância ativa e o log de retransmissão na instância em espera. Se houver problemas de limpeza ou replicação, esses registros binários podem se acumular com o tempo, consumindo espaço adicional em disco. Para verificar o número de registros binários em uma instância e o tamanho do arquivo, use o comando SHOW BINARY LOGS. Para obter mais informações, consulte SHOW BINARY LOGS statement no site do MySQL.

Se a instância de banco de dados estiver atuando como uma instância em espera de replicação, verifique o tamanho do valor dos registros de retransmissão (Relay_Log_Space) usando o seguinte comando:

SHOW SLAVE STATUS\G

Registros do MySQL (registros gerais, registros de consultas lentas e registros de erros)

O Amazon RDS for MySQL fornece registros (como registros gerais, registros de consultas lentas e registros de erros) que podem ser usados para monitorar seu banco de dados. Os registros de erros estão ativos por padrão. No entanto, os registros gerais e os registros de consultas lentas podem ser ativados usando um grupo de parâmetros personalizado na instância do RDS. Depois que os registros de consulta lenta e os registros gerais são ativados, eles são automaticamente armazenados nas tabelas slow_log e general_log tables dentro do banco de dados MySQL. Para verificar o tamanho de consultas lentas, registros gerais (do tipo “ARQUIVO”) e registros de erros, visualize e liste os arquivos de log do banco de dados.

Se o log de consultas lentas e as tabelas de log gerais estiverem usando armazenamento excessivo, gerencie os registros do MySQL baseados em tabelas girando manualmente as tabelas de log. Para remover completamente os dados antigos e recuperar seu espaço em disco, chame 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 registros. Modifique o parâmetro para que o valor de log\ _output para slow\ _log e general\ _log seja “Arquivo” em vez de “Tabela”.

Também é uma prática recomendada monitorar sua instância de banco de dados Amazon RDS usando o Amazon CloudWatch. Você pode configurar os alarmes do CloudWatch na métrica FreeStorageSpace para receber alertas sempre que seu espaço de armazenamento cair abaixo de um determinado valor limite. Por fim, monitore a métrica FreeStorageSpace configurando um alarme do CloudWatch para receber notificações sempre que sua instância de banco de dados estiver com pouco espaço livre. Para 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?

Além disso, você pode usar o recurso de escalonamento automático de armazenamento do Amazon RDS para gerenciar a capacidade automaticamente. Com o escalonamento automático de armazenamento, você não precisa aumentar manualmente o armazenamento do banco de dados. Para obter mais informações sobre o escalonamento automático de armazenamento do Amazon RDS, consulte Como trabalhar com armazenamento para instâncias de banco de dados do Amazon RDS.

---

Informações relacionadas

Como resolvo problemas com minha instância de banco de dados Amazon RDS for MySQL que está usando mais armazenamento do que o esperado?