英文:
golang MySQL "connection refused"
问题
我是Go（Golang）的新手。我编写了一个简单的基准测试程序，用于测试与MySQL的并发处理。但是，当我增加并发通道的数量时，不断出现"dial tcp 52.55.254.165:3306: getsockopt: connection refused"和"unexpected EOF"错误。
每个Go协程都在一个简单的客户表中进行批量插入，插入的行数从1到n不等。该程序允许设置插入大小（单个语句中的行数）和并行Go协程的数量（每个Go协程执行一次插入操作）。该程序在行数小于100和并行Go协程数小于100时运行良好。但是，当这些数字增加时，特别是并行Go协程的数量增加时，就会出现"unexpected EOF"错误。
我已经搜索了一些线索。根据这些线索，我已经设置了数据库的最大连接数、"max_allowed_packet"和"max_connections"。我还设置了Go程序的db.db.SetMaxOpenConns(200)、db.SetConnMaxLifetime(200)和db.SetMaxIdleConns(10)。我已经尝试了大量和小量的数字（从10到2000）。但是似乎没有解决这个问题。
我有一个全局的数据库连接打开。以下是代码片段：
// 主包
func main() {
var err error
db, err = sql.Open("mysql","usr:pwd@tcp(ip:3306)/gopoc")
if err != nil {
log.Panic(err)
}
db.SetMaxOpenConns(1000)
db.SetConnMaxLifetime(1000)
db.SetMaxIdleConns(10)
// sql.DB 应该是长期存在的，"defer" 会在此函数结束时关闭它
defer db.Close()
if err = db.Ping(); err != nil {
log.Panic(err)
}
http.HandleFunc("/addCust/", HFHandleFunc(addCustHandler))
http.ListenAndServe(":8080", nil)
}
// 添加客户处理程序
func addCustHandler(w http.ResponseWriter, r *http.Request) {
// 预期的 URL: /addCust/?num=3$pcnt=1
num, _ := strconv.Atoi(r.URL.Query().Get("num"))
pcnt, _ := strconv.Atoi(r.URL.Query().Get("pcnt"))
ch := make([]chan string, pcnt) // 初始化通道切片
for i := range ch {
ch[i] = make(chan string, 1)
}
var wg sync.WaitGroup
for i, chans := range ch {
wg.Add(1)
go func(cha chan string, ii int) {
defer wg.Done()
addCust(num)
cha <- "Channel[" + strconv.Itoa(ii) + "]\n"
}(chans, i)
}
wg.Wait()
var outputstring string
for i := 0; i < pcnt; i++ {
outputstring = outputstring + <-ch[i]
}
fmt.Fprintf(w, "Output:\n%s", outputstring)
}
func addCust(cnt int) sql.Result {
...
sqlStr := "INSERT INTO CUST (idCUST, idFI, fName, state, country) VALUES "
for i := 0; i < cnt; i++ {
sqlStr += "(" + strconv.Itoa(FiIDpadding+r.Intn(CidMax)+1) + ", " + strconv.Itoa(FiID) +", 'fname', 'PA', 'USA), "
}
// 去掉最后一个逗号
sqlStr = sqlStr[0:len(sqlStr)-2] + " on duplicate key update lname='dup';"
res, err := db.Exec(sqlStr)
if err != nil {
panic("\nInsert Statement error\n" + err.Error())
}
return res
}
希望这可以帮助你解决问题。
英文:
I'm new in Go (Golang). I wrote a simple benchmark program to test the concurrent processing with MySQL. Keep getting "dial tcp 52.55.254.165:3306: getsockopt: connection refused", "unexpected EOF" errors when I increase the number of concurrent channels.
Each go routine is doing a batch insert of 1 to n number of row to a simple customer table. The program allows to set variable insert size (number of rows in a single statement) and number of parallel go routine (each go routine performs one insert above). Program works fine with small numbers row<100 and number go routines<100. But start getting Unexpected EOF errors when the numbers increase, especially the number of parallel go routines.
Did search for clues. Based on them, I've set the database max connection and 'max_allowed_packet' and 'max_connections'. I've also set the go program db.db.SetMaxOpenConns(200), db.SetConnMaxLifetime(200), db.SetMaxIdleConns(10). I've experimented with big numbers and small (from 10 to 2000). Nothing seems to solve the program.
I have one global db connection open. Code snippet below:
// main package
func main() {
var err error
db, err = sql.Open("mysql","usr:pwd@tcp(ip:3306)/gopoc")
if err != nil {
log.Panic(err)
}
db.SetMaxOpenConns(1000)
db.SetConnMaxLifetime(1000)
db.SetMaxIdleConns(10)
// sql.DB should be long lived "defer" closes it once this function ends
defer db.Close()
if err = db.Ping(); err != nil {
log.Panic(err)
}
http.HandleFunc("/addCust/", HFHandleFunc(addCustHandler))
http.ListenAndServe(":8080", nil)
}
// add customer handler
func addCustHandler(w http.ResponseWriter, r *http.Request) {
// experected url: /addCust/?num=3$pcnt=1
num, _ := strconv.Atoi(r.URL.Query().Get("num"))
pcnt, _ := strconv.Atoi(r.URL.Query().Get("pcnt"))
ch := make([]chan string, pcnt) // initialize channel slice
for i := range ch {
ch[i] = make(chan string, 1)
}
var wg sync.WaitGroup
for i, chans := range ch {
wg.Add(1)
go func(cha chan string, ii int) {
defer wg.Done()
addCust(num)
cha <- "Channel[" + strconv.Itoa(ii) + "]\n"
}(chans, i)
}
wg.Wait()
var outputstring string
for i := 0; i < pcnt; i++ {
outputstring = outputstring + <-ch[i]
}
fmt.Fprintf(w, "Output:\n%s", outputstring)
}
func addCust(cnt int) sql.Result {
...
sqlStr := "INSERT INTO CUST (idCUST, idFI, fName, state, country) VALUES "
for i := 0; i < cnt; i++ {
sqlStr += "(" + strconv.Itoa(FiIDpadding+r.Intn(CidMax)+1) + ", " + strconv.Itoa(FiID) +", 'fname', 'PA', 'USA), "
}
//trim the last ,
sqlStr = sqlStr[0:len(sqlStr)-2] + " on duplicate key update lname='dup';"
res, err := db.Exec(sqlStr)
if err != nil {
panic("\nInsert Statement error\n" + err.Error())
}
return res
}
答案1
得分: 1
我猜你在每个例程中都调用了sql.Open函数？
Open函数应该只调用一次。你应该在例程之间共享已打开的数据库连接。Open函数返回的DB可以并发使用，并且具有自己的连接池。
英文:
I suppose you are calling sql.Open in each of your routine?
The Open function should be called just once. You should share your opened DB connection between your routines. The DB returned by the Open function can be used concurrently and has its own pool
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论