Mysql no reduce espacio consumido tras un delete en tablas InnoDb

Introducción

Muchas veces los administradores de sistemas, o los clientes de estos, no son conscientes que no es lo mismo jugar o trabajar con bases de datos de tamaño medio, y otra muy distinta es enfrentarse a databases de 40GB o más.

En estos entornos, mysqldump no es una opción viable, y se hace necesario el uso de una estructura maestro-esclavo como mínimo, para hacer los backups en el esclavo, en lugar de en el maestro, con sus correspondientes problemas además de ser un medio eficaz, por si algo ocurre.

Muchos Sysadmin jamás se han enfrentado a una restore de un servidor mysql de más de 50GB y los que lo han hecho saben la de cositas que pueden salir por ahí.

Recuperación de espacio

Bien, muchas veces en algunos escenarios hay que vaciar tablas pesadas, ya sea por fechas o contenidos, pero tras el vaciado este no suele ocurrir una reducción del espacio en el servidor.

En este documento hablo de mi experiencia con Mysql 8 en una base de datos con InnoDb, y además con el valor innodb_file_per_tableactivado. Esto es importante, pues de lo contrario el ataque debe ser realizado con otra óptica.

mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

Liberar espacio

Por ejemplo queremo liberar datos en una tabla entre dos fechas.

> mysql 
mysql> use mi_database;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE FROM logger_xxxxx WHERE created_at BETWEEN '2023-01-01 00:00:00'  AND '2023-04-30 23:59:59';
Query OK, 1849983 rows affected (5 min 55,64 sec)
mysql> ALTER TABLE logger_xxxxx ENGINE=InnoDB;
Query OK, 0 rows affected (6 min 3,97 sec)
Records: 0  Duplicates: 0  Warnings: 0

Evidentemente la tabla ya estaba en formato InnoDb, pero el comando en sí mismo realizado, una optimización, como la que haríamos con mysqlcheck --auto-repair --optimize database sólo que con ese comando las tablas InnoDb no se optimizarán.

Agradecimientos

En particular mysqldump.guru donde encontrarás maravillosos snipets para usar con mysql.

Aviso

Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad. El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de Castris

Si necesitas soporte profesional puedes contratar con Castris soporte profesional.