Improve GitOps speed when managing 100+ teams

[AdamBaali]

• @marko-lisica : customer-deebradel has a large number of teams that they manage through GitOps, which results in long GitOps runs (~50 minutes). They're already using the hash_sha256 key in their software YAML to speed things up (stops re-downloads if the app already exists in Fleet).
  • @marko-lisica: Eventually Fleet could find ways to enhance the speed of GitOps runs for many teams in a synchronous manner before exploring concurrency.

---

[AdamBaali]

Monday 27th fleetctl debug archive: https://fleetdm.slack.com/archives/C075TURNLB0/p1761549623449769?thread_ts=1761497052.170009&cid=C075TURNLB0

Tuesday 28th fleetctl debug archive: https://fleetdm.slack.com/archives/C075TURNLB0/p1761651575744019?thread_ts=1761497052.170009&cid=C075TURNLB0

[rfairburn]

This looks to be due to consistent high load from SELECTS to the nano_enrollement_queue table. It could also be what is being joined. It looks like there is some full table scan action going on based on the wait type:

[AdamBaali]

Update (Oct 28)

After scaling Aurora from 2 → 3 read replicas, GitOps dry runs still fail with 502 / timeout errors.

Database metrics indicate lock contention rather than resource exhaustion the writer is mostly idle, but read replicas show high load with blocking around nano_enrollment_queue and software-related queries.

Debug archives:

• 27 Oct
• 28 Oct

[JordanMontgomery]

I've spent a good bit of time over the past couple days looking at this. It seems like the nano_enrollment_queue is growing very large for some customers and in this case it's due almost entirely to ipad/ios refetch commands. We'll need to see if we can trim it down a bit for this customer and longer term come up with a solution to clean it up so it doesn't grow so large

[rfairburn]

I'd want to make sure that nano_command_results at least (and possibly nano_commands if we can ensure all are Ack'd) get cleaned up as well as the bad query is joining at least the command_results:

SELECT DISTINCTROW `neq` . `id` FROM `nano_enrollment_queue` `neq` INNER JOIN `nano_enrollments` `ne` ON `ne` . `id` = `neq` . `id` LEFT JOIN `nano_command_results` `ncr` ON `ncr` . `command_uuid` = `neq` . `command_uuid` AND `ncr` . `id` = `neq` . `id` WHERE `neq` . `active` = ? AND `ne` . `enabled` = ? AND `ncr` . `status` IS NULL AND `neq` . `created_at` >= NOW ( ) - INTERVAL ? SQL_TSI_DAY LIMIT ?