Skip to content

Performance issues on vulnerability_findings endpoint

Summary

This is a follow-up to an internal request for help issue.

The api/v4/projects/<project_id>/vulnerability_findings endpoint results in a PG::QueryCanceled: ERROR: canceling statement due to statement timeout on projects with a large number of vulnerability findings. In this issue(Internal), we noted that it was getting hung up on a query like so:

*/ SELECT \"security_findings\".* FROM \"security_findings\" INNER JOIN \"security_scans\" ON \"security_findings\".\"scan_id\" = \"security_scans\".\"id\" WHERE \"security_scans\".\"pipeline_id\" = $1 ORDER BY \"security_findings\".\"id\" ASC LIMIT $2"

We merged !117290 (merged) which skiped the ORDER BY to improve the query performance, but it was not enough to resolve the full scope of the performance issues.

The next step is to look at https://gitlab.com/gitlab-org/gitlab/-/blob/af749b979a3ef624d4c73f4962c9306681780f24/ee/app/finders/security/findings_finder.rb#L104 and see if we can remove it or replace it.

Steps to reproduce

See linked project(Internal), this also includes an example report for team members only.

What is the current bug behavior?

The Merge Request security report widget & Security tab on the pipeline page take a very long time to load (Upwards of 30 minutes). Occasionally, they see Error fetching the vulnerability list. Please check your network connection and try again. Curls to the vulnerability_findings timeout. Request ran for longer than 60000ms errors are displayed when trying to load vulnerability findings.

What is the expected correct behavior?

We should be able to retrieve vulnerabilities and be performant on projects with a large number of vulnerabilities reported.

Relevant logs and/or screenshots

v15.8.2

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info 

(For installations with omnibus-gitlab package run and paste the output of:
`sudo gitlab-rake gitlab:env:info`)

(For installations from source run and paste the output of:
`sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)

Results of GitLab application Check

Expand for output related to the GitLab application check 
(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true)


(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true)


(we will only investigate if the tests are passing)

Possible fixes

  1. Restructure the query in Security::FindingsFinder to use LATERAL JOIN.
SELECT
    "security_findings".*
FROM
    "security_scans",
    unnest('{1, 2, 3, 4, 5, 6, 7}'::smallint[]) AS "severities" ("severity"),
    LATERAL (
        SELECT 
            "security_findings".*
        FROM
            "security_findings"
        WHERE
            "security_findings"."scan_id" = "security_scans"."id"
            AND "security_findings"."deduplicated" = TRUE
            AND "security_findings"."severity" = "severities"."severity"
            AND (NOT EXISTS (
                    SELECT
                        1
                    FROM
                        "vulnerabilities"
                        INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
                    WHERE
                        "vulnerabilities"."state" = 2
                        AND (vulnerability_occurrences.uuid = security_findings.uuid::text)))
        ORDER BY
            "security_findings"."id" ASC
        LIMIT 20
    ) AS "security_findings"
WHERE
    "security_scans"."pipeline_id" = $PIPELINE_ID
    AND "security_scans"."latest" = TRUE
    AND "security_scans"."status" = 1
    AND "security_scans"."scan_type" IN (1, 2, 3, 4, 5, 6, 7)
ORDER BY
    "security_findings"."severity" DESC,
    "security_findings"."id" ASC
LIMIT 20
  1. Add a new index to security_findings 
CREATE INDEX ON security_findings (scan_id, deduplicated, severity DESC, id ASC)
  1. Add keyset pagination to the /api/:version/projects/:id/vulnerability_findings and modify any frontend code to keyset paginate by security_findings.id.

Verification steps

The verification project set up for this issue can be used. Prior to the changes this project was taking around 15 seconds to run the database queries.

Edited by Malcolm Locke