Details
-
Bug
-
Resolution: Fixed
-
Highest
-
6.4.14, 7.2.8, 7.3.1, 7.4.1, 7.5.0, 7.7.4, 8.4.2
-
6.04
-
123
-
Severity 2 - Major
-
1,698
-
-
Description
NOTE: This suggestion is for JIRA Server. Using JIRA Cloud? See the corresponding suggestion.
Problem Summary
The entity_property table was designed to be generic, and we provide API and encourage vendors to use it to store any kind of data. Given there is no policy for expiring data in there, and with many datasources it can reach millions of rows easily, we need to look for solutions to optimize it.
Expected Behavior
Have minimal impact in performance regardless of amount of data stored in the table.
Actual Behavior
When this table reaches millions of rows, and many classes use it, querying it will be slow and can impact performance of the whole database.
Steps to Reproduce
Use DVCS integration in an instance with more than 1 million issues - see JSW-15748.
Notes
From existing cases we identified following expensive SQLs for entity_property:
SELECT ID, UPDATED FROM entity_property WHERE ENTITY_NAME=:1 AND ENTITY_ID=:2 AND PROPERTY_KEY=:3
-
- Problem - no cover index
SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM entity_property WHERE ENTITY_NAME=:1 AND ENTITY_ID=:2 AND PROPERTY_KEY=:3
-
- Problem - no cover index
SELECT COUNT(*) FROM entity_property WHERE ENTITY_NAME=:1 AND PROPERTY_KEY=:2
-
- Problem - partial index:
- Unnecessary COUNT
SELECT ID, ENTITY_NAME, ENTITY_ID, PROPERTY_KEY, CREATED, UPDATED, json_value FROM dbo.entity_property WHERE (ENTITY_NAME='IssueProperty' AND ENTITY_ID='680519') AND (PROPERTY_KEY IN ('development') ) ORDER BY UPDATED, ID"
-
- Problem - no cover index, ORDER without index
Workaround
Delete stale cache data from entity_property
Identify table entity_property data distribution and delete some data if possible:
- Look for count of entities on the table, grouped by entity_name:
SELECT entity_name,count(id) from entity_property group by entity_name;
- If fusion.caches.issue is one of the entity_name with higher counts, purge entries not updated for more than 30 days (see
JSW-15748):DELETE FROM entity_property WHERE ENTITY_NAME = 'fusion.caches.issue' AND UPDATED < '<date-30d>';
You can use DBMS' specific functions for date intervals or simply replace <date-30d with a date 30 days before now.
Replace an index on entity_property
For MS SQL Server:
DROP INDEX ENTITYPROPERTY_ENTITY ON dbo.ENTITY_PROPERTY; CREATE INDEX ENTITYPROPERTY_ENTITY ON dbo.ENTITY_PROPERTY(entity_id, entity_name) INCLUDE(property_key);
Other databases + MS SQL Server 2016+:
DROP INDEX ENTITYPROPERTY_ENTITY; CREATE INDEX ENTITYPROPERTY_ENTITY ON ENTITY_PROPERTY(entity_id, entity_name, property_key);
Attachments
Issue Links
- is related to
-
-
- Closed
-
-
JRASERVER-38825 Improve Database Indexes to the PROPERTYENTRY Table
- Closed
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
CRANE-915 Loading...
-
- was cloned as
-
-
- Gathering Impact
-
- is blocked by
-
- Mentioned in
- mentioned in
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-