so it never gets removed

(the data never gets deleted)

which should happen every 5-minutes or so, and after restart

and scheduled queries:

"file_events": {
      "query": "SELECT * from file_events;",
      "removed": false,
      "interval": 30
"socket_events":{
      "query": "SELECT s.action, s.auid, s.family, s.local_address, s.local_port, s.path, s.pid, s.remote_address, s.remote_port, s.success, s.time, p.cmdline, p.cmdline_size, p.parent, p.uid, p.euid FROM socket_events s JOIN process_events p ON p.pid = s.pid WHERE s.action='bind';",
      "removed": false,
      "interval": 60
    },
    "process_events": {
      "query": "SELECT auid, cmdline, ctime, cwd, egid, euid, gid, parent, path, pid, time, uid FROM process_events WHERE path NOT IN ('/bin/date', '/bin/mktemp', '/usr/bin/dirname', '/usr/bin/head', '/bin/uname', '/bin/basename');",
      "removed": false,
      "interval": 30

not actual config just pasted here

the scheduled queries on the event tables are the same across the 50 or so boxes running osqueryd, but there are 6 or 7 which this problem. all same OS.

I mean socket_events

oh, I see it

IF you have only one query per event table, you can set events_expiry=1 , which means it clears after access

I recommend getting sst_dump and running it on the files to see what's in the DB

see this one: https://github.com/facebook/osquery/issues/4333

I've added events_expiry=1 and it lasted a few hours, but is now stuck in that same loop. this particular box generates about 450 process_events in a 30 second window

It seems like the rocksdb indexes are never deleted (but data is), and it means osquery is wasting time processing all the unnecessary indexes.

@shed7 I added a comment in https://github.com/facebook/osquery/issues/4333 . If you can try adding that LOG line, and let me know if it's happening, that would help. It's a tough one to reproduce, as devs who have looked into this issue see that all works fine for them.

this scenario only happens in my specific machines

have you reproduce this scenario in your machines? @packetzero

even as you said

Indexes are only being deleted if all queries have been completed. But I don't think the query results are posted yet

how can we avoid this?

This is why you should use a vendor's osquery. Vendors are the only ones motivated to fix issues.

BTW, do you see logging lines like this: I0210 230337.108081 69574656 rocksdb.cpp:68] RocksDB: [WARN] [db/column_family.cc:675] [queries] Stalling writes because we have 15 immutable memtables (waiting for flush), max_write_buffer_number is set to 16 rate 16777216

I just find another strange things that some of my machines have no process_events record but have only socket_event, when I run

curl

. it seems that the machine does not record the process_events. but the status of audit is normal.

I have no info like

RocksDB: [WARN] [db/column_family.cc:675] [queries] Stalling writes because we have 15 immutable memtables .....