optimizar - optimize en mysql

MySQL InnoDB no libera espacio en disco después de eliminar filas de datos de la tabla

Question

Tengo una tabla MySQL usando el motor de almacenamiento InnoDB; contiene alrededor de 2M filas de datos. Cuando eliminé las filas de datos de la tabla, no se publicó el espacio de disco asignado. Tampoco se redujo el tamaño del archivo ibdata1 después de ejecutar el comando optimize table .

¿Hay alguna manera de recuperar espacio en el disco de MySQL?

Estoy en una mala situación; esta aplicación se ejecuta en aproximadamente 50 ubicaciones diferentes y ahora aparece un problema de poco espacio en disco en casi todos ellos.

Answer 1

Hay varias formas de recuperar el espacio de disco después de eliminar los datos de la tabla para el motor MySQL Inodb

Si no usa innodb_file_per_table desde el principio, volcar todos los datos, eliminar todo el archivo, volver a crear la base de datos e importar los datos de nuevo es solo una forma (consulte las respuestas de FlipMcF más arriba)

Si está usando innodb_file_per_table, puede intentar

1. Si puede eliminar todos los datos, el comando truncar eliminará los datos y recuperará el espacio de disco por usted.
2. El comando Alter table soltará y volverá a crear la tabla para recuperar el espacio de disco. Por lo tanto, después de eliminar datos, ejecute alter table que no cambie nada para liberar hardisk (es decir: la tabla TBL_A tiene charset uf8, después de eliminar los datos ALTER TABLE TBL_A charset utf8 -> este comando no cambia nada de su tabla sino que hace que mysql vuelva a crear su tabla Espacio del disco
3. Crear TBL_B como TBL_A. Inserte los datos de selección que desea conservar de TBL_A en TBL_B. Suelte TBL_A y cambie el nombre de TBL_B a TBL_A. De esta manera es muy efectivo si TBL_A y los datos que se necesitan eliminar son grandes (el comando de eliminación en MySQL innodb es un rendimiento muy malo)

Answer 2

MySQL no reduce el tamaño de ibdata1. Nunca. Incluso si utiliza la optimize table para liberar el espacio utilizado de los registros eliminados, lo volverá a utilizar más adelante.

Una alternativa es configurar el servidor para utilizar innodb_file_per_table , pero esto requerirá una copia de seguridad, soltar la base de datos y restaurar. El lado positivo es que el archivo .ibd para la tabla se reduce después de una optimize table .

Answer 3

Otra forma de resolver el problema de recuperación de espacio es Crear particiones múltiples dentro de la tabla - Particiones basadas en Rango basadas en el Valor y simplemente colocar / truncar la partición para reclamar el espacio, que liberará el espacio utilizado por los datos completos almacenados en la partición particular.

Habrá algunos cambios necesarios en el esquema de la tabla cuando introduzca el particionamiento para su tabla como - Teclas únicas, Índices para incluir columna de partición, etc.

Answer 4

Si no utiliza innodb_file_per_table , es posible reclamar espacio en el disco, pero es bastante tedioso y requiere una gran cantidad de tiempo de inactividad.

El Cómo es bastante profundo, pero pegué la parte relevante a continuación.

Asegúrese de conservar también una copia de su esquema en su volcado.

Actualmente, no puede eliminar un archivo de datos del tablespace del sistema. Para disminuir el tamaño del espacio de tabla del sistema, use este procedimiento:

Use mysqldump para volcar todas sus tablas InnoDB.

Detener el servidor.

Elimine todos los archivos de espacio de tabla existentes, incluidos los archivos ibdata e ib_log. Si desea conservar una copia de seguridad de la información, copie todos los archivos ib * en otra ubicación antes de eliminar los archivos en su instalación de MySQL.

Elimine cualquier archivo .frm para tablas InnoDB.

Configure un nuevo espacio de tabla.

Reinicia el servidor.

Importar los archivos de volcado.

Answer 5

Solo tuve el mismo problema yo mismo.

Lo que ocurre es que, incluso si elimina la base de datos, innodb aún no liberará espacio en el disco. Tuve que exportar, detener mysql, eliminar los archivos manualmente, iniciar mysql, crear base de datos y usuarios, y luego importar. Gracias a Dios, solo tenía 200 MB de filas, pero ahorró 250 GB de archivo innodb.

Falla por diseño