Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

setProfilingLevel

Sets the database profiling level for queries run using the SingleStore Kai (mongodb://) endpoint.

By default, query profiling is disabled for MongoDB® queries. To profile and collect resource usage metrics for MongoDB® queries, set the profiling level to either 1 or 2.

This command is an extension of the MongoDB® setProfilingLevel() command.

Syntax

db.setProfilingLevel(<profiling_level>, <options>)

Arguments

  • profiling_level: Specifies the profiling level. It can have the following values:

    • 0: (Default) Disable profiling.

    • 1: Profile queries that exceed the specified slowms threshold or match the specified filter.

    • 2: Profile all queries and operations.

  • options: Specifies additional profiling options. If an integer is specified, the slowms threshold is updated with the specified value. It can have the following values:

    Option

    Description

    Default Value

    slowms

    Specifies the threshold in milliseconds for queries to be considered slow. Queries that take longer than the specified threshold are profiled.

    100

    failed

    Enables profiling of failed queries.

    false

Remarks

  • Run the getProfilingStatus() command to view the current profiling level.

  • The profile information also contains the SQL queries run to achieve the specified MongoDB® query/operation.

Examples

Example 1

The following example enables profiling, runs a query, and returns the profile information for the query.

  1. Enable profiling.

    db.setProfilingLevel(2)
  2. Run a sample MongoDB® query. For example, the following query finds all the documents in a collection named exampleCollection where Qty is greater than 25:

    db.exampleCollection.find({Qty:{$gt: 25}})
  3. View the profile information.

    db.system.profile.find().sort({ts: -1}).limit(1)
    [ { ts: Mon Mar 03 2025 17:30:01 GMT+0530 (India Standard Time),
        ns: 'dbTest.exampleCollection',
        op: 'query',
        millis: new Long("109"),
        ok: 1,
        client: '10.0.137.81:35940',
        command:
         { find: 'exampleCollection',
           filter: { Qty: { '$gt': 25 } },
           lsid: { id: new UUID("8ab9be41-646f-4a59-9f04-bc8370be95de") },
           '$db': 'dbTest' },
        sqlCommands: [ 'USING dbTest\nWITH\n`exampleCollection.find` AS (SELECT \'irm+QWRvSlmfBLyDcL6V3g==.D1qyPeKoaso=\' AS kai)\nSELECT BSON_EXCLUDE_MASK(_more, \'{"_id":1}\') AS _more_1, _id FROM dbTest.exampleCollection WHERE BSON_EXTRACT_BIGINT(_more, \'Qty\') > 25' ] } ]

    Note that the query profile contains information about the MongoDB® query and the equivalent SQL query. The sqlCommands section in the output shows the SQL commands run to achieve the search operation.

Additional Examples

  • To log failed queries and queries that exceed the specified slowms time:

    db.setProfilingLevel(1, {failed:true,slowms:60000})
  • To profile and log all the queries and operations:

    db.setProfilingLevel(2)
  • To disable profiling:

    db.setProfilingLevel(0)
  • To view the profiling metrics for the last 5 logged queries:

    db.system.profile.find().sort({ts: -1}).limit(5)

Last modified:

Was this article helpful?