Request a Demo
Home   >   mailing lists

Pgbouncer and Node JS application Query read timeout error - Mailing list pgsql-general

From KK CHN
Subject Pgbouncer and Node JS application Query read timeout error
Date
Msg-id CAKgGyB_OmPnsgrr7YWOY=w8VDH7pN2hCt4Tsd1nJy=-xM=64eA@mail.gmail.com
Whole thread
Responses Re: Pgbouncer and Node JS application Query read timeout error
List pgsql-general
Tree view
List, 

I am using pgbouncer(PgBouncer 1.23.1 RHEL 9.4) along with  Postgres16(RHEL 9.4)  for connection pooling.   


 Running a nodejs application which is throwing some errors  related to query timeout which the development team suspect after pgbouncer deployment this behaviour appears, but not sure 


The error which is thrown from  the nodejs logs as follows.. 

image.png
Is this due to   pgbouncer config issues or   nodejs  pool config issues ?     ( Also the nodejs application  restarts frequently after running for quiet few hours  as per the dev team reported, may be code level / mem leak/GC    issues but need to roll out this don't have any connection with pgbouncer deployment. 


for  reference here the pgbouncer  config params and  node js  params at present.           Any hints much helpful to rule out this is pgbouncer config issues ... 


pgbouncer.ini

[databases]

rpt_db = host=dbmain.mydomain.com port=5444 dbname=rpt_db
postgres = host=dbmain.mydomain.com=5444 dbname=postgres
keycloak = host=dbmain.mydomain.com=5444 dbname=keycloak
[users]
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 5444
auth_type = md5
auth_file = /usr/local/etc/pgbouncer.users
admin_users = myuser
stats_users = myuser
pool_mode = transaction
max_prepared_statements = 120
server_reset_query = DISCARD ALL
server_reset_query_always = 1
ignore_startup_parameters = extra_float_digits, options, statement_timeout, idle_in_transaction_session_timeout

max_client_conn = 5000
default_pool_size = 50
min_pool_size = 30
reserve_pool_size = 10
reserve_pool_timeout = 5
max_db_connections = 130
max_user_connections = 180
server_lifetime = 3600
server_idle_timeout = 600
[root@pgbouncer ~]#


NodeJS application  pg-pool-config.js     // 

import pkg from 'pg';
import { config } from 'dotenv';
import dblogger from '../helpers/dblogger.js';
const { Pool } = pkg;
config();

const { DB_HOST, DB_NAME_MGT, DB_NAME_TSP, DB_NAME_RDB, DB_USER, DB_PASSWORD, DB_PORT } = process.env;

const poolOptions = {
    max: 10,
    min: 2,
    idleTimeoutMillis: 600000,   //Idle for 5Min
    connectionTimeoutMillis: 10000,  //Reconnect 10sec
    //statement_timeout: 60000,    //Query executiion 1 min
    acquire: 20000,
    maxUses: 1000 //reconnect after 1000 queries
};
..................
............




TOP OUTPUT ON Pgbouncer VM 

last pid: 17171;  load averages:  0.45,  0.39,  0.40             up 7+07:19:36  10:34:48
23 processes:  1 running, 20 sleeping, 2 stopped
CPU:  0.5% user,  0.0% nice,  3.6% system,  0.0% interrupt, 95.9% idle
Mem: 13M Active, 1352M Inact, 1415M Wired, 1024M Buf, 13G Free
Swap: 7068M Total, 7068M Free

  PID USERNAME    THR PRI NICE   SIZE    RES STATE    C   TIME    WCPU COMMAND
  828 pgbouncer     1  48    0    25M    12M kqread   0  43.6H  20.45% pgbouncer
  217 root          3  20    0    53M    28M select   6   6:30   0.05% vmtoolsd




TOP OUTPUT ON    NODE JS  VM

top - 10:35:52 up 195 days,  5:37,  5 users,  load average: 0.89, 0.86, 0.91
Tasks: 612 total,   8 running, 604 sleeping,   0 stopped,   0 zombie
%Cpu(s):  2.9 us,  0.3 sy,  0.0 ni, 96.3 id,  0.0 wa,  0.1 hi,  0.3 si,  0.0 st
MiB Mem :  63785.5 total,  50434.0 free,   8909.5 used,   5429.2 buff/cache
MiB Swap:   4044.0 total,   4032.4 free,     11.6 used.  54875.9 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   4120 root      20   0 1140028 621676  36864 S  18.6   1.0  51895:18 node
1914526 root      20   0   21.0g 251816  49152 S   9.3   0.4   4:16.81 node /v


TOP OUTPUT ON DB SERVER VM

   (  [root@db1 ~]# grep -c ^processor /proc/cpuinfo
16
[root@db1 ~]#   )

TOP 
top - 10:37:22 up 407 days, 15:24,  1 user,  load average: 9.75, 10.65, 9.09
Tasks: 500 total,   2 running, 498 sleeping,   0 stopped,   0 zombie
%Cpu(s): 16.8 us,  8.8 sy,  0.0 ni, 49.7 id, 23.2 wa,  0.4 hi,  1.1 si,  0.0 st
MiB Mem :  31837.6 total,    351.4 free,  10369.1 used,  30018.5 buff/cache
MiB Swap:   8060.0 total,   6908.2 free,   1151.8 used.  21468.5 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
1842514 postgres+  20   0 8961888   2.4g   2.4g R  26.8   7.7   0:00.86 postgres
1827606 postgres+  20   0   21208   7808   7808 S  26.1   0.0   5:17.38 pgbackrest
1827611 postgres+  20   0   30596  18220   8064 D  25.1   0.1   3:54.74 pgbackrest




Any more inputs required let me know..


Thank you,

Krishane

Attachment

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: configure && --with
Next
From: Laurenz Albe
Date:
Subject: Re: Pgbouncer and Node JS application Query read timeout error