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

    • System Requirements

      The minimum system requirements for SupportCenter Plus: Inbound Requests Per Day Hard Disk Type RAM Processor OS 10x - Less than 100 500GB SSD 16GB 1.7GHz to 2.4GHz 10MB to 12MB Cache 4 cores to 8 cores or Any entry-level server-grade processor E.g: ...

    • Configuring Database

      In GUI Setups Configuring Database Server SupportCenter Plus is bundled with PostgreSQL database. You can also configure the application to set up MS SQL database. PostgreSQL 1. Go to <SupportCenter_Plus_Home>\bin directory in the command prompt and ...

    • 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 ...