MySQL Bugs: #108977: max_idle_server_connections config not working

Bug #108977	max_idle_server_connections config not working
Submitted:	3 Nov 2022 6:52	Modified:	9 Nov 2022 2:05
Reporter:	sangmin jeon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Router	Severity:	S3 (Non-critical)
Version:	8.0.29, 8.0.30	OS:	Ubuntu (20.0.4)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86
Tags:	8.0.29, 8.0.30, connection pool, mysqlrouter

Description:
Hi,

I want to use max_idle_server_connection config with 'Mysql Router', but it seems not working.

I'm using 3db instances(v8.0.30) with multi-primary mode, and 2mysql router instances(v8.0.30).

config options, values and descriptions are here
1. idle_timeout : 1000
Seconds to keep the idling connection in the collection pool before closing it
2. max_idle_server_connections : 30
Connections to keep open after the client disconnects

I expected least 30 connections are remain until 1000sec.
But not works for me.

Any help?
Thanks.

How to repeat:
1. start mysql router with bootstrap
router-0> mysqlrouter --bootstrap "{user}:{pwd}@{ip}:{port}" --user mysqlrouter

2. edit mysqlrouter.conf
##mysqlrouter.conf###
[DEFAULT]
...
...
#add connection_pool section
[connection_pool]
idle_timeout=1000
max_idle_server_connections=30

3. restart mysql router
router-0> systemctl restart mysqlrouter

4. get connection_pool config
router-0> curl -s localhost:{http_port}/api/20190715/connection_pool/main/config
{
  maxIdleServerConnections": 30,
  "idleTimeoutInMs": 1000000
}

5. make enough client connection, and get db connected threads
//in my case, I used "sysbench" for traffic
db-0> mysql -e"show status where variable_name like 'thread%'";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 46    |
| Threads_created   | 346   |
| Threads_running   | 6     |
+-------------------+-------+
db-1> mysql -e"show status where variable_name like 'thread%'";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 44    |
| Threads_created   | 317   |
| Threads_running   | 26    |
+-------------------+-------+
db-2> mysql -e"show status where variable_name like 'thread%'";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 44    |
| Threads_created   | 311   |
| Threads_running   | 11    |
+-------------------+-------+
router-0> curl -s localhost:{http_port}/api/20190715/routes/bootstrap_rw/connections
{
  "items": [
    {
      "bytesFromServer": 892287,
      "bytesToServer": 113477,
      "sourceAddress": "*****:*****",
      "destinationAddress": "*****:*****",
      "timeStarted": "2022-11-03T06:04:03.612788Z",
      "timeConnectedToServer": "2022-11-03T06:04:03.613119Z",
      "timeLastSentToServer": "2022-11-03T06:04:07.354824Z",
      "timeLastReceivedFromServer": "2022-11-03T06:04:07.354010Z"
    },
    {
      "bytesFromServer": 894063,
      "bytesToServer": 109876,
      "sourceAddress": "*****:*****",
      "destinationAddress": "*****:*****",
      "timeStarted": "2022-11-03T06:04:03.620025Z",
      "timeConnectedToServer": "2022-11-03T06:04:03.620893Z",
      "timeLastSentToServer": "2022-11-03T06:04:07.355070Z",
      "timeLastReceivedFromServer": "2022-11-03T06:04:07.354212Z"
    },
    {
      "bytesFromServer": 791357,
      "bytesToServer": 102485,
      "sourceAddress": "*****:*****",
      "destinationAddress": "*****:*****",
      "timeStarted": "2022-11-03T06:04:03.620489Z",
      "timeConnectedToServer": "2022-11-03T06:04:03.621262Z",
      "timeLastSentToServer": "2022-11-03T06:04:07.335298Z",
      "timeLastReceivedFromServer": "2022-11-03T06:04:07.335011Z"
    },
    ...
  ]
}

6. stop making client connection, and get db connected threads
db-0> mysql -e"show status where variable_name like 'thread%'";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 6     |
| Threads_connected | 3     |
| Threads_created   | 346   |
| Threads_running   | 2     |
+-------------------+-------+
db-1> mysql -e"show status where variable_name like 'thread%'";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 1     |
| Threads_created   | 317   |
| Threads_running   | 2     |
+-------------------+-------+
db-2> mysql -e"show status where variable_name like 'thread%'";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 1     |
| Threads_created   | 311   |
| Threads_running   | 2     |
+-------------------+-------+
router-0> curl -s localhost:{http_port}/api/20190715/routes/bootstrap_rw/connections
{
  "items": []
}

+ routing strategy was round-robin

##mysqlrouter.conf##
[routing:bootstrap_rw]
bind_address=0.0.0.0
bind_port=3306
destinations=metadata-cache://{name}/?role=PRIMARY
routing_strategy=round-robin
protocol=classic
client_connect_timeout=599
connect_timeout=5
max_connections=65535

Hi,

I apologize but I'm not sure I understand what you are trying to report.

After you disconnected you have less than 30 connections open:

db-2> mysql -e"show status where variable_name like 'thread%'";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 8     |
| Threads_connected | 1     |
| Threads_created   | 311   |
| Threads_running   | 2     |
+-------------------+-------+
router-0> curl -s localhost:{http_port}/api/20190715/routes/bootstrap_rw/connections
{
  "items": []
}

Where is the problem?

I misunderstand that 'max_idle_server_connections=30' means after I disconnected, 'mysql router' will keep "least" 30 connections until idle_timeout seconds.
Sorry to you and Thanks for help.

That would be min_* then :)

No worries, thank you for using MySQL