Crecimiento del fichero ibdata1 en MySQL: Que es y como purgarlo

Es relativamente común encontrarse con situaciones de falta de espacio en disco debido a un crecimiento del tamaño del fichero ibdata1 ubicado en /var/lib/mysql.

ibdata1 es un fichero de InnoDB que guarda, por defecto, los metadatos de InnoDB, el buffer de cambios, el buffer de doble escritura, los undo logs y los datos de las tablas en InnoDB.

Este fichero crece de tamaño a medida que se hacen transacciones, se realizan cambios en las tablas, etc, pero nunca disminuye de tamaño, por lo que si, por ejemplo, un día añadimos 4 gb en información en una tabla ibdata1 aumentará en consecuencia, y si al día siguiente borramos esos 4 gb, ibdata1 permanecerá con el mismo tamaño, aunque cierto es que de, por ejemplo, añadir tras esto 2 gb más de información a una tabla, ibdata1 no aumentará puesto que “internamente” tiene 4 gb libres.

Esto es un problema, puesto que no podemos purgar este fichero,  y en ocasiones tendremos problemas de espacio en disco por situaciones internas de la BBDD que escapan a nuestro control.

¿Como lo solucionamos?

La solución consiste en configurar InnoDB para que el fichero ibdata1 guarde los datos de las tablas en ficheros separados, de esta forma generará ficheros .frm e .ibd por cada tabla, y en caso de querer optimizar una tabla y vaciar datos obsoletos, podremos realizarlo con OPTIMIZE TABLE. Lamentablemente, para configurar InnoDB de esta forma, es necesario primero purgar el fichero ibdata1, y para ello, hay que borrar todas las bases de datos y recrearlas, explico el proceso.

 

1. Procedemos a realizar un dump de todas las bases de datos con mysqldump, ya sea una a una:

O, en su defecto todas a la vez:

Dropamos/Borramos todas las bases de datos EXCEPTO mysql_schema, mysql y performance_schema (podéis usar el siguiente comando)

Paramos MySQL/MariaDB

Editamos my.cnf y añadimos las siguientes líneas

(El tamaño de innodb_buffer_pool_size debe asignarse en función de la memoria, si es un servidor sólo dedicado a MySQL, un 80% de la memoria va bien, si es compartido con Apache y correo, etc, con un 30% de memoria debería bastar, pero es importante que innodb_log_file_size sea un 25% de buffer_pool_size)

Eliminamos el fichero ibdata1 y los logfiles:

Arrancamos MySQL de nuevo

Volcamos los dumps de las bbdd a MySQL.

Con esto, habremos purgado el ibdata1 y tendremos cada tabla en un fichero propio, pudiendo optimizar la misma si así lo queremos.

 

Deja un comentario

Your email address will not be published.