The adventures of one bug or how to fix pgx with someone else's hands

Hi, my name is Ivan and I do Avito Delivery.



Once I test one of our services for performance. And in the metrics pgbouncer'a I see such a sad picture:



pgbouncer metrics

 â€” (cl_active),  â€” , (cl_waiting, )



,   ,     pgx,   pgx pgbouncer’a.





  ,    10  â€” .   cl_active , cl_waiting  60. 60 ,  â€” 10? ,   :



load test

 â€”  RPS,  â€”    ,  â€”





diagram

,   «»



 Go  k8s,   . PostgreSQL  LXC ,   pgbouncer.



:   HTTP-, SQL-,  , .  ,  .



 pgbouncer   pgx/v4.   ( 10)   pgbouncer ( 100  ). pgbouncer  10  .





, . ,     . :



LSR-1223: item-storage.



500- .



- , - , - . , - .



item-storage pgx , cancelContext.


. .  , , : , ,   pgbouncer.   100. ~60 , , , ,  .



    pgx . , ,    pgbouncer, . , :



canceling someone else's request in pgbouncer



«Postgres   . , .    Postgres  PgBouncer   .   CustomCancel, , context.Context.»



@jackc, pgx,   CustomCancel, . , , :



Jack, bring back CustomCancel



: , ,   .



Jack refuses



, CustomCancel   -.



  pgx/v4 .   ,  , :



  • pgx;
  • pgbouncer;
  • , pgbouncer   .


, .  @jackc,  .



PostgreSQL pgbouncer



 PostgreSQL?



PostgreSQL cancel_key. ,   (CancelRequest StartupMessage)   cancel_key  ,   .  â€”  .



Pgbouncer   .  , cancel_key, .   cancel_key:



/* give each client its own cancel key */
get_random_bytes(client->cancel_key, 8);


cancel_key    pgbouncer, cancel_key    , . , pgbouncer’ cancel_key   .    , , pgbouncer cancel_key       :



/* remember server key */
server = main_client->link;
memcpy(req->cancel_key, server->cancel_key, 8);

/* attach to target pool */
req->pool = pool;
change_client_state(req, CL_CANCEL);

/* need fresh connection */
launch_new_connection(pool);


, pgbouncer   :



/* not linked client, just drop it then */
if (!main_client->link) {
  /* ... */

  disconnect_client(req, false, "cancel request for idle client");

  return;
}


, -    pgbouncer , , @jackc   .



, . :



  1. .
  2. .
  3.    , , .
  4.   .
  5.    , .


  : cancel_key  . . , ,   ( 4). , pgx/v4 :   pgx .



,   pgx/v4 + pgbouncer + PostgreSQL . : . ,  pgx.



 pgx/v4



pgx  ,   ,   .  :



  • Pgconn   .   .   .
  • Pgxpool   .   PgConn   pgconn.   , , «» PgConn.


    .    , :



  1. (context.Context).
  2. .
  3. PgConn   .
  4. Pgxpool ,   , PgConn, ,   , .
  5. «» PgConn , .  .       .


, :   ,   .



  , , pgbouncer’ .   â€”   .



,  pgx, ,   . ,    pgx.



 pgx



Pgx . select:



select {
case <-ctx.Done():
//...
}


,   :



pgConn.contextWatcher = ctxwatch.NewContextWatcher(
  func() { pgConn.conn.SetDeadline(time.Date(1, 1, 1, 1, 1, 1, 1, time.UTC)) },
  func() { pgConn.conn.SetDeadline(time.Time{}) },
)


, select,     net.Conn:



pgConn.conn.SetDeadline(time.Date(1, 1, 1, 1, 1, 1, 1, time.UTC))


,   ,   ,     .   pgx   . :



n, err := pgConn.conn.Write(buf)
if err != nil {
  pgConn.asyncClose()
  return &writeError{err: err, safeToRetry: n == 0}
}


  Write() . select .



, : . , .



- @jackc



,   ,    , .



-   :   , , .  pgbouncer’.  â€” , , pgbouncer.



: , , pgbouncer Postgres. docker-compose.yml ( ):



services:
  web:
    build: .
  pgbouncer:
    image: pgbouncer/pgbouncer
  postgres:
    image: postgres


. 100 ,  :



ctx, cancel := context.WithTimeout(ctx, 10 * time.Millisecond)
defer cancel()

q := `select pg_sleep(10)`
rows, _ := db.Query(ctx, q)
defer rows.Close()


10 ,  10 , .



  show pools pgbouncer’:



echo "show pools;" | psql -h localhost -p 6432 -U postgres pgbouncer


:



  • cl_active: 2
  • cl_waiting: 97
  • sv_login: 1
  •  â€”


   10, pgbouncer â€”  100. , pgbouncer’ , .



. , .  .





, , . .



(diff)  pgxpool pgconn. pgxpool , pgconn . PgConn .



  PgConn.CleanupDone(). PgConn , pgxpool  ,   .



- ,   :



  • cl_active: 1
  • cl_waiting: 8


pgx   , :



load test

 RPS,  ,



  ,   , (    ). ,   :



pgbouncer metrics

cl_active   24 ( ), cl_waiting



  , cl_waiting :



pgbouncer metrics



4,   .  .



 4 pgx, .  pgx  , .



TL;DR,



  1.    pgx/v4  ,  , pgx  .   pgx/v4 pgxpool — .
  2. SQL-   pgbouncer — .
  3. .


UPD: , pgx/v4+pgxpool, pgx/v4+database/sql . pgx database/sql. . , .





—  LSR’ .



—   .



—     .




All Articles