Utilisation des espaces de table InnoDB pour améliorer les temps de récupération sur incident pour RDS for MySQL

Chaque table de MySQL se compose d'une définition de table, de données et d'index. Le moteur de stockage MySQL InnoDB stocke les données de table et les index dans un tablespace. InnoDB crée un espace de table global partagé qui contient un dictionnaire de données et autres métadonnées pertinentes, et peut contenir des données de table et des index. InnoDB peut aussi créer des espaces de table distincts pour chaque table et partition. Ces espaces de table distincts sont stockés dans des fichiers ayant .ibd comme extension et l'en-tête de chaque espace de table contient un numéro qui l'identifie de façon unique.

Amazon RDS fournit un paramètre dans un groupe de paramètres MySQL appelé innodb_file_per_table. Ce paramètre contrôle le fait qu'InnoDB ajoute ou non de nouvelles données et de nouveaux index de tables au tablespace partagé (en définissant la valeur du paramètre du 0) ou à des tablespaces individuels (en définissant la valeur du paramètre sur 1). Amazon RDS définit la valeur par défaut pour le paramètre innodb_file_per_table sur 1, ce qui vous permet d'abandonner des tables InnoDB individuelles afin de libérer l'espace de stockage que ces tables utilisent au profit de l'instance de base de données. Dans la plupart des cas d'utilisation, la définition du paramètre innodb_file_per_table à la valeur 1 est celle recommandée.

Vous devez définir le paramètre innodb_file_per_table à la valeur 0 quand vous avez un nombre important de tables, tel que plus de 1 000 tables quand vous utilisez le stockage SSD standard (magnétique) ou à visée générale, ou plus de 10 000 tables quand vous utilisez le stockage IOPS provisionnées. Lorsque vous définissez ce paramètre à la valeur 0, les espaces de table individuels ne sont pas créés et cela peut améliorer le temps nécessaire pour la récupération sur incident de base de données.

MySQL traite chaque fichier de métadonnées, espaces de tables inclus, pendant le cycle de récupération sur incident. Le temps nécessaire à MySQL pour traiter les informations de métadonnées dans l'espace de table partagé est négligeable en comparaison du temps qu'il faut pour traiter des milliers de fichiers d'espace de table quand il y a plusieurs espaces de table. Comme le nombre d'espaces de table est stocké au sein de l'en-tête de chaque fichier, le temps total nécessaire pour lire tous les fichiers d'espace de table peut prendre jusqu'à plusieurs heures. Par exemple, un million d'espaces de table InnoDB sur un stockage standard peut nécessiter entre cinq et huit heures de traitement pendant un cycle de récupération sur incident. Dans certains, InnoDB peut déterminer qu'il a besoin d'un nettoyage supplémentaire après un cycle de récupération sur incident et, par conséquent, entamera un autre cycle de récupération sur incident, ce qui augmente le temps total de récupération. Gardez à l'esprit qu'un cycle de récupération sur incident implique aussi la restauration de transactions, la correction des pages rompues et autres opérations en plus du traitement des informations sur les espaces de table.

Comme le paramètre innodb_file_per_table réside dans un groupe de paramètres, vous pouvez modifier la valeur du paramètre en modifiant le groupe de paramètres utilisé par votre instance de base de données sans avoir à redémarrer celle-ci. Une fois que la valeur est modifiée, de la valeur 1 (créer des tables individuelles) à la valeur 0 (utiliser un espace de table partagé), par exemple, les nouvelles tables InnoDB sont ajoutées à l'espace de table partagé, pendant que les tables existantes continuent d'avoir des espaces de table individuels. Pour déplacer une table InnoDB vers l'espace de table partagé, vous devez utiliser la commande ALTER TABLE.

Migration de plusieurs espaces de table vers l'espace de table partagé

Vous pouvez déplacer les métadonnées d'une table InnoDB de son propre espace de table vers l'espace de table partagé, ce qui recrée les métadonnées de la table selon la valeur du paramètre innodb_file_per_table. Connectez-vous d’abord à votre instance de base de données MySQL, puis émettez les commandes appropriées comme illustré ci-après. Pour plus d’informations, consultez Connexion à votre instance de base de données MySQL.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

Par exemple, la requête suivante retourne une instruction ALTER TABLE pour chaque table InnoDB qui ne figure pas dans l'espace de table partagé.

Pour les instances de base de données 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','');

Pour les instances de base de données MySQL 8.4 et 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','');

La reconstruction d'une table MySQL pour déplacer les métadonnées de la table vers l'espace de table partagé nécessite temporairement un espace de stockage supplémentaire pour recréer la table et, par conséquent, l'instance de base de données doit avoir un espace de stockage disponible. Pendant la reconstruction, la table est verrouillée et inaccessible aux requêtes. Pour les petites tables ou les tables qui ne sont pas fréquemment consultées, ce n'est pas nécessairement un problème. Pour les tables volumineuses ou fréquemment consultées dans un environnement fortement concurrentiel, vous pouvez reconstruire les tables sur un réplica en lecture.

Vous pouvez créer un réplica en lecture et migrer les métadonnées de la table vers l'espace de table partagé du réplica en lecture. Tant que l'instruction ALTER TABLE bloque l'accès sur le réplica en lecture, l'instance de base de données source n'est pas impactée. L'instance de base de données source continue à générer ses journaux binaires, tandis que le réplica en lecture ralentit pendant le processus de reconstruction de la table. Étant donné que la reconstruction exige un espace de stockage supplémentaire et que le fichier journal de relecture peut devenir volumineux, vous devriez créer un réplica en lecture dont la capacité de stockage allouée est supérieure à l'instance de base de données source.

Pour créer un réplica en lecture et reconstruire les tables InnoDB afin d'utiliser l'espace de table partagé, procédez comme suit :

1. Assurez-vous que la rétention des sauvegardes est activée sur l'instance de base de données source de sorte que la journalisation binaire soit activée.

2. Utilisez AWS Management Console ou AWS CLI pour créer un réplica en lecture pour l'instance de base de données source. Étant donné que la création d'un réplica en lecture implique un grand nombre de processus semblables à ceux de la récupération sur incident, le processus de création peut prendre un certain temps si le nombre d'espaces de table InnoDB est élevé. Allouez plus d'espace de stockage sur le réplica en lecture qu'il n'en est actuellement utilisé sur l'instance de base de données source.

3. Lorsque le réplica en lecture a été créé, créez un groupe de paramètres avec les valeurs de paramètre read_only = 0 et innodb_file_per_table = 0. Associez ensuite le groupe de paramètres au réplica en lecture.

4. Émettez l'instruction SQL suivante pour toutes les tables que vous souhaitez migrer sur le réplica :

   ALTER TABLE name ENGINE = InnoDB

5. Une fois que toutes vos instructions ALTER TABLE sont terminées sur le réplica en lecture, vérifiez que celui-ci est connecté à l'instance de base de données source et que les deux instances sont synchronisées.

6. Utilisez la console ou l'interface de ligne de commande (CLI) pour promouvoir le réplica en lecture comme instance. Assurez-vous que le groupe de paramètres utilisé pour la nouvelle instance de base de données autonome a le paramètre innodb_file_per_table défini sur 0. Modifiez le nom de la nouvelle instance de base de données autonome et pointez toutes les applications vers la nouvelle instance de base de données autonome.