Trabalhar com espaços de tabela do InnoDB para melhorar o tempo de recuperação de falhas do RDS para MySQL

Cada tabela no MySQL consiste de uma definição, de dados e de índices de tabela. O mecanismo de armazenamento InnoDB do MySQL armazena dados e índices de tabela em um espaço de tabela. O InnoDB cria um tablespace compartilhado global que contém um dicionário de dados e outros metadados relevantes, e pode conter dados e índices de tabela. O InnoDB também pode criar tablespaces separados para cada tabela e partição. Esses tablespaces separados são armazenados em arquivos de extensão .ibd e o cabeçalho de cada tablespaces contém um número que o identifica de forma exclusiva.

O Amazon RDS fornece um parâmetro em um grupo de parâmetros do MySQL chamado innodb_file_per_table. Esses parâmetros controlam se o InnoDB adiciona novos dados de tabela e índices ao espaço de tabela compartilhado (definindo o valor do parâmetro como 0) ou aos espaços de tabelas individuais (definindo o valor do parâmetro como 1). O Amazon RDS define o valor padrão para innodb_file_per_table como 1, o que permite eliminar tabelas individuais do InnoDB e recuperar o armazenamento usado por essas tabelas para a instância de banco de dados. Na maioria dos casos de uso, definir o parâmetro innodb_file_per_table como 1 é a configuração recomendada.

Você deve definir o parâmetro innodb_file_per_table como 0 quando você tiver um grande número de tabelas, por exemplo mais de 1.000 tabelas, ao usar armazenamento SSD padrão (magnético) ou de propósito geral ou mais de 10.000 tabelas ao usar armazenamento de IOPS provisionadas. Quando você define esse parâmetro como 0, os tablespaces individuais não são criados e isso pode melhorar o tempo necessário para a recuperação da falha no banco de dados.

O MySQL processa cada arquivo de metadados, que inclui tablespaces, durante o ciclo de recuperação de falhas. O tempo que o MySQL leva para processar as informações de metadados no tablespace compartilhado é insignificante em comparação ao tempo necessário para processar milhares de arquivos de tablespace quando há vários tablespaces. Como o número do tablespace é armazenado no cabeçalho de cada arquivo, o tempo total para ler todos os arquivos do tablespace pode levar até várias horas. Por exemplo, pode levar de cinco a oito horas para processar um milhão de tablespaces do InnoDB no armazenamento padrão durante um ciclo de recuperação de falhas. Em alguns casos, o InnoDB pode determinar que precisa de limpeza adicional após um ciclo de recuperação de falhas para poder começar outro ciclo de recuperação de falhas, o que prolongará o tempo de recuperação. Lembre-se que um ciclo de recuperação de falhas também envolve transações de retorno, correção de páginas com defeito e outras operações além do processamento de informações de tablespace.

Como o parâmetro innodb_file_per_table reside em um grupo de parâmetros, você pode alterar o valor do parâmetro editando o grupo de parâmetros usado por sua instância de banco de dados sem ter que reiniciar a instância de banco de dados. Após a configuração ser alterada, por exemplo, de 1 (criar tabelas individuais) para 0 (usar tablespace compartilhado), novas tabelas do InnoDB serão adicionadas ao tablespace compartilhado, enquanto as tabelas existentes continuam a ter tablespaces individuais. Para mover uma tabela do InnoDB para o tablespace compartilhado, você deve usar o comando ALTER TABLE.

Migração de vários tablespaces ao tablespace compartilhado

Você pode mover os metadados da tabela do InnoDB de seu próprio tablespace ao tablespace compartilhado, que reconstruirá os metadados da tabela de acordo com a configuração do parâmetro innodb_file_per_table. Primeiro, conecte-se à sua instância de banco de dados MySQL. Depois disso, emita os comandos apropriados, conforme mostrado a seguir. Para obter mais informações, consulte Conexão a uma instância de banco de dados executando o mecanismo de banco de dados do MySQL.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

Por exemplo, a seguinte consulta retorna uma instrução ALTER TABLE para cada tabela InnoDB que não esteja no espaço de tabela compartilhado.

Para instâncias de banco de dados do MySQL 5.7:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

Para instâncias de banco de dados do MySQL 8.0:

SELECT CONCAT('ALTER TABLE `', 
REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', 
REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query 
FROM INFORMATION_SCHEMA.INNODB_TABLES 
WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');

Recriar uma tabela do MySQL para mover os metadados da tabela para o tablespace compartilhado requer temporariamente espaço de armazenamento adicional para recriar a tabela, portanto a instância do banco de dados deve ter espaço de armazenamento disponível. Durante a recriação, a tabela é bloqueada e inacessível para consultas. Para tabelas pequenas ou tabelas que não são acessadas com frequência, isso pode não ser um problema. Para tabelas grandes ou tabelas acessadas com frequência em um ambiente fortemente simultâneo, é possível recriar tabelas em uma réplica de leitura.

É possível criar uma réplica de leitura e migrar metadados da tabela para o tablespace compartilhado na réplica de leitura. Embora a instrução ALTER TABLE bloqueie o acesso na réplica de leitura, a instância de banco de dados de origem não é afetada. A instância de banco de dados de origem continuará a gerar seus logs binários enquanto a réplica de leitura apresentar atraso durante o processo de recriação da tabela. Como a recriação requer espaço de armazenamento adicional e o arquivo de log de reprodução pode ficar grande, você deve criar uma réplica de leitura com o armazenamento alocado maior que a instância de banco de dados de origem.

Para criar uma réplica de leitura e recriar tabelas do InnoDB a fim de usar o tablespace compartilhado, execute as seguintes etapas: