apiserver: "Failed to load table data" when opening "Vulnerability Audit" page

[migus-rs]

Current Behavior

As the title suggests, simply opening the "Vulnerability Audit" tab results in a long wait while loading, followed by the error message "Failed to load table data".

This happens on a portfolio with around 66K projects and 14.5M components running DT version 5.0.2.
The test user has access to all projects.
DTv4 with a similar portfolio is able to open the same view in just a few seconds.

A look at the database with pgAdmin shows the query in question taking more than several minutes.
In fact, it is still running ~30min after the UI gave up, so this might eventually become a resource/connection exhaustion problem as well?

Unfortunately, pgAdmin cuts off the query after 61 lines and the source code has quite a lot of dynamic annotations, so I cannot provide the output of EXPLAIN at this time.

Steps to Reproduce

1. Deploy DTv5
2. Have a modestly-sized portfolio
3. Navigate to the "Vulnerability Audit" tab in the UI
4. observe the issue

Expected Behavior

The UI works as expected and database resources are not exhausted.

Dependency-Track Version

5.x

Browser

N/A

Checklist

• I have read and understand the contributing guidelines
• I have checked the existing issues for whether this defect was already reported

[nscuro]

#6459 will very likely help, we can backport it to 5.0.3.

The interesting part is that v4 did not have any database-level pagination at all, so it pulled the full dataset into memory before pruning and enriching it.

[migus-rs]

Just a heads-up: This seems to have another side-effect I'll try to describe.
I'm adding this here as the behavior is easily triggered by the originally described problem but can also open a separate issue, if you think it is appropriate.

My test deployment currently has 2 apiserver containers running in Kubernetes. Out of curiosity I asked a colleague to refresh the "Vulnerability Audit" page a few times yesterday. Back then, this seemed to have little effect other than the page not loading as per usual.

Today I found spurious messages complaining about database pool connections being unavailable in my logs.

After some investigation and a look at the Grafana database dashboard, I noticed something peculiar.

Since around 14:30 yesterday one of the containers seemed to constantly use all of its database connections. However, there was neither exceptional load on that one nor a significant backlog of tasks to be executed.
Additionally, pgAdmin only showed 18 connections in total to the database server.

It happens that I asked my colleague to refresh the "Vulnerability Audit" page right around that time, so that seemed suspicious to me.
Finding that, I repeated the process of refreshing the page myself (~50x) with the result visible at the right edge of the Grafana diagram: The other container now also used up all its connections and started outputting error messages.

To some extent this behavior matches what I expected. Long-running queries block connections in the pool.
What concerns me is the apparent inability of either container to recover from this state on its own.
Even connections long closed by the database server are not detected as such by the pooling logic. Instead, they seem to be tracked as used and prevent other actions from being performed.

This ultimately results in a denial-of-service with only some error messages being shown when trying to access the web interface.

[nscuro]

Thanks for the additional context. I've been looking into making further improvements to these views on top of #6459.

What kills these queries is pretty much the counting, not the data retrieval itself, as it has to materialize the full result set to report an exact value. The worst-case scenario for this is:

1. You have millions of findings across all projects
2. The client requesting the data has access to all projects
3. The client has not specified any filters (or filters that don't meaningfully reduce the result set)
4. The client has requested sorting by an unindexed / computed value
5. The RAM available to the database is not sufficient to hold the full working set so data can't be served from cache
6. The database has HDD / remote disk storage, not local SSD / NVMe, so random disk access is heavily penalized

I ran some tests with up to 100M findings on a DB with 4GB RAM available (effective_cache_size=3GB) and HDD storage, and (6) is by far the biggest contributor (i.e. I/O cost from doing random access on HDD). Unfortunately the way these views were designed in v4 means there is no way to ensure proper indexing on all involved columns, it's effectively an OLAP workload retrofitted on a database schema optimized for OLTP.

I already have working changes locally to constrain the cost of these queries, mainly:

• Reporting exact counts only up to 10k findings, so determining the count no longer scales with number of findings. UI then shows "10,000+" if there are more than 10k. The v2 API already supports partial counts natively but I had to retrofit it onto v1 in a way that isn't a breaking change.
• Setting a strict timeout of 10-15s via statement_timeout. That way, slow queries are cancelled in the database and users get feedback along the lines of "took too long, try to apply more filters".

I'll also update the "Deploying to production" / "Configuring the database" guides to mention SSD / NVMe storage as recommendation (I think this should be baseline these days but best to mention explicitly).

[migus-rs]

Thanks for the in-depth explanation.

Opening the audit page as an admin user being so close to the worst-case described explains why this can take so long.

The one point of confusion remaining for me is the (possibly separate) issue of what seems like connection pool exhaustion.
The proposed statement_timeout most likely helps catching unexpected delay spikes, but might not be related to the problem in the same way from what I observed.

As mentioned in my last comment, the connection pool for one of the containers listed all its connections as active even after a day with the database having definitely closed the sessions according to pgAdmin.
Should this turn out to be a result of a mismatch in connection state tracking instead of "just" unfortunate query timeouts, adjusting the timeout alone reduces the likelihood of failure, but does not fix the underlying problem as far as I can tell.
After all, even after completion/timeout the connections were not returned to their idle state.

I'd like to understand the surrounding circumstances leading to this (aside from the database taking too long) to make sure we're not missing something else here.
I'll gladly move this topic to another issue or the discussions tab, if you think this warrants further analysis separately.

[nscuro]

Might be the same cause as #6292, i.e. something drops the connection and the connection pool never gets to know about it.

[migus-rs]

something drops the connection and the connection pool never gets to know about it.

That seems like a reasonable assumption.
During a load test yesterday all instances managed to recover fine from a database server crash, so the cause was probably something more subtle like you suggested.