Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Utilisation des tablespaces InnoDB pour améliorer les temps de reprise après incident pour for My RDS SQL

Chaque table de My SQL comprend une définition de table, des données et des index. Le moteur My SQL storage InnoDB stocke les données des tables 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 My SQL parameter 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 du innodb_file_per_table paramètre sur 1, ce qui vous permet de supprimer des tables InnoDB individuelles et de récupérer le stockage utilisé par ces tables pour 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 innodb_file_per_table paramètre sur 0 lorsque vous avez un grand nombre de tables, par exemple plus de 1 000 tables lorsque vous utilisez un SSD stockage standard (magnétique) ou à usage général ou plus de 10 000 tables lorsque vous utilisez le IOPS stockage provisionné. 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.

My SQL traite chaque fichier de métadonnées, y compris les tablespaces, pendant le cycle de reprise après incident. Le temps nécessaire SQL à My pour traiter les informations de métadonnées dans l'espace disque logique partagé est négligeable par rapport au temps nécessaire au traitement de milliers de fichiers d'espace disque logique lorsqu'il existe plusieurs espaces disque logiques. 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 My SQL DB, puis émettez les commandes appropriées comme indiqué ci-dessous. Pour de plus amples informations, veuillez consulter 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 My SQL 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 My SQL 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 SQL table My pour déplacer les métadonnées de la table vers le tablespace partagé nécessite temporairement un espace de stockage supplémentaire pour reconstruire la table. L'instance de base de données doit donc disposer d'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. Bien que l'ALTERTABLEinstruction bloque l'accès à la réplique en lecture, l'instance de base de données source n'est pas affecté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 :