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

For higher workloads, you need more RAM and CPU capacity in the application server.
It is recommended to leave some RAM space for the Operating System and other small applications or processes running on the server machine.
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.