Para la mayoría de los desarrolladores de aplicaciones, la base de datos es un altar de dioses demoníacos al que es mejor no acercarse. Pero no tiene por qué ser así

En igualdad de condiciones, el nivel de comodidad de un desarrollador con la base de datos subyacente define su nivel de antigüedad. Poca base de datos y poca experiencia en codificación = desarrollador junior; poca base de datos y buena experiencia en codificación = desarrollador de nivel medio; buena base de datos y buena experiencia en codificación = desarrollador senior.

Es una dura realidad que incluso a los desarrolladores con 6-8 años a sus espaldas les cuesta explicar los entresijos del optimizador de consultas y prefieren mirar al cielo cuando se les pregunta por el ajuste de la base de datos.

¿Por qué?

Sorprendentemente, la razón no es la pereza (aunque en parte lo sea).

La cuestión es que las bases de datos son una fuerza propia a la que enfrentarse. Incluso tradicionalmente, cuando sólo había que lidiar con los tipos relacionales de bases de datos, dominarlas era un milagro y una carrera en sí misma; hoy en día, tenemos tantos tipos de bases de datos que es simplemente imposible esperar que una sola alma mortal lo domine todo.

Dicho esto, hay muchas posibilidades de que usted siga siendo feliz con las bases de datos relacionales o forme parte de un equipo que tiene un producto funcionando en una base de datos relacional de forma satisfactoria desde hace mucho, mucho tiempo. Y en nueve de cada diez casos, está en MySQL (o MariaDB). En estos casos, bucear un poco más bajo el capó aporta enormes beneficios a la hora de aumentar el rendimiento de las aplicaciones y merece la pena aprenderlo.

¿Tiene curiosidad? ¡Sumerjámonos!

¿No siente curiosidad? Pues sumérjase de todos modos, ¡porque su carrera depende de ello! 😛

Optimizar la caché de consultas MySQL

Casi toda la optimización en el campo de la informática se reduce al almacenamiento en caché. Por un lado, la CPU mantiene varios niveles de caché para acelerar sus cómputos, y por otro, las aplicaciones web hacen un uso agresivo de soluciones de caché como Redis para servir resultados precomputados a los usuarios en lugar de golpear la base de datos cada vez.

Pero oiga, ¡incluso la pobre base de datos MySQL tiene su propia caché de consultas! Es decir, cada vez que consulte algo, y los datos aún estén obsoletos, MySQL servirá estos resultados almacenados en caché en lugar de ejecutar la consulta de nuevo, haciendo que la aplicación sea ridículamente más rápida.

Puede comprobar si tiene disponible la caché de consultas (nota, disponible, no habilitada) en su base de datos ejecutando esta consulta en la consola de la base de datos:

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
 ------------------ ------- 
| Nombre_variable Valor
 ------------------ ------- 
| have_query_cache | YES |
 ------------------ -------

Por lo tanto, puede ver que estoy ejecutando MariaDB y que tengo el almacenamiento en caché de consultas disponible para ser activado. Es extremadamente improbable que lo tenga desactivado si está usando una instalación estándar de MySQL.

Ahora veamos si tengo la caché de consultas realmente activada:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
 ------------------ ------- 
| Nombre_de_la_variable Valor
 ------------------ ------- 
| query_cache_type | ON
 ------------------ -------

Sí, lo tiene. Pero en caso de que no lo haga, puede activarlo diciendo

MariaDB [(none)]> SET GLOBAL query_cache_type = ON;

Curiosamente, esta variable también acepta un tercer valor que denota «bajo demanda», lo que significa que MySQL almacenará en caché sólo las consultas que le indiquemos, pero no entraremos en eso aquí.

Con esto, ya tiene activada la caché de consultas y ¡ha dado el primer paso hacia una configuración MySQL más robusta! Digo el primer paso porque aunque activarlo es una gran mejora, necesitamos ajustar el almacenamiento en caché de consultas para que se adapte a nuestra configuración. Así que aprendamos a hacerlo.

La otra variable de interés aquí es query_cache_size, cuya función se explica por sí misma:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
 ------------------ ---------- 
| Nombre_de_la_variable Valor
 ------------------ ---------- 
| query_cache_size | 16777216 |
 ------------------ ----------

Por lo tanto, tengo una caché de consulta de alrededor de 16 MB de tamaño. Tenga en cuenta que incluso si la caché de consulta está activada, pero este tamaño es cero, la caché está efectivamente desactivada. Por eso comprobar sólo una variable no es suficiente. Ahora bien, debe establecer un tamaño de caché de consulta, pero ¿cuánto debe ser? En primer lugar, tenga en cuenta que la función de caché de consultas necesitará por sí misma 4 KB para almacenar sus metadatos, por lo que lo que seleccione deberá estar por encima de esa cantidad.

Digamos que establece el tamaño de la caché de consultas en 500 KB:

MariaDB [(none)]> SET GLOBAL query_cache_size = 500000;

¿Hacer esto es suficiente? Pues no, porque el rendimiento real del motor de consultas depende de un par de cosas más:

  • En primer lugar, la variable query_cache_size debe ser lo suficientemente grande como para contener el resultado de sus consultas. Si es demasiado pequeña, no se almacenará nada en la caché.
  • En segundo lugar, si query_cache_size se fija en un número demasiado alto, habrá dos tipos de problemas: 1) El motor tendrá que hacer un trabajo extra almacenando y localizando los resultados de las consultas en esta enorme zona de memoria. 2) Si la mayoría de las consultas dan como resultado tamaños mucho más pequeños, la caché se fragmentará y se perderán las ventajas de utilizar una caché.

¿Cómo saber si la caché se está fragmentando? Compruebe el número total de bloques de la caché de la siguiente manera:

MariaDB [(none)]> show status like 'Qcache_total_blocks';
 --------------------- ------- 
| Nombre_variable Valor
 --------------------- ------- 
| Qcache_total_blocks 33
 --------------------- -------

Si el número es muy alto, la caché está fragmentada y necesita ser vaciada.

Así que, para evitar estos problemas, asegúrese de que el tamaño de query_cache_size se elige sabiamente. Si se siente frustrado porque no le he dejado aquí un número concreto, me temo que así son las cosas una vez que se pasa del desarrollo a la ingeniería. Debe examinar la aplicación que está ejecutando y ver cuáles son los tamaños de consulta para los resultados de consulta importantes y, a continuación, fijar este número. E incluso entonces puede que acabe cometiendo un error 🙂

Threading, pools de hilos, esperas y tiempos de espera

Esta es probablemente la parte más interesante de cómo funciona MySQL y hacerlo bien significa ¡hacer su aplicación varias veces más rápida!

Threading

MySQL es un servidor multihilo. Eso significa que, cada vez que hay una nueva conexión al servidor MySQL, éste abre un nuevo hilo con los datos de conexión y pasa un handle al mismo al cliente (Lea este artículo si se pregunta qué es un hilo). El cliente envía entonces todas las consultas sobre este hilo y recibe los resultados. Esto nos lleva a hacernos una pregunta natural: ¿cuántos hilos puede hacer girar MySQL? La respuesta se encuentra en la siguiente sección.

Reserva de hilos

Ningún programa en un sistema informático puede abrir tantos hilos como desee. La razón es doble: 1) Los hilos cuestan memoria (RAM), y el sistema operativo simplemente no le permitirá volverse loco y comérsela toda. 2) Gestionar, digamos, un millón de hilos es una tarea enorme por sí sola, y si el servidor MySQL pudiera crear tantos hilos, moriría intentando lidiar con la sobrecarga.

Para evitar estos problemas, MySQL viene con un pool de hilos — un número fijo de hilos que forman parte de un pool al inicio. Las nuevas peticiones de conexión hacen que MySQL coja uno de estos hilos y devuelva los datos de conexión, y si se agotan todos los hilos, naturalmente se rechazan nuevas conexiones. Veamos cómo de grande es el pool de hilos:

ariaDB [(none)]> mostrar variables como 'thread_pool_size';
 ------------------ ------- 
| Nombre_de_la_variable Valor
 ------------------ ------- 
| thread_pool_size | 4 |
 ------------------ -------

Así pues, mi máquina permite un máximo de cuatro conexiones al mismo tiempo. Es interesante observar que el número 4 proviene del hecho de que tengo un procesador de cuatro núcleos, lo que significa que mi ordenador sólo puede ejecutar 4 tareas paralelas a la vez (aquí hablo de tareas verdaderamente paralelas, no concurrentes). Idealmente, este es el límite que debería impulsar el valor de thread_pool_size, pero en máquinas más potentes aumentarlo beneficia hasta cierto punto. Si no quiere hacer esperar a todas las conexiones nuevas y está de acuerdo en asumir algún golpe en el rendimiento (de nuevo, esta es un área que puede juzgar mejor basándose en el rendimiento de su aplicación bajo carga), aumentarlo a 8 puede ser una buena idea.

Sin embargo, fijarlo más allá de 16 es una idea terrible a menos que tenga una máquina de 32 núcleos, ya que el rendimiento se degrada significativamente. La madriguera del conejo de los pools de hilos en MySQL es profunda, pero si está interesado, aquí tiene una discusión más detallada.

Esperas y tiempos de espera

Una vez que se ha creado un hilo y se ha adjuntado a un cliente, sería un desperdicio de recursos si el cliente no enviara ninguna consulta durante los siguientes segundos (o minutos). Como resultado, MySQL termina una conexión después de un periodo de inactividad. Esto es controlado por la variable wait_timeout:

MariaDB [(none)]> mostrar variables como 'wait%';
 --------------- ------- 
| Nombre_de_la_variable | Valor |
 --------------- ------- 
| wait_timeout | 28800 |
 --------------- -------

El valor resultante está en segundos. Así que sí, ¡por defecto MySQL está configurado para esperar 8 horas antes de cortar la cuerda! Esto puede ser bueno si tiene consultas de larga duración y realmente quiere esperarlas (pero incluso entonces, ¡ocho horas es absurdo!) pero terrible en la mayoría de los casos. Cuando se ejecuta una consulta, este valor se fija en 0 (es decir, para siempre), pero en general, debería fijarse en un valor muy bajo (5 segundos, por ejemplo, o incluso menos) para liberar la conexión para otros procesos.

Ajuste de las tablas temporales

Empecemos por lo que son las tablas temporales en MySQL.

Supongamos que tenemos un MySQL que estructuralmente se parece a esto TABLA A UNION (TABLA B INNER JOIN C). Es decir, estamos interesados en unir las tablas B y C, y luego realizar una unión del resultado con la tabla A. Ahora bien, MySQL procedería primero a unir las tablas B y C, pero antes de poder realizar una unión, necesita almacenar estos datos en algún lugar. Aquí es donde entran en juego las tablas temporales — MySQL las utiliza para almacenar datos en etapas intermedias de consultas complejas de forma temporal, y una vez que la consulta ha finalizado, esta tabla temporal se descarta.

Ahora la pregunta es: ¿por qué debemos molestarnos con todo esto?

Sencillamente porque la tabla temporal, sólo un resultado de consulta, son datos que están siendo utilizados por MySQL en el cálculo, y la velocidad de su acceso (entre otras limitaciones) determinará lo rápido que se ejecute la consulta. Por ejemplo, almacenar la tabla temporal en RAM será varias veces más rápido que almacenarla en el disco.

Existen dos variables que controlan este comportamiento:

MariaDB [(none)]> mostrar variables como 'MariaDB [(none)]> mostrar variables como 'tmp_table_size';  
 ---------------- ---------- 

| Nombre_de_la_variable Valor

 ---------------- ---------- 

| Tamaño_tabla_tmp 16777216

 ---------------- ---------- 
';
 --------------------- ---------- 
| Nombre_variable Valor
 --------------------- ---------- 
| max_heap_table_size | 16777216 |
 --------------------- ---------- 

MariaDB [(none)]> mostrar variables como 'tmp_table_size';
 ---------------- ---------- 
| Nombre_de_la_variable Valor
 ---------------- ---------- 
| tmp_table_size | 16777216 |
 ---------------- ----------

La primera, max_heap_table_size, nos dice cuánta RAM puede consumir una tabla MySQL («heap» se refiere aquí a la estructura de datos utilizada en la asignación y gestión de RAM — lea más aquí), mientras que la segunda, tmp_table_size, muestra cuál es el tamaño máximo de la tabla temporal. En mi caso, ambos están configurados a 16 MB, aunque el punto que estoy tratando de hacer es que aumentar sólo tmp_table_size no funcionará ya que en general, MySQL seguiría estando limitado por max_table_heap_size.

Ahora viene el punto: si las tablas temporales que se están creando son más grandes que el límite permitido por estas variables, MySQL se vería forzado a escribirlas en el disco duro, resultando en un rendimiento extremadamente pobre. Nuestro trabajo ahora es simple: hacer todo lo posible para adivinar el tamaño más exacto de los datos para las tablas temporales y ajustar estas variables a ese límite. Sin embargo, me gustaría advertir contra el absurdo: fijar este límite en 16 GB (suponiendo que disponga de esta cantidad de RAM) cuando la mayoría de sus tablas temporales tienen un tamaño inferior a 24 MB es una tontería — simplemente está desperdiciando RAM que podría haber sido utilizada por otras consultas o partes del sistema (caché, por ejemplo).

Conclusión

No es posible cubrir todas las variables del sistema en un artículo, o incluso todas las importantes en un artículo cuando la propia documentación de MySQL abarca varios miles de palabras. Aunque aquí hemos cubierto algunas variables universales, le animo a que investigue las variables del sistema para el motor que esté utilizando(InnoDB o MyISAM).

Mi resultado más deseable para escribir este artículo es que se lleve tres cosas:

  1. MySQL es una pieza típica de software que funciona dentro de los límites establecidos por el sistema operativo. No es un programa misterioso que hace Dios sabe qué y es imposible de domar. También, afortunadamente, no es tan difícil entender cómo está configurado y es controlado por sus variables de sistema.
  2. No existe un único ajuste que haga que su instalación de MySQL funcione a las mil maravillas. No tiene más remedio que buscar dentro de sus sistemas en funcionamiento (recuerde, la optimización viene después de que la aplicación esté en producción, no antes), hacer las mejores conjeturas y mediciones, y vivir con la realidad de que nunca va a ser perfecto.
  3. Afinar las variables no es la única manera de optimizar MySQL — la eficiencia en la escritura de consultas es otra gran cosa, pero es algo que trataré en otro artículo. Pero la cuestión es que, incluso si ha hecho un análisis divino y ha afinado estos parámetros al máximo, todavía es posible que todo se venga abajo.

¿Cuál es su variable favorita del sistema para afinar? 🙂