Changes for optimizing expiring worker
What does this MR do and why?
Solves #432518 (closed)
Changes for optimizing expiring worker
We are reducing batch size of expiring worker and querying PersonalAccessTokens table instead of User table since indexing is coming to work and query execution is much faster
Changelog: changed
Screenshots or screen recordings
For OLD User Query with batch size 1000 and no filter
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25991/commands/81812
Time: 22.472 s
- planning: 1.396 s
- execution: 21.077 s
- I/O read: 20.620 s
- I/O write: 0.000 msShared buffers:
- hits: 2099 (~16.40 MiB) from the buffer pool
- reads: 19315 (~150.90 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Query Execution after changes for Personal Access Token LOOP execution
SELECT "personal_access_tokens"."user_id" FROM "personal_access_tokens" WHERE "personal_access_tokens"."impersonation" = FALSE AND (revoked = false AND expire_notification_delivered = false AND expires_at >= CURRENT_DATE AND expires_at <= '2024-02-15') LIMIT 100
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26068/commands/82124
Runtime -
Time: 108.510 ms
- planning: 1.806 ms
- execution: 106.704 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 7161 (~55.90 MiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Query plan for internal loop where we are fetching all the tokens for related user
SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."user_id" = 1 AND "personal_access_tokens"."impersonation" = FALSE AND (revoked = false AND expire_notification_delivered = false AND expires_at >= CURRENT_DATE AND expires_at <= '2024-02-15')
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26068/commands/82082
Time: 12.135 ms
- planning: 2.392 ms
- execution: 9.743 ms
- I/O read: 9.541 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 0 from the buffer pool
- reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Query plan for finding users with user_ids
Number of user ids can be maximum 100 in this query
SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 70, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110)
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26068/commands/82125
Time: 555.302 ms
- planning: 456.330 ms
- execution: 98.972 ms
- I/O read: 97.979 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 123 (~984.00 KiB) from the buffer pool
- reads: 25 (~200.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Query plan for update all query
UPDATE "personal_access_tokens" SET "expire_notification_delivered" = TRUE WHERE "personal_access_tokens"."user_id" = 1 AND "personal_access_tokens"."impersonation" = FALSE AND (revoked = false AND expire_notification_delivered = false AND expires_at >= CURRENT_DATE AND expires_at <= '2024-02-15')
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26068/commands/82083
Time: 2.056 ms
- planning: 1.957 ms
- execution: 0.099 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 3 (~24.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
Before | After |
---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.