英文:
How can we run queries concurrently, using go routines?
问题
我正在使用gorm v1（ORM），go版本为1.14。
DB连接在我的应用程序启动时创建，
并且该DB在整个应用程序中传递。
我有一个复杂且长的功能。
假设我有10组要运行的查询，顺序无关紧要。
所以，我做的是
go queryset1(DB)
go queryset2(DB)
...
go queryset10(DB)
// 这里有一个等待，可以通过通道或WaitGroup实现。
在queryset1中：
func queryset1(db *gorm.DB, /*wg或errChannel*/){
db.Count() // 基本的计数查询
wg.Done() 或 errChannel <- nil
}
现在的问题是我遇到了错误: 1040 "too many connections" - Mysql。
为什么会发生这种情况？每个go例程是否都会创建一个新的连接？
如果是这样，有没有办法检查mysql中的这些"活动连接"
（而不是像连接一样显示状态变量）？
如何并发地查询数据库？
编辑：
这个人遇到了同样的问题
英文:
I am using gorm v1 (ORM), go version 1.14
DB connection is created at the start of my app
and that DB is being passed throughout the app.
I have a complex & long functionality.
Let's say I have 10 sets of queries to run and the order doesn't matter.
So, what I did was
go queryset1(DB)
go queryset2(DB)
...
go queryset10(DB)
// here I have a wait, maybe via channel or WaitGroup.
Inside queryset1:
func queryset1(db *gorm.DB, /*wg or errChannel*/){
db.Count() // basic count query
wg.Done() or errChannel <- nil
}
Now, the problem is I encounter the error :1040 "too many connections" - Mysql.
Why is this happening? Does every go routine create a new connection?
If so, is there a way to check this & "live connections" in mysql
(Not the show status variables like connection)
How can I concurrently query the DB?
答案1
得分: 2
错误与go-gorm本身无直接关系，而是与底层的MySQL配置和初始连接配置有关。在你的代码中，你可以在初始连接数据库时管理以下参数：
- 最大打开连接数（SetMaxOpenConns 函数）
- 最大空闲连接数（SetMaxIdleConns 函数）
- 空闲连接的最大超时时间（SetConnMaxLifetime 函数）
要了解更多详细信息，请查阅官方文档或这篇文章，了解如何从连接配置中获得最大性能。
如果你想避免每个 goroutine 使用单独的连接，你可以这样做：
// 限制同时执行的 goroutine 数量为 5
connCh := make(chan bool, 5)
go queryset1(DB, &wg, connCh)
go queryset2(DB, &wg, connCh)
...
go queryset10(DB, &wg, connCh)
wg.Wait()
close(connCh)
在你的 queryset 函数中：
func queryset1(db *gorm.DB, wg *sync.WaitGroup, connCh chan bool){
connCh <- true
db.Count() // 基本的 count 查询
<-connCh
wg.Done()
}
connCh 允许前 5 个 goroutine 写入它，并阻塞其他 goroutine 的执行，直到其中一个前 5 个 goroutine 从 connCh 通道中取走值。这将防止每个 goroutine 启动自己的连接。一些连接应该被重用，但这也取决于初始连接配置。
英文:
The error is not directly related to go-gorm, but to the underlying MySQL configuration and your initial connection configuration. In your code, you can manage the following parameters during your initial connection to the database.
- maximum open connections (SetMaxOpenConns function)
- maximum idle connections (SetMaxIdleConns function)
- maximum timeout for idle connections (SetConnMaxLifetime function)
For more details, check the official docs or this article how to get the maximum performance from your connection configuration.
If you want to prevent a situation where each goroutine uses a separate connection, you can do something like this:
// restrict goroutines to be executed 5 at a time
connCh := make(chan bool, 5)
go queryset1(DB, &wg, connCh)
go queryset2(DB, &wg, connCh)
...
go queryset10(DB, &wg, connCh)
wg.Wait()
close(connCh)
Inside your queryset functions:
func queryset1(db *gorm.DB, wg *sync.WaitGroup, connCh chan bool){
connCh <- true
db.Count() // basic count query
<-connCh
wg.Done()
}
The connCh will allow the first 5 goroutines to write in it and block the execution of the rest of the goroutines until one of the first 5 goroutines takes the value from the connCh channel. This will prevent the situations where each goroutine will start it's own connection. Some of the connections should be reused, but that also depends on the initial connection configuration.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论