OS versions: 502s after upgrading v4.73.1 -> v4.73.2

[BCTBB]

Fleet version: v4.73.2

Web browser and operating system: N/A

---

💥  Actual behavior

/api/latest/fleet/os_versions

The following query is returning approximately 1.15M records over 2 minutes. Details in linked 🧵 (More info)

SELECT
	os.id as os_id,
	sc.cve,
	sc.resolved_in_version,
	MIN(sc.created_at) as created_at
FROM software_cve sc
JOIN kernel_host_counts khc ON khc.software_id = sc.software_id
JOIN operating_systems os ON os.os_version_id = khc.os_version_id
WHERE os.id IN (<redacted>)
AND khc.hosts_count > 0
AND khc.team_id = <redacted> GROUP BY os.id, sc.cve, sc.resolved_in_version;

🛠️ To fix

TBD

🧑‍💻  Steps to reproduce

1. TODO
2. TODO

🕯️ More info (optional)

1. Related to Increased DB Load and 502s After upgrading v4.72.0->v4.73.1 #33147
2. Link to 🧵

[ksatter]

@lukeheath Tagging you on this prioritization.

[getvictor]

We originally thought the issue was due to the Kernel changes introduced in 4.73. The new index + query reorganization we added in 4.73.2 didn't help enough. I think Kernel vulns are still part of the problem, but the real issue is that we're querying for too much data from DB. The customer has >800 OS'es, and the server code is fetching details and all the vulnerabilities (including CVSS) for each one of them. Some Linux OSes can have 1000-2000 vulnerabilities.

@rachaelshaw I recommend enforcing a maximum page size of 20 for this endpoint. Currently, the server is not even using page size during the MySQL queries, which is part of the issue. /api/_version_/fleet/os_versions is the API endpoint

[noahtalerman]

I recommend enforcing a maximum page size of 20 for this endpoint.

Up to @rachaelshaw

@getvictor not sure I understand how a maximum page size of 20 would help...the UI is already using a page size of 20.

the server is not even using page size during the MySQL queries

This approach make sense to me. If the UI, and thus the API, is only asked for 20 items, why query for more than that?

[jmwatts]

@getvictor @BCTBB does this need to be internally reproduced before proceeding?

[ksatter]

@jmwatts We have this happening in a cloud environment. I don't know if that changes things in terms of reproduction, but thought it was pertinent.

[ksatter]

@lukeheath @rachaelshaw Customer has requested that we escalate this to a P1 as their vulnerability workflows are currently broken.