Mise en cache dans MySQL

7.1 Mise en cache côté DB

MySQL utilise des algorithmes hautement évolutifs lorsqu'il travaille avec des tables, de sorte que MySQL peut fonctionner même avec de petites quantités de mémoire. Naturellement, pour de meilleures performances, vous avez besoin de plus de RAM.

Pour afficher les paramètres actuels, connectez-vous à la base de données

#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)

Tout d'abord, assurez-vous que la mise en cache est activée. Variable:

• query_cache_typedoit être ON (1) ou DEMAND (2)
• query_cache_limit- détermine la taille maximale du résultat qui entrera dans le cache
• query_cache_sizeêtre différent de zéro. Lors de l'utilisation de DEMAND, seules les demandes contenant la directive seront mises en cacheSQL_CACHE;
• query_cache_min_res_unitla taille minimale du bloc de mémoire alloué pour stocker les résultats de la requête en cache. MySQL ne stocke pas le cache dans un gros bloc de mémoire, mais alloue des blocs d'une taille minimale query_cache_min_res_unit(= 4 Ko par défaut) à la demande. Le dernier bloc de ce type est tronqué à la taille des données et la mémoire restante est libérée.

L'effet de la mise en cache est que lorsque le serveur reçoit une demande, il regarde si le hachage de la demande est dans le cache. Si le hachage correspond - le serveur renvoie immédiatement le résultat - sans analyser la requête, optimiser, etc. surcharge - accompagnée d'un mécanisme de mise en cache - parcourir le cache, écrire le résultat de la requête dans le cache, etc.

Et si vous avez beaucoup de petites requêtes dans le cache, cela peut entraîner une fragmentation de la mémoire en raison d'un grand nombre de blocs libres. Et cela, à son tour, entraîne la suppression des entrées mises en cache en raison d'un manque de mémoire. Dans ce cas, il est logique de diminuer la valeur de query_cache_min_res_unit. Si la plupart de vos requêtes produisent des résultats volumineux, l'augmentation de ce paramètre peut améliorer les performances.

Essayons d'évaluer l'effet. On regarde comment évoluent les compteurs d'accès au cache (Qcahe_hits), le nombre de requêtes déclarées invalides par manque de mémoire (Qcache_lowmem_prunes), le nombre total de requêtes de type SELECT (et elles seules sont mises en cache) :

#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 État actuel du cache

Pour surveiller le cache des requêtes on utilise 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_blocksmontre combien de blocs libres il y a dans le cache (diminuera à mesure que les demandes mises en cache augmenteront);
• Qcache_total_blocks— le nombre de blocs occupés ;
• Qcache_free_memory- affiche la mémoire "disponible" libre pour la mise en cache ;
• Qcache_hits- le nombre de requêtes dont les résultats ont été extraits du cache, sans accéder réellement à la base de données ;
• Qcache_inserts- le nombre de requêtes ajoutées au cache ;
• Qcache_lowmem_prunes- le nombre de requêtes supprimées du cache par manque de mémoire ;
• Qcache_not_cached- le nombre de requêtes qui n'ont pas été écrites dans le cache en raison de l'utilisation des fonctions de gestion du temps, etc. ;
• Qcache_queries_in_cache- le nombre de requêtes qui sont dans le cache.

Vous pouvez afficher le nombre total de requêtes SELECT :

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

7.3 Efficacité de la mise en cache

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

La valeur actuelle peut être vue comme ceci :

SHOW VARIABLES LIKE 'query_cache_size';

Là encore, la question se pose : comment choisir une valeur adéquatequery_cache_size?

Cela aidera Qcache_lowmem_prunes. Cette variable stocke le nombre de requêtes qui ont été supprimées du cache en raison de la nécessité de mettre en cache de nouvelles requêtes. Il est nécessaire de s'efforcer d'obtenir une telle taille de cache à laquelle Qcache_lowmem_pruneselle n'augmentera que légèrement. Pour ce faire, il est recommandé de comparer la différence de valeurs Qcache_lowmem_prunespar heure et le nombre de requêtes reçues par mysql dans la même heure.

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 = (volume de trafic en 10 minutes) * 1,2"

Cela mettra les requêtes en cache pendant 10 minutes + donnera 20 % de mémoire supplémentaire pour la fragmentation du cache et une réserve de mise en cache supplémentaire

Vous pouvez calculer le nombre et le volume moyen d'une réponse à une requête en utilisant respectivement les variables Bytes_sent .

Et donc query_cache_sizenous avons augmenté les valeurs, après quoi vous devez faire attention aux valeurs Qcache_total_blocks, Qcache_free_blockset Qcache_queries_in_cache. MySQL stocke le cache dans des blocs. Pour 1 requête, 2 blocs sont nécessaires : un pour le texte même de la requête, le second pour le résultat.

Si l'on considère le tableau à partir de la valeurQcache%

Nombre total de blocs de cacheQcache_total_blocks – 28

La requête 6 est maintenant mise en cache, ce qui signifie que 6 * 2 = 12 blocs sont occupés

Blocs gratuits Qcache_free_blocks – 10. Plus le cache est inactif Qcache_free_blocks, plus le degré de "fragmentation" du cache est élevé.

Si la plupart des requêtes ont une petite quantité de données résultantes, il vaut la peine de réduire la taille minimale du bloc de cache query_cache_min_res_unit, qui est de 4 Ko par défaut.

Si la plupart des requêtes renvoient beaucoup de données, il vaut la peine d'augmenter la taille du bloc de cache.

L'essentiel est d'atteindre une valeur minimale Qcache_free_blocks.

Si le compteur Qcache_not_cachedest grand, vous pouvez essayer d'augmenter la variable query_cache_limit- cela vous permettra d'augmenter la limite et de mettre en cache les résultats des requêtes qui "ne correspondent pas".

Les variables de configuration suivantes sont responsables de l'utilisation du cache de requête :

• query_cache_size— la taille du cache des requêtes. query_cache_size = 0désactive l'utilisation du cache ;
• query_cache_limit- la taille de l'échantillon maximum stocké dans le cache ;
• query_cache_wlock_invalidate- détermine si les données seront extraites du cache si la table à laquelle elles appartiennent est verrouillée en lecture.
=

Pour activer la mise en cache des requêtes mysql, ajoutez simplement les lignes suivantes à my.cnf (Section [mysqld]) :

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Et redémarrez le service.

7.4 Où le cache n'est pas utilisé

Non mis en cache :

• Demandes deSQL_NO_CACHE
• Requêtes préparées(Prepared statements);
• Requêtes qui sont des sous-requêtes de la requête externe ;
• Requêtes à l'intérieur des procédures stockées et des fonctions ;
• Requêtes utilisant des fonctions :

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()avec un argument, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()sans arguments, USER (),UUID ();

• Requêtes utilisant des fonctions stockées, des variables utilisateur ou des tables de référence dans les bases de données système mysql ou INFORMATION_SCHEMA ;
• • Demandes sous les formes suivantes :
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Requêtes avec des tables temporaires ou n'utilisant pas de tables du tout ;
• Requêtes générant des avertissements (warnings) ;

Vous pouvez défragmenter le cache avec la commande :

mysql>flush query cache;

Effacer - commande :

mysql>flush query cache;

Le plus important

Ne jamais travailler en production avec les paramètres par défaut. Ainsi, la plupart des ressources du serveur ne seront pas utilisées. Un bon réglage de MySQL peut améliorer plusieurs fois les performances de la base de données. Cela accélérera non seulement l'application, mais fera également face à une lourde charge.