如何以编程方式在MySQL中设置max_connections [英] How to set max_connections in MySQL Programmatically

问题描述

我有一台服务器，许多用户将连接到该服务器并在那里使用数据库，而我正在使用MySQL.我知道MySQL中max_connections的默认数量是100或150，但是我确定我需要的数量超出该数量，因此我使用以下代码来增加数量:

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:

SET global max_connections = 1000000

现在，我尝试如下检查max_connections:

Now I try to check the max_connections as follows:

show variables like 'max_connections'

它给了我以下内容:

max_connections; 100000;

这是成功的标志(除非我理解错误).当我的用户开始连接时，当连接的用户数超过110时，我从服务器收到一个错误.错误是:

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:

连接错误:超时已过期.超时时间已过 从池中获取连接.这可能发生了 因为所有池化连接都在使用中，最大池大小为 达到了.

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?

推荐答案

如何更改max_connections

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

How to change max_connections

You can change max_connections while MySQL is running via 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)

致OP

timeout相关

我以前从未见过您的错误消息，所以我用Google搜索.可能您正在使用连接器/网络. 连接器/网络手册 说有最大连接池大小. (默认值为100)，请参见表22.21..

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.

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

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

他有两个问题.

第一季度-如果我禁用池化会发生什么情况 减慢建立数据库连接的速度. connection pooling是一种使用已经建立的数据库连接的机制.建立新连接的成本很高. http://en.wikipedia.org/wiki/Connection_pool

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

第2季度-可以增加合并的值还是将其最大值增加为100?

您可以增加，但是我确定my.cnf中的max_connections是最大值

you can increase but I'm sure what is MAX value, maybe max_connections in my.cnf

我的建议是不要关闭Pooling，将值增加100，直到没有连接错误为止.

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

如果您具有JMeter之类的压力测试工具，则可以进行自我测试.

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

这篇关于如何以编程方式在MySQL中设置max_connections的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！