Let me maybe recap with pictures:
First, yes, I can see Query targetting metric is wild:
So, performance advisor and query profiler should tell something, I thought. But not much infortunately, performance advisor is green. Query profiler at best produces:
Here, typically we produce at most “command” queries with typically around 300 docs (scanned to returned ratio). However, if I actually click on some random query with around 300 ratio, I see that 300 is a bit misleading, because proper value is N/A (returned is 0):
If I see more details, it shows it is getMore, which as far as I understand, operates on oplog, so no index are possible here at all.
The thing is,why this is around 300 in the first place? Taking quick look at the envs, 300 is approximate nuber of active watch queries, each probably making under the hood getMore query. We have like number of databases over 30, each with some number of collections. Each collection has like typically 1-2 watchers at worst. Therefore, I have impression that all watchers across databases and collections, when they watch something, they look into same collection and this is NOT covered by any index, because it is oplog? Also, session updates are dominant queries:
Queries of their own have nothing much:
I cant help but feel that MongoDB has something non optimized there. It produces probably some updates for each individual watch session (in internal collection), and querying those are outside of index. And this reports high query targeting alert. But again, I dont feel like I have any influence over it. I noticed long time ago those watch queries are very slow and non-scalable (hebce I have now central watch query per each collection from which I can support thousands of smaller watchers). But I thought at least that watchers across collections & databases should not interfere with each other that much. But it looks that MongoDB has issues even with this? Or am I wrong? But if so, no docs points me to any solution.
Thanks.