Skip to content

Count pipelines that have security jobs

Can Eldem requested to merge count-security-pipelines into master

What does this MR do?

With the upcoming removal of DinD &2462 (closed) for security products and the orchestration layer for SAST and Dependency Scanning, we will no longer have the sast and dependency_scanning jobs running in the pipeline, but instead will have 1 job per analyzer, with a different name.

This MR counts number of pipelines that have security jobs. I put a simplified model of the tables to represent how query works.

image

In example above, we have one pipeline running two dependency scanning analysers.

Related issue

#211621 (closed)

Queries

SELECT MAX("ci_pipelines"."id") FROM "ci_pipelines"

https://explain.depesz.com/s/6smu

Cold cache

Time: 13.392 ms
  - planning: 0.491 ms
  - execution: 12.901 ms
    - I/O read: 12.673 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 6 (~48.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 1 (~8.00 KiB)
  - writes: 0

Warm cache

Time: 1.148 ms
  - planning: 0.434 ms
  - execution: 0.714 ms
    - I/O read: 0.574 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1 (~8.00 KiB) from the buffer pool
  - reads: 4 (~32.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0
SELECT MIN("ci_pipelines"."id") FROM "ci_pipelines"

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

Cold cache

Time: 7.769 ms
  - planning: 0.311 ms
  - execution: 7.458 ms
    - I/O read: 7.302 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 5 (~40.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm cache

Time: 0.669 ms
  - planning: 0.538 ms
  - execution: 0.131 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 6 (~48.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Counting pipelines for dependency scanning

SELECT Count(DISTINCT "ci_builds"."commit_id") 
FROM   "ci_builds" 
       INNER JOIN "security_scans" 
               ON "security_scans"."build_id" = "ci_builds"."id" 
WHERE  "ci_builds"."type" = 'Ci::Build' 
       AND "ci_builds"."status" = 'success' 
       AND ( "ci_builds"."retried" = false 
              OR "ci_builds"."retried" IS NULL ) 
       AND ( security_scans.scan_type = 2 ) 
       AND "ci_builds"."created_at" BETWEEN '2020-06-25 12:12:35.535725' AND 
                                            '2020-07-23 12:12:35.536078' 
       AND "ci_builds"."commit_id" BETWEEN 0 AND 9999

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

Cold cache

Time: 462.206 ms
  - planning: 1.277 ms
  - execution: 460.929 ms
    - I/O read: 457.532 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 9 (~72.00 KiB) from the buffer pool
  - reads: 232 (~1.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm cache

Time: 4.133 ms
  - planning: 1.816 ms
  - execution: 2.317 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 232 (~1.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Counting pipelines for sast

same query as above only difference is that scan_type is = 1

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

Cold cache

Time: 415.861 ms
  - planning: 1.059 ms
  - execution: 414.802 ms
    - I/O read: 410.374 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 232 (~1.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm cache

Time: 2.543 ms
  - planning: 0.870 ms
  - execution: 1.673 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 232 (~1.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Counting pipelines for container scanning (scan_type = 3)

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

Cold Cache


Time: 513.824 ms
  - planning: 1.335 ms
  - execution: 512.489 ms
    - I/O read: 509.230 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 9 (~72.00 KiB) from the buffer pool
  - reads: 232 (~1.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm Cache

Time: 3.881 ms
  - planning: 1.520 ms
  - execution: 2.361 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 232 (~1.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Counting pipelines for dast (scan_type = 4)

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

Cold Cache

Time: 413.824 ms
  - planning: 1.335 ms
  - execution: 412.489 ms
    - I/O read: 409.230 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 9 (~72.00 KiB) from the buffer pool
  - reads: 232 (~1.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm cache

Time: 2.929 ms

  • planning: 1.076 ms
  • execution: 1.853 ms
    • I/O read: 0.000 ms
    • I/O write: 0.000 ms

Shared buffers:

  • hits: 232 (~1.80 MiB) from the buffer pool
  • reads: 0 from the OS file cache, including disk I/O
  • dirtied: 0
  • writes: 0

Counting pipelines for secret detection (scan_type = 5)

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

Cold cache

Time: 414.861 ms
  - planning: 1.059 ms
  - execution: 413.802 ms
    - I/O read: 410.374 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 232 (~1.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Warm Cache

Time: 3.105 ms
  - planning: 1.051 ms
  - execution: 2.054 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 232 (~1.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Counting pipelines for fuzzing (scan_type = 6)

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

Cold cache

Time: 384.009 ms
  - planning: 1.077 ms
  - execution: 382.932 ms
    - I/O read: 359.736 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 81 (~648.00 KiB) from the buffer pool
  - reads: 138 (~1.10 MiB) from the OS file cache, including disk I/O
  - dirtied: 20 (~160.00 KiB)
  - writes: 0

Warm cache

Time: 1.864 ms
  - planning: 1.152 ms
  - execution: 0.712 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 177 (~1.40 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Screenshots

Does this MR meet the acceptance criteria?

Conformity

Edited by Can Eldem

Merge request reports