MySQL Exception: message from server: "Too many connections"

最新推荐文章于 2024-03-22 16:00:44 发布

Ethan_pika

最新推荐文章于 2024-03-22 16:00:44 发布

阅读量1.4k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MySQL及调优文章标签： MySQL Exception

本文链接：https://blog.youkuaiyun.com/yitian_z/article/details/84768293

MySQL及调优专栏收录该内容

1 篇文章

订阅专栏

本文详细记录了解决MySQL因连接数过多导致的Too many connections错误的过程，包括调整max_connections参数，重启MySQL服务，并验证修改效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近在运行一个项目的过程中，出现如下MySQL异常：

2018-12-03 12:31:48  [ SlaveThread:19930 ] - [ ERROR ]  Error starting DataManager
java.sql.SQLException: Error preloading the connection pool
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2085)
	at org.apache.commons.dbcp2.BasicDataSource.getLogWriter(BasicDataSource.java:1588)
	at org.apache.commons.dbcp2.BasicDataSourceFactory.createDataSource(BasicDataSourceFactory.java:595)
	at zyt.custom.my.scheduler.DataManager.<init>(DataManager.java:62)
	at zyt.custom.my.scheduler.DataManager.getInstance(DataManager.java:73)
	at zyt.custom.my.scheduler.WorkerMonitor.<init>(WorkerMonitor.java:52)
	at zyt.custom.my.scheduler.WorkerMonitor.getInstance(WorkerMonitor.java:38)
	at storm.benchmark.AdvertisingTopology$EventFilterBolt.prepare(AdvertisingTopology.java:188)
	at org.apache.storm.topology.IRichBoltDelegate.prepare(IRichBoltDelegate.java:55)
	at com.twitter.heron.instance.bolt.BoltInstance.init(BoltInstance.java:167)
	at com.twitter.heron.instance.Slave.startInstanceIfNeeded(Slave.java:222)
	at com.twitter.heron.instance.Slave.handleNewAssignment(Slave.java:173)
	at com.twitter.heron.instance.Slave.handleNewPhysicalPlan(Slave.java:349)
	at com.twitter.heron.instance.Slave.access$300(Slave.java:49)
	at com.twitter.heron.instance.Slave$1.run(Slave.java:118)
	at com.twitter.heron.common.basics.WakeableLooper.executeTasksOnWakeup(WakeableLooper.java:160)
	at com.twitter.heron.common.basics.WakeableLooper.runOnce(WakeableLooper.java:89)
	at com.twitter.heron.common.basics.WakeableLooper.loop(WakeableLooper.java:79)
	at com.twitter.heron.instance.Slave.run(Slave.java:180)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1040)
	at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2194)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2225)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2024)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:779)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.GeneratedConstructorAccessor4.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
	at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
	at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:256)
	at org.apache.commons.pool2.impl.GenericObjectPool.create(GenericObjectPool.java:868)
	at org.apache.commons.pool2.impl.GenericObjectPool.addObject(GenericObjectPool.java:961)
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2081)
	... 21 more

分析异常的原因应为MySQL的连接数超出其允许的最大值，因此进行如下设置：

1. 在MySQL安装的Aliyun主机中，修改/etc/my.cnf配置文件内容：

# yitian add: 2018-05-21 for heron
max_connections=2000

该值的默认设置为100，这个值对分布式流处理系统中很容易不够，因此，这里设置的值足够大以避免再次发生该异常。

2. 配置完成后，重新启动MySQL。这里的MySQL使用rpm包进行的安装。因此使用如下命令进行停止和启动：

[root@iZ25dp8d02iZ etc]# service mysqld stop
[root@iZ25dp8d02iZ etc]# service mysqld start

3. 重新启动完成后，使用如下的命令查看修改是否生效：

[root@iZ25dp8d02iZ etc]# mysqladmin -uroot -p variables
Enter password:

可以看到输出的结果中有：

| max_connect_errors                      | 10                                                                                        |
| max_connections                         | 2000                                                                                      |
| max_delayed_threads                     | 20                                                                                        |

4. 修改成功。

重新启动系统后，发现无该异常产生。