Geo Framework: Persistent failures block
requested to merge 293972-geo-framework-persistent-failures-block-slow-down-retries-of-transient-failures into master
What does this MR do?
We have a lot of items that have retry_count > 40
because they stuck in a queue due to the fact that we don't order them when fetching. This MR adds the order by retry_at
field that has an index in all the cases.
Example query plan
gitlabhq_geo_development=# explain analyze SELECT "package_file_registry".* FROM "package_file_registry" WHERE ("package_file_registry"."state" IN (3)) AND ("package_file_registry"."verification_retry_at" IS NULL OR "package_file_registry"."verification_retry_at" < '2021-02-18 19:59:32.066340') ORDER BY "package_file_registry"."retry_at" ASC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=38817.72..48701.01 rows=84708 width=1167) (actual time=397.213..589.488 rows=950000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=37817.69..37923.58 rows=42354 width=1167) (actual time=388.354..418.620 rows=316667 loops=3)
Sort Key: retry_at
Sort Method: external merge Disk: 14272kB
Worker 0: Sort Method: external merge Disk: 13456kB
Worker 1: Sort Method: external merge Disk: 13352kB
-> Parallel Index Scan using index_package_file_registry_on_state on package_file_registry (cost=0.43..12992.24 rows=42354 width=1167) (actual time=0.050..273.559 rows=316667 loops=3)
Index Cond: (state = 3)
Filter: ((verification_retry_at IS NULL) OR (verification_retry_at < '2021-02-18 19:59:32.06634+02'::timestamp with time zone))
Planning Time: 8.983 ms
Execution Time: 637.814 ms
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec
-
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Related to #293972 (closed)
Edited by Michael Kozono