Adds delete_limit of 100 for ci_pipelines deletions from LFK woker
What does this MR do and why?
There has been consistent statement timeouts on performing LFK cleanup for merge_requests on ci_pipelines - log.
Most of the time are spent on cascading the ci_pipelines deletes to other tables. @ahegyi has taken out an instance (query plan) of this in gprd, where the cascade operations took more than 143.5 seconds.
we don't want to add more indexes on this table due to size and also all the rows from a partition have the same
partition_idvalue.source: #565940 (comment 2729405992)
Since it'd be costly to add indexes to make the cascade operations performant, this MR adds a delete_limit only for ci_pipelines async delete from merge_requests from LFK::DeleteRecord.
Hoping this should drain the existing merge_requests from loose_foreign_keys_deleted_records.
References
For more information please refer #565940 (comment 2714273532)
Screenshots or screen recordings
| Before | After |
|---|---|
How to set up and validate locally
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.