Gorm与Postgres在第一次SQL查询时花费的时间太长。

huangapple go评论86阅读模式
英文:

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:

==&gt; Open connection: 336.317&#181;s
==&gt; Create user: 7.741506ms
==&gt; Select by EMAIL user: 999.892&#181;s
==&gt; Update user: 1.034811ms
==&gt; Delete User user: 912.888&#181;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...

huangapple
  • 本文由 发表于 2023年4月24日 01:30:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76086284.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定