萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql Too many connections錯誤的解決辦法

mysql Too many connections錯誤的解決辦法

Too many connections是連接太多了正確的說是數據庫的配置有問題了,我們這裡來給各位分析一下關於Too many connections問題的解決辦法.

產生這種問題的原因是:

連接數超過了 MySQL 設置的值,與 max_connections 和 wait_timeout  都有關系。wait_timeout 的值越大,連接的空閒等待就越長,這樣就會造成當前連接數越大。

解決方法:

修改MySQL配置文件/etc/my.cnf,設置成max_connections=1000,wait_timeout=5。如果沒有此項設置可以自行添加,修改後重啟MySQL服務即可。要不經常性報此錯誤,則要對服務器作整體性能優化

補充:

在工作中,大家或許常常遇到Too many connections這個錯誤,這時作為DBA想進數據庫管理都進不去,是非常尴尬的一件事情。當然有同學說可以修改配置文件,但是修改配置文件是需要重啟mysqld的,這在業務繁忙的數據庫服務器上是不允許的。所以緊急情況下可以采用如下的方法,比如下面的測試。

[root@mysql-server-01 msb_5_6_19]# ./use
ERROR 1040 (HY000): Too many connections
[root@mysql-server-01 msb_5_6_19]#
我上面是采用MySQL沙箱環境,關於沙箱環境的簡單安裝及使用請參看我前面的文章。MySQL Sandbox安裝使用
可以看見我上面已經報了錯誤,提示也非常明顯,就是我們配置的連接數太小,現在已經用完了,這時我們剛想進數據庫做些操作,那麼采用如下方法:


[root@mysql-server-01 ~]# gdb -p $(cat /root/sandboxes/msb_5_6_19/data/mysql_sandbox5619.pid) -ex "set max_connections=500" -batch 
[New LWP 27541]
[New LWP 27540]
[New LWP 27539]
[Thread debugging using libthread_db enabled]
0x00000031152df343 in poll () from /lib64/libc.so.6
[root@mysql-server-01 ~]#

下面再次登錄數據庫看看,並查看最大連接數是否已經修改


[root@mysql-server-01 msb_5_6_19]# ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {msandbox} ((none)) > show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) >

在Percona5.5的thread_pool裡面提供了2個參數extra_port和extra_max_connections預留額外的連接,預防連接滿了以後我們無法進入數據庫進行相應的管理。


root@localhost : (none) 23:18:00> show variables like '%extra%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| extra_max_connections | 1     |
| extra_port            | 10086 |
+-----------------------+-------+
2 rows in set (0.00 sec)

root@localhost : (none) 23:18:04>

[root@mysql-server-01 user_3307]# netstat -nltp | grep 10086
tcp        0      0 0.0.0.0:10086               0.0.0.0:*                   LISTEN      29655/mysqld       
[root@mysql-server-01 user_3307]#
我這裡使用了10086端口,以及最大連接數為1。有這麼貼心的功能。必須給一個贊

總結:

通常控制最大連接數有兩個參數控制max_connections(該實例允許最大的連接數 ),max_user_connections(該實例允許每個用戶的最大連接數),通常情況下前期我們就需要規劃好多少連接數合適。一般情況下建議不要超過300。因為MySQL在連接數上升的情況下性能下降非常厲害,如果需要大量連接,這時可以引入thread_pool。所以我們需要保持一個原則:系統創建的用戶(給應用使用用戶)數* max_user_connections  < max_connections。這樣就不會發生文章開始說的問題。


java提示Too many connections錯誤的解決辦法

MySQL的錯誤,待解決[已解決]Data source rejected establishment of connection message from server: "Too many connections";

[15:22:53,461] [CustomExceptionHandler,44] - Handle Exception:hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [08004]; error code [1040]; Data source rejected establishment of connection message from server: "Too many connections"; nested exception is Java.sql.SQLException: Data source rejected establishment of connection message from server: "Too many connections"
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [08004]; error code [1040]; Data source rejected establishment of connection message from server: "Too many connections"; nested exception is java.sql.SQLException: Data source rejected establishment of connection message from server: "Too many connections"
java.sql.SQLException: Data source rejected establishment of connection message from server: "Too many connections"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1906)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:2535)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:817)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:1782)
at com.mysql.jdbc.Connection.<init>(Connection.java:450)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:291)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:277)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:259)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:241)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:80)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:315)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:109)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2150)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:300)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:146)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1093)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at com.easou.framework.dao.hibernate.BaseDAOHibernate.pageListQuery(BaseDAOHibernate.java:164)
at com.vitamin.cn.manager.news.dao.hibernate.NewsDAOHibernate.searchNews(NewsDAOHibernate.java:38)
at com.vitamin.cn.manager.news.bl.impl.NewsManagerImpl.searchNews(NewsManagerImpl.java:24)
at com.vitamin.cn.manager.news.action.NewsAction.listNews(NewsAction.java:88)
at com.vitamin.cn.manager.news.action.NewsAction.execute(NewsAction.java:35)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:414)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at com.easou.framework.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:88)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:524)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:199)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:282)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697)
at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)

1.可能是mysql的max connections設置的問題
2.可能是多次insert,update操作沒有關閉session,需要在spring裡配置transaction支持。

解決:

1.修改tomcat裡的session 的time-out時間減少為20,(不是必改項)
2.對處理量大的對數據庫insert或update的操作提供transaction支持.

=======================================
下面的是解決辦法:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"

原因:

因為你的mysql安裝目錄下的my.ini中設定的並發連接數太少或者系統繁忙導致連接數被占滿

解決方式:

打開MYSQL安裝目錄打開MY.INI找到max_connections(在大約第93行)默認是100 一般設置到500~1000比較合適,重啟mysql,這樣1040錯誤就解決啦。
max_connections=1000

一定要重新啟動MYSQL才能生效

CMD->

net stop mysql

net start mysql
 

關於改變innodb_log_file_size後無法啟動mysql的問題

innodb_buffer_pool_size=768M
innodb_log_file_size=256M
innodb_log_buffer_size=8M
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=20
以上是對innodb引擎的初步優化, 發現是更新innodb_log_file_size=256M時候出現了問題,只要加上這個就無法啟動,


後來才知道原來要STOP服務先,然後再刪除原來的文件………
打開/MySQL Server 5.5/data

刪除ib_logfile0, ib_logfile1........ib_logfilen
再開啟選項,成功啟動。


一,修改配置文件文件

修改/etc/my.cnf這個文件,在[mysqld]中新增max_connections=N,如果你沒有這個文件請從編譯源碼中的support-files文件夾中復制你所需要的*.cnf文件為到/etc/my.cnf。我使用的是my-medium.cnf,中型服務器配置。例如我的[mysqld]的內容如下


[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 160M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections=1000

由於對mysql還不是很熟悉,所以很多參數沒有修改。哈哈。。

2、非使用mysqld腳本自動啟動的用戶。

修改$MYSQL_HOME/bin/mysqld_safe文件
例如:/usr/local/mysql/bin/mysqld_safe這個文件
grep -n 'max_connection' $MYSQL_HOME/bin/mysqld_safe
修改對應行號的max_connections參數值
以上方法為參考網上的做法寫的


關於改變innodb_log_file_size後無法啟動mysql的問題

innodb_buffer_pool_size=768M
innodb_log_file_size=256M
innodb_log_buffer_size=8M
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=0
innodb_thread_concurrency=20
以上是對innodb引擎的初步優化, 發現是更新innodb_log_file_size=256M時候出現了問題,只要加上這個就無法啟動,

後來才知道原來要STOP服務先,然後再刪除原來的文件………
打開/MySQL Server 5.5/data

刪除ib_logfile0, ib_logfile1........ib_logfilen

再開啟選項,成功啟動。

copyright © 萬盛學電腦網 all rights reserved