英文:
Gorm with postgres taking too long on first sql
问题
我正在使用Gorm和postgres驱动程序。我正在测试一些查询,并意识到第一个查询总是非常慢(100毫秒+)。我在MySQL中进行了相同的基准测试,但没有出现这种情况。
如果我首先执行"Select by EMAIL user"查询,它将成为慢查询...
我想了解为什么第一个查询总是需要100毫秒以上,以及如何改进它。请帮忙 =)
以下是翻译好的代码部分:
package main
import (
"fmt"
"os"
"strings"
"time"
"gorm.io/driver/mysql"
"gorm.io/driver/postgres"
"gorm.io/gorm"
)
var config = gorm.Config{
PrepareStmt: true,
SkipDefaultTransaction: true,
}
func main() {
dsn := "postgresql://postgres:senha123@localhost:5432/postgres?sslmode=disable"
start := time.Now()
db, err := gorm.Open(postgres.Open(dsn), &config)
if err != nil {
panic("failed to connect database")
}
fmt.Printf("==> 打开连接: %s\n", time.Since(start))
executeBenchmark(db)
}
func executeBenchmark(db *gorm.DB) {
user := &User{
Name: "Inserted",
Email: "m@m.com",
Password: "asdf",
}
// 测试创建性能
start := time.Now()
db.Create(&user)
fmt.Printf("==> 创建用户: %s\n", time.Since(start))
// 测试按电子邮件选择性能
start = time.Now()
db.Where(&User{Email: user.Email}).First(&user)
fmt.Printf("==> 按EMAIL选择用户: %s\n", time.Since(start))
// 测试更新性能
start = time.Now()
db.Model(&user).Where(&User{ID: user.ID}).Update("name", "updated")
fmt.Printf("==> 更新用户: %s\n", time.Since(start))
// 测试删除性能
start = time.Now()
db.Unscoped().Delete(&User{}, user.ID)
fmt.Printf("==> 删除用户: %s\n", time.Since(start))
}
Postgres 结果:
==> 打开连接: 3.1902ms
==> 创建用户: 122.5041ms
==> 按EMAIL选择用户: 2.3431ms
==> 更新用户: 2.7019ms
==> 删除用户: 1.1994ms
如果我首先执行"按EMAIL选择用户"查询,它将成为慢查询...
使用相同的基准测试运行MySQL:
==> 打开连接: 6.2952ms
==> 创建用户: 8.98ms
==> 按EMAIL选择用户: 1.3064ms
==> 更新用户: 3.6067ms
==> 删除用户: 3.0996ms
Go: 1.20
gorm: 1.25.0
postgres驱动程序: 1.5.0
MySQL驱动程序: 1.5.0
英文:
I'm using Gorm with postgres driver. I'm testing some queries and realize the first query will always be very slow (100ms +). I've tested the same benchmark in MySQL, and that not occurs.
package main
import (
"fmt"
"os"
"strings"
"time"
"gorm.io/driver/mysql"
"gorm.io/driver/postgres"
"gorm.io/gorm"
)
var config = gorm.Config{
PrepareStmt: true,
SkipDefaultTransaction: true,
}
func main() {
dsn := "postgresql://postgres:senha123@localhost:5432/postgres?sslmode=disable"
start := time.Now()
db, err := gorm.Open(postgres.Open(dsn), &config)
if err != nil {
panic("failed to connect database")
}
fmt.Printf("==> Open connection: %s\n", time.Since(start))
executeBenchmark(db)
}
func executeBenchmark(db *gorm.DB) {
user := &User{
Name: "Inserted",
Email: "m@m.com",
Password: "asdf",
}
// Test Create performance
start := time.Now()
db.Create(&user)
fmt.Printf("==> Create user: %s\n", time.Since(start))
// Test Select by email performance
start = time.Now()
db.Where(&User{Email: user.Email}).First(&user)
fmt.Printf("==> Select by EMAIL user: %s\n", time.Since(start))
// Test Update performance
start = time.Now()
db.Model(&user).Where(&User{ID: user.ID}).Update("name", "updated")
fmt.Printf("==> Update user: %s\n", time.Since(start))
// Test delete performance
start = time.Now()
db.Unscoped().Delete(&User{}, user.ID)
fmt.Printf("==> Delete User user: %s\n", time.Since(start))
}
Postgres result:
==> Open connection: 3.1902ms
==> Create user: 122.5041ms
==> Select by EMAIL user: 2.3431ms
==> Update user: 2.7019ms
==> Delete User user: 1.1994ms
If I execute the Select by EMAIL user
first, it will be the slow query instead...
Running the same benchmark with MySQL:
==> Open connection: 6.2952ms
==> Create user: 8.98ms
==> Select by EMAIL user: 1.3064ms
==> Update user: 3.6067ms
==> Delete User user: 3.0996ms
Go: 1.20 <br />
gorm: 1.25.0 <br />
driver postgres: 1.5.0 <br />
driver MySQL: 1.5.0 <br />
I want to understand why the first query always takes 100ms+ and how I can improve it. Please, help =)
答案1
得分: 1
我仍然不知道为什么会发生这种情况,但是我在一个使用相同数据库网络的Docker容器中运行了这个基准测试,并且得到了更好的结果:
==> 打开连接:336.317微秒
==> 创建用户:7.741506毫秒
==> 通过EMAIL选择用户:999.892微秒
==> 更新用户:1.034811毫秒
==> 删除用户:912.888微秒
第一个SQL仍然是最差的,但我认为这是可以接受的。我会假设问题出在我的本地主机上的Docker上...
英文:
I still don't know why this is happening, but I ran this benchmark inside a docker container using the same db network and I got better results:
==> Open connection: 336.317µs
==> Create user: 7.741506ms
==> Select by EMAIL user: 999.892µs
==> Update user: 1.034811ms
==> Delete User user: 912.888µs
The first SQL is still the worst, but I think it is fine. I'll assume that the problem was something in my localhost with docker...
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论