Uso de espacios de tablas de InnoDB para mejorar los tiempos de recuperación tras un bloqueo de RDS para MySQL

Cada tabla de MySQL consta de una definición de tabla, datos e índices. El motor de almacenamiento InnoDB de MySQL almacena los datos y los índices de las tablas en un espacio de tabla. InnoDB crea un espacio de tablas global compartido que contiene un diccionario de datos y otros metadatos relevantes, y puede contener los datos e índices de las tablas. InnoDB también puede crear espacios de tabla independientes para cada tabla y partición. Estos espacios de tabla independientes se almacenan en archivos con la extensión .ibd y el encabezado de cada espacio de tabla contiene un número que lo identifica de forma inequívoca.

Amazon RDS proporciona un parámetro en un grupo de parámetros de MySQL denominado innodb_file_per_table. Este parámetro controla si InnoDB agrega los datos e índices de las tablas nuevas al espacio de tablas compartido (cuando se establece el valor del parámetro en 0) o a espacios de tabla individuales (cuando se establece el valor del parámetro en 1). Amazon RDS establece el valor predeterminado para el parámetro innodb_file_per_table en 1, que le permite eliminar tablas individuales de InnoDB y recuperar el almacenamiento utilizado por esas tablas para la instancia de base de datos. En la mayoría de los casos de uso, se recomienda establecer el parámetro innodb_file_per_table en 1.

Debe establecer el parámetro innodb_file_per_table en 0 si tiene un gran número de tablas, por ejemplo, más de 1 000 tablas si utiliza almacenamiento estándar (magnético) o SSD de uso general, o más de 10 000 tablas si utiliza almacenamiento de IOPS provisionadas. Cuando se establece este parámetro en 0, no se crean espacios de tabla individuales, y esto puede mejorar el tiempo que tarda la recuperación tras bloqueo de la base de datos.

MySQL procesa cada archivo de metadatos, lo que incluye los espacios de tabla, durante el ciclo de recuperación tras bloqueo. El tiempo que tarda MySQL en procesar la información de metadatos del espacio de tablas compartido es insignificante en comparación con el tiempo que tarda en procesar miles de archivos de espacio de tabla si hay múltiples espacios de tabla. Debido a que el número del espacio de tabla se almacena en el encabezado de cada archivo, el tiempo global necesario para leer todos los archivos de espacios de tabla puede llegar a ser de varias horas. Por ejemplo, un millón de espacios de tabla de InnoDB en almacenamiento estándar pueden tardar entre cinco y ocho horas en procesarse durante un ciclo de recuperación tras bloqueo. En algunos casos, InnoDB puede determinar que necesita realizar una limpieza adicional después de un ciclo de recuperación tras bloqueo, por lo que iniciará otro ciclo que alargará el tiempo de recuperación. Tenga en cuenta que un ciclo de recuperación tras bloqueo, además del procesamiento de la información de los espacios de tabla, también conlleva la reversión de transacciones, la reparación de páginas dañadas y otras operaciones.

Dado que el parámetro innodb_file_per_table reside en un grupo de parámetros, puede cambiar el valor del parámetro editando el grupo de parámetros utilizado por la instancia de base de datos sin tener que reiniciarla. Después de cambiar la configuración, por ejemplo, de 1 (crear tablas individuales) a 0 (utilizar el espacio de tablas compartido), las tablas de InnoDB nuevas se añadirán al espacio de tablas compartido, mientras que las tablas existentes continuarán teniendo espacios de tabla individuales. Para mover una tabla InnoDB al espacio de tablas compartido, debe utilizar el comando ALTER TABLE.

Migración de varios espacios de tabla al espacio de tablas compartido

Puede mover los metadatos de una tabla de InnoDB desde su propio espacio de tabla al espacio de tablas compartido, lo que reconstruirá los metadatos de la tabla de acuerdo con el valor del parámetro innodb_file_per_table. En primer lugar, conéctese a la instancia de la base de datos MySQL y, a continuación, emita los comandos apropiados como se muestra a continuación. Para obtener más información, consulte Conexión a una instancia de base de datos que ejecuta el motor de base de datos de MySQL.

ALTER TABLE table_name ENGINE = InnoDB, ALGORITHM=COPY; 

Por ejemplo, la siguiente consulta devuelve una instrucción ALTER TABLE para cada tabla de InnoDB que no está en el espacio de tablas compartido.

Para instancias de base de datos 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 instancias de base de datos 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','');

La reconstrucción de una tabla de MySQL para mover los metadatos de la tabla al espacio de tablas compartido requiere espacio de almacenamiento adicional temporalmente para reconstruir la tabla, por lo que la instancia de base de datos debe tener espacio de almacenamiento disponible. Durante la reconstrucción, la tabla está bloqueada e inaccesible para las consultas. Para las tablas pequeñas o las tablas a las que no se tiene acceso con frecuencia, esto puede no ser un problema. Para las tablas de gran tamaño o las tablas a las que se tiene acceso con frecuencia en un entorno con un gran número de accesos simultáneos, es posible reconstruir las tablas en una réplica de lectura.

Es posible crear una réplica de lectura y migrar los metadatos de las tablas al espacio de tablas compartido en la réplica de lectura. Aunque la instrucción ALTER TABLE bloquea el acceso en la réplica de lectura, la instancia de base de datos de origen no se ve afectada. La instancia de base de datos de origen continuará generando sus registros binarios, mientras que la réplica de lectura se retrasará con respecto a ella durante el proceso de reconstrucción de tablas. Dado que la reconstrucción requiere espacio de almacenamiento adicional y el archivo registro de reproducción puede ser muy grande, al crear una réplica de lectura, debe asignar una cantidad de almacenamiento mayor que la instancia de base de datos de origen.

Para crear una réplica de lectura y reconstruir las tablas de InnoDB para que utilicen el espacio de tablas compartido, siga estos pasos: