如何以编程方式在 MySQL 中设置 max_connections
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19822558/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set max_connections in MySQL Programmatically
提问by antf
I have a server where a lot of users will connect to it and use a database there, and I am using MySQL. I know that the default number of max_connectionsin MySQL is 100 or 150 but I am sure I need way beyond that number, therefore I used the following to increase the number:
我有一个服务器，很多用户将连接到它并在那里使用数据库，我正在使用 MySQL。我知道max_connectionsMySQL中的默认数量是 100 或 150，但我确信我需要超出这个数字，因此我使用以下方法来增加数字：
SET global max_connections = 1000000
Now I try to check the max_connectionsas follows:
现在我尝试检查max_connections如下：
show variables like 'max_connections'
It gives me the following:
它给了我以下内容：
max_connections; 100000;
Which is a sign that it succeeded (unless I am understanding it wrong). When my users start to connect I am receiving an error from the server when the number of connected users exceeds 110. The error is:
这是它成功的标志（除非我理解错了）。当我的用户开始连接时，当连接的用户数超过 110 时，我收到来自服务器的错误。错误是：
error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
错误连接：超时已过期。在从池中获取连接之前超时时间已过。这可能是因为所有池连接都在使用中并且达到了最大池大小。
Why am I getting this error, and how to fix it?
为什么我会收到此错误，以及如何修复它？
回答by Jason Heo
How to change max_connections
如何改变 max_connections
You can change max_connectionswhile MySQL is running via SET:
您可以max_connections在 MySQL 运行时通过SET以下方式进行更改：
mysql> SET GLOBAL max_connections = 5000;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE "max_connections";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 5000 |
+-----------------+-------+
1 row in set (0.00 sec)
To OP
到操作
timeoutrelated
timeout有关的
I had never seen your error message before, so I googled. probably, you are using Connector/Net. Connector/Net Manualsays there is max connection pool size. (default is 100) see table 22.21.
我以前从未见过你的错误信息，所以我用谷歌搜索。可能，您正在使用连接器/网络。连接器/网络手册说有最大连接池大小。（默认为 100）见表 22.21。
I suggest that you increase this value to 100k or disable connection pooling Pooling=false
我建议您将此值增加到 100k 或禁用连接池 Pooling=false
UPDATED
更新
he has two questions.
他有两个问题。
Q1 - what happens if I disable poolingSlow down making DB connection. connection poolingis a mechanism that use already made DB connection. cost of Making new connection is high. http://en.wikipedia.org/wiki/Connection_pool
Q1 - 如果我禁用池会发生什么放慢建立数据库连接。connection pooling是一种使用已经建立的数据库连接的机制。建立新连接的成本很高。http://en.wikipedia.org/wiki/Connection_pool
Q2 - Can the value of pooling be increased or the maximum is 100?
Q2 - 池化的值是否可以增加或最大值为 100？
you can increase but I'm sure what is MAX value, maybe max_connectionsin my.cnf
你可以增加，但我确定什么是 MAX 值，也许max_connections在 my.cnf
My suggestion is that do not turn off Pooling, increase value by 100 until there is no connection error.
我的建议是不要关闭 Pooling，将 value 增加 100，直到没有连接错误。
If you have Stress Test tool like JMeteryou can test youself.
如果你有像JMeter你可以测试自己的压力测试工具。
回答by Sumit Kumar Gupta
You can set max connections using:
您可以使用以下方法设置最大连接数：
set global max_connections = '1 < your number > 100000';
set global max_connections = '1 < your number > 100000';
This will set your number of mysql connection unti (Requires SUPERprivileges).
这将设置您的 mysql 连接数 unti（需要SUPER权限）。
