Mejorando el rendimiento de InnoDB

Detalles: 10 Marzo 2015

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)