How to handle Sleep Connection in ShardingSphere-Proxy

[Byeongcheol-Kim]

Question

For English only, other languages will not accept.

Before asking a question, make sure you have:

• Googled your question.
• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

---

Hello. I am currently using ShardingSphere-Proxy as a Djnago App connected to two Mysql DBs. However, after connecting ShardingSphere-Proxy in Django, it was not closed properly, so I found that the number of connections in Sleep state in ShardingSphere-Proxy gradually increased. To solve this, I wrote a code that explicitly closes the connection in Django, but I wondered if ShardingSphere-Proxy would add a function that closes the connection after a certain amount of time, such as Mysql's wait_time. ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout

Or, I wonder if it is possible to handle Sleep Connection in ShardingSphere-Proxy.

Please let me know in the comments if there's any information I've misunderstood.

[TeslaCN]

ShardingSphere-Proxy doesn't support closing idle connections for now.

[Byeongcheol-Kim]

@TeslaCN . Thank you for answer.

ShardingSphere-Proxy doesn't support closing idle connections for now.

If so, do you have plans to support? What can I do to add this feature?

[tristaZero]

Hello, @Byeongcheol-Kim Thanks for your issue. It sounds reasonable, so I talked with @TeslaCN to check whether it's possible to finish this minor but interesting improvement in our next release. Later on, @TeslaCN will start out with a function design, and we hope it can catch March's release. Thanks!

[Byeongcheol-Kim]

Hi. @tristaZero Thank you. I will close this issue once it is published. thank you.

[tristaZero]

@TeslaCN If any updates, please be free to let us know, Thanks.

[TeslaCN]

I have some idea about this issue.

How to configure idle timeout in database

The parameter corresponding to MySQL is wait_timeout
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout

The parameter corresponding to PostgreSQL is idle_session_timeout
https://www.postgresql.org/docs/current/runtime-config-client.html

But ShardingSphere-Proxy doesn't maintain global variables and session variables at present. So we may consider adding the idle timeout parameter in properties.

API changes

Add new prop:

props:
  proxy-frontend-connection-idle-timeout: 3600

Idea about implementing idle timeout in ShardingSphere-Proxy

Create a Handler for judging idle timeout and place it after FrontendChannelInboundHandler

In terms of connection idle detection, Netty has provided relevant implementations:
io.netty.handler.timeout.IdleStateHandler supports read/write idle timeout detection.
We can create a new Handler which extends IdleStateHandler to handle idle timeout logic.

How to distinguish real idle and I/O idle while executing long statement

When executing select sleep(20) or other long statements, the I/O of the connection may be idle, but the connection is busy.

Such as:

set wait_timeout=10
select sleep(20);

At the ShardingSphere-Proxy level, consider judging whether ConnectionSession.queryContext is empty:

• When ConnectionSession.queryContext is empty, it means that the connection has no executing statement;
• When ConnectionSession.queryContext is not null, it means the connection is executing the statement.

[Byeongcheol-Kim]

@TeslaCN , @tristaZero
I found a related issue. If it's a connection has no executing statement, it will show up in show processlist, but not be deleted with kill . Proxy 5.2.1 version.

mysql> SHOW PROCESSLIST;
+----------------------------------+----------------+---------------+---------+---------+-------+-------+------+
| Id                               | User           | Host          | db      | Command | Time  | State | Info |
+----------------------------------+----------------+---------------+---------+---------+-------+-------+------+
| 89caef357374e2b3424656ff66bbcdce | order          | ************* | order   | Sleep   | 41105 |       |      |
| de3959f21dfda272124773a0f1da1c2a | order          | ************* | order   | Sleep   | 38999 |       |      |
| 8ba3301cfe24a6ccfe33e31dd747775f | order          | ************* | order   | Sleep   | 38988 |       |      |
| 3f698bd7e74a7f202ff15045feb39b0d | order          | ************* | order   | Sleep   | 38985 |       |      |
| 29b36a2030b0a032d9ad40356e27946b | order          | ************* | order   | Sleep   | 907   |       |      |
| f198390c7b5dc960909eed33bfb8d89a | order          | ************* | order   | Sleep   | 40789 |       |      |
| 2c67ed1e33a618581c9d5d98620327c9 | order          | ************* | order   | Sleep   | 40789 |       |      |
| d8a6f28e4696e96af8e257fe8bf34b19 | order          | ************* | order   | Sleep   | 38994 |       |      |
| ff37cf2217643c13c7948807369919e8 | order          | ************* | order   | Sleep   | 38988 |       |      |
| c1ef33c37d64b8e9848b5273da793630 | order          | ************* | order   | Sleep   | 38999 |       |      |
| d2f6b6d1ded73b9d1129e21bf744943b | order          | ************* | order   | Sleep   | 38988 |       |      |
| 9eee71c95906f5e5e1856a2828911b36 | order          | ************* | order   | Sleep   | 38988 |       |      |
| 8299fdf04c2b899def20a2e0abaa6b48 | order          | ************* | order   | Sleep   | 40789 |       |      |
+----------------------------------+----------------+---------------+---------+---------+-------+-------+------+
13 rows in set (0.06 sec)

mysql> KILL 89caef357374e2b3424656ff66bbcdce;
Query OK, 0 rows affected (0.02 sec)

mysql> KILL 89caef357374e2b3424656ff66bbcdce;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW PROCESSLIST;
+----------------------------------+----------------+---------------+---------+---------+-------+-------+------+
| Id                               | User           | Host          | db      | Command | Time  | State | Info |
+----------------------------------+----------------+---------------+---------+---------+-------+-------+------+
| 89caef357374e2b3424656ff66bbcdce | order          | ************* | order   | Sleep   | 41388 |       |      |
| de3959f21dfda272124773a0f1da1c2a | order          | ************* | order   | Sleep   | 39282 |       |      |
| 8ba3301cfe24a6ccfe33e31dd747775f | order          | ************* | order   | Sleep   | 39271 |       |      |
| 3f698bd7e74a7f202ff15045feb39b0d | order          | ************* | order   | Sleep   | 39268 |       |      |
| 29b36a2030b0a032d9ad40356e27946b | order          | ************* | order   | Sleep   | 1190  |       |      |
| f198390c7b5dc960909eed33bfb8d89a | order          | ************* | order   | Sleep   | 41072 |       |      |
| 2c67ed1e33a618581c9d5d98620327c9 | order          | ************* | order   | Sleep   | 41072 |       |      |
| d8a6f28e4696e96af8e257fe8bf34b19 | order          | ************* | order   | Sleep   | 39277 |       |      |
| ff37cf2217643c13c7948807369919e8 | order          | ************* | order   | Sleep   | 39271 |       |      |
| c1ef33c37d64b8e9848b5273da793630 | order          | ************* | order   | Sleep   | 39282 |       |      |
| d2f6b6d1ded73b9d1129e21bf744943b | order          | ************* | order   | Sleep   | 39271 |       |      |
| 9eee71c95906f5e5e1856a2828911b36 | order          | ************* | order   | Sleep   | 39271 |       |      |
| 8299fdf04c2b899def20a2e0abaa6b48 | order          | ************* | order   | Sleep   | 41072 |       |      |
+----------------------------------+----------------+---------------+---------+---------+-------+-------+------+

[TeslaCN]

Kill connection is not supported for now. It can kill executing statements only.

[sunkai-cai]

let me try it.

[TeslaCN]

Welcome @sunkai-cai

[TeslaCN]

Hi @sunkai-cai
Any progress here?

[sunkai-cai]

Hi @sunkai-cai Any progress here?

yes，I'am here. sorry, I am late. I'll done it in this week.