systemadmin.es > DBA > Fichero ibdata1 enorme y el parámetro innodb_file_per_table

Fichero ibdata1 enorme y el parámetro innodb_file_per_table

Por defecto en MySQL, los datos de las tablas InnoDB se van guardando en el fichero ibdata1 (system tablespace) dentro del datadir de MySQL. Esto acabar normalmente generando un fichero enorme e inmanejable.

Dicho fichero (en terminología de InnoDB es un tablespace) tiene el inconveniente que crece indefinidamente y no se puede reducir. Por ejemplo, si por error se ha generado una tabla enorme, el ibdata con el autoextend por defecto crecerá para albergar los datos, pero al borrar la tabla ya no volverá a su tamaño original sino a lo máximo que haya llegado. Además, si lo borramos nos cargaremos las tablas InnoDB.

En la opción innodb_data_file_path, evidentemente que podemos controlar el tamaño máximo del fichero con max, pero eso no quita que el fichero pueda ser del tamaño que indiquemos y se este utilizando mucho menos.

La única opción que nos queda para un ibdata enorme es hacer un dump de la base de datos, borrar los datafiles, recrear la base de datos e importar los datos. Evidentemente esto no es nada pràctico.

Como que no me gusta ir añadiendo ficheros/discos en el innodb_data_file_path, por ejemplo:

innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend:max:100M

Personalmente prefiero que cada tabla tenga su tablespace, esto lo conseguimos con la opción innodb_file_per_table. Habilitando esta opción, las tablas que se creen a partir que este habilitado lo harán con un fichero por tabla (.ibd)

Las ventajas de de usar innodb_file_per_table son:

  • Recuperar el espacio al borrar o hace un OPTIMIZE de una tabla
  • Podemos mover tablas específicas a diferentes discos, en el caso de usar un solo tablespace podemos añadir discos pero no controlamos que tablas van a esos discos
  • Restaurar una tabla entera mediante ALTER TABLE tabla DISCARD TABLESPACE; y ALTER TABLE tabla IMPORT TABLESPACE;

Las tablas que ya estén creadas en el ibdata las podemos separar a ficheros independientes, una vez este habilitado el innodb_file_per_table, mediante un OPTIMIZE.

4 comments to “Fichero ibdata1 enorme y el parámetro innodb_file_per_table”

  1. Hola,

    ¿sabes si tiene algun inconveniente? ¿como rendimiento o algo?

    porque si configurarlo así parece mejor, porque no viene así de entrada

    Santi.

    Gracias por el post.

  2. Las ventajas de usar un solo tablespace es que resulta más simple, además si usas rawdevices te saltas el sistema de ficheros, por lo que en determinadas situaciones será mas rápido. No he hecho ningún benchmark ya que me importan más las ventajas de innodb_file_per_table pero seria un buen experimento a hacer.

  3. Yo tendría en cuenta el parámetro open_files para ajustar sifuese necesario el número máximo de ficheros abiertos de forma concurrente, si no, obligaríamos a MySQL a andar abriendo y cerrando ficheros lo cual iría en contra del rendimiento ya que pasas de tener 1 fichero para todas las tablas InnoDB a tener 1 fichero por cada tabla,.

  4. oye y como hago todo lo que dijiste ahi? es practiacamente lo que necesito, eliminar espacio, y mover tablas específicas a diferentes discos

Deja un comentario:

XHTML - Tags permitidos:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>