List script timeout on specific hosts due to large number of script results

[mason-buettner]

Fleet version: 4.72

Web browser and operating system: Chrome 139 on Windows 11 Pro 24H2

---

💥  Actual behavior

customer-ampella reports that hosts with a high number of host_script_results records fail to return the list of available scripts to run. When they attempt to navigate to the run script action (https://dogfood.fleetdm.com/hosts/HOST/details > Actions > Run script), the dialogue hangs and then displays an error as shown below:

When attempting to list scripts for one of the problematic hosts, the request hangs and times out.

🛠️ To fix

TBD

Product designer: _________________________

🧑‍💻  Steps to reproduce

1. Identify a host with a high number of records in the host_script_results table.
2. Attempt to list the scripts available to run on the system either via the UI or the API.
3. Observe timeout.

🕯️ More info (optional)

The hosts customer-ampella sees this behavior on have over 40,000 records in the host_script_results table. The query and redacted results used to identify affected hosts:

SELECT host_id, COUNT(*) as Totals FROM host_script_results group by host_id ORDER BY Totals ASC ;

|   [host_id_0] | 42526 |
|   [host_id_1] | 46263 |

I have been unable to personally reproduce due to a lack of access to an environment with an equivalent number of records in the specified table.

[Sampfluger88]

Linked to Unthread ticket:

Run Script Bug: Timeout and 502 Error #8076

[getvictor]

I reproduced the issue in local tests.

Benchmark Results Show Exponential Growth:

• 100 records: 1.6ms
• 1,000 records: 223ms (140x slower)
• 5,000 records: 5.3 seconds (24x slower than 1k)
• 10,000 records: 18.6 seconds (3.5x slower than 5k)
• 20,000 records: Would take ~74 seconds
• 40,000 records: Would take ~300+ seconds (5+ minutes)