¿Por qué el archivo ibdata1 en MySQL se hace cada vez más grande?
Cuando innodb_file_per_table está habilitado, las tablas se almacenan en su propio espacio de tabla, pero el espacio de tabla compartido aún almacena otros datos internos de InnoDB:
El diccionario de datos, es decir, los metadatos de la tabla InnoDB .
Cambio de buffers
Doble buffer de escritura
Registros de deshacer
Algunos de estos se pueden configurar en el servidor Percona para evitar un crecimiento excesivo. Por ejemplo, puede configurar el búfer de cambio máximo a través de innodbibufmax_size, o configurar innodbdoublewritefile para almacenar el búfer de doble escritura en un archivo separado.
En la versión MySQL, también es posible crear espacios de tabla de restauración externos para que puedan colocarse en sus propios archivos en lugar de almacenarse en ibdata1. Puedes echar un vistazo a este documento.
¿Qué provocó el rápido crecimiento de ibdata1?
Cuando hay un problema con MySQL, el primer comando que normalmente necesitamos ejecutar es:
MOSTRAR ESTADO INNODB DEL MOTOR/G
Esto nos mostrará algunos Información valiosa. Comencemos con la **parte de la transacción, luego encontraremos:
-Transacción 36E, activa durante 1256288 segundos
Id. del hilo MySQL 42, identificador del hilo del sistema operativo 0x7f8baaccc700, ID de consulta 7900290 localhost root
Mostrar estado de innodb del motor
La vista de lectura de transacciones no puede ver la transacción con id & gt. =36F, consulte <36F
Esta es una de las razones más comunes, una transacción bastante antigua creada hace 14 días. Este estado es activo, lo que significa que InnoDB ha creado una instantánea de los datos, por lo que es necesario mantener las páginas antiguas en el registro de deshacer para garantizar una vista coherente de la base de datos antes de que comience una transacción. Si su base de datos tiene muchas escrituras, esto significa que se almacenan muchas páginas de deshacer.
Si no puede encontrar ninguna transacción de larga duración, también puede monitorear otras variables en INNODB STATUS. "Longitud de la lista del historial" muestra algunas operaciones en espera de ser borradas. A menudo surgen problemas en esta situación porque el subproceso de limpieza (o versiones anteriores del subproceso principal) no pueden manejar deshacer tan rápido como llegan estos registros.
¿Cómo comprobar lo que está almacenado en ibdata1?
Desafortunadamente, MySQL no proporciona información para ver qué se almacena en el espacio de tabla compartido ibdata1***, pero hay dos herramientas que ayudarán. La primera es una versión modificada de innochecksum creada por Mark Callahan y publicada en este informe de vulnerabilidad.
Es muy fácil de usar:
# ./inno checksum/var/lib/MySQL/ibdata 1
0 suma de comprobación de error
13 File_Page_Index
19272 File_Page_Revocation_Log
230 Nodo de información de página de archivo
1 FIL_PAGE_IBUF_FREE_LIST
892 file_page_type_allocation
2 mapas de bits de página de relleno
195 file_page_type_system
1 file_page_type_ TRX _SYSTEM
1 HDR FSP
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 Otros
3 identificadores de índice máximo
Hay un total de 19272 páginas de registro de deshacer. Esto ocupa el 93% del espacio de la mesa.
La segunda forma de inspeccionar el contenido del espacio de manifiesto es la herramienta InnoDB Ruby de Jeremy Cole.
Es una herramienta más avanzada para inspeccionar la estructura interna de InnoDB. Por ejemplo, podemos usar el parámetro space-summary para obtener una lista de cada página y su tipo de datos. Podemos usar herramientas estándar de Unix para contar el número de páginas de registro de deshacer:
# innodb _ space-f/var/lib/MySQL/ibdata 1 space-summary | grep UNDO _ LOG-l< / p>
19272
Si bien innochedcksum es más rápido y fácil de usar en este caso particular, te recomiendo que uses la herramienta de Jeremy para aprender más sobre la distribución de datos y la estructura interna de InnoDB.
Bien, ahora conocemos el problema. Siguiente pregunta:
¿Cómo soluciono este problema?
La respuesta a esta pregunta es sencilla. Si aún puede enviar una declaración, hágalo. De lo contrario, debe cerrar el hilo e iniciar el proceso de reversión. Eso impedirá que ibdata1 crezca, pero obviamente su software tendrá errores y alguien encontrará el error. Ahora que sabe cómo identificar el problema, debe utilizar sus propias herramientas de depuración o registros de consultas simples para descubrir quién o qué está causando el problema.
Si el problema radica en el hilo de limpieza, la solución suele ser actualizar a una nueva versión y reemplazar el hilo principal con un hilo de limpieza independiente. Consulte este documento para obtener más información.
¿Existe alguna forma de reciclar el espacio usado?
No, actualmente no existe una forma rápida y sencilla. Los espacios de tabla de InnoDB nunca se reducen... Consulte el informe de error de hace 10 años, última actualización de James Day (gracias):
Cuando elimina algunas filas, la página se marca como eliminada y se reutiliza más tarde, pero este espacio nunca será reclamado. La única forma es iniciar la base de datos con el nuevo ibdata1. Para hacer esto, debe hacer una copia de seguridad completa lógica usando mysqldump, luego detener MySQL y eliminar toda la base de datos, los archivos ib_logfile e ibdata1. Cuando inicie MySQL nuevamente, se creará un nuevo espacio de tabla. Luego restaure la copia de seguridad lógica.