Re: DB Server slow down & hang during Peak hours of Usage

Hello,
in the postgresql.conf joined, 2 things (at least) look strange:
1) the values for background writer are the default values, fit for a
server with a limited writes throughput.
you may want to increase those, like:
bgwriter_delay = 50ms
bgwriter_lru_maxpages = 400
bgwriter_lru_multiplier = 4.0
and check the checkpoint log to see if there are still backend processes
writes.

2) work_mem is set to 2 GB.
so, if 50 simultaneous requests use at least one buffer for sorting,
joining, ..., you will consume 100 GB of RAM
this value seems huge for the kind of config/usage you describe.
You may try to set work_mem to 100 MB and check what's happening.

Also check the logs, postgres tells his life there...

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Mon, Aug 7, 2023 at 3:36 PM KK CHN <kkchn(dot)in(at)gmail(dot)com> wrote:

> List ,
>
> *Description:*
>
> Maintaining a DB Server Postgres and with a lot of read writes to this
> Server( virtual machine running on ESXi 7 with CentOS 7) .
>
> ( I am not sure how to get the read / write counts or required IOPS or any
> other parameters for you. If you point our I can execute those commands
> and get the data. )
>
> Peak hours say 19:00 Hrs to 21:00 hrs it hangs ( The application is an
> Emergency call response system writing many Emergency Response vehicles
> locations coordinates to the DB every 30 Seconds and every emergency call
> metadata (username, phone number, location info and address of the caller
> to the DB for each call)
>
> During these hours the system hangs and the Application ( which shows
> the location of the vehicles on a GIS map hangs ) and the CAD machines
> which connects to the system hangs as those machines can't connect to the
> DB and get data for displaying the caller information to the call taking
> persons working on them. )
>
> *Issue : *
> How to trace out what makes this DB hangs and make it slow and how to
> fix it..
>
> *Resource poured on the system :*
>
> *64 vCPUs allocate ( Out of a host machine comprised of 2 processor slots
> of 20 cores each with Hyper Threading, intel xeon 2nd Gen, CPU usage show
> 50 % in vCentre Console), and RAM 64 GB allocated ( buy usage always
> showing around 33 GB only ) *
>
> *Query :*
>
> How to rectify the issues that makes the DB server underperforming and
> find a permanent fix for this slow down issue*. *
>
> *Attached the Postgres.conf file here for reference .*
>
> *Any more information required I can share for analysis to fix the issue. *
>
>
> *Krishane *
>