Overview
The Skinny
Database Proxies provide a single entry point into MySQL for the calling client applications.
Proxies are wonderful tools to handle various situations like a master role switch to another node for maintenance, or for transparency with read and write connections.
However, when the time comes to perform the switch action, all of the calling clients have been funneled through the proxy, so identification of the calling host from the database itself becomes difficult.
The Problem
What is going on?
Let's illustrate how not knowing the source of a client connection can be an issue for the database administrator...
In the following diagram, three client applications connect to a Tungsten Cluster via the Connector proxy:
One of those applications slows down the whole cluster by selecting a huge amount of data (i.e. SELECT * FROM HUGE_TABLE;).
The DBA wants to correct the problem by killing the application/query (before letting the developer what he thinks of his work ;-).
The natural action for the DBA is to call the MySQL show processlist command to find the origin host:
mysql> show processlist;
+-------+----------+--------------+-----------------+---------+------+--------------+-----------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+--------------+-----------------+---------+------+--------------+-----------------------------------------------------------------------------------------------+
| 51 | tungsten | db1:32956 | tungsten_global | Sleep | 5175 | | NULL |
| 52 | tungsten | db1:32958 | tungsten_global | Sleep | 0 | | NULL |
| 22293 | app_user | db1:43390 | NULL | Query | 0 | starting | show processlist |
| 22517 | app_user | proxy1:44092 | test | Query | 0 | query end | update ta3 set value = 439, changed = '2019-07-24 16:28:05.975' where k1 = 100 and k2 = 80000 |
| 22518 | app_user | proxy1:44096 | test | Query | 0 | Sending data | select * from ta3 limit 100 |
| 22522 | app_user | proxy1:43640 | test | Query | 0 | query end | update ta3 set value = 254, changed = '2019-07-24 16:28:05.975' where k1 = 71 and k2 = 19000 |
| 22526 | app_user | proxy1:44122 | test | Query | 0 | query end | update ta3 set value = 836, changed = '2019-07-24 16:28:05.973' where k1 = 27 and k2 = 95000 |
| 22527 | app_user | proxy1:44128 | test | Query | 0 | updating | update ta3 set value = 39, changed = '2019-07-24 16:28:05.971' where k1 = 97 and k2 = 34000 |
| 22549 | app_user | proxy1:43670 | test | Query | 0 | query end | select * from ta3 limit 50 |
| 22550 | app_user | proxy1:43672 | test | Query | 0 | Sending data | select * from ta3 |
+-------+----------+--------------+-----------------+---------+------+--------------+-----------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
Fail!
The host is proxy1, and there is no way to know the real calling client origin host from this output.
The Solution
Quick and Easy, Just Add Tungsten!
Luckily, our smart DBA uses a Tungsten Cluster. Through the Connector proxy, the DBA will be able to run tungsten show processlist; from the MySQL client command prompt:
mysql> tungsten show processlist;
+---------------+----------------+----------+------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| DataSource | Id | User | Host | db | Command | Time | State | Info |
+---------------+----------------+----------+------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| db2 | 23405 | app_user | app5:55322 | test | Query | 0 | Sending data | select c.* from ta3 c join ta1 a on c.k1 = a.k1 join ta2 b on c.k2 = b.k2 where a.value between 512 |
| db1 | 23382 | app_user | app3:39364 | test | Query | 0 | query end | update ta3 set value = 479, changed = '2019-07-24 15:54:45.598' where k1 = 26 and k2 = 80000 |
| db3 | 22518 | app_user | app1:44096 | test | Query | 0 | Sending data | select * from ta3 limit 100 |
| db1 | 23383 | app_user | app2:42228 | test | Query | 0 | update | insert into ta3 values (257, 40000, '2019-07-24 15:54:45.792', '2019-07-24 15:54:45.792', 907) |
| db2 | 23312 | app_user | app3:39292 | test | Query | 0 | Sending data | select c.* from ta3 c join ta1 a on c.k1 = a.k1 join ta2 b on c.k2 = b.k2 where a.value between 356 |
| db3 | 23420 | app_user | app5:55356 | test | Query | 0 | Sending data | select c.* from ta3 c join ta1 a on c.k1 = a.k1 join ta2 b on c.k2 = b.k2 where a.value between 477 |
| db3 | 23375 | app_user | app3:39352 | test | Query | 0 | Sending data | select c.* from ta3 c join ta1 a on c.k1 = a.k1 join ta2 b on c.k2 = b.k2 where a.value between 595 |
| db3 | 22550 | app_user | app2:42166 | test | Query | 0 | Sending data | select * from ta3 |
| db1 | 23413 | app_user | app5:55342 | test | Query | 0 | updating | update ta3 set value = 324, changed = '2019-07-24 15:54:46.315' where k1 = 137 and k2 = 96000 |
+---------------+----------------+----------+------------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
9 rows in set (0.44 sec)
Bingo!
The Tungsten Connector has post-processed the request and replaced its own hostname with the real, origin hostname.
Oh, this "DataSource" entry? It will show on which database host the connection actually ended up...
This tool is available in proxy mode (which includes smart-scale, host-or-port-based read-write splitting) thanks to the Tungsten Connector SQL parser.
For those who use Bridge mode (the default as of v5.0.0), we developed an external tool that can be called from the command line:
$ ./tungsten-connector/bin/connector client-list
Executing Tungsten Connector Service --client-list ...
+--------------------+-------------------+--------------------+-----------------------+
| Client | Connector Inbound | Connector Outbound | Data Source |
+--------------------+-------------------+--------------------+-----------------------+
| /10.10.1.218:33084 | /10.10.1.212:3306 | /10.10.1.212:45074 | db1/10.10.1.212:13306 |
| /10.10.1.242:48830 | /10.10.1.212:3306 | /10.10.1.212:44918 | db2/10.10.1.213:13306 |
| /10.10.1.218:33132 | /10.10.1.212:3306 | /10.10.1.212:45146 | db2/10.10.1.213:13306 |
| /10.10.1.242:48844 | /10.10.1.212:3306 | /10.10.1.212:44928 | db3/10.10.1.214:13306 |
| /10.10.1.218:33124 | /10.10.1.212:3306 | /10.10.1.212:45134 | db2/10.10.1.213:13306 |
| /10.10.1.242:48802 | /10.10.1.212:3306 | /10.10.1.212:44906 | db1/10.10.1.212:13306 |
| /10.10.1.218:33130 | /10.10.1.212:3306 | /10.10.1.212:45144 | db1/10.10.1.212:13306 |
| /10.10.1.200:56626 | /10.10.1.212:3306 | /10.10.1.212:45108 | db3/10.10.1.214:13306 |
| /10.10.1.242:48806 | /10.10.1.212:3306 | /10.10.1.212:44910 | db4/10.10.1.215:13306 |
| /10.10.1.200:56542 | /10.10.1.212:3306 | /10.10.1.212:45064 | db1/10.10.1.212:13306 |
+--------------------+-------------------+--------------------+-----------------------+
Done Tungsten Connector Service --client-list
Match the offending request with the host/port couple and you're back to the origin application host!
The Library
Please read the docs!
For more information about using various Tungsten Connector-based inline commands, please visit the docs page at http://docs.continuent.com/tungsten-clustering-6.0/connector-inline.html
For more information about Tungsten Clustering, please visit https://docs.continuent.com.
Summary
The Wrap-Up
In this blog post we discussed how to accurately track end-to-end client connections through a proxy using the Tungsten Connector in a Tungsten Cluster.
Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!
For more information, please visit https://www.continuent.com/solutions
Want to learn more or run a POC? Contact us.
Comments
Add new comment