CODEWITHSUNDEEP
mysqlgo
jimy
jimy

Reputation: 3

MySQL connection err (with golang): too many connections, too many (8000 more) sleep connections running show processlist

Execute insert, update, query and delete sequentially in for loop for 500,000 times, "too many connections" occurs after running for ten seconds even I set max connection of MySQL to 10,000. When running 'show processlist', the number of sleep status connections quickly raise up to around 10,000. (but it's fine when executing CURD separately, very quick to finish 1000,000 times of loop).

Only execute for loop for one time works fine: 2017-05-16 17:10:26 total cost time5.241008ms.

below is the code:

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
}

The MySQL process list:

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 error messages:

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 setting: max_connections 10050 set global wait_timeout = 30; set global interactive_timeout = 30;

it's a memory table and had 5 indexes: KEY index1 (session_key), KEY index2 (Nas-IP), KEY index3 (user_name), KEY index4 (nat_ip), KEY index5 (frame_ip)

Upvotes: 0

Views: 2892

Answers (1)

william.taylor.09
william.taylor.09

Reputation: 2215

You have a defer inside of a loop, which just pushes the row.Close function onto the stack, and will execute when the function returns. You probably want something like this instead:

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()
    ...
}

Supporting docs: https://blog.golang.org/defer-panic-and-recover

Upvotes: 1

Related Questions