MySQL Query Cache: configuración y optimización

«Query Cache» es una de las funcionalidades más interesantes que ofrece MySQL. A grandes rasgos, Query cache almacena en memoria el contenido y resultado de una consulta tipo SELECT, de modo que cuando un cliente vuelva a ejecutar la misma consulta (tiene que ser exactamente la misma), esta no tendrá que procesarse y se servirá directamente de la memoria sin necesidad de utilizar recursos de MySQL.

Según esta explicación, lo primero que viene a la mente es que la Query Cache será efectiva en bases de datos en las que las tablas no tienen demasiados cambios y predominan los SELECT frente a los INSERT. Vamos a explicar de forma sencilla como activar la caché y revisar sus resultados. Recomiendo leer la documentación de MySQL para quien quiera profundizar en el tema.

Lo primero que debemos hacer para activar la caché de consultas es indicarlo en el fichero de configuración my.cnf, a través de la directiva query_cache_type, que acepta tres valores:

Query Caché desactivada:

query_cache_type = 0

Query Caché activada:

query_cache_type = 1

Query Caché activada según demanda (especificandolo en la consulta (SELECT /* SQL_CACHE */):

query_cache_type = 2

En nuestro caso la activamos con el parámetro «1», podemos ver el estado actual desde la shell MySQL:

mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| ON                 |
+--------------------+
1 row in set (0.00 sec)

Una vez activada, tenemos que asignar un tamaño de memoria disponible para el almacenamiento de estas consultas. En la mayoría de los casos con un valor menor de 100M debería ser suficiente. En el fichero my.cnf tenemos:

query_cache_size=28M

Bien, con estos dos parámetros configurados ya empezaría a utilizarse la Query Cache en nuestro servidor MySQL. Pasado un tiempo debería llegar el momento de comprobar los resultados y verificar si está siendo efectiva. Para ello, revisamos el estado de Query Cache directamente desde la shell MySQL:

mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 2319      |
| Qcache_free_memory      | 7119040   |
| Qcache_hits             | 143073915 |
| Qcache_inserts          | 67474731  |
| Qcache_lowmem_prunes    | 9384509   |
| Qcache_not_cached       | 71545921  |
| Qcache_queries_in_cache | 7528      |
| Qcache_total_blocks     | 17974     |
+-------------------------+-----------+
8 rows in set (0.00 sec)

La explicación de cada variable es la siguiente:

• Qcache_free_blocks: El número de bloques de memoria disponibles en la query cache.
• Qcache_free_memory: La cantidad de memoria libre en la query cache.
• Qcache_hits: El número de aciertos de la query caché (consultas servidas desde la cache).
• Qcache_inserts: El número de consultas insertadas a la cache.
• Qcache_lowmem_prunes: El número de consultas eliminadas de la cache por falta de memoria disponible.
• Qcache_not_cached: Número de consultas que no se han podido cachear.
• Qcache_queries_in_cache: Número de consultas registradas en la cache.
• Qcache_total_blocks: Número de bloques en la cache.

De estos puntos, conviene tener en cuenta que si el valor de Qcache_free_blocks es muy alto significa que hay fragmentación en la cache y conviene ejecutar FLUSH QUERY CACHE para desfragmentar (esto no elimina la cache). La eficiencia de la cache se mide mediante esta fórmula: Qcache_hits/(Com_select+Qcache_hits), evidentemente un número alto de hits y un buen ratio respecto a los Com_select (número total de consultas SELECT ejecutadas) es lo óptimo. Podéis ver el número de selects globales del siguiente modo:

mysql> show global status like 'Com_select%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Com_select    | 71742078 |
+---------------+----------+
1 row in set (0.00 sec)

Para comenzar a trastear con la Query Cache de MySQL esto puede ser suficiente, para el que quiera profundizar puede hacerlo revisando toda la documentación disponible de MySQL del enlace anterior.