Bug #119765 max allowed value for max_connections

• Submitted: 23 Jan 17:52
• Reporter: Marc Reilly
• Status: Open
• Category: MySQL Server: Connection Handling
• Severity: S3 (Non-critical)
• Version: 8.0,8.4,9.6
• OS: Any
• CPU Architecture: Any
• Tags: max_connections, MDL lock

[23 Jan 17:52] Marc Reilly

Description:
Given the changes in https://bugs.mysql.com/bug.php?id=98624 (8.0.21) to introduce a ERR when connections is > 65535, should the max allowed value for max connection be 65535 rather than 100,000?

> Commit 3f2baba
Dyre Tjeldvoll Dyre Tjeldvoll Dyre Tjeldvoll authored and Dyre Tjeldvoll committed on Apr 8, 2020
Bug#31019269: MDL SYSTEM BECOME INSANE AFTER 64K CONNECTIONS
Problem: Exhausting the lf pinbox caused incorrect results pre 8.0 and a misleading error message in 8.0 and newer.

Solution: Check and report an error if the lf pinbox has been exhausted.

Change-Id: Ic05663fc58339a10914111a5fa9cc5473f10c2b0

https://github.com/mysql/mysql-server/commit/3f2babad5c7477257447b86f4f0eae92dceefe50

https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_max_connection... 

How to repeat:
1/ set max_connections to 100k (ensure appropriate os limits configured correctly)
2/ Attempt to establish 100K connections. In my case I used 4 ec2 clients
3/ At `threads_connected` 65535 you will start seeing connection failures with:

```
[Thread 47] Connection 396 failed: 3985 (HY000): Not enough resources to complete lock request.
[Thread 42] Connection 386 failed: 3985 (HY000): Not enough resources to complete lock request.
[Thread 50] Connection 381 failed: 3985 (HY000): Not enough resources to complete lock request.
[Thread 47] Connection 397 failed: 3985 (HY000): Not enough resources to complete lock request.
[Thread 35] Connection 394 failed: 3985 (HY000): Not enough resources to complete lock request.
[Thread 42] Connection 387 failed: 3985 (HY000): Not enough resources to complete lock request.
[Thread 50] Connection 382 failed: 3985 (HY000): Not enough resources to complete lock request.
[Thread 47] Connection 398 failed: 3985 (HY000): Not enough resources to complete lock request.
[Thread 35] Connection 395 failed: 3985 (HY000): Not enough resources to complete lock request.

mysql> SHOW STATUS LIKE '%Threads_connected%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 65520 |
+-------------------+-------+
1 row in set (0.00 sec)
 
```

Suggested fix:
Maybe I'm missing something here but 1/ if 65,520 is the hardcoded max for lf_pins in the engine code, and 2/ every connection requires a pin: shouldn't max_connections max value be 65520 too, rather than 100k? 

https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_max_connection...