MySQL mit TCP-Lastverteilung und Galera Cluster skalieren

Liam CrillySenior Director, Produktmanagement

[Redaktion – Dieser ursprünglich 2016 veröffentlichte Beitrag wurde aktualisiert und verwendet nun NGINX-Funktionen, die seitdem überarbeitet wurden. Weitere Informationen finden Sie unter „Erweiterte Protokollierung mit dem NGINX-JavaScript-Modul“ und „NGINX Plus-Dashboard“. ]

Wir haben den TCP-Lastenausgleich mit NGINX Plus R5 eingeführt und in den nachfolgenden Versionen kontinuierlich um weitere Funktionen sowie die Unterstützung für den UDP-Lastenausgleich erweitert. In diesem Artikel untersuchen wir die wichtigsten Anforderungen an den TCP-Lastenausgleich und wie NGINX Plus diese erfüllt.

Um die Funktionen von NGINX Plus zu untersuchen, verwenden wir eine einfache Testumgebung, die die wichtigsten Komponenten einer Anwendung mit einem skalierten Datenbank-Backend abbildet. Eine vollständige Anleitung zum Aufbau der Testumgebung finden Sie im Anhang.

In dieser Umgebung fungiert NGINX Plus als Reverse-Proxy für den Datenbankserver und lauscht auf dem standardmäßigen MySQL-Port 3306. Dadurch erhält der Client eine einfache Schnittstelle, während die MySQL-Knoten im Backend horizontal skaliert (und sogar offline genommen) werden können, ohne dass dies Auswirkungen auf den Client hat. Wir verwenden das MySQL-Befehlszeilentool als Client, der in der Testumgebung die Frontend-Anwendung repräsentiert.

Viele der in diesem Artikel beschriebenen Funktionen gelten sowohl für NGINX Open Source als auch für NGINX Plus. Der Kürze halber sprechen wir im Folgenden durchgehend von NGINX Plus und weisen ausdrücklich auf Funktionen hin, die in NGINX Open Source nicht verfügbar sind.

Wir werden die folgenden Anwendungsfälle untersuchen:

• TCP-Lastverteilung
• Hochverfügbarkeit und Zustandsprüfungen
• Protokollierung und Diagnose
• Überlegungen zu gleichzeitigen Schreibvorgängen

TCP-Lastverteilung

Bevor Sie den Lastenausgleich für eine Anwendung konfigurieren, sollten Sie wissen, wie die Anwendung eine Verbindung zur Datenbank herstellt. Für die meisten unserer Tests verwenden wir das MySQL-Befehlszeilentool mysql(1), um eine Verbindung zum Galera-Cluster herzustellen, eine Abfrage auszuführen und die Verbindung anschließend zu schließen. Viele Anwendungsframeworks verwenden jedoch einen Verbindungspool, um die Latenz zu minimieren und die Ressourcen des Datenbankservers effizient zu nutzen.

Der TCP-Lastenausgleich wird im Konfigurationskontext stream konfiguriert. Daher erstellen wir unsere grundlegende MySQL-Konfiguration für den Lastenausgleich, indem wir der Hauptdatei stream einen nginx.conf-Block hinzufügen.

Codebeispiel wird geladen…

Dadurch wird unsere TCP-Load-Balancing-Konfiguration von der Hauptkonfigurationsdatei getrennt. Anschließend erstellen wir stream.conf im selben Verzeichnis wie nginx.conf. Beachten Sie, dass das Verzeichnis conf.d standardmäßig für den Konfigurationskontext http reserviert ist. Daher funktioniert es nicht, diesem Verzeichnis stream-Konfigurationsdateien hinzuzufügen.

Codebeispiel wird geladen…

Zunächst definieren wir eine upstream-Gruppe namens galera_cluster, die die drei MySQL-Knoten in unserem Galera-Cluster enthält. In unserer Testumgebung sind sie jeweils über localhost mit einer eindeutigen Portnummer erreichbar. Die Direktive zone legt einen Speicherbereich fest, der von allen NGINX Plus-Worker-Prozessen gemeinsam genutzt wird, um den Status des Lastenausgleichs zu verwalten. Der Block server{} konfiguriert, wie NGINX Plus mit Clients umgeht. NGINX Plus lauscht am standardmäßigen MySQL-Port 3306 und leitet den gesamten Datenverkehr an den im upstream-Block definierten Galera-Cluster weiter.

Um zu testen, ob diese Basiskonfiguration funktioniert, können wir mit dem MySQL-Client den Hostnamen des Knotens im Galera-Cluster ausgeben, mit dem wir verbunden sind.

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

Um zu überprüfen, ob der Lastenausgleich funktioniert, können wir denselben Befehl erneut ausführen.

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

Dies zeigt, dass der standardmäßige Round-Robin-Algorithmus für den Lastenausgleich ordnungsgemäß funktioniert. Wenn unsere Anwendung jedoch einen Verbindungspool für den Zugriff auf die Datenbank verwendet (wie oben vorgeschlagen), führt das Öffnen von Verbindungen zum Cluster nach dem Round-Robin-Prinzip wahrscheinlich zu einer unausgewogenen Anzahl von Verbindungen auf den einzelnen Knoten. Zudem kann eine Verbindung nicht mit einer bestimmten Arbeitslast gleichgesetzt werden, da Verbindungen entweder inaktiv sein können (und auf eine Abfrage der Anwendung warten) oder gerade eine Abfrage verarbeiten. Ein besser geeigneter Lastenausgleichsalgorithmus für langlebige TCP-Verbindungen ist Least Connections, der mit der Direktive least_conn konfiguriert wird:

Codebeispiel wird geladen…

Wenn ein Client nun eine neue Verbindung zur Datenbank herstellt, wählt NGINX Plus den Clusterknoten mit der geringsten Anzahl aktuell bestehender Verbindungen aus.

Hochverfügbarkeit und Systemzustandsprüfungen

Der große Vorteil der Verteilung der Datenbanklast auf einen Cluster besteht darin, dass dadurch auch Hochverfügbarkeit gewährleistet wird. Bei der oben beschriebenen Konfiguration markiert NGINX Plus einen Server als „nicht verfügbar“ und sendet keine TCP-Pakete mehr an ihn, wenn keine neue TCP-Verbindung hergestellt werden kann.

Zusätzlich zur Behandlung ausgefallener Server auf diese Weise kann NGINX Plus auch so konfiguriert werden, dass automatische, proaktive Systemdiagnosen durchgeführt werden, damit nicht verfügbare Server erkannt werden, bevor Clientanfragen an sie gesendet werden (diese Funktion ist ausschließlich in NGINX Plus verfügbar). Darüber hinaus lässt sich die Verfügbarkeit der Server mit einer Systemdiagnose auf Anwendungsebene testen. Dazu senden wir eine Anfrage an jeden Server und prüfen, ob wir eine Antwort erhalten, die einen ordnungsgemäßen Zustand bestätigt. Dadurch wird unsere Konfiguration wie folgt erweitert.

Codebeispiel wird geladen…

In diesem Beispiel definiert der match-Block die Anfrage- und Antwortdaten, die zum Einleiten eines Handshakes gemäß MySQL-Protokoll Version 10 erforderlich sind. Die health_check-Direktive im server{}-Block wendet dieses Muster an und stellt sicher, dass NGINX Plus MySQL-Verbindungen nur an Server weiterleitet, die tatsächlich neue Verbindungen annehmen können. In diesem Fall führen wir die Integritätsprüfung alle 20 Sekunden durch, schließen einen Server nach einem einzigen Fehler aus dem TCP-Load-Balancing-Pool aus und nehmen das Load Balancing zu diesem Server nach 2 aufeinanderfolgenden erfolgreichen Integritätsprüfungen wieder auf.

Protokollierung und Diagnose

NGINX Plus bietet eine flexible Protokollierung, sodass die gesamte TCP/UDP-Verarbeitung zur Fehlerbehebung oder Offline-Analyse aufgezeichnet werden kann. Bei TCP-Protokollen wie MySQL schreibt NGINX Plus einen Protokolleintrag, wenn die Verbindung geschlossen wird. Die Direktive „log_format“ legt fest, welche Werte in den Protokollen erscheinen. Wir können aus allen Variablen auswählen, die für die Stream-Module verfügbar sind. Wir definieren das Protokollformat im Kontext „stream“ am Anfang unserer Datei stream.conf.

Codebeispiel wird geladen…

Die Protokollierung wird durch Hinzufügen der Direktive „access_log“ im Block „server{}“ aktiviert. Dabei werden der Pfad zur Protokolldatei und der Name des im vorherigen Codeausschnitt definierten Protokollformats angegeben.

Codebeispiel wird geladen…

Dadurch werden Protokolleinträge wie im folgenden Beispiel erzeugt.

$ 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

Erweiterte Protokollierung mit dem NGINX-JavaScript-Modul

NGINX JavaScript ist die „NGINX-native“ Sprache für die programmatische Konfiguration. Es handelt sich um eine einzigartige JavaScript-Implementierung für NGINX und NGINX Plus, die speziell für serverseitige Anwendungsfälle und die Verarbeitung einzelner Anfragen entwickelt wurde.

[Redaktion – Der folgende Anwendungsfall ist nur einer von vielen für das NGINX-JavaScript-Modul. Eine Liste aller Anwendungsfälle finden Sie unter „Anwendungsfälle für das NGINX-JavaScript-Modul“.

Dieser Beitrag wurde aktualisiert und verwendet nun das überarbeitete Sitzungsobjekt (s) für das Stream-Modul, das mit NGINX JavaScript 0.2.4 eingeführt wurde, sowie die mit js_import eingeführte Direktive NGINX JavaScript 0.4.0.]

Innerhalb des Stream-Moduls für den TCP/UDP-Lastenausgleich ermöglicht NGINX JavaScript den Zugriff auf den Inhalt von Anfrage- und Antwortpaketen. Dadurch können wir die zur SQL-Abfrage gehörende Clientanfrage untersuchen und nützliche Elemente wie die SQL-Methode extrahieren, zum Beispiel SELECT oder UPDATE. NGINX JavaScript kann solche Werte anschließend als reguläre NGINX-Variablen bereitstellen. In diesem Beispiel speichern wir unseren JavaScript-Code in /etc/nginx/sql_method.js.

Codebeispiel wird geladen…

Der Funktion „getSqlMethod()“ wird ein JavaScript-Objekt (s) übergeben, das das aktuelle Paket darstellt. Eigenschaften dieses Objekts wie fromUpstream und buffer liefern uns die benötigten Informationen über das Paket und seinen Kontext.

Zunächst prüfen wir, ob das TCP-Paket vom Client stammt, da Pakete vom vorgeschalteten MySQL-Server nicht untersucht werden müssen. Hier interessiert uns das dritte Client-Paket, da die ersten beiden Pakete Handshake- und Authentifizierungsinformationen enthalten. Das dritte Client-Paket enthält die SQL-Abfrage. Anschließend wird der Anfang dieser Zeichenfolge mit einer der im Array methods definierten SQL-Methoden verglichen. Bei einer Übereinstimmung speichern wir das Ergebnis in der globalen Variablen $method und schreiben einen Eintrag in das Fehlerprotokoll. Die Protokollierung von NGINX JavaScript erfolgt im Fehlerprotokoll mit dem Schweregrad info und wird daher standardmäßig nicht angezeigt.

Die Funktion setSqlMethod() wird aufgerufen, wenn eine gleichnamige NGINX-Variable ausgewertet wird. In diesem Fall wird der Variablen der Wert der globalen NGINX-JavaScript-Variablen $method zugewiesen, der durch Aufrufe der Funktion getSqlMethod() ermittelt wurde.

Beachten Sie, dass dieser NGINX-JavaScript-Code für den MySQL-Befehlszeilenclient konzipiert ist, in dem eine einzelne Abfrage ausgeführt wird. Komplexe Abfragen oder mehrere Abfragen über eine langlebige Verbindung werden damit nicht korrekt erfasst. Der Code könnte jedoch für diese Anwendungsfälle angepasst werden. Anweisungen zur Installation und Aktivierung des NGINX-JavaScript-Moduls finden Sie im NGINX Plus-Administratorhandbuch.

Um die SQL-Methode in unsere Protokolle aufzunehmen, fügen wir die Variable „$sql_method“ in die Direktive „log_format“ ein.

Codebeispiel wird geladen…

Außerdem müssen wir unsere Konfiguration erweitern, um NGINX Plus mitzuteilen, wie und wann der NGINX-JavaScript-Code ausgeführt werden soll.

Codebeispiel wird geladen…

Zunächst geben wir mit der Direktive js_import den Speicherort des NGINX JavaScript-Codes an und weisen NGINX Plus mit der Direktive js_set an, die Funktion setSqlMethod() aufzurufen, wenn die Variable $sql_method ausgewertet werden muss. Anschließend verwenden wir innerhalb des Blocks server{} die Direktive js_filter, um die Funktion anzugeben, die bei jeder Verarbeitung eines Pakets aufgerufen wird. Optional können wir die Direktive error_log mit der Option info hinzufügen, um die Protokollierung von NGINX JavaScript zu aktivieren.

Mit dieser zusätzlichen Konfiguration sieht unser Zugriffsprotokoll nun wie folgt aus.

$ 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

NGINX Plus-Dashboard

[Redaktion – Dieser Abschnitt wurde aktualisiert und verweist nun auf die NGINX Plus API, die das ursprünglich hier behandelte separate erweiterte Statusmodul ersetzt und als veraltet kennzeichnet.]

Neben der detaillierten Protokollierung der MySQL-Aktivitäten können wir auf dem Live-Aktivitätsüberwachungs-Dashboard von NGINX Plus Echtzeitmetriken und den Zustand unserer vorgelagerten MySQL-Server einsehen (NGINX Open Source stellt nur einen kleineren Satz von Metriken über die Stub Status API bereit).

Das NGINX Plus-Dashboard wurde mit NGINX Plus R7 eingeführt und bietet eine Weboberfläche für die NGINX Plus API. Wir aktivieren es, indem wir einen neuen server{}-Block im http-Kontext in einer separaten Datei namens /etc/nginx/conf.d/dashboard.conf hinzufügen:

Codebeispiel wird geladen…

Wir müssen außerdem den server{}-Block in stream.conf um die Direktive status_zone ergänzen, damit Überwachungsdaten für unseren MySQL-Dienst erfasst werden können.

Codebeispiel wird geladen…

Mit dieser Konfiguration ist das NGINX Plus-Dashboard über Port 8080 verfügbar. Im folgenden Screenshot sind unsere drei MySQL-Server zu sehen. Für jeden werden Details zu zahlreichen aktiven Verbindungen sowie der aktuelle Integritätsstatus angezeigt. Wir sehen, dass der Knoten, der Port 33062 überwacht, zuvor einen kurzen Ausfall von 18,97 Sekunden hatte (angezeigt in der Spalte „DT“).

Überlegungen zu gleichzeitigen Schreibvorgängen

Galera Cluster stellt jeden MySQL-Serverknoten als primäre Datenbank bereit, die sowohl Lese- als auch Schreibvorgänge ausführt. Bei vielen Anwendungen ist das Verhältnis von Lese- zu Schreibvorgängen so hoch, dass das Risiko, dass dieselbe Tabellenzeile gleichzeitig von mehreren Clients aktualisiert wird, angesichts der Flexibilität eines Multimaster-Datenbankclusters durchaus vertretbar ist. In Situationen, in denen ein höheres Risiko gleichzeitiger Schreibvorgänge besteht, haben wir zwei Möglichkeiten.

1. Erstellen Sie zwei separate Upstream-Gruppen, eine für Lese- und eine für Schreibvorgänge, die jeweils einen anderen Port überwachen. Weisen Sie einen oder mehrere Knoten im Cluster den Schreibvorgängen zu und nehmen Sie alle Knoten in die Lesegruppe auf. Der Clientcode muss so aktualisiert werden, dass er für Lese- und Schreibvorgänge den jeweils passenden Port auswählt. Dieser Ansatz wird im Beitrag Erweiterter MySQL-Lastenausgleich mit NGINX Plus in unserem Blog erläutert und eignet sich besonders für stark skalierte Umgebungen mit vielen MySQL-Serverknoten.
2. Behalten Sie eine einzige Upstream-Gruppe bei und passen Sie den Clientcode so an, dass er Schreibfehler erkennt. Wird ein Schreibfehler erkannt, wartet der Code mit exponentiell zunehmenden Zeitabständen, bevor er es nach Beendigung des gleichzeitigen Zugriffs erneut versucht. Dieser Ansatz wird im Beitrag MySQL-Hochverfügbarkeit mit NGINX Plus und Galera Cluster in unserem Blog erläutert und eignet sich besonders für kleine Cluster, bei denen die ausschließliche Zuweisung von Clusterknoten für Schreibvorgänge die Hochverfügbarkeit beeinträchtigen würde.

Zusammenfassung

In diesem Artikel haben wir einige der wesentlichen Aspekte des Lastenausgleichs für eine TCP- (oder UDP-)Anwendung wie MySQL untersucht. NGINX Plus bietet einen voll ausgestatteten TCP/UDP-Load-Balancer, mit dem Sie Anwendungen unabhängig von der Art des Datenverkehrs leistungsstark, zuverlässig, sicher und skalierbar bereitstellen können.

Wenn Sie NGINX Plus ausprobieren möchten, starten Sie noch heute Ihre kostenlose 30-Tage-Testversion oder kontaktieren Sie uns, um Ihre Anwendungsfälle zu besprechen.

Anhang: Erstellen der Testumgebung

Die Testumgebung wird auf einer virtuellen Maschine installiert, damit sie isoliert und reproduzierbar ist. Sie kann jedoch ebenso auf einem physischen „Bare-Metal“-Server installiert werden.

NGINX Plus installieren

Weitere Informationen finden Sie im NGINX Plus-Administratorhandbuch.

Galera Cluster für MySQL installieren

In diesem Beispiel installieren wir Galera Cluster auf einem einzelnen Host und verwenden für jeden Knoten einen Docker-Container. Die folgende Anleitung basiert auf „Erste Schritte mit Galera und Docker“ und setzt voraus, dass sowohl Docker Engine als auch das MySQL-Befehlszeilentool bereits installiert sind.

1. Erstellen Sie eine grundlegende MySQL-Konfigurationsdatei (my.cnf), die vom Docker-Image in jeden Galera-Container kopiert wird.

   Codebeispiel wird geladen…

2. Rufen Sie das Galera-Basis-Docker-Image ab.

$ sudo docker pull erkules/galera:basic

1. Erstellen Sie den ersten Galera-Knoten (node1) und stellen Sie den standardmäßigen MySQL-Port als 33061 bereit.

$ 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. Erstellen Sie den zweiten Galera-Knoten (node2). Der MySQL-Port wird als Port 33062 bereitgestellt und für die Kommunikation innerhalb des Clusters mit node1 verknüpft.

$ 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. Erstellen Sie den dritten und letzten Galera-Knoten (node3) auf die gleiche Weise wie node2. Der MySQL-Port wird als Port 33063 bereitgestellt.

$ 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. Erstellen Sie ein Benutzerkonto namens „nginx“, das für den Remotezugriff vom Host auf den Cluster verwendet werden kann. Führen Sie dazu den Befehl „mysql(1)“ im Docker-Container selbst aus.

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

1. Stellen Sie sicher, dass Sie vom Host aus über das TCP-Protokoll eine Verbindung zum Galera-Cluster herstellen können.

$ 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. Führen Sie abschließend denselben Befehl auf einem anderen Knoten des Clusters aus, um zu überprüfen, dass das Benutzerkonto nginx repliziert wurde und der Cluster ordnungsgemäß funktioniert.

$ 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 |
+--------------------+

Über den Autor

Liam CrillySenior Director, Produktmanagement

Weitere Blogbeiträge von Liam Crilly