Thread: PostgreSQL Server Hang​

PostgreSQL Server Hang​

From
KK CHN
Date:

Description of System: 
1. We are running a Postgres Server (version 12, on CentOS 6) for an emergency call attending and  
vehicle tracking system fitted with mobile devices for vehicles with navigation apps for emergency service.

2.   vehicles every 30 Seconds sending location coordinates( Lat /Long ) and getting stored into the DB server at the emergency call center cum control room.  

Issue: 
We are facing an issue of  the database hanging and becoming unresponsive for
applications running which try to connect to the DB. So eventually applications are also crawling on its knees.

Mitigation done so far : 
What mitigation we have done is increasing the  resources,    CPU(vCPUs)  from  32 to 64  ( Not sure is this the right approach / may be too dumb idea, but the hanging issue for the time being rectified. )..  

RAM 32 GB increased to 48 GB,  but  it observed that RAM usage was always below 32GB only ( So foolishly increased the RAM !!!)

Question:  
How to optimize and fine tune this database performance issue ?  Definitely pouring the resources like the above is not a solution.  

What to check the root cause and find for the performance bottle neck reasons  ? 

Thank you,
Krishane


Additional Inputs If required: 

##################################################################
The DB machine   is running on a CentOS6 platform ..  Only a single Database
Instance  running as a Virtual Machine.

The  Database server also stores call center call related ( call arrival and
dispatch time stamps and short messages to and  from around 300 Desktop
application operators and data from Mobile tablets  fitted on Vehicles with  VTS App installed on it.  The vehicle locations every 30 seconds are continuously stored into the database..  

Voice calls from callers in emergency which are each 3MB in size not stored in the Database, but as  files stored in an NFS mount folder and  the Database stores only the references to that voice call  files for future reference ( The call volumes are around 1 lakh / day )   Only  meta data information related to the calls ,  caller name, caller number, lat/long data of caller, short description of caller situation which are less than 200 Characters  x  3 messages per each call  stored into DB.

This database  is also used for  making  reports on the action taken by call takers/despatchers, Vehicle tracking reports etc on a daily basis.   Around 2000 Vehicles are fitted with Mobile tablets with the emergency navigation apps in the fleet. 

The database grows roughly 1 GB / Day  )


############################################################################

Re: PostgreSQL Server Hang​

From
Andreas Kretschmer
Date:

On 22 June 2023 07:09:26 CEST, KK CHN <kkchn.in@gmail.com> wrote:
>*Description of System: *
>1. We are running a Postgres Server (version 12, on CentOS 6) for an
>emergency call attending and  vehicle tracking system fitted with mobile
>devices for vehicles with navigation apps for emergency service.
>
>2.   vehicles every 30 Seconds sending location coordinates( Lat /Long ) and
>getting stored into the DB server at the emergency call center cum control
>room.
>
>*Issue: *
>We are facing an issue of  the database hanging and becoming unresponsive
>for
>applications running which try to connect to the DB.
>
>*Question: *
>How to optimize and fine tune this database performance issue ?  Definitely
>pouring the resources like the above is not a solution.
>
>What to check the root cause and find for the performance bottle neck
>reasons  ?
>

Have you THP disabled?

Andreas