This is an issue and not a support question which should be asked at https://forum.greenbone.net/?
Is there an existing issue for this?
Current Behavior
I think there may be a performance issue in the calculation of statistics for the "Reports with High Results" chart. According to the intercepted SQL query [long_query2.sql], it calls the report_severity_count() function eight times and report_result_host_count() four times for each report. The function first tries to use to use pre-calculated data from report_counts. If it doesn't exist, it performs a full recalculation with additional processing of severity, QoD, override, etc.
This appears to become a significant performance bottleneck as the number of reports increases.
For my installation the SQL query itself takes approximately 8 minutes to complete when executed directly in PostgreSQL [explain.sql]. When the same query is executed through the web interface, it competes with other gvmd/database activity. The request eventually times out, additional long-running requests can accumulate, CPU usage reaches 100%, and the chart fails to load. After reducing the number of reports to approximately 60, the chart starts loading successfully again.
The EXPLAIN (ANALYZE, BUFFERS) output shows that the vast majority of the execution time is spent inside the inner GroupAggregate, where the report-level functions are evaluated.
I am attaching:
- long_query2.sql — the complete SQL queries captured from pg_stat_activity.
- explain.sql — EXPLAIN (ANALYZE, BUFFERS) output for the one of queries.
Could this query be optimized?
Expected Behavior
The chart query should scale reasonably with the number of stored reports and should not perform excessive repeated processing of the same report data.
Steps To Reproduce
- 469 reports, approximately 1.5 million results, 4 CPU cores, 8 GB RAM
- Open the "Reports" tab
- Infinite "Reports with High Results" chart loading
Operating System
CentOS 8
Version
gvmd 26.19.0 (I checked the gvmd changelog from 26.19.0 through the newer releases for changes related to my problem, but didn't find)
pg-gvm 22.6.17
Anything else?
No response
This is an issue and not a support question which should be asked at https://forum.greenbone.net/?
Is there an existing issue for this?
Current Behavior
I think there may be a performance issue in the calculation of statistics for the "Reports with High Results" chart. According to the intercepted SQL query [long_query2.sql], it calls the report_severity_count() function eight times and report_result_host_count() four times for each report. The function first tries to use to use pre-calculated data from report_counts. If it doesn't exist, it performs a full recalculation with additional processing of severity, QoD, override, etc.
This appears to become a significant performance bottleneck as the number of reports increases.
For my installation the SQL query itself takes approximately 8 minutes to complete when executed directly in PostgreSQL [explain.sql]. When the same query is executed through the web interface, it competes with other gvmd/database activity. The request eventually times out, additional long-running requests can accumulate, CPU usage reaches 100%, and the chart fails to load. After reducing the number of reports to approximately 60, the chart starts loading successfully again.
The EXPLAIN (ANALYZE, BUFFERS) output shows that the vast majority of the execution time is spent inside the inner GroupAggregate, where the report-level functions are evaluated.
I am attaching:
Could this query be optimized?
Expected Behavior
The chart query should scale reasonably with the number of stored reports and should not perform excessive repeated processing of the same report data.
Steps To Reproduce
Operating System
CentOS 8
Version
gvmd 26.19.0 (I checked the gvmd changelog from 26.19.0 through the newer releases for changes related to my problem, but didn't find)
pg-gvm 22.6.17
Anything else?
No response