Docs Menu

$queryStats (aggregation)

버전 6.0.7에 추가 되었습니다.

$queryStats

경고

The $queryStats aggregation stage is unsupported and is not guaranteed to be stable in a future release. Don't build functionality that relies on a specific output format of this stage, since the output may change in a future release.

기록된 쿼리에 대한 런타임 통계를 반환합니다.

$queryStats collects and reports metrics for aggregate(), find(), and distinct() queries. $queryStats does not collect information for queries that use Queryable Encryption.

The $queryStats stage is enabled on deployments hosted on MongoDB Atlas with a cluster tier of at least M10.

To run the $queryStats stage, your pipeline must meet the following requirements:

  • The pipeline must be run on the admin database.

  • $queryStats must be the first stage in the pipeline.

db.adminCommand( {
aggregate: 1,
pipeline: [
{
$queryStats: {
transformIdentifiers: {
algorithm: <string>,
hmacKey: <binData> /* subtype 8 - used for sensitive data */
}
}
}
],
cursor: { }
} )

중요

You cannot run $queryStats on a specific collection. For complete examples, see 예시.

$queryStats 은 다음 필드를 사용합니다.

필드
필요성
유형
설명

transformIdentifiers

옵션

문서

Specifies additional transformation options for the $queryStats output.

transformIdentifiers
.algorithm

Required if specifying the transformIdentifiers object

문자열

The type of hash transformation applied to namespace information and field names in output. The only currently supported algorithm value is hmac-sha-256.

transformIdentifiers
.hmacKey

Required if specifying the transformIdentifiers object

binData

The private key input in the HMAC transformation.

If your deployment enforces access control, the user running $queryStats must have the following permissions:

  • To run $queryStats without the transformIdentifiers option, the user must have the queryStatsRead privilege action.

  • To run $queryStats with the transformIdentifiers option, the user must have the both the queryStatsRead and queryStatsReadTransformed privilege actions.

The built-in clusterMonitor role provides the queryStatsRead and queryStatsReadTransformed privileges. The following example grants the clusterMonitor role on the admin database:

db.grantRolesToUser(
"<user>",
[ { role: "clusterMonitor", db: "admin" } ]
)

The following sections describe behavioral details of the $queryStats stage.

Statistics for the $queryStats stage are tracked in a virtual collection that is stored in-memory. The memory limit for the virtual collection is 1% of the system's total memory.

$queryStats groups queries with common properties into the same output document. The resulting document is called a query stats entry.

$queryStats groups similar queries together by normalizing user-provided field values to their data types. For example, a filter specified as { item: 'card' } is normalized to { item : '?string'}. $queryStats also normalizes the values of some query options like hint and comment.

$queryStats preserves literal values for options like readConcern and readPreference.

For the complete list of options included in a query stats entry, see find Command Query Shape.

When an HMAC key is specified to the transformIdentifiers option, $queryStats uses the HMAC key to apply an HMAC-SHA-256 hash function on the following data:

  • Document field names

  • Collection names

  • Database names

$queryStats does not apply the HMAC transformation to the following data:

  • MQL keywords such as operator names (for example, $gte).

  • Parameter names such as the partitionBy parameter in $setWindowFields.

  • Field values. $queryStats normalizes field values in a query to their data types (such as number or string) when the query is recorded. $queryStats never stores field values that contain user data.

For an example of transformed output, see Transformed Example.

MongoDB records $queryStats operations in the deployment logs. By default, MongoDB only logs the invocation of $queryStats operations, not the operation's output. For $queryStats operations that include the transformIdentifiers option, you can specify whether the transformed output is included in the log entry.

To learn how to control $queryStats logging behavior, see Toggle $queryStats Log Output.

$queryStats returns an array of query stats entries. Some query stats entry properties contain literal values, and some properties are normalized to group common queries.

Query stats entries contain the following top-level documents:

문서
설명

key

The unique combination of attributes that define an entry in the query stats output. The key contains attributes such as:

Each unique combination of attributes creates a separate entry in the $queryStats virtual collection.

asOf

The UTC time when $queryStats read this entry from the $queryStats virtual collection. asOf does not necessarily return the same UTC time for each result. Internally, the data structure is partitioned, and each partition will be read at an individual point in time.

metrics

Contains aggregated runtime metrics associated with each query stats entry. Each query stats entry records statistics for each query that shares the same key.

Each document in the output array contains the following fields:

필드
유형
Literal or Normalized
설명

key

문서

Literal

Contains the query shape and additional query attributes that group a set of queries together

key.queryShape

문서

Literal

Contains attributes used to group similar queries together. For more information, see Query Shape.

key.client

문서

Literal

Describes client information associated with the key

key.client.application

문서

Literal

The client application name

key.client.driver

문서

Literal

Describes the driver used to issue the query

key.client.driver.name

문자열

Literal

Name of the driver used to issue the query. Possible values include mongosh and nodejs.

key.client.driver.version

문자열

Literal

Version number of the driver used to issue the query

key.client.os

문서

Literal

Describes the operating system used by the client that issued the query

key.client.os.type

문자열

Literal

Type of the operating system

key.client.os.name

문자열

Literal

Name of the operating system

key.client.os.architecture

문자열

Literal

Architecture of the operating system. Possible values include arm64 and x86_64.

key.client.os.version

문자열

Literal

Version number of the operating system

key.readConcern

문서

Literal

The readConcern for the key

key.collectionType

문자열

Literal

The type of collection the query was issued on. For more information, see Collection Type.

key.hint

Document or String

Normalized

The index that was used as a hint for the query

key.batchSize

문자열

Normalized

The batch size for the key. Batch size specifies the number of documents to return in each batch of the response from the MongoDB instance.

key.comment

문자열

Normalized

Comment associated with the key

key.maxTimeMS

문자열

Normalized

최대 시간 (MS) value associated with the key

key.noCursorTimeout

부울

Normalized

noCursorTimeout option associated with the key

key.allowPartialResults

문자열

Literal

allowPartialResults (영문) option associated with the key

key.readPreference

문자열

Literal

Read preference associated with the key

key.apiVersion

문자열

Literal

The Stable API version associated with the key. See Stable API.

key.apiStrict

부울

Literal

The apiStrict parameter value associated with the key. See Stable API Parameters.

key.apiDeprecationErrors

부울

Literal

The apiDeprecationErrors parameter value associated with the key. See Stable API Parameters.

keyHash

문자열

Literal

A hashed representation of the values in the key. Each unique keyHash value corresponds to a unique entry in the $queryStats memory store.

metrics

문서

Literal

Describes runtime statistics for the key

metrics.lastExecutionMicros

NumberLong

Literal

Execution runtime for the most recent query for all queries with the given key

metrics.execCount

NumberLong

Literal

Number of times that queries with the given key have been executed

metrics.totalExecMicros

문서

Literal

Describes the total time spent running queries with the given key. If the query resulted in getMores, totalExecMicros includes the time spent processing the getMore requests. totalExecMicros does not include time spent waiting for the client.

All subfields of totalExecMicros are reported in microseconds.

metrics
.totalExecMicros
.sum

NumberLong

Literal

Total time spent running queries with the given key

metrics
.totalExecMicros
.max

NumberLong

Literal

Longest amount of time spent running a query with the given key

metrics
.totalExecMicros
.min

NumberLong

Literal

Shortest amount of time spent running a query with the given key

metrics
.totalExecMicros
.sumOfSquares

NumberDecimal

Literal

Sum of squares of the total execution times for all queries with the given key. A high sumOfSquares value indicates high variance in query execution times.

metrics
.firstResponseExecMicros

문서

Literal

Describes the time spent from when a query within they key began processing to when the server returns the first batch of results

All subfields of firstResponseExecMicros are reported in microseconds.

metrics
.firstResponseExecMicros
.sum

NumberLong

Literal

Combined amount of time spent from the beginning of query processing to when the server returns the first batch of results

metrics
.firstResponseExecMicros
.max

NumberLong

Literal

Longest amount of time spent from the beginning of query processing to when the server returns the first batch of results

metrics
.firstResponseExecMicros
.min

NumberLong

Literal

Shortest amount of time spent from the beginning of query processing to when the server returns the first batch of results

metrics
.firstResponseExecMicros
.sumOfSquares

NumberDecimal

Literal

Sum of squares of amounts of time spent from the beginning of query processing to when the server returns the first batch of results.

A high sumOfSquares value indicates high variance in query processing times.

metrics.docsReturned

문서

Literal

Describes the number of documents returned by queries within the key

metrics
.docsReturned
.sum

NumberLong

Literal

Total number of documents returned by queries with the given key

metrics
.docsReturned
.max

NumberLong

Literal

Maximum number of documents returned by a query with the given key

metrics
.docsReturned
.min

NumberLong

Literal

Fewest number of documents returned by a query with the given key

metrics
.docsReturned
.sumOfSquares

NumberDecimal

Literal

Sum of squares of number of documents returned by a query within the key.

A high sumOfSquares value indicates high variance in the number of documents returned between individual queries.

metrics.firstSeenTimestamp

날짜

Literal

Time that a query with the given key was first used since the last restart

metrics.lastSeenTimestamp

날짜

Literal

Time that a query with the given key was most recently used

The key.collectionType field indicates the type of collection that the recorded query was issued on. The collectionType can be one of the following values:

필드
설명

changeStream

The query was a change stream operation.

collection

The query was issued on a standard 컬렉션.

nonExistent

The query was issued on a collection that does not exist.

timeseries

The query was issued on a timeseries collection.

view

The query was issued on a view.

virtual

The query was issued on a virtual collection. The following operations occur in virtual collections:

The key.queryShape contains query attributes used to group similar queries together. The fields in key.queryShape vary based on the command that resulted in the query stats entry. $queryStats creates query stats entries for aggregate and find commands.

Each query shape property corresponds to a query option. For example, key.queryShape.sort corresponds to the sort() specification for the query shape.

The following table describes the query shape properties for find commands.

필드
유형
Literal or Normalized

key.queryShape.filter

문서

Normalized

key.queryShape.sort

문서

Literal

key.queryShape.projection

문서

Normalized

key.queryShape.skip

Integer

Normalized

key.queryShape.limit

Integer

Normalized

key.queryShape.singleBatch

부울

Literal

key.queryShape.max

문서

Normalized

key.queryShape.min

문서

Normalized

key.queryShape.returnKey

부울

Literal

key.queryShape.showRecordId

부울

Literal

key.queryShape.tailable

부울

Literal

key.queryShape.oplogReplay

부울

Literal

key.queryShape.awaitData

부울

Literal

key.queryShape.collation

문서

Literal

key.queryShape.allowDiskUse

부울

Literal

key.queryShape.let

문서

Normalized

The following table describes the query shape properties for aggregate commands.

필드
유형
Literal or Normalized

key.queryShape.pipeline

배열

Normalized

key.queryShape.explain

부울

Literal

key.queryShape.allowDiskUse

부울

Literal

key.queryShape.collation

문서

Literal

key.queryShape.hint

String or Document

Normalized

key.queryShape.let

문서

Normalized

To run the examples in this section, start with the following data:

db.products.insertMany(
[
{ item: "card", qty: 15 },
{ item: "envelope", qty: 20 },
{ item: "stamps" , qty: 30 }
]
)

Then, run these commands:

db.products.find( { item: "card" } )
db.products.aggregate( [
{
$match: { qty: { $gt: 20 } }
}
] )

The following examples show the output of $queryStats using different types of data transformation:

Input:

db.getSiblingDB("admin").aggregate( [
{
$queryStats: { }
}
] )

출력:

[
{
key: {
queryShape: {
cmdNs: { db: 'test', coll: 'products' },
command: 'find',
filter: { item: { '$eq': '?string' } }
},
client: {
driver: { name: 'nodejs|mongosh', version: '5.1.0' },
os: {
type: 'Darwin',
name: 'darwin',
architecture: 'arm64',
version: '22.6.0'
},
platform: 'Node.js v16.19.1, LE (unified)',
version: '5.1.0|1.8.0',
application: { name: 'mongosh 1.8.0' }
},
collectionType: 'collection'
},
keyHash: 'dsoJ+LHAru0z6MJ1/IygJnnLTrlpVYYmPnlmNZbZrLI=',
metrics: {
lastExecutionMicros: Long("4254"),
execCount: Long("1"),
totalExecMicros: {
sum: Long("4254"),
max: Long("4254"),
min: Long("4254"),
sumOfSquares: Decimal128("18096516")
},
firstResponseExecMicros: {
sum: Long("4254"),
max: Long("4254"),
min: Long("4254"),
sumOfSquares: Decimal128("18096516")
},
docsReturned: {
sum: Long("1"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Decimal128("1")
},
firstSeenTimestamp: ISODate("2023-09-14T12:30:27.989Z"),
latestSeenTimestamp: ISODate("2023-09-14T12:30:27.989Z")
},
asOf: Timestamp({ t: 1694695007, i: 0 })
},
{
key: {
queryShape: {
cmdNs: { db: 'test', coll: 'products' },
command: 'aggregate',
pipeline: [
{ '$match': { qty: { '$gt': '?number' } } }
]
},
apiVersion: '1',
client: {
driver: { name: 'nodejs|mongosh', version: '5.1.0' },
os: {
type: 'Darwin',
name: 'darwin',
architecture: 'arm64',
version: '22.6.0'
},
platform: 'Node.js v16.19.1, LE (unified)',
version: '5.1.0|1.8.0',
application: { name: 'mongosh 1.8.0' }
},
collectionType: 'collection',
cursor: { batchSize: '?number' }
},
keyHash: '2QLBfL0m1lliStdN4XvBjqVBtZQ6ffaB2L1pJ99twT8=',
metrics: {
lastExecutionMicros: Long("350"),
execCount: Long("3"),
totalExecMicros: {
sum: Long("3084"),
max: Long("2499"),
min: Long("235"),
sumOfSquares: Decimal128("6422726")
},
firstResponseExecMicros: {
sum: Long("3084"),
max: Long("2499"),
min: Long("235"),
sumOfSquares: Decimal128("6422726")
},
docsReturned: {
sum: Long("3"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Long("3")
},
firstSeenTimestamp: ISODate("2023-11-29T21:16:17.796Z"),
latestSeenTimestamp: ISODate("2023-11-29T21:17:12.385Z")
},
asOf: Timestamp({ t: 1701292827, i: 0 })
}
]

Input:

db.getSiblingDB("admin").aggregate( [
{
$queryStats: {
transformIdentifiers: {
algorithm: "hmac-sha-256" ,
hmacKey: BinData(8, "87c4082f169d3fef0eef34dc8e23458cbb457c3sf3n2")
}
}
}
] )

출력:

[
{
key: {
queryShape: {
cmdNs: {
db: 'Mtrt3iG7dsX5c5uCSIhSVlcu5qD3u3xx2EQnS1dJLxM=',
coll: '3oJE6AyOuf8h5NqWiXETxulFlPm3QUXbMnMjL2EqAU4='
},
command: 'find',
filter: {
'VWVRow7Ure92ajRPfrpWiU8OtDeWcLePFIq0+tooBng=': { '$eq': '?string' }
}
},
client: {
driver: { name: 'nodejs|mongosh', version: '5.1.0' },
os: {
type: 'Darwin',
name: 'darwin',
architecture: 'arm64',
version: '22.6.0'
},
platform: 'Node.js v16.19.1, LE (unified)',
version: '5.1.0|1.8.0',
application: { name: 'mongosh 1.8.0' }
},
collectionType: 'collection'
},
keyHash: 'q4vxam+wbk8tTrl8D0MDFH1LQAbI8fWspfkGKhEUROk=',
metrics: {
lastExecutionMicros: Long("4254"),
execCount: Long("1"),
keysExamined: {
sum: Int("5"),
max: Long("5"),
min: Long("5"),
sumOfSquares: Long("25")
},
docsExamined: {
sum: Long("1"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Long("1")
},
hasSortStage: false,
usedDisk: false,
fromMultiPlanner: false,
fromPlanCache: true,
totalExecMicros: {
sum: Long("4254"),
max: Long("4254"),
min: Long("4254"),
sumOfSquares: Long("18096516")
},
firstResponseExecMicros: {
sum: Long("4254"),
max: Long("4254"),
min: Long("4254"),
sumOfSquares: Long("18096516")
},
docsReturned: {
sum: Long("1"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Long("1")
},
firstSeenTimestamp: ISODate("2023-09-14T12:30:27.989Z"),
latestSeenTimestamp: ISODate("2023-09-14T12:30:27.989Z")
},
asOf: Timestamp({ t: 1694695712, i: 0 })
},
{
key: {
queryShape: {
cmdNs: {
db: 'Mtrt3iG7dsX5c5uCSIhSVlcu5qD3u3xx2EQnS1dJLxM=',
coll: '3oJE6AyOuf8h5NqWiXETxulFlPm3QUXbMnMjL2EqAU4='
},
command: 'aggregate',
pipeline: [
{
'$match': {
'RVqrwNEPotzdKnma/T7s4YcgNvpqO29BMDoni2N4IMI=': { '$gt': '?number' }
}
}
]
},
apiVersion: '1',
client: {
driver: { name: 'nodejs|mongosh', version: '5.1.0' },
os: {
type: 'Darwin',
name: 'darwin',
architecture: 'arm64',
version: '22.6.0'
},
platform: 'Node.js v16.19.1, LE (unified)',
version: '5.1.0|1.8.0',
application: { name: 'mongosh 1.8.0' }
},
collectionType: 'collection',
cursor: { batchSize: '?number' }
},
keyHash: 'HEhpQTYB+/wVoHLkOkMd+EC2jguQlMJ1N/vTE7+b8Js=',
metrics: {
lastExecutionMicros: Long("350"),
execCount: Long("3"),
keysExamined: {
sum: Int("5"),
max: Long("5"),
min: Long("5"),
sumOfSquares: Long("25")
},
docsExamined: {
sum: Long("1"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Long("1")
},
hasSortStage: false,
usedDisk: false,
fromMultiPlanner: false,
fromPlanCache: true,
totalExecMicros: {
sum: Long("3084"),
max: Long("2499"),
min: Long("235"),
sumOfSquares: Long("6422726")
},
firstResponseExecMicros: {
sum: Long("3084"),
max: Long("2499"),
min: Long("235"),
sumOfSquares: Long("6422726")
},
docsReturned: {
sum: Long("3"),
max: Long("1"),
min: Long("1"),
sumOfSquares: Long("3")
},
firstSeenTimestamp: ISODate("2023-11-29T21:16:17.796Z"),
latestSeenTimestamp: ISODate("2023-11-29T21:17:12.385Z")
},
asOf: Timestamp({ t: 1701293302, i: 0 })
},
]

MongoDB Atlas periodically uses $queryStats to collect anonymized data about your queries, which helps improve MongoDB products. Your data may also be used to make feature suggestions based on usage. MongoDB retains the data it collects with $queryStats for four years.

When Atlas runs $queryStats on your deployment, it uses a unique HMAC key per Atlas organization to transform your data and avoid collecting sensitive information.