-
Bug
-
Resolution: Fixed
-
Low
-
4.20.10, 5.3.1, 5.12.4
-
12
-
Severity 2 - Major
-
17
-
Issue Summary
Currently Insight queries all attributes, regardless whether they're made visible/searchable in the Insight Custom Fields. This would not be an issue if all the attributes of the Object Type are indexed and hence loaded in the Insight Index in memory (heap).
However, when there are some attributes which are not indexed, it can create a database bottleneck because Insight would then fetch them from the DB. With the number of objects for the Object Type and the number of un-indexed attributes, the number of times Insight runs the SELECT query can cause simple UI functions like Creating/Updating an issue with Insight Custom Fields take a long time.
This is reproducible on Data Center: yes
Steps to Reproduce
- Add a bunch of attributes for an object type:
Focus on attributes which not TextType (never indexed) or Object Reference (always indexed)
- Load a Jira issue while keeping SQL logging on
Expected Results
Insight queries on the attributes made visible on the Insight Custom Field configuration (Object attributes on Issue view) and Jira UI actions like Issue Create/update are not slow.
Actual Results
Jira fetches all attributes for the Object type in Insight Custom Field context. In SQL logs atlassian-jira-sql.log you'll see the following query ran thousands of times:
....
"SELECT ID,OBJECT_ATTRIBUTE_ID,ADDITIONAL_VALUE,BOOLEAN_VALUE,DATE_VALUE,DOUBLE_VALUE,INTEGER_VALUE,REFERENCED_OBJECT_ID,TEXT_VALUE FROM dbo.AO_8542F1_IFJ_OBJ_ATTR_VAL WHERE OBJECT_ATTRIBUTE_ID = ? ORDER BY ID ASC" {1=3703166}
....
If there are a lot of objects for the object type this creates a DB bottleneck causing Issue Load/edit time very slow as it runs thousands of SQL queries (for un-indexed attributes):
Thread dumps reveal threads waiting on DB:
"https-jsse-nio-8443-exec-12 url: /browse/XXXXXX-12345, /secure/ProjectIssueNavigatorAction!issueViewWithSidebar.jspa; user: xxx.yyyy" #1232 daemon prio=5 os_prio=0 cpu=1417654.25ms elapsed=274593.51s tid=0x00007f8662dac800 nid=0x673e runnable [0x00007f78c985c000] java.lang.Thread.State: RUNNABLE at java.net.SocketInputStream.socketRead0(java.base@11.0.15/Native Method) at java.net.SocketInputStream.socketRead(java.base@11.0.15/SocketInputStream.java:115) at java.net.SocketInputStream.read(java.base@11.0.15/SocketInputStream.java:168) at java.net.SocketInputStream.read(java.base@11.0.15/SocketInputStream.java:140) at com.microsoft.sqlserver.jdbc.TDSChannel.read(IOBuffer.java:2058) at com.microsoft.sqlserver.jdbc.TDSReader.readPacket(IOBuffer.java:6617) - locked <0x00007f84db9238b0> (a com.microsoft.sqlserver.jdbc.TDSReader) at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7805) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$CursorFetchCommand.doExecute(SQLServerResultSet.java:5493) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7418) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3272) - locked <0x00007f84aeddafc0> (a java.lang.Object) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.doServerFetch(SQLServerResultSet.java:5530) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.moveFirst(SQLServerResultSet.java:1413) [...trimmed..] at com.riadalabs.jira.plugins.insight.services.core.dal.dao.impl.InsightActiveObject.stream(InsightActiveObject.java:112) at com.riadalabs.jira.plugins.insight.services.core.dal.dao.impl.ObjectAttributeValueDaoImpl.findStreamObjectAttributeValuesFromObjectAttributeId(ObjectAttributeValueDaoImpl.java:113) at com.riadalabs.jira.plugins.insight.services.core.dal.impl.ObjectAttributeDalImpl.findObjectAttributeBeans(ObjectAttributeDalImpl.java:388) at com.riadalabs.jira.plugins.insight.services.core.ObjectManagerInJira.loadObject(ObjectManagerInJira.java:91) at com.riadalabs.jira.plugins.insight.services.core.IQLServiceImpl.lambda$findObjects$0(IQLServiceImpl.java:68) at com.riadalabs.jira.plugins.insight.services.core.IQLServiceImpl$$Lambda$12032/0x00007f78a5095cb0.apply(Unknown Source)
For example, here's an user <USER> who tried QuickCreateIssue and we run the prepared statement in the findStreamObjectAttributeValuesFromObjectAttributeId method 4255 times:
$ grep 'AO_8542F1_IFJ_OBJ_ATTR_VAL WHERE OBJECT_ATTRIBUTE_ID = ? ORDER BY ID ASC' atlassian-jira-sql.log.4 | grep '<USER>' | grep QuickCreate | awk -F'ms' '{print $2}' | sort -u | wc -l
4255
Couple of things to remember about Insight Index:
-
- By default Insight index for all attributes will be enabled (it is needed to be able to run the IQL queries faster) EXCEPT of TextArea attribute.
- Key, Label, Created, Updated and Object Type Attributes of type Object Reference are always indexed and can not be switched off.
Workaround
Make sure to have all attributes indexed for object types. We can get the Attributes which are not indexed per Object Schema with this SQL query:
SELECT * FROM "AO_8542F1_IFJ_OBJ_TYPE_ATTR" OTA LEFT OUTER JOIN "AO_8542F1_IFJ_OBJ_TYPE" OT ON OT."ID" = OTA."OBJECT_TYPE_ID" WHERE OT."OBJECT_SCHEMA_ID" = '<SCHEMA-ID>' AND OTA."INDEXED" IS NOT TRUE;
| Form Name | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
We have the same issue, this was fine before upgrading to v9.3.1 but now it looks terrible to have millions of attributes shown on issue view screens. Makes Jira issue views useless as we have to scroll to infinity and beyond...
Need immediate fix!
Thanks