Issue

Odin Automation installations upgraded to 7.1 version have DB (Operations, Billing, OACI databases) logging settings not configured properly while these settings are required to analyze the performance related problems. This issue is related only to the upgraded to 7.1 installations.

Resolution

Tuning of PostgreSQL DB logging parameters makes it possible to analyze performance bottlenecks in the product easier. After tuning is completed, the PostgreSQL starts generate more database logs. However, the additional logging doesn't impact the overall performance of the PostgreSQL, only critically 'slow' requests will be logged.

Important: The commands should be executed under 'postgres' database user against the corresponding database (Operations DB, Billing DB, OACI DB). If the database (Operations/Billing) operates in the High-Availability (HA) mode, then the commands should be executed for both master and slave DB instances.

To activate the logging settings for the upgraded to 7.1 version of Odin Automation, do the following:

  1. Connect to the database under 'postgres' database user:

    sudo -u postgres psql

  2. Set the following parameters for PostgreSQL:

    postgres=# alter system set log_destination = 'stderr';
alter system set logging_collector = 'on';
alter system set log_truncate_on_rotation = 'on';
alter system set log_rotation_age = '1d';
alter system set log_filename = 'postgresql-%a.log';
alter system set log_checkpoints = 'on';
alter system set log_connections = 'on';
alter system set log_disconnections = 'on';
alter system set log_error_verbosity = 'terse';
alter system set log_line_prefix = '[%m] p=%p:%l@%v c=%u@%h/%d:%a ';
alter system set log_lock_waits = 'on';
alter system set deadlock_timeout = '1s';
alter system set log_min_duration_statement = '5s';
alter system set log_autovacuum_min_duration = '1s';
alter system set log_temp_files = '1MB';

  3. (Optional) After execution of the commands, log in to the node hosting the respective database and make sure that the file /var/lib/pgsql/9.X/data/postgresql.auto.conf is created. It should contain the following records:

    log_destination = 'stderr'
logging_collector = 'on'
log_truncate_on_rotation = 'on'
log_rotation_age = '1d'
log_filename = 'postgresql-%a.log'
log_checkpoints = 'on'
log_connections = 'on'
log_disconnections = 'on'
log_error_verbosity = 'terse'
log_line_prefix = '[%m] p=%p:%l@%v c=%u@%h/%d:%a '
log_lock_waits = 'on'
deadlock_timeout = '1s'
log_min_duration_statement = '5s'
log_autovacuum_min_duration = '1s'
log_temp_files = '1MB'

  4. Finally, the PostgreSQL instance should be reloaded:

    systemctl reload postgresql-9.x

In case HA mode is activated for the database, after reloading the master DB, the same commands should be executed for the slave DB and it should be also reloaded.

Internal content