Google Cloud SQL + Go Gin连接超时与200个并发

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

Google Cloud SQL + Go Gin connection timeout with 200 concurrent

问题

我正在尝试对生产环境进行负载测试。我发现从Google Cloud SQL获取了很多"连接超时"的错误。起初,我尝试将Google SQL实例增加到更高的规格,但错误仍然发生。

我运行的应用程序通过代理连接到数据库。

请问有人能给我建议如何解决这个问题吗?我知道在现实世界中可能是不可能的。

编辑:添加示例代码

db.go

func DB() *pgxpool.Pool {

schema := os.Getenv("DB_SCHEMA")
port := os.Getenv("DB_PORT")
user := os.Getenv("DB_USERNAME")
password := os.Getenv("DB_PASSWORD")
host := os.Getenv("DB_HOST")
dbName := os.Getenv("DB_DATABASE")
sslMode := os.Getenv("DB_SSL_MODE")
sslCertificate := os.Getenv("DB_SSL_CERTIFICATE")
sslPrivateKey := os.Getenv("DB_SSL_PRIVATE_KEY")
sslRootCert := os.Getenv("DB_SSL_ROOT_CA")

connStr := fmt.Sprintf(
	"host=%s port=%s user=%s password=%s dbname=%s sslmode=%s search_path=%s sslcert=%s sslkey=%s sslrootcert=%s",
	host,
	port,
	user,
	password,
	dbName,
	sslMode,
	schema,
	sslCertificate,
	sslPrivateKey,
	sslRootCert,
)

db, err := pgxpool.Connect(context.Background(), connStr)
helpers.CheckErr(err)

return db

}

data_repository.go

func GetDataByPhone(phone string) (model.Data, error) {
db := db.DB()
defer db.Close()

var d model.Data
var strQuery = "SELECT phone, payload, updated_at " +
	"FROM example_db.public.data WHERE phone=$1"

db.QueryRow(
	context.Background(),
	strQuery,
	phone,
).Scan(
	&d.Phone,
	&d.Payload,
	&d.UpdatedAt,
)

return d, nil

}

英文:

I was trying to do load testing for the production. I see a lot of errors get from Google Cloud SQL "connection timeout". Firstly, I was trying to increases the Google SQL instance to higher, but the errors still happened.

FYI, I run an application connected to the database via proxy.

Anyone, please suggest to me how to fix this. I am OK to know it is impossible in the real world.

EDIT: Add example code

db.go

func DB() *pgxpool.Pool {

schema := os.Getenv("DB_SCHEMA")
port := os.Getenv("DB_PORT")
user := os.Getenv("DB_USERNAME")
password := os.Getenv("DB_PASSWORD")
host := os.Getenv("DB_HOST")
dbName := os.Getenv("DB_DATABASE")
sslMode := os.Getenv("DB_SSL_MODE")
sslCertificate := os.Getenv("DB_SSL_CERTIFICATE")
sslPrivateKey := os.Getenv("DB_SSL_PRIVATE_KEY")
sslRootCert := os.Getenv("DB_SSL_ROOT_CA")

connStr := fmt.Sprintf(
	"host=%s port=%s user=%s password=%s dbname=%s sslmode=%s search_path=%s sslcert=%s sslkey=%s sslrootcert=%s",
	host,
	port,
	user,
	password,
	dbName,
	sslMode,
	schema,
	sslCertificate,
	sslPrivateKey,
	sslRootCert,
)

db, err := pgxpool.Connect(context.Background(), connStr)
helpers.CheckErr(err)

return db

}

data_repository.go

func GetDataByPhone(phone string) (model.Data, error) {
db := db.DB()
defer db.Close()

var d model.Data
var strQuery = "SELECT phone, payload, updated_at " +
	"FROM example_db.public.data WHERE phone=$1"

db.QueryRow(
	context.Background(),
	strQuery,
	phone,
).Scan(
	&d.Phone,
	&d.Payload,
	&d.UpdatedAt,
)

return d, nil

}

答案1

得分: 1

看起来你没有正确关闭连接。

你可以在这里查看如何正确打开和关闭连接。特别是在Go语言中,应该是这样的:

sqlInsert := "INSERT INTO votes(candidate, created_at, updated_at) VALUES(?, NOW(), NOW())"

if team == "TABS" || team == "SPACES" {
    if _, err := app.db.Exec(sqlInsert, team); err != nil {
        fmt.Fprintf(w, "unable to save vote: %s", err)
        return fmt.Errorf("DB.Exec: %v", err)
    }
    fmt.Fprintf(w, "Vote successfully cast for %s!\n", team)
}
return nil

你还可以使用以下代码设置连接超时限制:

// 设置连接的最大生存时间(以秒为单位)。
db.SetConnMaxLifetime(1800 * time.Second)
英文:

It looks like you are not closing your connections properly.

You can have a look here about how you should open and close connections properly. Specifically in Go, it should be something like that:

   sqlInsert := "INSERT INTO votes(candidate, created_at, updated_at) VALUES(?, NOW(), NOW())"

    if team == "TABS" || team == "SPACES" {

    if _, err := app.db.Exec(sqlInsert, team); err != nil {
            fmt.Fprintf(w, "unable to save vote: %s", err)
            return fmt.Errorf("DB.Exec: %v", err)
    }
    fmt.Fprintf(w, "Vote successfully cast for %s!\n", team)}
    return nil

You can also set a connection timeout limit with:

   // Set Maximum time (in seconds) that a connection can remain open.
   db.SetConnMaxLifetime(1800 * time.Second)`

huangapple
  • 本文由 发表于 2022年3月7日 19:21:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/71380284.html
匿名

发表评论

匿名网友

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

确定