How To Identify Performance Issues With Insights

News Knowledge Base
, , , , , , , ,
1

How to identify performance issues with Insights 22.4+?

Issue Description

Insights is having performance issues, and they need to be diagnosed.

Root Cause

While the frontend portal could have performance issues, most likely it is the backend or how the tiles are built. This article will mainly outline how to check the performance of backend components and how tiles are built.

Diagnosing

  1. First step is to review the Insights recommendations here: Best Practices
    1. The baseline for performance issues is typically the OOTB Templates. If these load in a timely manner than most likely some best practices are not being followed on the custom dashboards.
    2. Check to see if any tiles are using the RobotLogs Explore. If they are, see if a different explorer can be used instead. The RobotLogs explore is the most performance intensive explore.
    3. Isolating slow widgets can also be helpful. Try copying the dashboards and removing some tiles to see if a particular one is slow (for example, we might find the slow tile might be using the RobotLogs for its explore). Isolating tiles can also make debugging much easier.
    4. Avoid joining two views together if possible. For example, if we are building a report of faulted jobs by machine, we would want to select the faulted job count and machine name both from the Job view on the Job Explore. If instead you use the RobotLogs explore and select the Faulted Job count from the Jobs view and Machine Name from the RobotLogs view, there will be a big performance hit because on the backend, the RobotLogs table has to be joined with the Jobs table.
  2. For cloud issues, if the best practices do not help, please open a ticket. Include the following:
    1. Export of Dashboards: Export Dashboard
    2. Har Trace: How To Generate A HAR File? - This should be generated by loading the specific dashboard.
    3. Let us know if the slowness affects the OOTB Templates
    4. Any other data that might be helpful (recording, etc).
  3. If the issue is related to PDFs and is an on-prem deployment, go to the section Troubleshooting PDF Rendering.
  4. Check to see if its a timezone issue:
    1. Go to the dashboard.
    2. In the options on the top right, change the timezone to UTC and reload the dashboard.
    3. If this resolves the issue, raise a ticket with UiPath and explain the test that was done. For on-prem, Windows can be inefficient at timezone conversion.
  5. For on-prem make sure to check the hardware requirements. Most issues will be caused by having an SQL Database that is undersized or by a Linux machine that is undersized.
    1. For SQL, the following query can be used to evaluate the size of the dataset. This can be ran on the Insights SQL server database: 
      • Select
    S.name + '.' + T.name As TableName ,
    SUM( P.rows ) As RowCont 

        From sys.tables As T
        
Inner Join sys.partitions As P On ( P.OBJECT_ID = T.OBJECT_ID )
        
Inner Join sys.schemas As S On ( T.schema_id = S.schema_id )
        
Where
        
( T.is_ms_shipped = 0 )
        
AND
        
( P.index_id IN (1,0) )
        
And
        
( T.type = 'U' )
        

        Group By S.name , T.name
        

        Order By SUM( P.rows ) Desc

    2. If a ticket is opened with UiPath for a performance issue, make sure to share the output of the above query along with the hardware specs of the SQL instance. If the SQL database is co-located with other databases, make sure to mention that.
    3. SQL requirements can be found at Insights Hardware And Software Requirements - SQL Requirements
  6. Next, identify which components are performing slowly.
    1. Pay attention to where the slowness is. Is it during login and the initial page loading? Or is it when accessing a dashboard?
    2. Generate a HAR trace. See How To Generate A HAR File?
    3. For this step, the network tab needs to be opened. (So right-click anywhere, select inspect, and then go to the 'Network' tab in the debug window)
    4. Reproduce the behavior
    5. Once the behavior has occurred, sort the network trace by time. An example is below
      1. In the example, sort the requests by time.
      2. We can see that the two longest-running requests were 'queries' and 'home'
      3. When clicking on specific requests (let's say queries in this example), the specific service or component that was being invoked can be seen. (If the request is already highlighted, click another request and then re-click the originally highlighted request).
      4. See the full details of the query request. It takes 21s and goes to the API: https://looker.local:9999/api/internal/querymanager/queries. This indicates that it is the looker component that is slow.
      5. When clicked on the 'home' request (which took 11s), it was directed to: https://insights.local:9443/insights/home. This is the frontend component that sits on the Windows machine. This request was also somewhat slow, but can be expected when the site has not been accessed for some time.
      6. If a ticket is to be opened, capture the har request and share it with UiPath support.
  7. Based on the above, zone in on specific components. If a Windows component is slow, log in to the Insights Windows server and check the current CPU usage and Memory utilization using Task Manager. Perfmon can also be used to see exactly what is causing the performance issue.
    1. For using perfmon, see more details at Windows-Commands - perfmon
    2. If it seems the Windows component is slow, it's recommended that a ticket is opened with UiPath. In the ticket, include: A har trace, a screenshot of the utilization in Task Manager.
  8. If it seems that it is the looker component (which is seen in the case of the 'queries' API call) we should check some items on the looker backend. To log in, do the following:
    1. Log in to the Looker back end. See more at Accessing The Backend UI Of Insights
    2. After logging in, go to the admin section:
      1. In Looker 22.4+
      2. In Looker 21.10
        • image.png
  9. Try to examine specific slow queries and see if they can be optimized:
    1. In the Admin section, find the section 'Database' and then select the subfield 'Queries'.
    2. On this page you can see the queries that are executing or have executed.
    3. Trigger the Dashboard that is slow.
    4. Look for any queries that are long-running. In the example below, we can see the last query had a runtime of three minutes.
      1. kb-0EMPa00000KM8oD.png1593×166 28 KB
    5. Click on the details of the query.
    6. In the Details pop-up, go to the SQL option.
    7. Grab the select clause from the query. The Query will always start with a select clause and then be followed by a from clause. From the select clause we can see if the query has unnecessary joins.
    8. We can examine the query to get a hint of why it's slow:
      1. For example, in the query below
      2. SELECT
    jobs.DisplayName  AS "jobs.process_display_name",
    UPPER(jobs.RobotName)  AS "jobs.robot_name",
    robotlogs.MachineName  AS "robotlogs.machine_name",
    COUNT(DISTINCT jobs.Id ) AS "jobs.processes_ran"
FROM
      3. In the above, we have four fields: jobs.DisplayName, jobs.RobotName, robotLogs.MachineName and Jobs.Id.
      4. We can also determine which view each field is a part of. For example, jobs. Displayname is part of the jobs view because it has the prefix 'jobs'. RobotLogs.MachineName is part of the RobotLogs view because it has the prefix 'robotlogs'.
      5. Checking the insights data model to see if the field selection can be consolidated into a single view: Insights Datamodel
      6. Per the insights datamodel, the Machine Name field is shared by both Jobs and RobotLogs. As such, if this were part of a slow query, we could go to the tile and change the field selected in the RobotLogs view with the equivalent field in the Jobs view.
      7. NOTE: The select query will often look more complicated (like when there are timezone conversions). However, the view name can also be explored by looking for the value after the 'AS' keyword. i.e. in the below we have: jobs.robot_name, jobs.end_date and robotllogs.machine_name. 
        1. UPPER(jobs.RobotName)  AS "jobs.robot_name",
        (TO_CHAR(TO_DATE(CONVERT_TIMEZONE('UTC', 'America/New_York', CAST(jobs.EndTime  AS TIMESTAMP_NTZ))), 'YYYY-MM-DD')) AS "jobs.end_date",
    robotlogs.MachineName  AS "robotlogs.machine_name",
  10. Next, test the DB connection and see if killing queries is allowed.
    1. Go to the section 'Database' and select the 'Database Connections'.
    2. Test the Insights connection:
    3. The test should succeed, but the test to cancel queries may fail.
    4. When Insights has performance issues, it's common for a user to load a dashboard and then try to reload the dashboard, or close the window and try again. This can make the issue worse because the query is not necessarily canceled unless the insights user can kill queries. If the query is not canceled, it can lead to a situation where multiple high-impact queries are queued over and over again. By giving the insights user the ability to kill queries, it can reduce the impact of users reloading dashboards and causing a bottleneck.
    5. To give the insights user the ability to cancel queries, they need permission to view the server state and alter connections. However, be aware that these are elevated permissions, and this allows insights to kill any connection. (The application will only ever try to terminate connections it owns. However, someone with the credentials could potentially kill other connections. This might be something that is only granted temporarily to alleviate symptoms. IMPORTANT: Make sure to replace the USERNAME with the actual Insights SQL username. 
      1. USE master;
GRANT ALTER ANY CONNECTION TO USERNAME;
GRANT VIEW SERVER STATE TO USERNAME;
      2. To verify the settings (Replace USERNAME with the action username): 
        1. SELECT
    prin.name AS [User],
    perm.permission_name,
    perm.state_desc
FROM sys.server_permissions perm
INNER JOIN sys.server_principals prin ON perm.grantee_principal_id = prin.principal_id
WHERE prin.name = 'USERNAME'
AND (perm.permission_name = 'ALTER ANY CONNECTION' OR perm.permission_name = 'VIEW SERVER STATE')
  11. In the Admin section, find the section called 'System Activity'. Within this section of options will be the option 'Database Performance'
    1. Go to 'Database Performance' first
    2. In the dashboard, in the top right corner, is a 3-dotted icon. Click that and select Download
    3. Go through the download options. The two options for the format will be PDF and CSV. Try PDF but sometimes due to performance issues, this might take too long. If this is the case, try CSV.
    4. We can also look at 'Instance Performance' but usually this info is not as helpful.
    5. Share the Database Performance information with UiPath.
  12. Finally, check the Linux system itself.
    1. The command 'uptime' will display how much CPU the Linux machine is using.
    2. Additionally, running 'top' will show the top consumers of CPU.
    3. And ps aux will also show process information.
    4. Capturing the output of the above commands can help determine if the bottleneck is in the Linux machine.
  13. If none of the above helps, please raise a ticket. Include the following:
    1. Export of Dashboards: Export Dashboard
    2. Har Trace: How To Generate A HAR File? - This should be generated by loading the specific dashboard.
    3. Let us know if the slowness affects the OOTB Templates
    4. Any other data that might be helpful (recording, etc).

Troubleshooting PDF Rendering:

  1. Get the Looker password and log in to the back-end UI. See: Accessing The Backend UI Of Insights
  2. In the backend UI, go to the Admin section (see steps in the Diagnosing section, step 5)
    1. Once the Admin section is selected, go to the section 'Database' and select 'Queries.'
    2. Keep this page open.
  3. In another browser session (we can use incognito or a different browser), log in to Insights and reproduce the issue.
  4. Go back to the backend UI.
  5. Queries will be listed. Either the queries will be rendered, or some will have been completed. What we are interested in are queries that are timed out or not complete. Also, the render queries will always have the source 'Renderer'. Here is an example
  1. If a query is timing out, go to the query and click 'Details.'
    • image.png
  2. Capture screenshot(s) of the Query Details.
    1. Make sure to capture both the info on the 'Info' toggle and the query on the 'SQL' toggle.
    2. What we are interested in is
      1. Which queries are timing out
      2. How long do they take to run
      3. And what the backend query is in SQL.
  3. Additionally, if the Insights portal displays any errors, share them as well.