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.

9 comentarios en “MySQL Query Cache: configuración y optimización

  1. Una pregunta, suponiendo que activamos esto, si se produce lo siguiente:

    SELECT …. FROM unatabla ….

    INSERT/UPDATE unatabla…

    SELECT …. FROM unatabla

    está última consulta, obtendría los datos correctos, es decir los datos actualizados luego del insert/update o traería los datos viejos ?

    Saludos

  2. Wow, por ende no habría razón para no tener activado esto!!! que desventajas tiene sino?

    Viste la otra consulta que te hice por el formulario de contacto?

  3. El tema es el siguiente, el otro día subí a mi vps un shell, la c999 más precisamente. Quedé sorprendido al ver que podía visualizar todo el root de mi vps. Lo curioso era que tenía el open_basedir activado. El tema es que cuando php corre como suphp, tenés que hacer una configuración de php.ini específica para cada usuario. Estaría bueno si te interesa y si tenés tiempo que hicieras un post acerca de como automatizar esto, utilizando hooks del cpanel y un script que active el open_basedir para cada usuario que se crea en el vps y también que bloquee la edición de esta configuración. En webhostingtalk cree un thread para esto, podes verlo aquí: http://www.webhostingtalk.com/showthread.php?s=d7571d6cb6803122f3000cfc8dc8bef8&p=7371664 Si querés escribime un mail para darme una mano ya que soy un novato total en esto. Un abrazo desde uruguay!

  4. mmmm, entiendo que podrás visualizar todo lo que tenga permisos de lectura para “otros”, pero por ejemplo si usas suPHP no podrás entrar en las /home/usuario del resto de cuentas del servidor.

    Por ejemplo, la mayor parte de ficheros de /etc/ requieren permiso de lectura para otros y eso no se puede modificar, de este modo cualquiera puede leer por ejemplo el fichero passwd. Quizás una opción, aunque un poco más drástica es deshabilitar ciertas funciones de PHP que pueden provocarte este problema (exec, system, etc).

    No sabía lo que me comentas de open_basedir, si tengo tiempo lo revisaré para ver si se puede automatizar (realmente poder se puede, únicamente es crear el script).

    Saludos

  5. Exacto es tal cual lo dices, si bien no soy un proveedor de hosting masivo, solo le vendo a conocidos, no me gusta sentirme inseguro. Deshabilitar las funciones podría ser una solución, de hecho lo pensé, pero también estaría perdiendo algunas funcionalidades. Sin dudas la mejor opción es automatizar un script para encerrar al usuario en su directorio unicamente pero lamentablemente no tengo muy claro como hacerlo jejee. Un abrazo y gracias!

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *