-
Type: Bug
-
Resolution: Done
-
Priority: Critical - P2
-
None
-
Affects Version/s: 2.6.4, 2.7.6
-
Component/s: Performance, Querying
-
ALL
-
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
- 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