Optimize RemoveExpiredMembersWorker
What does this MR do and why?
When there are many expired memberships, the query used by RemoveExpiredMembersWorker can time out, causing no expired memberships to be removed. Since the job only runs once daily, this could potentially be a self-compounding problem as more expired memberships build up.
This change removes the find_each
, which causes Rails to use batched queries. This means it does an initial count query, then queries with limit
and offset
clauses to fetch the actual records. These queries all have an intermediate stage where the results are reordered by the primary key id
column, meaning the limit
has to be applied after all records are counted and reordered. If we can eliminate this reorder step, the limit
can be applied during the index scan, aborting the scan once the limit is reached. This may reduce the real time taken by the query.
The potential drawback is if we have more than 1,000 or more memberships which cannot be removed, due to being the last member in a group. Then the worker will not go any further down the queue to get additional memberships. This problem should be mitigated by using order by expires_at desc
, so it will always be cleaning up the most recently expired memberships.
This optimization is in response to the issue we had last week with a large influx of expired memberships.
Database Review
Query plan before this change
Query plan after this change
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Before | After |
---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
- Run
RemoveExpiredMembersWorker
on the rails console RemoveExpiredMembersWorker.new.perform
Related to #470686 (closed)