software/versions endpoint is RAM-heavy

[iansltx]

Fleet version: 4.58.0

---

💥  Actual behavior

Running software/versions with a large number of hosts (e.g. 10k) with a typical amont of software per host is a heavy operation on RAM usage, spiking customer pod RAM usage and potentially causing the environment to OOM.

🧑‍💻  Steps to reproduce

1. Load a bunch of hosts and software into the system
2. Load the software/versions endpoint

🕯️ More info

Split from #23078, referenced in #22291. This is a lower priority than the hosts list with software listings enabled as the customer's use case can get by with using just that endpoint per current understanding. This endpoint doesn't appear to be DB-heavy, but generates a large response that we need to store and serialize more efficiently if possible.

🛠️ To fix

TBD, but probably involves deduplicating data structures.

[sharon-fdm]

Timebox 2 points for code review and attempt to make it lighter.

[ksykulev]

The performance issue/memory bloat is due to two issues both stemming from the way we fetch and display vulnerabilities.

1. In mysql/software.go the listSoftwareDB method has a query that joins software to software_cve and cve_meta. Although there is only 20 rows in the software versions table the query returns a variable number of results depending on the number of cves impacting the software version (upwards of hundreds at times). The method iterates through all the results making a copy of each result 904: result := result.

2. Once the vulnerabilities list has been grouped by software. It is then converted into a json data structure returned to the front end. The front end displays a count of vulnerabilities and up to three cve names in a tooltip. Unfortunately the entire list of cves is returned in the response, bloating the memory and the response json size.

Will open a new issue to deal with number 1.
Will address number 2 as part of this issue. The suggested fix is to truncate the list of cve returned from the api and allow fleet premium users to override this default, similar to what was done in ticket #23710.

[xpkoala]

Tested in a loadtest environment with ~58k hosts enrolled and ~82k software entries. While hitting the software/versions endpoint repeatedly (15 times per minute) I did not see any increase in ram usage. Memory utilization stayed constant at roughly %3 usage of max available RAM.

[jmwatts]

Looks like a new regression: #24765

Unable to filter on CVE severity on software/versions page

[jmwatts]

Copying test plan steps from #24765 that were completed. Will add additional info once this is fully tested.

fleet free
-> no vulnerability filter

• &without_vulnerability_details=false - should ignore and still show without details
• &without_vulnerability_details=true - should show without details
  -> vulnerability filter
• &without_vulnerability_details=false - should ignore and still show without details
• &without_vulnerability_details=true - should show without details

fleet premium
-> no vulnerability filter

• &without_vulnerability_details=false - doesn't show details

• &without_vulnerability_details=true - doesn't show details, is performant
  -> vulnerability filter

• &without_vulnerability_details=false - shows details, is heavy for large page sizes

• &without_vulnerability_details=true - doesn't show details, is slightly less heavy for large page sizes

• Also should test Free with vulnerability filters to confirm that we get license errors there.

• Verified in UI that only Vulnerable software filter is available on Fleet free, and all available filters are functional on Fleet free and Fleet premium

[jmwatts]

QA Notes:

Per discussion with @ksykulev and @iansltx this fix + changes from #24765 won't necessarily resolve the whole problem, but this fix allows for a performance improvement on the software/versions page when we are not filtering by Severity or Known exploits.

For all scenarios other than when applying Severity or Known exploits filters, the &without_vulnerability_details=true flag will avoid the costly joins on CVE scores for all known vulnerabilities.

After understanding the changes implemented and testing for regressions, this change looks good to release.