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...@gmail.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 * >
