Information on Client 0 System has performance issues: DB performance

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

Beginning in 21.0.9, users may see the following warning in Client 0:

What does this mean?  How can troubleshooting be done?

Environment

Version 21.0.9 and above

Cause

This message is shown due to an hourly performance check done by a WP that indicates there could be performance problems when communicating with the database.  The message could show that the performance does not meet the minimum requirements for a scaling Automic system.

Resolution

Here is some important information about this message:

• This message is shown because of an hourly performance check introduced in 21.0.9
• In 21.0.9, a randomly picked WP will run this check on the hour. 
• In 21.0.10 and 24.0, the check will run 5 minutes before every hour.  
• This is an indicator that certain aspects of the database access times took longer than the benchmarks we've found in a system that performs well
• This is the same check that runs at startup of the C/WPs
• This check has been always a part of the product, now are exposed beginning with 21.0.9++
  
  
• The check runs SQL that does the following:
  
  • 1000 inserts: INSERT INTO DIVDB (DIVDB_PK,DIVDB_Memo,DIVDB_String,DIVDB_Date) VALUES(1, ?, ?, ?)
  • 2000 selects: SELECT DIVDB_String, DIVDB_Date, DIVDB_Memo FROM DIVDB where DIVDB_PK = 1
  • 1000 rollbacks
  • and other DB api functions (like BIND/FETCH/...)
    
    
• This entire check should ideally take less than 3 seconds (3000 milliseconds) - this number can change depending on the database type - please use the metrics dashboard mentioned below to review benchmarks specific to your system.
• The information on where slowdowns could be is written to a JWP log and is kept in a performance table within the Automic database

 

The first step to review this is to take a look at the JWP logs for lines like the following:

U00005901 The following metrics were classified as suspicious:
U00005902 AUTOMIC#WP001 ([IP OR DNS NAME]) at UTC 2024-01-15 00:00:10.0: {db-perf=190.0,rollback=1244370.0,select=2623732.0,fetch=6656.0,bindpar=3204.0,insert=1328542.0,close=15371.0,cpu-perf=9.2744795E7,bindcol=5838.0}

The performance check adds all the values above and divides by 1000 (all values above are in microseconds, so dividing by 1000 will show it in milliseconds).  If this number is greater than 3000, then it will show this message.

So in the example above, this would be:
(1244370+2623732 + 6656 + 3204 + 1328542 + 15371 + 5838)/1000
or 5227713/1000
or 5228 ms

Since 5,228 ms is greater than 3000, this will show the warning in client 0 that the system might have problems scaling (performance problems) and this should be investigated further by your admins.  It's important to note that the information shown in the Automic product is indicative of problems outside of Automic.  This could be on any layer: JDBC, I/O on the AE server, network between the AE server and the database server, I/O on the database server, etc...  You may need to engage multiple teams (your DBAs, system admins, server admins, network team, etc...) to assist in troubleshooting.

 

Another source of troubleshooting can be found by using the Automic DB Metric Visualization Tool found at downloads.automic.com under the Tools menu:

This tool will show:

• A chart for each of the following:
  • How long the overall performance check takes
  • The time it takes for the INSERT statements
  • The time it takes for ROLLBACKS on the database
  • The time it takes for the FETCH/CLOSE to happen
  • The time it takes for BINDCOLUMN/BINDVALUE to happen
• Benchmark values that are used for this check which show as Ideal Value (green), Must be Reached (yellow) and System Will Not Scale (Red)
  • Values beyond the Red value show that the system will likely not scale or perform in an optimum fashion
  • Values near Yellow are okay, but not optimal
  • Values near Green show that performance is good
• Historical information on the performance check that can be used to see trends over time when the performance may have been better or worse
• The information icon on each chart will show:
  • What that particular metric looks at
  • What api call is used to reach out to the database
  • What to check if the value is not within the expected range

Here is an example of what one chart might look like with the information icon selected:

A DBA should be able to analyze the chart and the database to see what happens during at the time that this check is done and doublecheck how long the statements took in the database. 
For example, if the DBA says that the select took less than a millisecond on the database, but one of the values is at 2,623 ms, then there is a problem between Automic and the database or between the database and Automic.  This could be on any layer: JDBC, I/O on the AE server, network between the AE server and the database server, I/O on the database server, etc...  Multiple teams may need to be involved in troubleshooting this information.

Additional Information

Installing the Automic DB Metric Visualization Tool

After downloading the tool from https://downloads.automic.com/tools/automic_db_metric_visualization, do the following:

1. Log in to client 0 and update the Sql variable setting SQLVAR_INTERNAL value to YES

2. Unzip the db-metrics zip file. The structure of this will be

FOLDER - copy-to-awi-autoinstall-folder
FILE   - db-metrics-v1.0.0.xml

In the 'copy-to-awi-autoinstall-folder' folder are two folders

v21
v24

Each contain a jar file named webui-plugin-analytics.jar

Depending on the version of Automic that is installed, take the correct webui-plugin-analytics.jar file and place it in the autoinstall folder located in the AWI directory structure.

The autoinstall folder location depends on whether Tomcat or Jetty is used for AWI. 

For Jetty, the location is here:

/WebInterface/plugins/autoinstall

For Tomcat, the location is here:

<webserver>/webapps/awi/WEB-INF/autoinstall

No restart is required. 

3. Import the db-metrics-v1.0.0.xml file to a non-zero client. 

4. To fill the dashboard charts with data, run the script '_AE.DBOPEN.GET.VALUES.FOR.PERF.METRIC.CHARTS' located in the folder where the XML file was imported. 
   a)  It is recommended this script is scheduled to run hourly 2 minutes after the hour in v21++ and 2 minutes before the hour in v24++.
   b)  The system will perform the check every full hour sharp in v21 and 5 minutes before every full hour in v24++.
   c)  The (i) icon can be used on each chart to get detailed information on the values presented.

To run the script hourly, right click on the script and select Execution Options > Execute Recurring

At the bottom under Frequency, set the 'in intervals of every' to 1 hour and the Between time to 0 : 2 and 23 : 59  for version 21.0.0++ or 0 : 58 and 23 : 59 for version 24++

v21.0.9++:

v24++

This should be set to run every day and never end