Memory consumption investigation

[gildub]

A test with a important data set containing all Red Hat CVEs and OSVs with about 45% of the Red Hat CVEs and tested with a unique SBOM through a complex query such as http://localhost:8080/api/v2/sbom/urn%3Auuid%3A019a584a-cb0e-7ee0-9bff-a567e938b63d/advisory takes about 12 minutes and make trustd to use over 40GB of memory.

Meanwhile when running two queries as above concurrently makes trustd to consume over 64GB.

This is related to #2039, and the concern is about production requirements for big data set and what could we do to tackle the memory consumption.

[helio-frota]

not sure if I'm mixing things in my mind... but I think we can use 1 external database and then run cargo test on it [?] if yes then
we can probably use the memory-gate-threshold that is part of the test-context to see what happens [[?]]

MEM_LIMIT_MB=1 cargo nextest run -- sbom_details_cyclonedx_osv -- --test-threads=1

[gildub]

@helio-frota, I wonder what setup is required for production environment. For instance when using RDS postgres dbms, are those envar available and if yes then what value to assign to them ? I suppose, the question to ask ourselves is kind of the opposite, shoudn't the default values work out of the box for production environments ?

[helio-frota]

I wonder what setup is required for production environment.

🤷‍♂️

shoudn't the default values work out of the box for production environments ?

I think no 👍
But I think we need a long running quest to have things viable as possible with default values... sounds obvious I know...
Probably next year we should start thinking about some heavy (postgresql) dependent structures / alternatives...

We already have some specific procedures in the DB. Not sure if we for some cases we discard the reuse and start thinking on specialized queries for UI pages and/or materialized views for specific features ... I don't know atm.

[helio-frota]

are those envar available and if yes then what value to assign to them ?

ah you mean MEM_LIMIT_MB ? this is for development only we use this when running cargo test it will not set a memory limit it will watch how many memory that specific test is consuming 👍
@gildub https://github.com/guacsec/trustify/blob/main/docs/env-vars.md?plain=1#L65

[gildub]

@helio-frota, thanks for you input to help with this matter.

My approach has been to use heavy duty dbms this is why I choose AWS RDS.
That allowed me to provision a database capable of data growing 300+GB and to tailor the instance underneath using at least 16vCPUs and 128GB of RAM and with 3000+ IOPS.
Meanwhile it relies on RDS configuration and optimization which I haven't dig into.

[helio-frota]

I found those numbers, in which makes me think that something else happened in your case

The source_document contains the size of the SBOM:

The 2 biggest sboms in my database

SELECT id, max(size) as size
FROM source_document
group by id, size
order by size desc
limit 2

Result (312 MB and 22MB)

----------------------------------------------------
| id                                   | size      |
| ------------------------------------ | --------- |
| 1e14f5b7-0253-4e70-9081-09fb58afbaac | 327386719 |
| 8c9ced43-9578-4f9a-86d5-29639a8fa908 | 23247420  |
----------------------------------------------------

Getting SBOM ids

SELECT sbom_id FROM sbom where source_document_id = '1e14f5b7-0253-4e70-9081-09fb58afbaac'
SELECT sbom_id FROM sbom where source_document_id = '8c9ced43-9578-4f9a-86d5-29639a8fa908'

• On the first test I contaminated the results by accessing the dashbard first, and then running a GET on
  http://localhost:8080/api/v2/sbom/urn:uuid:UUID_here/advisory endpoint, and got this result referencing the 22MB SBOM and heaptrack shows this:

• On the second test I uploaded the 312MB file and then stopped trustify, and started again, got the SBOM id from the database directly and ran a GET on http://localhost:8080/api/v2/sbom/urn:uuid:UUID_here/advisory using the 312MB SBOM and heaptrack shows this:

[helio-frota]

I wonder what setup is required for production environment.

I have a new feedback related to that.
I'm using default postgresql.conf with this content on my database and this is unusable when I try to access search, dashboard and sbom pages:

| **Table / View**           | **Children** | **Parents** | **Columns** | **Rows**  |
| -------------------------- | ------------ | ----------- | ----------- | --------- |
| advisory                   | 5            | 3           | 12          | 617601    |
| advisory_vulnerability     | 2            | 1           | 9           | 614995    |
| base_purl                  | 2            | 1           | 5           | 121708    |
| cpe                        | 3            | 1           | 12          | 0         |
| cvss3                      | 0            | 1           | 13          | 297377    |
| cvss4                      | 0            | 1           | 14          | 0         |
| importer                   | 8            | 0           | 13          | 16        |
| importer_report            | 0            | 1           | 5           | 0         |
| license                    | 1            | 0           | 4           | 3686      |
| licensing_infos            | 0            | 2           | 5           | 21532     |
| organization               | 2            | 1           | 4           | 435       |
| package_relates_to_package | 0            | 6           | 4           | 403646780 |
| pg_stat_statements         | 0            | 0           | 49          | 0         |
| pg_stat_statements_info    | 0            | 0           | 2           | 0         |
| product                    | 2            | 2           | 4           | 8513      |
| product_status             | 0            | 5           | 7           | 0         |
| product_version            | 0            | 3           | 5           | 277480    |
| product_version_range      | 1            | 2           | 4           | 0         |
| purl_status                | 0            | 5           | 7           | 74497     |
| qualified_purl             | 1            | 1           | 5           | 2554315   |
| relationship               | 1            | 0           | 2           | 16        |
| sbom                       | 7            | 1           | 9           | 388475    |
| sbom_external_node         | 0            | 0           | 8           | 55310     |
| sbom_file                  | 0            | 1           | 2           | 73370     |
| sbom_node                  | 6            | 2           | 3           | 301196930 |
| sbom_node_checksum         | 0            | 2           | 4           | 8474450   |
| sbom_package               | 4            | 2           | 4           | 300679780 |
| sbom_package_cpe_ref       | 0            | 3           | 3           | 0         |
| sbom_package_license       | 0            | 2           | 5           | 252323620 |
| sbom_package_purl_ref      | 0            | 3           | 3           | 300165340 |
| seaql_migrations           | 7            | 0           | 2           | 31        |
| source_document            | 2            | 0           | 6           | 1006076   |
| status                     | 2            | 1           | 4           | 6         |
| user_preferences           | 0            | 0           | 4           | 0         |
| version_range              | 2            | 3           | 6           | 27851     |
| version_scheme             | 1            | 1           | 3           | 19        |
| versioned_purl             | 1            | 2           | 4           | 2072580   |
| vulnerability              | 1            | 0           | 10          | 297792    |
| vulnerability_description  | 0            | 2           | 6           | 334235    |
| weakness                   | 0            | 0           | 12          | 0         |

Using a tool to intercept the requests I noticed that UI is polling search and then as this is already unusable to me, it ends up being a sort of DOS for database. [ ofc we need to solve this on the back-end ]