Trimming the Aria Automation Config Postgres SQL database to increase UI performance

Products

• VMware Aria Suite

Issue/Introduction

When the performance of the Aria Automation Config UI is slow, sometimes is it because certain tables in the database have grown very large. It is possible to trim the values in the database and set keep_history parameter in the Aria Automation Config config file, to prevent these tables from growing too large again.

Environment

Aria Automation Config 

Cause

There are too many entries in some of the tables in the Aria Automation Config Postgres database, for the UI to perform well. 

Resolution

When the performance of the Aria Automation Config UI is slow sometimes is it because certain tables in the database have grown very large.

There are tables that pertain to job, event, schedule, and audit information.
You can check to see if the tables are large with the SQL select statement below against the Aria Automation Config Postgres Database.

To trim the database, you can create and run the python script below to remove rows from these tables.

The trim pythin script below (trim.py), shows that we will keep 7 days of data for each table.  

Then you can set in the keep_history values also to 7’s in the /etc/raas/raas config file on the Aria Automation Config server, so in the future only 7 days of data will be kept.

 

SQL statement to check table sizes:

SELECT table_schema, table_name, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a order by total_bytes desc;

 

Creating and running the trim python script (trim.py) on a salt master:

1. From the Aria Automation UI download the wheel file and FTP it to a salt-master:
   Administration->master plugins downloads->download client
   
   
2. Install the wheel file from above:
   salt-call pip.install /absolute path to the wheel file downloaded above
   
   
3. Create a script (trim.py) on the salt master replacing the raas_server_ip_or_fqdn with your Aria Automation Config FQDN or IP:
   
   from pprint import pprintfrom sseapiclient import APIClient
host = 'https://raas_server_ip_or_fqdn'
user = 'root'
password = 'salt'
client = APIClient(host, user, password, ssl_validate_cert=False, timeout=19000)
response = client.api.admin.trim_database(audit=7, events=7, jobs=7, schedule=7 )
print(response.ret)
4. Execute the above script with the following command:
   /opt/saltstack/salt/bin/python3 trim.py

 

Set keep_history on the Aria Automation Config server in /etc/raas/raas:

# How long to keep historical data in days (leave unset to keep forever)
keep_history:
  audit: 7 # How long to keep audit log (if audit is enabled)
  events: 7 # How long to keep salt events
  jobs: 7 # How long to keep job data (commands, jids, returns)
  schedule: 7 # How long to keep past schedule data