PostgreSQL Performance Tuning

PostgreSQL Performance Tuning

If you are running SupportCenter Plus with the bundled Postgres database, you can improve performance by allocating more resources for the Postgres database. 


The configuration given below is applicable for medium (more than 100 thousand non-archived tickets) to a large workload.

 

These resources will be shared by Postgres server and SupportCenter Plus' JVM (Java Virtual Machine).

 

 

Customer Type

Physical RAM

Postgres

Java max heap size

CPU

Default

4GB

1GB

2.5GB

Dual-core with hyper-threading / Quad-core

Medium

8GB

2GB

4GB

Dual-core with hyper-threading / Quad-core

Large

12GB

4GB

4GB

Quad-core with hyper-threading / Octa-core

Large

16GB

8GB

6GB

Quad-core with hyper-threading / Octa-core

 

  1. For higher workloads, you need more RAM and CPU capacity in the application server.  
  2. It is recommended to leave some RAM space for the Operating System and other small applications or processes running on the server machine.
  3. It is not recommended to run any other server or ManageEngine applications like ActiveDirectory Plus, DesktopCentral, etc. on the same machine. 

 

Below is a sample configuration for a 2GB setup, modify the similar configuration accordingly in SupportCenter/pgsql/ext_conf/postgres_ext.conf to suit your requirement.

 

Uncomment the below parameters when 2 GB RAM can be allotted for Postgres DB server

#

# shared_buffers = 512MB

# effective_cache_size = 2GB

# maintenance_work_mem = 128MB

# work_mem = 16MB

# wal_buffers = 16MB

##### 

 

Uncomment below parameters when SSD is used

 

# random_page_cost = 1.1

##### 

 

For Linux OS, the PostgreSQL version 9.2 requires users to modify kernel resources to allot required resources for the Postgres server.


Please add the below mentioned entry in /etc/sysctl.conf file.

 

kernel.shmmax=536870912

kernel.shmall=262144

 

and then execute sudo sysctl -p /etc/sysctl.conf to reload the configurations.

​
This is only for Linux users. For Windows users, modifying the postgres_ext.conf file alone is enough.

 

 


    • Related Articles

    • Performance Tuning Guide

       This guide provides a set of SQL queries and tips that improve the performance of ManageEngine SupportCenter Plus.  Restart the application after executing the queries. The list of performance tips discussed in this guide are: Java Tuning Enabling ...

    • Performance Settings

      Performance settings allow you to configure various settings to improve the performance and stability of the application. You can configure Database related settings under General tab and report related settings under Reports tab. General Settings In ...

    • Solutions

      You can browse through the available solutions before reporting an issue, thus resolving known issues by yourself.   Click Solutions in the application header. The list view of all available solutions will be displayed along with details such as ID, ...

    • SupportCenter Plus - Support Policy

      Following is the support policy of SupportCenter Plus Support will be provided only for the latest SupportCenter Plus builds (released within one year from the current date). We recommend you upgrade to the latest version to continue receiving ...

    • Backup Process

      Beginning with build 14000, SupportCenter Plus supports native backup and restore for databases. The backup files of builds 14000 or later do not support cross-compatibility between PostgreSQL(bundled with SupportCenter Plus) and Microsoft SQL ...