It looks like the process_open_sockets joined with...
# windows
It looks like the process_open_sockets joined with processes query is the biggest contributor. When does osquery dump the results to the db when there is a join? Given the tables may end up being called a large number of times (~170 processes and ~80 sockets). Would osquery write entries for each call to the table?
how are you determining this?
did you do an sst_dump on the database?
If you set disable_events=true , and restart osqueryd I would expect the IO stats to be smaller. I'm guessing it's windows_events writing lots to disk
I haven't, I was using procmon to see where all the writes were going - this just showed most of the calls going to the .log and .sst files. I then changed my configured scheduled queries to remove everything except the joined query and still got the large numbers vice versa with the windows_events table saw the numbers come down
I'll give it a go with the events disabled
I'll have to check that tomorrow, thanks for your help. I'll also try dumping the db and see what's actually going into it
Turns out its a combination of things (but mostly you were right about it being events). First the new process table causes some verbose error messages about trying to get cwd for system processes - which when verbose is on those messages ended up being written to the database. So when my join happened the process table ended being called 17 times (the number of ports open) leading to a bunch of entries in the db . Second the processes table now seems to cause a large number of calls to enumerate users local group membership - which in turn leads to lots of windows events - which get written to the database. I saw ~900 events appear after running the suspect query. For comparison when I run the same query with 3.3.2 (on the same machine) I only saw 8 entries...
Looks like in the changes to processes in experimental the constraint checking on pid was removed so now every time the processes table is called it runs for every process whereas previously it only collected data for specified pids (from the join)
What logger are you using? It could be buffering results and status logs in DB.
You are right, the new processes table does not implement pid index, which is costly. It's not hard to add.
Ah nice, I hadn't seen that PR.
I was using aws_kinesis which the docs suggest would buffer and flush based on aws_kinesis_period. I didn't realise the buffering was done in the DB