Remove `[analyzer]_pipeline` metrics join on ci_builds"
What does this MR do and why?
[analyzer]_pipeline
service ping metrics are sent for every CI job that contains a scan, not just for those that are part of successful non-retried jobs. This is an attempt to reduce the load on the database.
Related to #338010 (closed)
We took similar approach in this MR.
Impact to the database
Query
Before:
SELECT
"security_scans".*
FROM
"security_scans"
INNER JOIN "ci_builds" ON "ci_builds"."id" = "security_scans"."build_id"
AND "ci_builds"."type" = 'Ci::Build'
WHERE
"ci_builds"."status" = 'success'
AND (
"ci_builds"."retried" = FALSE
OR "ci_builds"."retried" IS NULL
)
AND "security_scans"."scan_type" = 1
AND "security_scans"."created_at" BETWEEN '2021-09-27 19:55:50.275002'
AND '2021-09-29 19:55:45.806409'
The explain plan generated from #database_lab
can be found at the following location https://explain.depesz.com/s/yE1T
After
A query like the following will be made for each scan type (of which there are nine).
SELECT
"security_scans".*
FROM
"security_scans"
WHERE
"security_scans"."scan_type" = 1
AND "security_scans"."created_at" BETWEEN '2021-09-27 19:55:50.275002'
AND '2021-09-29 19:55:45.806409'
This is an expected improvement on the previous query as it doesn't have to join with the ci_builds
table and filter by the latest successful build.
The new query searches using the index on scan_type
. This query is run against scan_type
1
(SAST), which accounts for the vast majority of Security Scans.
The explain plan generated from #database_lab
can be found at the following location https://explain.depesz.com/s/pzXV.
Merge request reports
Activity
changed milestone to %14.4
assigned to @subashis
- A deleted user
added databasereview pending label
- A deleted user
added product intelligence product intelligencereview pending labels
- A deleted user
added database documentation labels
1 Warning For the following files, a review from the Data team and Product Intelligence team is recommended
Please check the product intelligence guide.ee/lib/ee/gitlab/usage_data.rb
ee/spec/lib/ee/gitlab/usage_data_spec.rb
1 Message This merge request adds or changes documentation files. A review from the Technical Writing team before you merge is recommended. Reviews can happen after you merge. Documentation review
The following files require a review from a technical writer:
doc/development/service_ping/implement.md
The review does not need to block merging this merge request. See the:
-
Metadata for the
*.md
files that you've changed. The first few lines of each*.md
file identify the stage and group most closely associated with your docs change. - The Technical Writer assigned for that stage and group.
- Documentation workflows for information on when to assign a merge request for review.
Reviewer roulette
Changes that require review have been detected!
Please refer to the table below for assigning reviewers and maintainers suggested by Danger in the specified category:
Category Reviewer Maintainer backend Marius Bobin ( @mbobin
) (UTC+3, 9 hours ahead of@subashis
)Aleksei Lipniagov ( @alipniagov
) (UTC+3, 9 hours ahead of@subashis
)database Simon Tomlinson ( @stomlinson
) (UTC-5, 1 hour ahead of@subashis
)Mayra Cabrera ( @mayra-cabrera
) (UTC-5, 1 hour ahead of@subashis
)To spread load more evenly across eligible reviewers, Danger has picked a candidate for each review slot, based on their timezone. Feel free to override these selections if you think someone else would be better-suited or use the GitLab Review Workload Dashboard to find other available reviewers.
To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines. Please consider assigning a reviewer or maintainer who is a domain expert in the area of the merge request.
Once you've decided who will review this merge request, assign them as a reviewer! Danger does not automatically notify them for you.
If needed, you can retry the
danger-review
job that generated this comment.Generated by
Dangermentioned in commit 3813221e
added 1 commit
- 3813221e - Remove analyzer_pipeline metrics join to ci_builds
mentioned in issue #338010 (closed)
Setting label groupthreat insights based on
@subashis
's group.added groupthreat insights label
mentioned in commit 2c5d4ef1
added 1 commit
- 2c5d4ef1 - Remove analyzer_pipeline metrics join to ci_builds
mentioned in commit 608cdf9d
added 1 commit
- 608cdf9d - Remove analyzer_pipeline metrics join to ci_builds
mentioned in commit 198e627d
added 1 commit
- 198e627d - Remove analyzer_pipeline metrics join to ci_builds
@DylanGriffith can you please do the backend review, @stomlinson can you please do the database review, and @lmejia2 can you please do the product intelligence review?
requested review from @DylanGriffith, @lmejia2, and @stomlinson
mentioned in commit f12722fd
added 1041 commits
-
198e627d...02ad2733 - 1040 commits from branch
master
- f12722fd - Remove analyzer_pipeline metrics join to ci_builds
-
198e627d...02ad2733 - 1040 commits from branch
@DylanGriffith
, thanks for approving this merge request.This is the first time the merge request is approved. To ensure full test coverage, a new pipeline has been started.
For more info, please refer to the following links:
Thanks @subashis this LGTM. Could you please ensure you communicate this change with your PM and manager. These have all been agreed to previously but a reminder would be nice so they won't be surprised too much if they see any metrics change all of a sudden.
removed review request for @DylanGriffith
This looks good to me, thanks @subashis. As mentioned, the metrics could change, but that's intended.
@mayra-cabrera can you please do database maintainer review here?
added databasereviewed label and removed databasereview pending label
requested review from @mayra-cabrera and removed review request for @stomlinson
added product intelligenceapproved label and removed product intelligencereview pending label
removed review request for @lmejia2
- Resolved by Mayra Cabrera
added databaseapproved label and removed databasereviewed label
- A deleted user
added product intelligencereview pending label and removed product intelligenceapproved label
enabled an automatic merge when the pipeline for b376d14a succeeds
mentioned in commit 7202bb88
added workflowstaging-canary label and removed workflowrefinement label
added workflowstaging label and removed workflowstaging-canary label
added workflowcanary label and removed workflowstaging label
added workflowproduction label and removed workflowcanary label
mentioned in issue gitlab-com/www-gitlab-com#11934 (closed)
added releasedcandidate label
mentioned in merge request kubitus-project/kubitus-installer!306 (merged)
removed typefeature label
added releasedpublished label and removed releasedcandidate label