The document provides the approach to rectify the following Dashboard problems related to:

1. Navigation in all Dashboard pages
2. Navigation in a specific page or section
3. Compare Version
4. Monitoring snapshots

Action plan

1. Please perform the below -
   1. Reboot your web server
   2. Update the statistics
   3. Check Memory allocated for web server
   4. Restore the Database on another Server
      

   5. check the web.xml in the WAR deployed, search for alwaysLoadSection option

      if the option is set to on, ie:

       <param-name>alwaysLoadSection</param-name>
       <param-value>ON</param-value>
      <description>Expand Section</description>

      
      

      In this case, all sections in the loaded pages are automatically expanded. This generates lot of extra queries execution and leads to poor performance.

      Please set it back to off to speedup page loading:

      <param-name>alwaysLoadSection</param-name>
      <param-value>OFF</param-value>
      <description>Expand Section</description>

   6. Verify that no tables have disabled autovacuum and then run a manual full autovacuum on the database instance

   7. Recommendations for Tomcat Restart
2. If you have only one Database server at your end due to which you are still blocked or if issue persist then please contact CAST Technical Support with Relevant Inputs
   
   For CAST Technical Support only - 
   1. If the performance issue is specific to Investigation - Application Drilldown then refer to CAST Engineering Dashboard - Long response time for Investigation Application Drilldown - CAST Confidential
   3. Identify queries generating performance issue using the page - CAST Engineering Dashboard - Information - How to identify and execute SQL query involved in data display
   4. After identifying the query prefix the Query with "explain analyze" to the query and run again the query on PGAdmin
   Example: explain analyze <Query> ;

Check Memory allocated for web server

CAST recommendation and procedure to modify the JVM memory configuration settings as described in Appendix - Apache Tomcat, IBM Websphere, Java JDK installation and application server memory configuration of CAST installation Guide. Please follow the deployment scenario given for each version under Deployment - sizing and Appendix - Apache Tomcat - IBM Websphere - Java JDK-JRE installation and application server memory configuration

For the Tomcat memory parameters, it will also depend on the following criteria:

• If Tomcat installation is 32 or 64 bit
• If the java installation is 32 or 64 bit
  
  

It is generally recommented to use 64 bit instalaltions for Tomcat and Java to make the best use of memory on the machine. It is also recommended to apply a formulat for the parameter of "max memory pool" being 512 MB x Number of CED dashboards running on a Tomcat instance. For example, if you have deployed 4 WAR files on Tomcat and to ensure there is sufficient allocated memory for Tomcat in order to easily use the 4 deployed dashboards, the memory allocated to the Tomcat server should be a minimum of 2 GB (512Mb*4)

Restore the Database on another Server

Restore the Database on another Server and try to reproduce the issue. Restoring the database on another server will cause restructuring of data allowing us to differentiate if the root cause of the issue is a bad Tomcat / web server or a Bad Database server. 

Update the statistics

To update statistics, run the query CSS Tools - Optimize - Updating the statistics - using Cssoptimize exe on a schema on the Central Repository schema.

After the updates,

1. Connect to the Dashboard
2. Check if the performance problem has disappeared. If not, go to the next step

Verify that no tables have disabled autovacuum and then run a manual full autovacuum on the database instance

Run the following query to see if any tables that are used by the dashboard have disabled autovacuum:

SELECT relname   ,
       reloptions,
       pg_namespace.nspname
FROM   pg_class
       JOIN pg_namespace
       ON     pg_namespace.oid         = pg_class.relnamespace
WHERE  CAST (reloptions AS VARCHAR) LIKE '%autovacuum%'
AND    nspname                      LIKE '<central_database_name>';

which if there is some specific autovacuum setting for a table will return something like the following for the central database test_central and the table dss_sites:

If the autovacuum has been disabled for some tables, enable the autovacuum for these tables by running the following query:

ALTER TABLE <table_name> RESET(autovacuum_enabled);

and then run a manual full autovacuum in pgadmin on the database instance by selecting the wrench icon and then selecting a VACUUM with the full option as indicated in the screenshot below:

The wrench icon is only active when working at the postgres instance level.

Recommendations for Tomcat Restart

Recommendation is to restart the tomcat server regularly usually every night. Please note that after a restart, for AAD and AED, data in the cache will not be available and the first access will be slower.

Relevant Input

1. Answers to the following questions:
   1. Which web server is being used and the version ? For instance - Tomcat 7.0
   2. How many servers are used?
   3. How many deployed applications per server and at a time?
   4. How often is the weserver rebooted? You may need to do it more regularly.
2. Results of the query given in RDBMS Information - PostgreSQL - Checking missing statistics on a schema to check the last update statistics date.
3. The postgresql.conf file . By default it is located here - C:\Program Files\CAST\CASTStorageService2\db_data\postgresql.conf
4. Sherlock Export with options CAST Databases → Central Database, Logs, Computer Environment checked
5. Login and password to connect to Dashboard.
6. The number of simultaneous users when the performance problem is observed.