Serie de preguntas y respuestas de MySQL: Cómo evitar que el tamaño del archivo ibdata1 explote

¿Qué es el archivo ibdata1?

Ibdata1 es un archivo utilizado para construir el espacio de tablas del sistema innodb. Contiene los metadatos de la tabla innodb, la cancelación de registros, el búfer de modificación y el búfer de doble escritura. Si la opción "Un archivo por tabla" está activada, es posible que el archivo no contenga datos para todas las tablas. Cuando la opción innodb_file_per_table está activada, los datos y los índices de la tabla recién creada no existirán en el espacio de tablas del sistema, sino en. Archivos ibd de las tablas respectivas.

Obviamente, este archivo será cada vez más grande. La opción innodb_autoextend_increment especifica el tamaño del paso del crecimiento automático de este archivo cada vez.

¿Qué causa que el archivo de ibdata1 se haga cada vez más grande?

Ibdata1 almacena datos, índices y cachés y es el dato más importante en MYSQL. Entonces, a medida que la base de datos crece, las tablas crecerán, eso es inevitable. Si se hace cada vez más grande con el tiempo, será un inconveniente para nosotros lidiar con registros y espacio, y no sabemos por dónde empezar. A continuación, tenemos que manejar esta situación y almacenar los datos en una biblioteca diferente.

¿Qué debo hacer si el tamaño del archivo * * * del espacio de tabla compartido ibdata1 de InnoDB aumenta drásticamente?

1. Antecedentes del problema

Las personas que usan MySQL/InnoDB también pueden tener problemas. No sé por qué, el archivo de ibdata1 es inexplicablemente largo y no sé cómo reducirlo, como la barriga de un hombre después de los 30 años. Sudor, es tan gratificante que mi barriga aún no haya crecido, hoho~

Antes de comenzar oficialmente, necesitamos saber para qué se usa el archivo ibdata1.

El archivo Ibdata1 es el * * * archivo de espacio de tabla del motor de almacenamiento InnoDB. Almacena principalmente los siguientes datos:

Diccionario de datos

Búfer de escritura doble.

Insertar búfer/reemplazar búfer

Segmento de retroceso

Deshacer espacios

Tablas del sistema de restricciones de clave externa

En Además, cuando la opción InnoDB_file_per_table = 0, la tabla y la tabla innodb deben almacenarse en el archivo ibdata1. índice. El archivo ibdata1, a partir de la versión 5.6.7, tiene un tamaño predeterminado de 12 MB. Antes de esto, el tamaño predeterminado era 10 MB y la opción asociada era innodb_data_file_path. Por ejemplo, normalmente lo configuro así:

innodb _ data _ file _ path = IB data 1:1G: expansión automática

Por supuesto, si innodb_file_per_table = 1 está habilitado o no, el archivo ibdata1 debe existir porque debe almacenar el motor innodb mencionado anteriormente y los datos necesarios, especialmente el segmento de reversión y el espacio para deshacer marcados en negrita arriba, que son las principales razones del aumento en el tamaño de ibdata1. archivo. Lo discutiremos en detalle a continuación.

2. Análisis de motivos

Sabemos que InnoDB admite MVCC similar a ORACLE y utiliza registros de deshacer y rehacer registros para implementar funciones de MVCC. Cuando se modifica una fila de datos en una transacción, InnoDB almacena una versión anterior de la fila de datos en el registro de deshacer. Si otra transacción quiere modificar esta fila de datos en este momento, almacenará la última versión de datos visibles de esta cosa en el registro de deshacer, y así sucesivamente. Si hay n transacciones para modificar estos datos, es necesario almacenar n copias del historial de versiones (ligeramente diferente de ORACLE, el registro de deshacer de InnoDB no es completamente un bloque físico, principalmente un registro lógico, puede ver el código fuente de InnoDB u otros relacionados información) . Estos registros de deshacer deben esperar a que finalice la transacción y luego juzgar nuevamente en función de la visibilidad de otras transacciones determinada por el nivel de aislamiento de la transacción para confirmar si estos registros de deshacer se pueden eliminar. Este trabajo se llama purga (el trabajo de purga no consiste solo en eliminar registros de deshacer vencidos, sino también otras cosas, que se discutirán más adelante).

Entonces viene la pregunta.

Si hay un historial de versiones que requiere leer una gran cantidad de datos en una transacción, pero esta transacción no se puede confirmar o revertir por algún motivo esta mañana, y hay una gran cantidad de transacciones que necesitan modificar los datos después de la transacción se inicia, los datos generados por estas nuevas transacciones nunca se podrán eliminar y formarán una pila. Esta es una de las razones principales del aumento en el tamaño del archivo ibdata1. El escenario más clásico de esta situación es una gran cantidad de copia de seguridad de datos, por lo que recomendamos que el trabajo de copia de seguridad se realice en un servidor esclavo dedicado en lugar de en el servidor maestro.

Por otro lado, el trabajo de limpieza de InnoDB no ha podido borrar los registros de deshacer eliminados a tiempo debido al bajo rendimiento de E/S de archivos u otras razones. Esta es otra razón principal para el aumento en el tamaño del archivo. archivo ibdata1. Esta situación ocurre cuando la configuración de hardware del servidor es débil y no se actualiza a tiempo para mantenerse al día con el desarrollo empresarial.

Un error poco común es un error en las primeras versiones de MySQL que se ejecutan en sistemas de 32 bits. Cuando se descubre que la cantidad total de registros de deshacer que se borrarán excede un cierto valor, el hilo de limpieza pierde resistencia directamente y ya no se borra. Este problema se produjo cuando usamos la versión MySQL de 32 bits en los primeros días. Una vez nos encontramos con una situación en la que el tamaño del archivo aumentaba a más de 100 g. Más tarde, dedicamos mucho esfuerzo a migrar todos estos ejemplos a 64. -sistema de bits, y finalmente resolvió el problema.

Finalmente, el valor de la opción innodb_data_file_path no se ajustó al principio o se estableció en un valor muy pequeño, lo que inevitablemente condujo al aumento del archivo ibdata1. La documentación de referencia my.cnf proporcionada por Percona nunca agrega este valor, lo cual me desconcierta. ¿Dejaste deliberadamente una puerta secreta como xx de la que a menudo me quejo para poder ayudar a los clientes a optimizarla más tarde? (Mi cerebro está demasiado oscuro, esto no es bueno ~~)

Para resumir brevemente, hay varias razones para el tamaño vertiginoso del archivo ibdata1:

Hay una gran cantidad de transacciones concurrentes, que generan una gran cantidad de registros de deshacer;;

Hay transacciones antiguas que no se han enviado durante mucho tiempo, lo que resulta en una gran cantidad de registros de deshacer antiguos;;

El rendimiento de E/S del archivo es deficiente y el progreso de limpieza es lento;

Inicialización La configuración es demasiado pequeña;

Hay un error en los sistemas de 32 bits.

Como nota al margen, otra base de datos popular, PostgreSQL, almacena los datos del historial de versiones y el espacio de la tabla de datos original juntos, por lo que no hay ningún problema en esta situación y, por lo tanto, la transacción de PostgreSQL regresa. La transferencia será rápida y El trabajo de vacío debe realizarse con regularidad (consulte el mecanismo de implementación MVCC de PostgreSQL para obtener más detalles, es posible que no esté del todo correcto).

3. Sugerencias sobre soluciones

Al ver la descripción anterior de las causas de estos problemas, algunos estudiantes pueden encontrar esto fácil de resolver. Reducir el tamaño del archivo de ibdata1 no finaliza el espacio de tabla. Lamentablemente, a partir de ahora, InnoDB no tiene forma de restaurar/reducir el espacio de tabla para el archivo ibdata1. Una vez que se expande la parte inferior del archivo ibdata1, solo podemos restaurar el tamaño original haciendo una copia de seguridad de los datos primero y luego reiniciando la instancia, o restaurando los archivos de espacio de tablas independientes en la nueva instancia en secuencia. Aparte de eso, no hay mejor manera.

Por supuesto, este problema no es imposible de prevenir. Según las razones anteriores, las sugerencias y contramedidas correspondientes son:

Actualice a 5.6 y superior (64 bits) y utilice un espacio de tabla de deshacer independiente. A partir de la versión 5.6, se admiten espacios de tabla de deshacer independientes, por lo que no hay necesidad de preocuparse por agrandar el archivo ibdata1.

Al inicializar la configuración, configure el archivo ibdata1 en al menos 1 GB.

Aumente el número de subprocesos de purga, como configurar innodb_purge_threads = 8;

Para mejorar las capacidades de E/S de archivos, el SSD debe instalarse rápidamente

Envíe transacciones de manera oportuna, no acumule retrasos;

Auto-commit=1 está activado de forma predeterminada para evitar olvidar que una transacción no se ha enviado durante mucho tiempo;

Verifique el marco de desarrollo para confirmar si autocommit = 0 está configurado, recuerde Hay una confirmación o reversión explícita después de que finaliza la transacción.

Resumen