Mysql Too many connections问题

Too many connections


最近发现两台MySQL server在中午的时候忽然(很突然的那种)发飙,不断的挂掉。重启mysql也尽是失败,看mysql的errorlog,只能看到类似如下的信息:

Forcing close of thread 12232 user: 'root'

用mysqladmin 简单的监控了下mysql的情况:

mysqladmin -uroot -p******** status -i 1

发现Queries per second avg只有200左右,可以说很低,但是Threads 确非常不稳定,居然会瞬间升级200以上,一般情况下这个线程这个值都是不会高于5的个位数!

然后继续看

mysqladmin -uroot -p******** processlist

居然有大量的unauthenticated user?? 如下情况

 

+------+-----------+---------+----+---------+------+-------+------------------+
[root@app028 ~]# mysqladmin -uroot -p************ processlist
+------+-----------+---------+----+---------+------+-------+------------------+
| Id   | User                 | Host               | db | Command | Time | State | Info       |
+------+-----------+---------+----+---------+------+-------+------------------+
| 2007 | unauthenticated user | 192.168.4.29:58519 |    | Connect |     | login | |
| 2008 | unauthenticated user | 192.168.4.29:58553 |    | Connect |     | login | |
| 2009 | unauthenticated user | 192.168.4.29:58571 |    | Connect |     | login | |
| 2010 | unauthenticated user | 192.168.4.29:58577 |    | Connect |     | login | |
| 2011 | unauthenticated user | 192.168.4.29:58579 |    | Connect |     | login | |
| 2012 | unauthenticated user | 192.168.4.29:58589 |    | Connect |     | login | |

google了一下,

发现这算属MySQL的一个bug,不管连接是通过hosts还是ip的方式,MySQL都会对DNS做反查,IP到DNS,由于反查的接续速度过 慢(不管是不是isp提供的dns服务器的问题或者其他原因),大量的查询就难以应付,线程不够用就使劲增加线程,但是却得不到释放,所以MySQL会 “假死”。

解决的方案很简单,结束这个反查的过程,禁止任何解析。

打开mysql的配置文件(my.cnf),在[mysqld]下面增加一行:

skip-name-resolve

重新载入配置文件或者重启MySQL服务即可。






Too Many Connections问题

02-23

我数据库用的是MYSQL rnrn先贴出我连接数据库的代码:rn[code=VB.NET]rn Public Sub New()rn connectingstring = System.Configuration.ConfigurationSettings.AppSettings("Conn").ToString.Trimrn 'connectingstring = "server=localhost;user id=root;pwd=;database=proto_management"rn myConn = New MySqlConnection(connectingstring)rn 'Close()rn End Subrnrn Public Sub Close()rn myConn.Close()rn myConn.Dispose()rn End Subrn[/code]rn我是调用web.config的connection来连接数据库的rnrn再贴出我web.config的代码:rn[code=XML] rn rn [/code]rnrn我每次调用New()都会带上close()的但是当我操作我的程序时。也其实就是增删查改 到不知道多少次的时候就会报出Too Many Connections 的warning。我上网查过 他们说改mysql的配置文件把连接端口增加到10000或者更多,但是这并不能根治Too Many Connections的错误。rnrn其实这个错误就是打开端口过多。但是我每次打开端口的时候都调用了close的函数了啊。为什么端口还是存在呢?是挂在内存里面吗?我后来在close()的函数里面再加了dispose。但是一点效果都没有,还是继续Too Many Connections。请大家帮忙解答,解答。因为代码过多。在这不好贴出来。这个程序我是用类集约函数的。当需要连接的时候NEW()close()都是在这个类里面调用。这个类里面还有很多绑定函数,期间都有调用到NEW()而且每次最后都会close().请大家帮我分析分析 谢谢。

Too many connections 错误

03-27

这个错误一般什么情况下发生,如何处理呢。rnrnrn[code=Java]rn16:45:51,703 WARN JDBCExceptionReporter:71 - SQL Error: 1040, SQLState: 08004rn16:45:51,703 ERROR JDBCExceptionReporter:72 - Data source rejected establishment of connection, message from server: "Too many connections"rn16:45:51,718 WARN JDBCExceptionReporter:71 - SQL Error: 1040, SQLState: 08004rn16:45:51,718 ERROR JDBCExceptionReporter:72 - Data source rejected establishment of connection, message from server: "Too many connections"rn16:45:51,718 INFO DefaultLoadEventListener:95 - Error performing load commandrnorg.hibernate.exception.JDBCConnectionException: Cannot open connectionrn at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:74)rn at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)rn at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)rn at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:420)rn at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)rn at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)rn at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)rn at org.hibernate.loader.Loader.doQuery(Loader.java:661)rn at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)rn at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)rn at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:47)rn at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:41)rn at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:2730)rn at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:365)rn at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:346)rn at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:123)rn at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:177)rn at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:87)rn at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:862)rn at org.hibernate.impl.SessionImpl.get(SessionImpl.java:799)rn at org.springframework.orm.hibernate3.HibernateTemplate$2.doInHibernate(HibernateTemplate.java:489)rn at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)rn at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:483)rn at org.springframework.orm.hibernate3.HibernateTemplate.get(HibernateTemplate.java:477)rn at model.CibprojectDAO.findById(CibprojectDAO.java:67)rn at busdao.XiangmuobjectDAOImpl.saveXiangMuData(XiangmuobjectDAOImpl.java:38)rn at autorun.ReadTxt2DB.ReadDataDBWrite(ReadTxt2DB.java:146)rn at autorun.ReadTxt2DB.init(ReadTxt2DB.java:211)rn at autorun.MyTask.run(MyTask.java:12)rn at java.util.TimerThread.mainLoop(Timer.java:512)rn at java.util.TimerThread.run(Timer.java:462)rnCaused by: com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"rn at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921)rn at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1053)rn at com.mysql.jdbc.Connection.createNewIO(Connection.java:2743)rn at com.mysql.jdbc.Connection.(Connection.java:1553)rn at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)rn at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)rn at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)rn at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:771)rn at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)rn at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)rn at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)rn at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:417)rn ... 27 morern16:45:51,718 ERROR CibprojectDAO:71 - get failedrnorg.springframework.dao.DataAccessResourceFailureException: Cannot open connection; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connectionrnCaused by: rn[/code]

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试