Performance Improvement Review: Dojo Edit finding takes 50 seconds to complete #10313
Description
Problem description
Dojo Edit Finding from UI takes 50 seconds to complete. How can the performance be improved here?
The system has 10K engagements with 100K findings
Steps to reproduce
Steps to reproduce the behavior:
- Go to Findings
- Click on Finding
- Select a finding
- See time lag
Expected behavior
The edit finding is taking a very long time to open
Deployment method (select with an X)
- [X ] Docker Compose
- Kubernetes
- GoDojo
Environment information
- Operating System: [e.g. Ubuntu 18.04] - Oracle Linux 7
- DefectDojo version (see footer) or commit message: [use
git show -s --format="[%ci] %h: %s [%d]"]
v. 2.29.4 ( release mode )
Logs
Use docker-compose logs (or similar, depending on your deployment method) to get the logs and add the relevant sections here showing the error occurring (if applicable).
In MysQL, below query gets triggered(captured from slow query log)
# Time: 2024-06-03T05:05:11.959378Z
# User@Host: defectdojo[defectdojo] @ defectdojo.host.com [10x.10y.1mn.1qr] Id: 3691498
# Query_time: 3.210302 Lock_time: 0.000005 Rows_sent: 97162 Rows_examined: 194324
SET timestamp=1717391108;
SELECT `dojo_endpoint`.`id`, `dojo_endpoint`.`protocol`, `dojo_endpoint`.`userinfo`, `dojo_endpoint`.`host`, `dojo_endpoint`.`port`, `dojo_endpoint`.`path`, `dojo_endpoint`.`query`, `dojo_endpoint`.`fragment`, `dojo_endpoint`.`product_id` FROM `dojo_endpoint` INNER JOIN `dojo_product` ON (`dojo_endpoint`.`product_id` = `dojo_product`.`id`) WHERE `dojo_endpoint`.`product_id` = 2 ORDER BY `dojo_product`.`name` ASC, `dojo_endpoint`.`host` ASC, `dojo_endpoint`.`protocol` ASC, `dojo_endpoint`.`port` ASC, `dojo_endpoint`.`userinfo` ASC, `dojo_endpoint`.`path` ASC, `dojo_endpoint`.`query` ASC, `dojo_endpoint`.`fragment` ASC;
The uwsgi logs show 50 seconds time to serve the edit finding request
[pid: 1901|app: -|req: -/-] 10.x.y.z (user@defectdojo.com) {60 vars in 1600 bytes} [Mon Jun 3 06:11:16 2024] GET /finding/1059966/edit => generated 12856718 bytes in 50006 msecs (HTTP/1.1 200) 8 headers in 372 bytes (25 switches on core 0)
Get all findings results in below query that takes 36 seconds from 25 findings
`SELECT `dojo_finding`.`id`, `dojo_finding`.`title`, `dojo_finding`.`date`, `dojo_finding`.`sla_start_date`, `dojo_finding`.`cwe`, `dojo_finding`.`cve`, `dojo_finding`.`cvssv3`, `dojo_finding`.`cvssv3_score`, `dojo_finding`.`url`, `dojo_finding`.`severity`, `dojo_finding`.`description`, `dojo_finding`.`mitigation`, `dojo_finding`.`impact`, `dojo_finding`.`steps_to_reproduce`, `dojo_finding`.`severity_justification`, `dojo_finding`.`refs`, `dojo_finding`.`test_id`, `dojo_finding`.`active`, `dojo_finding`.`verified`, `dojo_finding`.`false_p`, `dojo_finding`.`duplicate`, `dojo_finding`.`duplicate_finding_id`, `dojo_finding`.`out_of_scope`, `dojo_finding`.`risk_accepted`, `dojo_finding`.`under_review`, `dojo_finding`.`last_status_update`, `dojo_finding`.`review_requested_by_id`, `dojo_finding`.`under_defect_review`, `dojo_finding`.`defect_review_requested_by_id`, `dojo_finding`.`is_mitigated`, `dojo_finding`.`thread_id`, `dojo_finding`.`mitigated`, `dojo_finding`.`mitigated_by_id`, `dojo_finding`.`reporter_id`, `dojo_finding`.`numerical_severity`, `dojo_finding`.`last_reviewed`, `dojo_finding`.`last_reviewed_by_id`, `dojo_finding`.`param`, `dojo_finding`.`payload`, `dojo_finding`.`hash_code`, `dojo_finding`.`line`, `dojo_finding`.`file_path`, `dojo_finding`.`component_name`, `dojo_finding`.`component_version`, `dojo_finding`.`static_finding`, `dojo_finding`.`dynamic_finding`, `dojo_finding`.`created`, `dojo_finding`.`scanner_confidence`, `dojo_finding`.`sonarqube_issue_id`, `dojo_finding`.`unique_id_from_tool`, `dojo_finding`.`vuln_id_from_tool`, `dojo_finding`.`sast_source_object`, `dojo_finding`.`sast_sink_object`, `dojo_finding`.`sast_source_line`, `dojo_finding`.`sast_source_file_path`, `dojo_finding`.`nb_occurences`, `dojo_finding`.`publish_date`, `dojo_finding`.`service`, `dojo_finding`.`planned_remediation_date`, `dojo_finding`.`planned_remediation_version`, `dojo_finding`.`effort_for_fixing`, COUNT(CASE WHEN `dojo_endpoint_status`.`mitigated` = 0 THEN `dojo_endpoint_status`.`id` ELSE NULL END) AS `active_endpoint_count`, COUNT(CASE WHEN `dojo_endpoint_status`.`mitigated` = 1 THEN `dojo_endpoint_status`.`id` ELSE NULL END) AS `mitigated_endpoint_count` FROM `dojo_finding` LEFT OUTER JOIN `dojo_endpoint_status` ON (`dojo_finding`.`id` = `dojo_endpoint_status`.`finding_id`) GROUP BY `dojo_finding`.`id` ORDER BY `dojo_finding`.`numerical_severity` ASC LIMIT 25;`
25 rows in set (35.40 sec)
The corresponding uwsgi logs shows 42 seconds
[pid: 1897|app: -|req: -/-] 10.x.y.z (user@defectdojo.com) {60 vars in 1568 bytes} [Mon Jun 3 06:20:54 2024] GET /finding => generated 3207729 bytes in 41669 msecs (HTTP/1.1 200) 9 headers in 516 bytes (12 switches on core 0
Sample scan files
If applicable, add sample scan files to help reproduce your problem.
Screenshots
If applicable, add screenshots to help explain your problem.
Additional context (optional)
Add any other context about the problem here.