Almacenamiento en caché en MySQL

7.1 Almacenamiento en caché en el lado de la base de datos

MySQL usa algoritmos altamente escalables cuando trabaja con tablas, por lo que MySQL puede ejecutarse incluso con pequeñas cantidades de memoria. Naturalmente, para un mejor rendimiento, necesita más RAM.

Para ver la configuración actual, conéctese a la base de datos

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

Primero, asegúrese de que el almacenamiento en caché esté habilitado. Variable:

• query_cache_typedebe estar ENCENDIDO (1) o DEMANDA (2)
• query_cache_limit- determina el tamaño máximo del resultado que entrará en el caché
• query_cache_sizeser diferente de cero. Al usar DEMAND, solo se almacenarán en caché las solicitudes que contengan la directiva.SQL_CACHE;
• query_cache_min_res_unitel tamaño mínimo del bloque de memoria asignado para almacenar los resultados de la consulta en caché. MySQL no almacena el caché en una gran parte de la memoria, sino que asigna bloques con un tamaño mínimo query_cache_min_res_unit(= 4 KB por defecto) a pedido. El último bloque de este tipo se trunca al tamaño de los datos y se libera la memoria restante.

El efecto del almacenamiento en caché es que cuando el servidor recibe una solicitud, busca si el hash de la solicitud está en el caché. Si el hash coincide, el servidor devuelve inmediatamente el resultado, sin analizar la solicitud, optimizar, etc. sobrecarga - acompañada por un mecanismo de almacenamiento en caché - navegar por el caché, escribir el resultado de la consulta en el caché, etc.

Y si tiene muchas solicitudes pequeñas en el caché, esto puede provocar la fragmentación de la memoria debido a una gran cantidad de bloques libres. Y esto, a su vez, provoca que las entradas en caché se eliminen por falta de memoria. En este caso, tiene sentido disminuir el valor de query_cache_min_res_unit. Si la mayoría de sus consultas generan grandes resultados, aumentar esta configuración puede mejorar el rendimiento.

Tratemos de evaluar el efecto. Vemos cómo cambian los contadores de aciertos de caché (Qcahe_hits), el número de solicitudes declaradas no válidas por falta de memoria (Qcache_lowmem_prunes), el número total de solicitudes del tipo SELECT (y solo se almacenan en caché):

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 Estado actual de la memoria caché

Para monitorear el caché de consultas se utiliza SHOW STATUS:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blocksmuestra cuántos bloques libres hay en el caché (disminuirá a medida que aumenten las solicitudes en caché);
• Qcache_total_blocks— el número de manzanas ocupadas;
• Qcache_free_memory- muestra la memoria libre "disponible" para el almacenamiento en caché;
• Qcache_hits- el número de solicitudes, cuyos resultados se tomaron de la memoria caché, sin acceder realmente a la base de datos;
• Qcache_inserts- el número de solicitudes que se agregaron al caché;
• Qcache_lowmem_prunes- la cantidad de solicitudes que se eliminaron del caché debido a la falta de memoria;
• Qcache_not_cached- el número de solicitudes que no se escribieron en la memoria caché debido al uso de funciones de gestión del tiempo, etc.;
• Qcache_queries_in_cache- el número de solicitudes que están en el caché.

Puede ver el número total de consultas SELECT:

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

7.3 Eficiencia de almacenamiento en caché

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

El valor actual se puede ver así:

SHOW VARIABLES LIKE 'query_cache_size';

De nuevo surge la pregunta: ¿cómo elegir un valor adecuado?query_cache_size?

Esto ayudará Qcache_lowmem_prunes. Esta variable almacena la cantidad de solicitudes que se han eliminado del caché debido a la necesidad de almacenar en caché nuevas solicitudes. Es necesario esforzarse por lograr un tamaño de caché en el que Qcache_lowmem_prunesaumente solo ligeramente. Para ello, se recomienda comparar la diferencia de valores Qcache_lowmem_prunespor hora y la cantidad de solicitudes recibidas por mysql en la misma hora.

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (volumen de tráfico en 10 minutos) * 1,2 "

Esto almacenará en caché las solicitudes durante 10 minutos y otorgará un 20 % de memoria adicional para la fragmentación de caché y una reserva adicional de almacenamiento en caché.

Puede calcular el número y el volumen promedio de una respuesta a una solicitud utilizando las variables Bytes_sent , respectivamente

Y así query_cache_sizeaumentamos los valores, después de lo cual debe prestar atención a los valores Qcache_total_blocksy Qcache_free_blocks. Qcache_queries_in_cacheMySQL almacena el caché en bloques. Para 1 solicitud, se necesitan 2 bloques: uno para el texto de la solicitud en sí, el segundo para el resultado.

Si consideramos la tabla a partir del valorQcache%

Número total de bloques de cachéQcache_total_blocks – 28

La solicitud 6 está en caché ahora, lo que significa que 6 * 2 = 12 bloques están ocupados

Bloques gratis Qcache_free_blocks – 10. Cuanto más inactiva Qcache_free_blocks, mayor es el grado de "fragmentación" de la memoria caché.

Si la mayoría de las consultas tienen una pequeña cantidad de datos resultantes, vale la pena reducir el tamaño mínimo del bloque de caché query_cache_min_res_unit, que es de 4 KB de forma predeterminada.

Si la mayoría de las solicitudes devuelven una gran cantidad de datos, vale la pena aumentar el tamaño del bloque de caché.

Lo principal es lograr un valor mínimo Qcache_free_blocks.

Si el contador Qcache_not_cachedes grande, puede intentar aumentar la variable query_cache_limit; le permitirá aumentar el límite y almacenar en caché los resultados de las consultas que "no encajan".

Las siguientes variables de configuración son responsables del uso de la caché de consultas:

• query_cache_size— el tamaño de la caché de consultas. query_cache_size = 0deshabilita el uso de caché;
• query_cache_limit- el tamaño de la muestra máxima almacenada en el caché;
• query_cache_wlock_invalidate- determina si los datos se tomarán del caché si la tabla a la que pertenecen está bloqueada para lectura.
=

Para habilitar el almacenamiento en caché de consultas mysql, simplemente agregue las siguientes líneas a my.cnf (Sección [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Y reiniciar el servicio.

7.4 Donde no se usa el caché

No en caché:

• Solicitudes deSQL_NO_CACHE
• Consultas preparadas(Prepared statements);
• Consultas que son subconsultas de la consulta externa;
• Consultas dentro de procedimientos y funciones almacenados;
• Consultas que utilizan funciones:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()con un argumento, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()sin argumentos, USER (),UUID ();

• Consultas utilizando funciones almacenadas, variables de usuario o tablas de referencia en bases de datos del sistema mysql o INFORMACION_ESQUEMA;
• • Solicitudes en los siguientes formularios:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Consultas con tablas temporales o sin usar tablas;
• Solicitudes que generan advertencias (advertencias);

Puede desfragmentar el caché con el comando:

mysql>flush query cache;

Borrar - comando:

mysql>flush query cache;

El más importante

Nunca trabaje en producción con la configuración predeterminada. Esto dará como resultado que la mayoría de los recursos del servidor no se utilicen. El ajuste adecuado de MySQL puede mejorar varias veces el rendimiento de la base de datos. Esto no solo acelerará la aplicación, sino que también hará frente a una carga pesada.