High DB load during a cron job with top SQL: `DELETE FROM host_software WHERE host_id = ? AND software_id IN (...)`

[AndreyKizimenko]

Fleet version: 4.76

Web browser and operating system: N/A

---

💥  Actual behavior

{"component":"http","err":"error in query ingestion","host_id":"host_id"-1,"ingestion-err":"ingesting query software_macos: update host software: delete host software: Error Number-2 (Token-3): Lock wait timeout exceeded; try restarting transaction","ip_addr":"ip_addr"-4,"level":"error","method":"POST","took":"took"-5,Token-6,"uri":"/api/osquery/distributed/write","x_for_ip_addr":"x_for_ip_addr"-7}

Top SQL: DELETE FROM host_softwareWHEREhost_id= ? ANDsoftware_id IN (...)

Thread with more info: https://fleetdm.slack.com/archives/C051QJU3D0V/p1762436321236109

🛠️ To fix

TODO

🧑‍💻  Steps to reproduce

1. Spin up a loadtest instance using default parameters with 100k hosts
2. Wait for several cron jobs to complete
3. Check server logs
4. Check DB performance on the Writer instance during the cron jobs

🕯️ More info (optional)

This is a released bug, confirmed that the same errors happen on 4.75 loadtest instance

[getvictor]

Proposal:

• don't retry (assume it will be fixed in the next refresh)
• still update the timestamp (no change, just a note)

@noahtalerman OK with not retrying. We will retry naturally in 1 hour, or IT admin can manually refresh.

Work:

• Don't retry this query (1 line change?)
• Run loadtest (100k hosts) to verify fix; note the vulnerability run times for reference (look in cron_stats table)

Agreed on 3, assuming Andrey will help spin up load test.