PostgreSQL Server Hang​

From: KK CHN <kkchn(dot)in(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: PostgreSQL Server Hang​
Date: 2023-06-22 05:09:26
Message-ID: CAKgGyB-JTUS92pnouGrcQdUx1aNqGQzMU7AJxHCrY5nvK3zQ6Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

*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 )

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2023-06-22 05:39:19 Re: PostgreSQL Server Hang​
Previous Message shveta malik 2023-06-22 04:12:56 Re: Support logical replication of DDLs