Comment optimiser les requêtes MySQL avec la mise en cache de ProxySQL sur Ubuntu 16.04

L’auteur a choisi la Free Software Foundation pour recevoir un don dans le cadre du programme Write for DOnations.

Introduction

ProxySQL est un serveur proxy compatible SQL qui peut être positionné entre votre application et votre base de données. Il offre de nombreuses fonctionnalités, telles que l’équilibrage de la charge entre plusieurs serveurs MySQL et le fait de servir de couche de mise en cache pour les requêtes. Ce tutoriel se concentrera sur la fonction de mise en cache de ProxySQL, et sur la façon dont il peut optimiser les requêtes pour votre base de données MySQL.

La mise en cache de MySQL se produit lorsque le résultat d’une requête est stocké de sorte que, lorsque cette requête est répétée, le résultat peut être renvoyé sans avoir besoin de trier dans la base de données. Cela peut augmenter considérablement la vitesse des requêtes courantes. Mais dans de nombreuses méthodes de mise en cache, les développeurs doivent modifier le code de leur application, ce qui pourrait introduire un bug dans la base de code. Pour éviter cette pratique sujette aux erreurs, ProxySQL vous permet de mettre en place une mise en cache transparente.

Dans une mise en cache transparente, seuls les administrateurs de bases de données doivent modifier la configuration de ProxySQL pour permettre la mise en cache des requêtes les plus courantes, et ces modifications peuvent être effectuées via l’interface d’administration de ProxySQL. Tout ce que le développeur doit faire, c’est se connecter au proxy qui est conscient du protocole, et le proxy décidera si la requête peut être servie à partir du cache sans passer par le serveur dorsal.

Dans ce tutoriel, vous utiliserez ProxySQL pour mettre en place une mise en cache transparente pour un serveur MySQL sur Ubuntu 16.04. Vous testerez ensuite ses performances en utilisant mysqlslap avec et sans mise en cache pour démontrer l’effet de la mise en cache et le temps qu’elle peut faire gagner lors de l’exécution de nombreuses requêtes similaires.

Conditions préalables

Avant de commencer ce guide, vous aurez besoin des éléments suivants :

• Un serveur Ubuntu 16.04 avec au moins 2 Go de RAM, configuré avec un utilisateur non root avec des privilèges sudo et un pare-feu, comme indiqué dans notre guide de configuration initiale du serveur Ubuntu 16.04.

Étape 1 - Installez et configurez le serveur MySQL

Tout d’abord, vous devez installer le serveur MySQL et le configurer pour qu’il soit utilisé par ProxySQL comme serveur dorsal afin de répondre aux requêtes des clients.

Sur Ubuntu 16.04, mysql-server peut être installé en utilisant cette commande :

sudo apt-get install mysql-server

Appuyez sur Y pour confirmer l’installation.

Il vous sera alors demandé votre mot de passe d’utilisateur root MySQL. Saisissez un mot de passe fort et enregistrez-le pour une utilisation ultérieure.

Maintenant que votre serveur MySQL est prêt, vous allez le configurer pour que ProxySQL fonctionne correctement. Vous devez ajouter un utilisateur de surveillance pour ProxySQL pour surveiller le serveur MySQL, puisque ProxySQL écoute le serveur dorsal via le protocole SQL, plutôt que d’utiliser une connexion TCP ou des requêtes HTTP GET pour s’assurer que le serveur dorsal fonctionne. L’utilisateur de surveillance utilisera une connexion SQL fictive pour déterminer si le serveur est actif ou s’assurer

Tout d’abord, connectez-vous au shell MySQL :

mysql -uroot -p

-uroot vous connecte en utilisant l’utilisateur root de MySQL, et -p vous demande le mot de passe de l’utilisateur root. Cet utilisateur root est différent de l’utilisateur root de votre serveur, et le mot de passe est celui que vous avez saisi lors de l’installation du paquet mysql-server.

Entrez le mot de passe root et appuyez sur ENTER.

Vous allez maintenant créer deux utilisateurs : un utilisateur de surveillance pour ProxySQL et un autre que vous utiliserez pour exécuter les requêtes des clients et leur accorder les bons privilèges. Ce tutoriel donnera à cet utilisateur le nom de sammy.

Créez l’utilisateur de surveillance :

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';

La requête CREATE USER est utilisée pour créer un nouvel utilisateur qui peut se connecter à partir d’IP spécifiques. L’utilisation de % indique que l’utilisateur peut se connecter à partir de n’importe quelle adresse IP. IDENTIFIED BY définit le mot de passe du nouvel utilisateur ; entrez le mot de passe que vous souhaitez, mais assurez-vous de le mémoriser pour une utilisation ultérieure.

Une fois l’utilisateur de surveillance créé, faites de même pour l’utilisateur sammy :

CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';

Ensuite, accordez des privilèges à vos nouveaux utilisateurs. Exécutez la commande suivante pour configurer l’utilisateur de surveillance :

GRANT SELECT ON sys.* TO 'monitor'@'%';

La requête GRANT est utilisée pour accorder des privilèges aux utilisateurs. Ici, vous avez accordé uniquement SELECT sur toutes les tables de la base de données sys à l’utilisateur de surveillance ; il a besoin de ce privilège uniquement pour écouter le serveur dorsal.

Maintenant, accordez tous les privilèges de toutes les bases de données à l’utilisateur sammy :

GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';

Cela permettra à sammy de faire les recherches nécessaires pour tester votre base de données plus tard.

Appliquez les changements de privilèges en exécutant ce qui suit :

FLUSH PRIVILEGES;

Enfin, sortez du shell mysql :

exit;

Vous avez maintenant installé mysql-server (serveur mysql) et créé un utilisateur qui sera utilisé par ProxySQL pour surveiller votre serveur MySQL, et un autre pour exécuter les requêtes des clients. Maintenant, vous allez installer et configurer ProxySQL.

Étape 2 - Installez et configurez le ProxySQL Server (serveur ProxySQL)

Vous pouvez maintenant installer le ProxySQL server (serveur ProxySQL), qui sera utilisé comme couche de mise en cache pour vos requêtes. Une couche de mise en cache existe comme arrêt entre vos serveurs d’application et les serveurs dorsaux de la base de données ; elle est utilisée pour se connecter à la base de données et pour sauvegarder les résultats de certaines requêtes dans sa mémoire, en vue d’un accès ultérieur rapide.

La page Github des versions de ProxySQL propose des fichiers d’installation pour les distributions Linux courantes. Pour les besoins de ce tutoriel, vous utiliserez wget pour télécharger le fichier d’installation ProxySQL version 2.0.4 de Debian :

wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb

Ensuite, installez le paquet en utilisant dpkg :

sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

Une fois qu’il est installé, lancez ProxySQL avec cette commande :

sudo systemctl start proxysql

Vous pouvez vérifier si ProxySQL a démarré correctement avec cette commande :

sudo systemctl status proxysql

Vous obtiendrez un résultat similaire à celui-ci :

Output
root@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled)
   Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago
     Docs: man:systemd-sysv-generator(8)
    Tasks: 0
   Memory: 0B
      CPU: 0

Il est maintenant temps de connecter votre serveur ProxySQL au serveur MySQL. Pour cela, utilisez l’interface SQL d’administration de ProxySQL, qui par défaut écoute le port 6032 sur localhost et a admin comme nom d’utilisateur et mot de passe.

Connectez-vous à l’interface en exécutant ce qui suit :

mysql -uadmin -p -h 127.0.0.1 -P6032

Entrez admin lorsque le mot de passe vous est demandé.

-uadmin définit le nom d’utilisateur comme admin, et le drapeau -h spécifie l’hôte comme localhost. Le port est le 6032, spécifié à l’aide du drapeau -P.

Ici, vous avez dû spécifier explicitement l’hôte et le port car, par défaut, le client MySQL se connecte en utilisant un fichier sockets local et le port 3306

Maintenant que vous êtes connecté au shell mysql en tant qu’admin, configurez l’utilisateur de surveillance pour que ProxySQL puisse l’utiliser. Tout d’abord, utilisez des requêtes SQL standard pour définir les valeurs de deux variables globales :

UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

La variable mysql-monitor_username spécifie le nom d’utilisateur MySQL qui sera utilisé pour vérifier si le serveur dorsal est actif ou non. La variable mysql-monitor_password indique le mot de passe qui sera utilisé lors de la connexion au serveur dorsal. Utilisez le mot de passe que vous avez créé pour le nom de l’utilisateur de surveillance.

Chaque fois que vous créez un changement dans l’interface d’administration de ProxySQL, vous devez utiliser la bonne commande LOAD pour appliquer les changements à l’instance de ProxySQL en cours. Vous avez modifié les variables globales de MySQL, alors chargez-les dans RUNTIME pour appliquer les changements :

LOAD MYSQL VARIABLES TO RUNTIME;

Ensuite, SAUVEGARDEZ les modifications apportées à la base de données sur disque pour que les changements persistent entre les redémarrages. ProxySQL utilise sa propre base de données locale SQLite pour stocker ses propres tables et variables :

SAVE MYSQL VARIABLES TO DISK;

Maintenant, vous allez parler du serveur dorsal à ProxySQL. La table mysql_servers contient des informations sur chaque serveur dorsal où ProxySQL peut se connecter et exécuter des requêtes. Ajoutez donc un nouvel enregistrement en utilisant une instruction SQL INSERT standard avec les valeurs suivantes pour hostgroup_id, hostname et port :

INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);

Pour appliquer les changements, lancez à nouveau LOAD et SAVE :

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Enfin, vous indiquerez à ProxySQL quel utilisateur se connectera au serveur dorsal ; vous définirez sammy comme utilisateur et remplacerez sammy_password par le mot de passe que vous avez créé précédemment :

INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);

La table mysql_users contient des informations sur les utilisateurs utilisés pour se connecter aux serveurs dorsaux ; vous avez indiqué le username, password et le default_hostgroup.

LOAD et SAVE les changements :

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Puis, sortez du shell mysql :

exit;

Pour tester que vous pouvez vous connecter à votre serveur dorsal en utilisant ProxySQL, exécutez la requête de test suivante :

mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"

Dans cette commande, vous avez utilisé le drapeau -e pour exécuter une requête et fermer la connexion. La requête imprime le nom d’hôte du serveur dorsal.

La sortie ressemblera à ceci, avec your_hostname remplacé par votre nom d’hôte :

Output
+----------------------------+
| hostname                   |
+----------------------------+
| your_hostname        |
+----------------------------+

Pour en savoir plus sur la configuration de ProxySQL, voir l’étape 3 de Comment utiliser ProxySQL comme équilibreur de charge pour MySQL sur Ubuntu 16.04.

Jusqu’à présent, vous avez configuré ProxySQL pour utiliser votre serveur MySQL comme serveur dorsal et vous vous êtes connecté au serveur dorsal en utilisant ProxySQL. Maintenant, vous êtes prêt à utiliser mysqlslap pour évaluer les performances de la requête sans mise en cache.

Étape 3 - Testez l’utilisation de mysqlslap sans mise en cache

Au cours de cette étape, vous téléchargerez une base de données test afin de pouvoir effectuer des recherches sur celle-ci avec mysqlslap pour tester la latence sans mise en cache, établissant ainsi une référence pour la rapidité de vos requêtes. Vous découvrirez également comment ProxySQL conserve les enregistrements des requêtes dans la table stats_mysql_query_digest.

mysqlslap est un client d’émulation de charge qui est utilisé comme un outil de test de charge pour MySQL. Il peut tester un serveur MySQL avec des requêtes générées automatiquement ou avec des requêtes personnalisées exécutées sur une base de données. Il est installé avec le paquet client MySQL, vous n’avez donc pas besoin de l’installer ; à la place, vous téléchargerez une base de données à des fins de test uniquement, sur laquelle vous pourrez utiliser mysqlslap.

Dans ce tutoriel, vous utiliserez un exemple de base de données sur les employés. Vous utiliserez cette base de données sur les employés car elle comporte un vaste ensemble de données qui peuvent illustrer les différences d’optimisation des requêtes. La base de données comporte six tables, mais les données qu’elle contient contiennent plus de 300 000 registres d’employés. Cela vous permettra d’imiter une production à grande échelle.

Pour télécharger la base de données, il faut d’abord cloner le dépôt Github en utilisant cette commande :

git clone https://github.com/datacharmer/test_db.git

Ensuite, entrez dans le répertoire test_db et chargez la base de données dans le serveur MySQL à l’aide de ces commandes :

cd test_db
mysql -uroot -p < employees.sql

Cette commande utilise la redirection du shell pour lire les requêtes SQL dans le fichier employees.sql et les exécuter sur le serveur MySQL pour créer la structure de la base de données.

Vous verrez des résultats comme celui-ci :

Output
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:00:32

Une fois que la base de données est chargée dans votre serveur MySQL, vérifiez que mysqlslap fonctionne avec la requête suivante :

mysqlslap -usammy -p -P6033 -h127.0.0.1  --auto-generate-sql --verbose

mysqlslap a des drapeaux similaires à ceux du client mysql ; voici ceux qui sont utilisés dans cette commande :

• -u spécifie l’utilisateur utilisé pour se connecter au serveur.
• -p demande le mot de passe de l’utilisateur.
• -P se connecte en utilisant le port spécifié.
• -h se connecte à l’hôte spécifié.
• --auto-generate-sql permet à MySQL d’effectuer des tests de charge en utilisant ses propres requêtes générées.
• -verbose fait en sorte que la sortie affiche plus d’informations.

Vous verrez un résultat similaire à celui qui suit :

Output
Benchmark
    Average number of seconds to run all queries: 0.015 seconds
    Minimum number of seconds to run all queries: 0.015 seconds
    Maximum number of seconds to run all queries: 0.015 seconds
    Number of clients running queries: 1
    Average number of queries per client: 0

Dans cette sortie, vous pouvez voir le nombre moyen, minimum et maximum de secondes passées pour exécuter toutes les requêtes. Cela vous donne une indication sur le temps nécessaire pour exécuter les requêtes d’un certain nombre de clients. Dans ce résultat, un seul client a été utilisé pour exécuter les requêtes.

Ensuite, découvrez les requêtes mysqlslap exécutées dans la dernière commande en regardant le stats_mysql_query_digest de ProxySQL. Cela nous donnera des informations comme un résumé des requêtes, qui est une forme normalisée de l’instruction SQL qui peut être référencée ultérieurement pour permettre la mise en cache.

Entrez dans l’interface d’administration de ProxySQL avec cette commande :

mysql -uadmin -p -h 127.0.0.1 -P6032

Exécutez ensuite cette requête pour trouver des informations dans la table stats_mysql_query_digest :

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

Vous verrez un résultat similaire à celui qui suit :

+------------+----------+-----------+--------------------+----------------------------------+
| count_star | sum_time | hostgroup | digest             | digest_text                      |
+------------+----------+-----------+--------------------+----------------------------------+
| 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    |
| 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
+------------+----------+-----------+--------------------+----------------------------------+
2 rows in set (0.01 sec)

La requête précédente sélectionne les données de la table stats_mysql_query_digest qui contient des informations sur toutes les requêtes exécutées dans ProxySQL. Vous avez ici cinq colonnes sélectionnées :

• count_star : Le nombre de fois que cette requête a été exécutée.
• sum_time : Temps total (en millisecondes) que cette requête a pris pour s’exécuter.
• hostgroup : Le groupe d’hôtes utilisé pour exécuter la requête.
• digérer : Un résumé de la requête exécutée.
• digest_text : La requête proprement dite. Dans l’exemple de ce tutoriel, la deuxième requête est paramétrée en utilisant ? des marques à la place des paramètres de la variable. select @@version_comment limit 1 et select @@version_comment limit 2, par conséquent, sont regroupées comme une même requête avec le même condensé.

Maintenant que vous savez comment vérifier les données de requête dans la table stats_mysql_query_digest, quittez le shell mysql :

exit;

La base de données que vous avez téléchargée contient quelques tables avec des données de démonstration. Vous allez maintenant tester les requêtes sur la table dept_emp en sélectionnant les enregistrements dont la date from_date est ultérieure à 2000-04-20 et en enregistrant le temps d’exécution moyen.

Utilisez cette commande pour exécuter le test :

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

Ici, vous utilisez de nouveaux drapeaux :

• --concurrency=100 : Ce paramètre fixe le nombre d’utilisateurs à simuler, en l’occurrence 100.
• --iterations=20 : le test est alors exécuté 20 fois et les résultats sont calculés à partir de chacune d’elles.
• --create-schema=employés : Vous avez sélectionné ici la base de données des employés.
• --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" : Ici, vous avez spécifié la requête exécutée dans le test.

Le test durera quelques minutes. Une fois terminé, vous obtiendrez des résultats similaires à ceux qui suivent :

Output
Benchmark
        Average number of seconds to run all queries: 18.117 seconds
        Minimum number of seconds to run all queries: 8.726 seconds
        Maximum number of seconds to run all queries: 22.697 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

Vos chiffres pourraient être un peu différents. Conservez ces chiffres quelque part afin de pouvoir les comparer avec les résultats obtenus après avoir activé la mise en cache.

Après avoir testé ProxySQL sans mise en cache, il est temps d’exécuter à nouveau le même test, mais cette fois avec la mise en cache activée.

Étape 4 - Testez l’utilisation de mysqlslap avec mise en cache

Dans cette étape, la mise en cache nous aidera à réduire la latence lors de l’exécution de requêtes similaires. Ici, vous allez identifier les requêtes exécutées, prendre leurs digests dans la table stats_mysql_query_digest de ProxySQL, et les utiliser pour activer la mise en cache. Ensuite, vous ferez un nouveau test pour vérifier la différence.

Pour activer la mise en cache, vous devez connaître les digests des requêtes qui seront mises en cache. Connectez-vous à l’interface d’administration de ProxySQL en utilisant cette commande :

mysql -uadmin -p -h127.0.0.1 -P6032

Exécutez ensuite à nouveau cette requête pour obtenir une liste des requêtes exécutées et leurs digests :

SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

Vous obtiendrez un résultat similaire à celui-ci :

Output
+------------+-------------+-----------+--------------------+------------------------------------------+
| count_star | sum_time    | hostgroup | digest             | digest_text                              |
+------------+-------------+-----------+--------------------+------------------------------------------+
| 2000       | 33727110501 | 1         | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? |
| 1          | 601         | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname            |
| 1          | 0           | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ?         |
+------------+-------------+-----------+--------------------+------------------------------------------+
3 rows in set (0.00 sec)

Regardez la première rangée. Il s’agit d’une requête qui a été exécutée 2000 fois. Il s’agit de la requête référencée exécutée précédemment. Prenez son digest et enregistrez-le afin de l’utiliser dans l’ajout d’une règle de requête pour la mise en cache.

Les prochaines requêtes ajouteront une nouvelle règle de requête à ProxySQL qui correspondra au digest de la requête précédente et lui attribuera une valeur cache_ttl. cache_ttl est le nombre de millisecondes pendant lesquelles le résultat sera mis en cache en mémoire :

INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);

Dans cette commande, vous ajoutez un nouvel enregistrement à la table mysql_query_rules ; cette table contient toutes les règles appliquées avant l’exécution d’une requête. Dans cet exemple, vous ajoutez une valeur pour la colonne cache_ttl qui entraînera la mise en cache de la requête correspondante par le digest donné pendant un nombre de millisecondes spécifié dans cette colonne. Vous mettez 1 dans la colonne Appliquer pour vous assurer que la règle est appliquée aux requêtes.

CHARGEZ et ENREGISTREZ ces modifications, puis quittez le shell mysql :

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
exit;

Maintenant que la mise en cache est activée, relancez le test pour vérifier le résultat :

mysqlslap -usammy -P6033 -p -h127.0.0.1  --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

Cela donnera un résultat similaire à ce qui suit :

Output
Benchmark
        Average number of seconds to run all queries: 7.020 seconds
        Minimum number of seconds to run all queries: 0.274 seconds
        Maximum number of seconds to run all queries: 23.014 seconds
        Number of clients running queries: 100
        Average number of queries per client: 1

Ici, vous pouvez voir la grande différence dans le temps d’exécution moyen : il est passé de 18,117 secondes à 7,020.

Conclusion

Dans cet article, vous avez configuré la mise en cache transparente avec ProxySQL pour mettre en cache les résultats des requêtes de base de données. Vous avez également testé la vitesse de requête avec et sans mise en cache pour voir la différence que la mise en cache peut faire.

Vous avez utilisé un niveau de mise en cache dans ce tutoriel. Vous pouvez aussi essayer la mise en cache sur le web, qui se trouve devant un serveur web et met en cache les réponses à des requêtessimilaires, renvoyant la réponse au client sans toucher aux serveurs dorsaux. C’est très similaire à la mise en cache de ProxySQL, mais à un niveau différent. Pour en savoir plus sur la mise en cache sur le web, consultez nos Principes de base de la mise en cache sur le web : Terminologie, en-têtes HTTP et stratégies de mise en cache.

Le serveur MySQL possède également son propre cache de requêtes ; vous pouvez en apprendre plus à ce sujet dans notre tutoriel Comment optimiser MySQL avec le cache de requêtes sur Ubuntu 18.04.