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
Inputs required for analyzing Performance Issues
We need a couple of details to analyze Performance issue. So, please send the below details : Below mentioned details should be gathered before restarting the application. 1. Let us know whether you are taking Scheduled full backup or backup without ...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: ...How to connect to external postgres(external DB)
1. From the postgres installed path navigate to bin folder and open a command prompt 2. Execute the command --> pg_ctl -D "C:\Program Files\PostgreSQL\10\data" start (Postgres server gets started) 3. Connect to DB using the command-->psql -U postgres ...Performance Debug
For Performance/slowness issues instead of taking Threaddump manually or instead of invoking PerformanceTool make use of the below URL when the issue occurs, http://servername:portnumber/servlet/PerformanceDebug The above URL works even when the ...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 ...