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. )..
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.
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.
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 )
############################################################################
