在for循环中依次执行insert、update、query、delete 50万次，即使我把MySQL的max connection设置为10000，运行十秒后还是出现“too many connections”。运行“show processlist”时， sleep 状态连接数迅速上升到 10,000 左右。 (但是单独执行CURD时还好，很快就完成1000,000次循环)。

只执行一次 for 循环工作正常:2017-05-16 17:10:26 总成本时间 5.241008ms。

下面是代码:

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
}

MySQL进程列表:

mysql> show full processlist;
...............
| 1292138 | cid  | localhost:46799 | ciddb | Sleep   |    0 |          | NULL                  |
| 1292139 | cid  | localhost:46800 | ciddb | Sleep   |    0 |          | NULL                  |
| 1292140 | cid  | localhost:46801 | ciddb | Sleep   |    0 |          | NULL                  |
| 1292141 | cid  | localhost:46802 | ciddb | Sleep   |    0 |          | NULL                  |
+---------+------+-----------------+-------+---------+------+----------+-----------------------+
8719 rows in set (0.04 sec)

MySQL 错误信息:

2017-05-16 16:37:56 <LL_ERROR> stmt_insert Exec err:Error 1040: Too many connections. 
2017-05-16 16:37:56 <LL_ERROR> stmt_update Exec err:Error 1040: Too many connections. 
2017-05-16 16:37:56 <LL_ERROR> Query err:Error 1040: Too many connections. 2017-05-16 16:37:56 <LL_ERROR> stmt_del Exec err:Error 1040: Too many connections. 
2017-05-16 16:37:56 <LL_ERROR> stmt_insert Exec err:Error 1040: Too many connections. 
2017-05-16 16:37:56 <LL_ERROR> stmt_update Exec err:Error 1040: Too many connections 

MySQL 设置:max_connections 10050 set global wait_timeout = 30;设置全局 interactive_timeout = 30;

这是一个内存表，有 5 个索引:KEY index1 (session_key), KEY index2 (Nas-IP), KEY index3 (user_name), KEY index4 (nat_ip), KEY index5 (frame_ip)

最佳答案

循环内部有一个 defer，它只是将 row.Close 函数压入堆栈，并在函数返回时执行。你可能想要这样的东西:

for i:=0;i<500000;i++{
    ...
    sql := "SELECT * FROM memDB1 where session_key='" + session_key+"'"
    row, err := db.Query(sql)
    if err != nil {
        Log(LL_ERROR, "Query err:", err.Error(), ".")
        continue
    }
    row.Close()
    ...
}

支持文档: https://blog.golang.org/defer-panic-and-recover

关于MySQL 连接错误(使用 golang): too many connections, 太多(8000 多个) sleep 连接正在运行 show processlist，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/44000340/