UNIVERSIDAD TECNOLÓGICA

DE CD JUÁREZ

InnoDB

Materia: Base De Datos

Maestra: Gabriela Macias

Alumno: Luis Enrique Pérez Campos

Grupo: ITIC 22

Es una tecnología de almacenamiento de datos de código abierto para la base de datos MySQL, incluidos

como formato de tabla estándar en todas las distribuciones de MySQL AB a partir de las versiones 4.0. Su característica principal es que soporta transacciones de tipo ACID y bloqueo de registros e integridad referencial. InnoDB ofrece una fiabilidad y consistencia muy superior a MyISAM, la anterior tecnología de tablas de MySQL, si bien el mejor rendimiento de uno u otro formato dependerá de la aplicación específica.

InnoDB

En octubre de 2005, Oracle Corp. adquirió a la finlandesa Innobase, compañía que desarrolla InnoDB.

Comparación con MyISAMMyISAM es la alternativa habitual a InnoDB a la hora de escoger la tecnología de almacenamiento de datos en MySQL. Estas son algunas de las diferencias entre los dos:

InnoDB se recupera de un problema volviendo a ejecutar sus logs, mientras que MyISAM necesita repasar todos los índices y tablas que hayan sido actualizados y reconstruirlos si esos cambios no han sido escritos en disco. El primer proceso requiere más o menos el mismo tiempo siempre, mientras que el segundo aumenta con el tamaño de la base de datos.

MyISAM deja al sistema operativo la tarea de hacer la caché de las lecturas y escrituras de los registros, mientras que InnoDB realiza él mismo la tarea, combinando cachés de registro y de índice. InnoDB no envía directamente los cambios en las tablas al sistema operativo para que las escriba, lo que puede hacerlo mucho más rápido que MyISAM en ciertos escenarios.

InnoDB almacena físicamente los registros en el orden de la clave primaria, mientras que MyISAM los guarda en el orden en que fueron añadidos. Cuando la clave primaria se escoge de acuerdo con las necesidades de las consultas más habituales esto puede suponer una mejora sustancial del rendimiento. Por otro lado, si los datos se insertan en un orden que difiera sustancialmente del orden de la clave primaria, se obliga a InnoDB a reordenar mucho los datos para mantenerlos en el orden adecuado.

InnoDB no dispone de la compresión de datos de la que disfruta MyISAM, de modo que tanto el espacio en disco como la caché en la memoria RAM pueden ser más grandes. Este problema se ha reducido en MySQL 5.0, reduciéndolo en aproximadamente un 20%.

Cuando opera con transacciones ACID, InnoDB debe escribir en disco al menos una vez por cada transacción, aunque puede combinar las escrituras de varias inserciones concurrentes. Para los discos duros típicos, esto supone un límite de aproximadamente 200 transacciones por segundo, por lo que aumentarlas exige controladores de disco con caché de escritura y sistema de alimentación ininterrumpido para mantener la integridad. InnoDB ofrece diversos modos de funcionamiento que reducen este efecto, pero conllevan una pérdida de integridad transaccional. MyISAM no tiene ese problema porque no soporta transacciones.

Configuración de InnoDB

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 = /ibdatainnodb_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 ordenadorset-variable = innodb_buffer_pool_size=70Mset-variable = innodb_additional_mem_pool_size=10M## Establecer el tamaño del fichero de registro (log) en un 25% del tamaño delbufferset-variable = innodb_log_file_size=20Mset-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.cnf o my.ini, pero no los dos. El fichero my.cnf debe colocarse en el directorio raíz de la unidad C:. El fichero my.ini debería colocarse en el directorio WINDIR; por ejemplo, C:\

WINDOWS o C:\WINNT. Puede utilizarse el comando SET en una ventana de consola para mostrar el valor de WINDIR:

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 fichero my.ini. Si se instaló MySQL empleando los asistentes de instalación y

configuración, el fichero my.ini se 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.cnf

Opciones globales.

$MYSQL_HOME/my.cnf

Opciones específicas del servidor.

defaults-extra-file

El fichero especificado con la opción --defaults-extra-file.

~/.my.cnf

Opciones 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 índicesinnodb_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 < 2GBinnodb_buffer_pool_size=1Ginnodb_additional_mem_pool_size=20Minnodb_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 delbufferinnodb_log_file_size=250Minnodb_log_buffer_size=8M#innodb_flush_log_at_trx_commit=1innodb_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-lockingmax_connections=200read_buffer_size=1Msort_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 RAMkey_buffer_size=value