Basic Performance Issues RCA On Postgres DB Cx
Whenever cx faces slowness in PG SQL Setup, below are the first step of actions to check and proceed based on our previous performance tickets.
1. Antivirus are main cause of slowness, high CPU, and memory consumption of PGSQL in many cx tickets. Usually many antivirus considers PGSQL as threat and try to restrict PGSQL activities which in turn affects our SDP queries running in that server and cause slowness or sometimes when antivirus restrict resource usage to PGSQL, then it causes resource contention in PGSQL causing performance issues. This issue can be easy seen when looking through task manager.
Below are the guidelines from PGSQL official WIKI.
So always advise cx to exclude, SDP/pgsql/data folder and postgres.exe from AV scan. These guidelines already updated in our admin guide also.
2. PG Tuning - When PGSQL needs much resource to process, and we haven't allocated enough resources in SDP/pgsql/extconf/postgres_ext.conf, then this might cause resource contention in PGSQL server and affect SDP performance. Always ensure whether cx properly done PG tuning in their environment, and all the values are correct as per our guidelines. We can see Postgres tuning parameter values in HealthMeter > Database Properties section.
PGSQL Tuning Doc: https://help.servicedeskplus.com/postgresql-performance-tuning
PGSQL Tuning Doc: https://help.servicedeskplus.com/postgresql-performance-tuning
3. REINDEX & VACUUM - When Postgres DB data got fragmented due to frequent operations then every query running in the application will be slow, to avoid this cx below 14303 build should enable DB Maintenance schedule under performance settings page and for cx's from 14303 should follow the steps mentioned in Performance Settings page and manually invoke the bundled SDP_HOME/bin/DBMaintenance.sh script file in offline mode.
4. DISK IO - When the application server disk got fragmented heavily, then this would cause performance issues too. To avoid this, cx should monitor Disk Utility applications and defragment regularly.
This is the running queries screenshot taken during a performance issue for sample.
New to ADSelfService Plus?
Related Articles
Performance debugging from version 9306 onwards
On Performance/Crash Issues, we have one URL to generate all debug details, this is applicable from 9306 onwards. Need to execute the URL in the browser, which has already login session into the application ...How to resolve Connectivity issues with Postgresql Database from our product?
Log Traces Feb 20, 2024 6:40:00 PM [SYSERR] [INFO] : java.sql.SQLException: java.lang.Exception: Exception during getConnection from pool Exception occurred during get connection from datasource Nov 15, 2023 1:29:10 PM [SYSERR] [INFO] : Caused by: ...Remote read only access to database for Postgres customers
Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow ...How to Connect External Postgres
Install a External Postgres such that the version of that postgres is currently in the series of inbuilt postgres of our Application. Say for example, In Servicedeskplus Application version 14010, which has postgres version 10.21, then you should ...Query Executor Tool for PostGres
This tool is designed to execute queries in the customer environment by connecting the database by reading the database configuration file. We need to enter the query that we require to execute in queryToExecute.txt file. We can enter multiple ...