'entity_property' table is slow with high number of rows and under high load

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

Workaround

Identify table entity_property data distribution and delete some data if possible:

1. Look for count of entities on the table, grouped by entity_name:

   SELECT entity_name,count(id) from entity_property group by entity_name;
   

2. 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.