REST API response to time series request is very slow

Products

VMware Smart Assurance

Issue/Introduction

In larger MnR deployments, REST API queries takes around 20 minutes to get ~20000 time series metrics (i.e 20-25 metrics per second).

Environment

Watch4Net/M&R 7.X

Cause

REST API query response is slow while trying to get properties data from property-store.

In larger deployments, properties from all backend properties are stored in the frontend server:

/opt/APG/Databases/APG-Property-Store/Default/data/DB

When property store size grows large (around 300-400 GB), slowness is seen in rest query response.

Resolution

After the properties for each backend are fetched, they are indexed and written out to this directory:

APG-Property-Store directory /opt/APG/Databases/APG-Property-Store/Default/data/DB.

That directory can be split up into smaller directories, each being around 5gb in size.

Disable the import-properties tasks on the frontend, stop the task if it is running.
Before starting procedure: save copy of resources table from master database on PBE

/opt/APG/Databases/MySQL/Default/bin/mysqldump --user=root -p db-password resources --socket ../data/mysql.sock > /tmp/resources.sql

On the PBE, run manage-resources.sh update for each APG database, changing the setting:cachegrp so that instead of being DB in every case, it uses a value from (DB1..DBx). After running the commands to update the resources, the import-properties tasks should be run.

To get list of resources:

/opt/APG/bin/manage-resources.sh list | grep APG
  "dba/APG-DB",
    dba/APG-DB-1 ,
Etc.
 

Update cachegrp for each APG DB:

/opt/APG/bin/manage-resources.sh update dba/APG-DB "{'settings':{'cachegrp':'DB1'}}"
/opt/APG/bin/manage-resources.sh update dba/APG-DB-1 "{'settings':{'cachegrp':'DB2'}}"
Etc.

Once the import-properties task is completed on the frontend, then stop the tomcat service with this command:

manage-modules.sh service tomcat stop

Delete the APG-Property-Store DB by removing the directory
```
 /opt/APG/Databases/APG-Property-Store/Default/data/DB
```
Run the import-properties task from the GUI. This may take several hours
Check that this directory

/opt/APG/Databases/APG-Property-Store/Default/data

- has subdirectories DB1..DBx.
Start the tomcat service (manage-modules.sh service tomcat start)
Login from Frontend GUI and confirm that reports work as before.
If there is a need to roll back,
- Stop tomcat on frontend
- Restore the resources table from master database from the saved version.
```
/opt/APG/Databases/MySQL/Default/bin/mysql --defaults-file=/opt/APG/Databases/MySQL/Default/my.cnf --user=root -p db-password < /tmp/resources.sql
```
- Delete the (DB1..DB6) directories and run the import-properties task, that should recreate the DB directory
- Start tomcat on front end and check reports
- Enable the import-properties tasks on front end. Check the time to run.

It is recommended to distribute the property store in each backend instance to speed up import properties task execution time.