Article ID: 130616, created on Apr 11, 2017, last review on Apr 13, 2017

  • Applies to:
  • Operations Automation
  • Business Automation

Overview

For better performance experience, we recommend adjusting PostgreSQL configuration using the utility pg-tune. The recommendation is applicable to the Operations, Billing and OACI IM databases. It is important to note, that the utility should be performed on the node, hosting the respective database, as it makes the calculations based on the memory available to host.

How to use the utility pg-tune (general schema):

  1. Check the current system shared memory settings:

    # sysctl -a | grep 'shmmax\|shmall'
    
  2. Calculate the recommended values using the following script shmsetup.sh. The script should be performed on the node hosting the database.
  3. Set the recommended values, if they exceed the current ones. To do that, add or modify the values for kernel.shmmax and kernel.shmall in the /etc/sysctl.conf and apply the changes by running the command:

    # sysctl -p
    
  4. Create a backup of the current PostgreSQL configuration file:

    # cp <POSTGRESQL_CONF> <POSTGRESQL_CONF>.orig
    
  5. Generate the optimized configuration file using pg-tune:

    # python pg-tune.py -n <MAX_CONNECTIONS> -p <NEW_DB_VERSION> -i <POSTGRESQL_CONF> -o <POSTGRESQL_CONF>.new
    
  6. Check the new settings in the <POSTGRESQL_CONF>.new
  7. Replace the current PostgreSQL configuration file with the optimized one:

    mv $<POSTGRESQL_CONF>.new <POSTGRESQL_CONF>
    

The values of <POSTGRESQL_CONF>, <MAX_CONNECTIONS> and <NEW_DB_VERSION> are different depending on the product. They are listed in the corresponding section below.

Operations DB

The utility pg-tune tunes the number of connections (set to 256) and sets the memory limits that depend on the RAM size. You can see the settings applied by pg-tune in file /var/lib/pgsql/9.1/data/postgresql.conf on Odin Automation database server (at the end of the configuration file).

However, if the RAM size was changed (increased) on Odin Automation database server it is required to re-configure PostgreSQL using pg-tune.

Launch pg-tune using the following values:

for PostgreSQL 9.6

    POSTGRESQL_CONF = /var/lib/pgsql/9.6/data/postgresql.conf
    MAX_CONNECTIONS = 256
    NEW_DB_VERSION = 9.6

for PostgreSQL 9.1

    POSTGRESQL_CONF = /var/lib/pgsql/9.1/data/postgresql.conf
    MAX_CONNECTIONS = 256
    NEW_DB_VERSION = 9.1

To apply the changes execute the following:

  1. Stop Odin Automation. Execute the following command on Odin Automation Management Node:

    # service pem stop
    

    or for Odin Automation 6.0:

    # service pem stop
    # service pau stop
    
  2. Restart PostgreSQL. Execute the following command on Odin Automation database server:

    for PostgreSQL 9.6

    # service postgresql-9.6 restart
    

    for PostgreSQL 9.1

    # service postgresql-9.1 restart
    
  3. Start Odin Automation. Execute the following command on Odin Automation Management Node:

    # service pem start
    

    or for Odin Automation 6.0:

    # service pau start
    # service pem start
    

Billing DB

Launch pg-tune using the following values:

for PostgreSQL 9.6

    POSTGRESQL_CONF = /var/lib/pgsql/9.6/data/postgresql.conf
    MAX_CONNECTIONS = 500
    NEW_DB_VERSION = 9.6

for PostgreSQL 9.0

    POSTGRESQL_CONF=/var/lib/pgsql/data/postgresql.conf
    MAX_CONNECTIONS = 500
    NEW_DB_VERSION = 9.0

To apply the changes execute the following:

  1. Stop Billing service. Execute the following command on Billing application server:

    # service pba stop
    
  2. Restart PostgreSQL. Execute the following command on Billing database server:

    for PostgreSQL 9.6

    # service postgresql-9.6 restart
    

    for PostgreSQL 9.0

    # service postgresql restart
    
  3. Start Billing service. Execute the following command on Billing application server:

    # service pba start
    

OACI IM DB

Launch pg-tune using following values:

    POSTGRESQL_CONF = /var/lib/pgsql/9.1/data/postgresql.conf
    MAX_CONNECTIONS = 100
    NEW_DB_VERSION = 9.1

Note: The number of DB connections is configurable in the IM configuration file and can be verified like shown below:

    # awk '/database.*maxConnections/ {print $3}' /usr/local/share/PACI-im/IM-config.xml
    maxConnections="10"

MAX_CONNECTIONS in PostgreSQL configuration must be not less than maximum number of database connections allowed in the IM configuration.

To apply the changes execute the following:

  1. Stop OACI services using the article #126873.

  2. Restart PostgreSQL. Execute on the IM database server:

    # service postgresql-9.1 restart
    
  3. Start OACI services using the article #126873.

198398b282069eaf2d94a6af87dcb3ff caea8340e2d186a540518d08602aa065 e12cea1d47a3125d335d68e6d4e15e07 5356b422f65bdad1c3e9edca5d74a1ae

Email subscription for changes to this article
Save as PDF