Entender el Query Cache en MySQL

Cuando salio MySQL 4.0 vino con algunas opciones muy interesantes como el Query Cache que por defecto está desactivado. Es conveniente saber un poco de él.

La idea detrás del query cache es bastante simple. Muchas aplicaciones, especialmente las aplicaciones basadas en la web, ejecutan consultas repetitivas a la Base de Datos. Estas consultas repetitivas consumen recursos de la Base de Datos y ralentizan toda tu aplicación. Y seguramente te harán sentirte mal.

Muchos desarrolladores cuando llegan a esta situación rapidamente realizan un cache en el lado del servidor.

Construir tu propio sistema de cache no es algo terriblemente dificil, pero toma tiempo y te obliga a escribir un código extra. Puedes aprovecharte del código que ya está escrito pero eso te obligará a tomar algunas decisiones. ¿Cuándo expira el cache? ¿Se acude al cache más recientemente usado (LRU) cache? ¿Se asigna tiempo a cada elemento cacheado? ¿Puede la aplicación olvidarse del cache?

El objetivo final es que tu aplicación recuerde cosas en vez de estar continuamente pidiéndolas.

O quizás, y esto es lo que te propongo, usa el query cache que viene de fabrica con MySQL.

Para empezar, el query cache de MySQL ya está construido. La cantidad de código que necesitarás en tu aplicación (si es que necesitas algunas) es mínima. Y ya que el cache opera en el servidor de la base de datos, te podrás aprovechar de él independientemente del lenguaje que uses: PHP, Java, Perl... a MySQL no le preocupa .

Debido a que MySQL controla el cache, no hace falta preocuparse sobre la duración del cache ya que en teoría se borraría cuando cambie los datos.

En la practiva, esto no es tran granular, pero podría llegar a serlo. En muchas aplicaciones, se te obligará a actuar así en el lado del servidor web.

El cache de MySQL como el resto de MySQL, es facil de configurar. Como verás dentro de poco es cuestión de establecer algunos parámetros de configuración y MySQL hace el resto.

Además, como esperas, el cache de MySQL es rápido, muy rápido

¿Cómo funciona?

El query cache en MySQL es un area de memoria donde se guardan las consultas previamente realizadas y las limpia cuando han cambiado los datos.

Cuando el query cache está activo, MySQL primero comprueba el cache para los resultados de una SELECT realizada anteriormente. En caso de encontrarlo lo usa y de lo contrario realiza la consulta con normalidad.

Si MySQL localiza el resultado en el cache, simplemente devuelve el resultado al cliente y no realiza ningun trabajo adicional. Los resultados del cache se guardan de la misma forma que los datos que se envían por la red con lo que existe muy poco consumo de tiempo de CPU.

Consultas que no sean de tipo SELECT como los UPDATE, INSERT, REPLACE, o DELETE también se ven afectados por el cache debido a que pueden cambiar datos.

En otras palabras, si un UPDATE se ejecuta contra la tabla customer, MySQL purga todos los resultados previos derivados de la tabla customer que estuvieran guardados con anterioridad.

Con tablas muy grandes, sería mejor que en vez de borrar todo lo cacheado se borrara unicamente los resultados que hubieran variado. Pero actuar de esa forma añade complejidad y el cache se supone que no es perfecto y debe guardar una proporcion entre rendimiento y velocidad.

Configuración

En el 99% de los casos, solo hay dos parámetros de configuración sobre los que preocuparse: query_cache_type y query_cache _size. Ambos se suelen configurar en la sección [mysqld] de tu archivo /etc/my.cnf .

Hay tres posibles valores para el query_cache_type: 0 desactiva el query cache; 1 activa el query cache; y 2 establece el query cache en el modo "on demand".

Poniendo el tipo a 1 significa que cada SELECT se considera por inclusión en el cache. Esto trabaja bien en muchos casos, pero en muchas aplicaciones sólo un pequeño subset de sus consultas son cacheables. En esos casos, es mejor olvidarse del cache y para eso puedes añadir a tus consultas el SQL_NO_CACHE:

   SELECT SQL_NO_CACHE * FROM my_table WHERE ...

O puedes establecerlo a 2. Al hacer eso MySQL sólo considera consultas para el cache si explicitamente las marcas. Haces eso indicando SQL_CACHE:

   SELECT SQL_CACHE * FROM my_table WHERE ...

El otro parámetro de configuración, query_cache_size, controla cuanta memoria de MySQL se debe establecer para el cache. El valor que especifiques es el límite superior. Empieza con un valor relativamente pequeño (8 o 16 MB) y aumentalo si lo estimas necesario.

Estadisticas y Optimización

Una vez que el cache está funcionando, sería bueno saber si lo está haciendo bien. Puedes examinar directamente las estadísticas del cache de MySQL mediante la instrucción SHOW STATUS:

mysql> show status like 'Qca%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 8        |
| Qcache_inserts          | 545875   |
| Qcache_hits             | 83951    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2343256  |
| Qcache_free_memory      | 33508248 |
| Qcache_free_blocks      | 1        |
| Qcache_total_blocks     | 18       |
+-------------------------+----------+
8 rows in set (0.00 sec)

Como puedes ver, MySQL (por lo menos en la versión 4.0.17) te da el track de ocho estadísticas que te dan una idea de lo que está sucediendo. Si te gustaría calcular un cache hit rate (o porcentaje) por ti mismo, necesitarás saber cuantas consultas SELECT ha manejado el servidor:

mysql> show status like 'Com_sel%';

+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 2889628 |
+---------------+---------+
1 row in set (0.01 sec)

Aquí , MySQL muestra que ha sido capaz de servir 83,951 de 2,889,628 consultas desde su cache. Esto es 1 de cada 34. Y con eso sabemos que sólo 545,875 de las 2.8 millones de consultas se han insertado en el query cache. Viendo la razón de una tan importante diferencia llegamos a la conclusión de que es debido a que el servidor se está ejecutando con el query_ cache_type establecido a 2.

mysql> show variables like 'query_cache%';

+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| query_cache_limit | 1048576  |
| query_cache_size  | 33554432 |
| query_cache_type  | DEMAND   |
+-------------------+----------+
3 rows in set (0.00 sec)

Y sin duda esto es.

El query_cache_limit es el tamaño del cache. La configuración que te muestra (1 MB) es el valor por defecto y sirve para muchas aplicaciones. Sin embargo, si tienes muchas consultas que devuelven una gran cantidad de datos, siéntete libre de aumentarlo.