Hi!
SUMMARY
As already mentioned in the topic, I have a query which is using a wrong index and is not logged as a slow operation, even it is running for 200+ seconds and all other queries running for more than 20ms are logged.
SETUP
Application Host: is using mongo-csharp-driver version 2.27.0
MongoDB Cluster: is running with version 7.0.12 (not Atlas)
The Application-Host is executing about 6 queries in parallel like the following example with different values:
db.report.find(
{
“Id1”: {
“$in”: [ ]
},
“Id2”: {
“$in”: [ ]
},
“Id3”: ,
“Id4”: “”
},
{
“Id1”: 1,
“Id2”: 1,
“value”: 1,
“isNull”: 1
}
)
Collection “report” provides the following indexes:
db.report.getIndexes()
[
{ v: 2, key: { _id: 1 }, name: ‘id’ },
{
v: 2,
key: { Id1: 1, Id2: 1, Id3: 1, Id4: 1 },
name: ‘cust_index1’
},
{
v: 2,
key: { Id2: 1, Id3: 1, Id4: 1 },
name: ‘cust_index2’
},
{
v: 2,
key: { Id1: 1, Id2: 1 },
name: ‘cust_index3’
},
{ v: 2, key: { Id2: 1, rank: 1 }, name: ‘Id2_1_rank_1’ }
]
PROBLEM
If executed by the C SHARP driver, the query plan is always using index { Id2: 1, Id3: 1, Id4: 1 }.
This leads to a very high amount of disk-io.
If the exact same query is executed directly ins the mongodb shell, the correct index { Id1: 1, Id2: 1, Id3: 1, Id4: 1 } is used and the query performance is good.
I found a similar issue here, but in this case a Java driver was used and the issue could be fixed by using a new driver version:
We are already using pretty new version (a newer version was just released a few hours ago).
I think we are able to fix this issue by setting a hint to the find query.
But I really wonder why the slow query is also not logged as a slow operation.
Any ideas?
As already said: Logging is working and we see slow log operations running for less than 100ms in the logs.
Thanks in advance,
Tobias