如何以编程方式在MySQL中设置max_connections ?

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_connections in 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。我知道MySQL中max_connections的默认数量是100或150，但我确信我需要的远不止这个数字，因此我使用了以下方法来增加这个数字:

SET global max_connections = 1000000

Now I try to check the max_connections as 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?

为什么我得到这个错误，以及如何修正它?

1 个解决方案

#1

How to change max_connections

You can change max_connections while MySQL is running via SET:

当MySQL通过SET运行时，可以更改max_connections:

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

timeout related

I had never seen your error message before, so I googled. probably, you are using Connector/Net. Connector/Net Manual says there is max connection pool size. (default is 100) see table 22.21.

我以前从未见过你的错误信息，所以我用谷歌搜索了一下。可能，您正在使用连接器/Net。连接器/网络手册说有最大连接池大小。(默认为100)见表22.21。

I suggest that you increase this value to 100k or disable connection pooling Pooling=false

我建议将该值增加到100k或禁用连接池池池池=false

UPDATED

he has two questions.

他有两个问题。

Q1 - what happens if I disable pooling Slow down making DB connection. connection pooling is a mechanism that use already made DB connection. cost of Making new connection is high. http://en.wikipedia.org/wiki/Connection_pool

如果禁用池，使DB连接变慢，会发生什么?连接池是一种使用已经建立的DB连接的机制。新连接的成本很高。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_connections in my.cnf

你可以增加，但我确定最大值是多少，可能是my.cnf中的max_connections

My suggestion is that do not turn off Pooling, increase value by 100 until there is no connection error.

我的建议是不要关闭池，将值增加100，直到没有连接错误。

If you have Stress Test tool like JMeter you can test youself.

如果你有像JMeter这样的压力测试工具，你可以自己测试。