MariaDB / El cache de query

Bienvenidos sean a este post, hoy hablaremos sobre este cache particular.

Este cache es el encargado de almacenar todos los queries ejecutados en el servidor con sus respectivos resultados, si el usuario solicita un query y este existe en el cache se le devuelve inmediatamente y por supuesto se chequea si el usuario tiene los permisos suficientes, esta activo de manera predeterminada, pero es recomendable que el DBA verifique la performance de las aplicaciones en el servidor dado que este cache en algunas ocasiones puede bajar la misma por lo tanto debe decidir si lo mantiene activo o no, igualmente la buena practica es que este chequeo se repita periodicamente si se cambian las aplicaciones, aunque este cache tiene la posibilidad de ser activado «on demand», es decir que puede estar desactivado y ser activado con los queries que lo solicitan, si los datos cambian continuamente es muy dificil que se mejore la performance, por esta razon si los datos rara vez se cambian (p.e por la noche o mejor aun una vez por semana) una buena practica es ejecutar el query una vez efectuado el cambio para cachearlos.

Este cache es protegido por un mutex (exclusion mutua), este es un tipo de bloqueo usado para asegurarnos que dos threads no pueden acceder al mismo recurso al mismo tiempo, pero cualquier cambio en la estructura de las tablas o los datos invalidan los queries almacenados de dichas tablas, si bien muchos queries almacenados pueden tener multiples resultados el mas minimo cambio en alguno de ellos puede invalidarlos y este query invalidado puede ser ejecutado por multiples conexiones al mismo tiempo para luego ser re-ejecutado por multiples conexiones y cacheado de vuelta al mismo tiempo, a este problema se lo denomina Tormenta de perdidas (miss storm) o estampida de cache (cache stampede).

Un query del usuario debe coincidir exactamente con el almacenado en el cache, diferencias entre mayusculas y minusculas, espacios y de manera predeterminada los comentarios pueden hacer dos queries diferentes, veamos unos ejemplos:

SELECT * FROM t;
select * from 1;

SELECT *   FROM t;
SELECT * FROM t;

SELECT * FROM t;
SELECT * FROM t/* */;

Tambien los queries solo coinciden si tienen la misma base predeterminada, conjunto de caracteres predeterminados, SQL_MODE y version de protocolo, veamos algunos queries que no seran cacheados:

• si generan avisos
• si son ejecutados desde un programa almacenado
• si usan tablas temporales
• si usan variables creadas por el usuario
• si contienen funciones no deterministas, funciones almacenadas o funciones definidas por el usuario
• si usan tablas del sistema
• si utilizan algunas de estas opciones: INTO OUTFILE, INTO DUMPFILE, LOCK IN SHARE MODE, FOR UPDATE
• si no usan tablas
• si referencia a una tabla donde la cuenta tiene privilegios a nivel columna
• si la tabla envuelve a motores de almacenamiento especiales (p.e SPIDER)

A continuacion veremos algunos comandos para trabajar con el cache, vamos a suponer que tenemos el cache activado y queremos que el query no sea agregado, para ello usaremos la siguiente opcion:

SELECT SQL_NO_CACHE <el resto del query>

En cambio si tenemos el cache desactivado pero queremos que el query se almacene debemos usar la siguiente opcion:

SELECT SQL_CACHE <el resto del query>

Si todos los cambios hacen que nuestra memoria quede fragmentada para defragmentarla debemos usar el siguiente comando:

FLUSH QUERY CACHE;

Y por ultimo puede suceder que el cache se llene con queries inutiles o innecesarios, para poder limpiarlo o resetearlo podemos usar el siguiente comando:

RESET QUERY CACHE;

Hasta ahora hemos visto algunos conceptos basicos y como poder tener algunos acciones sobre este cache pero ahora hablaremos sobre como configurarlo, para ello tenemos las siguiente variables:

• query_cache_type, esta variable activa o desactiva al cache, siendo el valor 0 para desactivarlo, 1 para activarlo y 2 para usar en demanda
• query_cache_size, esta variable establece el tamaño del cache, su valor predeterminado es de 1024 (1 MB), si se pasa 0 se desactivara pero se recomienda usar la variable anterior para esto y por ultimo el valor debe ser siempre multiplo 1024
• query_cache_alloc_block_size, establece el tamaño de los bloques de memoria que usaremos en el cache, un valor alto evita la fragmentacion pero puede caer en un desperdicio de memoria.
• query_cache_limit, establece la capacidad maxima de queries almacenados
• query_cache_strip_comments, esta variable determina si se quitan los comentarios o no

La ultima variable si recibe el valor de ON permite que el cache almacene los queries sin los comentarios permitiendo que dos queries identicos pero con comentarios diferentes tengan una coincidencia, aunque esto es innecesario si el cliente usado o la API se encargan de esta tarea, para obtener informacion basica sobre este cache podemos usar el siguiente comando:

MariaDB [(none)]> show status like 'qcache%';

Lo cual nos devolvera la siguiente informacion:

MariaDB [(none)]> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16759640 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 48       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.002 sec)

MariaDB [(none)]>

Veamos que representa cada variable:

• Qcache_free_blocks, devuelve la cantidad de bloques de memoria libres
• Qcache_free_memory, devuelve la memoria disponible
• Qcache_hits, es la cantidad de queries que coincidieron
• Qcache_inserts, es el numero de queries que se agregaron al cache
• Qcache_lowmem_prunes, es el numero de queries borrados del cache
• Qcache_not_cached, es el numero de queries que no se pudieron almacenar
• Qcache_queries_in_cache, es el total actual de numero de queries en el cache
• Qcache_total_blocks, es el numero total de bloques de memoria

Si bien con esto tenemos la informacion basica del cache tambien podemos habilitar el plug-in, dado que esta instalado pero no activo, para obtener mas y mejor informacion sobre este cache debemos usar el siguiente comando:

MariaDB [(none)]> install soname 'query_cache_info';

Una vez iinstalado nos agregara la tabla QUERY_CACHE_INFO a la base information_schema, veamos las columnas con la informacion que nos interesa:

• STATEMENT_SCHEMA, la base seleccionada para el query
• STATEMENT_TEXT, el query almacenado
• RESULT_BLOCKS_COUNT, los numeros de bloques usados para almacenar el resultado
• RESULT_BLOCKS_SIZE, el tamaño del bloque de memoria
• RESULT_BLOCKS_SIZE_USED, la cantidad de memoria usada para almacenar los resultados

Hay mas columnas pero estas seran las que nos brindaran la verdadera informacion mas util, vamos a hacer un ejemplo para verlo en accion, primero reseteemos el cache:

MariaDB [(none)]> reset query cache;

Volvamos a ver la informacion basica de nuestro cache:

MariaDB [(none)]> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16759640 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 48       |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.002 sec)

MariaDB [(none)]>

Ahora hagamos un query sobre la tabla que agregamos recientemente:

MariaDB [(none)]> select * from information_schema.query_cache_info;
Empty set (0.003 sec)

MariaDB [(none)]>

En este caso al estar vacio no nos devolvio ningun resultado, ahora hagamos un query simple para almacenarlo:

MariaDB [(none)]> select count(*) from base1.tabla3;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.003 sec)

MariaDB [(none)]>

Nota: 
Yo use esta tabla pero no es lo importante sino que ejecuten el count sobre alguna para que sea almacenada.

Ahora volvemos a hacer la busqueda basica para ver el resultado:

MariaDB [(none)]> show status like 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16758104 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 49       |
| Qcache_queries_in_cache | 1        |
| Qcache_total_blocks     | 4        |
+-------------------------+----------+
8 rows in set (0.003 sec)

MariaDB [(none)]>

Observen como ahora tenemos un query almacenado, probemos haciendo un query sobre la tabla para ver que nos devuelve:

MariaDB [(none)]> select * from information_schema.query_cache_info\G
*************************** 1. row ***************************
       STATEMENT_SCHEMA: 
         STATEMENT_TEXT: select count(*) from base1.tabla3
    RESULT_BLOCKS_COUNT: 1
     RESULT_BLOCKS_SIZE: 512
RESULT_BLOCKS_SIZE_USED: 127
                  LIMIT: -1
        MAX_SORT_LENGTH: 1024
GROUP_CONCAT_MAX_LENGTH: 1048576
   CHARACTER_SET_CLIENT: utf8mb4
   CHARACTER_SET_RESULT: utf8mb4
              COLLATION: utf8mb4_general_ci
               TIMEZONE: SYSTEM
    DEFAULT_WEEK_FORMAT: 0
DIV_PRECISION_INCREMENT: 4
               SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
          LC_TIME_NAMES: en_US
       CLIENT_LONG_FLAG: 1
     CLIENT_PROTOCOL_41: 1
          PROTOCOL_TYPE: 0
    MORE_RESULTS_EXISTS: 0
               IN_TRANS: 0
             AUTOCOMMIT: 1
          PACKET_NUMBER: 1
                   HITS: 0
1 row in set (0.005 sec)

MariaDB [(none)]>

Como pueden ver la informacion es mucho mas precisa que la anterior la cual nos puede ser mucho mas util a la hora de analizar de como interfiere o beneficia a la performance de nuestro equipo.

En resumen, hoy hemos visto el cache de query,, que es, para que se usa, como nos beneficia, como nos perjudica, como podemos evitarlo, como analizarlo, y como habilitar el plug-in para obtener mas informacion, espero les haya sido de utilidad sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.