Preload de tablas InnoDB en el buffer pool de MySQL

Este post se basa en un enlace que ha compartido Asier Marqués de blackslot.com en Twitter: How to preload tables into InnoDB buffer pool with MySQL?. El thread de stackoverflow.com describe como precargar tablas InnoDB en el buffer pool para tener el contenido en memoria y reducir los “misses” a disco.

Esta técnica es interesante cuando tenemos un pool de slaves MySQL y queremos que funcionen a pleno rendimiento desde el primer momento. Por ejemplo, si las primeras querys con el buffer “frio” necesitan 1 segundo para ejecutarse en lugar de unos pocos milisegundos, al conectar el slave MySQL al balanceador/proxy se puede degradar la experiencia de navegación durante el “warming up” (proceso para calentar el buffer InnoDB moviendo registros de disco a memoria que puede llevar minutos, horas, etc.).

Escenario: MySQL de PowerStack con la configuración por defecto y 1GB de datos InnoDB (30 millones de registros aprox.) sobre una VM con 4 cores y 2GB de RAM. El objetivo es cargar el contenido completo de la tabla InnoDB en el buffer pool y ver que tras el preload la memoria de “mysqld” crece en +1GB y las siguientes querys no generan I/O a disco. Antes de la precarga de datos MySQL está usando ~150MB de RAM y ha leído unos ~12MB desde disco:

# grep VmRSS /proc/`pidof mysqld`/status
VmRSS:    152848 kB

# grep read_bytes /proc/`pidof mysqld`/io
read_bytes: 12455936

La primera técnica consiste en forzar un full tablescan con un SELECT sobre una columna no indexada para que MySQL tenga que recorrer todas las filas y moverlas al buffer pool (si existe un índice lo podemos evitar con IGNORE INDEX):

mysql> SELECT COUNT(*) FROM tabla WHERE columna_no_index=0;
30 segundos

Tras el SELECT “mysqld” ha leído ~1GB desde disco y ha crecido +1GB en memoria RSS. Con la ayuda del contador interno innodb_buffer_pool_pages_data de MySQL vemos que esa memoria está destinada a InnoDB (el buffer pool está organizado en páginas de innodb_page_size bytes -generalmente 16KB-, para este caso: 64135 páginas x 16KB ~= 1GB):

# grep VmRSS /proc/`pidof mysqld`/status
VmRSS:   1177276 kB

# cat /proc/`pidof mysqld`/io
read_bytes: 1064693760

# mysqladmin extended | grep buffer_pool_pages_data
innodb_buffer_pool_pages_data = 64135

Lanzamos un segundo SELECT, en este caso con SQL_NO_CACHE para evitar sacar el resultado desde la query cache + un drop_caches para vaciar caches/buffers de Linux, y comprobamos que MySQL no ha generado I/O de lectura para obtener los datos, esto hace que la consulta sea mucho más rápida (de 30 a 5 segundos para un fullscan de 30M filas):

# echo 3 > /proc/sys/vm/drop_caches

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM tabla WHERE columna_no_index=0'
5 segundos

# cat /proc/`pidof mysqld`/io
read_bytes: 1064693760

Podríamos agregar este “hack” a la función start() del script de inicio MySQL y aprovechar para desfragmentar los ficheros .ibd, así las futuras lecturas/precargas serán secuenciales = más rápidas:

iptables -I MySQL -p tcp --dport 3306 -j DROP
mysql db -e 'OPTIMIZE TABLE tabla'
mysql db -e 'SELECT COUNT(*) FROM tabla WHERE columna_no_index=0'
iptables -F MySQL
(..)

Este tip es útil cuando el tamaño de las tablas es menor a la RAM, si los datos no entran en el buffer de InnoDB sería interesante poder hacer un dump del pool antes de parar MySQL para recuperar su contenido al arrancar, así el LRU de la cache InnoDB nos ayudaría a iniciar con los registros mas “populares”. El motor XtraDB de Percona permite realizar dump+restore del buffer pool de InnoDB, el hack se basa en un parche para MySQL 5.1 de Jeremy Cole (MySQL Database Architect en Twitter) de la ya extinta Proven Scaling.

Una vez tenemos el contenido de las tablas InnoDB en memoria tenemos que evitar que se muevan a swap, para ello bajamos el swappiness de Linux al 0% (dejar un servidor sin swap mediante swapoff no sería buena idea) y arrancamos MySQL con innodb_flush_method=O_DIRECT para evitar el doble buffering (interno de InnoDB + el de Linux, este último tiene estructuras en el filesytem que se pueden mover a swap, lectura relacionada).