ProxySQL: Auch Umwege führen zum Ziel

proxysql-title

Bei einem der letzten Beratungseinsätze in Sachen MySQL haben wir bei einem Kunden einen MySQL-Server analysiert, auf dem mehrere Tausend interne Mandanten in eigenen Schemata betrieben wurden. Das Setup war zum Glück recht einfach und transparent: 1 Mandant = 1 Schema = 1 Account (User).
Das System war über die Zeit stark gewachsen (Anzahl Mandanten und Datenvolumen) und hatte diverse Performanceprobleme (I/O, CPU, Metadaten Locks, ...).

Teile und herrsche (wieder).

Um die Probleme schnell in den Griff zu bekommen, wurde die Entscheidung getroffen, das System in mehrere kleinere Datenbanken zu zerlegen. Da es jedoch keine Übersicht über die Clients (Applikationsuser, direkte Anwender mit SQL-Clients, Excel, ...), geschweige denn einen zentralen Konfigurationsmechanismus für all diese gab (z.B. Ansible), sollte die Änderung für die Mandaten (Clients) transparent erfolgen.
Aus diesem Grund haben wir uns entschlossen, das Problem mit dem Tool ProxySQL zu lösen.
ProxySQL (http://www.proxysql.com) ist ein zwischen Client und Server geschalteter Dienst, der unterschiedliche Aufgaben übernehmen kann:
  • Load Balancing
  • Failover
  • Firewall Aufgaben
  • Query Rules
  • Sharding
  • ...

Die Umleitung wird berechnet....

An dieser Stelle soll die "Fast-Routing"-Funktion des Proxys genutzt werden. Der Proxy soll die unterschiedlichen Mandanten (User) erkennen und auf die verschiedenen Datenbankserver verteilen, auf denen sich die entsprechenden Datenbanken der Mandaten befinden. Letztendlich wird der Proxy so konfiguriert, dass er die IP und den Port des alten Servers übernimmt und die User anhand ihres User-Namens identifiziert und auf die neuen Server weiterleitet.

Die Installation des Proxys erfolgt über entsprechend bereitgestellte Repositories und ist damit mit ein, zwei Kommandos erledigt:

https://proxysql.com/documentation/installing-proxysql/

Im Anschluss an die Installation werden die DB-User-Accounts und die Server (Shards) bekannt gemacht bzw. konfiguriert. Dies geschieht über SQL-Kommandos. ProxySQL speichert die Konfiguration in einer SQL-Engine (SQLite). Praktischerweise kann für diese Aufgabe der MySQL Client direkt genutzt werden.

In unserem Beispiel laufen ProxySQL und zwei Datenbanken auf der selben virtuellen Maschine. Hier der Überblick über die verwendeten Ports:

# Port Aufgabe/System
1 3306 DB Server 1
23307DB Server 2
36032Admin-Port ProxySQL
46033Dienst-Port ProxySQL; über diesen werden die Verbindungen aufgebaut und weitergeleitet
# Aufbau einer administrativen Verbindung zu ProxySQL
bash> mysql -uadmin -padmin --host=127.0.0.1 --port=6032 
mysql> select hostgroup_id, hostname, port, max_connections from mysql_servers; 
+--------------+-----------+------+-----------------+ 
| hostgroup_id | hostname  | port | max_connections | 
+--------------+-----------+------+-----------------+ 
| 1            | 127.0.0.1 | 3306 | 1000            | 
| 2            | 127.0.0.1 | 3307 | 1000            | 
+--------------+-----------+------+-----------------+ 
2 rows in set (0,01 sec) 

Per Default arbeitet das Admin-Interface auf dem Port 6032. Hier wurden bereits per INSERT zwei Server hinterlegt. 
Im nächsten Schritt werden die User mit ihrer HOSTGROUP_ID (als Zielsystem) hinterlegt:

mysql> insert into mysql_users values ('neu', 'neu', 1,0,2,'neu',0,1,1,1,1,100,'');
mysql> select * from mysql_users where username = 'neu'\G
*************************** 1. row ***************************
              username: neu
              password: neu
                active: 1
               use_ssl: 0
     default_hostgroup: 2
        default_schema: neu
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 1
               backend: 1
              frontend: 1
       max_connections: 100
               comment:
1 row in set (0,00 sec)

mysql> INSERT INTO mysql_query_rules_fast_routing (username,schemaname,flagIN,destination_hostgroup,comment) VALUES ('neu','neu',0,2,'');
Query OK, 1 row affected (0,00 sec)

mysql> select * from mysql_query_rules_fast_routing;
+----------+------------+--------+-----------------------+---------+
| username | schemaname | flagIN | destination_hostgroup | comment |
+----------+------------+--------+-----------------------+---------+
| neu      | neu        | 0      | 2                     |         |
+----------+------------+--------+-----------------------+---------+
1 row in set (0,00 sec) 

Zunächst wird ein User "neu" erzeugt und eine entsprechende "FAST_ROUTING"-Regel, die diesen User auf den zweiten Server (DESTINATION_HOSTGROUP=2) routet. Wie der Name vermuten lässt, kann sich hinter der DESTINATION_HOSTGROUP auch eine Gruppe von Servern befinden (z.B. für ein HA-Konzept).

Die Strecke ist berechnet.

Meldet sich nun der User "neu" über den Proxy an, so wird er automatisch auf den entsprechenden Server geleitet. In unserem Beispiel läuft der Proxy auf dem Standardport 6033 (und nicht auf 3306, wie bei unserem Kunden).

# der Client loggt sich ein
mysql -uneu -pneu -h192.168.56.101 -P6033


# Status auf dem ProxySQL (nach dem Einloggen)
mysql> show processlist;
+-----------+------+-----+-----------+---------+---------+------+
| SessionID | user | db  | hostgroup | command | time_ms | info |
+-----------+------+-----+-----------+---------+---------+------+
| 4340      | neu  | neu | 2         | Sleep   | 18040   | NULL |
+-----------+------+-----+-----------+---------+---------+------+
1 row in set (0,02 sec) 

Wie zu erkennen ist, wurde der User "neu" direkt auf die Hostgruppe 2 in die Datenbank (Schema) "neu" geleitet und kann direkt arbeiten.

Sie haben Ihr Ziel erreicht.

ProxySQL ist ein mächtiges Werkzeug mit umfangreichen Konfigurationsmöglichkeiten. In diesem konkreten Fall konnte die entsprechende Problemstellung jedoch mit wenigen Kommandos und geringem Aufwand gelöst werden. Ein Blick auf dieses Werkzeug lohnt sich in jedem Fall.

By accepting you will be accessing a service provided by a third-party external to https://blog.ordix.de/