5.12. La caché de consultas de MySQL

MySQL 5.0

5.12. La caché de consultas de MySQL

MySQL 5.0 Server proporciona una query cache. Cuando se usa, la query cache almacena el texto de una consulta SELECT junto con el resultado que se le envió al cliente. Si se recibe una consulta idéntica posteriormente, el servidor devuelve el resultado de la caché de consultas en lugar de parsear y ejecutar la consulta de nuevo.

La caché de consultas es muy útil en un entorno donde tiene tablas que no cambian frecuentemente y donde el servidor recibe muchas consultas idénticas. Esta es la típica situación de muchos servidores Web que generan muchas páginas dinámicas basadas en contenido de base de datos.

Nota: La caché de consultas no devuelve datos antiguos. Cuando las tablas se modifican, cualquier entrada relevante en la caché de consultas se eliminia.

Nota: La caché de consultas no funciona en un entorno donde tiene muchos servidores mysqld actualizando las mismas tablas MyISAM.

Nota: La caché de consultas no se usa para comandos preparados en la parte del servidor. Si está usando este tipo de comandos preparados, considere que no se beneficiarán de la caché de consultas. Consulte Sección 24.3.4, “Sentencias preparadas de la API C”.

A continuación algunos datos de rendimiento de la caché de consultas. Estos resultados se generaron con el MySQL benchmark suite en un Linux Alpha 2 x 500MHz con 2GB RAM y 64MB de caché de consultas.

Si todas las consultas que está ejecutando son simples (tales como seleccionar un registro de una tabla con un registro), pero difierente de forma que las consultas no pueden cachearse, la pérdida de rendimiento por tener activa la caché de consultas es del 13%. Este puede considerarse el peor escenario posible. En el mundo real, las consultas suelen ser mucho más complicadas, así que la pérdida de rendimiento es considerablemente menor.
Las búsquedas para un registro en una tabla de un registro són un 238% más rápidas con la caché de consultas que sin ella. Esto puede considerarse como la mínima mejora esperada para una consulta que se cachea.

Para desactivar la caché de consultas al arrancar el servidor, ponga la variable de sistema query_cache_size a 0. Al desactivar el código de caché de consultas, no hay una pérdida de rendimiento evidente. Las capacidades de la caché de consultas pueden quitarse totalmente del servidor usando la opción --without-query-cache con configure al compilar MySQL.

5.12.1. Cómo opera la caché de consultas

Esta sección describe cómo funciona la caché de consultas cuando está operacional. Sección 5.12.3, “Configuración de la caché de consultas” describe cómo controlar si está o no operacional.

Las consultas se comparan antes de parsearla, así que las siguientes dos consultas se consideran diferentes por la caché de consultas:

SELECT * FROM tbl_name
Select * from tbl_name

Las consultas deben ser exactamenten las mismas (byte a byte) para se consideradas idénticas. Además, las consultas que son idénticas pueden tratarse como diferentes por otras razones. Las consultas que usan distintas bases de datos, distintas versiones de protocolo, o distintos conjuntos de carácteres se consideran distintas consultas y se cachean por separado.

Antes que una consulta se guarde en la cache de consultas, MySQL comprueba que el usuario tenga permisos de SELECT para todas las bases de datos y tablas involucradas. Si no es el caso, el resultado cacheado no se usa.

Si un resultado de consulta se retorna desde la caché de consultas, el servidor incrementa la variable de estado Qcache_hits, no Com_select. Consulte Sección 5.12.4, “Estado y mantenimiento de la caché de consultas”.

Si una tabla cambia, entonces todas las consultas cacheadas que usen esa tabla pasan a ser inválidas y se eliminan de la caché.Esto incluye consultas que usen tablas MERGE que mapeen las tablas cambiadas. Una tabla puede cambiarse por varios tipos de comandos, tales como INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, o DROP DATABASE.

Las tablas transaccionales InnoDB que se han cambiado se invalidan cuando se realiza un COMMIT.

En MySQL 5.0, la caché de consultas también funciona dentro de transacciones cuando se usa tablas InnoDB ,haciendo uso del número de versión para detectar si sus contenidos han cambiado.

En MySQL 5.0, las consultas generadas por vistas se cachean. Un problema con los resultados en la caché de consultas generados por vistas que no se invalidaban apropiadamente tras algunas operaciones en dichas vistas se resolvión en MySQL 5.0.3. Consulte Sección C.1.9, “Cambios en la entrega 5.0.3 (23 Mar 2005: Beta)”.

Antes de MySQL 5.0, una consulta que empezase con un comentario podía cachearse, pero no podía obtenerse de la caché. Este problema está resuelto en MySQL 5.0.

La caché de consultas funciona para consultas del tipo SELECT SQL_CALC_FOUND_ROWS ... y SELECT FOUND_ROWS(). FOUND_ROWS() devuelve el valor correcto incluso si la consulta precedente se obtuvo de la cache debido a que el número de registros encontrados también se almacena en la caché.

Una consulta no puede cachearse si contiene cualquiera de las siguientes funciones:

`BENCHMARK()`	`CONNECTION_ID()`	`CURDATE()`
`CURRENT_DATE()`	`CURRENT_TIME()`	`CURRENT_TIMESTAMP()`
`CURTIME()`	`DATABASE()`	`ENCRYPT()` con un parámetro
`FOUND_ROWS()`	`GET_LOCK()`	`LAST_INSERT_ID()`
`LOAD_FILE()`	`MASTER_POS_WAIT()`	`NOW()`
`RAND()`	`RELEASE_LOCK()`	`SYSDATE()`
`UNIX_TIMESTAMP()` sin parámetros	`USER()`

Una consulta tampoco se cachea bajo las siguientes condiciones:

Se refiere a funciones definidas por el usuario (UDFs).
Se refiere a variables de usuario.
Se refiere a las tablas en la base de datos del sistema mysql .
Es cualquiera de las siguientes formas:
```
SELECT ... IN SHARE MODE
SELECT ... FOR UPDATE
SELECT ... INTO OUTFILE ...
SELECT ... INTO DUMPFILE ...
SELECT * FROM ... WHERE autoincrement_col IS NULL
```
La última forma no se cachea ya que se usa como solución de ODBC para obtener el último ID insertado. Consulte Sección 25.1.14.1, “Cómo obtener el valor de una columna AUTO_INCREMENT en ODBC”.
Se usó como comando preparado, incluso sin emplear marcadores. Por ejemplo, la consulta usada aquí:
```
char *my_sql_stmt = "SELECT a, b FROM table_c";
/* ... */
mysql_stmt_prepare(stmt, my_sql_stmt, strlen(my_sql_stmt));
```
no se cachea. Consulte Sección 24.3.4, “Sentencias preparadas de la API C”.
Usa tablas TEMPORARY.
No usa ninguna tabla.
El usuario tiene permisos a nivel de columan para cualquiera de las tablas involucradas.

5.12.2. Opciones de `SELECT` para la caché de consultas

Dos opciones relacionadas con la caché de consultas pueden especificarse en los comandos SELECT:

SQL_CACHE

El resultado de la consulta se cachea si el valor de la variable de sistema query_cache_type es ON o DEMAND.
SQL_NO_CACHE

La consulta resultado no se cachea.

Ejemplos:

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

5.12.3. Configuración de la caché de consultas

La variable de sistema have_query_cache indica si la caché de consultas está disponible:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Cuando se usa un binario MySQL 5.0 estándard, este valor siempre es YES, incluso si el cacheo de consultas está desactivado.

Muchas otras variables de sistema controlan las operaciones de la caché de consultas. Estas pueden especificarse en un fichero de opciones o en la línea de comandos al arrancar mysqld. Las variables de sistema de la caché de consultas todas tienen nombres que empiezan con query_cache_. Se describen brevemente en Sección 5.3.3, “Variables de sistema del servidor”, con información adicional de configuracion dada aquí.

Para especificar el tamaño de la caché de consulta, inicialice la variable de sistema query_cache_size. Ponerla a 0 desactiva la caché de consultas. El tamaño por defecto de la caché es 0; esto es, que está desactivada.

Si el tamaño de la caché de consultas es mayor que 0, la variable query_cache_type influye en su funcionamiento. Esta variable puede tener los siguientes valores:

Un valor de 0 o OFF evita cachear o recibir valores cacheados.
Un valor de 1 o ON permite el cacheo excepto para aquellos comandos que empiecen con SELECT SQL_NO_CACHE.
Un valor de 2 o DEMAND provoca el cacheo de sólo los comandos que empiecen con SELECT SQL_CACHE.

Inicializar con el valor GLOBAL la variable query_cache_type determina el comportamiento de la caché de consultas para todos los clientes que se conecten tras el cambio. Clientes individuales que controlan el comportamiento de la caché para su propia conexión meidante el valor SESSION de query_cache_type. Por ejemplo, un cliente puede desactivar el uso de la caché de consultas para sus propias consultas así:

mysql> SET SESSION query_cache_type = OFF;

Para controlar el tamaño máximo de resultados de consultas individuales que pueden cachearse, inicialice la variable query_cache_limit variable. El valor por defecto es 1MB.

El resultado de una consulta (los datos enviados al cliente) se guardan en la caché de consulta durante la recepción del resultado. Por lo tanto los datos normalmente no se guardan en un gran paquete. La caché de consulta prepara bloques para guardar estos datos bajo demanda, así que cuando se llena un bloque, se prepara un nuevo bloque. Como la reserva de memoria es una operación costosa (lenta), la caché de consultas reserva bloques con un tamaño mínimo dado por la variable de sistema query_cache_min_res_unit. Cuando se ejecuta una consulta, el último bloque de resultados se ajusta al tamaño actual para liberar la memoria no usada. En función del tipo de consulta que ejecute el servidor, puede encontrar útil cambiar el variable de query_cache_min_res_unit:

El valor por defecto de query_cache_min_res_unit es 4KB. Debe ser un valor adecuado para la mayoría de los casos.
Si tiene muchas consultas con resultados pequeños, el tamaño de bloque por defecto puede llevar a fragmentación de memoria, como se indica con un alto número de bloques libres. La fragmentación puede forzar a la caché de consultas a borrar consultas de la caché debido a la falta de memoria. En este caso, debe decrementar el valor de query_cache_min_res_unit. El número de bloques libres y de consultas borradas debido a falta de espacio se da en las variables Qcache_free_blocks y Qcache_lowmem_prunes.
Si la mayoría de las consultas tienen resultados grandes (compruebe las variables de estado Qcache_total_blocks y Qcache_queries_in_cache ), puede incrementar el rendimiento incrementando query_cache_min_res_unit. Sin embargo, tenga cuidado de no hacerlo demasiado grandes (consulte el punto anterior).

5.12.4. Estado y mantenimiento de la caché de consultas

Puede comprobar si la caché de consultas está presente en su servidor MySQL con el siguiente comando:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Puede defragmentar la caché de consultas para mejor uso de esta memoria con el comando FLUSH QUERY CACHE . El comando no elimina ninguna consulta de la caché.

El comando RESET QUERY CACHE elimina todos los resultados de consultas de la caché de consultas. El comando FLUSH TABLES también lo hace.

Para monitorizar el rendimiento de la caché de consultas, use SHOW STATUS para ver las variables de estado de la caché:

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 36     |
| Qcache_free_memory      | 138488 |
| Qcache_hits             | 79570  |
| Qcache_inserts          | 27087  |
| Qcache_lowmem_prunes    | 3114   |
| Qcache_not_cached       | 22989  |
| Qcache_queries_in_cache | 415    |
| Qcache_total_blocks     | 912    |
+-------------------------+--------+

Las descripicones de estas varibles se dan e Sección 5.3.4, “Variables de estado del servidor”. Algnos de sus usos se describen aquí.

El número total de consultas SELECT es igual a:

  Com_select
+ Qcache_hits
+ consultas con errores detectadas por el parser

El valor Com_select es igual a:

  Qcache_inserts
+ Qcache_not_cached
+ consultas con errores encontrados durante la comprobación de columnas/permisos

La caché de consultas usa bloques del tamaño de las variables, así Qcache_total_blocks y Qcache_free_blocks puede indicar la fragementación de memoria de la caché de consultas. Tras FLUSH QUERY CACHE, sólo queda un bloque de memoria libre.

Cada consulta cacheada requiere como mínimo dos bloques (uno para el texto de la consulta y otro o más para el resultado de la misma). Además, cada tabla que se usa por una consulta requiere un bloque. Sin embargo, si dos o más consultas usan la misma tabla, sólo un bloque necesita ser reservado.

La información proporcionada por la variable de estado Qcache_lowmem_prunes puede usarse para ayudarle a ajustar el tamaó de la caché de consutlas. Cuenta el número de consultas que se han eliminado de la caché para liberar memoria para cachear nuevas consultas. La caché de consultas usa una estrategia de último resultado usado (LRU) para decidir qué consultas eliminar de la caché. Información para ajustar las variables se da en Sección 5.12.3, “Configuración de la caché de consultas”.

previous page start next page

5.12. La caché de consultas de MySQL

MySQL 5.0