Date Posted:
Product: TIBCO Spotfire®
Product: TIBCO Spotfire®
Problem:
Troubleshooting Guide: Performance issues with Information Links
Solution:
This article describes basic troubleshooting steps for performance issues with Information Links (Information Link taking too long to fully open).
There are a few common causes, most specifically:
- Database performance
- Network performance
- Resources available to the TIBCO Spotfire Server (TSS)
Beyond those technical issues, performance related Information Services issues may also be caused by unrealistic expectations of performance. For example, where the Information Link execution time is compared to an unrelated or different benchmark, or compared to a business requirement which does not fully consider the technical details (amount of data being accessed, network speeds, etc).
Identify Information Link execution in sql.log
First, find the actual execution times of the Information Link as seen in the TIBCO Spotfire Server’s sql.log (<TSS Installation Directory>\tomcat\logs\). Here are 3 key log entries:
(1) The query being executed:2016-10-03T15:06:38,331+0200 [*pool-6-thread-2, ali, #13, #62*]: [data-source="mssql2k12"] SELECT B1."NAME" AS "NAME", B1."AT_BATS" AS "ATBATS", B1."HITS" AS "HITS", B1."TEAM" AS "TEAM" FROM "spotfire_demodata"."dbo"."BASEBALL" B1(2) The Execution time, which is the actual time to execute the Information link query on data source and have the data returned to TSS:
2016-10-03T15:06:38,331+0200 [*pool-6-thread-2, uname, #13, #62*]: Information Link 'MyInfoLink' (e9f667a4-3325-4910-82ca-79b2955442e1) executed successfully, time: 1.482 seconds
(3) The Job Closed time, which is the total time from when the query starts executing until the client has received all the result data. This is inclusive of the prior the execution time:
2016-10-03T15:06:38,783+0200 [*Thread-16, uname, #13, #62*]: Information Link 'MyInfoLink' (e9f667a4-3325-4910-82ca-79b2955442e1) job closed, time: 1.934 seconds
3rd party client test for meaningful comparison and benchmarking
Second, evaluate the execution time and job closed time with the expected execution time and how those expectations were set:
- What is the basis of the expected execution time? Other client timings, non-technical business requirements, etc
- What test was performed to get these time requirements?
The design of the benchmark test is very important. Note that comparing the execution time from a 3rd party client on your local client machine which connects directly to your data source is not the same as opening a report which uses Information Links from the Analyst client on your local machine (since the Information Link is executed on the TIBCO Spotfire Server, and not from the local client machine).
The best test to use when analyzing Information Link performance is:
- Use a third party tool which can use a JDBC driver (Recommended: DbVisualizer https://www.dbvis.com/, see KB 000020474 for additional related details)
- Use the same JDBC driver as the TIBCO Spotfire Server is using (found in \tomcat\lib or \tomcat\webapps\spotfire\WEB-INF\lib)
- Use the same query as seen in the sql.log
- Execute the query on the same machine as the TIBCO Spotfire Server
Comparison
Then compare of the result of the 3rd party client test to the ”executed successfully” and "job closed" log entries in the sql.log file mentioned above.
Results
Based on this comparison, there are 4 primary potential outcomes which are covered in this article:
- Case 1: Time is equal in third party tool
- Case 2: Time is better in third party tool
- Case 3: Time is equal for execution time, but a lot of time is spent before closing job
- Case 4: Time of query is equal, including job closure, but start of job on server is delayed
Case 1. Time is equal in third party tool
In this case, the "executed" and "job closed" time from the Spotfire sql.log are approximately the same as the execution time seen in the third party tool test. That means there is no indication that there is a performance degradation in the Spotfire application, and you should instead involve your database and network administrators to investigate any ways to improve performance outside of Spotfire.Case 2. Time is better in a third party tool
In this case, the "executed" and "job closed" time from the Spotfire sql.log are significantly more than the execution time seen in the third party tool test. Here are a few steps to take:Verify resource utilization
On the TIBCO Spotfire Server, look at both the overall system and Tomcat process resource utilization:- Is the overall server utilization approaching 100% CPU or 85% RAM? If so, it is likely a resource limitation that is causing issues. Consider increasing the system resources to accommodate the increased load.
- Is the Tomcat process (tomcat7.exe or tomcat8.exe) RAM utilization pegged at the java heap max setting limit (this is defined in the JvmMx parameter, see reference Modifying the virtual memory (server running as Windows service)? If so, the JvmMx may need to be increased to accommodate the size of data being transferred through the TSS.
Data Import vs Streaming
It is possible you might be importing the whole data set instead of streaming it, causing the JVM to be strained. An example of when we import instead of stream data is when the SQL runtime is removed from a data source template:
<sql-runtime> com.spotfire.ws.im.ds.sql.mysql.MySQLDriverSQLRuntime </sql-runtime>This is an example of error as seen in the server.log:
ERROR 2012-05-17 14:19:23,326 [*pool-2-thread-10, spf_admin, #32*] ws.dat.AbstractOperator: Start job failed. com.spotfire.ws.dat.OperatorException[faultCode=SOAP-ENV:Server.SQLFailure; faultString=Failed to execute query: Ran out of memory retrieving query results.; message=com.spotfire.ws.im.IMException: Failed to execute query: Ran out of memory retrieving query results.;The resolution to this is to edit the data source template and return the SQL runtime section to start streaming again. Alternatively, although not recommended, if this runtime section was removed on purpose, increase the JvmMx max memory to accommodate the entire data set.
Metadata Validation
Validation of metadata is another step which can consume a lot of time during Information Link execution, when the data source provides the metadata slower than expected by the TIBCO Spotfire Server information-services cache lifetime. The metadata validation cache lifetime is 5 seconds, so if poor database performance results in the validation taking longer than 5 seconds, it will cause Spotfire to re-validate items of the expired cache. This can lead to long execution times.To resolve, the metadata cache time-out can be increased to a larger value like 60 seconds. To see additional details about this situation and detailed resolution steps, see KB 000019997 Creating new or re-saving existing Information Models and elements take longer than expected. This is a generally recommended configuration.
Runtime query validation
Alternatively, only if required, the runtime query validation can be disabled per KB 000022572 When clicking "Edit SQL" on an Information Link in Information Designer it takes a long time before it allows editing of the Information Link's SQL queryCase 3. Time is equal for execution time, but a lot of time is spent before closing job
In this case, the "executed" time may be quick, but the "job closed" time is significantly longer than the "executed" time indicating there is a lot of time spent in the transfer of the data from the TSS to the client. Here are a few steps to take:
Verify resource utilization
Always begin by checking the TIBCO Spotfire Server health and resource utilization:- Is the overall server utilization approaching 100% CPU or 85% RAM? If so, it is likely a resource limitation that is causing issues. Consider increasing the system resources to accommodate the increased load.
- Is the Tomcat process (tomcat7.exe or tomcat8.exe) RAM utilization pegged at the java heap max setting limit (this is defined in the JvmMx parameter, see reference Modifying the virtual memory (server running as Windows service)? If so, the JvmMx may need to be increased to accommodate the size of data being transferred through the TSS.
Slow network performance
More commonly however this is caused by network performance issue between the TIBCO Spotfire Server and the installed client or Node Manager. To check, look for the following message in server.log:DEBUG 2016-06-08T02:08:12,139-0500 [*pool-3-thread-57, user, #10300*] ws.dat.Job$GetDataTask: Waiting for up to 1 more second for data block to be consumed from full data block queueThis log entry is seen when data blocks get created with data on the TSS, and the TSS is waiting for the client to read them before they can get purged. When nothing reads them, and the TSS keeps creating new data blocks as data is read from the database, the limit of data blocks per job is hit. It does this until the queue has free spots, or until it times out.
To help avoid this issue, the data-block-queue-size can be increased to let the TSS hold more data blocks in the queue. For details see KB 000028923 Information Link execution takes longer than expected to close due to full data block queue.
HTTP Transfer Speeds
A test can be performed to see if issue only appears when sending the data over the HTTP protocol.- Save the analysis with all data embedded
- Copy the dxp file to the TIBCO Spotfire Server machine to a shared folder (for example, accessible via \\servername\myfolder\myanalysis.dxp)
- From the client machine, copy the file with File Explorer via \\servername\myfolder\myanalysis.dxp. Note the time to complete the file transfer.
- Copy the dxp file on the TIBCO Spotfire Server machine to a folder on the tomcat web server (for example, \tomcat\webapps\ROOT\)
- From the client machine, copy the file from a browser via its URL like http:\\servername\myanalysis.dxp
If the HTTP transfer is significantly slower, then contact your network administrator for assistance in investigating and improving the performance.
Diagnostic test to isolate network performance between server and client machines
Another diagnostic test that can be performed can help eliminate the TSS to Client network from the equation of possible causes. First, install the TIBCO Spotfire Analyst client on the TIBCO Spotfire Server machine, or copy the TIBCO Spotfire Portable client there. Launch the client and authenticate using http://localhost/ as the server URL. Open the report or Information Link and note the time to load. If the report loads quickly, then the issue is with the network connection between the TSS and the client machines. Contact your network administrator for assistance in investigating and improving the performance. If the report still loads slowly, then the network connection between the TSS and the client machines can be removed as a possible cause.
Case 4. Time of query is equal, including job closure, but start of job on server is delayed
In this case, the "executed" and "job closed" time from the Spotfire sql.log are approximately the same as the execution time seen in the third party tool test, but the delay is because the execution of the Information Link is not started immediately. An easy way to see if this is the case, is to look at the library.log file, and see if you see the following entries repeated for the duration of time it takes before the query is executed:10.40.0.220;;user;2016-08-10T20:21:12,366-0400;Content loaded;reportName;spotfire.dxp;19223314;e6a54d5f-08f6-426c-a6d9-224a13a85492 ;;;2016-08-10T20:21:26,162-0400;Metadata loaded;sourceName;spotfire.datasource;1056;8f1f7096-458e-4511-9c11-487ccef249cc ;;;2016-08-10T20:22:26,161-0400;Metadata loaded;sourceName;spotfire.datasource;1056;8f1f7096-458e-4511-9c11-487ccef249ccPossible solutions for this:
- Verify the connectivity between the TIBCO Spotfire Server application database server and the TIBCO Spotfire Server machine. Ensure there are no network communication issues.
- Verify the driver used by the TIBCO Spotfire Server to connect to database. For production use, it is recommended to use the native Microsoft SQL or Oracle drivers, not the TIBCO Data Direct drivers.
- Involve your database administrator to investigate why it takes a long time to read the library content metadata from the TIBCO Spotfire Server application database