CAST Imaging - Configuration process - Export the application - Performance issue

Action Plan

1. Check if the server is configured according to CAST recommendation, for this perform the following

   1. Run the following query on a Postgres session :

      SELECT NAME, setting FROM pg_settings

      As a query result sample, refer to PG_param.sql

   2. Compare the server configuration retrieved from the previous step with the server configuration recommended by CAST: Server configuration

      If the configurations are not identical then
      1. Change the parameter from postgresql.conf (the file is in the postgresql directory\db_data) as recommended by CAST,
      2. Restart PgAdmin Service, then
      3. Compute a snapshot by skipping analysis. The issue can be due to the change in work_mem parameter.
2. Vacuum and Analyze the local schema by following - Optimizing CAST schemas
3. Check if there are indexes that have been dropped in the local schema, to do so compare the count of indexes between the used local schema with a fresh local schema, for more information refer to  SQL Queries - Common SQL Queries - How to count the number of indexes in a schema. If there are indexes that are missing, you must run a component reinstall using the Server Manager Tool.
4. Check the usage of CPU on RDBMS Server and Export machine, for that you can use the Task manager:
   
   If the usage of CPU has reached 100% then manage the processes that are currently in progress in order to allocate enough space for the export process.
5. At this stage we need to check if the computation gets stuck in a specific stored procedure or there are one or several stored procedure that is consuming long time. For this we start by getting the time processed by stored procedure by following this page - CAST Imaging System - Information - Profile execution time of extracting application
   1. If you are able to identify the action(s) that takes the longest time from the above query then we can confirm that the issues are due to the performance of the action(s),If it is so contact  CAST Technical Support and provide the following Relevant Input

   2. Else If you are not able to identify the action(s) that takes the longest time then the issue maybe due to the fact that the computation is stuck in a specific action, In order to identify the action compute the following on the local schema:

      SELECT step_name FROM aaa_is_logs ORDER BY history_id DESC LIMIT 1

      Query result sample: 

      "Begin aaa_is_init"

      If you confirm that the query is stuck on a specific stored procedure then  contact CAST Technical Support and provide the following Relevant Input

   3. Else if you are not able to identify that the issue is due to the performance issue of an action or to the fact that the computation is stuck then this can be due to the fact that there is a query that gets stuck without history in the schema, in order to identify the query, do the following:

      1. Get the active query:

         1. By using statistic table, for that run the following on an SQL session:

            SELECT application_name, client_addr, query_start, state, query_start, query FROM pg_stat_activity WHERE client_addr = inet '<IP Addresse of the machine hosting the triplet >' AND state != 'idle'

            Query result sample:

            "pgAdmin III - Query Tool";"192.168.17.61";"2018-03-08 10:06:11.594+00";"idle";"2018-03-06 16:45:15.814+00";"SELECT CASE WHEN typbasetype=0 THEN oid else typbasetype END AS basetype FROM pg_type WHERE oid=20"

            Query interpretation

            The query returns the application name, the address IP of the machine hosting the triplet, query state (active or not active), the query start date and the the query

         2. If the active query takes the largest time of the Export then you have identified the root cause of the issue. contact CAST Technical Support. with the following Relevant Input