Mise à l’échelle de MySQL avec l’équilibrage de charge TCP et un cluster Galera

Liam CrillyDirecteur senior de la gestion des produits

[Éditeur – Initialement publié en 2016, cet article a été mis à jour afin d’utiliser les fonctionnalités de NGINX qui ont été révisées depuis. Pour plus de détails, consultez Journalisation avancée avec le module JavaScript de NGINX et Tableau de bord NGINX Plus.]

Nous avons introduit l’équilibrage de charge TCP dans NGINX Plus R5, puis avons continuellement ajouté de nouvelles fonctionnalités dans les versions suivantes, ainsi que la prise en charge de l’équilibrage de charge UDP. Dans cet article, nous examinons les principales exigences en matière d’équilibrage de charge TCP et la manière dont NGINX Plus y répond.

Pour découvrir les fonctionnalités de NGINX Plus, nous utiliserons un environnement de test simple représentant les principaux composants d’une application dotée d’un backend de base de données dimensionné pour la montée en charge. Pour obtenir des instructions complètes sur la création de l’environnement de test, consultez l’annexe.

Dans cet environnement, NGINX Plus fait office de proxy inverse pour le serveur de base de données et écoute sur le port MySQL par défaut, le port 3306. Le client bénéficie ainsi d’une interface simple, tandis que les nœuds MySQL du backend peuvent être ajoutés (voire mis hors ligne) sans aucune incidence sur celui-ci. Nous utilisons l’outil MySQL en ligne de commande comme client, lequel représente l’application frontend dans l’environnement de test.

Bon nombre des fonctionnalités décrites dans cet article s’appliquent à la fois à NGINX Open Source et à NGINX Plus. Par souci de concision, nous parlerons de NGINX Plus tout au long de l’article et signalerons explicitement les fonctionnalités qui ne sont pas disponibles dans NGINX Open Source.

Nous allons explorer les cas d’usage suivants :

• Répartition de charge TCP
• Haute disponibilité et contrôles d’intégrité
• Journalisation et diagnostics
• Considérations relatives aux écritures simultanées

Équilibrage de charge TCP

Avant de configurer l’équilibrage de charge pour une application, il est important de comprendre comment celle-ci se connecte à la base de données. Pour la plupart de nos tests, nous utilisons l’outil en ligne de commande MySQL, mysql(1), pour nous connecter au cluster Galera, exécuter une requête, puis fermer la connexion. Cependant, de nombreux frameworks applicatifs utilisent un pool de connexions afin de réduire la latence et d’utiliser efficacement les ressources du serveur de base de données.

L’équilibrage de charge TCP est configuré dans le contexte de configuration stream ; nous créons donc notre configuration de base pour l’équilibrage de charge MySQL en ajoutant un bloc stream au fichier principal nginx.conf.

Chargement de l’exemple de code…

Cela permet de séparer notre configuration de répartition de charge TCP du fichier de configuration principal. Nous créons ensuite stream.conf dans le même répertoire que nginx.conf. Notez que, par défaut, le répertoire conf.d est réservé au contexte de configuration http ; l’ajout de fichiers de configuration stream dans ce répertoire ne fonctionne donc pas.

Chargement de l’exemple de code…

Nous définissons tout d’abord un groupe upstream nommé galera_cluster, qui contient les trois nœuds MySQL de notre cluster Galera. Dans notre environnement de test, chacun d’eux est accessible sur localhost via un numéro de port unique. La directive zone définit une quantité de mémoire partagée entre tous les processus de travail NGINX Plus afin de conserver l’état de l’équilibrage de charge. Le bloc server{} configure la manière dont NGINX Plus gère les clients. NGINX Plus écoute sur le port MySQL par défaut, 3306, et transfère tout le trafic vers le cluster Galera défini dans le bloc upstream.

Pour vérifier que cette configuration de base fonctionne, nous pouvons utiliser le client MySQL afin d’obtenir le nom d’hôte du nœud du cluster Galera auquel nous nous sommes connectés.

$ echo "SHOW VARIABLES WHERE Variable_name = 'hostname'" | mysql --protocol=tcp --user=nginx --password=plus -N 2> /dev/nullhostname	node1

Pour vérifier que l’équilibrage de charge fonctionne, nous pouvons répéter la même commande.

$ !!;!!;!!hostname	node2
hostname	node3
hostname	node1

Cela montre que l’algorithme d’équilibrage de charge par défaut, de type round-robin, fonctionne correctement. Toutefois, si notre application utilise un pool de connexions pour accéder à la base de données (comme suggéré ci-dessus), l’ouverture de connexions au cluster selon une répartition round-robin risque d’entraîner un nombre déséquilibré de connexions sur chaque nœud. En outre, une connexion ne correspond pas nécessairement à une charge de travail donnée, car elle peut être inactive (dans l’attente d’une requête de l’application) ou occupée à traiter une requête. Un algorithme d’équilibrage de charge plus adapté aux connexions TCP de longue durée est celui du plus petit nombre de connexions (« Least Connections »), configuré à l’aide de la directive least_conn :

Chargement de l’exemple de code…

Désormais, lorsqu’un client ouvre une nouvelle connexion à la base de données, NGINX Plus choisit le nœud du cluster qui compte le moins de connexions actives.

Haute disponibilité et contrôles d’intégrité

Le principal avantage de la répartition de la charge de travail de la base de données sur un cluster est qu’elle assure également une haute disponibilité. Avec la configuration décrite ci-dessus, NGINX Plus marque un serveur comme « indisponible » et cesse de lui envoyer des paquets TCP si une nouvelle connexion TCP ne peut pas être établie.

En plus de gérer ainsi les serveurs hors service, NGINX Plus peut également être configuré pour effectuer automatiquement des contrôles d’intégrité proactifs afin que les serveurs indisponibles soient détectés avant que les requêtes des clients ne leur soient envoyées (cette fonctionnalité est réservée à NGINX Plus). En outre, la disponibilité des serveurs peut être testée au moyen d’un contrôle d’intégrité au niveau applicatif : nous pouvons ainsi envoyer une requête à chaque serveur et vérifier que la réponse reçue indique qu’il fonctionne correctement. Notre configuration est donc complétée comme suit.

Chargement de l’exemple de code…

Dans cet exemple, le bloc match définit les données de requête et de réponse nécessaires pour lancer une négociation avec la version 10 du protocole MySQL. La directive health_check du bloc server{} applique ce modèle et garantit que NGINX Plus transmet les connexions MySQL uniquement aux serveurs réellement capables d’accepter de nouvelles connexions. Dans ce cas, la vérification de l’état est effectuée toutes les 20 secondes, un serveur est exclu du pool d’équilibrage de charge TCP après un seul échec, puis réintégré après 2 vérifications de l’état consécutives réussies.

Journalisation et diagnostics

NGINX Plus offre une journalisation flexible permettant d’enregistrer l’ensemble de ses traitements TCP/UDP à des fins de débogage ou d’analyse hors ligne. Pour les protocoles TCP tels que MySQL, NGINX Plus écrit une entrée de journal à la fermeture de la connexion. La directive « log_format » définit les valeurs qui apparaissent dans les journaux. Nous pouvons choisir parmi toutes les variables disponibles pour les modules Stream. Nous définissons le format de journalisation dans le contexte stream, au début de notre fichier stream.conf.

Chargement de l’exemple de code…

La journalisation est activée en ajoutant la directive « access_log » dans le bloc server{} et en indiquant le chemin d’accès au fichier journal ainsi que le nom du format de journal défini dans l’extrait précédent.

Chargement de l’exemple de code…

Cela génère des entrées de journal telles que l’exemple ci-dessous.

$ tail -3 /var/log/nginx/galera_access.log192.168.91.1 [23/Jul/2021:17:42:18 +0100] TCP 200 369 1611 127.0.0.1:33063 0.000 0.003 12.614 12.614
192.168.91.1 [23/Jul/2021:17:42:18 +0100] TCP 200 369 8337 127.0.0.1:33061 0.001 0.001 11.181 11.181
192.168.91.1 [23/Jul/2021:17:42:19 +0100] TCP 200 369 1611 127.0.0.1:33062 0.001 0.001 10.460 10.460

Journalisation avancée avec le module JavaScript de NGINX

NGINX JavaScript est le langage de configuration programmatique « natif de NGINX ». Il s’agit d’une implémentation JavaScript unique pour NGINX et NGINX Plus, spécialement conçue pour les cas d’utilisation côté serveur et le traitement de chaque requête.

[Éditeur – Le cas d’usage suivant n’est que l’un des nombreux cas d’usage du module JavaScript NGINX. Pour consulter la liste complète, voir Cas d’usage du module JavaScript NGINX.

Cet article a été mis à jour afin d’utiliser l’objet de session (s) remanié pour le module Stream, introduit dans NGINX JavaScript 0.2.4, ainsi que la directive js_import introduite dans NGINX JavaScript 0.4.0.]

Dans le module Stream dédié à la répartition de charge TCP/UDP, NGINX JavaScript permet d’accéder au contenu des paquets de requête et de réponse. Nous pouvons ainsi examiner la requête du client correspondant à la requête SQL et en extraire des éléments utiles, tels que la commande SQL, par exemple SELECT ou UPDATE. NGINX JavaScript peut ensuite rendre ces valeurs accessibles sous forme de variables NGINX standard. Dans cet exemple, nous plaçons notre code JavaScript dans /etc/nginx/sql_method.js.

Chargement de l’exemple de code…

La fonction « getSqlMethod() » reçoit un objet JavaScript (s) qui représente le paquet actuel. Les propriétés de cet objet, telles que fromUpstream et buffer, nous fournissent les informations nécessaires sur le paquet et son contexte.

Nous vérifions d’abord que le paquet TCP provient du client, car il n’est pas nécessaire d’examiner les paquets provenant du serveur MySQL en amont. Ici, nous nous intéressons au troisième paquet du client, car les deux premiers contiennent les informations de négociation et d’authentification. Le troisième paquet du client contient la requête SQL. Le début de cette chaîne est ensuite comparé à l’une des méthodes SQL définies dans le tableau methods. Lorsqu’une correspondance est trouvée, nous stockons le résultat dans la variable globale $method et ajoutons une entrée au journal des erreurs. Les messages de journalisation de NGINX JavaScript sont consignés dans le journal des erreurs avec le niveau de gravité info et ne s’affichent donc pas par défaut.

La fonction setSqlMethod() est appelée lorsqu’une variable NGINX du même nom est évaluée. La variable reçoit alors la valeur de la variable globale NGINX JavaScript $method, obtenue lors des appels à la fonction getSqlMethod().

Notez que ce code NGINX JavaScript est conçu pour le client MySQL en ligne de commande, dans lequel une seule requête est exécutée. Il ne capture pas avec précision les requêtes complexes ni les requêtes multiples sur une connexion persistante, bien que le code puisse être adapté à ces cas d’utilisation. Pour obtenir des instructions sur l’installation et l’activation du module NGINX JavaScript, consultez le Guide d’administration de NGINX Plus.

Pour inclure la méthode SQL dans nos journaux, nous ajoutons la variable « $sql_method » à la directive « log_format ».

Chargement de l’exemple de code…

Nous devons également étendre notre configuration afin d’indiquer à NGINX Plus comment et quand exécuter le code JavaScript NGINX.

Chargement de l’exemple de code…

Nous indiquons d’abord l’emplacement du code JavaScript NGINX à l’aide de la directive js_import et utilisons la directive js_set pour demander à NGINX Plus d’appeler la fonction setSqlMethod() lorsqu’il doit évaluer la variable $sql_method. Ensuite, dans le bloc server{}, nous utilisons la directive js_filter pour spécifier la fonction qui sera appelée chaque fois qu’un paquet est traité. Nous pouvons éventuellement ajouter la directive error_log avec l’option info pour activer la journalisation JavaScript de NGINX.

Avec cette configuration supplémentaire en place, notre journal d’accès ressemble désormais à ceci.

$ tail -3 /var/log/nginx/galera_access.log192.168.91.1 [23/Jul/2021:17:42:18 +0100] TCP 200 369 1611 127.0.0.1:33063 0.000 0.003 12.614 12.614 UPDATE
192.168.91.1 [23/Jul/2021:17:42:18 +0100] TCP 200 369 8337 127.0.0.1:33061 0.001 0.001 11.181 11.181 SELECT
192.168.91.1 [23/Jul/2021:17:42:19 +0100] TCP 200 369 1611 127.0.0.1:33062 0.001 0.001 10.460 10.460 UPDATE

Tableau de bord NGINX Plus

[Éditeur – Cette section a été mise à jour pour faire référence à l’API NGINX Plus, qui remplace et rend obsolète le module étendu Status distinct initialement présenté ici.]

Outre la journalisation détaillée de l’activité MySQL, nous pouvons consulter les métriques en temps réel et l’état de nos serveurs MySQL en amont dans le tableau de bord de surveillance de l’activité en direct de NGINX Plus (NGINX Open Source fournit un ensemble plus restreint de métriques, uniquement par l’intermédiaire de l’API Stub Status).

Le tableau de bord NGINX Plus a été introduit dans NGINX Plus R7 et fournit une interface web pour l’API NGINX Plus. Pour l’activer, nous ajoutons un nouveau bloc server{} dans le contexte http, au sein d’un fichier distinct nommé /etc/nginx/conf.d/dashboard.conf :

Chargement de l’exemple de code…

Nous devons également mettre à jour le bloc server{} dans stream.conf avec la directive status_zone afin de permettre la collecte des données de surveillance pour notre service MySQL.

Chargement de l’exemple de code…

Avec cette configuration, le tableau de bord NGINX Plus est accessible sur le port 8080. Dans la capture d’écran suivante, nous pouvons voir nos trois serveurs MySQL, chacun affichant les détails de nombreuses connexions en cours ainsi que son état de santé actuel. Nous pouvons constater que le nœud à l’écoute sur le port 33062 a précédemment subi une brève interruption de 18,97 secondes (indiquée dans la colonne « DT »).

Considérations relatives aux écritures simultanées

Galera Cluster présente chaque nœud de serveur MySQL comme une base de données maître capable d’effectuer aussi bien des lectures que des écritures. Pour de nombreuses applications, le nombre de lectures est tellement supérieur à celui des écritures que le risque qu’une même ligne de table soit mise à jour simultanément par plusieurs clients est tout à fait acceptable au regard de la flexibilité offerte par un cluster de bases de données multimaître. Dans les situations où le risque d’écritures simultanées est plus élevé, deux options s’offrent à nous.

1. Créez deux groupes de serveurs en amont distincts, l’un pour les lectures et l’autre pour les écritures, chacun écoutant sur un port différent. Affectez un ou plusieurs nœuds du cluster aux écritures et incluez tous les nœuds dans le groupe de lectures. Le code client doit être mis à jour afin de sélectionner le port approprié pour les opérations de lecture et d’écriture. Cette approche est présentée dans l’article Équilibrage de charge MySQL avancé avec NGINX Plus de notre blog et convient particulièrement aux environnements à grande échelle comportant de nombreux nœuds de serveur MySQL.
2. Conservez un seul groupe de serveurs en amont et modifiez le code client afin de détecter les erreurs d’écriture. Lorsqu’une erreur d’écriture est détectée, le code applique un délai d’attente exponentiel avant de réessayer, une fois les accès concurrents terminés. Cette approche est présentée dans l’article Haute disponibilité de MySQL avec NGINX Plus et Galera Cluster sur notre blog. Elle convient particulièrement aux petits clusters, pour lesquels réserver des nœuds aux écritures compromettrait la haute disponibilité.

Résumé

Dans cet article, nous avons exploré plusieurs aspects essentiels de la répartition de charge d’une application TCP (ou UDP) telle que MySQL. NGINX Plus fournit un répartiteur de charge TCP/UDP complet pour vous aider à proposer des applications performantes, fiables, sécurisées et évolutives, quel que soit le type de trafic.

Pour essayer NGINX Plus, commencez votre essai gratuit de 30 jours dès aujourd’hui ou contactez-nous pour discuter de vos cas d’utilisation.

Annexe : création de l’environnement de test

L’environnement de test est installé sur une machine virtuelle afin d’être isolé et reproductible. Toutefois, rien n’empêche de l’installer sur un serveur physique « bare metal ».

Installation de NGINX Plus

Consultez le guide d’administration de NGINX Plus.

Installation de Galera Cluster pour MySQL

Dans cet exemple, nous installons Galera Cluster sur un seul hôte en utilisant un conteneur Docker pour chaque nœud. Les instructions suivantes sont adaptées de « Bien démarrer avec Galera et Docker » et supposent que Docker Engine ainsi que l’outil en ligne de commande MySQL sont déjà installés.

1. Créez un fichier de configuration MySQL de base (my.cnf) qui sera copié dans chaque conteneur Galera par l’image Docker.

   Chargement de l’exemple de code…

2. Téléchargez l’image Docker de base de Galera.

$ sudo docker pull erkules/galera:basic

1. Créez le premier nœud Galera (node1), en exposant le port MySQL par défaut sur le port 33061.

$ sudo docker run -p 33061:3306 --detach=true --name node1 -h node1 erkules/galera:basic --wsrep-cluster-name=local-test --wsrep-cluster-address=gcomm://

1. Créez le deuxième nœud Galera (node2). Le port MySQL est exposé sur le port 33062 et relié à node1 pour permettre la communication au sein du cluster.

$ sudo docker run -p 33062:3306 --detach=true --name node2 -h node2 --link node1:node1 erkules/galera:basic --wsrep-cluster-name=local-test --wsrep-cluster-address=gcomm://node1

1. Créez le troisième et dernier nœud Galera (node3) de la même manière que node2. Le port MySQL est exposé sur le port 33063.

$ sudo docker run -p 33063:3306 --detach=true --name node3 -h node3 --link node1:node1 erkules/galera:basic --wsrep-cluster-name=local-test --wsrep-cluster-address=gcomm://node1

1. Créez un compte utilisateur nommé « nginx » permettant d’accéder à distance au cluster depuis l’hôte. Pour ce faire, exécutez la commande « mysql(1) » depuis le conteneur Docker lui-même.

$ sudo docker exec -ti node1 mysql -e"GRANT ALL PRIVILEGES ON *.* TO 'nginx'@'172.17.0.1' IDENTIFIED BY 'plus'"

1. Vérifiez que vous pouvez vous connecter au cluster Galera depuis l’hôte à l’aide du protocole TCP.

$ mysql --protocol=tcp -P 33061 --user=nginx --password=plus -e "SHOW DATABASES"mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

1. Enfin, exécutez la même commande sur un autre nœud du cluster afin de vérifier que le compte utilisateur nginx a bien été répliqué et que le cluster fonctionne correctement.

$ mysql --protocol=tcp -P 33062 --user=nginx --password=plus -e "SHOW DATABASES"mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

À propos de l’auteur

Liam CrillyDirecteur senior de la gestion des produits

Autres articles de Liam Crilly