2 de Agosto, 2005

La caché de peticiones de MySQL

Desde MySQL 4.0.1, este SGBD dispone de una query cache que se encarga de guardar la cadena de texto de una petición junto con su respuesta para acceder a ella posteriormente, sin volver a analizar la sentencia SQL y ejecutarla, con el consiguiente aumento de velocidad.

Bien, pues no lo estaba usando :).

Voy a hacer un par de pruebas con el Apache benchmarking tool desde mi estación de trabajo en la misma red local del servidor. No pretende ser una comparativa rigurosa, solo ver si realmente hay un beneficio con el uso de la caché.

La prueba va a consistir en realizar 10 peticiones concurrentes durante 10 segundos:

$  ab -c 10 -t 10 -w http://blackshell.usebox.net/

Resultados sin query cache

...
Document Length:	39981 bytes
Concurrency Level:	10
Time taken for tests:	0.10010 seconds
Complete requests:	126
Failed requests:	0
Total transferred:	5218295 bytes
HTML transferred:	5188919 bytes
Requests per second:	12.59
...

Ahora comprobamos si tenemos soporte para la query cache y lo activamos.

mysql> show variables like '%query_cache%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| have_query_cache  | YES     |
| query_cache_limit | 1048576 |
| query_cache_size  | 0       |
| query_cache_type  | ON      |
+-------------------+---------+
4 rows in set (0.00 sec)

mysql> set global query_cache_size = 10*1024*1024;
Query OK, 0 rows affected (0.03 sec)

Tenemos query cache en el servidor, pero no está activado porque query_cache_size está a cero.

Vamos a emplear una caché de 10 megabytes. Este es un valor arbitrario que podré ir ajustando en el futuro, en base a la memoria física de la que dispongo (512 MB) y la carga del servidor. Al poner un valor al tamaño de la caché, esta se activa.

Ahora cargamos la portada para asegurarnos de que la caché funciona mirando las variables de estado asociadas:

mysql> show status like 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 30       |
| Qcache_inserts          | 30       |
| Qcache_hits             | 23       |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 1        |
| Qcache_free_memory      | 10383801 |
| Qcache_free_blocks      | 1        |
| Qcache_total_blocks     | 65       |
+-------------------------+----------+
8 rows in set (0.00 sec)

Perfecto, tenemos 30 peticiones en la caché (las que se realizan en este momento en la portada).

Además podemos ver que se han consumido tan solo 99,57 KB (resultado de restar Qcache_free_memory al valor de query_cache_size). Esto puede ya ponernos en la pista de un tamaño más adecuado para nuestra caché (un 20% de lo indicado sería más que suficiente, ya que la portada es la página más pesada y que más veces se accede en este servidor :D). En sitios más grandes y complejos que blackshell puede ser más difícil acertar con un buen valor para la caché.

Ahora repetimos el test.

Resultados con query cache

...
Document Length:	39981 bytes
Concurrency Level:	10
Time taken for tests:	0.10000 seconds
Complete requests:	204
Failed requests:	0
Total transferred:	8377560 bytes
HTML transferred:	8331336 bytes
Requests per second:	20.40
...

Parece que sí se nota el uso de la caché :). El servidor ha satisfecto un 62.03% más de peticiones por segundo (en total 78 peticiones más en los 10 segundos de la prueba), y sin que el administrador se caliente mucho la cabeza :P.

Para que la configuración de la caché se mantenga al volver a arrancar el servidor, he empleado la opción de mysqld_safe tal que: --query_cache_size=2097152, aunque hay otras formas de conseguir lo mismo.

Solo me queda recomendar la lectura de la documentación oficial, porque faltan algunos detalles (como resaltar la importancia de que se guarde la cadena de texto de la consulta... no es lo mismo a ojos de la caché select * from table que SELECT * from table).

Tampoco es un aumento de rendimiento espectacular, pero si tenemos memoria para dedicar a este tema, puede ser buena idea hacerlo. Aunque no siempre es recomendable, porque hay casos en los que no aporta beneficio, como cuando las tablas de modifican con una frecuencia muy alta (por segundo) y además hay que soportar la carga extra por gestionar la caché (las peticiones cacheadas se eliminan de la caché cuando se modifica la tabla de la que se extraen sus datos).

A ver si esta sorpresa me recuerda en el futuro que hay que mirar el changelog del software que usamos, para no perdernos mejoras tan interesantes como esta ;).

Actualización: Hasta que uno no se pone con estas cosas...

Hablando con Felipe y Jaime tomando café me he dado cuenta que hacía demasiadas consultas a la base de datos para la portada (unas 34 :o). He intentando optimizar el número de consultas usando left join, a costa de ganar en complejidad y esperando que la caché ayude.

He repetido el test:

...
Document Length:	36441 bytes
Concurrency Level:	10
Time taken for tests:	0.10007 seconds
Complete requests:	290
Failed requests:	0
Total transferred:	10631978 bytes
HTML transferred:	10569122 bytes
Requests per second:	28.98
...

La portada ahora es algo más pequeña, pero aún así creo que hay una mejora porque he conseguido bajar el número de consultas a solo 4 ;).

Anotación por Juan J. Martínez, clasificada en: mysql.

Los comentarios están cerrados: los comentarios se cierran automáticamente una vez pasados 30 días. Si quieres comentar algo acerca de la anotación, puedes hacerlo por e-mail.

Algunas anotaciones relacionadas: