How to Test MySQL Server Hostname with ProxySQL Multiplexing

by Kevin Markwardt on Jul 24, 2019 12:00:00 AM

Overview

While working on a MySQL Galera cluster with ProxySQL, I was in the process of testing traffic going to the MySQL nodes by using the @@hostname command to ensure which MySQL host behind the proxy the query ran on. This was important as my client is using query rules to route traffic according to the rule to either the master or the slave. But to my surprise, I didn't always get the result that I was expecting. This is where ProxySQL multiplexing comes into play.

Scenario

In my scenario, I was on a test server connecting to ProxySQL which was then routing my queries to the MySQL Galera nodes. I would connect into ProxySQL using the MySQL client.

Important note: When testing query routing with ProxySQL using comments and the MySQL client, you have to use the "-c" command line option in order for the comment to not be stripped away when running queries. You want to preserve the comment so ProxySQL can apply the appropriate query rule to the query.
-c, --comments Preserve comments. Send comments to the server. The
 default is --skip-comments (discard comments), enable
 with --comments.

These are the query rules that I have in place - where 10 is my Writer hostgroup and 20 is my reader hostgroup.

+---------+--------+--------------+-----------------------------+-----------------------+-----------+
 | rule_id | active | match_digest | match_pattern | destination_hostgroup | multiplex |
 +---------+--------+--------------+-----------------------------+-----------------------+-----------+
 | 10 | 1 | NULL | ^SELECT .* FOR UPDATE$ | 10 | NULL |
 | 20 | 1 | NULL | -- route to master | 10 | NULL |
 | 30 | 1 | NULL | -- route to slave | 20 | NULL |
 | 40 | 1 | NULL | ^SELECT | 20 | NULL |
 +---------+--------+--------------+-----------------------------+-----------------------+-----------+

Here are the MySQL Galera hosts. We have a dedicated server for write traffic, MARIADB-001, that is the only ONLINE server in hostgroup 10. And the other servers are for read traffic, MARIADB-002 and MARIADB-003, they are set to ONLINE in hostgroup 20.

mysql> SELECT hostgroup_id, hostname, status, weight FROM runtime_mysql_servers ORDER BY hostgroup_id, weight DESC;
 +--------------+-------------+--------------+--------+
 | hostgroup_id | hostname | status | weight |
 +--------------+-------------+--------------+--------+
 | 10 | MARIADB-001 | ONLINE | 100 |
 | 10 | MARIADB-002 | OFFLINE_SOFT | 90 |
 | 10 | MARIADB-003 | OFFLINE_SOFT | 80 |
 | 20 | MARIADB-001 | OFFLINE_SOFT | 100 |
 | 20 | MARIADB-002 | ONLINE | 90 |
 | 20 | MARIADB-003 | ONLINE | 80 |
 +--------------+-------------+--------------+--------+

Next I test the rules to make sure they are working correctly. We can see the query with the comment "-- route to master" goes to the primary writer server and the query with the comment "-- route to slave" goes to the read-only server.

MySQL [(none)]> select @@hostname; -- route to master
 +-----------------------------------------+
 | @@hostname |
 +-----------------------------------------+
 | MARIADB-001.us-west-2 |
 +-----------------------------------------+

MySQL [(none)]> select @@hostname; -- route to slave
 +-----------------------------------------+
 | @@hostname |
 +-----------------------------------------+
 | MARIADB-003.us-west-2 |
 +-----------------------------------------+

Next, I moved traffic from the current master by setting it to OFFLINE_SOFT, and then enabled one of the other MySQL Galera masters to be the new primary writer by setting it to ONLINE.

UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = 'MARIADB-001' AND hostgroup_id = 10;
 UPDATE mysql_servers SET status = 'ONLINE' WHERE hostname = 'MARIADB-002' AND hostgroup_id = 10;
 UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = 'MARIADB-002' AND hostgroup_id = 20;
 LOAD MYSQL SERVERS TO RUNTIME;
 SELECT hostgroup_id, hostname, status, weight FROM runtime_mysql_servers ORDER BY hostgroup_id, weight DESC;
 +--------------+-------------+--------------+--------+
 | hostgroup_id | hostname | status | weight |
 +--------------+-------------+--------------+--------+
 | 10 | MARIADB-001 | OFFLINE_SOFT | 100 |
 | 10 | MARIADB-002 | ONLINE | 90 |
 | 10 | MARIADB-003 | OFFLINE_SOFT | 80 |
 | 20 | MARIADB-001 | OFFLINE_SOFT | 100 |
 | 20 | MARIADB-002 | OFFLINE_SOFT | 90 |
 | 20 | MARIADB-003 | ONLINE | 80 |
 +--------------+-------------+--------------+--------+

I used the MySQL client and the same session by never disconnecting the client. I expected the write traffic to go to the new write master MARIADB-002 server but I was a little surprised when it did not. It continued to go to the MARIADB-001 server.

MySQL [(none)]> select @@hostname; -- route to master
 +-----------------------------------------+
 | @@hostname |
 +-----------------------------------------+
 | MARIADB-001.us-west-2 |
 +-----------------------------------------+

Resolution

I opened a bug with ProxySQL and come to find out about Multiplexing ( https://github.com/sysown/proxysql/wiki/Multiplexing). The key item that was affecting me is the following:

All queries that have @ in their query_digest will disable multiplexing, and will never be enabled again. Handling of switchovers from nodes gaining OFFLINE_SOFT status When multiplexing is disabled due to any of the reasons described here, an active connection will remain connected to a node that has gone the OFFLINE_SOFT status. Queries will also remain to be routed to this node. If you use a connection pool mechanism in the application, make sure you recycle your connections often enough in a Galera cluster. If an active transaction was the reason for multiplexing to be disabled, the connection is moved after the transaction has finished.

They provide remediation to this by creating a new query rule to allow this behavior if you want it:

mysql_query_rules.multiplexing allows to enable or disable multiplexing based on matching criteria.
 The field currently accepts these values:
 
 0 : disable multiplex
 1 : enable multiplex
 2 : do not disable multiplex for this specific query containing @

So I created the following rule and, magically, all of my concerns have washed away and the query routing was working exactly the way that I was expecting it to.

INSERT INTO mysql_query_rules (rule_id,active,match_digest,multiplex) VALUES(1,'1','^SELECT @@hostname',2);
 LOAD MYSQL QUERY RULES TO RUNTIME;

Then I tested again and after the failover, my queries with the "-- route to master" comment now route to the correct primary write MySQL server.

MySQL [(none)]> select @@hostname; -- route to master
 +-----------------------------------------+
 | @@hostname |
 +-----------------------------------------+
 | MARIADB-002.us-west-2 |
 +-----------------------------------------+

No Comments Yet

Let us know what you think

Share this