InnoDB es un motor de almacenamiento de propósito general de alta confiabilidad y alto rendimiento. Fue diseñado para optimizar la eficiencia de CPU y maximizar el rendimiento cuando se procesan grandes volúmenes de datos. Es el motor de almacenamiento por defecto desde MySQL 5.5. Entre las principales ventajas de InnoDB sobre MyISAM se pueden enumerar las siguientes:
- Sus operaciones de manipulación de datos (consultas SQL) respetan el modelo ACID (Atomicity, Consistency, Isolation and Durability). Lo cual significa que son atómicas (se ejecutan de forma completa, todo o nada); consistentes (se mantiene la consistencia de la base de datos en todo momento); aisladas (no interfieren con otras operaciones); y persistentes (resisten al paso del tiempo).
- Posee locks a nivel de filas, lo cual mejora la concurrencia y performance (MyISAM sólo posee locks a nivel tabla).
- Las tablas poseen sus datos ordenados de acuerdo a las claves primarias para optimizar las consultas (MyISAM no posee ordenamiento, los datos se insertan al final de la tabla).
- Soporta claves foráneas para mejorar la consistencia (MyISAM no). Los inserts, updates y deletes son verificados para asegurar la consistencia entre diferentes tablas.
Por supuesto, es posible mezclar tablas InnoDB con MyISAM u otros motores de almacenamiento de MySQL.
InnoDB mantiene su propio buffer pool para caching de datos e índices en memoria principal. Conocer cómo funciona este buffer pool, y aprovecharlo para mantener los datos más frecuentemente accedidos en memoria, es uno de los aspectos más importantes del tunning de performance para mejorar el rendimiento del motor de almacenamiento InnoDB.
En la práctica, el tamaño del buffer pool debe ser lo más grande que sea posible, dejando memoria suficiente para el resto de los procesos en el servidor sin incurrir en paginado excesivo, o trashing de memoria. Cuanto más grande sea el pool, más InnoDB se comporta como una base de datos en memoria, leyendo los datos desde disco una vez, y accediendo a memoria en accesos subsecuentes. Incluso se almacenan en caché los datos modificados por inserts y updates para que las escrituras a disco puedan ser agrupadas para mejorar aún más la performance.
InnoDB gestiona el pool como una lista ordenada, utilizando una variación del algoritmo LRU (menos recientemente utilizado). Cuando se necesita espacio dentro del pool para un nuevo bloque, InnoDB desaloja al bloque menos recientemente utilizado y agrega al nuevo bloque al medio de la lista. Esta estrategia de insertar en el medio divide a la lista en bloques "nuevos" (los más recientemente accedidos), desde el medio hasta el tope; y en bloques "viejos" (los menos recientemente accedidos), desde el medio hasta la cola. Cada vez que se lee un bloque, se envía al tope de la lista, y a medida que no son accedidos van "envejeciendo", moviéndose hacia la cola de la lista. De esta forma, los bloques más utilizados por las consultas se mantienen como bloques "nuevos", y permanecen en memoria principal.
Seleccionar el tamaño adecuado para el buffer pool de InnoDB es una de las opciones más importantes para mejorar el rendimiento de un servidor de bases de datos. Para volúmenes de datos pequeños, es suficiente con que el tamaño del buffer pool InnoDB sea un 10% más grande que el tamaño de los datos. De esta forma podríamos mantener todos los datos, junto con algunas estructuras adicionales (por ejemplo locks, buffer de inserts, etc.), todo el tiempo en memoria principal.
Si, en cambio, el tamaño de los datos es superior al de la memoria principal (y se trata de un servidor de bases de datos dedicado), vamos a querer aprovechar la mayor cantidad de memoria principal posible para asignar al buffer pool (siempre que no se quite memoria para el resto de los procesos en el sistema). En este caso se deberá decidir cuánta memoria asignar al buffer pool, teniendo en cuenta las necesidades del sistema operativo (estructuras de datos del kernel, otros procesos, buffers, etc.) y de MySQL (otros buffers, cachés, hilos de ejecución, tablas temporales, etc.). Tener en cuenta, además, que InnoDB asigna al buffer pool más memoria que la indicada en la configuración, que es utilizada para estructuras relacionadas al mismo (aproximadamente un 10% del tamaño especificado).
El tamaño del buffer pool se configura en la variable innodb_buffer_pool_size
dentro del archivo my.cnf
. En un servidor dedicado, con grandes volúmenes de datos, el valor recomendado es de aproximadamente un 80% del tamaño de la memoria principal. Por ejemplo, en un sistema con 2GB de memoria RAM, utilizo un buffer pool de 1600 MB:
innodb_buffer_pool_size = 1600M
Luego de modificar esta variable en el archivo de configuración del servidor, es necesario reiniciar el servicio (en MySQL 5.6 y anteriores):
[root@db ~]# service mysql restart
Es posible verificar que el tamaño del buffer pool haya quedado correctamente configurado, ejecutando la siguiente consulta:
mysql> SHOW VARIABLES LIKE '%buffer_pool_size%'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | innodb_buffer_pool_size | 1677721600 | +-------------------------+------------+ 1 row in set (0,00 sec)
Luego, comprobar las estadísticas de utilización de memoria en el sistema:
[root@db ~]# free -m total used free shared buffers cached Mem: 1876 1812 64 0 7 19 -/+ buffers/cache: 1785 91 Swap: 1535 480 1055
[root@db ~]# vmstat procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 0 0 492152 76448 2328 14656 2 1 24 51 13 4 2 0 97 1 0
Revisar cuidadosamente los valores de las columnas si
(swapped in/sec.) y so
(swapped out/sec.) para no incurrir en trashing de memoria.
Puede ocurrir que, inmediatamente luego de modificar el valor de tamaño, el buffer pool no utilice toda la memoria que tiene asignada, sino que vaya creciendo con el tiempo (hasta llegar al valor que se ha asignado). Por esta razón se debe monitorear con cuidado el servidor en cuestión luego de haber aplicado el cambio.
Notar que en este caso el sistema está utilizando 480 MB de memoria de intercambio (swap), pero no se ha incurrido en trashing, ya que los valores de si
y so
no son altos.
Es posible utilizar el comando top
, ordenando (utilizando 'O' mayúscula) por la columna "SWAP" (tecla 'P'), para determinar qué procesos están utilizando más memoria de intercambio:
top - 13:35:40 up 4 days, 6:16, 1 user, load average: 0.21, 0.08, 0.02 Tasks: 131 total, 1 running, 130 sleeping, 0 stopped, 0 zombie Cpu(s): 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 1921976k total, 1849304k used, 72672k free, 7592k buffers Swap: 1572860k total, 492692k used, 1080168k free, 13488k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ SWAP COMMAND 2406 mysql 20 0 3561m 1.6g 3636 S 0.0 88.1 484:16.23 200m mysqld 1600 root 20 0 687m 6616 4248 S 0.0 0.3 0:58.68 4288 beremote 1564 root 20 0 80856 1020 916 S 0.0 0.1 0:01.90 796 master 1579 postfix 20 0 81108 1044 920 S 0.0 0.1 0:00.44 796 qmgr 434 root 16 -4 10868 272 268 S 0.0 0.0 0:00.29 632 udevd 740 root 18 -2 10864 248 244 S 0.0 0.0 0:00.00 624 udevd 743 root 18 -2 10864 248 244 S 0.0 0.0 0:00.00 624 udevd 1397 nagios 20 0 39364 648 644 S 0.0 0.0 0:00.00 620 nrpe 1412 root 20 0 66216 504 400 S 0.0 0.0 0:00.00 608 sshd 1218 root 20 0 171m 1988 1652 S 0.3 0.1 3:26.11 576 vmtoolsd 1587 root 20 0 114m 672 596 S 0.0 0.0 0:01.36 524 crond 1358 root 20 0 243m 1108 792 S 0.0 0.1 0:03.90 340 rsyslogd
Se observa que el proceso mysqld
está utilizando 200 MB de espacio de intercambio.
Es importante evitar a toda costa que el sistema operativo mande a swap a MySQL (u otros procesos importantes) por no poseer suficiente memoria, por ejemplo para caching de archivos. Incluso puede ocurrir que el sistema operativo decida matar al proceso MySQL en caso de quedarse sin memoria (principal+swap).
Si MySQL está utilizando mucha swap (como en este caso), muy posiblemente parte del buffer pool resida en la misma. Por ende empeoramos las cosas, ya que ahora se requieren dos o más accesos a disco ante un miss en el buffer pool (al recorrer el buffer pool se producirán miss en memoria principal para aquellas páginas del mismo que se encuentran sólo en swap).
Por ende será necesario bajar el tamaño del buffer pool (presumiblemente a 1400M
) para que páginas de memoria del proceso MySQL no sean enviadas a swap.
Un detalle importante para optimizar el uso de la memoria principal, consiste en ajustar adecuadamente el parámetro del kernel "swappiness", el cual indica cuán agresivamente el kernel utiliza el área de intercambio. Por ejemplo, se puede configurar en 1
, o incluso 0
, para evitar lo máximo posible el uso de la swap:
[root@db ~]# echo 1 > /proc/sys/vm/swappiness [root@db ~]# sysctl vm.swappiness vm.swappiness = 1
Es posible configurar este parámetro de forma persistente en el archivo /etc/sysctl.conf
:
[root@db ~]# grep swap /etc/sysctl.conf vm.swappiness = 1
Más allá de esta recomendación general de asignar un 80% de la memoria principal al buffer pool de InnoDB en servidores dedicados, este valor puede cambiar dependiendo de la cantidad de memoria física que posea el sistema. Si el sistema posee poca memoria, se deberán garantizar como mínimo unos 300-500 MB para el resto del sistema operativo y procesos (por supuesto estos valores dependen de cada servidor y conjunto de procesos). Si en cambio el sistema posee mucha memoria, tal vez este número sea más grande. Por ejemplo, con 48 GB de memoria física, asignar unos 38 GB (80%) tal vez sea poco, porque dejaríamos unos 10 GB para el resto del sistema, lo cual sería un desperdicio. Tal vez un valor más adecuado sería 44-46 GB.
Además de configurar correctamente el tamaño del buffer pool de InnoDB, es importante deshabilitar el doble buffering que se produce a causa del sistema operativo. No es deseable que el sistema operativo haga caching de disco de lo que ya está cacheando InnoDB. Por otro lado, el buffer pool de InnoDB es más eficiente que el de cualquier sistema operativo. En los sitemas GNU/Linux y FreeBSD se debe configurar adecuadamente la variable innodb_flush_method
:
innodb_flush_method = O_DIRECT
De esta forma, se evita que el sistema operativo almacene en buffers los archivos abiertos por InnoDB.
En todo momento, es posible obtener información de estado del motor InnoDB ejecutando la siguiente consulta SQL:
mysql> SHOW STATUS like '%innodb%'; +-----------------------------------+-----------+ | Variable_name | Value | +-----------------------------------+-----------+ | Com_show_innodb_status | 0 | | Innodb_buffer_pool_pages_data | 29249 | | Innodb_buffer_pool_pages_dirty | 0 | | Innodb_buffer_pool_pages_flushed | 8 | | Innodb_buffer_pool_pages_free | 67133 | | Innodb_buffer_pool_pages_latched | 1 | | Innodb_buffer_pool_pages_misc | 6018 | | Innodb_buffer_pool_pages_total | 102400 | | Innodb_buffer_pool_read_ahead_rnd | 5 | | Innodb_buffer_pool_read_ahead_seq | 372 | | Innodb_buffer_pool_read_requests | 689038005 | | Innodb_buffer_pool_reads | 19274 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 8 | | Innodb_data_fsyncs | 35 | | Innodb_data_pending_fsyncs | 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes | 0 | | Innodb_data_read | 481398784 | | Innodb_data_reads | 20313 | | Innodb_data_writes | 35 | | Innodb_data_written | 271872 | | Innodb_dblwr_pages_written | 8 | | Innodb_dblwr_writes | 8 | | Innodb_log_waits | 0 | | Innodb_log_write_requests | 0 | | Innodb_log_writes | 9 | | Innodb_os_log_fsyncs | 19 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 4608 | | Innodb_page_size | 16384 | | Innodb_pages_created | 0 | | Innodb_pages_read | 29249 | | Innodb_pages_written | 8 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 0 | | Innodb_row_lock_time_avg | 0 | | Innodb_row_lock_time_max | 0 | | Innodb_row_lock_waits | 0 | | Innodb_rows_deleted | 0 | | Innodb_rows_inserted | 0 | | Innodb_rows_read | 229210331 | | Innodb_rows_updated | 0 | +-----------------------------------+-----------+ 44 rows in set (0,00 sec)
Referencias
MySQL 5.7 Reference Manual: Introduction to InnoDB
MySQL 5.7 Reference Manual: The InnoDB Buffer Pool
MySQL 5.7 Reference Manual: InnoDB Startup Options and System Variables - innodb_buffer_pool_size
MySQL 5.7 Reference Manual: InnoDB Startup Options and System Variables - innodb_flush_method
Documentation for /proc/sys/vm/*
PERCONA MYSQL PERFORMANCE BLOG: Choosing innodb_buffer_pool_size
Rackspace Support Network: MySQL Engines - MyISAM vs Innodb