Add option to mlogfilter to find bad query ratio (docsExamined vs nreturned)

[lukasvosyka]

Hi,

we are using Atlas in production and it happens that we receive alerts about "Query Targeting: Scanned Objects / Returned has gone above 1000".

The problem we are having is to pin down the root cause with the tools on hand. One reason for this is, that the queries in question still run quickly enough to not pop up in the Profile nor the Performance Advisor. So last resort is to use the incredible mtools for this.

I quickly realized, that mtools doesn't have this feature yet, to spot queries, that are leading to this kind of Atlas alert (which can become quite annoying :)) so I thought this would be a good enhancement, especially for Atlas users having collections with only a few thousand documents.

I spotted mlogfilter to be a good candidate for this kind of feature. Maybe a new flag --queryratio 1000 would be a good fit?

Best,
Lukas

[stennie]

The existing mlogfilter --scan uses a similar heuristic to look for inefficient queries, however the ratio and number of scanned documents aren't adjustable at the moment

Thanks for the PR! We'll consider how best to merge.

One reason for this is, that the queries in question still run quickly enough to not pop up in the Profile nor the Performance Advisor.

Queries that are inefficient but faster than the slowOpThresholdMs won't show up in logs, profiling, or Performance Advisor.

If you are receiving Performance Advisor alerts for queries that aren't in the logs, I would contact Atlas support for further explanation. Actionable alerts would be better than log forensics (although it doesn't hurt to have a backup approach).

Regards,
Stennie

[lukasvosyka]

Hi @stennie ,

If you are receiving Performance Advisor alerts for queries that aren't in the logs, I would contact Atlas support for further explanation. Actionable alerts would be better than log forensics (although it doesn't hurt to have a backup approach).

I agree with you and also contacted Atlas Support in the first place, asking for an efficient way to spot the root cause of such alerts with the tools at hand. The answer though was more about the current cause and which query wasnt indexed by showing me the log lines in question :) ..but no answer to my real question. So I thought I'd go this route.

The existing mlogfilter --scan uses a similar heuristic to look for inefficient queries, however the ratio and number of scanned documents aren't adjustable at the moment

If I understand the code correctly, I think --scan does a check on the ratio of keysExaminded vs documents returned. I was looking for docsExamined vs documents returned.

An alternative could be to change ´--scanto something like--scanIndex [RATIO]and the introduced flag to--scanDocs [RATIO]` and both do the same but first uses keysExamined and the latter docsExamined. :-)

Best,
Lukas