Search Engine: Elastic

Article ID: 131447, created on Sep 12, 2017, last review on Sep 12, 2017

  • Applies to:
  • Operations Automation 7.1

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.

5356b422f65bdad1c3e9edca5d74a1ae caea8340e2d186a540518d08602aa065 e12cea1d47a3125d335d68e6d4e15e07 8c199f0ee4305da1a577740620df4a51 1941880841f714e458ae4dc3d9f3062d

Email subscription for changes to this article
Save as PDF