How to optimize your DB (Database)

Note: This article assumes you have the right specs for either all-in-one or for a separate database server for the expected number of managed devices.

Step 1: Use the PGTune Calculator

1. Open the PGTune calculator
2. Fill in all parameters based on your machine settings you've configured in. 
   • DB Version : Select your PostgreSQL version
   • OS Type : Linux
   • DB Type : Web Application
   • Total Memory Size : Insert memory size based on the JetPatch Database server you chose
   • Number CPU : Insert memory size based on the JetPatch Database server you chose
   • Storage : HDD Storage
3. Click Generate to get the recommended configuration.
4. Log into the database server

Step 2: Apply Recommendations from PGTune

1. Log into the Database Server.
2. Locate the postgresql.conf File:
   • Default Path: /var/lib/pgsql/[version#]/data/postgresql.conf
   • Custom Mounts: If the file isn't in the default path, use the following command:

     locate postgresql.conf

3. Update the Configuration File:
   • Open the postgresql.conf file in a text editor like nano or vi:

     vi /path/to/postgresql.conf

   • Append the recommended configuration from PGTune to the bottom of the file and custom adjust max_connections Based on Environment Size:
     • Modify the max_connections value as per the table below:

Step 4: Restart Services and Verify

1. Restart PostgreSQL

• Restart the database service to apply the changes. Replace <version#> with your PostgreSQL version:

  systemctl restart postgresql-<version#>.service

2. Verify max_connections

• Log into PostgreSQL:

  psql -U jetpatch -h localhost jetpatchdb

  (Modify the username or database name if necessary. Use the database password found in the intigua.properties file.)

• Once inside PostgreSQL, run the following query to confirm the max_connections value:

  SELECT current_setting('max_connections');

3. Update datasource.rw.maximum-pool-size

• Edit the intigua.properties file located at:

  /usr/share/tomcat/default/conf/intigua.properties

• Set the datasource.rw.maximum-pool-size value based on your environment size as per the table below:

#example
datasource.rw.maximum-pool-size=195

4. Restart Tomcat

systemctl restart tomcat