En MySQL 5.0, el motor de almacenamiento InnoDB está habilitado por defecto. Si no se desean emplear tablas InnoDB, puede agregarse la opción skip-innodb al fichero de opciones de MySQL.

Dos recursos basados en disco muy importantes que gestiona el motor de almacenamiento InnoDB son sus ficheros de datos de espacios de tablas y sus ficheros de registro (log).

Si no se especifican opciones de configuración para InnoDB, MySQL 5.0 crea en el directorio de datos de MySQL un fichero de datos de 10MB (autoextensible) llamado ibdata1 y dos ficheros de registro (log) de 5MB llamados ib_logfile0 y ib_logfile1.

Nota: InnoDB dota a MySQL de un motor de almacenamiento transaccional (conforme a ACID) con capacidades de commit (confirmación), rollback (cancelación) y recuperación de fallas. Esto no es posible si el sistema operativo subyacente y el hardware no funcionan como se requiere. Muchos sistemas operativos o subsistemas de disco podrían diferir o reordenar operaciones de escritura a fin de mejorar el rendimiento. En algunos sistemas operativos, la propia llamada del sistema (fsync()), que debería esperar hasta que todos los datos no guardados de un fichero se graben a disco, en realidad puede retornar antes de que los datos se guarden en las tablas de almacenamiento. Debido a esto, una caída del sistema operativo o un corte en el suministro eléctrico pueden destruir datos recientemente grabados, o, en el peor de los casos, corromper la base de datos debido a que las operaciones de escritura han sido reordenadas. Si la integridad de los datos es importante, se deberían llevar a cabo algunas pruebas que simulen caídas (“pull-the-plug”) e interrupciones súbitas, antes de comenzar el uso para producción. En Mac OS X 10.3 y posteriores, InnoDB emplea un método especial de volcado a fichero llamado fcntl(). Bajo Linux, es aconsejable deshabilitar el write-back cache.

En discos duros ATAPI, un comando como hdparm -W0 /dev/hda puede funcionar. Hay que tener en cuenta que algunas unidades o controladores de disco podrían estar imposibilitados de desactivar el write-back cache.

Nota: Para obtener un buen desempeño, se deberían proveer expresamente los parámetros de InnoDB como se explica en los siguientes ejemplos. Naturalmente, habrá que editar la configuración para acomodarla a los requerimientos del hardware en uso.

Para configurar los ficheros de espacio de tablas de InnoDB, debe utilizarse la opción innodb_data_file_path en la sección [mysqld] del fichero de opciones my.cnf. En Windows, se puede emplear en su lugar my.ini. El valor de innodb_data_file_path debería ser una lista de una o más especificaciones de ficheros. Si se incluirá más de un fichero de datos, habrá que separarlos con punto y coma (';'):

innodb_data_file_path=espec_fichero_datos1
[;espec_fichero_datos2]...

Por ejemplo, la siguiente es una configuración que creará explícitamente un espacio de tablas con las mismas características que el predeterminado:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

Esto configura un único fichero de 10MB llamado ibdata1 el cual es autoextensible. No se suministra la ubicación del fichero, por lo tanto, el directorio predeterminado es el directorio de datos de MySQL.

El tamaño del fichero se especifica empleando como sufijo las letras M o G para indicar unidades de MB o GB.

A continuación se configura un espacio de tablas que contiene un fichero de datos de tamaño fijo de 50MB llamado ibdata1 y un fichero autoextensible de 50MB llamado ibdata2, ambos en el directorio de datos:

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

La sintaxis completa para especificar un fichero de datos incluye el nombre del fichero, su tamaño, y varios atributos opcionales:

nombre_de_fichero:tamaño_de_fichero[:autoextend[:max:tamaño_máximo_de_fichero]]

El atributo autoextend y aquellos que lo siguen sólo pueden emplearse con el último fichero en la línea de innodb_data_file_path.

Si se especifica la opción autoextend para el último fichero de datos, InnoDB incrementará el tamaño del fichero si se queda sin capacidad para el espacio de tablas. El incremento es de 8MB cada vez.

Si se agotara la capacidad del disco, podría desearse agregar otro fichero de datos en otro disco. Las instrucciones para reconfigurar un espacio de tablas existente se encuentran en Sección 15.7, “Añadir y suprimir registros y ficheros de datos InnoDB”.

InnoDB no detecta el tamaño máximo de fichero, por lo tanto, hay que ser cuidadoso en sistemas de ficheros donde el tamaño máximo sea de 2GB. Para especificar el tamaño máximo de un fichero autoextensible, se emplea el atributo max. La siguiente configuración le permite a ibdata1 crecer hasta un límite de 500MB:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M

InnoDB crea los ficheros de espacios de tablas en el directorio de datos de MySQL en forma predeterminada. Para especificar una ubicación expresamente, se emplea la opción innodb_data_home_dir. Por ejemplo, para crear dos ficheros llamados ibdata1 e ibdata2 pero creándolos en el directorio /ibdata, InnoDB se configura de este modo:

[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

Nota: InnoDB no crea directorios, de modo que hay que estar seguro de que el directorio /ibdata existe antes de iniciar el servidor. Esto se aplica también a cualquier directorio de ficheros de registro (log) que se configure. Para crear los directorios necesarios se emplea el comando mkdir que existe en Unix y DOS.

InnoDB forma el directorio para cada fichero de datos concatenando el valor textual de innodb_data_home_dir con el nombre del fichero, agregando una barra o barra invertida entre ellos si se necesita. Si la opción innodb_data_home_dir no aparece en my.cnf, el valor predeterminado es el directorio ./, lo cual indica el directorio de datos de MySQL.

Si se especifica una cadena vacía en innodb_data_home_dir, se pueden especificar rutas absolutas para los ficheros de datos listados en el valor de innodb_data_file_path. El siguiente ejemplo es equivalente al anterior:

[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend

Un ejemplo sencillo de my.cnf . Suponiendo que se posee un ordenador con 128MB de RAM y un disco duro, el siguiente ejemplo muestra posibles parámetros de configuración InnoDB en my.cnf o my.ini incluyendo el atributo autoextend.

Este ejemplo satisface las necesidades de la mayoría de los usuarios, tanto en Unix como en Windows, que no deseen distribuir los ficheros de datos InnoDB en varios discos. Crea un fichero de datos autoextensible llamado ibdata1 y dos ficheros de registro (log) de InnoDB llamados ib_logfile0 y ib_logfile1 en el directorio de datos de MySQL. También, el fichero de registros archivados de InnoDB ib_arch_log_0000000000 que MySQL crea automáticamente, termina ubicado en el directorio de datos.

[mysqld]
# Las demas opciones del servidor MySQL pueden escribirse aquí
# ...
# Los ficheros de datos deben ser capaces de contener datos e índices
# Hay que asegurarse de tener suficiente espacio en disco.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Establecer el tamaño del buffer en un 50-80% de la memoria del ordenador
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
#
# Establecer el tamaño del fichero de registro (log) en un 25% del tamaño del
buffer
set-variable = innodb_log_file_size=20M
set-variable = innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1

Hay que asegurarse de que el servidor MySQL tiene los derechos de acceso apropiados para crear ficheros en el directorio de datos. Más generalmente, el servidor debe tener derechos de acceso a cualquier directorio donde necesite crear ficheros de datos o registro (logs).

Notar que los ficheros de datos deben ser menores de 2GB en algunos sistemas de ficheros. El tamaño combinado de los ficheros de registro debe ser menor de 4GB. El tamaño combinado de los ficheros de datos debe ser de por lo menos 10MB.

Cuando se crea un espacio de tablas InnoDB por primera vez, es mejor iniciar el servidor MySQL desde la línea de comandos. Entonces, InnoDB imprimirá en pantalla la información acerca de la creación de bases de datos, de forma que se podrá ver lo que está ocurriendo. Por ejemplo, en Windows, si mysqld-max se ubica en C:\mysql\bin, se puede iniciar de este modo:

C:\> C:\mysql\bin\mysqld-max --console

Si no se envía la salida del servidor a la pantalla, se puede ver el fichero de registro de errores del servidor para averiguar lo que InnoDB imprime durante el proceso de inicio.

Consulte Sección 15.5, “Crear el espacio de tablas InnoDB” para un ejemplo de cómo debería lucir la información mostrada por InnoDB.

¿Dónde deben especificarse las opciones en Windows? Las reglas para ficheros de opciones en Windows son las siguientes:

¿Dónde deben especificarse las opciones en Unix? En Unix, mysqld lee las opciones en los siguientes ficheros, si existen, en el siguiente orden:

MYSQL_HOME representa una variable de entorno la cual contiene la ruta al directorio que hospeda al fichero específico de servidor my.cnf.

Si se desea estar seguro de que mysqld lee sus opciones únicamente desde un fichero determinado, se puede emplear --defaults-option como la primera opción en la línea de comandos cuando se inicia el servidor:

mysqld --defaults-file=ruta_a_my_cnf

Un ejemplo avanzado de my.cnf . Suponiendo que se posee un ordenador Linux con 2GB de RAM y tres discos duros de 60GB (en los directorios /, /dr2 y /dr3). El siguiente ejemplo muestra posibles parámetros de configuración InnoDB en my.cnf.

[mysqld]
# Las demas opciones del servidor MySQL pueden escribirse aquí
# ...
innodb_data_home_dir =
#
# Los ficheros de datos deben ser capaces de contener datos e índices
innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
#
# Establecer el tamaño del buffer en un 50-80% de la memoria del ordenador,
# pero hay que asegurarse que en Linux x86 el uso total de memoria es < 2GB
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#
innodb_log_files_in_group = 2
#
# Establecer el tamaño del fichero de registro (log) en un 25% del tamaño del
buffer
innodb_log_file_size=250M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
#
# Quitar marca de comentario a las siguientes lineas si se desea usarlas
#innodb_thread_concurrency=5

Nótese que el ejemplo ubica los dos ficheros de datos en discos diferentes. InnoDB llena el espacio de tablas comenzando por el primer fichero de datos. En algunos casos, el rendimiento de la base de datos mejorará si no se colocan todos los datos en el mismo disco físico. Colocar los ficheros de registro (log) en un disco diferente a los datos, a menudo es beneficioso para el rendimiento. También se pueden utilizar dispositivos en bruto (raw devices) como ficheros de datos InnoDB, lo cual mejorará la velocidad de E/S. Consulte Sección 15.14.2, “Usar dispositivos en bruto (raw devices) para espacios de tablas”.

Advertencia: En GNU/Linux x86 de 32 bits, se debe tener cuidado con no establecer el uso de memoria en un número demasiado alto. glibc le puede permitir al heap de proceso que crezca por sobre la pila de los subprocesos, lo cual hará caer el servidor. Es arriesgado que el resultado del siguiente cálculo exceda los 2GB:

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

Cada hilo emplea una pila (a menudo de 2MB, pero de solamente 256KB en los binarios de MySQL AB) y en el peor caso también empleará una cantidad de memoria adicional igual a sort_buffer_size + read_buffer_size.

Compilando MySQL por sí mismo, el usuario puede emplear hasta 64GB de memoria física en Windows de 32 bits. Consulte la descripción de innodb_buffer_pool_awe_mem_mb en Sección 15.4, “Opciones de arranque de InnoDB”.

¿Cómo deben ajustarse otros parámetro del servidor mysqld? Los siguientes son valores típicos adecuados para la mayoría de los usuarios:

[mysqld]
skip-external-locking
max_connections=200
read_buffer_size=1M
sort_buffer_size=1M
#
# Establecer key_buffer a un 5 - 50% de la RAM., dependiendo de cuánto se usen
# tablas MyISAM, pero manteniendo key_buffer_size + InnoDB
# buffer pool size < 80% de la RAM
key_buffer_size=value