Add a new DB index for AGS Destroy action

Description

DESCRIPTION

AGS-3.3.0.4
MySQL 5.7

During a customer investigation, we found one of the queries used by the destroy action in the retention schedule was running very slowly, taking around 50 minutes, to process each record.

INVESTIGATION
Debug logging has big gaps between each node being processed.

Thread dumps showed the thread DefaultScheduler_Worker-5 waiting in socketRead0 against the database for this class

The SQL query we’re waiting on is

Their DBA collected a query plan showing the query was doing an index scan on the alf_node_properties table holding 600m rows.

This matches what I’m seeing in my test environment with 1.4 million rows

Their observation is that our TMDQ index ‘idx_alf_nprop_l' can’t be used here because its leftmost column is qname_id and not long_value.

After creating an index on alf_node_properties.long_value, I get the following query plan

The left outer join on (p.long_value = cd.id) reads 12 rows using the new index, instead of 1444415 with the full index scan.

It seems unlikely that we could or should alter the TMDQ index, so should we look at a new RM/AGS index to support the destroy query?

https://github.com/Alfresco/governance-services/blob/14f27e366939bac368b0dd669fcca3936f640664/rm-community/rm-community-repo/config/alfresco/module/org_alfresco_module_rm/query/rm-common-SqlMap.xml#L56

Environment

MySQL 5.7

Testcase ID

None

Activity

Show:
Mark Tunmer
March 29, 2021, 11:08 AM

feedback from the customer is that after rebuilding the indexes on the affected tables, they did not actually create this index and performance was still acceptable.

Assignee

Unassigned

Reporter

Mark Tunmer

Labels

None

Escalated By

None

Security Issue

None

ACT Numbers

01014227

Premier Customer

None

Code Branch

None

Build Location

None

Patch Attached

None

Dependent Version/s

None

Cloud or Enterprise

None

Prioritization Score

None

Bug Priority

Category 2

Delivery Team

None

Components

Fix versions

Affects versions