pgbouncer笔记 2

文章目录说明1. 如何判断 pgx 是否发送 Named Prepared Statement方法1开启 PostgreSQL 日志推荐如果 pgx 使用 Named Statement如果是 unnamed如果 Simple Protocol方法2查看 pg_prepared_statements方法3抓 PostgreSQL 协议包最准确NamedUnnamed2. 方案 A稳定优先PgBouncer 配置Go代码3. 方案 B性能优先PgBouncerGo代码4. 两个方案对比5. 针对你当前环境的建议第一步第二步对于方案 A 与方案 B 你列举的例子中没有用到 PrepareContext 与 ExecContext, 我期望使用到这两个函数, 使用这两个函数后, 方案 A 与方案 B 还是否成立?先给结论一、先明确三种“Prepare”不是一回事二、方案 A PrepareContext 会发生什么三、方案 B PrepareContext 会怎样四、那么 PrepareContext 有没有办法在 transaction 模式使用条件1条件2五、那 GORM 的 PrepareStmt 呢推荐六、如果你希望同时使用 PrepareContext有没有推荐方案目标1稳定目标2极致性能七、重新修正之前两个方案八、结合你们项目GORM PgBouncer transactionGo 程序大量使用 PrepareContext ExecContext 为了能不改应用代码 也不会报错 是不是只能使用 pgdog 连接池工具了说明PgBouncer 1.25 transaction 模式支持 Extended Protocol也支持协议级 named Prepared Statement通过 max_prepared_statements但它不是完整的 PostgreSQL session prepared statement 语义因此不能保证所有驱动、所有 prepared statement 使用方式都兼容。1. 如何判断 pgx 是否发送 Named Prepared Statement这是定位你之前stmt xxx does not exist的关键。pgx v5 有多种 QueryExecMode模式是否创建 Named Prepared Statement协议QueryExecModeSimpleProtocol❌Simple QueryQueryExecModeExec❌每次 Parse unnamedExtendedQueryExecModeCacheStatement✅Extended Named PreparedQueryExecModeDescribeExec❌ExtendedQueryExecModeCacheDescribe❌缓存 DescribeExtended方法1开启 PostgreSQL 日志推荐postgresql.conflog_statement all log_min_duration_statement 0重载SELECTpg_reload_conf();如果 pgx 使用 Named Statement日志类似LOG: execute stmtcache_1: SELECT * FROM users WHERE id$1 DETAIL: parameters: $1 10或者LOG: execute lrupsc_1_12345: SELECT ...看到execute xxx:其中 xxx 不是unnamed说明✅ named prepared statement如果是 unnamed日志LOG: execute unnamed: SELECT * FROM users WHERE id$1说明Extended Protocol但是❌ unnamed statement如果 Simple Protocol日志LOG: statement: SELECT * FROM users WHERE id10没有execute方法2查看 pg_prepared_statements直接连接 PostgreSQLselect*frompg_prepared_statements;例如结果name | statement ----------------------------------------------- lrupsc_1_12345 | SELECT * FROM users WHERE id$1说明当前 backend 有named prepared statement。注意如果通过 PgBouncer transaction你查询的时候必须连接到同一个 backend。否则可能为空。方法3抓 PostgreSQL 协议包最准确tcpdumptcpdump\-ieth0\port5432\-wpg.pcapWireshark看NamedParse Statement: lrupsc_1_12345 SQL: select...UnnamedParse Statement: (empty)2. 方案 A稳定优先目标GORM pgx PgBouncer transaction 不要使用 Prepared Statement架构GORM | database/sql | pgx | Extended Protocol | PgBouncer(transaction) | PostgreSQL注意不是 Simple Protocol。这里使用QueryExecModeExec即Parse Bind Execute但是每次重新 Parse。PgBouncer 配置pgbouncer.ini[databases] testdb host127.0.0.1 port5432 dbnametestdb [pgbouncer] pool_mode transaction max_prepared_statements 0 max_client_conn 1000 default_pool_size 50关键max_prepared_statements 0关闭 PgBouncer prepared statement tracking。Go代码go.modrequire(gorm.io/gorm gorm.io/driver/postgres)main.gopackagemainimport(fmtgorm.io/driver/postgresgorm.io/gorm)typeUserstruct{IDuintgorm:primaryKeyNamestringAgeint}funcmain(){dsn:host127.0.0.1 usertestuser password123456 dbnametestdb port6432db,err:gorm.Open(postgres.New(postgres.Config{DSN:dsn,// 方案A// 不使用 Simple// 使用 pgx Exec 模式PreferSimpleProtocol:false,}),gorm.Config{// GORM层关闭缓存PrepareStmt:false,},)iferr!nil{panic(err)}db.AutoMigrate(User{})// insertuser:User{Name:Tom,Age:20,}db.Create(user)// updatedb.Model(User{}).Where(id?,user.ID).Update(age,30,)// selectvarresult User db.Where(id?,user.ID,).First(result)fmt.Println(result)}此时一次查询Parse Bind Execute下一次还是Parse Bind Execute不会Bind existing statement所以不会出现prepared statement does not exist3. 方案 B性能优先目标使用 pgx statement cache PgBouncer prepared statement virtualizationPgBouncerpgbouncer.ini[pgbouncer] pool_mode transaction max_prepared_statements 500 max_client_conn1000 default_pool_size50注意不要server_reset_query_always1不要强制DISCARD ALLGo代码这里需要明确GORM 的PreferSimpleProtocol不够表达CacheStatement所以推荐显式创建 pgx config。完整packagemainimport(fmtgithub.com/jackc/pgx/v5github.com/jackc/pgx/v5/stdlibgorm.io/driver/postgresgorm.io/gorm)typeUserstruct{IDuintgorm:primaryKeyNamestringAgeint}funcmain(){dsn:postgres://testuser:123456127.0.0.1:6432/testdbconfig,err:pgx.ParseConfig(dsn)iferr!nil{panic(err)}// pgx层 config.DefaultQueryExecModepgx.QueryExecModeCacheStatement sqlDB:stdlib.OpenDB(*config)db,err:gorm.Open(postgres.New(postgres.Config{// 使用已有连接Conn:sqlDB,},),gorm.Config{// GORM层 PrepareStmt:false,},)iferr!nil{panic(err)}db.AutoMigrate(User{})user:User{Name:Tom,Age:20,}db.Create(user)db.Model(User{}).Where(id?,user.ID).Update(age,40,)varresult User db.Where(id?,user.ID,).First(result)fmt.Println(result)}此时第一次Parse statementl rupsc_xxx Bind Execute第二次Bind ExecutePgBouncerclient stmt ↓ backend stmt4. 两个方案对比方案A方案BGORM PrepareStmtfalsefalsepgx模式ExecCacheStatementPgBouncer max_prepared_statements0500Extended Protocol✅✅Named Prepared❌✅性能中高稳定性最高较高transaction pool非常适合需要验证5. 针对你当前环境的建议你现在PostgreSQL 15PgBouncer 1.24/1.25GORMpgx v5transaction pooling曾经出现stmt xxx does not exist我建议测试顺序第一步先跑方案APrepareStmtfalse QueryExecModeExec max_prepared_statements0如果稳定说明问题一定来自 prepared statement 生命周期。第二步再切方案BCacheStatement max_prepared_statements500如果方案B失败那么基本可以确定PgBouncer pgx statement cache 兼容性问题而不是 GORM 或 PostgreSQL 问题。这两个方案对比测试一次基本就能把问题定位。对于方案 A 与方案 B 你列举的例子中没有用到 PrepareContext 与 ExecContext, 我期望使用到这两个函数, 使用这两个函数后, 方案 A 与方案 B 还是否成立?先给结论如果代码中显式使用PrepareContext()stmt,err:db.PrepareContext(ctx,sql)那么方案 A不成立或者说目标失效方案 B也不成立至少不再是 pgx CacheStatement 的控制路径原因PrepareContext()是database/sql层主动要求创建 Prepared Statement它绕过了 pgx 的DefaultQueryExecMode决策。这是非常关键的区别。一、先明确三种“Prepare”不是一回事很多混淆来自这里。Go 里面有GORM PrepareStmt | v database/sql PrepareContext() | v pgx protocol Parse | v PostgreSQL Prepared Statement而pgx QueryExecModeCacheStatement | v pgx 自动管理 Prepared Statement这是两条不同路径。二、方案 A PrepareContext 会发生什么方案 A目标不要使用 prepared statement 避免 PgBouncer transaction 问题配置GORM: PrepareStmtfalse pgx: QueryExecModeExec PgBouncer: max_prepared_statements0但是你的代码ctx:context.Background()stmt,err:db.DB().PrepareContext(ctx,select * from users where id$1,)rows,err:stmt.QueryContext(ctx,1,)流程database/sql PrepareContext() | v pgx Parse statement name xxx | v PostgreSQL 创建 prepared statement此时QueryExecModeExec已经没有决定权。因为你已经告诉 driver请帮我 Prepare 一个 statement。所以方案 A 变成GORM | database/sql PrepareContext | pgx | Prepared Statement | PgBouncer transaction结果max_prepared_statements0 ↓ PgBouncer 不维护 prepared mapping ↓ 切换 backend ↓ stmt 不存在可能再次出现ERROR 26000 prepared statement xxx does not exist三、方案 B PrepareContext 会怎样方案 Bpgx QueryExecModeCacheStatement PgBouncer max_prepared_statements500但是你的代码stmt,err:db.PrepareContext(...)流程database/sql PrepareContext ↓ pgx 创建一个 SQL Prepared Statement ↓ PgBouncer这里不是pgx statement cache而是database/sql statement所以实际上变成GORM | database/sql stmt cache | pgx prepared statement | PgBouncer mapping你的链路多了一层。类似GORM PrepareStmt database/sql PrepareContext pgx statement PgBouncer prepared mapping非常复杂。四、那么 PrepareContext 有没有办法在 transaction 模式使用有但是需要满足条件。条件1PgBouncerpool_modesession最简单。因为client | backend 一直绑定Prepared Statement 生命周期一致。条件2transaction 模式必须max_prepared_statements 0并且你的 driver 行为必须符合 PgBouncer 支持范围。例如pool_modetransaction max_prepared_statements500然后客户端Parse named statement Bind ExecutePgBouncer 才能代理。五、那 GORM 的 PrepareStmt 呢例如db,_:gorm.Open(postgres.New(...),gorm.Config{PrepareStmt:true,},)它内部做的事情类似stmt,err:conn.PrepareContext(ctx,sql,)所以它等价于你手工PrepareContext()因此对于 PgBouncer transaction我的建议推荐PrepareStmt:false六、如果你希望同时使用 PrepareContext有没有推荐方案看目标。目标1稳定不要PrepareContext()改db.QueryContext(ctx,sql,args...)例如rows,err:db.QueryContext(ctx,select * from users where id$1,1,)让 pgx 决定Exec模式目标2极致性能使用PrepareContext()那么建议PgBouncer session pool或者直接PG connection pool例如pgxpool七、重新修正之前两个方案严格来说我前面给你的方案 A/B 默认前提是应用不显式调用 PrepareContext也不启用 GORM PrepareStmt。完整定义应该是方案A方案BGORM PrepareStmtfalsefalse手工 PrepareContext❌ 不允许⚠️ 可以但增加风险pgx CacheStatement❌✅PgBouncer max_prepared_statements0500transaction pool稳定性能更高八、结合你们项目GORM PgBouncer transaction你前面提到Go 程序大量使用 PrepareContext ExecContext这个信息非常关键。如果真实情况是stmt,err:db.PrepareContext(...)stmt.ExecContext(...)那么你们的问题根源很可能不是pgx QueryExecModeCacheStatement而是database/sql Prepared Statement PgBouncer transaction pooling这种情况下即使max_prepared_statements500也不一定能解决。因为你使用的是显式 prepared statement 生命周期而不是pgx 自动 statement cache 生命周期。这两个在 PgBouncer transaction 模式下是完全不同的问题。你们之前遇到的stmt xxx does not exist如果代码里大量存在PrepareContext/ExecContext这个方向的优先级应该放到最高。Go 程序大量使用 PrepareContext ExecContext 为了能不改应用代码 也不会报错 是不是只能使用 pgdog 连接池工具了Go 程序大量使用 PrepareContext ExecContext 为了能不改应用代码 也不会报错 是不是只能使用 pgdog 连接池工具了