Como optimizar MySQL cacheando queries

El caché de consultas es una característica prominente de MySQL que acelera la recuperación de datos de una base de datos. Esto se logra almacenando las sentencias SELECT de MySQL junto con el conjunto de registros recuperados en la memoria, luego, si un cliente solicita consultas idénticas, puede servir los datos más rápido sin ejecutar comandos nuevamente desde la base de datos.

En este tutorial, primero configurará MySQL sin caché de consultas y ejecutará consultas para ver qué tan rápido se ejecutan. Luego, configurará la memoria caché de consultas y probará su servidor MySQL habilitado para mostrar la diferencia de rendimiento.

Comprobación de la disponibilidad de caché

Para comprobar si nuestra instalación de MySQL tiene cache de consultas disponible, debemos correr la siguiente consulta:

show variables like 'have_query_cache';

Y si hay disponibilidad el resultado será el siguiente:

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.01 sec)

Para revisar la configuración del cache y las variables que la constituyen corremos el siguiente query:

show variables like 'query_cache_%' ;

Y el resultado sería mas o menos el siguiente:

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | OFF      |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

• query_cache_limit determina el tamaño máximo de resultados de consultas individuales que se pueden almacenar en caché.
• query_cache_min_res_unit La cantidad mínima de memoria asignada a cada bloque.
• query_cache_size controla la cantidad total de memoria asignada a la caché de consulta. Si el valor se establece en cero, significa que la memoria caché de consulta está deshabilitada.
• query_cache_type Establecer este valor en 0 evita el almacenamiento en caché o la recuperación de consultas en caché. También puede establecerlo en 1 para habilitar el almacenamiento en caché para todas las consultas excepto las que comienzan con la instrucción SELECT SQL_NO_CACHE. Un valor de 2 le dice a MySQL que solo cachee las consultas que comienzan con el comando SELECT SQL_CACHE.
• query_cache_wlock_invalidate controla si MySQL debe recuperar los resultados de la caché si la tabla utilizada en la consulta está bloqueada. El valor predeterminado es OFF.

Probando una consulta en caché

Primero encendemos el perfilador para medir el tiempo que toma cada consulta.

SET profiling = 1;

Supongamos que tenemos una tabla de clientes, y corremos el siguiente query:

Select * from clientes;

Y obtenemos la siguiente salida:

+-------------+---------------+
| id_cliente  | nombre_cliente|
+-------------+---------------+
|           1 | JANE DOE      |
|           2 | JANIE DOE     |
|           3 | JOHN ROE      |
|           4 | MARY ROE      |
|           5 | RICHARD ROE   |
|           6 | JOHNNY DOE    |
|           7 | JOHN SMITH    |
|           8 | JOE BLOGGS    |
|           9 | JANE POE      |
|          10 | MARK MOE      |
+-------------+---------------+
10 rows in set (0.00 sec)

Ahora hay que revisar los tiempos de ejecución de la consulta:

SHOW PROFILES;

Obtendrá una salida similar a la siguiente:

+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00044075 | Select * from clientes  |
+----------+------------+-------------------------+
1 row in set, 1 warning (0.00 sec)

Ahora corremos el mismo query pero primero habilitamos el cache editando la configuración en el archivo /etc/mysql/my.cnf añadiendo lo siguiente al final de la sección mysqld

[mysqld]
query_cache_type=1
query_cache_size = 10M
query_cache_limit=256K

Y procedemos a reiniciar el servidor de MySQL

sudo systemctl restart mysql

Ahora corremos el mismo query que antes y luego volvemos a revisar los perfiles para obtener los siguientes resultados:

+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00049250 | Select * from clientes  |
|        2 | 0.00026000 | Select * from clientes  |
+----------+------------+-------------------------+
2 rows in set, 1 warning (0.00 sec)

Y vemos que en la consulta 2, con el caché habilitado hemos reducido el tiempo a la mitad!

Conclusión

En comparación con los datos leídos desde el disco, los datos almacenados en caché de la RAM (memoria de acceso aleatorio) tienen un tiempo de acceso más corto, lo que reduce la latencia y mejora las operaciones de entrada / salida (E / S). Como ejemplo, para un sitio de WordPress o un portal de comercio electrónico con llamadas de alta lectura y cambios de datos poco frecuentes, el caché de consultas puede mejorar drásticamente el rendimiento del servidor de base de datos y hacerlo más escalable.