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.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
1.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):
2.1 Check the current system shared memory settings:
# sysctl -a | grep 'shmmax\|shmall'
2.2 Calculate the recommended values using the following script shmsetup.sh. The script should be performed on the node hosting the database.
2.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
2.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
2.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>
2.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 (see step 2.4).
To change current 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:
3.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
3.2 Restart PostgreSQL. Execute the following command on Odin Automation database server:
# service postgresql-9.x restart
3.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 512. 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 512 -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:
4.1 Stop Billing service. Execute the following command on Billing application server:
# service pba stop
4.2 Restart PostgreSQL. Execute the following command on Billing database server:
# service postgresql-9.x restart
4.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:
5.1 Stop OACI services using the article #126873.
5.2 Restart PostgreSQL. Execute on the IM database server:
# service postgresql-9.x restart
5.3 Start OACI services using the article #126873.