Skip to content

Migration to correct the data inconsistency regarding vulnerability state

Subashis Chakraborty requested to merge 364356-db-migration into master

This MR introduces a database migration to correct the state of the vulnerability state. It checks the dismissed_at value, and if set, the state should be DISMISSED.

Related to #364356 (closed)

Database Migration

Up 
bundle exec rails db:migrate                                 
main: == 20220630151641 ScheduleSetCorrectVulnerabilityState: migrating =============
main: == 20220630151641 ScheduleSetCorrectVulnerabilityState: migrated (0.1387s) ====
Down 
bundle exec rails db:migrate:down:main VERSION=20220630151641
main: == 20220630151641 ScheduleSetCorrectVulnerabilityState: reverting =============
main: == 20220630151641 ScheduleSetCorrectVulnerabilityState: reverted (0.0479s) ====

Query Analysis

Query 
UPDATE vulnerabilities 
SET state=2 
WHERE dismissed_at is not null
AND vulnerabilities.id BETWEEN 100000 AND 101000

Analysis: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/10982/commands/39457

Time: 270.245 ms
  - planning: 6.346 ms
  - execution: 263.899 ms
    - I/O read: 228.984 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 6466 (~50.50 MiB) from the buffer pool
  - reads: 702 (~5.50 MiB) from the OS file cache, including disk I/O
  - dirtied: 387 (~3.00 MiB)
  - writes: 7 (~56.00 KiB)

Time calculation for background migration

  • Total number of records in vulnerabilities: 45903344
  • Batch size: 10_000
  • Sub-batch size: 500
  • Total no. of batches: 45903344/10_000 = 4590
  • Estimated time per batch:
    • No. of updates per sub-batch: 20
    • 270.245 ms for updating with 500 items
    • Sub-batch total: ~0.27 s per sub-batch
    • Total batch time: .27 * 20 = 5.4 sec per batch
  • Time interval: 2 minutes
  • Total Time: 4590 * 2.09 ~= 9593 minutes ~= 159.885 hours ~= 6.66 days
Edited by Subashis Chakraborty

Merge request reports