Skip to content

Migrate license_management artifacts to license_scanning

Tetiana Chupryna requested to merge 213571-migrate-lm-to-ls-artifacts into master

What does this MR do?

We removed support for license_management artifacts in favor of license_scanning. We need to migrate job_artifact.file_type so everything that we store in the db will be of the same file type.

Issue #213571 (closed)

Migration

Up

➜ rails db:migrate == 20200808221641 AddIndexForLicenseComplianceArtifacts: migrating ============ -- transaction_open?() -> 0.0000s -- index_exists?(:ci_job_artifacts, [:job_id, :file_type], {:where=>"file_type = 10 OR file_type = 101", :name=>"index_ci_job_artifacts_on_license_compliance_file_types", :algorithm=>:concurrently}) -> 0.0046s -- add_index(:ci_job_artifacts, [:job_id, :file_type], {:where=>"file_type = 10 OR file_type = 101", :name=>"index_ci_job_artifacts_on_license_compliance_file_types", :algorithm=>:concurrently}) -> 0.0057s == 20200808221641 AddIndexForLicenseComplianceArtifacts: migrated (0.0107s) === == 20200809221641 MigrateLicenseManagementArtifactsToLicenseScanning: migrating == 20200809221641 MigrateLicenseManagementArtifactsToLicenseScanning: migrated (0.0296s)

Down

➜ rails db:rollback == 20200809221641 MigrateLicenseManagementArtifactsToLicenseScanning: reverting == 20200809221641 MigrateLicenseManagementArtifactsToLicenseScanning: reverted (0.0000s) == 20200808221641 AddIndexForLicenseComplianceArtifacts: reverting ============ -- transaction_open?() -> 0.0000s -- indexes(:ci_job_artifacts) -> 0.0056s -- remove_index(:ci_job_artifacts, {:algorithm=>:concurrently, :name=>"index_ci_job_artifacts_on_license_compliance_file_types"}) -> 0.0024s == 20200808221641 AddIndexForLicenseComplianceArtifacts: reverted (0.0085s) ===

Queries

CREATE INDEX index_ci_job_artifacts_on_file_type ON public.ci_job_artifacts USING btree (file_type) WHERE (file_type = ANY (ARRAY[10, 101]));

The query has been executed. Duration: 38.648 min

explain WITH ci_job_artifacts_with_row_number as (SELECT job_id, id, ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY id ASC) as row_number FROM ci_job_artifacts WHERE (file_type = 101 OR file_type = 10) AND job_id >= 673784020 AND job_id < 674784020) DELETE FROM ci_job_artifacts WHERE ci_job_artifacts.id IN (SELECT id from ci_job_artifacts_with_row_number WHERE ci_job_artifacts_with_row_number.row_number > 1)

In batches of 1000

(time for 1 batch)

Time: 950.508 ms

  • planning: 0.600 ms
  • execution: 949.908 ms
    • I/O read: 934.132 ms
    • I/O write: 0.000 ms

Shared buffers:

  • hits: 15 (~120.00 KiB) from the buffer pool
  • reads: 1120 (~8.80 MiB) from the OS file cache, including disk I/O
  • dirtied: 496 (~3.90 MiB)
  • writes: 0

https://explain.depesz.com/s/hmnp

Approx time for whole query: ~5 min

UPDATE "ci_job_artifacts" SET "file_type" = 101 WHERE "ci_job_artifacts"."file_type" = 10 In batches of 1000

Without batches:

https://explain.depesz.com/s/LpPm

Update on ci_job_artifacts  (cost=0.57..8409160.75 rows=146454 width=137)
  ->  Index Scan using index_ci_job_artifacts_on_job_id_and_file_type on ci_job_artifacts  (cost=0.57..8409160.75 rows=146454 width=137)
        Index Cond: (file_type = 10)

Summary:

Time: 48.968 min
  - planning: 0.213 ms
  - execution: 48.968 min
    - I/O read: 47.563 min
    - I/O write: 7.931 s

Shared buffers:
  - hits: 3275778 (~25.00 GiB) from the buffer pool
  - reads: 3160736 (~24.10 GiB) from the OS file cache, including disk I/O
  - dirtied: 524020 (~4.00 GiB)
  - writes: 44241 (~345.60 MiB)

Screenshots

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
Edited by Tetiana Chupryna

Merge request reports