Details
-
Bug
-
Status: Closed
-
Critical - P2
-
Resolution: Works as Designed
-
2.6.4, 2.7.6
-
None
-
ALL
-
Description
I found really bad performance on mongodb 2.6.4 (... 2.6.latest, 2.7.latest)..
It seams that Query Plan Cache is always empty, and for each single query (not working over a specific index), mongodb performs a Query Plan.
Performance are good only if hint() operator is specified with the query. I tested the same scenario on mongodb 2.4.3 but everything works fine.
the test query
db.test26.find({"user":"mario","removed":false,"objId":3}).explain()
|
|
Explain result:
|
|
{
|
"cursor" : "BtreeCursor user_1_removed_1_a_1",
|
"isMultiKey" : false,
|
"n" : 2,
|
"nscannedObjects" : 210000,
|
"nscanned" : 210000,
|
"nscannedObjectsAllPlans" : 482615,
|
"nscannedAllPlans" : 524601,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 5158,
|
"nChunkSkips" : 0,
|
"millis" : 1072,
|
"filterSet" : false
|
}
|
I tried to run these commands:
db.test26.find({"user":"mario","removed":false,"objId":3}).explain()
|
db.test26.find({"user":"mario","removed":false,"objId":3})
|
db.test26.find({"user":"mario","removed":false,"objId":3}).hint("user_1_removed_1_a_1")
|
getting the following log output:
# log for query with explain true: [conn11] query test.test26 query: { query: { user: "mario", removed: false, objId: 3.0 }, $explain: true } planSummary: IXSCAN { user: 1.0, removed: 1.0, a: 1.0 } ntoreturn:0 ntoskip:0 nscanned:210000 nscannedObjects:210000 keyUpdates:0 numYields:1685 locks(micros) r:2127426 nreturned:1 reslen:3291 1072ms
|
# log for query without explain: [conn11] query test.test26 query: { user: "mario", removed: false, objId: 3.0 } planSummary: IXSCAN { user: 1.0, removed: 1.0, a: 1.0 } ntoreturn:0 ntoskip:0 nscanned:210000 nscannedObjects:210000 keyUpdates:0 numYields:1 locks(micros) r:1298548 nreturned:2 reslen:424 981ms
|
# log for query with hint: [conn11] query test.test26 query: { query: { user: "mario", removed: false, objId: 3.0 }, $hint: "user_1_removed_1_a_1" } planSummary: IXSCAN { user: 1.0, removed: 1.0, a: 1.0 } ntoreturn:0 ntoskip:0 nscanned:210000 nscannedObjects:210000 keyUpdates:0 numYields:1 locks(micros) r:777390 nreturned:2 reslen:424 390ms
|
The query number 2 has the same performance of the query with the explain command, and I suppose that every time the query runs the mongodb server perform a full query plan!!! After the execution of the query, the Query Plan Cache seams to be empty
db.collection.getPlanCache().listQueryShapes() = []
|
Additional notes
If I declare other indexes on the same collection the performance of query downgrade!
mongodb 2.4.3 log for the same test:
1st run of the query: [conn2] query test.test26 query: { user: "mario", removed: false, objId: 3.0 } ntoreturn:0 ntoskip:0 nscanned:1470000 keyUpdates:0 numYields: 5 locks(micros) W:5252 r:8464649 nreturned:2 reslen:424 4381ms
|
2nd run of the query: [conn2] query test.test26 query: { user: "mario", removed: false, objId: 3.0 } ntoreturn:0 ntoskip:0 nscanned:210000 keyUpdates:0 locks(micros) r:652975 nreturned:2 reslen:424 652ms
|
Attachments
Issue Links
- related to
-
SERVER-13675 Plans with differing performance can tie during plan ranking
-
- Closed
-
-
SERVER-14525 Perf regression in 2.6.2 caused by not caching plans that tie during plan ranking
-
- Closed
-