Configurer le Query Cache dans MariaDB
16 mars 2018
Le Query Cache est un mécanisme de mise en cache des requêtes dans MariaDB / MySQL. L’objectif est d’améliorer les temps de réponse, en réduisant les accès au disque et en privilégiant la lecture des données des requêtes à partir de la mémoire. Le souci est que ce dispositif peut entrer en concurrence avec la mise en mémoire des pages d’index, qui constitue le mécanisme principal utilisé par MySQL / MariaDB. Oracle a d’ailleurs décidé de supprimer le Query Cache à partir de la version 8 de MySQL. C’est une des raisons supplémentaires, de mon point de vue, qui doit vous amener à privilégier MariaDB à MySQL, même s’il ne faut certainement pas abuser de ce mécanisme qui est un pis-aller lorsque les tables ne sont pas correctement indexées.
Paramétrage du Query Cache dans MySQL / MariaDB
Afin de savoir si le Query Cache est activé dans vos instances MySQL / MariaDB, tapez à partir de l’interpréteur SQL :
SELECT @@have_query_cache;
La configuration du Query Cache est dynamique. Elle peut s’effectuer à chaud à l’aide des trois commandes suivantes :
SET GLOBAL query_cache_type=1; -- Activation du Query Cache pour toutes les requêtes, sauf celles utilisant la clause SQL_NO_CACHE SET GLOBAL query_cache_limit=128MB; -- Taille maximale des données d'une requête mise en cache SET GLOBAL query_cache_size=256MB; -- Taille maximale de toutes les données des requêtes mises en cache
Pour que le Query Cache soit pris en compte au prochain démarrage,, vous devrez ajouter au fichier /etc/my.cnf ou à l’un des fichiers contenus dans /etc/my.cnf.d, les variables indiquées ci-dessus :
query_cache_type=1 query_cache_limit=128MB; query_cache_size=256MB;
Suivre la consommation du Query Cache
Dans MariaDB, vous disposez d’un plugin (/usr/lib64/mariadb/plugin/query_cache_info.so sur Fedora 27) qui vous permet de connaître le remplissage du Query Cache :
INSTALL SONAME 'query_cache_info';
Le plugin ajoute la table query_cache_info au schéma information_schema :
SELECT SUM(result_blocks_size)/1048576 FROM information_schema.query_cache_info;
Efficacité du Query Cache
Un hit ratio de plus de 80% – l’idéal étant qu’il tende vers 100% – entre le nombre de requêtes exécutées à partir du cache (qcache_hits) et la totalité des requêtes exécutées (com_select) est un indicateur de bonne santé de la base de données. Pour mesurer le nombre de plans d’exécution mis en cache, vous disposez de l’indicateur qcache_queries_in_cache qui est à comparer avec l’ensemble des plans d’exécution qcache_queries_in_cache+qcache_not_cached.
SELECT
variable_name,
variable_value
FROM
information_schema.global_status
WHERE
variable_name IN ('qcache_hits','qcache_queries_in_cache','qcache_not_cached','com_select');
Défragmentation du Query Cache
Vous pouvez défragmenter les données des reqêtes mises en cache à l’aide de la commande SQL :
FLUSH QUERY CACHE;
Réinitialiser le Query Cache
Enfin, vous pouvez vider le cache en tapant à partir de l’interpréteur MySQL :
RESET QUERY CACHE;