Run Windows MDM load test on main

[lukeheath]

Task

Conduct Windows MDM test:

• 30,000 Windows hosts
• Use reference architecture for 50,000

Condition of satisfaction

For all the tests these metrics can be considered a "norm" when it comes to performance

Test plan

1. Enroll 30k hosts, wait for the enrollment to finish, confirm the baseline DB and server load
2. Transfer 30k hosts to a team with no Profiles > Check DB and server load
3. Add 15 profiles to the team with these hosts > Confirm perf > Wait to stabilize
4. Add 15 profiles to a different team > Transfer all 30k hosts to that team > Confirm perf > Wait for it to settle
5. Add 15 other profiles to a different team > Transfer all 30k hosts to that team > Confirm perf

[getvictor]

osquery perf switches I use:

  default     = [
    "--start_period", "20m",
    "--logger_tls_period", "120s",
    "--orbit_prob", "1.0",
    "--mdm_prob", "1.0",
    "--mdm_check_in_interval", "1m",
    "--http_message_signature_prob", "0",
    "--os_templates", "windows_11"
  ]

[ksykulev]

Metrics from OTEL

Enrollment

High-latency endpoints

Endpoint	P99	Calls	Notes
POST /api/osquery/distributed/write	363ms	15,204	Hosts reporting query results
mysql.UpdateHostSoftware	217ms	7,528	Software inventory inserts
GET /fleet/device/{token}/desktop	85ms	155,246	Fleet Desktop status check
HEAD /fleet/device/{token}/ping	84ms	201,232	Device ping

High-volume endpoints

Endpoint	P99	Calls
POST /api/fleet/orbit/config	37ms	1,552,526
POST /api/osquery/config	35ms	776,165
POST /api/osquery/distributed/read	11ms	4,657,138
POST /api/osquery/log	11ms	388,080
HEAD /api/fleet/device/ping	1.4ms	4,644,367

POST /api/osquery/distributed/write — slowest trace: 681ms

Span	Duration	% of Total
mysql.UpdateHostSoftware	267ms	39%
→ SELECT id, name, source... FROM software	130ms	19%
sql.tx.commit (various)	~94ms	14%
UPDATE hosts SET label_updated_at	39ms	6%
SELECT 1 FROM host_operating_system	19ms	3%
UPDATE host_disks	18ms	3%
Everything else	~114ms	17%

UpdateHostSoftware is 39% of the total. The software SELECT query is 130ms (19%). The rest is scattered across many small DB operations.

POST /api/fleet/orbit/config — slowest trace: 1,504ms

Span	Duration	% of Total
SELECT mwe.host_uuid FROM mdm_windows_enrollments mwe JOIN hosts h ON...	1,496ms	99.4%
Everything else	8ms	0.6%

A single query on mdm_windows_enrollments JOIN hosts has a P99 of 37ms, but the tail latency is poor.

Adding 15 profiles to a team. Then moving 30k hosts from that team to a team with 0 profiles.

Operation	P99	Calls
POST /api/mdm/microsoft/enroll	59.6s	30,057	MDM enrollment per host
POST /configuration_profiles	36.8s	50	Uploading profiles
activity.mysql.NewActivity	23.4s	30,032	Activity log per host
POST /api/mdm/microsoft/policy	16.4s	30,149	Policy sync per host
SELECT host_uuid FROM (subquery)...	13.1s	1,178	Profile manager batch query
hosts/transfer/filter	7.3s	1	The actual transfer call
SELECT hmwp...	2.8s	49	Profile status query
DELETE FROM windows_mdm_command_queue	2.1s	899	Command queue cleanup
cron: mdm_windows_profile_manager	1.1s	19,969	Profile manager cron

Query	Calls	Notes
SELECT id, mdm_device_id... FROM mdm_windows_enrollments	53M	Per management request
SELECT hm.host_id... FROM host_mdm	37.5M	Per orbit/config
SELECT mwe.host_uuid FROM mdm_windows_enrollments JOIN hosts	37.4M	Per orbit/config
POST /api/mdm/microsoft/management	35.4M	MDM check-ins
SELECT EXISTS(... FROM windows_mdm_command_queue)	17.7M	Per management check-in
INSERT INTO host_mdm_windows_profiles	68K	Profile status rows
DELETE FROM host_mdm_windows_profiles WHERE host_uuid = ?	30K	Profile cleanup on transfer

microsoft/enroll — 70.7s trace

Span	Duration	%
activity.mysql.NewActivity	6,001ms	8.5%
SELECT hm... FROM host_mdm	59ms
INSERT INTO mdm_windows_enrollments	50ms
begin_tx	49ms
Everything else (15+ queries)	~80ms
Unaccounted gap	~64,400ms	91%

I saw a NewActivity 52s span within a 60s enroll trace.

Operation	Duration (ms)
POST /api/mdm/microsoft/enroll	60,394.3
activity.mysql.NewActivity	52,030.3
INSERT INTO wstep_serials () VALUES ()	58.7
... lots of smaller spans

The NewActivity span is 52 seconds but its only child INSERT INTO activity_past is 1.7ms. Likely blocked on connection pool or table-level lock contention with 30K concurrent enrollments all trying to write to activity_past simultaneously

mdm_windows_profile_manager cron — 28.5s trace

Span	Duration	%
SELECT host_uuid FROM (subquery...)	17,471ms	61%
SELECT hmwp... profile status	2,577ms	9%
SELECT ds... desired state	195ms
15x INSERT INTO host_mdm_windows_profiles	~85-115ms each
15x INSERT INTO windows_mdm_command_queue	~85-107ms each

[ksykulev]

load test results

✅ Enroll 30k hosts, wait for the enrollment to finish, confirm the baseline DB and server load
✅ Transfer 30k hosts to a team with no Profiles > Check DB and server load
✅ Add 2-3 profiles to the team with these hosts > Confirm perf > Wait to stabilize
❌ Add 30 profiles to a different team > Transfer all 30k hosts to that team > Confirm perf > Wait for it to settle
❌ Add 30 other profiles to a different team > Transfer all 30k hosts to that team > Confirm perf