【求助】连接MySQL报错too many connections, show processlist太多(8000多条)sleep状态

jimyokl · 2017-05-16 06:48:10 · 3443 次点击 · 大约8小时之前 开始浏览    置顶
这是一个创建于 2017-05-16 06:48:10 的主题,其中的信息可能已经有所发展或是发生改变。

按顺序执行插入、更新、查询、删除,运行十几秒后报错太多连接,MySQL设置最大连接一万也不够,show processlist十秒钟不到就显示一万个sleep状态的连接,请教大神帮忙解释下我是有连接没关闭吗? 代码:

func insertUnis(db *sql.DB, name string){

stmt_insert, err := db.Prepare("insert into memDB1 values(?,?,'world.cnworld.cnworld.cnworld.cn',?,'ee02:123::af01:9231:df18:8998:ee02:123::af01:9231:df18:8998:abcd','2017070035|2703258|1943|0|0|10.121.205.248|088:abcd:e02:123::/64','Framed-Interface-Id`varchar(64)COLLATEutf8_binNOTNULLdefaultthah','varchar(64)COLLATEutf8_binNOTNULLdefault0xBF019231DF18899934hdas','Call-From-Id`varchar(32)COLLATEd',?,'Current-Bandwidt','NAS-Port-Idvarchar(256)COLLATEutf8_binNOTNULLdefaultsrunk 6/0/36:33.351 0/0/0/0/0/0NAS-Port-Idvarchar(256)COLLATEutf8_binNOTNULLdefaultsrunk 6/0/36:33.351 0/0/0/0/0/0NAS-Port-Idvarchar(256)COLLATEutf8_binNOTNULLdefaultsrunk 6/0/36:33.351 0/0/0/0/0/0sefiuhu',48204,57239,2458787490,2458787490,2458787490,'Static-Bandwidthvhar(24)','Bas-Bandwidth` varchar(64) COLLATE utf8_bin NOT NULL defaultegse','Session-Id varchar(64) COLLATE utf8_bin NOT NULL defaultlasefjie','Coa-Session-Id` varchar(64) COLLATE utf8_bin NOT NULL defaultsdf','macaddr`varchar(32) COLLATE utf8',?,65535,45656,'Reseverd1` varchar(32) COLLATE u',4294967292)")

if err != nil {
    Log(LL_ERROR, "stmt_insert Prepare err:", err.Error(), ".")
}
defer stmt_insert.Close()
stmt_update, err := db.Prepare("update memDB1 set user_name=? where session_key=?")
if err != nil {
    Log(LL_ERROR, "stmt_update Prepare err:", err.Error(), ".")
}    
defer stmt_update.Close()
stmt_del, err := db.Prepare("delete from memDB1 where session_key=?")
    if err != nil {
        Log(LL_ERROR, "stmt_del Prepare err:", err.Error(), ".")
}
defer stmt_del.Close() 
for i:=0;i<500000;i++{
    session_key:=string(Krand(64,3))
    user_name := string(Krand(64,3))
    frame_ip := string(Krand(32,3))        
    Nas_IP := string(Krand(32,3))    
    nat_ip := string(Krand(32,3))    

    _, err = stmt_insert.Exec(session_key,user_name,frame_ip,Nas_IP,nat_ip)
    if err != nil {
        Log(LL_ERROR, "stmt_insert Exec err:", err.Error(), ".")
    }

    _, err = stmt_update.Exec(string(Krand(64,3)), session_key)
    if err != nil {
        Log(LL_ERROR, "stmt_update Exec err:", err.Error(), ".")
    }
    sql := "SELECT * FROM memDB1 where session_key='" + session_key+"'"
    row, err := db.Query(sql)
    defer row.Close()
    if err != nil {
        Log(LL_ERROR, "Query err:", err.Error(), ".")
    }
    _, err = stmt_del.Exec(session_key)
     if err != nil {
        Log(LL_ERROR, "stmt_del Exec err:", err.Error(), ".")
    }
}
common_package.WgDay.Done()
return

}

show processlist:

| 1234491 | cid | localhost:36979 | ciddb | Sleep | 0 | | NULL | | 1234492 | cid | localhost:36980 | ciddb | Sleep | 0 | | NULL | | 1234493 | cid | localhost:36981 | ciddb | Sleep | 0 | | NULL | | 1234494 | cid | localhost:36982 | ciddb | Sleep | 0 | | NULL | | 1234495 | cid | localhost:36983 | ciddb | Sleep | 0 | | NULL | | 1234496 | cid | localhost:36984 | ciddb | Sleep | 0 | | NULL | | 1234497 | cid | localhost:36985 | ciddb | Sleep | 0 | | NULL | | 1234498 | cid | localhost:36986 | ciddb | Sleep | 0 | | NULL | +---------+------+-----------------+-------+---------+------+----------+-----------------------+

8040 rows in set (0.01 sec)


有疑问加站长微信联系(非本文作者)

入群交流(和以上内容无关):加入Go大咖交流群,或添加微信:liuxiaoyan-s 备注:入群;或加QQ群:692541889

3443 次点击  ∙  1 赞  
加入收藏 微博
5 回复  |  直到 2017-06-14 03:38:05
smallforest
smallforest · #1 · 8年之前

需要关闭一下链接吧

去掉一些defer 运行结束就关闭

jimyokl
jimyokl · #2 · 8年之前

谢谢 @smallforest 我把3个stmt合为一个,在循环外 defer stmt.Close()

jimyokl
jimyokl · #3 · 8年之前

改了以后还是报错: 2017-05-16 16:37:52 <LL_ERROR> stmt_del Exec err:Error 1040: Too many connections. 2017-05-16 16:37:52 <LL_ERROR> stmt_insert Exec err:Error 1040: Too many connections. 2017-05-16 16:37:52 <LL_ERROR> stmt_update Exec err:Error 1040: Too many connections. 2017-05-16 16:37:52 <LL_ERROR> Query err:Error 1040: Too many connections.

monky
monky · #4 · 8年之前

关闭的不对啊 db.close()

jimyokl
jimyokl · #5 · 8年之前

谢谢了@monky

添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传