Proxysql: Destroy session if client disconnects during authentication

[renecannao]

Here is an example of a not handled situation.

Admin> SELECT * FROM stats_mysql_processlist WHERE user='unauthenticated user' LIMIT 10;
+----------+-----------+----------------------+----+-------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+
| ThreadID | SessionID | user                 | db | cli_host    | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info |
+----------+-----------+----------------------+----+-------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+
| 1        | 672316    | unauthenticated user |    | 10.25.70.23 | 31189    | -1        |            |            |          |          | 0       | 120618  |      |
| 3        | 672317    | unauthenticated user |    | 10.25.70.23 | 20459    | -1        |            |            |          |          | 0       | 120620  |      |
| 0        | 672330    | unauthenticated user |    | 10.25.70.23 | 31192    | -1        |            |            |          |          | 0       | 120620  |      |
| 3        | 672337    | unauthenticated user |    | 10.25.70.23 | 59929    | -1        |            |            |          |          | 0       | 120619  |      |
| 2        | 672338    | unauthenticated user |    | 10.25.70.23 | 62562    | -1        |            |            |          |          | 0       | 120620  |      |
| 3        | 672358    | unauthenticated user |    | 10.25.70.23 | 20467    | -1        |            |            |          |          | 0       | 120619  |      |
| 3        | 672384    | unauthenticated user |    | 10.25.70.23 | 59937    | -1        |            |            |          |          | 0       | 120619  |      |
| 2        | 672390    | unauthenticated user |    | 10.25.70.23 | 62571    | -1        |            |            |          |          | 0       | 120619  |      |
| 3        | 672412    | unauthenticated user |    | 10.25.70.23 | 59942    | -1        |            |            |          |          | 0       | 120618  |      |
| 0        | 672415    | unauthenticated user |    | 10.25.70.23 | 62580    | -1        |            |            |          |          | 0       | 120619  |      |
+----------+-----------+----------------------+----+-------------+----------+-----------+------------+------------+----------+----------+---------+---------+------+
10 rows in set (0.01 sec)

[eightnoteight]

Hi, @renecannao

Our production architecture is apache php + proxysql sidecar => 3 node proxysql cluster => aurora(we run 2.0.2), often during the peak traffic duration, we are facing this particular issue. the unauthenticated user connections go up-to 65k ish, but the number of open file descriptors on the proxysql cluster servers don't go any more than 2000.

Is there anyway to destroy these sessions by manual intervention or by any other means? as of now we had to restart the cluster nodes manually to clear off these connections.

or any workaround on proxysql sidecar end to limit the connection attempts or have a static connection pool?

[davidrjonas]

I believe this is the same issue we're seeing. In proxysql 2.0.4, "unauthenticated user" connections are growing by about 120 per minute (at other times much faster). If it it hits mysql-max_connections then a "Too many connections" error will be returned. Backend connections remain a cool 15-25.

mysql> SELECT COUNT(*) FROM stats_mysql_processlist WHERE hostgroup = -1 AND time_ms > 1000;
+----------+
| COUNT(*) |
+----------+
| 21983    |
+----------+
1 row in set (0.43 sec)

mysql> SELECT * FROM stats_mysql_processlist WHERE hostgroup = -1 AND time_ms > 1000 LIMIT 5;
+----------+-----------+----------------------+----+------------+----------+-----------+------------+------------+----------+----------+-------------------+---------+------+--------------+---------------+
| ThreadID | SessionID | user                 | db | cli_host   | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command           | time_ms | info | status_flags | extended_info |
+----------+-----------+----------------------+----+------------+----------+-----------+------------+------------+----------+----------+-------------------+---------+------+--------------+---------------+
| 1        | 99        | unauthenticated user | NULL | 10.25.0.60 | 8801     | -1        | NULL       | NULL       | NULL     | NULL     | Connecting client | 9246625 | NULL | NULL         | NULL          |
| 2        | 213       | unauthenticated user | NULL | 10.25.0.60 | 33090    | -1        | NULL       | NULL       | NULL     | NULL     | Connecting client | 9241630 | NULL | NULL         | NULL          |
| 6        | 275       | unauthenticated user | NULL | 10.25.0.60 | 23775    | -1        | NULL       | NULL       | NULL     | NULL     | Connecting client | 9240532 | NULL | NULL         | NULL          |
| 0        | 276       | unauthenticated user | NULL | 10.25.0.60 | 45806    | -1        | NULL       | NULL       | NULL     | NULL     | Connecting client | 9240395 | NULL | NULL         | NULL          |
| 7        | 281       | unauthenticated user | NULL | 10.25.0.60 | 54052    | -1        | NULL       | NULL       | NULL     | NULL     | Connecting client | 9240539 | NULL | NULL         | NULL          |
+----------+-----------+----------------------+----+------------+----------+-----------+------------+------------+----------+----------+-------------------+---------+------+--------------+---------------+
5 rows in set (0.32 sec)

Notice how far out timeout_ms is. I couldn't find a setting to control when those should be aborted.

The cli_host is always the same due to NAT downstream. The architecture here is AWS Host -> ProxySQL -(NAT)> DirectConnect (~10ms) -> ProxySQL -> Database. The ProxySQL closest to the Database is the one having trouble.

I can script a reaping of these connections with KILL CONNECTION. But is there a better way?

[eightnoteight]

@davidrjonas

in the end we figured out that the memory utilization of the proxysql seems to be going up continuously because of the mapping of prepared statements between the client and the backend connection that proxysql keeps.

since our client made a long-lived connection to proxysql, the memory utilization kept on going up because of the huge number of unique prepared statements and huge number of client servers we have

so after rotating the client connections every 5 minutes, the memory utilization went down and after that, the unauthenticated user connections seems to have reduced completely

so if your memory utilization is also high, reducing that might help in reducing these unauthenticated connections.

[feiazifeiazi]

select d.time_ms/1000.0/60/60 time_hour,d.time_ms,d.ThreadId,d.SessionId,d.user ,d.cli_host,d.cli_port,d.hostgroup from stats_mysql_processlist d where 1=1 and d.user ='unauthenticated user' order by d.time_ms desc;

[ernstae]

I've seen this behavior, and also have been trying to understand it. We've
only seen it occurring on one of many clusters.
I am also running a KILL CONNECTION for any of the unauthenticated user
connections on a routine basis.

On Mon, Oct 28, 2019 at 8:57 PM Srinivas Devaki [e-mail]
wrote:

Hi, @renecannao https://github.com/renecannao

Our production architecture is apache php + proxysql sidecar => 3 node
proxysql cluster => aurora(we run 2.0.2), often during the peak traffic
duration, we are facing this particular issue. the unauthenticated user
connections go up-to 65k ish, but the number of open file descriptors on
the proxysql cluster servers don't go any more than 2000.

Is there anyway to destroy these sessions by manual intervention or by any
other means? as of now we had to restart the cluster nodes manually to
clear off these connections.

or any workaround on proxysql sidecar end to limit the connection attempts
or have a static connection pool?

currently, we are clearing these by doing KILL CONNECTION %s on whatever
connection is stuck at "unauthenticated user"

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/sysown/proxysql/issues/1132?email_source=notifications&email_token=AA2IE6SXHTKGN5ZYFUPZOGDQQ6YCHA5CNFSM4DWIGKIKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOECPE3LY#issuecomment-547245487,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AA2IE6QZYGROZTBHEARKQR3QQ6YCHANCNFSM4DWIGKIA
.

[renecannao]

Fixed in #3023