CMS Snapshot Analysis - Run Analyzer - Performance issue - Saving Step

This page will help you solve problems related to Performance issues that occurs during the saving step. The saving step occurs at the end of the run analyzer step, when the analysis process compares and flush the results in the Knowledge database.

1. Identify whether the performance issue lies in the Comparing Step or in the Saving Step. You can verify this, by checking the dates of begin and end for each step in the analysis log file.
2. Take a backup of the Management Base and Knowledge Base
3. Activate the AMT Debug Mode by following the page : Activate AMT saving debug mode, disable all the tools after analysis, and then relaunch analysis.

4. Replay the AMT saving step-by-step and generate a stack trace

5. Provide the call stack to CAST Technical Support, along with the below Relevant Input

Check if the performance issue is due to database server bad performance

1. Run the PGBench tool that will run a set of SQL transaction on your CSS / postgresql Server. Follow the steps of the page RDBMS Information - PostgreSQL - Performance monitor with PGBENCH
2. Open the output file
3. Check the 3 last lines giving information about performance.

Replay the AMT Saving step-by-step

1. Identify the number of the SAV_IN tables set.
   1. Open pgadmin and look for tables called SAV_IN_OBJECTS_X where X is a number. If after activating the IN tables, you analyzed the execution unit which has a performance issue only, then there should be only one. X is the number of the sav_in_tables set.
2. Identify the session ID

   1. Run the following query on the KB : <SET_NUMBER> should be replaced by the number of the SAV_IN tables set

      Session ID

      SELECT DISTINCT session_id 
      FROM            sav_in_objects_<SET_NUMBER>;

   2. The result of the query will be the SESSION_ID
      
      
3. Identify the project ID

   1. Run the following query on the KB (objtyp 669 is the id type for User Projects):

      Project ID

      SELECT idkey, 
             keynam 
      FROM   keys 
      WHERE  objtyp = 669 
             AND keynam != 'KB Information Finalization'; 

   2. Example of results :

      Project ID results

      301;"My_User_Project"

      The project ID is 301 in this example

4. Launch the initialization by running the following query on the KB

   Launch Initialization

   SELECT cache_init(<SESSION_ID>);

5. Fill the IN tables. Run the following query on the KB :

   Fill-in the IN tables

   SELECT dbgamt_replayloadintable(<SET_NUMBER>, <SESSION_ID>);

6. Launch the Comparing Step

   1. Run the following query in KB :

      Comparing Step

      select CACHE_PROCESSID(<SESSION_ID>, <PROJECT_ID>);

   2. Check if issue is reproduced (query is stuck) in Comparing step.
      1. If issue is reproduced in Comparing Step, then proceed to 'generate a stack trace' step
      2. If not, then launch the Merging Step

         1. Run the following query in KB :

            Merging Step

            SELECT cache_flushdata(<SESSION_ID>)

         2. If issue is reproduced(query is stuck), then proceed to 'generate a stack trace' step
            
            
7. Generate a stack trace for the running query
   1. Using PGAdmin, connect to the CSS server. Click Tools, click Server Status like below :
      
   2. Check in the server status the function that is currently in progress
      
      
      1. If the issue was in the Comparing Step, then you should see "CACHE_PROCESSID" in orange color in the above screenshot.
         
      2. If the issue was in the Merging Step, then you should see "CACHE_FLUSHDATA" in orange color in the below screenshot.
         
      3. Click on the orange button to cancel the query. It will generate a stack trace in the Log View. If not open, open the Log View by selecting View → Application Logs in the Main Menu.
         
      4. Copy the whole stack trace and provide it to  CAST Technical Support
         

Relevant Input

1. Sherlock (CAST Support Tool (CST) - alias Sherlock) with option  Export databases (KB only), taken after you apply the Action Plan
   
2. Stack trace generated during the Action Plan.