Horton

Thread

  1. Resource Usage same In spite of fronting my DB server with Pgbouncer ?

    KK CHN <kkchn.in@gmail.com> — 2026-01-23T18:14:45Z

    List,
I am fronting my postgres 16 server with  pgbouncer1.23.1

What surprises me,  even though I am deploying  pgbouncer as a separate VM
in front of DB server VM,  the top command shows the almost same resource
usage statistics in the case of load averages, Memory usage etc on the DB
Server,  whether I am infronting DB server with pgbouncer or not.

Please find the top  output from the db server pasted here.

https://glot.io/snippets/hf4ilogbz0

My Pgbouncer server  top output  shows littler resource usages in terms of
CPU, MEM usage
on the top out put of  pgbouncer VM ( load averagaes  less than 3  always,
and Ram usage is very low,  swap usage almost nil..


Why eventhough I have deployed  pgbouncer  for this setup why DB server
still shows large resource usage as in the pasted out put.

How can I improve the resource usages on DB server ?  what is the root
cause DB server's still high resource usage ?

Any hints are most welcome .

My  pgbouncer.ini  config params pasted here for  any valuable suggestions
for fine tuning.


[root@pgbouncer ~]# grep ^[^\;\;] /usr/local/etc/pgbouncer.ini
[databases]

recoil = host=dbmain.erss.in port=5444 dbname=recoil
[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 = 100
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 = 900
max_user_connections = 800
server_lifetime = 3600
server_idle_timeout = 60000
[root@pgbouncer ~]#


Regards,
Krishane

  2. Re: Resource Usage same In spite of fronting my DB server with Pgbouncer ?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2026-01-23T18:48:17Z

    
On 1/23/26 10:14 AM, KK CHN wrote:
> 
> 
> 
> List,
> I am fronting my postgres 16 server with  pgbouncer1.23.1
> 
> What surprises me,  even though I am deploying  pgbouncer as a separate 
> VM in front of DB server VM,  the top command shows the almost same 
> resource usage statistics in the case of load averages, Memory usage etc 
> on the DB Server,  whether I am infronting DB server with pgbouncer or not.
> 
> Please find the top  output from the db server pasted here.
> 
> https://glot.io/snippets/hf4ilogbz0 <https://glot.io/snippets/hf4ilogbz0>
> 
> My Pgbouncer server  top output  shows littler resource usages in terms 
> of CPU, MEM usage
> on the top out put of  pgbouncer VM ( load averagaes  less than 3  
> always,  and Ram usage is very low,  swap usage almost nil..
> 
> 
> Why eventhough I have deployed  pgbouncer  for this setup why DB server 
> still shows large resource usage as in the pasted out put.

That would seem logical to me as pgBouncer is just passing the 
connections to the Postgres server, the server is doing the heavy 
lifting of dealing with statements in the connections.

 From the top output it looks like you are working with some form of a 
EDB product. You need to specify what that product is and it's version. 
This list is for the community version of Postgres and people will 
assume that is what you are talking about. There are folks that maybe 
able to help with EDB versions, but they need to know what it is.


> Regards,
> Krishane

  3. Re: Resource Usage same In spite of fronting my DB server with Pgbouncer ?

    KK CHN <kkchn.in@gmail.com> — 2026-01-23T19:47:19Z

    On Sat, Jan 24, 2026 at 12:18 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

>
>
> On 1/23/26 10:14 AM, KK CHN wrote:
> >
> >
> >
> > List,
> > I am fronting my postgres 16 server with  pgbouncer1.23.1
> >
> > What surprises me,  even though I am deploying  pgbouncer as a separate
> > VM in front of DB server VM,  the top command shows the almost same
> > resource usage statistics in the case of load averages, Memory usage etc
> > on the DB Server,  whether I am infronting DB server with pgbouncer or
> not.
> >
> > Please find the top  output from the db server pasted here.
> >
> > https://glot.io/snippets/hf4ilogbz0 <https://glot.io/snippets/hf4ilogbz0
> >
> >
> > My Pgbouncer server  top output  shows littler resource usages in terms
> > of CPU, MEM usage
> > on the top out put of  pgbouncer VM ( load averagaes  less than 3
> > always,  and Ram usage is very low,  swap usage almost nil..
> >
> >
> > Why eventhough I have deployed  pgbouncer  for this setup why DB server
> > still shows large resource usage as in the pasted out put.
>
> That would seem logical to me as pgBouncer is just passing the
> connections to the Postgres server, the server is doing the heavy
> lifting of dealing with statements in the connections.
>

You mean to say the SQL statements are making this issue ?  I also
suspected wrongly formed query statements making this much load on the DB
server.
 I also suspect this,  as the developers who write queries are not so
expertised for writing optimized queries, needs to be addressed separately.

How can I find out which query statements are making the DB server on its
knees ?  Any method to find the bad queries? what parameters/behaviours to
be checked for finding those query statements which really makes the
db server to its knees  by the heavy lifting ?   any hints most welcome, I
can explore and fix those ones.


>
>  From the top output it looks like you are working with some form of a
> EDB product. You need to specify what that product is and it's version.


Sorry I missed to mention it, this is an EDB 16 server.   Eventhoug I
prefer to use any piece of S/W that is FOSS community editions, sometimes
it is demanded to manage these products  too.


>
> This list is for the community version of Postgres and people will
> assume that is what you are talking about. There are folks that maybe
> able to help with EDB versions, but they need to know what it is.
>
>
> > Regards,
> > Krishane
>
>

  4. Re: Resource Usage same In spite of fronting my DB server with Pgbouncer ?

    Adrian Klaver <adrian.klaver@aklaver.com> — 2026-01-23T23:11:19Z

    
On 1/23/26 11:47 AM, KK CHN wrote:

> You mean to say the SQL statements are making this issue ?  I also 
> suspected wrongly formed query statements making this much load on the 
> DB server.
>   I also suspect this,  as the developers who write queries are not so 
> expertised for writing optimized queries, needs to be addressed separately.
> 
> How can I find out which query statements are making the DB server on 
> its knees ?  Any method to find the bad queries? what parameters/ 
> behaviours to be checked for finding those query statements which really 
> makes the db server to its knees  by the heavy lifting ?   any hints 
> most welcome, I can explore and fix those ones.

For log settings that deal with statements, take a look at:

https://www.postgresql.org/docs/16/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN

and

https://www.postgresql.org/docs/16/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

There is also, for viewing statistics on current activity:

https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-STATS-VIEWS

For another view of statistics see the extension:

https://www.postgresql.org/docs/16/pgstatstatements.html

In particular:

https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

Also for lock activity:

https://www.postgresql.org/docs/16/view-pg-locks.html

> Sorry I missed to mention it, this is an EDB 16 server.   Eventhoug I 
> prefer to use any piece of S/W that is FOSS community editions, 
> sometimes it is demanded to manage these products  too.
> 

EDB has variations of what they offer, a more specific definition would 
be helpful.