[SERVER-8692] Add logging to indicate when cached query plan changes Created: 24/Feb/13  Updated: 11/Jul/16  Resolved: 24/Jan/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: None
Fix Version/s: 2.5.5

Type: Improvement Priority: Major - P3
Reporter: Daniel Pasette (Inactive) Assignee: Benety Goh
Resolution: Done Votes: 4
Labels: query_triage
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
is duplicated by SERVER-12358 Add query plan history and logging Closed
Related
related to SERVER-9152 Ability to get the list of remembered... Closed
is related to SERVER-8871 Support admin hints per query pattern Closed
is related to SERVER-666 query optimizer introspection Closed
Backwards Compatibility: Fully Compatible
Participants:

 Description   

This information can be useful to detect when a query plan is oscillating between a good and a bad plan.

Things exit the cache for:
1. safety reasons (index add/drop)
2. no more space in cache
3. performance degrades
4. writes (changes data distribution)
5. explicit commands to mutate cache

Let's log when this happens w/level...1.

The query optimizer currently generates a query plan which is cached and re-used based on the normalized "shape" of the query predicate and sort criteria.

It would be useful to be able to identify plans by id (e.g., hash value) for tracking, hinting and logging.
Tracking

Store the history when the query plan is changed and what it was changed to in the db's system.plans collection.
Diagnostics
Log every time the plan changes for a given query, e.g., <query_id>: changed <old_plan_id> to <new_plan_id>



 Comments   
Comment by Githook User [ 28/Jan/14 ]

Author:

{u'username': u'benety', u'name': u'Benety Goh', u'email': u'benety@mongodb.com'}

Message: SERVER-8692 additional test cases for queries against null to document existing behavior.
Branch: master
https://github.com/mongodb/mongo/commit/692155f68924f19cbaae2892e56aa5edbf62fca2

Comment by Daniel Pasette (Inactive) [ 13/Jan/14 ]

christopher.price@mtvn.com, just saw your comment from september. I believe the feature you're looking for is reflected in SERVER-3443, scheduled for 2.5.5.

Comment by Christopher Price [ 06/Sep/13 ]

Maybe this should be a separate ticket, but how difficult would it be to add the index that was used on the query in the "slow query" log?

Comment by Christopher Price [ 26/Jun/13 ]

Based on previous discussion: looking forward to seeing the index that is used when
a) a query plan is used for the first time and
b) the index that is used on a query plan changes.

The benefits of this is that we can then report/review when query plans oscillate between 2 or more indexes and also get a concrete idea of which indexes are actually used (or not used) in our databases.

Generated at Thu Feb 08 03:18:07 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.