Capítulo 15. El motor de almacenamiento `InnoDB`
Anterior |  | Siguiente

## Capítulo 15. El motor de almacenamiento `InnoDB`

## 15.1. Panorámica de `InnoDB`

`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. `InnoDB` realiza bloqueos a nivel de fila y también porporciona funciones de lectura consistente sin bloqueo al estilo Oracle en sentencias `SELECT`. Estas características incrementan el rendimiento y la capacidad de gestionar múltiples usuarios simultáneos. No se necesita un bloqueo escalado en `InnoDB` porque los bloqueos a nivel de fila ocupan muy poco espacio. `InnoDB` también soporta restricciones `FOREIGN KEY`. En consultas SQL, aún dentro de la misma consulta, pueden incluirse libremente tablas del tipo `InnoDB` con tablas de otros tipos.

`InnoDB` se diseñó para obtener el máximo rendimiento al provesar grandes volúmenes de datos. Su eficiencia en el uso de CPU probablemente no es igualada por ningún otro motor de bases de datos relaciones en disco.

A pesar de estar totalmente integrado con el servidor MySQL, el motor de almacenamiento `InnoDB` mantiene su propio pool de almacenamiento intermedio para tener un cache de datos e índices en la memoria principal. `InnoDB` almacena sus tablas e índices en un espacio de tablas, el cual puede consistir de varios ficheros (o particiones disco). Esto difiere de, por ejemplo, el motor `MyISAM`, donde cada tabla se almacena empleando ficheros separados. Las tablas `InnoDB` pueden ser de cualquier tamaño, aún en sistemas operativos donde el tamaño de los ficheros se limita a 2GB.

En MySQL 5.0, `InnoDB` viene incluido por defecto en las distribuciones binarias. El instalador Windows Essentials configura a `InnoDB` como el tipo de base de datos MySQL por defecto en Windows.

`InnoDB` se utiliza en muchos grandes sitios de bases de datos que necesitan alto rendimiento. El famoso sitio de noticias de Internet Slashdot.org corre sobre `InnoDB`. Mytrix, Inc. almacena más de 1TB de datos en `InnoDB`, y otros sitios manejan una carga promedio de 800 inserciones y actualizaciones por segundo en `InnoDB`.

`InnoDB` se publica bajo la misma licencia GNU GPL Versión 2 (de Junio de 1991) que MySQL. Para más información sobre el licenciamiento de MySQL, consulte http://www.mysql.com/company/legal/licensing/.

## 15.2. Información de contacto de `InnoDB`

Información de contacto para Innobase Oy, creador del motor `InnoDB`:

    Sitio web: http://www.innodb.com/
    Correo electrónico: <sales@innodb.com>
    Teléfonos: +358-9-6969 3250 (oficina)
               +358-40-5617367 (móvil)

    Innobase Oy Inc.
    World Trade Center Helsinki
    Aleksanterinkatu 17
    P.O.Box 800
    00101 Helsinki
    Finland

## 15.3. 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 = /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.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 í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

## 15.4. Opciones de arranque de `InnoDB`

Esta sección describe las opciones de servidor relacionadas con `InnoDB`. En MySQL 5.0, todas son especificadas con la forma `--`opt_name`=`value`` en la línea de comandos o en ficheros de opciones.

- `innodb_additional_mem_pool_size`

  El tamaño del pool de memoria que `InnoDB` utiliza para almacenar información del diccionario de datos y otras estructuras de datos internas. Mientras más tablas se tengan en la aplicación, mayor será este tamaño. Si `InnoDB` se queda sin memoria en este pool, comenzará a tomar memoria del sistema operativo, y dejará mensajes de advertencia en el log de errores de MySQL. El valor por defecto es 1MB.

- `innodb_autoextend_increment`

  El tamaño a incrementar (en megabytes) cuando se extiende el tamaño de un espacio de tablas autoextensible, luego de llenarse. El valor por defecto es 8. Esta opción puede cambiarse en tiempo de ejecución como una variable de sistema global.

- `innodb_buffer_pool_awe_mem_mb`

  El tamaño (en MB) del pool de buffer, si está ubicado en la memoria AWE en Windows de 32 bits, y sólo relevante en este tipo de sistemas operativos. Si el sistema operativo Windows de 32 bits en uso soporta más de 4GB de memoria, usualmente llamado “Address Windowing Extensions”, se puede ubicar el pool del buffer de `InnoDB` dentro de la memoria física AWE utilizando este parámetro. El máximo valor posible es de 64000. Si se especifica este parámetro, `innodb_buffer_pool_size` es la ventana en el espacio de direcciones de 32 bits de mysqld donde `InnoDB` direcciona la memoria AWE. Un valor adecuado para `innodb_buffer_pool_size` son 500MB.

- `innodb_buffer_pool_size`

  El tamaño del buffer de memoria que `InnoDB` emplea para el almacenamiento intermedio de los datos e índices de sus tablas. Mientras más grande sea este valor, menores operaciones de E/S en disco serán necesarias para acceder a los datos de las tablas. En un servidor de bases de datos dedicado, se puede establecer este valor en hasta el 80% de la memoria física del ordenador. Sin embargo, no debe establecerse en un valor demasiado grande porque la pugna por la memoria física podría causar que el sistema oeprativo comience a paginar.

- `innodb_checksums`

  `InnoDB` emplea validación por sumas de verificación (checksums) en todas las páginas leídas desde el disco, para asegurar una tolerancia extra contra fallas frente a hardware averiado o ficheros corruptos. Sin embargo, bajo ciertas circunstancias inusuales (por ejemplo al ejecutar pruebas de rendimiento) esta característica extra de seguridad es innecesaria. En tales casos, esta opción (que está habilitada por defecto) puede deshabilitarse con `--skip-innodb-checksums`. Esta opción fue agregada en MySQL 5.0.3.

- `innodb_data_file_path`

  Las rutas a los ficheros individuales de datos y sus tamaños. La ruta de directorio completa a cada fichero de datos se obtiene concatenando `innodb_data_home_dir` con cada ruta especificada aquí. Los tamaños de fichero se especifican en megabytes o gigabytes (1024MB) agregando `M` o `G` al valor que representa el tamaño. La sumatoria de los tamaños de fichero debe ser de al menos 10MB. En algunos sistemas operativos, los ficheros deben tener menos de 2GB. Si no se indica `innodb_data_file_path`, el comportamiento predeterminado de inicio es crear un único fichero autoextensible de 10MB llamado `ibdata1`. En aquellos sistemas operativos que soporten ficheros grandes, se puede establecer el tamaño de fichero en más de 4GB. También pueden utilizarse como ficheros de datos particiones de dispositivos en bruto. Consulte Sección 15.14.2, “Usar dispositivos en bruto (raw devices) para espacios de tablas”.

- `innodb_data_home_dir`

  La porción común de la ruta de directorio para todos los ficheros de datos `InnoDB`. Si este valor no se establece, por defecto será el directorio de datos de MySQL. También puede especificarse como una cadena vacía, en cuyo caso se podrán utilizar rutas absolutas en `innodb_data_file_path`.

- `innodb_doublewrite`

  Por defecto, `InnoDB` almacena todos los datos dos veces, la primera en el buffer de escritura doble (o doublewrite), y luego a los ficheros de datos reales. Esta opción puede emplearse para desactivar dicha funcionalidad. Al igual que `innodb_checksums`, esta opción está habilitada por defecto; puede desactivarse con `--skip-innodb-doublewrite` en pruebas de rendimiento o casos en que el máximo desempeño prevalezca sobre la preocupacion por la integridad de los datos o las posibles fallas. Esta opción se agregó en MySQL 5.0.3.

- `innodb_fast_shutdown`

  Si se establece a 0, `InnoDB` efectúa una descarga completa y vuelca los buffers de inserción antes de llevar a cabo el cierre del servidor. Estas operaciones pueden tomar minutos o incluso horas en casos extremos. Si se establece en 1, `InnoDB` pasa por alto estas operaciones al cierre. El valor por defecto es 1. Si se establece en 2 (opción que está disponible desde MySQL 5.0.5, excepto en Netware), InnoDB simplemente vuelca a disco sus registros (logs) y se cierra en frío, como si hubiera ocurrido una caída; ninguna transacción confirmada se perderá, pero en el siguiente inicio se ejecutará una recuperación ante caídas.

- `innodb_file_io_threads`

  El número de subprocesos (threads) de E/S de fichero en `InnoDB`. Normalmente esto debería ser dejado en el valor predeterminado de 4, pero la E/S de disco en Windows puede beneficiarse con un número mayor. En Unix, incrementar el número no tiene efecto; `InnoDB` siempre utiliza el valor predeterminado.

- `innodb_file_per_table`

  Esta opción provoca que `InnoDB` cree cada nueva tabla utilizando su propio fichero `.ibd` para almacenar datos e índices, en lugar de colocarlo en el espacio de tablas compartidas. Consulte Sección 15.6.6, “Usar un espacio de tablas para cada tabla”.

- `innodb_flush_log_at_trx_commit`

  Cuando `innodb_flush_log_at_trx_commit` se establece en 0, una vez por segundo el buffer de registros (log buffer) se graba en el fichero de registro y se vuelca a disco, pero no se hace nada al confirmar una transacción. Cuando este valor es 1 (predeterminado), cada vez que se confirma una transacción el buffer de registros (log buffer) se graba en el fichero de registro y se vuelca a disco Cuando se establece en 2, el buffer de registros (log buffer) se graba en el fichero de registro, pero no se vuelca a disco. Sin embargo, el volcado a disco del fichero de registro se produce una vez por segundo también cuando vale 2. Se debe tener en cuenta que el volcado una vez por segundo no está 100% garantizado que ocurra en cada segundo, debido a cuestiones de programación (scheduling) de procesos. Se puede alcanzar un mayor rendimiento estableciendo un valor diferente de 1, pero en caso de caída se puede perder un segundo de transacciones. Si se establece el valor en 0, cualquier caída en un proceso de mysqld puede borrar el último segundo de transacciones. Si se establece el valor en 2, entonces únicamente una caída del sistema operativo o una interrupción de energía pueden borrar el último segundo de transacciones. Hay que notar que muchos sistemas operativos y algunos tipos de discos puedne ser engañosos en las operaciones de volcado a disco. Podrían indicarle a mysqld que el volcado ha tenido lugar, aunque no sea así. En tal caso la estabilidad de las transacciones no está garantizada ni aún con el valor 1, y en el peor de los casos una interrupción de energía puede incluso corromper la base de datos InnoDB. Utilizar un caché de disco apoyado por baterías en el controlador de disco SCSI o en el propio disco, acelera los volcados a disco, y hace más segura la operación. También puede intentarse con el comando de Unix hdparm, el cual deshabilita el almacenamiento en caches de hardware de las operaciones de escritura a disco, o utilizar algún otro comando específico del fabricante del hardware. El valor por defecto de esta opción es 1

- `innodb_flush_method`

  Esta opción solamente es relevante en sistemas Unix. Si se establece en `fdatasync` (el valor predeterminado), `InnoDB` utiliza `fsync()` para volcar tanto los ficheros de datos como de registro (log). Si se establece en `O_DSYNC`, `InnoDB` emplea `O_SYNC` para abrir y volcar los ficheros de registro, pero utiliza `fsync()` para volcar los ficheros de datos. Si se especifica `O_DIRECT` (disponible en algunas versiones de GNU/Linux), `InnoDB` utiliza `O_DIRECT` para abrir los ficheros de datos, y `fsync()` para volcar tanto los ficheros de datos como de registro. Nótese que `InnoDB` emplea `fsync()` en lugar de `fdatasync()`, y no emplea `O_DSYNC` por defecto porque han ocurrido problemas con éste en muchas variedades de Unix.

- `innodb_force_recovery`

  Advertencia: Esta opción debería ser definida solamente en una situación de emergencia cuando se desean volcar las tablas desde una base de datos corrupta. Los posibles valores van de 1 a 6. Los significados de estos valores se describen en Sección 15.8.1, “Forzar una recuperación”. Como una medida de seguridad, `InnoDB` impide que un usuario modifique datos cuando esta opción tiene un valor mayor a 0.

- `innodb_lock_wait_timeout`

  El límite de tiempo, en segundos, que una transacción `InnoDB` puede esperar por un bloqueo antes de ser cancelada. `InnoDB` automáticamente detecta bloqueos mutuos (deadlocks) en su propia tabla de bloqueos, y cancela la transacción. InnoDB detecta los bloqueos por el uso de la sentencia `LOCK TABLES`. El valor predeterminado es de 50 segundos.

  Para conseguir la mayor estabilidad y consistencia posibles en una configuración de replicación, se debería utilizar `innodb_flush_logs_at_trx_commit=1`, `sync-binlog=1`, y `innodb_safe_binlog` en el fichero `my.cnf` principal.

- `innodb_locks_unsafe_for_binlog`

  Esta opción desactiva el bloqueo de la clave siguiente en búsquedas y exploraciones de índices `InnoDB`. El valor por defecto de esta opción es falso.

  Normalmente, `InnoDB` utiliza un algoritmo denominado bloqueo de clave siguiente (next-key). `InnoDB` efectúa un bloqueo a nivel de fila de tal forma que cuando busca o explora el índice de una tabla, establece bloqueos compartidos o exclusivos en cualquier registro de índice que encuentre. El bloqueo que `InnoDB` establece en registros de índice también afecta al “vacío” que precede a ese registro. Si un usuario tiene un bloqueo compartido o exclusivo sobre el registro R en un índice, otro usuario no puede insertar un nuevo registro de índice inmediatamente antes de R en el orden del índice. Esta opción provoca que `InnoDB` no utilice el bloqueo de clave siguiente en búsquedas o exploraciones de índices. El bloqueo de clave siguiente es todavía utilizado para asegurar las restricciones de claves foráneas y la verificación de claves duplicadas. Nótese que el uso de esta opción puede provocar problemas secundarios: suponiendo que se deseen leer y bloquear todos los registros hijos de la tabla `child` que tengan un identificador mayor a 100, junto al posterior intento de actualizar algunas columnas en las filas seleccionadas:

      SELECT * FROM child WHERE id > 100 FOR UPDATE;

  Supóngase que hay un índice sobre la columna `id`. La consulta explora aquel índice comenzando por el primer registro en que `id` sea mayor que 100. Si el bloqueo efectuado sobre los registros del índice no bloquea las inserciones realizadas en los espacios vacíos, en la tabla se insertará un nuevo registro. Si se ejecuta el mismo `SELECT` dentro de la misma transacción, se verá un nuevo registro en el conjunto de resultados devuelto por la consulta. Esto también significa que si se agregan nuevos elementos a la base de datos, InnoDB no garantiza la serialización; sin embargo, los conflictos de serialización aún están garantizados. Por lo tanto, si esta opción se utiliza, InnoDB garantiza como mucho el nivel de aislamiento `READ COMMITTED`.

  A partir de MySQL 5.0.2 esta opción es aún más insegura. `InnoDB` en un `UPDATE` o `DELETE` solamente bloquea los registros que se actualizan o borran. Esto reduce notablemente la probabilidad de bloqueos mutuos (deadlocks), pero aún pueden ocurrir. Nótese que esta opción todavía no le permite a operaciones como `UPDATE` predominar sobre otras operaciones similares (como otro `UPDATE`) aún en el caso en que actúen sobre registros diferentes. Considérese lo siguiente: example:

      CREATE TABLE A(A INT NOT NULL, B INT);
      INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
      COMMIT;

  Si una conexión realiza una consulta:

      SET AUTOCOMMIT = 0;
      UPDATE A SET B = 5 WHERE B = 3;

  y la otra conexión ejecuta otra consulta a continuación de la primera:

      SET AUTOCOMMIT = 0;
      UPDATE A SET B = 4 WHERE B = 2;

  La consulta dos tendrá que esperar la confirmación o la cancelación de la consulta uno, porque ésta tiene un bloqueo exclusivo en el registro (2,3), y la consulta dos, mientras explora los registros, también intenta colocar un bloqueo exclusivo en la misma fila, cosa que no puede hacer. Esto se debe a que la consulta dos primero establece el bloqueo sobre un registro y luego determina si el registro pertenece al conjunto de resultados, y si no es así libera el bloqueo innecesario, cuando se emplea la opción `innodb_locks_unsafe_for_binlog`.

  Por lo tanto, la consulta uno se ejecuta de este modo:

      x-lock(1,2)
      unlock(1,2)
      x-lock(2,3)
      update(2,3) to (2,5)
      x-lock(3,2)
      unlock(3,2)
      x-lock(4,3)
      update(4,3) to (4,5)
      x-lock(5,2)
      unlock(5,2)

  entonces la consulta dos se ejecuta así:

      x-lock(1,2)
      update(1,2) to (1,4)
      x-lock(2,3) - wait for query one to commit or rollback

- `innodb_log_arch_dir`

  El directorio donde los ficheros de registro (logs) terminados se archivarán si se utiliza el archivo de ficheros de registro. Si se utiliza, el valor de este parámetro debería ser el mismo que `innodb_log_group_home_dir`. Sin embargo, no es requerido.

- `innodb_log_archive`

  Este valor generalmente debería establecerse a 0. Debido a que la recuperación a partir de una copia de respaldo es realizada por MySQL empleando sus propios ficheros de registro (log), en general no hay necesidad de archivar los ficheros de registro de `InnoDB`. El valor predeterminado para esta opción es 0.

- `innodb_log_buffer_size`

  El tamaño del buffer que `InnoDB` emplea para escribir los ficheros de registro (logs) en disco. Los valores razonables se encuentran entre 1MB y 8MB. El valor predeterminado es 1MB. Un buffer de fichero de registro grande le permite a las transacciones extensas ejecutarse sin necesidad de guardar el fichero de registro en disco antes de que la transacción se confirme. Por lo tanto, si se manejan grandes transacciones, incrementar el tamaño del buffer de ficheros de registro ahorra operaciones de E/S en disco.

- `innodb_log_file_size`

  El tamaño de cada fichero de registro (log) en un grupo de ficheros de registro. El tamaño combinado de estos ficheros debe ser inferior a 4GB en ordenadores de 32 bits. El valor predeterminado es de 5MB. El rango de valores razonables va desde 1MB hasta la 1/`N` parte del tamaño del pool de buffer, donde `N` es la cantidad de ficheros de registro en el grupo. Mientras mayor es el valor, menor es la cantidad de guardado de puntos de verificación que se necesitan en el pool de buffer, ahorrando operaciones de E/S en disco. Pero tener ficheros de registro más grandes también significa que la recuperación es más lenta en caso de caídas.

- `innodb_log_files_in_group`

  En un grupo de ficheros de registro (logs), es la cantidad de ficheros que contiene. `InnoDB` escribe en los ficheros siguiendo una forma circular. El valor predeterminado es 2 (recomendado).

- `innodb_log_group_home_dir`

  La ruta de directorio a los ficheros de registro (log) de `InnoDB`. Debe tener el mismo valor que `innodb_log_arch_dir`. Si no se especifican parámetros de ficheros de registro `InnoDB`, la acción predeterminada es crear dos ficheros de 5MB llamados `ib_logfile0` y `ib_logfile1` en el directorio de datos de MySQL.

- `innodb_max_dirty_pages_pct`

  Un entero en el rango de 0 a 100. El valor por defecto es 90. El subproceso (thread) principal en `InnoDB` intenta volcar páginas desde el pool de buffer de modo que a lo sumo este porcentaje de las páginas aún sin volcar sea volcado en un momento determinado. Si se tiene el privilegio `SUPER`, este porcentaje pude cambiarse mientras el servidor está en ejecución:

      SET GLOBAL innodb_max_dirty_pages_pct = value;

- `innodb_max_purge_lag`

  Esta opción controla la forma de retrasar las operaciones `INSERT`, `UPDATE` y `DELETE` cuando las operaciones de descarga (ver Sección 15.12, “Implementación de multiversión”) están sufiendo demoras. TEl valor por defecto de este parámetro es cero, lo que significa que no se retrasarán. Esta opción puede modificarse en tiempo de ejecución como una variable global de sistema.

  El sistema de transacciones de InnoDB mantiene una lista de transacciones que have delete-marked index records by `UPDATE` or `DELETE` operations. Se deja que la longitud de esta lista sea `purge_lag`. Cuando `purge_lag` excede a `innodb_max_purge_lag`, cada operación de `INSERT`, `UPDATE` y `DELETE` se retrasa durante ((`purge_lag`/`innodb_max_purge_lag`)*10)-5 milisegundos. El retraso se computa en el comienzo de un lote de descarga, cada diez segundos. Las operaciones no se retrasan si no puede ejecutarse la descarga debido a una vista de lectura consistente (consistent read) anterior que contenga los registros a ser descargados.

  Un escenario típico para una carga de trabajo problemática podría ser 1 millón, asumiendo que las transacciones son pequeñas, sólo 100 bytes de tamaño, y se pueden permitir 100 MB de registros sin descargar en las tablas.

- `innodb_mirrored_log_groups`

  El número de copias idénticas de grupos de ficheros de registro que se mantienen para la base de datos. Actualmente debería establecerse en 1.

- `innodb_open_files`

  Esta opción sólo es relevante si se emplean múltiples espacios de tablas en `InnoDB`. Especifica el número máximo de ficheros `.ibd` que `InnoDB` puede mantener abiertos al mismo tiempo. El mínimo es 10. El valor predeterminado es 300.

  Los descriptores de fichero empleados para ficheros `.ibd` son únicamente para `InnoDB`. Son independientes de los especificados por la opción de servidor `--open-files-limit`, y no afectan la operación del caché de tablas.

- `innodb_safe_binlog`

  Contribuye a asegurar la consistencia entre el contenido de las tablas `InnoDB` y el registro binario (binary log). Consulte Sección 5.10.3, “El registro binario (Binary Log)”.

- `innodb_status_file`

  Esta opción provoca que `InnoDB` cree un fichero ``<datadir>`/innodb_status.`<pid>`` para la salida períodica de `SHOW INNODB STATUS`. Disponible desde MySQL 4.0.21.

- `innodb_table_locks`

  `InnoDB` respeta lo establecido por `LOCK TABLES`, y MySQL no retorna desde un `LOCK TABLE .. WRITE` hasta que todos los otros flujos (threads) han levantado sus bloqueos a la tabla. El valor por defecto es 1, lo cual significa que `LOCK TABLES` causará que InnoDB bloquee una tabla internamente. En aplicaciones que emplean `AUTOCOMMIT=1`, los bloqueos internos de tabla de InnoDB pueden originar bloqueos mutuos (deadlocks). Se puede establecer `innodb_table_locks=0` en `my.cnf` (o `my.ini` en Windows) para eliminar ese problema.

- `innodb_thread_concurrency`

  `InnoDB` intenta mantener el número de flujos (threads) del sistema operativo que concurren dentro de `InnoDB` en un valor menor o igual al límite especificado por este parámetro. Antes de MySQL 5.0.8, el valor por defecto es 8. Si se tienen dificultades de rendimiento, y `SHOW INNODB STATUS` indica que hay muchos subprocesos esperando por semáforos, se podrían tener subprocesos pugnando por recursos, y se debería establecer este parámetro en un número mayor o menor. Si se posee un ordenador con varios procesadores y discos, se puede intentar aumentar el valor para hacer mejor uso de los recursos del ordenador. Un valor recomendado es la suma del número de procesadores y discos que tiene el sistema. Un valor de 500 o mayor deshabilitará la verificación de concurrencia. A partir de MySQL 5.0.8, el valor por defecto es 20, y la verificación de concurrencia se deshabilita si se establece en 20 o más.

- `innodb_status_file`

  Esta opción provoca que `InnoDB` cree un fichero ``<datadir>`/innodb_status.`<pid>`` para almacenar periódicamente la salida de `SHOW INNODB STATUS`.

## 15.5. Crear el espacio de tablas `InnoDB`

Suponiendo que se ha instalado MySQL y se editó el fichero de opciones para que contenga los parámetros de `InnoDB` necesarios, antes de iniciar MySQL se debería verificar que los directorios indicados para los ficheros de datos y de registro (log) `InnoDB` existen y que el servidor MySQL tiene permisos de acceso a dichos directorios. `InnoDB` no puede crear directorios, solamente ficheros. Hay que verificar también que se tiene suficiente espacio en disco para los ficheros de datos y de registro.

Cuando se crea una base de datos `InnoDB`, es mejor ejecutar el servidor MySQL mysqld desde la línea de comandos, no desde el envoltorio mysqld_safe o como un servicio de Windows. Cuando se lo ejecuta desde la línea de comandos, se puede ver lo que mysqld imprime y qué está ocurriendo. En Unix, simplemente debe invocarse mysqld. En Windows, hay que usar la opción `--console`.

Cuando se inicia el servidor MySQL luego de la configuración inicial de `InnoDB` en el fichero de opciones, `InnoDB` crea los ficheros de datos y de registro e imprime algo como lo siguiente:

    InnoDB: The first specified datafile /home/heikki/data/ibdata1
    did not exist:
    InnoDB: a new database to be created!
    InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728
    InnoDB: Database physically writes the file full: wait...
    InnoDB: datafile /home/heikki/data/ibdata2 did not exist:
    new to be created
    InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000
    InnoDB: Database physically writes the file full: wait...
    InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist:
    new to be created
    InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size
    to 5242880
    InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist:
    new to be created
    InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size
    to 5242880
    InnoDB: Doublewrite buffer not found: creating new
    InnoDB: Doublewrite buffer created
    InnoDB: Creating foreign key constraint system tables
    InnoDB: Foreign key constraint system tables created
    InnoDB: Started
    mysqld: ready for connections

Se ha creado una nueva base de datos `InnoDB`. Se puede conectar al servidor MySQL con los programas cliente acostumbrados, como mysql. Cuando se detiene el servidor MySQL, con mysqladmin shutdown, la salida es como la siguiente:

    010321 18:33:34  mysqld: Normal shutdown
    010321 18:33:34  mysqld: Shutdown Complete
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed

Se puede mirar en los directorios de ficheros de datos y registro y se verán los ficheros creados. El directorio de registro (log) también contiene un pequeño fichero llamado `ib_arch_log_0000000000`. Ese fichero resulta de la creación de la base de datos, luego de lo cual `InnoDB` desactivó el guardado de registros (log). Cuando MySQL inicia de nuevo, los ficheros de datos y de registro ya han sido creados, por lo que la salida es más breve:

    InnoDB: Started
    mysqld: ready for connections

Es posible agregar la opción `innodb_file_per_table` a `my.cnf`, y hacer que InnoDB almacene cada tabla en su propio fichero `.ibd` en un directorio de bases de datos de MySQL. Consulte Sección 15.6.6, “Usar un espacio de tablas para cada tabla”.

### 15.5.1. Resolución de problemas en la inicialización de `InnoDB`

Si `InnoDB` imprime un error de sistema operativo en una operación de ficheros, generalmente el problema es uno de los siguientes:

- No se creó el directorio para los ficheros de datos o de registros (log) de `InnoDB`.

- mysqld no tiene los permisos de acceso para crear ficheros en aquellos directorios.

- mysqld no puede leer el fichero de opciones `my.cnf` o `my.ini` adecuado, y por lo tanto no ve las opciones especificadas.

- El disco está lleno o se excedió la cuota de disco.

- Se ha creado un subdirectorio que tiene el mismo nombre que uno de los ficheros de datos especificados.

- Hay un error de sintaxis en `innodb_data_home_dir` o `innodb_data_file_path`.

Si algo va mal durante el intento de `InnoDB` de inicializar el espacio de tablas o los ficheros de registro, se deberán borrar todos los ficheros creados por `InnoDB`. Esto comprende todos los ficheros `ibdata` y todos los `ib_logfile`. En caso de haber creado alguna tabla `InnoDB`, habrá que borrar del directorio de datos de MySQL los correspondientes ficheros `.frm` de estas tablas (y cualquier fichero `.ibd` si se están empleando múltiples espacios de tablas). Entonces puede intentarse nuevamente la creación de la base de datos `InnoDB`. Es mejor iniciar el servidor MySQL desde una línea de comandos de modo que pueda verse lo que ocurre.

## 15.6. Crear tablas `InnoDB`

Suponiendo que se ha iniciado el cliente MySQL con el comando `mysql test`, para crear una tabla `InnoDB` se debe especificar la opción `ENGINE = InnoDB` o `TYPE = InnoDB` en la sentencia SQL de creación de tabla:

    CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
    CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;

La sentencia SQL crea una tabla y un índice en la columna `a` en el espacio de tablas `InnoDB` que consiste en los ficheros de datos especificados en `my.cnf`. Adicionalmente, MySQL crea un fichero `customers.frm` en el directorio `test` debajo del directorio de bases de datos de MySQL. Internamente, `InnoDB` agrega a su propio diccionario de datos una entrada para la tabla `'test/customers'`. Esto significa que puede crearse una tabla con el mismo nombre `customers` en otra base de datos, y los nombres de las tablas no entrarán en conflicto dentro de `InnoDB`.

Se puede consultar la cantidad de espacio libre en el espacio de tablas `InnoDB` dirigiendo una sentencia `SHOW TABLE STATUS` para cualquier tabla `InnoDB`. La cantidad de espacio libre en el espacio de tablas aparece en la sección `Comment` en la salida de `SHOW TABLE STATUS`. Un ejemplo:

    SHOW TABLE STATUS FROM test LIKE 'customers'

Nótese que las estadísticas que `SHOW` muestra acerca de las tablas `InnoDB` son solamente aproximadas. Se utilizan en la optimización SQL. No obstante, los tamaños en bytes reservados para las tablas e índices son exactos.

### 15.6.1. Cómo utilizar transacciones en `InnoDB` con distintas APIs

En forma predeterminada, cada cliente se que conecta al servidor MySQL comienza con el modo de autocommit habilitado, lo cual automáticamente confirma (commit) cada sentencia SQL ejecutada. Para utilizar transacciones de múltiples sentencias se puede deshabilitar el modo autocommit con la sentencia SQL `SET AUTOCOMMIT = 0` y emplear `COMMIT` y `ROLLBACK` para confirmar o cancelar la transacción. Si se desea dejar activado autocommit, se pueden encerrar las transacciones entre las sentencias `START TRANSACTION` y `COMMIT` o `ROLLBACK`. El siguiente ejemplo muestra dos transacciones. La primera se confirma, la segunda se cancela.

    shell> mysql test
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5 to server version: 3.23.50-log
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
        -> ENGINE=InnoDB;
    Query OK, 0 rows affected (0.00 sec)
    mysql> BEGIN;
    Query OK, 0 rows affected (0.00 sec)
    mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
    Query OK, 1 row affected (0.00 sec)
    mysql> COMMIT;
    Query OK, 0 rows affected (0.00 sec)
    mysql> SET AUTOCOMMIT=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
    Query OK, 1 row affected (0.00 sec)
    mysql> ROLLBACK;
    Query OK, 0 rows affected (0.00 sec)
    mysql> SELECT * FROM CUSTOMER;
    +------+--------+
    | A    | B      |
    +------+--------+
    |   10 | Heikki |
    +------+--------+
    1 row in set (0.00 sec)
    mysql>

En APIs como PHP, Perl DBI/DBD, JDBC, ODBC, o la interface de llamadas C estándar de MySQL, se pueden enviar sentencias de control de transacciones como `COMMIT` al servidor MySQL en forma de cadenas, igual que otras sentencias SQL como `SELECT` o `INSERT`. Algunas APIs también ofrecen funciones o métodos especiales para confirmación y cancelación de transacciones.

### 15.6.2. Pasar tablas `MyISAM` a `InnoDB`

Importante: No se deben convertir las tablas del sistema en la base de datos `mysql` (por ejemplo, `user` o `host`) al tipo `InnoDB`. Las tablas del sistema siempre deben ser del tipo `MyISAM`.

Si se desea que todas las tablas que no sean de sistema se creen como tablas `InnoDB`, simplemente debe agregarse la línea `default-table-type=innodb` a la sección `[mysqld]` del fichero `my.cnf` o `my.ini`.

`InnoDB` no posee una optimización especial para la creación separada de índices en la misma forma que la tiene el motor de almacenamiento `MyISAM`. Por lo tanto, no hay beneficio en exportar e importar la tabla y crear los índices posteriormente. La manera más rápida de cambiar una tabla al motor `InnoDB` es hacer las inserciones directamente en una tabla `InnoDB`. Es decir, utilizar `ALTER TABLE ... ENGINE=INNODB`, o crear una tabla `InnoDB` vacía con idénticas definiciones e insertar las filas con `INSERT INTO ... SELECT * FROM ...`.

Si se tienen restricciones `UNIQUE` sobre claves secundarias, se puede acelerar la importación de una tabla desactivando temporalmente la verificación de unicidad durante la sesión de importación: `SET UNIQUE_CHECKS=0;`. Para tablas grandes, esto ahorra gran cantidad de operaciones de E/S en disco, debido a que `InnoDB` puede emplear su buffer de inserciones para grabar registros de índices secundarios en lote.

Para obtener un mejor control sobre el proceso de inserción, podría ser mejor llenar la tablas grandes por partes:

    INSERT INTO nuevatabla SELECT * FROM viejatabla
       WHERE clave > valor1 AND clave <= valor2;

Luego de que todos los registros se hayan insertado, se pueden renombrar las tablas.

Durante la conversión de tablas grandes, se puede reducir la cantidad de operaciones de E/S en disco incrementando el tamaño del pool de buffer de `InnoDB`. No debe usarse más del 80% de la memoria física. También pueden aumentarse los tamaños de los ficheros de registro (log) de `InnoDB`.

Hay que asegurarse de no llenar completamente el espacio de tablas: las tablas `InnoDB` necesitan mucho más espacio que las tablas `MyISAM`. Si una sentencia `ALTER TABLE` se queda sin espacio, realizará una cancelación (rollback), y esto puede tomar horas si lo hace sobre el disco. Para las inserciones, `InnoDB` emplea el buffer de inserción para combinar en lotes los registros secundarios de índices con los índices. Esto ahorra gran cantidad de operaciones de E/S en disco. Durante la cancelación no se emplea ese mecanismo, de modo que puede llevar más de 30 veces el tiempo insumido por la inserción.

Si se produjera una de estas cancelaciones fuera de control, sino se tienen datos valiosos en la base de datos, puede ser preferible matar el proceso de la base de datos en lugar de esperar que se completen millones de operaciones de E/S en disco. Para el procedimiento completo, consulte Sección 15.8.1, “Forzar una recuperación”.

### 15.6.3. Cómo funciona una columna `AUTO_INCREMENT` en `InnoDB`

Si se especifica que una columna de una tabla es `AUTO_INCREMENT`, la entrada para la tabla `InnoDB` en el diccionario de datos contiene un contador especial llamado "contador de auto incremento", que se utiliza para asignar nuevos valores a la columna. El contador de auto incremento se almacena sólo en la memoria principal, no en disco.

`InnoDB` utiliza el siguiente algoritmo para inicializar el contador de auto incremento para una tabla `T` que contiene una columna `AUTO_INCREMENT` llamada `ai_col`: Luego de iniciarse el servidor, cuando un usuario realiza por primera vez una inserción en una tabla `T`, `InnoDB` ejecuta el equivalente de esta sentencia:

    SELECT MAX(ai_col) FROM T FOR UPDATE;

El valor retornado por la sentencia se incrementa en uno y se asigna a la columna, y al contador de auto incremento de la tabla. Si la tabla está vacía, se asigna el valor `1`. Si el contador aún no se ha inicializado y se ejecuta una sentencia `SHOW TABLE STATUS` que muestre su salida para la tabla `T`, el contador se inicializa (pero no se incrementa) y se almacena para usos posteriores. Nótese que en esta inicialización se realiza una lectura normal con bloqueo exclusivo y el bloqueo permanece hasta el final de la transacción.

`InnoDB` sigue el mismo procedimiento para inicializar el contador de auto incremento de una tabla recientemente creada.

Nótese que si durante un `INSERT` el usuario especifica un valor `NULL` o `0` para una columna `AUTO_INCREMENT`, `InnoDB` trata a la columna como si no se hubiera especificado un valor y genera un nuevo valor para ella.

Luego de que el contador de auto incremento ha sido inicializado, si un usuario inserta una fila que explícitamente indica para la columna auto incremental un valor mayor que el valor actual del contador, éste se establece al valor actual de la columna. Si no se indica un valor, `InnoDB` incrementa el valor del contador en uno y lo asigna a la columna.

Al acceder al contador de auto incremento, `InnoDB` emplea un nivel de bloqueo de tabla especial `AUTO-INC`, que mantiene hasta el final de la sentencia SQL actual y no hasta el final de la transacción. Esta estrategia de bloqueo especial fue introducida para mejorar la concurrencia de inserciones en una tabla que contiene una columna `AUTO_INCREMENT`. Dos transacciones no pueden tener el bloqueo `AUTO-INC` simultáneamente en la misma tabla.

Nótese que pueden observarse valores faltantes en la secuencia de valores asignados a la columna `AUTO_INCREMENT` si se cancelan transacciones que ya han obtenido números desde el contador.

El comportamiento del mecanismo de auto incremento no se encuentra definido si un usuario asigna un valor negativo a la columna o si el valor se vuelve mayor que el entero más grande que puede almacenarse en el tipo de entero especificado.

A partir de MySQL 5.0.3, `InnoDB` soporta la opción `AUTO_INCREMENT = `n`` en sentencias `CREATE TABLE` y `ALTER TABLE`, para establecer inicialmente o modificar el valor actual del contador. El efecto de esta acción es cancelado al reiniciar el servidor, por las razones tratadas anteriormente en esta sección.

### 15.6.4. Restricciones (constraints) `FOREIGN KEY`

`InnoDB` también soporta restricciones de claves foráneas. La sintaxis para definir una restricción de clave foránea en `InnoDB` es así:

    [CONSTRAINT símbolo] FOREIGN KEY [id] (nombre_índice, ...)
        REFERENCES nombre_de_tabla (nombre_índice, ...)
        [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
        [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Las definiciones de claves foráneas están sujetas a las siguientes condiciones:

- Ambas tablas deben ser `InnoDB` y no deben ser tablas temporales.

- En la tabla que hace referencia, debe haber un índice donde las columnas de clave extranjera estén listadas en primer lugar, en el mismo orden.

- En la tabla referenciada, debe haber un índice donde las columnas referenciadas se listen en primer lugar, en el mismo orden. En MySQL/InnoDB 5.0, tal índice se creará automáticamente en la tabla referenciada si no existe aún.

- No están soportados los índices prefijados en columnas de claves foráneas. Una consecuencia de esto es que las columnas `BLOB` y `TEXT` no pueden incluirse en una clave foránea, porque los índices sobre dichas columnas siempre deben incluir una longitud prefijada.

- Si se proporciona un `CONSTRAINT`símbolo``, éste debe ser único en la base de datos. Si no se suministra, `InnoDB` crea el nombre automáticamente.

`InnoDB` rechaza cualquier operación `INSERT` o `UPDATE` que intente crear un valor de clave foránea en una tabla hija sin un valor de clave candidata coincidente en la tabla padre. La acción que `InnoDB` lleva a cabo para cualquier operación `UPDATE` o `DELETE` que intente actualizar o borrar un valor de clave candidata en la tabla padre que tenga filas coincidentes en la tabla hija depende de la accion referencial especificada utilizando las subcláusulas `ON UPDATE` y `ON DETETE` en la cláusula `FOREIGN KEY`. Cuando el usuario intenta borrar o actualizar una fila de una tabla padre, `InnoDB` soporta cinco acciones respecto a la acción a tomar:

- `CASCADE`: Borra o actualiza el registro en la tabla padre y automáticamente borra o actualiza los registros coincidentes en la tabla hija. Tanto `ON DELETE CASCADE` como `ON UPDATE CASCADE` están disponibles en MySQL 5.0. Entre dos tablas, no se deberían definir varias cláusulas `ON UPDATE CASCADE` que actúen en la misma columna en la tabla padre o hija.

- `SET NULL`: Borra o actualiza el registro en la tabla padre y establece en `NULL` la o las columnas de clave foránea en la tabla hija. Esto solamente es válido si las columnas de clave foránea no han sido definidas como `NOT NULL`. MySQL 5.0 soporta tanto `ON DELETE SET NULL` como `ON UPDATE SET NULL`.

- `NO ACTION`: En el estándar `ANSI SQL-92`, `NO ACTION` significa ninguna acción en el sentido de que unintento de borrar o actualizar un valor de clave primaria no sera permitido si en la tabla referenciada hay una valor de clave foránea relacionado. (Gruber, Mastering SQL, 2000:181). En MySQL 5.0, `InnoDB` rechaza la operación de eliminación o actualización en la tabla padre.

- `RESTRICT`: Rechaza la operación de eliminación o actualización en la tabla padre. `NO ACTION` y `RESTRICT` son similares en tanto omiten la cláusula `ON DELETE` u `ON UPDATE`. (Algunos sistemas de bases de datos tienen verificaciones diferidas o retrasadas, una de las cuales es `NO ACTION`. En MySQL, las restricciones de claves foráneas se verifican inmediatamente, por eso, `NO ACTION` y `RESTRICT` son equivalentes.)

- `SET DEFAULT`: Esta acción es reconocida por el procesador de sentencias (parser), pero `InnoDB` rechaza definiciones de tablas que contengan `ON DELETE SET DEFAULT` u `ON UPDATE SET DEFAULT`.

`InnoDB` soporta las mismas opciones cuando se actualiza la clave candidata en la tabla padre. Con `CASCADE`, las columnas de clave foránea en la tabla hija son establecidas a los nuevos valores de la clave candidata en la tabla padre. Del mismo modo, las actualizaciones se producen en cascada si las columnas actualizadas en la tabla hija hacen referencia a claves foráneas en otra tabla.

Nótese que `InnoDB` soporta referencias de clave foránea dentro de una tabla, y, en estos casos, la tabla hija realmente significa registros dependientes dentro de la tabla.

`InnoDB` necesita que haya índices sobre las claves foráneas y claves referenciadas, así las verificaciones de claves foráneas pueden ser veloces y no necesitan recorrer la tabla. En MySQL 5.0, el índice en la clave foránea se crea automáticamente. Esto contrasta con versiones más antiguas (anteriores a 4.1.8), donde los índices debían crearse explícitamente o fallaba la creación de restricciones de claves foráneas.

Las columnas involucradas en la clave foránea y en la clave referenciada deben tener similares tipos de datos internos dentro de `InnoDB`, de modo que puedan compararse sin una conversión de tipo. La longitud y la condición de con o sin signo de los tipos enteros deben ser iguales. La longitud de los tipos cadena no necesita ser la misma. Si se especifica una acción `SET NULL`, hay que asegurarse de que las columnas en la tabla hija no se han declarado como `NOT NULL`.

Si MySQL informa que ocurrió un error número 1005 en una sentencia `CREATE TABLE` y la cadena con el mensaje de error se refiere al errno (número de error) 150, significa que la creación de una tabla falló debido a una restricción de clave foránea formulada incorrectamente. Del mismo modo, si un `ALTER TABLE` falla y hace referencia al número de error 150, significa que se ha formulado incorrectamente una restricción de clave extranjera cuando se alteró la tabla. En MySQL 5.0, puede emplearse `SHOW INNODB STATUS` para mostrar una explicación detallada del último error de clave foránea sufrido por `InnoDB` en el servidor.

Nota: `InnoDB` no verifica las restricciones de claves foráneas en las claves foráneas o valores de claves referenciados que contengan una columna `NULL`.

Una desviación del estándar SQL: Si en la tabla padre hay varios registros que contengan el mismo valor de clave referenciada, entonces `InnoDB` se comporta en las verificaciones de claves extranjeras como si los demás registros con el mismo valor de clave no existiesen. Por ejemplo, si se ha definido una restricción del tipo `RESTRICT`, y hay un registro hijo con varias filas padre, `InnoDB` no permite la eliminación de ninguna de éstas.

`InnoDB` lleva a cabo las operaciones en cascada a través de un algoritmo de tipo depth-first, basado en los registros de los indices correspondientes a las restricciones de claves foráneas.

Una desviación del estándar SQL: Si `ON UPDATE CASCADE` u `ON UPDATE SET NULL` vuelven a modificar la misma tabla que se está actualizando en cascada, el comportamiento es como en `RESTRICT`. Esto significa que en una tabla no se pueden ejecutar operaciones `ON UPDATE CASCADE` u `ON UPDATE SET NULL` que hagan referencia a ella misma. De ese modo se previenen bucles infinitos resultantes de la actualización en cascada. En cambio, una operación `ON DELETE SET NULL`, puede hacer referencia a la misma tabla donde se encuentra, al igual que `ON DELETE CASCADE`. En MySQL 5.0, las operaciones en cascada no pueden anidarse en más de 15 niveles de profundidad.

Una desviación del estándar SQL: Como sucede en MySQL en general, en una sentencia SQL que realice inserciones, eliminaciones o actualizaciones en varias filas, InnoDB verifica las restricciones `UNIQUE` y `FOREIGN KEY` fila a fila. De acuerdo con el estándar SQL, el comportamiento predeterminado debería ser que las restricciones se verifiquen luego de que la sentencia SQL ha sido procesada por completo.

Note: Actualmente, los disparadores no son activados por acciones de claves foráneas en cascada.

Un ejemplo sencillo que relaciona tablas `padre` e `hijo` a través de una clave foránea de una sola columna:

    CREATE TABLE parent(
      id INT NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=INNODB;

    CREATE TABLE child(
      id INT,
      parent_id INT,
      INDEX par_ind (parent_id),
      FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
    ) ENGINE=INNODB;

Aquí, un ejemplo más complejo, en el cual una tabla `product_order` tiene claves foráneas hacia otras dos tablas. Una de las claves foráneas hace referencia a un índice de dos columnas en la tabla `product`. La otra hace referencia a un índice de una sola columna en la tabla `customer`:

    CREATE TABLE product (
      category INT NOT NULL,
      id INT NOT NULL,
      price DECIMAL,
      PRIMARY KEY(category, id)
    ) ENGINE=INNODB;

    CREATE TABLE customer (
      id INT NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=INNODB;

    CREATE TABLE product_order (
      no INT NOT NULL AUTO_INCREMENT,
      product_category INT NOT NULL,
      product_id INT NOT NULL,
      customer_id INT NOT NULL,
      PRIMARY KEY(no),
      INDEX (product_category, product_id),
      FOREIGN KEY (product_category, product_id)
        REFERENCES product(category, id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
      INDEX (customer_id),
      FOREIGN KEY (customer_id)
        REFERENCES customer(id)
    ) ENGINE=INNODB;

`InnoDB` permite agregar una nueva restricción de clave foránea a una tabla empleando `ALTER TABLE`:

    ALTER TABLE yourtablename
        ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
        REFERENCES tbl_name (index_col_name, ...)
        [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
        [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Debe recordarse crear en primer lugar los índices necesarios.. También se puede agregar una clave foránea autoreferente a una tabla empleando `ALTER TABLE`.

`InnoDB` también soporta el uso de `ALTER TABLE` para borrar claves foráneas:

    ALTER TABLE nombre_tabla DROP FOREIGN KEY símbolo_clave_foránea;

Si la cláusula `FOREIGN KEY` incluye un nombre de `CONSTRAINT` cuando se crea la clave foránea, se puede utilizar ese nombre para eliminarla. En otro caso, el valor `símbolo_clave_foránea` es generado internamente por `InnoDB` cuando se crea la clave foránea. Para saber cuál es este símbolo cuando se desee eliminar una clave foránea, se emplea la sentencia `SHOW CREATE TABLE`. Un ejemplo:

    mysql> SHOW CREATE TABLE ibtest11c\G
    *************************** 1. row ***************************
           Table: ibtest11c
    Create Table: CREATE TABLE `ibtest11c` (
      `A` int(11) NOT NULL auto_increment,
      `D` int(11) NOT NULL default '0',
      `B` varchar(200) NOT NULL default '',
      `C` varchar(175) default NULL,
      PRIMARY KEY  (`A`,`D`,`B`),
      KEY `B` (`B`,`C`),
      KEY `C` (`C`),
      CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
    REFERENCES `ibtest11a` (`A`, `D`)
    ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
    REFERENCES `ibtest11a` (`B`, `C`)
    ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=INNODB CHARSET=latin1
    1 row in set (0.01 sec)

    mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;

El procesador de sentencias (parser) de `InnoDB` permite emplear acentos graves (ASCII 96) para encerrar los nombres de tablas y columnas en una clásusula `FOREIGN KEY ... REFERENCES ...`. El parser de `InnoDB` también toma en cuenta lo establecido en la variable de sistema `lower_case_table_names`.

`InnoDB` devuelve las definiciones de claves foráneas de una tabla como parte de la salida de la sentencia `SHOW CREATE TABLE`:

    SHOW CREATE TABLE tbl_name;

A partir de esta versión, mysqldump también produce definiciones correctas de las tablas en el fichero generado, sin omitir las claves foráneas.

Se pueden mostrar las restricciones de claves foráneas de una tabla de este modo:

    SHOW TABLE STATUS FROM nombre_bd LIKE 'nombre_tabla';

Las restricciones de claves foráneas se listan en la columna `Comment` de la salida producida.

Al llevar a cabo verificaciones de claves foráneas, `InnoDB` establece bloqueos compartidos a nivel de fila en los registros de tablas hijas o padres en los cuales deba fijarse. `InnoDB` verifica las restricciones de claves foráneas inmediatamente, la verificación no es demorada hasta la confirmación de la transacción.

Para facilitar la recarga de ficheros de volcado de tablas que tengan relaciones de claves foráneas, mysqldump incluye automáticamente una sentencia en la salida del comando para establecer `FOREIGN_KEY_CHECKS` a 0. Esto evita problemas con tablas que tengan que ser creadas en un orden particular cuando se recarga el fichero de volcado. También es posible establecer el valor de esta variable manualmente:

    mysql> SET FOREIGN_KEY_CHECKS = 0;
    mysql> SOURCE dump_file_name;
    mysql> SET FOREIGN_KEY_CHECKS = 1;

Esto permite importar las tablas en cualquier orden si el fichero de volcado contiene tablas que no están ordenadas según lo requieran sus claves foráneas. También acelera la operación de importación. Establecer `FOREIGN_KEY_CHECKS` a 0 también puede ser útil para ignorar restricciones de claves foráneas durante operaciones `LOAD DATA` y `ALTER TABLE`.

`InnoDB` no permite eliminar una tabla que está referenciada por una restricción `FOREIGN KEY`, a menos que se ejecute `SET FOREIGN_KEY_CHECKS=0`. Cuando se elimina una tabla, las restricciones que fueron definidas en su sentencia de creación también son eliminadas.

Si se recrea una tabla que fue eliminada, debe ser definida de acuerdo a las restricciones de claves foráneas que están haciendo referencia a ella. Debe tener los tipos y nombres correctos de columnas, y debe tener índices sobre las tablas referenciadas, como se estableció anteriormente. Si estas condiciones no se cumplen, MySQL devuelve un error número 1005 y menciona el error número 150 en el mensaje de error.

### 15.6.5. `InnoDB` y replicación MySQL

La replicación en MySQL funciona para tablas `InnoDB` del mismo modo que lo hace para tablas `MyISAM`. Es posible usar replicación en una forma en que el tipo de tabla en el servidor esclavo no es igual a la tabla original en el servidor maestro. Por ejemplo, se pueden replicar modificaciones de una tabla `InnoDB` en el servidor maestro sobre una tabla `MyISAM` en el servidor esclavo.

Para configurar un nuevo esclavo para un servidor maestro, se debe realizar una copia del espacio de tablas `InnoDB` y de los ficheros de registro, así como de los ficheros `.frm` de las tablas `InnoDB`, y mover las copias al servidor esclavo. El procedimiento adecuado para esto se encuenta en Sección 15.9, “Trasladar una base de datos `InnoDB` a otra máquina”.

Si se puede detener el servidor maestro o un esclavo existente, se puede tomar un backup en frío del espacio de tablas `InnoDB` y de los ficheros de registro y utilizarlos para configurar un servidor esclavo. Para crear un nuevo esclavo sin detener ningún servidor, se puede utilizar la herramienta comercial `InnoDB Hot Backup` tool.

Una limitación menor en la replicación `InnoDB` es que `LOAD TABLE FROM MASTER` no funciona con tablas de tipo `InnoDB`. Hay dos posibles soluciones:

- Hacer un volcado de la tabla en el maestro e importarlo dentro del esclavo.

- Utilizar `ALTER TABLE `nombre_tabla` TYPE=MyISAM` en el maestro antes de realizar la replicación con `LOAD TABLE `nombre_tabla` FROM MASTER`, y luego emplear `ALTER TABLE` para convertir la tabla en el maestro nuevamente a `InnoDB`.

Las transacciones que fallen en el maestro no afectan en absoluto la replicación. La replicación en MySQL se basa en el registro (log) binario donde MySQL registra las sentencias SQL que modifican datos. Un esclavo lee el registro binario del maestro y ejecuta las mismas sentencias SQL. Sin embargo, las sentencias emitidas dentro de una transacción no se graban en el registro binario hasta que se confirma la transacción, en ese momento todas las sentencias son grabadas de una vez. Si una sentencia falla, por ejemplo por infringir una clave foránea, o si se cancela una transacción, ninguna sentencia se guarda en el registro binario y la transacción no se ejecuta en absoluto en el servidor esclavo.

### 15.6.6. Usar un espacio de tablas para cada tabla

En MySQL 5.0, se puede almacenar cada tabla `InnoDB` y sus índices en su propio fichero. Esta característica se llama “multiple tablespaces” (espacios de tablas múltiples) porque, en efecto, cada tabla tiene su propio espacio de tablas.

El uso de múltiples espacios de tablas puede ser beneficioso para usuarios que desean mover tablas específicas a discos físicos separados o quienes deseen restaurar respaldos de tablas sin interrumpir el uso de las demás tablas InnoDB.

Se pueden habilitar múltiples espacios de tablas agregando esta línea a la sección `[mysqld]` de `my.cnf`:

    [mysqld]
    innodb_file_per_table

Luego de reiniciar el servidor, `InnoDB` almacenará cada nueva tabla creada en su propio fichero ``nombre_tabla`.ibd` en el directorio de la base de datos a la que pertenece la tabla. Esto es similar a lo que hace el motor de almacenamiento `MyISAM`, pero `MyISAM` divide la tabla en un fichero de datos ``tbl_name`.MYD` y el fichero de índice ``tbl_name`.MYI`. Para `InnoDB`, los datos y los índices se almacenan juntos en el fichero `.ibd`. El fichero ``tbl_name`.frm` se sigue creando como es usual.

Si se quita la línea `innodb_file_per_table` de `my.cnf` y se reinicia el servidor, `InnoDB` creará nuevamente las tablas dentro de los ficheros del espacio de tablas compartido.

`innodb_file_per_table` afecta solamente la creación de tablas. Si se inicia el servidor con esta opción, las tablas nuevas se crearán empleando ficheros `.ibd`, pero aún se puede acceder a las tablas existentes en el espacio de tablas compartido. Si se remueve la opción, las nuevas tablas se crearán en el espacio compartido, pero aún se podrá acceder a las tablas creadas en espacios de tablas múltiples.

`InnoDB` siempre necesita del espacio de tablas compartido. Los ficheros `.ibd` no son suficientes para que funcione `InnoDB`. El espacio de tablas compartido consiste de los ya conocidos ficheros `ibdata`, donde `InnoDB` coloca su diccionario de datos interno y los registros para deshacer cambios (undo logs).

No se puede mover libremente ficheros `.ibd` entre directorios de bases de datos en la misma forma en que se hace con ficheros de tablas `MyISAM`. Esto se debe a que la definición de las tablas se almacena en el espacio de tablas compartido de `InnoDB`, y también porque `InnoDB` debe preservar la consistencia de los identificadores de transacciones y los números de secuencia de registros (log).

Dentro de una determinada instalación MySQL, se puede mover un fichero `.ibd` y las tablas asociadas de una base de datos a otra con la conocida sentencia `RENAME TABLE`:

    RENAME TABLE nombre_bd_anterior.nombre_tabla TO nombre_bd_nuevo.nombre_tabla;

Si se tiene un respaldo “limpio” de un fichero `.ibd`, se lo puede restaurar dentro de la instalación MySQL de donde proviene del siguiente modo:

1. Utilizando esta sentencia `ALTER TABLE`:

       ALTER TABLE nombre_tabla DISCARD TABLESPACE;

   Precaución: Esto eliminará el actual fichero `.ibd`.

2. Colocando el fichero `.ibd` nuevamente en el directorio de la base de datos adecuada.

3. Utilizando esta sentencia `ALTER TABLE`:

       ALTER TABLE nombre_tabla IMPORT TABLESPACE;

En este contexto, un respaldo “limpio” de un fichero `.ibd` significa:

- ç El fichero `.ibd` no contiene modificaciones realizadas por transacciones sin confirmar.

- No quedan entradas sin combinar en el buffer de inserciones en el fichero `.ibd`.

- Se han quitado todos los registros de índice marcados para eliminación en el fichero `.ibd`.

- mysqld ha descargado todas las páginas modificadas del fichero `.ibd` desde el buffer pool hacia el fichero.

Se puede realizar un respaldo limpio del fichero `.ibd` con el siguiente método:

1. Detener toda actividad del servidor mysqld y confirmar todas las transacciones.

2. Esperar hasta que `SHOW INNODB STATUS` indique que no hay transacciones activas en la base de datos, y el estado del subproceso (trhead) principal de `InnoDB` sea `Waiting for server activity` (Esperando por actividad del servidor). Entonces, se puede hacer una copia del fichero `.ibd`.

Otro método para hacer una copia limpia de un fichero `.ibd` es utilizar la herramienta comercial InnoDB Hot Backup:

1. Utilizar InnoDB Hot Backup para respaldar la instalación `InnoDB`.

2. Iniciar un segundo servidor mysqld sobre el respaldo y permitirle limpiar los ficheros `.ibd` del mismo.

Figura en la lista de pendientes (TODO) para permitir mover ficheros `.ibd` limpios a otra instalación MySQL. Esto necesita que se inicialicen los IDs (identificadores) de transacciones y los números de secuencia de registros (log) en el fichero `.ibd`.

## 15.7. Añadir y suprimir registros y ficheros de datos `InnoDB`

Esta sección describe lo que se puede hacer cuando el espacio de tablas `InnoDB` se queda sin espacio o cuando se desea cambiar el tamaño de los ficheros de registro (log).

La manera más sencilla de incrementar el tamaño del espacio de tablas `InnoDB` es configurarlo desde un principio para que sea autoextensible, especificando el atributo `autoextend` para el último fichero de datos en la definición del espacio de tablas. Entonces, `InnoDB` incrementa el tamaño de ese fichero automáticamente en intervalos de 8MB cuando se queda sin espacio. El tamaño del intervalo a incrementar puede configurarse estableciendo el valor de `innodb_autoextend_increment`, el cual está expresado en megabytes, y cuyo valor predeterminado es 8.

Alternativamente, se puede incrementar el tamaño del espacio de tablas agregando otro fichero de datos. Para hacer esto, se debe detener el servidor MySQL, editar el fichero `my.cnf` para agregar un nuevo fichero de datos al final de `innodb_data_file_path`, e iniciar nuevamente el servidor.

Si el último fichero de datos especificado tiene la palabra clave `autoextend`, el procedimiento para editar a `my.cnf` debe tener en cuenta el tamaño que ha alcanzado este último fichero. Hay que obtener el tamaño del fichero de datos, redondearlo hacia abajo a la cantidad de megabytes (1024 * 1024 bytes) más cercana, y especificar este número explícitamente en `innodb_data_file_path`. Entonces se podrá agregar otro fichero de datos. Hay que recordar que solamente el último fichero de datos en `innodb_data_file_path` puede especificarse como autoextensible.

Como ejemplo, se asumirá que el espacio de tablas tiene sólo un fichero de datos autoextensible `ibdata1`:

    innodb_data_home_dir =
    innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Suponiendo que este fichero de datos, a lo largo del tiempo, ha crecido hasta 988MB, debajo se ve la línea de configuración luego de agregar otro fichero de datos autoextensible.

    innodb_data_home_dir =
    innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

Cuando se agrega un nuevo fichero al espacio de tablas, hay que asegurarse de que no exista. `InnoDB` crea e inicializa el fichero al reiniciar el servidor.

Actualmente no es posible quitar un fichero de datos del espacio de tablas. Para reducir el tamaño del espacio de tablas, emplear este procedimiento:

1. Utilizar mysqldump para hacer un volcado de todas las tablas `InnoDB`.

2. Detener el servidor.

3. Eliminar todos los ficheros existentes del espacio de tablas.

4. Configurar un nuevo espacio de tablas.

5. Reiniciar el servidor.

6. Importar el fichero de volcado de tablas.

Si se desea modificar la cantidad o tamaño de los ficheros de registro (log) de `InnoDB`, se debe detener el servidor MySQL y asegurarse de que se cerró sin errores. Luego, copiar los ficheros de registro antiguos en un lugar seguro, sólo para el caso de que algo haya fallado en el cierre del servidor y se necesite recuperar el espacio de tablas. Eliminar los antiguos ficheros de registro del directorio de ficheros de registro, editar `my.cnf` para modificar la configuración de los ficheros de registro, e iniciar nuevamente el servidor MySQL. mysqld verá al iniciar que no hay ficheros de registro e informará que está creando nuevos.

## 15.8. Hacer una copia de seguridad y recuperar una base de datos `InnoDB`

La clave de una administración de bases de datos segura es realizar copias de respaldo regularmente.

InnoDB Hot Backup es una herramienta de respaldo en línea que puede utilizarse para respaldar la base de datos `InnoDB` mientras ésta se está ejecutando. InnoDB Hot Backup no necesita que se detenga la base de datos y no establece ningún bloqueo ni dificulta el normal procesamiento de la base de datos. InnoDB Hot Backup es una herramienta adicional comercial (no grautita) cuyo cargo anual de licencia es de €390 por cada ordenador en el que se ejecute el servidor MySQL. Consulte la página de Internet de InnoDB Hot Backup para obtener información detallada y ver capturas de pantallas.

Si se está en condiciones de detener el servidor MySQL, puede realizarse una copia de respaldo binaria, que consiste en todos los ficheros usados por `InnoDB` para administrar sus tablas. Se utiliza el siguiente procedimiento:

1. Detener el servidor MySQL y asegurarse de que lo hace sin errores.

2. Copiar todos los ficheros de datos (ficheros `ibdata` e `.ibd`) en un lugar seguro.

3. Copiar todos los ficheros `ib_logfile` en un lugar seguro.

4. Copiar el o los ficheros de configuración `my.cnf` en un lugar seguro.

5. Copiar todos los ficheros `.frm` de las tablas `InnoDB` en un lugar seguro.

La replicación funciona con tablas `InnoDB`, de forma que puede emplearse para mantener una copia de la base de datos en sitios de bases de datos que necesiten alta disponibilidad.

Adicionalmente a la realización de copias de respaldo binarias como se ha descripto, también se deberían realizar regularmente volcados de las tablas con mysqldump. El motivo de esto es que un fichero binario podría corromperse sin que el usuario lo note. El volcado de las tablas se almacena en ficheros de texto que son legibles por los seres humanos, facilitando la localización de corrupción en las tablas. Además, puesto que el formato es más simple, las probabilidades de una corrupción seria de datos son menores. mysqldump también tiene una opción `--single-transaction` que puede usarse para capturar una imagen consistente de la base de datos sin bloquear otros clientes.

Para estar en condiciones de recuperar una base de datos `InnoDB` a partir del respaldo binario descripto anteriormente, se debe ejecutar el servidor MySQL con el registro binario (binary logging) activo. Entonces se puede aplicar el log binario al respaldo de la base de datos para lograr la recuperación a una fecha y hora determinadas:

    mysqlbinlog nombre_de_host-bin.123 | mysql

Para recuperarse de una caida del servidor, sólo se requiere reiniciarlo. `InnoDB` verifica automáticamente los registros (logs) y ejecuta una recuperación de la base de datos del tipo roll-forward, es decir, hasta el momento anterior a la falla. `InnoDB` revierte automáticamente las transacciones no grabadas que existían al momento de la caída. Durante la recuperación, mysqld muestra información parecida a esta:

    InnoDB: Database was not shut down normally.
    InnoDB: Starting recovery from log files...
    InnoDB: Starting log scan based on checkpoint at
    InnoDB: log sequence number 0 13674004
    InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
    InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
    InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
    InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
    ...
    InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
    InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
    InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
    InnoDB: 1 uncommitted transaction(s) which must be rolled back
    InnoDB: Starting rollback of uncommitted transactions
    InnoDB: Rolling back trx no 16745
    InnoDB: Rolling back of trx no 16745 completed
    InnoDB: Rollback of uncommitted transactions completed
    InnoDB: Starting an apply batch of log records to the database...
    InnoDB: Apply batch completed
    InnoDB: Started
    mysqld: ready for connections

Si la base de datos se corrompe o falla el disco, habrá que efectuar la recuperación desde una copia de respaldo. En el caso de corrupción, primero habría que encontrar una copa de respaldo realizada antes de la corrupción. Luego de restaurar la copia de respaldo base, debe realizarse la recuperación a partir de los ficheros de registro binario.

En algunos casos de corrupción de base de datos es suficiente con volcar, eliminar, y volver a crear una o unas pocas tablas corruptas. Se puede emplear la sentencia SQL `CHECK TABLE` para verificar si una tabla está corrupta, aunque `CHECK TABLE`, naturalmente, no puede detectar cada posible clase de corrupción. Se puede emplear `innodb_tablespace_monitor` para verificar la integridad de la gestión de espacio de ficheros dentro de los ficheros de espacio de tablas.

En algunos casos, una aparente corrupción de página de base de datos se debe en realidad a que el sistema operativo está corrompiendo su propio cache de ficheros, y los datos en el disco podrían estar en buenas condiciones. Lo mejor es, antes que nada, intentar el reinicio del ordenador. Ello puede eliminar errores que dan la sensación de tener corrupción en la página de base de datos.

### 15.8.1. Forzar una recuperación

Si hay corrupción de página de base de datos, es posible que se desee hacer un volcado de tablas desde la base de datos con `SELECT INTO OUTFILE`; usualmente, la mayoría de los datos obtenidos de esta manera están intactos. Aún así, la corrupción puede hacer que `SELECT * FROM `tbl_name`` o las operaciones en segundo plano de `InnoDB` caigan o, incluso activar la recuperación roll-forward de `InnoDB`. Sin embargo, se puede forzar el motor de almacenamiento de `InnoDB` para que se inicie mientras se evitan las operaciones en segundo plano, de forma que se pueda realizar un volcado de las tablas. Por ejemplo, puede agregarse la siguiente linea a la sección `[mysqld]` del fichero de opciones antes de reiniciar el servidor:

    [mysqld]
    innodb_force_recovery = 4

Debajo se listan los valores mayores a cero permitidos para `innodb_force_recovery`. Un número mayor incluye todas las precauciones de los números por debajo. Si se logra hacer un volcado de tablas con un valor de a lo sumo 4, se puede estar relativamente seguro de que solamente se han perdido algunos datos en páginas individuales corruptas. Un valor de 6 es más dramático, porque las páginas de base de datos han quedado obsoletas, lo que a su vez puede introducir más corrupción en B-trees y otras estructuras de bases de datos.

- `1` (`SRV_FORCE_IGNORE_CORRUPT`)

  Le permite al servidor ejecutarse aún si detecta una página corrupta; intenta hacer que `SELECT * FROM `tbl_name`` salte sobre los registros de índice y páginas corruptos, lo cual es de ayuda en el volcado de las tablas.

- `2` (`SRV_FORCE_NO_BACKGROUND`)

  Impide que se ejecute el subproceso (thread) principal. Si durante la operación de descarga (purge) ocurriese una caida, esto la previene.

- `3` (`SRV_FORCE_NO_TRX_UNDO`)

  No revierte transacciones luego de la recuperación.

- `4` (`SRV_FORCE_NO_IBUF_MERGE`)

  También evita las operaciones de combinación del buffer de inserciones. Si ello pudiese causar una caida, es mejor no hacerlo; no calcula estadísticas de tablas.

- `5` (`SRV_FORCE_NO_UNDO_LOG_SCAN`)

  No tiene en cuenta el contenido de los registros para revertir cambios (undo logs) al iniciar la base de datos: `InnoDB` considera como confirmadas inclusive a las transacciones incompletas.

- `6` (`SRV_FORCE_NO_LOG_REDO`)

  Omite la creación del registro (log) que permite la recuperación de tipo roll-forward.

En otro caso la base de datos no debe emplearse con ninguna de estas opciones habilitadas. Como una medida de seguridad, `InnoDB` impide que los usuarios lleven a cabo operaciones `INSERT`, `UPDATE`, o `DELETE` cuando `innodb_force_recovery` está configurado en un valor mayor a 0.

Se pueden ejecutar sentencias `DROP` y `CREATE` para eliminar y crear tablas incluso cuando se está empleando la recuperación forzada. Si se sabe que una determinada tabla está provocando caídas al hacer una cancelación (rollback), se la puede eliminar. También puede utilizarse para detener una cancelación fuera de control causada por una importación o un `ALTER TABLE` en masa. Se puede interrumpir el proceso mysqld y establecer `innodb_force_recovery` a un valor de `3` para poner a funcionar la base de datos sin que ejecute cancelación (rollback), luego emplear `DROP` para eliminar la tabla que está causando la cancelación fuera de control.

### 15.8.2. Marcadores

`InnoDB` implementa un mecanismo de puntos de verificación llamado fuzzy checkpoint (punto de verificación difuso). `InnoDB` descarga las páginas modificadas de la base de datos desde el pool de buffers en lotes pequeños. No es necesario descargar al disco el pool de buffers en una sola acción, lo cual en la práctica podría detener temporalmente el procesamiento de sentencias SQL del usuario.

Durante la recuperación de caídas, `InnoDB` busca un marcador de checkpoint escrito en los ficheros de registro (log). Ya sabe que todas las modificaciones a la base de datos anteriores al marcador están presentes en la imagen en disco de la misma. Entonces `InnoDB` recorre los ficheros de registro (log) desde el checkpoint hacia adelante, aplicando sobre la base de datos las modificaciones registradas.

`InnoDB` escribe en los ficheros de registro en una forma rotativa. Todas las modificaciones confirmadas que hagan a las páginas de la base de datos en el pool de buffers diferentes de las grabadas en disco deben estar disponibles en los ficheros de registro en caso de que `InnoDB` tenga que hacer una recuperación. Esto significa que cuando `InnoDB` comienza a reutilizar un fichero de registro, tiene que asegurarse de que las imágenes en disco de las páginas de base de datos contienen las modificaciones asentadas en el fichero de registro que se va a reutilizar. En otras palabras, `InnoDB` debe crear un punto de verificación (checkpoint) y esto a menudo implica la descarga a disco de las páginas de base de datos modificadas.

La descripción anterior explica porqué hacer los ficheros de registro muy grandes puede ahorrar operaciones de E/S en disco destinadas a la creación de puntos de verificación. A menudo se hace hincapié en establecer el tamaño total de los ficheros de registro en lo mismo que el pool de buffers o aún más grande. La desventaja que tienen los ficheros de registro grandes es que la recuperación ante una caída puede tomar más tiempo debido a que hay más información que debe aplicarse a la base de datos.

## 15.9. Trasladar una base de datos `InnoDB` a otra máquina

En Windows, `InnoDB` siempre almacena internamente en minúsculas los nombres de bases de datos y tablas. Para mover bases de datos en un formato binario de Unix a Windows o de Windows a Unix, se deberían tener en minúsculas todos los nombres de tablas y bases de datos. Una forma apropiada de cumplir con esto es agregar la siguiente línea a la sección `[mysqld]` de los ficheros `my.cnf` o `my.ini` antes de crear cualquier base de datos o tablas:

    [mysqld]
    lower_case_table_names=1

Al igual que los ficheros de datos `MyISAM`, los ficheros de datos y de registro (log) de `InnoDB` son compatibles a nivel binario en todas las plataformas que tengan el mismo formato de números de coma flotante. Se puede mover una base de datos `InnoDB` simplemente copiando todos los ficheros relevantes que se listan en Sección 15.8, “Hacer una copia de seguridad y recuperar una base de datos `InnoDB`”. Si los formatos de número de coma flotante difieren pero no se han empleado tipos de datos `FLOAT` o `DOUBLE` en las tablas, el procedimiento es el mismo: copiar los ficheros necesarios. Si los formatos difieren y las tablas contienen datos de coma flotante, se deberá emplear mysqldump para volcar las tablas en un ordenador e importar los ficheros en volcado en otro.

Una forma de incrementar el rendimiento es desactivar el modo autocommit (ejecución automática) al importar datos, asumiendo que el espacio de tablas tiene suficiente sitio para el extenso segmento de cancelación (rollback) que generará la importación de transacciones. La confirmación (commit) se hará luego de importar una tabla entera o un segmento de una tabla.

## 15.10. Bloqueo y modelo de transacciones de `InnoDB`

En el modelo de transacciones de `InnoDB`, la meta es combinar las mejores propiedades de una base de datos multiversión con el tradicional bloqueo de dos fases. `InnoDB` bloquea a nivel de fila y ejecuta consultas por defecto como lecturas consistentes (consistent reads) no bloqueadas, al estilo de Oracle. La tabla de bloqueo en `InnoDB` se almacena en forma tan eficiente que no se necesitan bloqueos escalables: generalmente varios usuarios están habilitados a bloquear cada fila de la base de datos, o cualquier subconjunto de filas, sin que `InnoDB` incurra en falta de memoria.

### 15.10.1. Modos de bloqueo `InnoDB`

`InnoDB` implementa un bloqueo a nivel de fila estándar, donde hay dos tipos de bloqueos:

- Compartido (Shared) (`S`) le permite a una transacción leer una fila.

- Exclusivo (Exclusive) (`X`) le permite a una transacción actualizar o eliminar una fila.

Si una transacción `A` sostiene un bloqueo exclusivo (`X`) sobre una tupla `t`, entonces una solicitud de otra transacción `B` para establecer un bloqueo de cualquier tipo sobre `t` no puede ser atendida inmediatamente. En lugar de eso, la transacción `B` debe esperar a que la transacción `A` libere el bloqueo en la tupla `t`.

Si la transacción `A` sostiene un bloqueo compartido (`S`) sobre una tupla `t`, entonces

- Una solicitud de otra transacción `B` para un bloqueo `X` sobre `t` no puede ser atendido inmediatamente.

- Una solicitud de otra transacción `B` para un bloqueo `S` sobre `t` puede ser atendido inmediatamente. En consecuencia, tanto `A` como `B` sostendrán un bloqueo `S` sobre `t`.

Adicionalmente, `InnoDB` soporta bloqueo de granularidad múltiple (multiple granularity locking), el cual permite que existan simultáneamente bloqueos en registros y bloqueos en tablas enteras. Para hacer práctico el nivel de bloqueo de granularidad múltiple, se emplean tipos adicionales de bloqueo, llamados bloqueos de intención (intention locks). Los bloqueos de intención son bloqueos de tabla en `InnoDB`. La idea detrás de los mismos es que una transacción indique qué tipo de bloqueo (compartido o exclusivo) requerirá más tarde sobre una fila de esa tabla. En `InnoDB` se utilizan dos tipos de bloqueos de intención (asumiendo que la transacción `T` ha solicitado un bloqueo del tipo indicado en la tabla `R`):

- Intención compartida (Intention shared) (`IS`): La transacción `T` trata de establecer bloqueos `S` en tuplas individuales de la tabla `T`.

- Intención exclusiva (Intention exclusive) (`IX`): La transacción `T` trata de establecer bloqueos `X` en las tuplas.

Luego, el protocolo de bloqueo de intención es el siguiente:

- Antes de que de una determinada transacción logre un bloqueo `S` en una determinada fila, primero debe conseguir establecer un bloqueo `IS` o superior en la tabla que contiene a la fila.

- Antes de que de una determinada transacción logre un bloqueo `X` en una determinada fila, primero debe conseguir establecer un bloqueo `IX` en la tabla que contiene a la fila.

Estas reglas pueden resumirse convenientemente por medio de una matriz de compatibilidad entre tipos de bloqueo:

| X | IX | S | IS | -
X | N | N | N | N | S
IX | N | S | N | S | S
S | N | S | S | S | S
IS | N | S | S | S | S
- | S | S | S | S | S

Por lo tanto, los bloqueos de intención solamente bloquean solicitudes sobre tablas completas (Ej: `LOCK TABLES ... WRITE`). El propósito principal de `IX` y `IS` es mostrar que alguien está bloqueando una fila, o va a bloquear una fila en la tabla.

### 15.10.2. `InnoDB` y `AUTOCOMMIT`

En `InnoDB`, toda la actividad del usuario se produce dentro de una transacción. Si el modo de ejecución automática (autocommit) está activado, cada sentencia SQL conforma una transacción individual por sí misma. MySQL siempre comienza una nueva conexión con la ejecución automática habilitada.

Si el modo de ejecución automática se deshabilitó con `SET AUTOCOMMIT = 0`, entonces puede considerarse que un usuario siempre tiene una transacción abierta. Una sentencia SQL `COMMIT` o `ROLLBACK` termina la transacción vigente y comienza una nueva. Ambas sentencias liberan todos los bloqueos `InnoDB` que se establecieron durante la transacción vigente. Un `COMMIT` significa que los cambios hechos en la transacción actual se convierten en permanentes y se vuelven visibles para los otros usuarios. Por otra parte, una sentencia `ROLLBACK`, cancela todas las modificaciones producidas en la transacción actual.

Si la conexión tiene la ejecución automática habilitada, el usuario puede igualmente llevar a cabo una transacción con varias sentencias si la comienza explícitamente con `START TRANSACTION` o `BEGIN` y la termina con `COMMIT` o `ROLLBACK`.

### 15.10.3. `InnoDB` y `TRANSACTION ISOLATION LEVEL`

En los términos de los niveles de aislamiento de transacciones SQL:1992, el nivel predeterminado en `InnoDB` es `REPEATABLE READ`. En MySQL 5.0, `InnoDB` ofrece los cuatro niveles de aislamiento de transacciones descriptos por el estándar SQL. Se puede establecer el nivel predeterminado de aislamiento por todas las conexiones mediante el uso de la opción `--transaction-isolation` en la línea de comandos o en ficheros de opciones. Por ejemplo, se puede establecer la opción en la sección `[mysqld]` de `my.cnf` de este modo:

    [mysqld]
    transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                             | REPEATABLE-READ | SERIALIZABLE}

Un usuario puede cambiar el nivel de aislamiento de una sesión individual o de todas las nuevas conexiones con la sentencia `SET TRANSACTION`. Su sintaxis es la siguiente:

    SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                           {READ UNCOMMITTED | READ COMMITTED
                            | REPEATABLE READ | SERIALIZABLE}

Nótese que se usan guiones en los nombres de niveles de la opción `--transaction-isolation`, pero no en la sentencia `SET TRANSACTION`.

El comportamiento predeterminado es establecer el nivel de aislamiento a partir de la próxima transacción que se inicie. Si se emplea la palabra clave `GLOBAL`, la sentencia establece el nivel predeterminado de la transacción globalmente para todas las nuevas conexiones creadas a partir de ese punto (pero no en las existentes). Se necesita el privilegio `SUPER` para hacer esto. Utilizando la palabra clave `SESSION` se establece el nivel de transacción para todas las futuras transacciones ejecutadas en la actual conexión.

Cualquier cliente es libre de cambiar el nivel de aislamiento de la sesión (incluso en medio de una transacción), o el nivel de aislamiento para la próxima transacción.

Los niveles de aislamiento de transacciones globales y de sesión pueden consultarse con estas sentencias:

    SELECT @@global.tx_isolation;
    SELECT @@tx_isolation;

En el bloqueo a nivel de fila, `InnoDB` emplea bloqueo de clave siguiente (next-key). Esto significa que, además de registros de índice, `InnoDB` también puede bloquear el “vacío” que precede a un registro de índice para bloquear inserciones de otros usuarios inmediatamente antes del registro de índice. Un bloqueo de clave siguiente hace referencia a bloquear un registro de índice y la posición vacía antes de él. Bloquear una posición vacía es establecer un bloqueo que actúa solamente sobre el vacío anterior a un registro de índice.

A continuación una descripción detallada de cada nivel de aislamiento en `InnoDB`:

- `READ UNCOMMITTED`

  Las sentencias `SELECT` son ejecutadas sin realizar bloqueos, pero podría usarse una versión anterior de un registro. Por lo tanto, las lecturas no son consistentes al usar este nivel de aislamiento. Esto también se denomina “lectura sucia” (dirty read). En otro caso, este nivel de aislamiento funciona igual que `READ COMMITTED`.

- `READ COMMITTED`

  Similar en parte al mismo nivel de aislamiento de Oracle. Todas las sentencias `SELECT ... FOR UPDATE` y `SELECT ... LOCK IN SHARE MODE` bloquean solamente los registros de índice, no los espacios vacíos que los preceden, por lo tanto se permite la libre inserción de nuevos registros junto a los bloqueados. Las sentencias `UPDATE` and `DELETE` que empleen un índice único con una condición de búsqueda única bloquean solamente el registro de índice hallado, no el espacio que lo precede. En las sentencias `UPDATE` y `DELETE` que actúan sobre rangos de registros, `InnoDB` debe bloquear los espacios vacíos y bloquear las inserciones de otros usuarios en los espacios vacíos que hay dentro del rango. Esto es necesario debido a que las “filas fantasma” deben ser bloqueadas para que funcionen la replicación y recuperación en MySQL.

  Las lecturas consistentes se comportan como en Oracle: Cada lectura consistente, incluso dentro de la misma transacción, establece y lee su propia captura tomada de la base de datos. Consulte Sección 15.10.4, “Lecturas consistentes que no bloquean”.

- `REPEATABLE READ`

  Este es el nivel de aislamiento predeterminado de `InnoDB`. Las sentencias `SELECT ... FOR UPDATE`, `SELECT ... LOCK IN SHARE MODE`, `UPDATE`, y `DELETE` que utilicen un índice único con una condición de búsqueda única, bloquean solamente el registro de índice hallado, no el espacio vacío que lo precede. Con otras condiciones de búsqueda, estas operaciones emplean bloqueo de clave siguiente (next-key), bloqueando el rango de índice cubierto por la operación incluyendo los espacios vacíos, y bloqueando las nuevas inserciones por parte de otros usuarios.

  En lecturas consistentes (consistent reads), hay una importante diferencia con respecto al nivel de aislamiento anterior: En este nivel, todas las lecturas consistentes dentro de la misma transacción leen de la captura de la base de datos tomada por la primer lectura. Esta práctica significa que si se emiten varias sentencias `SELECT` dentro de la misma transacción, éstas serán consistentes unas con otras. Consulte Sección 15.10.4, “Lecturas consistentes que no bloquean”.

- `SERIALIZABLE`

  Este nivel es similar a `REPEATABLE READ`, pero todas las sentencias `SELECT` son convertidas implícitamente a `SELECT ... LOCK IN SHARE MODE`.

### 15.10.4. Lecturas consistentes que no bloquean

Lectura consistente significa que `InnoDB` utiliza su característica de multiversión para presentar a una consulta una captura de la base de datos en un momento determinado. La consulta ve los cambios realizados exactamente por aquellas transacciones confirmadas antes de ese momento, y no los cambios hechos con posterioridad o por transacciones no confirmadas. La excepción a esto es que la consulta ve los cambios efectuados por la transacción a donde pertenece.

Si se está ejecutando con el nivel de aislamiento predeterminado `REPEATABLE READ`, entonces todas las lecturas consistentes dentro de la misma transacción leen la captura creada por la primer lectura en esa transacción. Se puede refrescar esta captura confirmando la transacción actual y emitiendo nuevas consultas.

Lectura consistente es el modo por defecto en el cual `InnoDB` procesa las sentencias `SELECT` en los niveles de aislamiento `READ COMMITTED` y `REPEATABLE READ`. Una lectura consistente no establece ningún bloqueo en las tablas a las que accede, y, por lo tanto, otros usuarios están libres para modificar las tablas sobre las que se está haciendo la lectura consistente.

### 15.10.5. Bloquear lecturas `SELECT ... FOR UPDATE` y `SELECT ... LOCK IN SHARE MODE`

En ciertas circunstancias, no es conveniente una lectura consistente. Por ejemplo, se podría desear agregar una fila en la tabla `hijo`, y estar seguro de que dicha fila tiene una fila padre en la tabla `padre`. El siguiente ejemplo muestra cómo implementar integridad referencial en el código de la aplicación.

Suponiendo que se utiliza una lectura consistente para leer la tabla `padre` y efectivamente puede verse el registro padre para la fila hijo que se agregará, ¿puede agregarse en forma segura la fila hijo dentro de la tabla `hijo`? No, porque puede haber ocurrido que entretanto otro usuario haya borrado el registro padre de la tabla `padre`, sin que se tenga conocimiento de ello.

La solución es llevar a cabo el `SELECT` en un modo con bloqueo, utilizando `LOCK IN SHARE MODE`:

    SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Realizar una lectura en modo compartido (share mode) significa que se leen los últimos datos disponibles, y se establece un bloqueo en modo compartido en los registros que se leen. Un bloqueo en modo compartido evita que otros actualicen o eliminen la fila que se ha leido. Además, si los datos más actualizados pertenecen a una transacción todavía no confirmada de otra conexión, se espera hasta que la transacción se confirme. Luego de ver que la mencionada consulta devuelve el registro padre `'Jones'`, se puede agregar con seguridad el registro hijo en la tabla `hijo` y confirmar la transacción.

Otro ejemplo: se tiene un campo contador, entero, en una tabla llamada `child_codes` que se emplea para asignar un identificador único a cada registro hijo agregado a la tabla `hijo`. Obviamente, utilizar una lectura consistente o una lectura en modo compartido para leer el valor actual del contador no es una buena idea, puesto que dos usuarios de la base de datos pueden ver el mismo valor del contador, y agregar registros hijos con el mismo identificador, lo cual generaría un error de clave duplicada.

En este caso, `LOCK IN SHARE MODE` no es una buena solución porque si dos usuarios leen el contador al mismo tiempo, al menos uno terminará en un deadlock cuando intente actualizar el contador.

En este caso, hay dos buenas formas de implementar la lectura e incremento del contador: (1), actualizar el contador en un incremento de 1 y sólo después leerlo, o (2) leer primero el contador estableciendo un bloqueo `FOR UPDATE`, e incrementándolo luego. La última puede ser implementada como sigue:

    SELECT counter_field FROM child_codes FOR UPDATE;
    UPDATE child_codes SET counter_field = counter_field + 1;

Una sentencia `SELECT ... FOR UPDATE` lee el dato más actualizado disponible, estableciendo bloqueos exclusivos sobre cada fila leída. Es decir, el mismo bloqueo que haría `UPDATE`.

Nótese que el anterior es un sencillo ejemplo de cómo funciona `SELECT ... FOR UPDATE`. En MySQL, la tarea específica para generar un identificador único en realidad puede realizarse utilizando un sólo acceso a la tabla:

    UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
    SELECT LAST_INSERT_ID();

La sentencia `SELECT` simplemente recupera la información del identificador (relativa a la conexión actual). No accede ninguna tabla.

### 15.10.6. Bloqueo de la próxima clave (Next-Key Locking): evitar el problema fantasma

En el bloqueo a nivel de fila, `InnoDB` utiliza un algoritmo llamado bloqueo de próxima clave. `InnoDB` lleva a cabo el bloqueo a nivel de fila de tal manera que cuando busca o recorre el índice de una tabla, establece bloqueos compartidos o exclusivos en los registros de índice que encuentra. Por lo tanto, los bloqueos a nivel de fila son en realidad bloqueos sobre registros del índice.

El conjunto de bloqueos de `InnoDB` sobre los registros del índice también afecta al “gap” (posición vacía) que precede al registro de índice. Si un usuario tiene un bloqueo compartido o exclusivo sobre un registro `R` en un índice, otro usuario no puede insertar un nuevo registro inmediatamente antes de `R` en el orden del índice. Este bloqueo de posiciones vacías se hace para evitar el llamado “problema fantasma”. Suponiendo que se desean leer y bloquear todos los hijos de la tabla `hijos` que tengan un identificador mayor a 100, con el posterior intento de actualizar algunas columnas en las filas seleccionadas:

    SELECT * FROM child WHERE id > 100 FOR UPDATE;

Suponiendo que hay un índice sobre la columna `id`, la consulta recorre ese índice comenzando por el primer registro donde `id` es mayor a 100. Si el bloqueo establecido sobre el índice no bloqueara también las inserciones hechas en las posiciones vacías, durante el proceso se podría insertar una nueva fila en la tabla. Si se ejecuta la misma sentencia `SELECT` dentro de la misma transacción, se podría ver una nueva fila en el conjunto de resultados devuelto por la consulta. Esto es contrario al principio de aislamiento de las transacciones: una transacción deberia ejecutarse de forma que los datos que ha leido no cambien en el transcurso de la misma. Si se considera un conjunto de columnas como datos, el nuevo registro hijo “fantasma” violaría el principio de aislamiento.

Cuando `InnoDB` recorre un índice, también puede bloquear la posición vacía después del último registro del índice. Es precisamente lo que ocurre en el ejemplo anterior: Los bloqueos impuestos por `InnoDB` evitan cualquier inserción en la tabla donde `id` fuera mayor de 100.

Se puede emplear bloqueo de próxima clave para efectuar el control de la unicidad en una aplicación: Si se leen los datos en modo compartido y no se ve un duplicado de la fila que se va a insertar, entonces puede hacerse con la seguridad de que el bloqueo de próxima clave establecido sobre el registro que continúa a la fila insertada evita que cualquiera inserte un duplicado de ésta. Por lo tanto, el bloqueo de próxima clave permite “bloquear” la no existencia de algo en la tabla.

### 15.10.7. Un ejemplo de lectura consistente en `InnoDB`

Suponiendo que se está ejecutando en el nivel de aislamiento predeterminado `REPEATABLE READ`, cuando se realiza una lectura consistente -esto es, una sentencia `SELECT` ordinaria-, `InnoDB` le otorga a la transacción un punto en el tiempo (timepoint) del momento en que se realizó la consulta. Si otra transacción elimina una fila y confirma la acción en un momento posterior a dicho punto, no se verá la fila como borrada. Las inserciones y actualizaciones se tratan del mismo modo.

Se puede obtener un timepoint más reciente confirmando la transacción actual y emitiendo un nuevo `SELECT`.

Esto se llama control de concurrencia multiversión.

                   Usuario A                 Usuario B

               SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;
    tiempo
    |          SELECT * FROM t;
    |          empty set
    |                                 INSERT INTO t VALUES (1, 2);
    |
    v          SELECT * FROM t;
               empty set
                                      COMMIT;

               SELECT * FROM t;
               empty set

               COMMIT;

               SELECT * FROM t;
               ---------------------
               |    1    |    2    |
               ---------------------
               1 row in set

En este ejemplo, el usuario A podrá ver la fila insertada por B solamente cuando B haya confirmado la inserción y A haya confirmado también, de modo que su timepoint avance e incluya la inserción confirmada por B.

Si se desea ver el “más reciente” estado de la base de datos, se debería emplear ya sea el nivel de aislamiento `READ COMMITTED` o bien una lectura con bloqueo:

    SELECT * FROM t LOCK IN SHARE MODE;

### 15.10.8. Establecimiento de bloqueos con diferentes sentencias SQL en `InnoDB`

Una lectura con bloqueo, un `UPDATE`, o un `DELETE` generalmente establecen bloqueos sobre cada registro de índice que es examinado durante el procesamiento de la consulta SQL. No son afectadas las filas excluidas por una condición `WHERE` en la consulta. `InnoDB` no recuerda exactamente la condición `WHERE`, solamente los rangos de índices que fueron examinados. Los bloqueos sobre los registros son normalmente bloqueos de próxima clave, que también impiden las inserciones en las posiciones vacías (“gap”) inmediatamente anteriores a los registros.

Si los bloqueos a establecer son exclusivos, entonces `InnoDB` recupera también los registros de índices agrupados (clustered) y los bloquea.

Si no hay índices apropiados para la consulta y MySQL debe examinar la tabla entera para procesarla, se bloqueará cada fila en la tabla, lo que impide cualquier inserción de otros usuarios. Es importante crear índices adecuados de modo que las consultas no examinen muchas filas innecesariamente.

- `SELECT ... FROM` es una lectura consistente, que lee una captura de la base de datos y no establece bloqueos a menos que el nivel de aislamiento de la transacción sea `SERIALIZABLE`. Para el nivel `SERIALIZABLE`, se establecen bloqueos compartidos de próxima clave en los registros de índice encontrados.

- `SELECT ... FROM ... LOCK IN SHARE MODE` establece bloqueos compartidos de próxima clave en todos los registros de índice hallados por la lectura.

- `SELECT ... FROM ... FOR UPDATE` establece bloqueos exclusivos de próxima clave en todos los registros de índice hallados por la lectura.

- `INSERT INTO ... VALUES (...)` establece un bloqueo exclusivo sobre la fila insertada. Nótese que no se trata de un bloqueo de próxima clave, y no evita que otros usuarios inserten registros en la posición vacía precedente. Si ocurriese un error por duplicación de claves, se establecerá un bloqueo compartido sobre el registro de índice duplicado.

- Mientras se inicializa una columna previamente declarada `AUTO_INCREMENT`, `InnoDB` establece un bloqueo exclusivo al final del índice asociado con dicha columna. Al accederse al contador de autoincremento, `InnoDB` emplea una modo de bloqueo de tabla específico llamado `AUTO-INC`, que dura solamente hasta el final de la actual consulta SQL, en lugar de existir hasta el final de la transacción. Consulte Sección 15.10.2, “`InnoDB` y `AUTOCOMMIT`”.

  En MySQL 5.0, `InnoDB` trae el valor de una columna previamente declarada `AUTO_INCREMENT` sin establecer ningún bloqueo.

- `INSERT INTO T SELECT ... FROM S WHERE ...` establece un bloqueo exclusivo (pero no de próxima clave) en cada fila insertada dentro de `T`. La búsqueda en `S` se hace como una lectura consistente, pero se establecen bloqueos compartidos de próxima clave en `S` si está activado el registro binario (binary logging) de MySQL. `InnoDB` tiene que establecer bloqueos en este último caso: en una recuperación de tipo roll-forward desde una copia de respaldo, cada semtencia SQL debe ser ejecutada en exactamente la misma manera en que se hizo originalmente.

- `CREATE TABLE ... SELECT ...` lleva a cabo el `SELECT` como una lectura consistente o con bloqueos compartidos, como en el punto anterior.

- `REPLACE` se ejecuta del mismo modo que una inserción si no hay colisiones con claves únicas. En otro caso, se coloca un bloqueo exclusivo de próxima clave en la fila que será actualizada.

- `UPDATE ... WHERE ...` establece un bloqueo exclusivo de próxima clave sobre cada registro encontrado por la búsqueda.

- `DELETE FROM ... WHERE ...` establece un bloqueo exclusivo de próxima clave sobre cada registro encontrado por la búsqueda.

- Si se define una restricción `FOREIGN KEY` sobre una tabla, cualquier inserción, actualización o eliminación que necesite la verificación de las condiciones impuestas por la restricción establecerá bloqueos compartidos a nivel de registro sobre los registros examinados durante la verificación. `InnoDB` también establece estos bloqueos en el caso de que la verificación falle.

- `LOCK TABLES` establece bloqueos de tabla, pero es la capa de MySQL de mayor nivel por debajo de la capa de `InnoDB` la que establece estos bloqueos. `InnoDB` tiene conocimiento de los bloqueos de tabla si se establecen `innodb_table_locks=1` y `AUTOCOMMIT=0`, y la capa de MySQL por debajo de `InnoDB` sabe acerca de los bloqueos a nivel de fila. En otro caso, la detección automática de deadlocks de InnoDB no puede detectar los deadlocks donde estén involucradas estas tablas. Además, puesto que la capa superior de MySQL no sabe acerca de bloqueos a nivel de fila, es posible obtener un bloqueo de tabla sobre una tabla donde otro usuario ha colocado bloqueos a nivel de fila. Sin embargo, esto no pone en peligro la integridad de la transacción, como se dice en Sección 15.10.10, “Detección de interbloqueos (deadlocks) y cancelación de transacciones (rollbacks)”. Consulte también Sección 15.16, “Restricciones de las tablas `InnoDB`”.

### 15.10.9. ¿Cuándo ejecuta o deshace implicitamente MySQL una transacción?

MySQL comienza cada conexión de cliente con el modo de ejecución automática (autocommit) habilitado por defecto. Cuando la ejecución automática está habilitada, MySQL realiza la confirmación luego de cada sentencia SQL, si dicha sentencia no devuelve un error.

Si se tiene desactivado el modo de ejecución automática y se cierra una conexión sin hacer una confirmación explícita de una transacción, MySQL cancelará dicha transacción.

Si una sentencia SQL devuelve un error, la confirmación o cancelación dependen del error. Consulte Sección 15.15, “Tratamiento de errores de InnoDB”.

Las siguientes sentencias SQL (y sus sinónimos) provocan en MySQL una confirmación implícita de la transacción en curso:

- `ALTER TABLE`, `BEGIN`, `CREATE INDEX`, `DROP DATABASE`, `DROP INDEX`, `DROP TABLE`, `LOAD MASTER DATA`, `LOCK TABLES`, `RENAME TABLE`, `SET AUTOCOMMIT=1`, `START TRANSACTION`, `TRUNCATE`, `UNLOCK TABLES`.

- Antes de MySQL 5.0.8, `CREATE TABLE` provocaba la confirmación si se empleaba el registro binario (binary logging). A partir de MySQL 5.0.8, las sentencias `CREATE TABLE`, `TRUNCATE TABLE`, `DROP DATABASE`, y `CREATE DATABASE` provocan una confirmación implícita.

- La sentencia `CREATE TABLE` en `InnoDB` se procesa como una transacción individual. Esto significa que un `ROLLBACK` emitido por el usuario no cancelará las sentencias `CREATE TABLE` hechas durante una transacción.

### 15.10.10. Detección de interbloqueos (deadlocks) y cancelación de transacciones (rollbacks)

`InnoDB` detecta automáticamente un deadlock de transacciones y cancela una o más transacciones para evitarlo. `InnoDB` intenta escoger para cancelar transacciones pequeñas, el tamaño de la transacción es determinado por el número de filas insertadas, actualizadas, o eliminadas.

`InnoDB` se mantiene al tanto de los bloqueos de tablas si `innodb_table_locks=1` (1 es el valor predeterminado), y la capa MySQL por debajo sabe acerca de bloqueos a nivel de fila. En otro caso, `InnoDB` no puede detectar deadlocks cuando están involucrados un bloqueo de tabla establecido por una sentencia `LOCK TABLES` o por otro motor de almacenamiento que no sea `InnoDB`. Estas situaciones se deben resolver estableciendo el valor de la variable de sistema `innodb_lock_wait_timeout`.

Cuando `InnoDB` lleva a cabo una cancelación completa de una transacción, todos los bloqueos de la transacción son liberados. Sin embargo, si solamente se cancela como resultado de un error una sentencia SQL individual, algunos de los bloqueos impuestos por la sentencia SQL podrían mantenerse. Esto se debe a que `InnoDB` guarda los bloqueos de fila en un formato en el que no puede luego saber qué sentencia SQL originó cada bloqueo.

### 15.10.11. Cómo tratar con interbloqueos

Los deadlocks son un problema clásico de las bases de datos transaccionales, pero no son peligrosos a menos que sean tan frecuentes que no se puedan ejecutar en absoluto ciertas transacciones. Normalmente, las aplicaciones deben ser escritas de modo que esten preparadas para emitir nuevamente una transacción si ésta es cancelada debido a un deadlock.

`InnoDB` emplea bloqueos automáticos a nivel de fila. Se pueden producir deadlocks aún en el caso de transacciones que solamente insertan o eliminan una fila individual. Esto se debe a que estas operaciones no son realmente “atómicas”; sino que establecen automáticamente bloqueos enlos (posiblemente varios) registros de índice de la fila insertada o eliminada.

Con las siguientes técnicas se puede estar a cubierto de los deadlocks y reducir la probabilidad de que ocurran:

- Emplear `SHOW INNODB STATUS` para determinar la causa del último deadlock. Puede ayudar a afinar la aplicación para evitar que ocurran otros.

- Siempre hay que estar preparado para emitir nuevamente una transacción que haya fallado por un deadlock. Los deadlocks no revisten peligro, simplemente hay que intentar de nuevo.

- Confirmar las transacciones frecuentemente. Las transacciones pequeñas son menos propensas a originar conflictos.

- Si se están usando lecturas que establecen bloqueos (`SELECT ... FOR UPDATE` o `... LOCK IN SHARE MODE`), hay que intentar utilizar un nivel de aislamiento bajo, como `READ COMMITTED`.

- Acceder a las tablas y filas en un orden fijo. Entonces, las transacciones forman secuencias bien definidas y no originan deadlocks.

- Agregar a las tablas índices adecuadamente elegidos. Entonces las consultas necesitarán examinar menos registros de índice y en consecuencia establecerán menos bloqueos. Utilizar `EXPLAIN SELECT` para determinar los índices que MySQL considera más apropiados para las consultas.

- Utilizar menos el bloqueo. Si es aceptable que `SELECT` devuelva datos de una captura de la base de datos que no sea la más actualizada, no hay que agregarle las cláusulas `FOR UPDATE` o `LOCK IN SHARE MODE`. En este caso es adecuado utilizar el nivel de aislamiento `READ COMMITTED`, porque cada lectura consistente dentro de la misma transacción leerá de su propia captura más reciente.

- Si nada de esto ayuda, habrá que serializar las transacciones con bloqueos a nivel de tabla. La forma correcta de emplear `LOCK TABLES` con tablas transaccionales, como InnoDB, es establecer `AUTOCOMMIT = 0` y no invocar a `UNLOCK TABLES` hasta que se haya confirmado explícitamente la transacción. Por ejemplo, si se necesitara escribir en una tabla `t1` y leer desde una tabla `t2`, se puede hacer esto:

      SET AUTOCOMMIT=0;
      LOCK TABLES t1 WRITE, t2 READ, ...;
      [aquí se hace algo con las tablas t1 y t2];
      COMMIT;
      UNLOCK TABLES;

  Los bloqueos a nivel de tabla favorecen el funcionamiento de la cola de transacciones, y evitan los deadlocks.

- Otra manera de serializar transacciones es crear una tabla “semáforo” auxiliar que contenga sólo una fila. Hay que hacer que cada transacción actualice esa fila antes de acceder otras tablas. De ese modo, todas las transacciones se producirán en serie. Nótese que el algoritmo de detección instantánea de deadlocks de `InnoDB` también funciona en este caso, porque el bloqueo de serialización es un bloqueo a nivel de fila. Con los bloqueos a nivel de tabla de MySQL, debe emplearse el método de timeout para solucionar deadlocks.

- En aquellas aplicaciones que emplean el comando de MySQL `LOCK TABLES`, MySQL no establece bloqueos de tabla si `AUTOCOMMIT=1`.

## 15.11. Consejos de afinamiento del rendimiento de `InnoDB`

- Si la utilidad `top` de Unix o el Administrador de Tareas de Windows muestra que el porcentaje de uso de CPU durante la carga de trabajo es inferior al 70%, probablemente se está trabajando directamente sobre el disco. Podría suceder que se estén produciendo excesivas confirmaciones de transacciones, o que el pool de buffer sea muy pequeño. Puede ser de ayuda incrementar el tamaño del buffer, pero no debe alcanzar ni superar el 80% del total de la memoria física del ordenador.

- Incluir varias modificaciones en una sola transacción. `InnoDB` debe descargar su registro (log) al disco cada vez que se confirma una transacción, si dicha transacción realiza modificaciones en la base de datos. Dado que la velocidad de rotación de un disco es generalmente de 167 revoluciones por segundo, esto restringe el número de confirmaciones a la misma fracción de segundo si el disco no “engaña” al sistema operativo.

- Si es aceptable la pérdida de alguna de las últimas transacciones confirmadas, se puede establecer en `my.cnf` el parámetro `innodb_flush_log_at_trx_commit` a un valor de 0. `InnoDB` intenta descargar el registro (log) una vez por segundo en cualquier caso, aunque la descarga no está garantizada.

- Incrementar el tamaño de los ficheros de registro (log), incluso hasta equiparar el tamaño del pool de buffer. Cuando `InnoDB` ha colmado la capacidad de los ficheros de log, debe escribir los contenidos modificados desde el pool de buffer al disco en un punto de verificación. Los ficheros de log pequeños pueden causar escrituras en disco innecesarias. La desventaja de los ficheros de log grandes es que la recuperación demanda más tiempo.

- También el buffer del log debe ser suficientemente grande (en el orden de los 8MB).

- Emplear el tipo de columna `VARCHAR` en lugar de `CHAR` si se almacenarán cadenas de longitud variable o si la columna contendrá muchos valores `NULL`. Una columna `CHAR(`N`)` siempre utiliza `N` bytes para almacenar los datos, inclusive si la cadena es más corta o es `NULL`. Las tablas más pequeñas aprovechan mejor el espacio del pool de buffer y reducen las operaciones de E/S en disco.

  Cuando se utiliza `row_format=compact` (el formato de registro predeterminado para InnoDB en MySQL 5.0) y un conjunto de caracteres de longitud variable como `utf8` o `sjis`, `CHAR(`N`)` ocupará una cantidad variable de espacio, con un mínimo de `N` bytes.

- En algunas versiones de GNU/Linux y Unix, descargar ficheros a disco con la función de Unix `fsync()` (la cual es utilizada en forma predeterminada por `InnoDB`) y otros métodos similares, es sorprendentemente lento. Si no se está satisfecho con el rendimiento de las operaciones de escritura de la base de datos, se puede intentar establecer el valor de `innodb_flush_method` en `my.cnf` a `O_DSYNC`, si bien `O_DSYNC` parece ser más lento en otros sistemas.

- Durante el empleo del motor de almacenamiento InnoDB en arquitecturas Solaris 10 para x86_64 (AMD Opteron), es importante usar la opción `forcedirectio` al montar cualquier sistema de ficheros usado para almacenar los ficheros relacionados con InnoDB (el comportamiento predeterminado en Solaris 10/x86_64 es no utilizar esta opción al montar el sistema de ficheros). Si no se utiliza `forcedirectio` se producirá una seria degradación en la velocidad y rendimiento de InnoDB en esta plataforma.

- Al importar datos dentro de `InnoDB`, hay que asegurarse de que MySQL no tiene habilitado el modo de ejecución automática (autocommit) porque provocaría una descarga del log a disco en cada inserción. Para desactivar la ejecución automática durante la operación de importación, hay que encerrarla entre sentencias `SET AUTOCOMMIT` y `COMMIT`:

      SET AUTOCOMMIT=0;
      /* Sentencias de importación SQL ... */
      COMMIT;

  Si se utiliza la opción `--opt` con mysqldump, se obtienen ficheros de voclado que son rápidos de importar en una tabla `InnoDB`, incluso sin encerrarlos en las sentencias `SET AUTOCOMMIT` y `COMMIT`.

- Tener cuidado con las cancelaciones de inserciones masivas: `InnoDB` emplea el buffer de inserciones para reducir la cantidad de operaciones de E/S en disco durante las inserciones, pero ese mecanismo no tiene efecto en la cancelación. Una cancelación efectuada directamente sobre el disco puede tomar 30 veces el tiempo que insumen las correspondientes inserciones. Matar el proceso del servidor de bases de datos no es de ayuda, porque la cancelación recomienza al volver a iniciar el servidor. La única forma de librarse de una cancelación fuera de control es incrementar el tamaño del pool de buffer para que la cancelación se haga sobre la CPU y se ejecute más rápidamente, o utilizar un procedimiento especial. Consulte Sección 15.8.1, “Forzar una recuperación”.

- También hay que tener cuidado con las operaciones de gran tamaño realizadas directamente sobre el disco. Hay que emplear `DROP TABLE` y `CREATE TABLE` para obtener una tabla vacía, no `DELETE FROM `tbl_name``.

- Emplear la sintaxis de múltiples filas de `INSERT` para reducir la carga extra de la comunicación entre el cliente y el servidor si se necesita insertar muchos registros:

      INSERT INTO yourtable VALUES (1,2), (5,5), ...;

  Esta sugerencia es válida para las inserciones en cualquier tipo de tabla, no solamente en `InnoDB`.

- Si se tienen restricciones `UNIQUE` en claves secundarias, se puede acelerar la importación desactivando temporalmente, durante la importación, la verificación de tales restricciones:

      SET UNIQUE_CHECKS=0;

  For big tables, this saves a lot of disk I/O because `InnoDB` can use its insert buffer to write secondary index records in a batch.

- If you have `FOREIGN KEY` constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:

      SET FOREIGN_KEY_CHECKS=0;

  For big tables, this can save a lot of disk I/O.

- If you often have recurring queries to tables that are not updated frequently, use the query cache:

      [mysqld]
      query_cache_type = ON
      query_cache_size = 10M

### 15.11.1. `SHOW INNODB STATUS` y los monitores `InnoDB`

`InnoDB` includes `InnoDB` Monitors that print information about the `InnoDB` internal state. You can use the SQL statement `SHOW INNODB STATUS` to fetch the output of the standard `InnoDB` Monitor to your SQL client. This information is useful in performance tuning. (If you are using the mysql interactive SQL client, the output is more readable if you replace the usual semicolon statement terminator with `\G`.) For a discussion of `InnoDB` lock modes see Sección 15.10.1, “Modos de bloqueo `InnoDB`”.

    mysql> SHOW INNODB STATUS\G

Another way to use `InnoDB` Monitors is to let them continuously write data to the standard output of the server mysqld. In this case, no output is sent to clients. When switched on, `InnoDB` Monitors print data about every 15 seconds. Server output usually is directed to the `.err` log in the MySQL data directory. This data is useful in performance tuning. On Windows, you must start the server from a command prompt in a console window with the `--console` option if you want to direct the output to the window rather than to the error log.

Monitor output includes information of the following types:

- Table and record locks held by each active transaction

- Lock waits of a transactions

- Semaphore waits of threads

- Pending file I/O requests

- Buffer pool statistics

- Purge and insert buffer merge activity of the main `InnoDB` thread

To cause the standard `InnoDB` Monitor to write to the standard output of mysqld, use the following SQL statement:

    CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

The monitor can be stopped by issuing the following statement:

    DROP TABLE innodb_monitor;

The `CREATE TABLE` syntax is just a way to pass a command to the `InnoDB` engine through MySQL's SQL parser: The only things that matter are the table name `innodb_monitor` and that it be an `InnoDB` table. The structure of the table is not relevant at all for the `InnoDB` Monitor. If you shut down the server when the monitor is running, and you want to start the monitor again, you must drop the table before you can issue a new `CREATE TABLE` statement to start the monitor. This syntax may change in a future release.

You can use `innodb_lock_monitor` in a similar fashion. This is the same as `innodb_monitor`, except that it also provides a great deal of lock information. A separate `innodb_tablespace_monitor` prints a list of created file segments existing in the tablespace and validates the tablespace allocation data structures. In addition, there is `innodb_table_monitor` with which you can print the contents of the `InnoDB` internal data dictionary.

A sample of `InnoDB` Monitor output:

    mysql> SHOW INNODB STATUS\G
    *************************** 1. row ***************************
    Status:
    =====================================
    030709 13:00:59 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 18 seconds
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
    --Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore:
    X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
    a writer (thread id 32782) has reserved it in mode wait exclusive
    number of readers 1, waiters flag 1
    Last time read locked in file btr0sea.c line 731
    Last time write locked in file btr0sea.c line 1347
    Mutex spin waits 0, rounds 0, OS waits 0
    RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485
    ------------------------
    LATEST FOREIGN KEY ERROR
    ------------------------
    030709 13:00:59 Transaction:
    TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inser
    ting
    15 lock struct(s), heap size 2496, undo log entries 9
    MySQL thread id 25, query id 4668733 localhost heikki update
    insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
    Foreign key constraint fails for table test/ibtest11a:
    ,
      CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`, `D`)
     ON DELETE CASCADE ON UPDATE CASCADE
    Trying to add in child table, in index PRIMARY tuple:
     0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2: len 4;
     hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4: len 7; hex
     00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
    But in parent table test/ibtest11b, in index PRIMARY,
    the closest match we can find is record:
    RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; a
    sc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ......
    ;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;;
    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    030709 12:59:58
    *** (1) TRANSACTION:
    TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inser
    ting
    LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
    MySQL thread id 21, query id 4553379 localhost heikki update
    INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t','e187358f'
    ,'g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
    trx id 0 290252780 lock mode S waiting
    Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
    a35818;; 1:
    *** (2) TRANSACTION:
    TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inser
    ting
    130 lock struct(s), heap size 11584, undo log entries 437
    MySQL thread id 23, query id 4554396 localhost heikki update
    REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,'
    h396', NULL, NULL, 7.31,7.31,7.31,200)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
    trx id 0 290251546 lock_mode X locks rec but not gap
    Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc a
    a35818;; 1:
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole
    trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa
    35720;; 1:
    *** WE ROLL BACK TRANSACTION (1)
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 0 290328385
    Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
    Total number of lock structs in row lock hash table 70
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
    MySQL thread id 32, query id 4668737 localhost heikki
    show innodb status
    ---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 in
    serting
    1 lock struct(s), heap size 320
    MySQL thread id 29, query id 4668736 localhost heikki update
    insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlhh
    gghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
    ---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 co
    mmitting
    1 lock struct(s), heap size 320, undo log entries 1
    MySQL thread id 19, query id 4668734 localhost heikki update
    insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjgjlh
    hgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
    ---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 st
    arting index read
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update
    update ibtest11a set B = 'kHdkkkk' where A = 89572
    ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIM
    ARY trx id 0 290328327 lock_mode X waiting
    Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc
     supremum.;;
    ------------------
    ---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 ro
    llback of SQL statement
    ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
    MySQL thread id 25, query id 4668733 localhost heikki update
    insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
    ---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782
    58 lock struct(s), heap size 5504, undo log entries 159
    MySQL thread id 23, query id 4668732 localhost heikki update
    REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t','e200498f
    ','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),
    ---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 in
    serting
    4 lock struct(s), heap size 1024, undo log entries 165
    MySQL thread id 21, query id 4668735 localhost heikki update
    INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL,
    'h321', NULL, NULL, 7.31,7.31,7.31,200)
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request (insert buffer thread)
    I/O thread 1 state: waiting for i/o request (log thread)
    I/O thread 2 state: waiting for i/o request (read thread)
    I/O thread 3 state: waiting for i/o request (write thread)
    Pending normal aio reads: 0, aio writes: 0,
     ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
    25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf for space 0: size 1, free list len 19, seg size 21,
    85004 inserts, 85004 merged recs, 26669 merges
    Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
    1877.67 hash searches/s, 5121.10 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 18 1212842764
    Log flushed up to   18 1212665295
    Last checkpoint at  18 1135877290
    0 pending log writes, 0 pending chkp writes
    4341 log i/o's done, 1.22 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 84966343; in additional pool allocated 1402624
    Buffer pool size   3200
    Free buffers       110
    Database pages     3074
    Modified db pages  2674
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages read 171380, created 51968, written 194688
    28.72 reads/s, 20.72 creates/s, 47.55 writes/s
    Buffer pool hit rate 999 / 1000
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    Main thread process no. 3004, id 7176, state: purging
    Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
    1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================
    1 row in set (0.05 sec)

Some notes on the output:

- If the `TRANSACTIONS` section reports lock waits, your application may have lock contention. The output can also help to trace the reasons for transaction deadlocks.

- The `SEMAPHORES` section reports threads waiting for a semaphore and statistics on how many times threads have needed a spin or a wait on a mutex or a rw-lock semaphore. A large number of threads waiting for semaphores may be a result of disk I/O, or contention problems inside `InnoDB`. Contention can be due to heavy parallelism of queries, or problems in operating system thread scheduling. Setting `innodb_thread_concurrency` smaller than the default value can help in such situations.

- The `BUFFER POOL AND MEMORY` section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.

- The `ROW OPERATIONS` section shows what the main thread is doing.

`InnoDB` sends diagnostic output to `stderr` or to files rather than to `stdout` or fixed-size memory buffers, in order to avoid potential buffer overflows. As a side effect, the output of `SHOW INNODB STATUS` is written to a status file every fifteen seconds. The name of the file is `innodb_status.`pid``, where `pid` is the server process ID. This file is created in the MySQL data directory. `InnoDB` removes the file for a normal shutdown. If abnormal shutdowns have occurred, instances of these status files may be present and must be removed manually. Before removing them, you might want to examine them to see if they contain useful information to the cause of abnormal shutdowns. IN MySQL 5.0, `innodb_status.`pid`` file is only created if the configuration option `innodb_status_file=1` is set.

## 15.12. Implementación de multiversión

Because `InnoDB` is a multi-versioned database, it must keep information about old versions of rows in the tablespace. This information is stored in a data structure called a rollback segment after an analogous data structure in Oracle.

Internally, `InnoDB` adds two fields to each row stored in the database. A 6-byte field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byte field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated.

`InnoDB` uses the information in the rollback segment to perform the undo operations needed in a transaction rollback. It also uses the information to build earlier versions of a row for a consistent read.

Undo logs in the rollback segment are divided into insert and update undo logs. Insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits. Update undo logs are used also in consistent reads, and they can be discarded only after there is no transaction present for which `InnoDB` has assigned a snapshot that in a consistent read could need the information in the update undo log to build an earlier version of a database row.

You must remember to commit your transactions regularly, including those transactions that only issue consistent reads. Otherwise, `InnoDB` cannot discard data from the update undo logs, and the rollback segment may grow too big, filling up your tablespace.

The physical size of an undo log record in the rollback segment is typically smaller than the corresponding inserted or updated row. You can use this information to calculate the space need for your rollback segment.

In the `InnoDB` multi-versioning scheme, a row is not physically removed from the database immediately when you delete it with an SQL statement. Only when `InnoDB` can discard the update undo log record written for the deletion can it also physically remove the corresponding row and its index records from the database. This removal operation is called a purge, and it is quite fast, usually taking the same order of time as the SQL statement that did the deletion.

In a scenario where the user inserts and deletes rows in smallish batches at about the same rate in the table, it is possible that the purge thread starts to lag behind, and the table grows bigger and bigger, making everything disk-bound and very slow. Even if the table carries just 10 MB of useful data, it may grow to occupy 10 GB with all the “dead” rows. In such a case, it would be good to throttle new row operations, and allocate more resources to the purge thread. The startup option and settable global variable `innodb_max_purge_lag` exists for exactly this purpose. See Sección 15.4, “Opciones de arranque de `InnoDB`” for more information.

## 15.13. Estructuras de tabla y de índice

MySQL stores its data dictionary information for tables in `.frm` files in database directories. This is true for all MySQL storage engines. But every `InnoDB` table also has its own entry in `InnoDB` internal data dictionaries inside the tablespace. When MySQL drops a table or a database, it has to delete both an `.frm` file or files, and the corresponding entries inside the `InnoDB` data dictionary. This is the reason why you cannot move `InnoDB` tables between databases simply by moving the `.frm` files.

Every `InnoDB` table has a special index called the clustered index where the data of the rows is stored. If you define a `PRIMARY KEY` on your table, the index of the primary key is the clustered index.

If you do not define a `PRIMARY KEY` for your table, MySQL picks the first `UNIQUE` index that has only `NOT NULL` columns as the primary key and `InnoDB` uses it as the clustered index. If there is no such index in the table, `InnoDB` internally generates a clustered index where the rows are ordered by the row ID that `InnoDB` assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus the rows ordered by the row ID are physically in the insertion order.

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O when compared to the traditional solution. (In many databases, the data is traditionally stored on a different page from the index record.)

In `InnoDB`, the records in non-clustered indexes (also called secondary indexes) contain the primary key value for the row. `InnoDB` uses this primary key value to search for the row from the clustered index. Note that if the primary key is long, the secondary indexes use more space.

`InnoDB` compares `CHAR` and `VARCHAR` strings of different lengths such that the remaining length in the shorter string is treated as if padded with spaces.

### 15.13.1. Estructura física de un índice

All indexes in `InnoDB` are B-trees where the index records are stored in the leaf pages of the tree. The default size of an index page is 16KB. When new records are inserted, `InnoDB` tries to leave 1/16 of the page free for future insertions and updates of the index records.

If index records are inserted in a sequential order (ascending or descending), the resulting index pages is about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full. If the fillfactor of an index page drops below 1/2, `InnoDB` tries to contract the index tree to free the page.

### 15.13.2. Búfer de inserts

It is a common situation in a database application that the primary key is a unique identifier and new rows are inserted in the ascending order of the primary key. Thus the insertions to the clustered index do not require random reads from a disk.

On the other hand, secondary indexes are usually non-unique, and insertions into secondary indexes happen in a relatively random order. This would cause a lot of random disk I/O operations without a special mechanism used in `InnoDB`.

If an index record should be inserted to a non-unique secondary index, `InnoDB` checks whether the secondary index page is in the buffer pool. If that is the case, `InnoDB` does the insertion directly to the index page. If the index page is not found in the buffer pool, `InnoDB` inserts the record to a special insert buffer structure. The insert buffer is kept so small that it fits entirely in the buffer pool, and insertions can be done very fast.

Periodically, the insert buffer is merged into the secondary index trees in the database. Often it is possible to merge several insertions to the same page of the index tree, saving disk I/O operations. It has been measured that the insert buffer can speed up insertions into a table up to 15 times.

The insert buffer merging may continue to happen *after* the inserting transaction has been committed. In fact, it may continue to happen after a server shutdown & restart (see Sección 15.8.1, “Forzar una recuperación”).

The insert buffer merging may take many hours, when many secondary indexes must be updated, and many rows have been inserted. During this time, disk I/O will be increased, which can cause significant slowdown on disk-bound queries. Another significant background I/O operation is the purge thread (see Sección 15.12, “Implementación de multiversión”).

### 15.13.3. Adaptive Hash Indexes

If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. `InnoDB` has an automatic mechanism that monitors index searches made to the indexes defined for a table. If `InnoDB` notices that queries could benefit from building a hash index, it does so automatically.

Note that the hash index is always built based on an existing B-tree index on the table. `InnoDB` can build a hash index on a prefix of any length of the key defined for the B-tree, depending on the pattern of searches that `InnoDB` observes for the B-tree index. A hash index can be partial: It is not required that the whole B-tree index is cached in the buffer pool. `InnoDB` builds hash indexes on demand for those pages of the index that are often accessed.

In a sense, `InnoDB` tailors itself through the adaptive hash index mechanism to ample main memory, coming closer to the architecture of main memory databases.

### 15.13.4. Estructura física de los registros

Records in `InnoDB` tables have the following characteristics:

- Each index record in `InnoDB` contains a header of six bytes. The header is used to link consecutive records together, and also in row-level locking.

- Records in the clustered index contain fields for all user-defined columns. In addition, there is a six-byte field for the transaction ID and a seven-byte field for the roll pointer.

- If no primary key was defined for a table, each clustered index record also contains a six-byte row ID field.

- Each secondary index record contains also all the fields defined for the clustered index key.

- A record contains also a pointer to each field of the record. If the total length of the fields in a record is less than 128 bytes, the pointer is one byte; otherwise, two bytes. The array of these pointers is called the record directory. The area where these pointers point is called the data part of the record.

- Internally, `InnoDB` stores fixed-length character columns such as `CHAR(10)` in a fixed-length format. `InnoDB` truncates trailing spaces from `VARCHAR` columns. Note that MySQL may internally convert `CHAR` columns to `VARCHAR`. See Sección 13.1.5.1, “Cambios tácitos en la especificación de columnas”.

- An SQL `NULL` value reserves 1 or 2 bytes in the record directory. Besides that, an SQL `NULL` value reserves zero bytes in the data part of the record if stored in a variable length column. In a fixed-length column, it reserves the fixed length of the column in the data part of the record. The motivation behind reserving the fixed space for `NULL` values is that then an update of the column from `NULL` to a non-`NULL` value can be done in place and does not cause fragmentation of the index page.

## 15.14. Gestión de espacio de ficheros y de E/S de disco (Disk I/O)

### 15.14.1. E/S de disco (Disk I/O)

`InnoDB` uses simulated asynchronous disk I/O: `InnoDB` creates a number of threads to take care of I/O operations, such as read-ahead.

There are two read-ahead heuristics in `InnoDB`:

- In sequential read-ahead, if `InnoDB` notices that the access pattern to a segment in the tablespace is sequential, it posts in advance a batch of reads of database pages to the I/O system.

- In random read-ahead, if `InnoDB` notices that some area in a tablespace seems to be in the process of being fully read into the buffer pool, it posts the remaining reads to the I/O system.

`InnoDB` uses a novel file flush technique called doublewrite . It adds safety to recovery following an operating system crash or a power outage, and improves performance on most varieties of Unix by reducing the need for `fsync()` operations.

Doublewrite means that before writing pages to a data file, `InnoDB` first writes them to a contiguous tablespace area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer has completed does `InnoDB` write the pages to their proper positions in the data file. If the operating system crashes in the middle of a page write, `InnoDB` can later find a good copy of the page from the doublewrite buffer during recovery.

### 15.14.2. Usar dispositivos en bruto (raw devices) para espacios de tablas

In MySQL 5.0, you can also use raw disk partitions as tablespace data files. By using a raw disk, you can perform non-buffered I/O on Windows and on some Unix systems without filesystem overhead, which may improve performance.

When you create a new data file, you must put the keyword `newraw` immediately after the data file size in `innodb_data_file_path`. The partition must be at least as large as the size that you specify. Note that 1MB in `InnoDB` is 1024 * 1024 bytes, whereas 1MB usually means 1,000,000 bytes in disk specifications.

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

The next time you start the server, `InnoDB` notices the `newraw` keyword and initializes the new partition. However, do not create or change any `InnoDB` tables yet. Otherwise, when you next restart the server, `InnoDB` reinitializes the partition and your changes are lost. (Starting from 3.23.44, as a safety measure `InnoDB` prevents users from modifying data when any partition with `newraw` is specified.)

After `InnoDB` has initialized the new partition, stop the server, change `newraw` in the data file specification to `raw`:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

Then restart the server and `InnoDB` allows changes to be made.

On Windows, you can allocate a disk partition as a data file like this:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=//./D::10Gnewraw

The `//./` corresponds to the Windows syntax of `\\.\` for accessing physical drives.

When you use raw disk partitions, be sure that they have permissions that allow read and write access by the account used for running the MySQL server.

### 15.14.3. Gestión del espacio de ficheros

The data files you define in the configuration file form the tablespace of `InnoDB`. The files are simply concatenated to form the tablespace. There is no striping in use. Currently you cannot define where in the tablespace your tables are allocated. However, in a newly created tablespace, `InnoDB` allocates space starting from the first data file.

The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of 64 consecutive pages. The “files” inside a tablespace are called segments in `InnoDB`. The term “rollback segment” is somewhat confusing because it actually contains many tablespace segments.

Two segments are allocated for each index in `InnoDB`. One is for non-leaf nodes of the B-tree, the other is for the leaf nodes. The idea here is to achieve better sequentiality for the leaf nodes, which contain the data.

When a segment grows inside the tablespace, `InnoDB` allocates the first 32 pages to it individually. After that `InnoDB` starts to allocate whole extents to the segment. `InnoDB` can add to a large segment up to 4 extents at a time to ensure good sequentiality of data.

Some pages in the tablespace contain bitmaps of other pages, and therefore a few extents in an `InnoDB` tablespace cannot be allocated to segments as a whole, but only as individual pages.

When you ask for available free space in the tablespace by issuing a `SHOW TABLE STATUS`, `InnoDB` reports the extents that are definitely free in the tablespace. `InnoDB` always reserves some extents for clean-up and other internal purposes; these reserved extents are not included in the free space.

When you delete data from a table, `InnoDB` contracts the corresponding B-tree indexes. It depends on the pattern of deletes whether that frees individual pages or extents to the tablespace, so that the freed space becomes available for other users. Dropping a table or deleting all rows from it is guaranteed to release the space to other users, but remember that deleted rows are physically removed only in an (automatic) purge operation after they are no longer needed for transaction rollbacks or consistent reads.

### 15.14.4. Desfragmentar una tabla

If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

A symptom of fragmentation is that a table takes more space than it 'should take'. How much exactly is that, is difficult to determine. All InnoDB data and indexes are stored in B-trees, and their fillfactor may vary 50 % to 100 %. Another symptom of fragmentation is that a table scan:

    SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;

takes more time than it should. (In the above query, we are “fooling” the SQL optimizer into scanning the clustered index, not a secondary index.) Most disks can read 10 - 50 MB/s. This can be used to estimate how fast a table scan should run.

It can speed up index scans if you periodically perform a “null” `ALTER TABLE` operation:

    ALTER TABLE tbl_name ENGINE=INNODB

That causes MySQL to rebuild the table. Another way to perform a defragmention operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

If the insertions to an index are always ascending and records are deleted only from the end, the `InnoDB` file space management algorithm guarantees that fragmentation of the index does not occur.

## 15.15. Tratamiento de errores de InnoDB

Error handling in `InnoDB` is not always the same as specified in the SQL standard. According to the standard, any error during an SQL statement should cause the rollback of that statement. `InnoDB` sometimes rolls back only part of the statement, or the whole transaction. The following items describe how `InnoDB` performs error handling:

- If you run out of file space in the tablespace, you get the MySQL `Table is full` error and `InnoDB` rolls back the SQL statement.

- A transaction deadlock or a timeout in a lock wait causes `InnoDB` to roll back the whole transaction.

- A duplicate-key error rolls back the SQL statement, if you have not specified the `IGNORE` option in your statement.

- A `row too long error` rolls back the SQL statement.

- Other errors are mostly detected by the MySQL layer of code (above the `InnoDB` storage engine level), and they roll back the corresponding SQL statement. Locks are not released in a rollback of a single SQL statement.

During implicit rollbacks, as well as during the execution of an explicit `ROLLBACK` SQL command, `SHOW PROCESSLIST` displays `Rolling back` in the `State` column for the relevant connection.

### 15.15.1. Códigos de error de `InnoDB`

The following is a non-exhaustive list of common `InnoDB`-specific errors that you may encounter, with information about why they occur and how to resolve the problem.

- `1005 (ER_CANT_CREATE_TABLE)`

  Cannot create table. If the error message string refers to `errno` 150, table creation failed because a foreign key constraint was not correctly formed.

- `1016 (ER_CANT_OPEN_FILE)`

  Cannot find the `InnoDB` table from the `InnoDB` data files though the `.frm` file for the table exists. See Sección 15.17.1, “Resolver problemas de las operaciones del diccionario de datos de `InnoDB`”.

- `1114 (ER_RECORD_FILE_FULL)`

  `InnoDB` has run out of free space in the tablespace. You should reconfigure the tablespace to add a new data file.

- `1205 (ER_LOCK_WAIT_TIMEOUT)`

  Lock wait timeout expired. Transaction was rolled back.

- `1213 (ER_LOCK_DEADLOCK)`

  Transaction deadlock. You should rerun the transaction.

- `1216 (ER_NO_REFERENCED_ROW)`

  You are trying to add a row but there is no parent row, and a foreign key constraint fails. You should add the parent row first.

- `1217 (ER_ROW_IS_REFERENCED)`

  You are trying to delete a parent row that has children, and a foreign key constraint fails. You should delete the children first.

### 15.15.2. Códigos de error del sistema oeprativo

To print the meaning of an operating system error number, use the perror program that comes with the MySQL distribution.

The following table provides a list of some common Linux system error codes. For a more complete list, see Linux source code.

- `1 (EPERM)`

  Operation not permitted

- `2 (ENOENT)`

  No such file or directory

- `3 (ESRCH)`

  No such process

- `4 (EINTR)`

  Interrupted system call

- `5 (EIO)`

  I/O error

- `6 (ENXIO)`

  No such device or address

- `7 (E2BIG)`

  Arg list too long

- `8 (ENOEXEC)`

  Exec format error

- `9 (EBADF)`

  Bad file number

- `10 (ECHILD)`

  No child processes

- `11 (EAGAIN)`

  Try again

- `12 (ENOMEM)`

  Out of memory

- `13 (EACCES)`

  Permission denied

- `14 (EFAULT)`

  Bad address

- `15 (ENOTBLK)`

  Block device required

- `16 (EBUSY)`

  Device or resource busy

- `17 (EEXIST)`

  File exists

- `18 (EXDEV)`

  Cross-device link

- `19 (ENODEV)`

  No such device

- `20 (ENOTDIR)`

  Not a directory

- `21 (EISDIR)`

  Is a directory

- `22 (EINVAL)`

  Invalid argument

- `23 (ENFILE)`

  File table overflow

- `24 (EMFILE)`

  Too many open files

- `25 (ENOTTY)`

  Inappropriate ioctl for device

- `26 (ETXTBSY)`

  Text file busy

- `27 (EFBIG)`

  File too large

- `28 (ENOSPC)`

  No space left on device

- `29 (ESPIPE)`

  Illegal seek

- `30 (EROFS)`

  Read-only file system

- `31 (EMLINK)`

  Too many links

The following table provides a list of some common Windows system error codes. For a complete list see the Microsoft website.

- `1 (ERROR_INVALID_FUNCTION)`

  Incorrect function.

- `2 (ERROR_FILE_NOT_FOUND)`

  The system cannot find the file specified.

- `3 (ERROR_PATH_NOT_FOUND)`

  The system cannot find the path specified.

- `4 (ERROR_TOO_MANY_OPEN_FILES)`

  The system cannot open the file.

- `5 (ERROR_ACCESS_DENIED)`

  Access is denied.

- `6 (ERROR_INVALID_HANDLE)`

  The handle is invalid.

- `7 (ERROR_ARENA_TRASHED)`

  The storage control blocks were destroyed.

- `8 (ERROR_NOT_ENOUGH_MEMORY)`

  Not enough storage is available to process this command.

- `9 (ERROR_INVALID_BLOCK)`

  The storage control block address is invalid.

- `10 (ERROR_BAD_ENVIRONMENT)`

  The environment is incorrect.

- `11 (ERROR_BAD_FORMAT)`

  An attempt was made to load a program with an incorrect format.

- `12 (ERROR_INVALID_ACCESS)`

  The access code is invalid.

- `13 (ERROR_INVALID_DATA)`

  The data is invalid.

- `14 (ERROR_OUTOFMEMORY)`

  Not enough storage is available to complete this operation.

- `15 (ERROR_INVALID_DRIVE)`

  The system cannot find the drive specified.

- `16 (ERROR_CURRENT_DIRECTORY)`

  The directory cannot be removed.

- `17 (ERROR_NOT_SAME_DEVICE)`

  The system cannot move the file to a different disk drive.

- `18 (ERROR_NO_MORE_FILES)`

  There are no more files.

- `19 (ERROR_WRITE_PROTECT)`

  The media is write protected.

- `20 (ERROR_BAD_UNIT)`

  The system cannot find the device specified.

- `21 (ERROR_NOT_READY)`

  The device is not ready.

- `22 (ERROR_BAD_COMMAND)`

  The device does not recognize the command.

- `23 (ERROR_CRC)`

  Data error (cyclic redundancy check).

- `24 (ERROR_BAD_LENGTH)`

  The program issued a command but the command length is incorrect.

- `25 (ERROR_SEEK)`

  The drive cannot locate a specific area or track on the disk.

- `26 (ERROR_NOT_DOS_DISK)`

  The specified disk or diskette cannot be accessed.

- `27 (ERROR_SECTOR_NOT_FOUND)`

  The drive cannot find the sector requested.

- `28 (ERROR_OUT_OF_PAPER)`

  The printer is out of paper.

- `29 (ERROR_WRITE_FAULT)`

  The system cannot write to the specified device.

- `30 (ERROR_READ_FAULT)`

  The system cannot read from the specified device.

- `31 (ERROR_GEN_FAILURE)`

  A device attached to the system is not functioning.

- `32 (ERROR_SHARING_VIOLATION)`

  The process cannot access the file because it is being used by another process.

- `33 (ERROR_LOCK_VIOLATION)`

  The process cannot access the file because another process has locked a portion of the file.

- `34 (ERROR_WRONG_DISK)`

  The wrong diskette is in the drive. Insert %2 (Volume Serial Number: %3) into drive %1.

- `36 (ERROR_SHARING_BUFFER_EXCEEDED)`

  Too many files opened for sharing.

- `38 (ERROR_HANDLE_EOF)`

  Reached the end of the file.

- `39 (ERROR_HANDLE_DISK_FULL)`

  The disk is full.

- `87 (ERROR_INVALID_PARAMETER)`

  The parameter is incorrect. (If you get this error on Windows, and you have set `innodb_file_per_table` in `my.cnf` or `my.ini`, then add the line `innodb_flush_method=unbuffered` to your `my.cnf` or `my.ini` file.)

- `112 (ERROR_DISK_FULL)`

  The disk is full.

- `123 (ERROR_INVALID_NAME)`

  The filename, directory name, or volume label syntax is incorrect.

- `1450 (ERROR_NO_SYSTEM_RESOURCES)`

  Insufficient system resources exist to complete the requested service.

## 15.16. Restricciones de las tablas `InnoDB`

- A table cannot contain more than 1000 columns.

- The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.

- The maximum row length, except for `VARCHAR`, `BLOB` and `TEXT` columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. `LONGBLOB` and `LONGTEXT` columns must be less than 4GB, and the total row length, including also `BLOB` and `TEXT` columns, must be less than 4GB. `InnoDB` stores the first 768 bytes of a `VARCHAR`, `BLOB`, or `TEXT` column in the row, and the rest into separate pages.

- On some older operating systems, data files must be less than 2GB.

- The combined size of the `InnoDB` log files must be less than 4GB.

- The minimum tablespace size is 10MB. The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table.

- `InnoDB` tables do not support `FULLTEXT` indexes.

- `InnoDB` tables do not support spatial column types.

- `ANALYZE TABLE` counts `cardinality` by doing 10 random dives to each of the index trees and updating index cardinality estimates accordingly. Note that because these are only estimates, repeated runs of `ANALYZE TABLE` may produce different numbers. This makes `ANALYZE TABLE` fast on `InnoDB` tables but not 100% accurate as it doesn't take all rows into account.

  MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you may try using `ANALYZE TABLE`. In the few cases that `ANALYZE TABLE` doesn't produce values good enough for your particular tables, you can use `FORCE INDEX` with your queries to force the usage of a particular index, or set `max_seeks_for_key` to ensure that MySQL prefers index lookups over table scans. See Sección 5.3.3, “Variables de sistema del servidor”. See Sección A.6, “Cuestiones relacionadas con el optimizados”.

- On Windows, `InnoDB` always stores database and table names internally in lowercase. To move databases in binary format from Unix to Windows or from Windows to Unix, you should have all database and table names in lowercase.

- Warning: Do not convert MySQL system tables in the `mysql` database from `MyISAM` to `InnoDB` tables! This is an unsupported operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or re-generate them with the mysql_install_db script.

- `InnoDB` does not keep an internal count of rows in a table. (This would actually be somewhat complicated because of multi-versioning.) To process a `SELECT COUNT(*) FROM T` statement, `InnoDB` must scan an index of the table, which takes some time if the index is not entirely in the buffer pool. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If your table does not change often, using the MySQL query cache is a good solution. `SHOW TABLE STATUS` also can be used if an approximate row count is sufficient. See Sección 15.11, “Consejos de afinamiento del rendimiento de `InnoDB`”.

- For an `AUTO_INCREMENT` column, you must always define an index for the table, and that index must contain just the `AUTO_INCREMENT` column. In `MyISAM` tables, the `AUTO_INCREMENT` column may be part of a multi-column index.

- `InnoDB` does not support the `AUTO_INCREMENT` table option for setting the initial sequence value in a `CREATE TABLE` or `ALTER TABLE` statement. To set the value with `InnoDB`, insert a dummy row with a value one less and delete that dummy row, or insert the first row with an explicit value specified.

- When you restart the MySQL server, `InnoDB` may reuse an old value for an `AUTO_INCREMENT` column (that is, a value that was assigned to an old transaction that was rolled back).

- When an `AUTO_INCREMENT` column runs out of values, `InnoDB` wraps a `BIGINT` to `-9223372036854775808` and `BIGINT UNSIGNED` to `1`. However, `BIGINT` values have 64 bits, so do note that if you were to insert one million rows per second, it would still take nearly three hundred thousand years before `BIGINT` reached its upper bound. With all other integer type columns, a duplicate-key error results. This is similar to how `MyISAM` works, because it is mostly general MySQL behavior and not about any storage engine in particular.

- `DELETE FROM `tbl_name`` does not regenerate the table but instead deletes all rows, one by one.

- `TRUNCATE `tbl_name`` is mapped to `DELETE FROM `tbl_name`` for `InnoDB` and doesn't reset the `AUTO_INCREMENT` counter.

- `SHOW TABLE STATUS` does not give accurate statistics on `InnoDB` tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.

- In MySQL 5.0, the MySQL `LOCK TABLES` operation acquires two locks on each table if `innodb_table_locks=1`, with 1 being the default.) In addition to a table lock on the MySQL layer, it also acquires an `InnoDB` table lock. Older versions of MySQL did not acquire `InnoDB` table locks; the old behavior can be selected by setting `innodb_table_locks=0`. If no `InnoDB` table lock is acquired, `LOCK TABLES` completes even if some records of the tables are being locked by other transactions.

- All `InnoDB` locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke `LOCK TABLES` on `InnoDB` tables in `AUTOCOMMIT=1` mode, because the acquired `InnoDB` table locks would be released immediately.

- Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, `LOCK TABLES` in MySQL performs an implicit `COMMIT` and `UNLOCK TABLES`. An InnoDB variant of `LOCK TABLES` has been planned that can be executed in the middle of a transaction.

- The `LOAD TABLE FROM MASTER` statement for setting up replication slave servers does not yet work for `InnoDB` tables. A workaround is to alter the table to `MyISAM` on the master, do then the load, and after that alter the master table back to `InnoDB`.

- The default database page size in `InnoDB` is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB. You have to update the values of `UNIV_PAGE_SIZE` and `UNIV_PAGE_SIZE_SHIFT` in the `univ.i` source file.

- In MySQL 5.0, triggers are not yet activated by cascaded foreign key actions.

## 15.17. Resolver problemas relacionados con `InnoDB`

- A general rule is that when an operation fails or you suspect a bug, you should look at the MySQL server error log, which typically has a name something like ``hostname`.err`, or possibly `mysql.err` on Windows.

- When troubleshooting, it is usually best to run the MySQL server from the command prompt, rather than through the mysqld_safe wrapper or as a Windows service. You can then see what mysqld prints to the console, and so have a better grasp of what is going on. On Windows, you must start the server with the `--console` option to direct the output to the console window.

- Use the `InnoDB` Monitors to obtain information about a problem. If the problem is performance-related, or your server appears to be hung, you should use `innodb_monitor` to print information about the internal state of `InnoDB`. If the problem is with locks, use `innodb_lock_monitor`. If the problem is in creation of tables or other data dictionary operations, use `innodb_table_monitor` to print the contents of the `InnoDB` internal data dictionary.

- If you suspect that a table is corrupt, run `CHECK TABLE` on that table.

### 15.17.1. Resolver problemas de las operaciones del diccionario de datos de `InnoDB`

A specific issue with tables is that the MySQL server keeps data dictionary information in `.frm` files it stores in the database directories, while `InnoDB` also stores the information into its own data dictionary inside the tablespace files. If you move `.frm` files around, or if the server crashes in the middle of a data dictionary operation, the `.frm` files may end up out of sync with InnoDB's internal data dictionary.

A symptom of an out-of-sync data dictionary is that a `CREATE TABLE` statement fails. If this occurs, you should look in the server's error log. If the log says that the table already exists inside the `InnoDB` internal data dictionary, you have an orphaned table inside the `InnoDB` tablespace files that has no corresponding `.frm` file. The error message looks like this:

    InnoDB: Error: table test/parent already exists in InnoDB internal
    InnoDB: data dictionary. Have you deleted the .frm file
    InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
    InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
    InnoDB: See the Restrictions section of the InnoDB manual.
    InnoDB: You can drop the orphaned table inside InnoDB by
    InnoDB: creating an InnoDB table with the same name in another
    InnoDB: database and moving the .frm file to the current database.
    InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
    InnoDB: succeed.

You can drop the orphaned table by following the instructions given in the error message.

Another symptom of an out-of-sync data dictionary is that MySQL prints an error that it cannot open a `.InnoDB` file:

    ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)

In the error log you can find a message like this:

    InnoDB: Cannot find table test/child2 from the internal data dictionary
    InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
    InnoDB: have deleted and recreated InnoDB data files but have forgotten
    InnoDB: to delete the corresponding .frm files of InnoDB tables?

This means that there is an orphaned `.frm` file without a corresponding table inside `InnoDB`. You can drop the orphaned `.frm` file by deleting it manually.

If MySQL crashes in the middle of an `ALTER TABLE` operation, you may end up with an orphaned temporary table inside the `InnoDB` tablespace. Using `innodb_table_monitor` you can see listed a table whose name is `#sql-...`. In MySQL 5.0, you can perform SQL statements on tables whose name contains the character '`#`' if you enclose the name in backticks. Thus, you can drop such an orphaned table like any other orphaned table using the method described above. Note that to copy or rename a file in the Unix shell, you need to put the file name in double quotes if the file name contains '`#`'.

Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.

Anterior |  | Siguiente
Capítulo 14. Motores de almacenamiento de MySQL y tipos de tablas | Inicio | Capítulo 16. MySQL Cluster