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:

 — (cl_active),  — , (cl_waiting, )

,   ,     pgx,   pgx pgbouncer’a.

  ,    10  — .   cl_active , cl_waiting  60. 60 ,  — 10? ,   :

 —  RPS,  —    ,  —

,   «»

 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, . , :

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

@jackc, pgx,   CustomCancel, . , , :

: , ,   .

, 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   , :

 RPS,  ,

  ,   , (    ). ,   :

cl_active   24 ( ), cl_waiting

  , cl_waiting :

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’ .

—   .

—     .