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:
-
Solo debe crearse el fichero
my.cnfomy.ini, pero no los dos. -
El fichero
my.cnfdebe colocarse en el directorio raíz de la unidadC:. -
El fichero
my.inidebería colocarse en el directorioWINDIR; por ejemplo,C:\WINDOWSoC:\WINNT. Puede utilizarse el comandoSETen una ventana de consola para mostrar el valor deWINDIR:C:\> SET WINDIR windir=C:\WINNT
-
Si el ordenador emplea un gestor de arranque donde la unidad
C:no es la unidad de arranque, sólo es posible emplear el ficheromy.ini. -
Si se instaló MySQL empleando los asistentes de instalación y configuración, el fichero
my.inise ubica en el directorio de instalación de MySQL. Consulte Sección 2.3.5.14, “Dónde está el fichero my.ini”.
¿Dónde deben especificarse las opciones en Unix? En Unix, mysqld lee las opciones en los siguientes ficheros, si existen, en el siguiente orden:
-
/etc/my.cnfOpciones globales.
-
$MYSQL_HOME/my.cnfOpciones específicas del servidor.
-
defaults-extra-fileEl fichero especificado con la opción
--defaults-extra-file. -
~/.my.cnfOpciones específicas del usuario.
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