Search Engine: Elastic

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

  • Applies to:
  • Operations Automation
  • Business Automation

Overview

If you are installing Odin Automation 7.2 from scratch, Operations and Billing database configurations will be automatically tuned by means of odin-pg-tune tool. However, the OACI IM database should be tuned manually (it is required).

Important: If you are upgrading the system to 7.2 version, the automatic tuning of databases will be in case: Operations DB is hosted on MN and Billing DB is hosted on Billing DB node. If the Operations DB is hosted on a remote node or PGHA cluster, and Billing DB is hosted on PGHA cluster, then they should be tuned manually. You will see the respective warning about the required manual tuning during the upgrade. In case of PGHA cluster configuration for Operations and Billing DBs, both master and slave DBs should be tuned manually. OACI IM database is not tuned automatically during the upgrade, odin-pg-tune should be run manually for it.

How to Use `odin-pg-tune`

For better performance experience, we recommend adjusting PostgreSQL configuration using the utility odin-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.

To install the utility, do the following:

  1. If the database is located on service node registered in Odin Automation, then odin-pg-tune utility is installed from Odin pa-central repository (the location of the repository can be found in /etc/yum.repos.d/pa-central.repo. To install the utility, do the following:

    yum install -y odin-perftools
    
  2. If the database is located on a remote node which is not registered in Odin Automation, then the access to Odin pa-central repository will not be available. As one of the options, you can log in to MN and download odin-perftools.x86_64.rpm from Odin pa-central repository using yum-utils tool and then copy to a remote node.

To view the help for the odin-pg-tune utility, do the following:

# odin-pg-tune --help

Usage: odin-pg-tune [options]

Options:
  --version             show program's version number and exit
  -h, --help            show this help message and exit
  -m MEMORY, --memory=MEMORY
                        Total system memory, in bytes. Will try to get the
                        available amount of memory, otherwise default is
                        4294967296
  -n CONNECTIONS, --connections=CONNECTIONS
                        Maximum number of expected connections. Will try to
                        get the current value from config file or database,
                        otherwise default is: 320
  -a AV_MAX_WORKERS, --av_max_workers=AV_MAX_WORKERS
                        Maximum number of auto vacuum workers. Will try to get
                        the current value from config file or database,
                        otherwise default is: 3
  -o OUTPUT_CONFIG, --output-config=OUTPUT_CONFIG
                        Output configuration file. Supported only when -i
                        option is used. Defaults to standard output
  -p PG_VERSION, --pg-version=PG_VERSION
                        Sets the target postgresql version explicitly. Will
                        try to get the value from database, otherwise defaults
                        to 9.6
  -f, --force           Force applying of new settings

  If using postgresql config file as a source directly:
    -i INPUT_CONFIG, --input-config=INPUT_CONFIG
                        Input configuration file
    --cfg-file-only     Use only input configuration file, don't try to find
                        postgresql.auto.conf
  If accessing database directly:
    --db-host=DB_HOST   database hostname/IP [default: 127.0.0.1]
    --db-port=DB_PORT   database port [default: 5432]
    --db-name=DB_NAME   database name [default: postgres]
    --db-user=DB_USER   database username [default: postgres]
    --db-pass=DB_PASS   database password [default: postgres]

Hereinafter, in 9.x occurencies, x is a minor version of PostgreSQL.

If you define the option --cfg-file-only, then the parameters from the /var/lib/pgsql/9.x/data/postgresql.auto.conf will be ignored. odin-pg-tune will use the parameters only from the /var/lib/pgsql/9.x/data/postgresql.conf and tune them.

Note: When PostgreSQL is started, first it uses parameters from the postgresql.conf file, then overrides them by taking values from the postgresql.auto.conf file. Therefore, if you have configured the database using odin-pg-tune with --cfg-file-only option, be aware that when PostgreSQL will be started, the values for the parameters will be overriden by those in postgresql.auto.conf.

How to use the utility odin-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_ORIGINAL>
    

    For example:

    # cp /var/lib/pgsql/9.X/data/postgresql.conf /var/lib/pgsql/9.X/data/postgresql_before_pg_tune.conf
    
  5. Generate the optimized configuration file using odin-pg-tune:

    # odin-pg-tune -f -n <MAX_CONNECTIONS> -p <NEW_DB_VERSION> -i <POSTGRESQL_CONF> -o <POSTGRESQL_CONF>
    
  6. Check the new settings in the <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 odin-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 odin-pg-tune in file /var/lib/pgsql/9.6/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 odin-pg-tune. Before tuning the database, create a backup of the current PostgreSQL configuration file.

Launch odin-pg-tune using the following values:

# odin-pg-tune -f -n 256 -p 9.x -i /var/lib/pgsql/9.x/data/postgresql.conf -o /var/lib/pgsql/9.x/data/postgresql.conf

To apply the changes execute the following:

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

    # service pa-agent stop
    # service pau 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:

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

    # service pau start
    # service pa-agent start
    

    or for Odin Automation 6.0:

    # service pau start
    # service pem start
    

Billing DB

For Billing DB the recommended minimum number of connections is 256. You can define more number of connections but it is highly recommended to consult with Odin representatives preliminarily. Before tuning the database, create a backup of the current PostgreSQL configuration file.

Launch odin-pg-tune using the following values:

# odin-pg-tune -f -n 256 -p 9.x -i /var/lib/pgsql/9.x/data/postgresql.conf -o /var/lib/pgsql/9.x/data/postgresql.conf

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:

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

    # service pba start
    

OACI IM DB

For OACI IM DB the recommended minimum number of connections is 100. Before tuning the database, create a backup of the current PostgreSQL configuration file.

Launch odin-pg-tune using the following values:

# odin-pg-tune -f -n 100 -p 9.x -i /var/lib/pgsql/9.x/data/postgresql.conf -o /var/lib/pgsql/9.x/data/postgresql.conf

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.x restart
    
  3. Start OACI services using the article #126873.

198398b282069eaf2d94a6af87dcb3ff caea8340e2d186a540518d08602aa065 e12cea1d47a3125d335d68e6d4e15e07 5356b422f65bdad1c3e9edca5d74a1ae

Email subscription for changes to this article
Save as PDF