Article ID: 129158, created on Jul 23, 2016, last review on Jul 23, 2016

  • Applies to:
  • Operations Automation 6.0

Question

Operations Automation in its day-to-day work relies on the integrated database. What actions could be done by system administrators to keep database in good shape?

Answer

Despite that OA database uses modern PostgreSQL engine, it requires some maintenance operations to be performed on a regular basis by system administrator. Poorly maintained database could lead to slow performance of queries which will proportionally slow down all interactions with OA interface and even may interfere with services provisioning.

Universal solution for the database maintenance is Vacuuming. It is standard procedure during which obsolete records (touples) are being removed and database space is reclaimed.

At first step, gather "maintenance" metrics via executing the following queues. These queries do not require stop of service and could be executed anytime.

Preparation procedures

  1. Check the necessity of vacuuming:

    plesk=> select relname,n_live_tup,n_dead_tup from pg_stat_all_tables order by n_dead_tup desc limit 20;
    

    This metric will show you "live" and obsolete records for all tables. 20% of n_dead_tup (relatively to n_live_tup) clearly shows necessity of vacuuming.

  2. Check database size:

    plesk=> SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
    FROM information_schema.tables
    ORDER BY
        pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;
    

    This metric will show how big tables are and, using data from the first step, it is possible to calculate amount of reclaimable disk space and therefore necessity of VACUUM FULL.

Maintenance procedures

All procedures below should be performed during the maintenance window because database performance will degrade during vacuuming.

  1. Perform OA database backup as described in KB 113963:

    # /usr/local/pem/bin/backup.sh [-o output_directory] [-d dbhost] –t db
    
  2. Stop OA services on the management node:

    service pem stop
    service pau stop
    

    More information about OA services could be obtained from KB 4642.

  3. Reclaim unused disk space and perform obsolete touples cleanup by performing VACUUM FULL:

    plesk=> VACUUM FULL;
    

    Duration of full vacuum scales with amount of obsolete records and overall size of the database. It is safe to plan around an hour of maintenance for this operation. This step could be skipped if you are performing regular weekly maintenance and could be performed monthly.

  4. Perform ANALYZE which will optimize SQL planner resulting in improved queries responsiveness:

    plesk=> VACUUM ANALYZE;
    
  5. Start OA services:

    service pau start
    service pem start
    

Maintenance of indexes is better perform with caution. If you are experiencing frequent occurrences of index errors, please contact Odin technical support.

External references:

  1. vacuum command
  2. routine vacuuming

5356b422f65bdad1c3e9edca5d74a1ae caea8340e2d186a540518d08602aa065 e12cea1d47a3125d335d68e6d4e15e07 956c448bddc7e1f3585373687602379f 6f1456866eed87488c0f02b298a741c0

Email subscription for changes to this article
Save as PDF