Search Engine: Elastic

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

  • Applies to:
  • Operations Automation 6.0
  • Business Automation 7.0
  • Odin Automation Essentials 7.1

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 (tuples) are being removed and database space is reclaimed.

Since OA 7.1 with PostgreSQL 9.6 autovacuum routine is enabled. Autovacuum performs periodic cleanup of DB tables basing on bloat level and resources required for vacuuming. While autovacuum tries to be 'invisible' in terms of DB performance and does not eliminate all obsolete tuples on sight, it allows to maintain reasonable level of dead records across the database.

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

Check the necessity of vacuuming:

oss=# SELECT relname,n_live_tup,n_dead_tup, 
CASE WHEN n_live_tup=0 THEN round((n_dead_tup::numeric/100), 2) || '%' 
ELSE round((n_dead_tup::numeric/n_live_tup::numeric*100), 2) || '%'  
END as bloat, 
pg_size_pretty(pg_total_relation_size('"' || t.table_schema || '"."' || t.table_name || '"')) AS size 
FROM pg_stat_all_tables p 
JOIN information_schema.tables t on p.relname = t.table_name 
ORDER BY pg_total_relation_size('"' || t.table_schema || '"."' || t.table_name || '"') desc limit 50;

This metric will show you "live" and obsolete records for all tables sorted by table sizes. 20% of bloat of big table clearly shows the necessity of vacuuming. The decision should take into account table size and its bloat level. Example of output follows:

                  relname             | n_live_tup | n_dead_tup | bloat  |  size
--------------------------------------+------------+------------+--------+--------
 aps_property_value                   |     711182 |      31818 | 4.47%  | 243 MB
 proxies_config_files_contents        |        478 |          0 | 0.00%  | 201 MB
 aps_resource_link                    |     235545 |       1006 | 0.43%  | 90 MB
 resources_usage_log                  |     289225 |      27806 | 9.61%  | 65 MB

In this example, 9.6% bloat level of a table resources_usage_log consume less space then 4.47% of aps_property_value. Effectively, bloat level shows the amount of reclaimable disk space and therefore necessity of VACUUM FULL.

Maintenance procedures

All procedures below should be performed during the maintenance window since some operations require stop of OA services and 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 pa-agent stop
    service pau stop
    

    It is required to stop any services which use database since VACUUM will block any requests to processed tables. More information about OA services could be obtained from KB 4642.

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

    oss=> VACUUM FULL;
    

    Warning: VACUUM FULL copies all 'alive' records from each table to temporary one. So this step requires amount of free diskspace equal to the biggest table of the database.

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

    Notice: VACUUM could be canceled at runtime with to harmful consequences.

  4. It is required to update planner after VACUUM FULL since Postgres planner relies on statistics on data distribution across the tables and VACUUMing changes how data is distributed. Perform ANALYZE which will optimize SQL planner resulting in improved queries responsiveness:

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

    service pau start
    service pa-agent start
    

Maintenance of indexes is not usually required unless indexes are corrupted or invalid. 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 c0f836394088a28cc30dd0e5fe8b600e 198398b282069eaf2d94a6af87dcb3ff b2c3b33425dfc50c7d41a2efaa7f84f3 e2dd85103e4d5b8ef7231deccb87506e d29f8259a7236d291b08be8147a45abd

Email subscription for changes to this article
Save as PDF