Mise à l'échelle de MySQL avec l'équilibrage de charge TCP et le cluster Galera

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

Nous avons introduit l'équilibrage de charge TCP dans NGINX Plus R5 et avons continuellement ajouté des fonctionnalités dans les versions ultérieures, ainsi que la prise en charge de l'équilibrage de charge UDP . Dans cet article, nous explorons les exigences clés de l’équilibrage de charge TCP et la manière dont NGINX Plus les répond.

Pour explorer les fonctionnalités de NGINX Plus, nous utiliserons un environnement de test simple qui représente les composants clés d'une application avec un backend de base de données à l'échelle. Pour obtenir des instructions complètes sur la création de l’environnement de test, consultez l’ annexe .

Dans cet environnement, NGINX Plus agit comme un proxy inverse pour le serveur de base de données, écoutant sur le port MySQL par défaut de 3306. Cela fournit une interface simple au client, tandis que les nœuds MySQL back-end peuvent être mis à l'échelle (et même mis hors ligne) sans affecter le client de quelque manière que ce soit. Nous utilisons l’ outil de ligne de commande MySQL comme client, qui représente l’application frontale dans l’environnement de test.

La plupart des fonctionnalités décrites dans cet article s’appliquent à la fois à NGINX Open Source et à NGINX Plus. Par souci de concision, nous ferons référence à NGINX Plus tout au long de cet article et indiquerons explicitement les fonctionnalités qui ne sont pas disponibles dans NGINX Open Source.

Nous explorerons les cas d’utilisation suivants :

• Équilibrage de charge TCP
• Haute disponibilité et contrôles de santé
• Journalisation et diagnostic
• 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 l’application se connecte à la base de données. Pour la plupart de nos tests, nous utilisons l’outil de 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 d’application utilisent un pool de connexions pour minimiser la latence et utiliser efficacement les ressources du serveur de base de données.

L'équilibrage de charge TCP est configuré dans le contexte de configuration de flux , nous créons donc notre configuration d'équilibrage de charge MySQL de base en ajoutant un bloc de flux au fichier nginx.conf principal.

Cela sépare notre configuration d'équilibrage 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 et donc l'ajout de fichiers de configuration de flux à ce répertoire ne fonctionne pas.

Nous définissons d’abord un groupe en amont nommé galera_cluster , contenant les trois nœuds MySQL de notre cluster Galera. Dans notre environnement de test, ils sont chacun accessibles sur localhost avec un numéro de port unique. La directive de zone définit une quantité de mémoire partagée entre tous les processus de travail NGINX Plus pour maintenir l’état d’équilibrage de charge. Le bloc serveur{} configure la manière dont NGINX Plus traite les clients. NGINX Plus écoute sur le port MySQL par défaut, 3306, et transmet tout le trafic au cluster Galera défini dans le bloc en amont .

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

$ echo "AFFICHER LES VARIABLES OÙ Nom_variable = 'nom_hôte'" | mysql --protocol=tcp --user=nginx --password=plus -N 2> /dev/null nom_hôte node1

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

$ !!;!!;!! nom d'hôte nœud2 nom d'hôte nœud3 nom d'hôte nœud1

Cela montre que l’algorithme d’équilibrage de charge round-robin par défaut fonctionne correctement. Cependant, 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 de manière circulaire est susceptible d’entraîner un nombre déséquilibré de connexions sur chaque nœud. De plus, nous ne pouvons pas assimiler une connexion à une charge de travail donnée, car les connexions peuvent être inactives (en attente d’une requête de l’application) ou occupées à traiter une requête. Un algorithme d’équilibrage de charge plus approprié pour les connexions TCP de longue durée est Least Connections , configuré avec la directive least_conn :

Désormais, lorsqu’un client ouvre une nouvelle connexion à la base de données, NGINX Plus choisit le nœud de cluster avec le plus petit nombre de connexions actuelles.

Haute disponibilité et contrôles de santé

Le grand avantage du partage de la charge de travail de la base de données sur un cluster est qu’il offre également une haute disponibilité . Avec la configuration décrite ci-dessus, NGINX Plus marque un serveur comme « hors service » et arrête de lui envoyer des paquets TCP si une nouvelle connexion TCP ne peut pas être établie .

En plus de gérer les serveurs en panne de cette manière, NGINX Plus peut également être configuré pour effectuer des contrôles de santé automatiques et proactifs afin que les serveurs indisponibles soient détectés avant que les demandes des clients ne leur soient envoyées (il s'agit d'une fonctionnalité exclusive de NGINX Plus). De plus, la disponibilité des serveurs peut être testée à l’aide d’un contrôle de santé au niveau de l’application, ce qui signifie que nous pouvons envoyer une requête à chaque serveur et vérifier que nous obtenons une réponse indiquant une bonne santé. Cela étend notre configuration comme suit.

Dans cet exemple, le bloc de correspondance définit les données de demande et de réponse requises pour lancer une négociation avec le protocole MySQL version 10 . La directive health_check dans le 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, nous effectuons le contrôle d'intégrité toutes les 20 secondes, excluons un serveur du pool d'équilibrage de charge TCP après une seule défaillance et reprenons l'équilibrage de charge sur celui-ci après 2 contrôles d'intégrité réussis consécutifs.

Journalisation et diagnostics

NGINX Plus fournit une journalisation flexible afin que tous ses traitements TCP/UDP puissent être enregistrés à 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 lorsque la connexion est fermée. La directive log_format définit les valeurs qui apparaissent dans les journaux. Nous pouvons choisir parmi n’importe laquelle des variables disponibles pour les modules Stream. Nous définissons le format du journal dans le contexte du flux , en haut de notre fichier stream.conf .

La journalisation est activée en ajoutant la directive access_log dans le bloc server{} , en spécifiant le chemin d'accès au fichier journal et le nom du format de journal défini dans l'extrait précédent.

Cela produit des entrées de journal telles que l’exemple ci-dessous.

$ tail -3 /var/log/nginx/galera_access.log 192.168.91.1 [23/juillet/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/juillet/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/juillet/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 NGINX

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

[ Éditeur – Le cas d’utilisation suivant n’est qu’un parmi tant d’autres pour le module JavaScript NGINX. Pour obtenir la liste de tous les cas d’utilisation, consultez Cas d’utilisation du module JavaScript NGINX .

Cet article a été mis à jour pour utiliser l'objet de session ( s ) refactorisé pour le module Stream introduit dans NGINX JavaScript 0.2.4 et la directive js_import introduite dans NGINX JavaScript 0.4.0.]

Dans le module Stream pour l'équilibrage de charge TCP/UDP, NGINX JavaScript fournit un accès au contenu des paquets de demande et de réponse. Cela signifie que nous pouvons examiner la requête client correspondant à la requête SQL et extraire des éléments utiles tels que la méthode SQL, par exemple SELECT ou UPDATE . NGINX JavaScript peut alors rendre ces valeurs disponibles en tant que variables NGINX standard. Dans cet exemple, nous mettons notre code JavaScript dans /etc/nginx/sql_method.js .

La fonction getSqlMethod() reçoit un ou plusieurs objets JavaScript qui représentent le paquet actuel. Les propriétés de cet objet telles que fromUpstream et buffer nous fournissent les informations dont nous avons besoin sur le paquet et son contexte.

Nous vérifions d’abord que le paquet TCP provient du client, car nous n’avons pas besoin d’examiner les paquets provenant du serveur MySQL en amont. Ici, nous nous intéressons au troisième paquet client car les deux premiers paquets contiennent des informations de négociation et d’authentification. Le troisième paquet 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 des méthodes . Lorsque nous trouvons une correspondance, nous stockons le résultat dans la variable globale $method et écrivons une entrée dans le journal des erreurs. La journalisation JavaScript NGINX est écrite dans le journal des erreurs au niveau des informations de gravité et n'apparaît donc pas par défaut.

La fonction setSqlMethod() est appelée lorsqu'une variable NGINX du même nom est évaluée. Lorsque cela se produit, la variable est renseignée par la variable globale JavaScript NGINX $method qui a été obtenue à partir d'appels à la fonction getSqlMethod() .

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

Pour inclure la méthode SQL dans nos journaux, nous incluons la variable $sql_method dans la directive log_format .

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

Nous spécifions d’abord l’emplacement du code JavaScript NGINX avec la directive js_import et utilisons la directive js_set pour indiquer à 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é. En option, nous pouvons ajouter la directive error_log avec l'option info pour activer la journalisation JavaScript NGINX.

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

$ tail -3 /var/log/nginx/galera_access.log 192.168.91.1 [23/juillet/2021:17:42:18 +0100] TCP 200 369 1611 127.0.0.1:33063 0,000 0,003 12,614 12,614 MISE À JOUR 192.168.91.1 [23/juillet/2021:17:42:18 +0100] TCP 200 369 8337 127.0.0.1:33061 0,001 0,001 11,181 11,181 SÉLECTIONNEZ 192.168.91.1 [23/07/2021:17:42:19 +0100] TCP 200 369 1611 127.0.0.1:33062 0.001 0.001 10.460 10.460 MISE À JOUR

Tableau de bord NGINX Plus

[ Éditeur – Cette section a été mise à jour pour faire référence à l' API NGINX Plus , qui remplace et déprécie le module d'état étendu distinct initialement décrit ici. ]

En plus d'enregistrer l'activité MySQL en détail, nous pouvons observer les métriques en temps réel et l'état de nos serveurs MySQL en amont sur le tableau de bord de surveillance de l'activité en direct NGINX Plus (NGINX Open Source fournit un ensemble plus restreint de métriques et via l' API Stub Status uniquement).

Le tableau de bord NGINX Plus a été introduit dans NGINX Plus R7 et fournit une interface Web à l' API NGINX Plus . Nous l'activons en ajoutant un nouveau bloc server{} dans le contexte http dans un fichier /etc/nginx/conf.d/dashboard.conf distinct :

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

Avec cette configuration en place, le tableau de bord NGINX Plus est disponible 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 et l'état de santé actuel. Nous pouvons voir que le nœud écoutant sur le port 33062 a déjà connu une brève panne de 18,97 secondes (signalé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 qui effectue à la fois des lectures et des écritures. Pour de nombreuses applications, le rapport entre les lectures et les écritures est si élevé que le risque que la même ligne de table soit mise à jour par plusieurs clients en même temps est tout à fait acceptable par rapport à la flexibilité offerte par un cluster de base de données multimaître. Dans les situations où le risque d’écritures simultanées est plus élevé, nous avons deux options.

1. Créez deux groupes en amont distincts, un pour les lectures et un pour les écritures, chacun écoutant sur un port différent. Dédiez un ou plusieurs nœuds du cluster aux écritures, avec tous les nœuds inclus dans le groupe de lectures. Le code client doit être mis à jour pour sélectionner le port approprié pour les opérations de lecture et d'écriture. Cette approche est abordée dans Équilibrage de charge MySQL avancé avec NGINX Plus sur notre blog et favorise un environnement hautement évolutif avec de nombreux nœuds de serveur MySQL.
2. Conservez un seul groupe en amont et modifiez le code client pour détecter les erreurs d’écriture. Lorsqu'une erreur d'écriture est détectée, le code recule de manière exponentielle avant de réessayer, une fois la concurrence terminée. Cette approche est abordée dans MySQL High Availability avec NGINX Plus et Galera Cluster sur notre blog et privilégie un petit cluster, où dédier des nœuds de cluster aux écritures compromettrait la haute disponibilité.

Résumé

Dans cet article, nous avons exploré plusieurs des aspects essentiels de l'équilibrage de charge d'une application TCP (ou UDP) telle que MySQL. NGINX Plus fournit un équilibreur de charge TCP/UDP complet pour vous aider à fournir des applications avec performances, fiabilité, sécurité et évolutivité, quel que soit le type de trafic.

Pour essayer NGINX Plus, démarrez 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 qu'il soit isolé et répétable. Cependant, il n’y a aucune raison pour qu’il ne puisse pas être installé 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 à l’aide de conteneurs Docker pour chaque nœud. Les instructions suivantes sont adaptées de Prise en main de Galera avec Docker et supposent que Docker Engine et l' outil de ligne de commande MySQL sont déjà installés.

1. Créez un fichier de configuration MySQL de base ( my.cnf ) à copier dans chaque conteneur Galera par l'image Docker.

    

2. Extrayez l’image Docker de base de Galera.

   $ sudo docker pull erkules/galera:basic
   

3. Créez le premier nœud Galera ( node1 ), en exposant le port MySQL par défaut comme 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://
   

4. Créez le deuxième nœud Galera ( node2 ). Le port MySQL est exposé comme 33062 et lié à node1 pour la communication intercluster.

   $ 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
   

5. Créez le troisième et dernier nœud Galera ( node3 ) de la même manière que node2 . Le port MySQL est exposé comme 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
   

6. Créez un compte utilisateur appelé nginx qui peut être utilisé pour l'accès à distance au cluster depuis l'hôte. Cela s'effectue en exécutant la commande mysql(1) depuis le conteneur Docker lui-même.

   $ sudo docker exec -ti node1 mysql -e"ACCORDER TOUS LES PRIVILÈGES SUR *.* À 'nginx'@'172.17.0.1' IDENTIFIÉ PAR 'plus'"
   

7. Vérifiez que vous pouvez vous connecter au cluster Galera depuis l'hôte, en utilisant le protocole TCP.

   $ mysql --protocol=tcp -P 33061 --user=nginx --password=plus -e "SHOW DATABASES" mysql : [Avertissement] L'utilisation d'un mot de passe sur l'interface de ligne de commande peut être non sécurisée. +--------------------+ | Base de données | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+
   

8. Enfin, exécutez la même commande sur un autre nœud du cluster pour montrer que le compte utilisateur nginx a été répliqué et que le cluster fonctionne correctement.

   $ mysql --protocol=tcp -P 33062 --user=nginx --password=plus -e "SHOW DATABASES" mysql : [Avertissement] L'utilisation d'un mot de passe sur l'interface de ligne de commande peut être non sécurisée. +--------------------+ | Base de données | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+