chenwei791129 opened a new issue, #9182: URL: https://github.com/apache/devlake/issues/9182
### Search before asking - [x] I had searched in the [issues](https://github.com/apache/devlake/issues?q=is%3Aissue) and found no similar issues. ### What happened On a GitLab project whose pipelines create many manual deploy jobs (one per target/environment, most of which are never triggered), `refdiff` → `calculateDeploymentCommitsDiff` takes **5–6 hours on every daily run**, even when no new deployments were collected. Root cause is in `backend/plugins/refdiff/tasks/deployment_commit_diff_calculator.go` (unchanged between `v1.0.3-beta10` and `v1.0.3-beta18`). Step 1 selects the pairs to calculate: ```go dal.Select("dc.id, dc.commit_sha, p.commit_sha as prev_commit_sha"), dal.From("cicd_deployment_commits dc"), dal.Join("LEFT JOIN project_mapping pm ON (pm.table = 'cicd_scopes' AND pm.row_id = dc.cicd_scope_id)"), dal.Join("LEFT JOIN cicd_deployment_commits p ON (dc.prev_success_deployment_commit_id = p.id)"), dal.Where(` pm.project_name = ? AND NOT EXISTS ( SELECT 1 FROM _tool_refdiff_finished_commits_diffs fcd WHERE fcd.new_commit_sha = dc.commit_sha AND fcd.old_commit_sha = p.commit_sha )`, ...), ``` When a deployment commit has no previous successful deployment, the `LEFT JOIN` yields `p.commit_sha = NULL`. After the diff is calculated, the pair is marked as finished with `OldCommitSha: pair.PrevCommitSha`, which is the Go zero value `''` (the column is `varchar(40) NOT NULL`). On the next run the guard evaluates `'' = NULL`, which is `NULL`, not `TRUE`, so `NOT EXISTS` is always true. **Every such pair is selected and recalculated again on every run, forever.** Step 3 has no de-duplication either. Each of these rows recomputes the full ancestry of its commit (a diff against "nothing") and rewrites all of those rows into `commits_diffs`. Numbers from our instance (single project, MySQL 8.4): | Metric | Value | |---|---| | `cicd_deployment_commits` rows for the project | ~167,000 | | Rows with no previous successful deployment | ~144,000 (only ~3,400 distinct `commit_sha`) | | Of those, GitLab deployment status `blocked` (manual job never run) / `skipped` | ~135,000 / ~7,500 | | `_tool_refdiff_finished_commits_diffs` rows with `old_commit_sha = ''` (instance-wide) | ~94,000 | | `commits_diffs` rows per recalculated pair | ~3,400 (full history) | | `calculateDeploymentCommitsDiff` duration, every daily run | 16,000–21,000 s | The same query also picks up deployments whose `result` is empty (blocked/skipped manual jobs). These can never be a previous successful deployment and never contribute to DORA metrics, but they still pay the full cost. ### What do you expect to happen A pair that has already been calculated is skipped on later runs, including pairs without a previous successful deployment. Once no new deployments are collected, the subtask should finish in seconds. ### How to reproduce 1. Use a GitLab project whose `.gitlab-ci.yml` defines deploy jobs with `environment:` and `when: manual`, so most pipelines leave `blocked` deployments behind. 2. Add it to a project with the DORA and refdiff plugins enabled, and collect data. 3. Run the blueprint again without any new deployments. 4. The `calculateDeploymentCommitsDiff` progress total equals the number of deployment commits without a previous successful deployment, not 0. The log shows `total N commits of difference found between [new][<sha>] and [old][(total:1)]` for the same SHAs on every run. Quick check (MySQL): ```sql -- rows that will be re-selected on every run SELECT COUNT(*) FROM cicd_deployment_commits dc LEFT JOIN cicd_deployment_commits p ON dc.prev_success_deployment_commit_id = p.id WHERE dc.cicd_scope_id = '<scope id>' AND p.id IS NULL; -- finished markers stored with an empty old sha SELECT COUNT(*) FROM _tool_refdiff_finished_commits_diffs WHERE old_commit_sha = ''; ``` ### Anything else Possible fixes (not mutually exclusive): 1. Compare against the stored value: `fcd.old_commit_sha = COALESCE(p.commit_sha, '')`. MySQL's NULL-safe `<=>` alone is not enough, because the stored value is `''`, not `NULL`. 2. De-duplicate pairs by `(commit_sha, prev_commit_sha)` before step 3. Many deployment commits share the same SHA, e.g. one pipeline with many deploy jobs. 3. Optionally only calculate diffs for deployments with `result = 'SUCCESS'`, since blocked/skipped deployments are never used as a previous successful deployment. I'll open a PR for option 1 with an e2e regression test (running the subtask a second time must not recalculate anything). Option 2 can follow separately if desired. ### Version v1.0.3-beta10 (code path verified unchanged in v1.0.3-beta18) ### Are you willing to submit PR? - [x] Yes I am willing to submit a PR! ### Code of Conduct - [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
