Maîtriser le cache de requêtes MySQL : variables clés et meilleures pratiques d'optimisation

Qu'est-ce que le cache de requêtes ?

Le cache de requêtes est une fonctionnalité de MySQL conçue pour améliorer les performances de la base de données en mettant en cache les résultats des requêtes SELECT. Lorsqu'une requête déjà exécutée est à nouveau exécutée, MySQL peut rapidement extraire le résultat de ce cache au lieu de le réexécuter sur la base de données. Cela accélère non seulement la récupération des données, mais réduit également la charge sur la base de données, la rendant très efficace pour les requêtes fréquemment exécutées avec des paramètres cohérents.

Comment fonctionne le cache de requêtes

Chaque fois qu'une requête est émise, MySQL regarde d'abord dans le cache de requêtes pour vérifier si le résultat d'une requête identique a été stocké précédemment. S'il y a une correspondance, MySQL contourne le processus d'exécution de requête habituel et sert directement le résultat mis en cache. C'est beaucoup plus rapide car cela évite les étapes fastidieuses de traitement des requêtes et d'accès au disque, en tirant plutôt parti de la vitesse d'accès à la mémoire.

Nous passons maintenant aux variables critiques du cache de requêtes – query_cache_type, query_cache_size, query_cache_limit et query_cache_min_res_unit – et à leur impact sur Performances MySQL.

1. query_cache_type

La variable query_cache_type dans MySQL contrôle si le cache de requêtes est activé, désactivé ou configuré pour fonctionner uniquement à la demande.

query_cache_type Utilisation

Le cache de requêtes stocke le texte d'une instruction SELECT ainsi que le résultat correspondant qui a été envoyé au client. Si une instruction identique est reçue ultérieurement, le serveur récupère les résultats du cache plutôt que d'analyser et d'exécuter à nouveau l'instruction. L'activation du cache de requêtes peut réduire considérablement le temps nécessaire pour obtenir les résultats des requêtes fréquemment exécutées en les servant à partir de la mémoire plutôt que de les réexécuter.

La définition de la variable query_cache_type dans MySQL sur différentes valeurs détermine le comportement du cache de requêtes :

• 0 (OFF) – Désactive le cache de requêtes, bien qu'un tampon de query_cache_size octets soit toujours alloué.

• 1 (ON) – Active le cache de requêtes pour toutes les requêtes SELECT sauf si SQL_NO_CACHE est spécifié dans la requête.

• 2 (DEMANDE) – Active le cache de requêtes uniquement pour les requêtes qui utilisent explicitement la clause SQL CACHE.

query_cache_typeConfiguration

Query_cache_type peut être configuré hors ligne ou en ligne, lorsque le serveur est en cours d'exécution. La configuration peut être préférée en ligne, pour permettre les tests. Lorsque le serveur sera redémarré, le query_cache_type sera rétabli.

Configuration de la ligne de commande :

mysqld> set global query_cache_type = XX

Remplacez XX par une valeur adaptée aux besoins de votre base de données. Pour vérifier que la variable a été modifiée :

mysqld> show global variables like 'query_cache_type'

Fichier de configuration :

[mysqld]
query_cache_type = XX

Remplacez XX par une valeur adaptée aux besoins de votre base de données. Redémarrez le serveur MySQL.

Considérations sur query_cache_type

Selon les recommandations générales, vous devez définir query_cache_type sur 1 (ON) pour les environnements dans lesquels les données changent rarement mais les lectures sont fréquentes. Défini sur 0 (OFF) dans les environnements très dynamiques où la surcharge liée à la maintenance du cache peut dépasser les avantages.

L'activation du cache de requêtes sans discernement peut ne pas toujours apporter des avantages en termes de performances et peut même dégrader les performances dans certains scénarios. Tenez compte des facteurs suivants lors de la configuration de query_cache_type :

• La taille du cache de requêtes – Un cache plus grand peut contenir plus de résultats de requête mais nécessite plus de mémoire.

• Modèles de requête – Les requêtes avec des résultats qui changent fréquemment ou des ensembles de résultats volumineux peuvent ne pas bénéficier de la mise en cache.

• Invalidation du cache – Les mises à jour, insertions ou suppressions sur les tables mises en cache invalident les entrées de cache correspondantes, entraînant une perte de cache.

• Concurrency – Le cache de requêtes n'est pas adapté aux charges de travail hautement concurrentes en raison de problèmes de contention.

• Version MySQL – La fonctionnalité de cache de requêtes a été obsolète dans MySQL 5.7 et supprimée dans MySQL 8.0, car elle présente des limites et peut provoquer des conflits dans les environnements multithread.

2. query_cache_size

Spécifie la quantité de mémoire allouée pour stocker les résultats des requêtes mises en cache. Il s'agit d'un facteur primordial pour déterminer combien de résultats peuvent être mis en cache à la fois.

query_cache_size Utilisation

La variable query_cache_size détermine la quantité de mémoire allouée au cache de requêtes. Cette valeur doit être ajustée en fonction de la nature de votre charge de travail et des ressources mémoire disponibles :

• Petits ensembles de résultats – Si votre application exécute fréquemment des requêtes qui renvoient de petits ensembles de résultats, une taille de cache de requêtes plus grande peut être bénéfique. Cela permet de stocker davantage de requêtes dans le cache, réduisant ainsi le besoin d'exécution de requêtes.

• Requêtes identiques fréquentes – Dans les scénarios où les mêmes requêtes sont exécutées à plusieurs reprises, l'augmentation de query_cache_size peut améliorer les performances en mettant en cache ces requêtes et leurs résultats.

• Taux de réussite du cache de requêtes – La surveillance du taux de réussite du cache de requêtes peut fournir des informations sur l'efficacité du cache. Si le taux de réussite est faible, augmenter query_cache_size peut contribuer à améliorer l'efficacité du cache.

query_cache_sizeConfiguration

Query_cache_size peut être configuré hors ligne ou en ligne, lorsque le serveur est en cours d'exécution. La configuration peut être préférée en ligne, pour permettre les tests. Lorsque le serveur sera redémarré, query_cache_size sera rétabli.

Configuration de la ligne de commande :

mysqld> set global query_cache_type = XX

Remplacez XX par une valeur adaptée aux besoins de votre base de données. Pour vérifier que la variable a été modifiée :

mysqld> show global variables like 'query_cache_type'

Fichier de configuration :

[mysqld]
query_cache_type = XX

Remplacez XX par une valeur adaptée aux besoins de votre base de données. Redémarrez le serveur MySQL.

Considérations sur query_cache_size

Le query_cache_size doit être défini en fonction de la mémoire disponible et de la nature de votre charge de travail. Un réglage trop grand peut entraîner un épuisement de la mémoire, tandis qu'un réglage trop petit peut limiter son efficacité.

La surveillance de l'utilisation du cache (accès par rapport aux insertions) guidera le dimensionnement approprié. Commencez avec une taille modérée, comme 64 Mo à 128 Mo, et ajustez en fonction des performances et de la mémoire système disponible.

Considérez les facteurs suivants lors de la configuration de query_cache_size :

• Modèles de requête – Les requêtes avec des résultats qui changent fréquemment ou des ensembles de résultats volumineux peuvent ne pas bénéficier de la mise en cache.

• Invalidation du cache – Les mises à jour, insertions ou suppressions sur les tables mises en cache invalident les entrées de cache correspondantes, entraînant une perte de cache.

• Concurrency – Le cache de requêtes n'est pas adapté aux charges de travail hautement concurrentes en raison de problèmes de contention.

• Version MySQL – La fonctionnalité de cache de requêtes a été obsolète dans MySQL 5.7 et supprimée dans MySQL 8.0 en raison de limitations et de conflits dans les environnements multithread.

3. query_cache_limit

Cette variable définit la taille maximale des résultats de requête individuels pouvant être mis en cache. Cela empêche les requêtes volumineuses de consommer une quantité disproportionnée d'espace de cache.

query_cache_limit Utilisation

Lorsque le résultat d'une requête dépasse la query_cache_limit, le résultat n'est pas mis en cache. Cela évite que des requêtes trop volumineuses ou gourmandes en ressources ne remplissent le cache avec des résultats qui pourraient ne pas être réutilisés fréquemment. En définissant une valeur appropriée pour query_cache_limit, vous pouvez garantir que seuls les résultats de requêtes plus petits et plus couramment utilisés sont mis en cache, optimisant ainsi l'utilisation de la mémoire.

query_cache_limitConfiguration

Query_cache_limit peut être configuré hors ligne ou en ligne, lorsque le serveur est en cours d'exécution. La configuration peut être préférée en ligne, pour permettre les tests. Lorsque le serveur sera redémarré, query_cache_limit sera rétabli.
Configuration de la ligne de commande :

mysqld> set global query_cache_type = XX

Remplacez XX par une valeur adaptée aux besoins de votre base de données. Pour vérifier que la variable a été modifiée :

mysqld> show global variables like 'query_cache_type'

Fichier de configuration :

[mysqld]
query_cache_type = XX

Remplacez XX par une valeur adaptée aux besoins de votre base de données. Redémarrez le serveur MySQL.

Considérations sur query_cache_limit

Il est généralement recommandé de définir query_cache_limit entre 1 Mo et 4 Mo, en fonction de la nature des requêtes et de la taille du cache disponible. Il est important de noter qu'un réglage trop bas de query_cache_limit peut entraîner l'exclusion des résultats de requêtes utiles du cache, réduisant ainsi l'efficacité du cache de requêtes.

4. query_cache_min_res_unit

La variable query_cache_min_res_unit dans MySQL détermine la taille minimale en octets des blocs alloués par le cache de requêtes. Ce paramètre a un impact sur l'efficacité du cache de requêtes en contrôlant la granularité des résultats mis en cache.

query_cache_min_res_unit Utilisation

Lorsqu'un résultat de requête est stocké dans le cache de requêtes, il occupe une certaine quantité de mémoire. La variable query_cache_min_res_unit définit la taille minimale des blocs mémoire alloués pour ces résultats mis en cache. Si le résultat d'une requête est inférieur à cette valeur, il occupera toujours la taille minimale définie par query_cache_min_res_unit.

query_cache_min_res_unitConfiguration

Query_cache_min_res_unit peut être configuré hors ligne ou en ligne, lorsque le serveur est en cours d'exécution. La configuration peut être préférée en ligne, pour permettre les tests. Lorsque le serveur sera redémarré, le query_cache_min_res_unit sera rétabli.

Configuration de la ligne de commande :

mysqld> set global query_cache_size = XX

Remplacez XX par une valeur adaptée aux besoins de votre base de données. Pour vérifier que la variable a été modifiée :

mysqld> show global variables like 'query_cache_size'

Fichier de configuration :

[mysqld]
query_cache_size = XX

Remplacez XX par une valeur adaptée aux besoins de votre base de données. Redémarrez le serveur MySQL.

Considérations query_cache_min_res_unit

La configuration de query_cache_min_res_unit implique de définir la variable sur une valeur appropriée qui équilibre la consommation de mémoire avec l'efficacité de la mise en cache. La valeur doit être choisie en fonction de la taille moyenne des résultats de requête dans votre charge de travail.

• Une valeur plus petite peut conduire à une utilisation plus efficace de la mémoire, mais pourrait augmenter la surcharge en raison d'un plus grand nombre d'entrées dans le cache.

• À l'inverse, une valeur plus élevée peut réduire le nombre d'entrées de cache mais peut entraîner un gaspillage de mémoire pour des résultats de requête plus petits.

Analysez votre charge de travail pour déterminer la taille moyenne des résultats de requête. Ajustez la valeur de query_cache_min_res_unit en fonction de cette analyse pour atteindre un équilibre entre la consommation de mémoire et l'efficacité de la mise en cache. Pour la plupart des configurations, cela se situera entre 16 Mo et 64 Mo.

Activer et régler le cache de requêtes en 4 étapes

Le cache de requêtes est obsolète depuis MySQL 5.7.20 et entièrement supprimé dans MySQL 8.0. Si votre version de MySQL est toujours capable d'utiliser query_cache, elle doit être activée car elle est désactivée par défaut. Pour activer et configurer le cache de requêtes dans MySQL, MariaDB ou Percona, vous devrez généralement accéder au fichier my.cnf ou my.ini de votre serveur. Voici une approche étape par étape :

1. Activer le cache de requête – Définissez query_cache_type sur 1 ou 2. Définir query_cache_type ou query_cache_size sur zéro désactivera toujours le cache. Pour la mise en cache sélective (recommandée pour la plupart des cas d'utilisation), vous utiliseriez :

mysqld> set global query_cache_type = XX

2. Définir la taille du cache – Définissez query_cache_size. Un point de départ peut être 10 à 20 % de votre mémoire totale disponible, mais cela nécessite un réglage en fonction de votre charge de travail :

mysqld> show global variables like 'query_cache_type'

3. Définir la limite de taille des résultats – Configurez query_cache_limit pour contrôler la taille des résultats stockés. Cela peut commencer à quelques mégaoctets, en fonction de la taille typique de votre requête :

[mysqld]
query_cache_type = XX

4. Ajuster l'unité de résultat minimale – Modifiez query_cache_min_res_unit en fonction de vos besoins. Réduire cette valeur en dessous de la valeur par défaut peut aider à utiliser l'espace du cache plus efficacement, surtout si vous vous attendez à beaucoup de petites requêtes :

mysqld> set global query_cache_size = XX

Surveillance du cache de requêtes avec des vérifications de l'état

La fragmentation QCache est un indicateur clé des performances du cache de requêtes dans MySQL. Cette fonctionnalité est conçue pour stocker les résultats des requêtes SELECT afin que les requêtes répétées puissent être exécutées rapidement sans avoir besoin de réexécuter la requête, améliorant ainsi les performances. Cependant, au fil du temps, le cache de requêtes peut devenir fragmenté, entraînant une efficacité réduite.

Calculer la fragmentation de QCache

Consultez notre documentation complète sur les contrôles de santé, qui contient des informations et des instructions étape par étape sur la façon de calculer la fragmentation QCache.

Une fois que vous avez calculé la fragmentation QCache et QcacheDeleteRate, vous devez interpréter les résultats. Idéalement, la fragmentation QCache devrait être inférieure à 10 et le QcacheDeleteRate devrait être inférieur à 20.

Actions recommandées

Si la fragmentation QCache est élevée, vous devrez peut-être ajuster la taille du cache de requêtes pour réduire la fragmentation. Si le QcacheDeleteRate est élevé, vous devrez peut-être augmenter la taille du cache de requêtes ou optimiser vos requêtes pour réduire le nombre d'INSERT.

Optimisez facilement votre cache de requêtes

Le réglage du cache de requêtes MySQL implique l'ajustement de plusieurs paramètres pour optimiser les performances de la base de données, de la gestion de l'utilisation de la mémoire à la réduction des temps de requête. Bien que les variables abordées ici constituent une bonne base, une gestion efficace nécessite une surveillance et des mises à jour continues basées sur la charge et les performances réelles du système.

Pour simplifier ce processus, pensez à utiliser un outil de gestion puissant comme Releem qui automatise ces ajustements. Un tel outil peut surveiller en permanence les performances de votre système et mettre à jour dynamiquement les paramètres query_cache en temps réel.

Cela vous laisse le temps de vous concentrer sur des objectifs plus larges pendant que Releem gère les subtilités de l'optimisation du cache de requêtes.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!