Optimizando InnoDB en MySQL 5.6

innodb-supportRealmente MySQL 5.6 no es la panacea, trae, según los desarrolladores, muchas mejoras en InnoDB, yo a simple vista no he podido observar ninguna salvo una ligera mejora en la velocidad, aún no he probado el FULL TEXT INDEX. Lo que si se nota nada más actualizar MySQL es el aumento exponencial de los ficheros asociados a innodb, fundamentalmente el fichero ibdata.

Mysql guarda las tablas en un fichero denominado ibdata1, y aunque trunques las tablas y las
vacíes, este fichero no decrece.

Lo recomendable cuando instalas MySQL 5.6 (o actualizas a esta versión) es hacer un backup de cada tabla (así es más fácil la restauración en caso de error), borrar todas las tablas y las carpetas asociadas en /var/lib/mysql (salvo la de mysql), borrar los archivos de log de innodb (ib_logfile0 y ib_logfile1) y el archivo ibdata1.

Una vez hemos hecho esto modificamos nuestro archivo my.cnf ubicado en /etc/ y ponemos las siguientes líneas:

innodb_autoinc_lock_mode=0
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=100M
innodb_log_buffer_size=20M
innodb_buffer_pool_size=700M
innodb_flush_log_at_trx_commit=1

La importante es innodb_file_per_table con la cual crearemos un espacio de tablas individual para cada tabla, pero esto solo se aplica a tablas nueva, de ahí la necesidad de borrar todo y volverlo a crear. (el resto de opciones las veremos en otro post)

Una vez modificado el archivo my.conf reiniciamos el servidor MySQL e importamos una a una nuestras bases de datos. Con ello el archivos ibdata1 tendrá un tamaño muy muy pequeño y todos los espacios de tablas estarán en la misma carpeta que nuestra base de datos, un espacio de tabla por tabla de la base de datos.

NOTA: este espacio de tablas si es posible que se reduzca utilizando la consulta OPTIMIZE TABLE (se reducen los de la tabla en concreto el ibdata1 nunca se reduce)

Si esta opción no te vale y quieres seguir utilizando un solo archivo hay algunas consideraciones que debes tener en cuenta.

Si quieres moverlo a otra ubicación basta con mover el archivo a la nueva ubicación y modificar la directiva innodb_data_home_dir.

NOTA: si especificas innodb_data_file_path debes tener en cuenta que el tamaño que pongas del archivo tiene que ser menor que el tamaño del archivo que ya existe si no te va a dar un error.

Comentarios