You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@devlake.apache.org by "AnkeshThakur (via GitHub)" <gi...@apache.org> on 2025/03/25 13:54:48 UTC
[I] [Question][DORA] Dora tasks is taking significantly long [incubator-devlake]
AnkeshThakur opened a new issue, #8361:
URL: https://github.com/apache/incubator-devlake/issues/8361
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
## Question
We have been using devlake for a while and we are increasing its scope to monitor many of our projects. With increased usage, we are hit with very long Dora calculation step. Total process takes around 10+ hours to complete. Logs says dora's `calculateChangeLeadTime` is taking a lot of time (log snip is below).
Is there any known way to improve it significantly? Have added few indexes but they did not help. Maybe more indexes or some parameters to boost concurrency?
## Screenshots
If applicable, add screenshots to help explain.
## Additional context
Slow query log is showing that these tasks are processing one data point at a time and scan significantly high number of rows. Adding index to relevant columns improves efficiency by ~25%. However, overall process still takes good amount of time which makes it unsuitable for daily data refresh because during this task no data is available in `project_pr_metrics` and other important tables.
Sample slow query logs:
```
# Time: 2025-03-25T10:26:54.420337Z
# User@Host: ########[#####] @ [##.##.###.##] Id: 17460
# Query_time: 10.153949 Lock_time: 0.000004 Rows_sent: 1 Rows_examined: 314841
SET timestamp=1742898404;
SELECT dc.* FROM cicd_deployment_commits dc LEFT JOIN cicd_deployment_commits p ON (dc.prev_success_deployment_commit_id = p.id) LEFT JOIN project_mapping pm ON (pm.table = 'cicd_scopes' AND pm.row_id = dc.cicd_scope_id) INNER JOIN commits_diffs cd ON (cd.new_commit_sha = dc.commit_sha AND cd.old_commit_sha = COALESCE (p.commit_sha, '')) WHERE dc.prev_success_deployment_commit_id <> '' AND dc.environment = 'PRODUCTION' AND (pm.project_name = '#####' AND cd.commit_sha = 'csrffa028d4c5c85341f861beb685fba4e115e65' AND dc.RESULT = 'SUCCESS') ORDER BY dc.started_date, dc.id ASC LIMIT 1;
```
Log of `calculateChangeLeadTime`
```
[GIN] 2025/03/25 - 13:41:54 | 200 | 2.83µs | 10.221.51.137 | GET "/ping"
time="2025-03-25 13:41:55" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1699(not exactly)"
time="2025-03-25 13:41:58" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1701(not exactly)"
[GIN] 2025/03/25 - 13:41:59 | 200 | 2.35µs | 10.221.51.137 | GET "/ping"
[GIN] 2025/03/25 - 13:41:59 | 200 | 1.34µs | 10.221.51.137 | GET "/ping"
time="2025-03-25 13:42:02" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1703(not exactly)"
[GIN] 2025/03/25 - 13:42:04 | 200 | 2.62µs | 10.221.51.137 | GET "/ping"
[GIN] 2025/03/25 - 13:42:04 | 200 | 880ns | 10.221.51.137 | GET "/ping"
time="2025-03-25 13:42:06" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1705(not exactly)"
[GIN] 2025/03/25 - 13:42:09 | 200 | 2.69µs | 10.221.51.137 | GET "/ping"
[GIN] 2025/03/25 - 13:42:09 | 200 | 960ns | 10.221.51.137 | GET "/ping"
time="2025-03-25 13:42:10" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1707(not exactly)"
time="2025-03-25 13:42:13" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1709(not exactly)"
[GIN] 2025/03/25 - 13:42:14 | 200 | 2.63µs | 10.221.51.137 | GET "/ping"
[GIN] 2025/03/25 - 13:42:14 | 200 | 880ns | 10.221.51.137 | GET "/ping"
time="2025-03-25 13:42:16" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1711(not exactly)"
[GIN] 2025/03/25 - 13:42:19 | 200 | 2.35µs | 10.221.51.137 | GET "/ping"
[GIN] 2025/03/25 - 13:42:19 | 200 | 870ns | 10.221.51.137 | GET "/ping"
time="2025-03-25 13:42:20" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1713(not exactly)"
time="2025-03-25 13:42:23" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1715(not exactly)"
[GIN] 2025/03/25 - 13:42:24 | 200 | 2.41µs | 10.221.51.137 | GET "/ping"
[GIN] 2025/03/25 - 13:42:24 | 200 | 1.28µs | 10.221.51.137 | GET "/ping"
time="2025-03-25 13:42:26" level=info msg=" [pipeline service] [pipeline #144] [task #2081] [calculateChangeLeadTime] finished records: 1717(not exactly)"
```
--
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: dev-unsubscribe@devlake.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Question][DORA] Dora tasks is taking significantly long [incubator-devlake]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #8361: [Question][DORA] Dora tasks is taking significantly long
URL: https://github.com/apache/incubator-devlake/issues/8361
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Question][DORA] Dora tasks is taking significantly long [incubator-devlake]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #8361:
URL: https://github.com/apache/incubator-devlake/issues/8361#issuecomment-2928198926
This issue has been closed because it has been inactive for a long time. You can reopen it if you encounter the similar problem in the future.
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Question][DORA] Dora tasks is taking significantly long [incubator-devlake]
Posted by "klesh (via GitHub)" <gi...@apache.org>.
klesh commented on issue #8361:
URL: https://github.com/apache/incubator-devlake/issues/8361#issuecomment-2753545804
Hi, @AnkeshThakur , Can you share which index improved the performance the most? Would you like to put up a PR for that?
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Question][DORA] Dora tasks is taking significantly long [incubator-devlake]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #8361:
URL: https://github.com/apache/incubator-devlake/issues/8361#issuecomment-2908198804
This issue has been automatically marked as stale because it has been inactive for 60 days. It will be closed in next 7 days if no further activity occurs.
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Question][DORA] Dora tasks is taking significantly long [incubator-devlake]
Posted by "dosubot[bot] (via GitHub)" <gi...@apache.org>.
dosubot[bot] commented on issue #8361:
URL: https://github.com/apache/incubator-devlake/issues/8361#issuecomment-2751357539
<!-- Answer -->
To improve the performance of the `calculateChangeLeadTime` function in DevLake, consider the following strategies:
1. **Optimize Database Queries**: Review the SQL queries used in the function, especially those in `getFirstCommit`, `getFirstReview`, and `getDeploymentCommit`. Ensure that they are optimized and that appropriate indexes are applied to the columns involved in joins and where clauses. This can significantly reduce the number of rows scanned and improve query performance.
2. **Batch Processing**: The function already processes pull requests in batches of 100, which is a good practice. Ensure that this batch size is optimal for your database and workload. You might experiment with different batch sizes to find the most efficient one.
3. **Concurrency and Parallelism**: If the function is not already running concurrently, consider parallelizing the processing of pull requests. This can be done by splitting the workload across multiple threads or processes, depending on your system's capabilities.
4. **Indexing**: Since you've already added some indexes, ensure they are on the most frequently queried columns. For example, indexes on `cicd_deployment_commits.commit_sha`, `project_mapping.project_name`, and `commits_diffs.new_commit_sha` could be beneficial if not already present.
5. **Asynchronous Processing**: If possible, decouple the calculation from the main workflow and run it asynchronously. This way, the main application can continue to function while the calculations are being performed in the background.
6. **SQL Optimization Techniques**: Consider using SQL window functions or other advanced SQL features to reduce the complexity of queries. This can help in calculating metrics more efficiently.
7. **Database Configuration**: Review your database configuration to ensure it is optimized for the workload. This includes settings related to memory allocation, cache sizes, and connection pooling.
By implementing these strategies, you should be able to reduce the time taken by the `calculateChangeLeadTime` function and improve the overall performance of your DevLake setup <sup>[[1]](https://github.com/apache/incubator-devlake/blob/main/backend/plugins/dora/tasks/change_lead_time_calculator.go)</sup>.
<!-- Dosu Comment Footer -->
*To continue talking to [Dosu](https://dosu.dev), mention @dosu.*
---
<sup>Help Dosu learn! Give it feedback: [Great Response](https://app.dosu.dev/response-feedback/7149e0c0-3210-46e9-b0a5-3a8a5664becf?feedback_type=great_response) | [Irrelevant Answer](https://app.dosu.dev/response-feedback/7149e0c0-3210-46e9-b0a5-3a8a5664becf?feedback_type=irrelevant_answer) | [Incorrect Sources](https://app.dosu.dev/response-feedback/7149e0c0-3210-46e9-b0a5-3a8a5664becf?feedback_type=incorrect_sources) | [Too Verbose](https://app.dosu.dev/response-feedback/7149e0c0-3210-46e9-b0a5-3a8a5664becf?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/7149e0c0-3210-46e9-b0a5-3a8a5664becf?feedback_type=hallucination) | [Bug Report](https://app.dosu.dev/response-feedback/7149e0c0-3210-46e9-b0a5-3a8a5664becf?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/7149e0c0-3210-46e9-b0a5-3a8a5664becf?feedback_type=other)</sup>
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Question][DORA] Dora tasks is taking significantly long [incubator-devlake]
Posted by "AnkeshThakur (via GitHub)" <gi...@apache.org>.
AnkeshThakur commented on issue #8361:
URL: https://github.com/apache/incubator-devlake/issues/8361#issuecomment-2753435128
After adding indexes and buffer size increase this is taking around 5 hours to complete
```
time="2025-03-26 06:40:47" level=info msg=" [pipeline service] [pipeline #101] [task #2018] [calculateChangeLeadTime] finished records: 4071(not exactly)"
[GIN] 2025/03/26 - 06:40:52 | 200 | 2.17µs | ##.##.###.## | GET "/ping"
[GIN] 2025/03/26 - 06:40:52 | 200 | 2.2µs | ##.##.###.## | GET "/ping"
time="2025-03-26 06:40:52" level=info msg=" [pipeline service] [pipeline #101] [task #2018] [calculateChangeLeadTime] finished records: 4072(not exactly)"
[GIN] 2025/03/26 - 06:40:57 | 200 | 490ns | ##.##.###.## | GET "/ping"
[GIN] 2025/03/26 - 06:40:57 | 200 | 2.32µs | ##.##.###.## | GET "/ping"
time="2025-03-26 06:40:57" level=info msg=" [pipeline service] [pipeline #101] [task #2018] [calculateChangeLeadTime] finished records: 4073(not exactly)"
time="2025-03-26 06:40:57" level=info msg=" [pipeline service] [pipeline #101] [task #2018] subtask calculateChangeLeadTime finished in 16147444 ms"
```
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Question][DORA] Dora tasks is taking significantly long [incubator-devlake]
Posted by "AnkeshThakur (via GitHub)" <gi...@apache.org>.
AnkeshThakur commented on issue #8361:
URL: https://github.com/apache/incubator-devlake/issues/8361#issuecomment-2754859599
Overall time is reduced from 10 hours to 5 hours. And the Dora step's time is reduced from ~9 hours to ~4 hours. I have not identified the index that performed best. I need to observe it more. But I had added these indexes:
```
CREATE INDEX idx_cicd_deployment_commits_prev_success_deployment_commit_id ON cicd_deployment_commits(prev_success_deployment_commit_id);
CREATE INDEX idx_cicd_deployment_commits_environment ON cicd_deployment_commits(environment);
CREATE INDEX idx_commits_diffs_new_commit_sha ON commits_diffs(new_commit_sha);
CREATE INDEX idx_commits_diffs_old_commit_sha ON commits_diffs(old_commit_sha);
CREATE INDEX idx_cicd_deployment_commits_commit_sha ON cicd_deployment_commits(commit_sha);
CREATE INDEX idx_cicd_deployment_commits_result ON cicd_deployment_commits(RESULT);
CREATE INDEX idx_commits_diffs_composite ON commits_diffs (new_commit_sha, old_commit_sha);
```
My hunch is that it is idx_commits_diffs_composite but I am not certain.
--
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: dev-unsubscribe@devlake.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org