Skip to content

Reschedules migration for uuid recalculation

Subashis Chakraborty requested to merge 212322-recalculate-uuids into master

What does this MR do?

This mr reschedules RecalculateVulnerabilitiesOccurrencesUuid background migration since we still have some UUIDv4 records: 121537

related to this MR !47529 (merged)

This mr also adds a log message to make sure, the migration runs properly.

Query details

rails db:migrate

== 20210426225417 ScheduleRecalculateUuidOnVulnerabilitiesOccurrences2: migrating 
-- Scheduling RecalculateVulnerabilitiesOccurrencesUuid jobs
-- Scheduled 1 RecalculateVulnerabilitiesOccurrencesUuid jobs with a maximum of 2500 records per batch and an interval of 120 seconds.

The migration is expected to take at least 120 seconds. Expect all jobs to have completed after 2021-05-04 16:34:19 UTC."
== 20210426225417 ScheduleRecalculateUuidOnVulnerabilitiesOccurrences2: migrated (0.0542s) 

Estimated execution time

Records in table: 8_905_040
Records to recalculate: 121537 – https://gitlab.slack.com/archives/CLJMDRD8C/p1620668684228300
Batch size: 2_500
Batches: 3562
Delay: 120s

(3562*2) + (3562*120) = 434564 seconds ~= 120 hours ~= 5 days

Why we choose Batch Size (2_500)?

!60540 (comment 574839026)

Do we need to iterate over all vulnerability_occurrences?

!60540 (comment 574649270)

How many records are going to be affected by this migration?

!60540 (comment 574650817)

Modifying one record using BulkImport

This is copied from from the previous MR

Execution plan and timings
 ModifyTable on public.vulnerability_occurrences  (cost=0.44..3.48 rows=1 width=1304) (actual time=56.287..56.291 rows=0 loops=1)
   Buffers: shared hit=25 read=39 dirtied=11
   I/O Timings: read=55.419
   CTE cte
     ->  Result  (cost=0.00..0.01 rows=1 width=98) (actual time=0.002..0.003 rows=1 loops=1)
   ->  Nested Loop  (cost=0.43..3.47 rows=1 width=1304) (actual time=7.115..7.121 rows=1 loops=1)
         Buffers: shared read=4
         I/O Timings: read=7.047
         ->  CTE Scan on cte  (cost=0.00..0.02 rows=1 width=220) (actual time=0.018..0.021 rows=1 loops=1)
         ->  Index Scan using vulnerability_occurrences_pkey on public.vulnerability_occurrences  (cost=0.43..3.45 rows=1 width=1092) (actual time=7.092..7.093 rows=1 loops=1)
               Index Cond: (vulnerability_occurrences.id = cte.cte_id)
               Buffers: shared read=4
               I/O Timings: read=7.047
Time: 56.743 ms
  - planning: 0.332 ms
  - execution: 56.411 ms
    - I/O read: 55.419 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 25 (~200.00 KiB) from the buffer pool
  - reads: 39 (~312.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 11 (~88.00 KiB)
  - writes: 0

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

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 #212322 (closed)

Edited by Michał Zając

Merge request reports

Loading