[SERVER-23332] Expose query plan cache key in system.profile entry and query log lines Created: 24/Mar/16  Updated: 13/Aug/18  Resolved: 31/Jul/18

Status: Closed
Project: Core Server
Component/s: Logging, Querying
Affects Version/s: None
Fix Version/s: 4.1.2

Type: Improvement Priority: Minor - P4
Reporter: Shakir Sadikali Assignee: Haley Connelly
Resolution: Done Votes: 3
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
is documented by DOCS-11965 Docs for SERVER-23332: Expose query p... Closed
Duplicate
is duplicated by SERVER-34455 Consider distinguishing parsed query ... Closed
is duplicated by SERVER-30859 Hash query shapes when logging Closed
Related
is related to SERVER-34455 Consider distinguishing parsed query ... Closed
Backwards Compatibility: Fully Compatible
Sprint: Query 2018-07-16, Query 2018-07-30, Query 2018-08-13
Participants:
Case:

 Description   

It would be nice if we included a hash of all queries (without their binds) so we can get a sense for unique types of queries being run on a system and to be able to get a better performance profile for "bad" queries. If we could add that hash into the mongod.log we can easily "group" queries for prioritization when telling the customer what to "tweak".



 Comments   
Comment by Githook User [ 31/Jul/18 ]

Author:

{'name': 'Haley Connelly', 'email': 'haley.connelly@10gen.com', 'username': 'haleyConnelly'}

Message: SERVER-23332 Expose query plan cache key in system.profile entry and query log lines
Branch: master
https://github.com/mongodb/mongo/commit/075d7fe467e12aa3dfa19aff27672a7a5b34aff0

Comment by Ian Whalen (Inactive) [ 08/Jun/18 ]

Just a note that this should be expanded to include the plan cache introspection commands.

Comment by David Storch [ 28/Aug/17 ]

Copying in some more context from kevin.arhelger in SERVER-30859:

  1. Especially long queries are truncated in the mongod log file.
  2. Parsing the shape of queries from a log line is difficult.

I propose two improvements.

  1. Always print a hash of the query shape. This eliminates the issue of #1 as we can now group queries by hash. This also will speed log analysis, eliminating the need to parse the query.
  2. Add an administrative command to return a query shape from a hash in the plan cache.

Not only would this speed analysis of slow queries but would be helpful with log redaction. The analysis can be performed on just the hash with the associated metadata and the customer can investigate the actual queries independently.

Comment by David Storch [ 25/Mar/16 ]

shakir.sadikali thanks for elaborating. I do believe that if we map this request from Oracle terminology to MongoDB terminology, the idea is to expose the plan cache key in system.profiler (and probably also in the logs). I am going to update the summary of the ticket accordingly, as this will allow the query team developers to more quickly understand the feature request. Please let me know if you think my interpretation is not accurate.

Comment by Shakir Sadikali [ 24/Mar/16 ]

david.storch Perhaps we're using different terminology. Ideally, I would parse out all the bind variables and then create a "hash key" of the query text and use that as a unique identifier to any unique parsed query. I would then be able to use that to perform reporting (i.e. how often is a query executing, what's it's plan, number of I/O aggregate and per exec, time on CPU...etc. This would be analogous to v$sql_area, v$sql_plan, etc. in Oracle. I consider system.profile analogous to v$active_session_history.

Comment by David Storch [ 24/Mar/16 ]

shakir.sadikali, I'm not sure I understand this request. Are you suggesting that we add the internal plan cache key to the query log line? The plan cache key is a sequence of bytes, generated from the parsed query, which we use internally as our concept of "query shape".

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