Introduction
This short blog post will show you how to use the command line to control the Quality Of Service when creating a new connection to the MySQL Server database.
Quality Of Service (QoS) determines where the Connector proxy will send client request packets, either to the Primary for writes or to a Replica for reads.
Primary/Replica Selection
A QoS of RW_STRICT will always send the request to the Primary (writable) node.
A QoS of RO_RELAXED will attempt to find an available Replica to read from, and if no Replica is available, will then read from the Primary.
| QoS | Primary Selected | Replica Selected |
|---|---|---|
| RW_STRICT | Yes, always | No |
| RO_RELAXED | Only if no Replica available | Yes, if below Max Applied Latency |
For more detailed information, please visit our Routing Methods online documentation.
Command Line QoS Control
By default, the Connector will provide a QoS of RW_STRICT, sending client requests to the Primary.
With proxy mode (NOT bridge mode), you can easily tell the Connector to change the QoS by adding the `-D@qos={RW_STRICT|RO_RELAXED}` argument to the mysql command-line tool.
For example, when you add a -D@qos=RO_RELAXED argument, all traffic is routed to a Replica, and when you add a -D@qos=RW_STRICT argument, all traffic is routed to a Primary!
If you ask for RO_RELAXED and send a write through that session, one of two bad things will happen because a Replica will (usually) be selected:
- the request does not have permissions to write, then the request will fail (not too terrible);
- the request has the privileges to write and succeeds (very bad), creating a Replica with data drift from the primary, because that write will never be replicated to the other nodes!
Also note that this same technique applies to any client application, not only the mysql command line tool, as long as the QoS is specified in the schema name with `@qos=`
For example, by default the QoS is RW_STRICT, so the Connector will return the Primary:
db14-demo shell> /usr/bin/mysql --user=app_user --password=secret --host=db14-demo.continuent.com --port=3306
mysql> tungsten connection status;
+---------------------------------------------------------------------------------------------------------------------------------+
| Message |
+---------------------------------------------------------------------------------------------------------------------------------+
| db15-demo.continuent.com@emea(master:ONLINE)
STATUS [OK] [Tue Mar 07 16:21:00 UTC 2023], QOS=RW_STRICT SSL.IN=true SSL.OUT=true |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@hostname for update; select @@hostname;
+--------------------------+
| @@hostname |
+--------------------------+
| db15-demo.continuent.com |
+--------------------------+
1 row in set (0.00 sec)
+--------------------------+
| @@hostname |
+--------------------------+
| db15-demo.continuent.com |
+--------------------------+
1 row in set (0.00 sec)
When you add a -D@qos=RO_RELAXED QoS argument, all traffic is routed to a Replica, including WRITES:
shell> /usr/bin/mysql --user=app_user --password=secret --host=db14-demo.continuent.com --port=3306 -D@qos=RO_RELAXED
mysql> tungsten connection status;
+---------------------------------------------------------------------------------------------------------------------------------+
| Message |
+---------------------------------------------------------------------------------------------------------------------------------+
| db13-demo.continuent.com@emea(slave:ONLINE)
STATUS [OK] [Tue Mar 07 16:21:00 UTC 2023], QOS=RO_RELAXED SSL.IN=true SSL.OUT=true |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select @@hostname for update; select @@hostname;
+--------------------------+
| @@hostname |
+--------------------------+
| db13-demo.continuent.com |
+--------------------------+
1 row in set (0.00 sec)
+--------------------------+
| @@hostname |
+--------------------------+
| db13-demo.continuent.com |
+--------------------------+
1 row in set (0.00 sec)
Conversely, if you create a read-only listener port using an entry like `connector-readonly-listen-port=3307` in your INI, then you would see that with no extra arguments, all traffic is directed to a Replica, but that we can force the traffic to go to the Primary the same way as above:
shell> /usr/bin/mysql --user=app_user --password=secret --host=db14-demo.continuent.com --port=3307
mysql> tungsten connection status;
+---------------------------------------------------------------------------------------------------------------------------------+
| Message |
+---------------------------------------------------------------------------------------------------------------------------------+
| db14-demo.continuent.com@emea(slave:ONLINE)
STATUS [OK] [Tue Mar 07 16:21:00 UTC 2023], QOS=RO_RELAXED SSL.IN=true SSL.OUT=true |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@hostname for update; select @@hostname;
+--------------------------+
| @@hostname |
+--------------------------+
| db14-demo.continuent.com |
+--------------------------+
1 row in set (0.00 sec)
+--------------------------+
| @@hostname |
+--------------------------+
| db14-demo.continuent.com |
+--------------------------+
1 row in set (0.00 sec)
shell> /usr/bin/mysql --user=app_user --password=secret --host=db14-demo.continuent.com --port=3307 -D@qos=RW_STRICT
mysql> tungsten connection status;
+---------------------------------------------------------------------------------------------------------------------------------+
| Message |
+---------------------------------------------------------------------------------------------------------------------------------+
| db15-demo.continuent.com@emea(master:ONLINE)
STATUS [OK] [Tue Mar 07 16:21:00 UTC 2023], QOS=RW_STRICT SSL.IN=true SSL.OUT=true |
+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select @@hostname for update; select @@hostname;
+--------------------------+
| @@hostname |
+--------------------------+
| db15-demo.continuent.com |
+--------------------------+
1 row in set (0.00 sec)
+--------------------------+
| @@hostname |
+--------------------------+
| db15-demo.continuent.com |
+--------------------------+
1 row in set (0.01 sec)
Additional Information
The interesting syntax -D@qos= adds the QoS to the schema name but without specifying a schema name, as explained here.
Wrap-Up
The Tungsten Connector proxy within a Tungsten Cluster is a very powerful tool, able to route traffic many different ways. In this blog post, the Quality of Service feature was demonstrated. We showed you how to use the command line to control the Quality Of Service when creating a new connection to the MySQL Server database using the `-D@qos=RO_RELAXED` and `-D@qos=RW_STRICT` arguments to the `mysql` command-line tool. Hope you enjoyed this brief foray into your Command Line Superpowers!
Comments
Add new comment