High CPU usage in Reports page (Reports with High Results chart) with ~500 reports on gvmd 26.19.0

Building from Source and Advanced Topics
1

Environment

  • gvmd: 26.19.0

  • GSA: 26.14.0

  • PostgreSQL: 17.9

  • Docker deployment

  • Database revision: 272

The installation was upgraded from an gvmd 22.4 several months ago.

Problem

Hello. I’ve got a problem. As soon as the Reports page is opened, PostgreSQL starts executing one very expensive aggregate query, trying to load the Reports with High Results chart. While this query is still running, the browser eventually retries the request, and more identical queries are started. Finally, 10-15 PostgreSQL backend processes appear, all consuming CPU.

On a 16-core server this drives PostgreSQL CPU usage close to 100%.

htop1
htop12403×962 440 KB

htop2
htop21920×802 162 KB

Observations

The problem appears to be caused specifically by the default chart:

Reports with High Results

The other two charts (Reports by Severity Class, Reports by CVSS).

chart
chart2335×1091 290 KB

If the “Reports with High Results” chart is hidden (once it manages to render), the issue does not occur. This strongly suggests that the expensive query belongs only to this chart.

Database statistics

reports               : 493
results               : 1,489,570
report_hosts          : 1,094,734
report_host_details   : 6,385,800

Vacuum / Analyze

Autovacuum is working.

Manual VACUUM ANALYZE was also executed.

It did not change the behavior.

Reports state

Most reports are finished.

Done        : 484
Stopped     : 7
Interrupted : 2

I don’t understand why the resulting SQL is so expensive :worried:

Sure, I can just disable the problematic chart, but I can only do this for one user and I can’t change the default view.

1 Like
2

@popple how much ram is installed? storage on fast ssd?

Eero

3

I use the virtual machine with 18 GB RAM and 100 GB of HDD storage

              total        used        free      shared  buff/cache   available
Mem:           18Gi       3.1Gi       2.2Gi       251Mi        13Gi        14Gi
Swap:            0B          0B          0B

NAME ROTA  SIZE MODEL
sr0     1 1024M QEMU DVD-ROM    
vda     1  100G
4

@popple HDD ? you mean old mechanical rotating disk? that is not good solution. Try ssd

Eero

5

@popple .. In principle, you could capture the SQL query and optimize the tables by adding appropriate indexes, potentially with the help of AI. However, as already noted, HDD storage is a poor fit for this workload—an SSD is really the only sensible solution.

Eero

1 Like
7

Seems so:c
EXPLAIN (ANALYZE, BUFFERS) showed that the chart query takes about 8 minutes to complete when the system is otherwise idle. When other processes are running, it can time out and multiple concurrent executions can drive CPU usage to 100%. In my case, only after reducing the number of reports, the chart starts loading normally.