Understanding idle vs active connections

[Andrew Eross]

Hi guys,

I've read the docs: https://proxysql.com/documentation/stats-statistics/, but I'm still having a little bit of trouble understanding what is really an "active" vs an "idle" connection.

Our backend is an Azure MySQL server, where Azure limits us to 1365 connections (one of the reasons we're using proxysql is to use these more efficiently, of course):

mysql> select * from runtime_mysql_servers;

+--------------+----------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                     | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | DB-hostname                                     | 3306 | 0         | ONLINE | 1      | 0           | 1300            | 0                   | 1       | 0              |         |
+--------------+----------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

We're recently getting some production alerts where Azure is reporting that we're getting close to that limit, e.g. we sometimes hit 1000 active connections during the day.

What I'm having trouble understanding is if that's actually a problem or not because I believe proxysql may likely be keeping a bunch of idle connections alive, and maybe it's not a real issue, you know.

For example, if I query the active process list:

select * from stats_mysql_processlist;

I'll get a list of 176 entries right now, but almost every single one says the command state is "Sleep":

+----------+-----------+-------------------+----------------------+-----------+----------+-----------+------------+------------+----------------------------------------------+----------+---------+----------+------+--------------+---------------+
| 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        | 42036     | someuser            | somedb | 10.x.x.x | 55773    | 1         | 10.x.x.x  | 36234      |  DB-hostname                        | 3306     | Sleep   | 33330    | NULL | 4            | NULL          |
| 0        | 42336     |  someuser           |  somedb   | 10.x.x.x | 56277    | 1         | 10.x.x.x  | 55380      |  DB-hostname                                       | 3306     | Sleep   | 39548    | NULL | 4            | NULL          |
| 0        | 42337     |  someuser           |  somedb   | 10.x.x.x | 56278    | 1         | 10.x.x.x  | 55386      |  DB-hostname                                       | 3306     | Sleep   | 38643    | NULL | 4            | NULL          |
| 2        | 42338     |  someuser           |  somedb   | 10.x.x.x | 56284    | 1         | 10.x.x.x  | 39504      |  DB-hostname                                       | 3306     | Sleep   | 38306    | NULL | 4            | NULL          |
| 0        | 42345     |  someuser           |  somedb   | 10.x.x.x | 56330    | 1         | 10.x.x.x  | 36854      |  DB-hostname                              | 3306     | Sleep   | 47560    | NULL | 4            | NULL          |
| 2        | 42356     |  someuser           |  somedb   | 10.x.x.x | 56375    | 1         | 10.x.x.x  | 33654      |  DB-hostname                                       | 3306     | Sleep   | 41710    | NULL | 4            | NULL          |
...etc..

However, the connection pool stats seem to say that ConnFree is only 1 and ConnUsed is 144:

mysql> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+----------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host                                     | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries  | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+----------+-------------------+-----------------+-----------------+------------+
| 1         |  DB-hostname                                  | 3306     | ONLINE | 144      | 1        | 21940  | 8       | 1234        | 20441689 | 0                 | 17034698249     | 104237156204    | 906        |
+-----------+----------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+----------+-------------------+-----------------+-----------------+------------+
1 row in set (0.01 sec)

That's where I'm getting confused about what the "actual number of used/active connections" are?

Are these sleeping connections actually idle/available for use by other clients.. or not?

Any hints?

Thanks!

Andrew

[Andrew Eross]

I think I've partially answered my own question -

I'm pretty sure this is because our .NET app's MySQL connector has its own internal connection pooling enabled by default. I'm pretty sure those sleeping connections are from its connection pooling keeping them alive, so they appear to be "in use" from a proxysql perspective.

So really my question comes down to best practices. 

I've done a bunch of googling and really can't find anything:

With proxysql in the picture, should you completely disable all application level connection pooling or not?

Thank you,

Andrew