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).

This entry was posted in Linux, MySQL, Storage and tagged , , . Bookmark the permalink.
  • TooMany Secrets

    Según mi ex-jefe, era mucho más partidario de usar la caché del filesystem que no la del mysql, ya que él piensa que por defecto la caché del fs será mucho mejor y más rápida, al ser manejada directamente por el kernel. En otras palabras, juzga más eficiente el uso de caché del FS que no del mysql. De hecho llegué a configurar varios MySQL con el O_DIRECT, pero al cabo de unos meses me hizo revertir el tema.
    Personalmente creo que no es así o, quizás, según sea el caso. Pero igualmente creo que debes tener algún problema en tu diseño y/o (más seguro) en tus querys, si tienes que usar la caché del FS y no la propia del mysql ya ideada para ello.

    • http://woop.es Santi Saez

      Manuel, un placer ver un comentario tuyo por aquí, gracias! :)

      En general yo también pienso que un sistema de cache específico como es el buffer pool de InnoDB es más eficiente que delegar este trabajo al sistema operativo y además te quitas el doble buffering.

      En cualquier caso, la propia documentación de MySQL alerta para innodb_flush_method que:

      On some systems where InnoDB data and log files are located on a SAN, it has been found that setting innodb_flush_method to O_DIRECT can degrade performance of simple SELECT statements by a factor of three.

      Como siempre lo importante es no cerrar puertas y hacer muchas pruebas para ver que configuración viene mejor en nuestro entorno!

      Saludos,

      • http://twitter.com/toomanysecrets Manuel Trujillo

        El placer es mío ;-)
        Como había indicado vía twitter, el tema de postgresql cambia precisamente también por la plataforma donde se desarrolla; freebsd. El filesystem UFS2, entre otras perlas, tiene un tema que le permite predecir, con una exactitud flipante , qué el lo próximo que se tiene que hacer (aparte del comportamiento propio del kernel del sistema). Esta es una de las razones por las que siempre (aunque la gente se me extrañaba), he montado postgresql bajo FreeBSD con UFS2+softupdates; siempre me ha dado mucho más rendimiento en el mismo hierro que no bajo Linux+ponga-aquí-su-FS-favorito.
        En cambio MySQL se desarrolla bajo Linux y, creo yo que por lógica, lo que hacen también es adaptarse en lo posible al kernel que lo acoge (a día de hoy mysql rinde super-bien bajo FreeBSD, pero aunque yo sea un gran partidario de este magnífico sistema operativo, siempre monto mysql bajo Linux cuando tiene que soportar grandes cargas).

  • http://twitter.com/miguel2angel Miguel Angel Nieto

    El buffer pool de InnoDB es mucho más eficiente que el propio buffer del FS. Por el simple hecho de que sacar un dato del buffer del FS requiere muchas más llamadas al sistema que si lo sacamos directamente del buffer pool.

    InnoDB siempre va a buscar en su buffer pool y si no lo encuentra se va al disco. ¿Que ventaja cree tu jefe que puede tener todas esas llamadas extras? Por otro lado, el buffer pool conoce la estructura interna (claves primarias, secundarias, etc.) mientras que el del FS solamente almacena datos sin conocer ninguna lógica que le ayude a encontrar los datos que buscas.

  • Luis Ugalde (@forondarena)

     Yo creo que la mayoría de motores de bases de datos prefieren gestionar cuanta más memoria mejor por ellos mismos.

    Sin embargo, y por dar algún ejemplo de software que ve las cosas de otra forma; por lo lo poco que sé de PostgreSQL, me parece que no siguen esta misma idea.

    Para algunos tipos de consultas grandes, gestión de vacuum y para algunas operaciones con el WAL, entre otras, postgreSQL prefiere usar sus buffers y sus algoritmos propios (como clock-sweep); pero para las operaciones que en el fondo usan llamadas estándar de SO se leen recomendaciones a favor de dejar al SO que gestione sus buffers, y con ello evitar el doble buffering.

    De hecho, un punto de partida que suele recomendarse por ejemplo para máquinas de 8G de ram es “sólo” 2G.

    Ah eso sí, lo que siempre recomienda todo el mundo es monitorizar y adaptar el sistema a las necesidades de cada caso.

    Una referencia: http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Smith/dp/184951030X/

  • Pingback: Miguel Angel Nieto » Pre-cachear los datos de InnoDB en el buffer pool