Manage and Monitor Queries

      +
      Monitoring and profiling SQL++ queries, query service nodes, and corresponding system resources is very important for smoother operational performance and efficiency of the system. In fact, often it is vital for diagnosing and troubleshooting issues such as query performance, resource bottlenecks, and overloading of various services.

      System keyspaces provide various monitoring details and statistics about individual queries and query service. When running on a cluster with multiple query nodes, stats about all queries on all query nodes are collected in these system keyspaces.

      For example, this can help identify:

      • The top 10 slow or fast queries running on a particular query node or the cluster.

      • Resource usage statistics of the query service, or resources used for a particular query.

      • Details about the active, completed, and prepared queries.

      • Find long running queries that are running for more than 2 minutes.

      These system keyspaces are like virtual keyspaces that are transient in nature, and are not persisted to disk or permanent storage. Hence, the information in the keyspaces pertains to the current instantiation of the query service.

      All the power of the SQL++ query language can be applied on the keyspaces to obtain various insights.

      The following diagnostics are provided:

      System Catalogs

      Monitoring Catalogs

      Security Catalogs

      Other

      Authentication and Client Privileges

      Client applications must be authenticated with sufficient privileges to access system keyspaces.

      • Users must have the Query System Catalog role to access restricted system keyspaces. For more details about user roles, see Authorization.

      • In addition, users must have permission to access a bucket, scope, or collection to be able to view that item in the system catalog. Similarly, users must have SELECT permission on the target of an index to be able to view that index in the system catalog.

      • The following system keyspaces are considered open, that is, all users can access them without any special privileges:

        • system:dual

        • system:datastores

        • system:namespaces

      Examples on this Page

      In the REST API examples:

      • $BASE_URL is the protocol, host name or IP address, and port — for example, http://localhost:8093.

      • $USER is the user name.

      • $PASSWORD is the password.

      Monitor System Vitals

      The system:vitals catalog provides data about the running state and health of the query node, such as number of logical cores, active threads, queued threads, CPU utilization, memory usage, network utilization, garbage collection percentage, and so on. This information can be very useful to assess the current workload and performance characteristics of a query node.

      Get System Vitals

      To view system vitals, use the Admin REST API or a SQL++ query.

      • REST API

      • SQL++

      To view system vitals with the Admin REST API:

      curl -u $USER:$PASSWORD $BASE_URL/admin/vitals

      To view system vitals with SQL++:

      SELECT * FROM system:vitals;

      System Vitals Details

      Getting system vitals, as described in Get System Vitals, returns results similar to the following.

      {
        "uptime": "7h39m32.668577197s",
        "local.time": "2021-04-30 18:42:39.517208807 +0000 UTC m=+27573.945319668",
        "version": "7.0.0-N1QL",
        "total.threads": 191,
        "cores": 2,
        "gc.num": 669810600,
        "gc.pause.time": "57.586373ms",
        "gc.pause.percent": 0,
        "memory.usage": 247985184,
        "memory.total": 11132383704,
        "memory.system": 495554808,
        "cpu.user.percent": 0,
        "cpu.sys.percent": 0,
        "request.completed.count": 140,
        "request.active.count": 0,
        "request.per.sec.1min": 0.0018,
        "request.per.sec.5min": 0.0055,
        "request.per.sec.15min": 0.0033,
        "request_time.mean": "536.348163ms",
        "request_time.median": "54.065567ms",
        "request_time.80percentile": "981.869933ms",
        "request_time.95percentile": "2.543128455s",
        "request_time.99percentile": "4.627922799s",
        "request.prepared.percent": 0
      }

      For field names and meanings, see Vitals.

      Monitor and Manage Active Requests

      The system:active_requests catalog lists all currently executing active requests or queries.

      Get Active Requests

      To view active requests, use the Admin REST API or a SQL++ query.

      • REST API

      • SQL++

      To view active requests with the Admin REST API:

      curl -u $USER:$PASSWORD $BASE_URL/admin/active_requests

      To view active requests with SQL++:

      SELECT * FROM system:active_requests;

      To get the query plan for active requests, include meta().plan in a SQL++ query. See Query Profiling.

      • SQL++

      To view active requests with SQL++, including the query plan:

      SELECT *, meta().plan FROM system:active_requests;

      Terminate an Active Request

      The DELETE command can be used to terminate an active request, for instance, a non-responding or a long-running query.

      • REST API

      • SQL++

      To terminate an active request uuid with the Admin REST API:

      curl -u $USER:$PASSWORD -X DELETE $BASE_URL/admin/active_requests/uuid

      To terminate an active request uuid with SQL++:

      DELETE FROM system:active_requests WHERE requestId = "uuid";

      Active Request Details

      Getting active requests, as described in Get Active Requests, returns results similar to the following.

      [
        {
          "active_requests": {
            "clientContextID": "832adfa0-e9e6-464e-b5e6-b7ec7549d511",
            "cpuTime": "111.877µs",
            "elapsedTime": "77.631814ms",
            "executionTime": "77.517185ms",
            "n1qlFeatCtrl": 76,
            "node": "127.0.0.1:8091",
            "phaseOperators": {
              "authorize": 1,
              "fetch": 1,
              "primaryScan": 1,
              "project": 1,
              "stream": 1
            },
            "phaseTimes": {
              "authorize": "4.998µs",
              "fetch": "69.519µs",
              "instantiate": "16.28µs",
              "parse": "597.435µs",
              "plan": "24.141851ms",
              "plan.index.metadata": "24.005473ms",
              "plan.keyspace.metadata": "2.022µs",
              "primaryScan": "23.496033ms",
              "project": "824ns",
              "stream": "2.242µs"
            },
            "queryContext": "default:travel-sample.inventory",
            "remoteAddr": "127.0.0.1:37506",
            "requestId": "05cc1895-9986-4819-b4d3-8a4311e8f319",
            "requestTime": "2024-05-21T13:29:16.864Z",
            "scanConsistency": "unbounded",
            "state": "running",
            "statement": "SELECT * FROM system:active_requests;",
            "statementType": "SELECT",
            "useCBO": true,
            "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:126.0) Gecko/20100101 Firefox/126.0",
            "users": "builtin:Administrator"
          }
        }
      ]

      For field names and meanings, see Requests.

      For query plan field names and meanings, see Query Profiling Details.

      Monitor and Manage Prepared Statements

      The system:prepareds catalog provides data about the known prepared statements and their state in a query node’s prepared statement cache. For each prepared statement, this catalog provides information such as name, statement, query plan, last use time, number of uses, and so on.

      A prepared statement is created and stored relative to the current query context. You can create multiple prepared statements with the same name, each stored relative to a different query context. This enables you to run multiple instances of the same application against different datasets.

      When there are multiple prepared statements with the same name in different query contexts, the name of the prepared statement in the system:prepareds catalog includes the associated query context in brackets.

      Get Prepared Statements

      To get a list of all known prepared statements, use the Admin REST API or a SQL++ query.

      • REST API

      • SQL++

      To get a list of all known prepared statements with the Admin REST API:

      curl -u $USER:$PASSWORD $BASE_URL/admin/prepareds

      To get a list of all known prepared statements with a SQL++ query:

      SELECT * FROM system:prepareds;

      To get information about a specific prepared statement, use the Admin REST API or a SQL++ query.

      • REST API

      • SQL++

      To get information about a specific prepared statement example1 with the Admin REST API:

      curl -u $USER:$PASSWORD $BASE_URL/admin/prepareds/example1

      To get information about a specific prepared statement example1 with a SQL++ query:

      SELECT * FROM system:prepareds WHERE name = "example1";

      To get the query plan for prepared statements, include meta().plan in a SQL++ query. See Query Profiling.

      • SQL++

      To view prepared statements with SQL++, including the query plan:

      SELECT *, meta().plan FROM system:prepareds;

      Delete Prepared Statements

      To delete a specific prepared statement, use the Admin REST API or a SQL++ query.

      • REST API

      • SQL++

      To delete a prepared statement p1 with the Admin REST API:

      curl -u $USER:$PASSWORD -X DELETE $BASE_URL/admin/prepareds/p1

      To delete a prepared statement p1 with a SQL++ query:

      DELETE FROM system:prepareds WHERE name = "p1";

      To delete all the known prepared statements, use a SQL++ query.

      • SQL++

      To delete all known prepared statements:

      DELETE FROM system:prepareds;

      Prepared Statement Details

      To try the examples in this section, first create a couple of prepared statements.

      Create a prepared statement with default query context

      For this example, unset the query context. For more information, see Query Context.

      Query
      PREPARE p1 AS SELECT * FROM `travel-sample`.inventory.airline WHERE iata = "U2";
      Create a prepared statement with specified query context

      For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

      Query
      PREPARE p1 AS SELECT * FROM airline WHERE iata = "U2";

      Getting prepared statements, as described in Get Prepared Statements, returns results similar to the following.

      {
        "requestID": "d976e59a-d74e-4350-b0df-fa137099d594",
        "signature": {
          "*": "*",
          "plan": "json"
        },
        "results": [
          {
            "prepareds": {
              "encoded_plan": "H4sIAAAAAAAA/6RTUW/TPBT9K9H5XrbJ30QBMcmIhzJ1AjG0qh3wwKbEJLedmWt71061UIXfjpxkndYhENpbYt97z7nn+GxAtnQVVbk3ykICAgtSsWY6djayMwHy6JWAthXdjr3+TBy0s5Avh7N5qewHaoJXJQXIDSpaqNpEGVmtyfwf1MobOtR2TTY6bg6VZqMtQS6UCdQKWLUiSPgR+u9uFOTdIAg4T6yi4zT+v/sfjOt45Vj/IAh41mttaNmTONUhQn7d4FzxkuL9tL/SEpiyXkMepQ/nA+Sz9rIV+FleaVPtMpjTTU22TG19AZPtcP+5aMp6pbhJcr6AwLe6vO54P+CLQfR+n3zLPh/Y576fcleXe3bfqYydYxsMt/k1NZCR66T+9eAdJO4l+L0NoXQ+nWxhIVAHbZeQWAaNVjxc6YRiefWnXZ6EvYs2VayMIYMneXWiTSSGQOlspXvhsLdXDPyKw0KrqIr97E12gU/PL7D/iMh7q6NWZtpLDwGmUJuYR+JV6ADp1qfCQGaRVouKBzsu28s2vbYd4pFJrZDuBFJOtyE8Go0EbmriJqWVbmOfYKab86aTaz45nRyfJxC9tF2skyoHkDhAKzC0TGeT6Xg2yfwoG8+zvic7yE5mZx+z4oFpxePEZF/eTWaTLMmyFeV19zLo+O3ZsNivAAAA//+q+jhuaAQAAA==",
              "featuresControl": 76,
              "indexApiVersion": 4,
              "indexScanKeyspaces": {
                "default:travel-sample.inventory.airline": false
              },
              "name": "p1", (1)
              "namespace": "default",
              "node": "127.0.0.1:8091",
              "statement": "PREPARE p1 AS SELECT * FROM `travel-sample`.inventory.airline WHERE iata = \"U2\";",
              "uses": 0
            }
          },
          {
            "prepareds": {
              "encoded_plan": "H4sIAAAAAAAA/6STT28TMRDFv8rqcWkrExFAVDLiEKpUIIoaJQUOtNqY3Ulq6tju2Bt1iZbPjry7TWmKQKg3/xnPvPk9zwZkC1dSmXujLCQgsCAVK6YjZyM7EyAPXwloW9LNyOvPxEE7C/myP5sVyn6gOnhVUIDcoKSFqkyUkdWazNOgVt7QQNs12ei4HijNRluCXCgTqBGwakWQ8EN06zYV5G0iCDhPrKLjlP7J3QajKl461j8IAp71WhtadiJOdIiQXzc4U7ykeJftn7IEJqzXkIdp4XyAfNZcNAI/i0ttyl0FM7quyBbpWRfAZNu6/x00Yb1SXCecLyDwrSquWt339KKH3vWTb9Xnvfrcd1lu43LP7jsVsXVsg/42v6IaMnKV6F/13kHiDsGfbQiF8+lkWxYCVdB2CYll0GjE/ZaOKRaXf+vlUbV3q00UK2PI4FFeHWsTiSFQOFvqDhz29ua9vvlgrlVU8/3sTXaOT8/Psf9AyHuro1Zm0qGHAFOoTMwj8Sq0BenGp8BAZpFai4p7Oy6aiyb9th3hkUmtkO4E0pxuh/BwOBS4rojrNK108wDy4HevmK7P6pbibHwyPjpLtfXSttOeYB1A4gCNQJ9pMh1PRtNx5ofZaJZ1b7KD7Hh6+jHreWRf3o2n4ywx2RJ53X4LOnp72nf1KwAA////9+bsZQQAAA==",
              "featuresControl": 76,
              "indexApiVersion": 4,
              "indexScanKeyspaces": {
                "default:travel-sample.inventory.airline": false
              },
              "name": "p1(travel-sample.inventory)", (2)
              "namespace": "default",
              "node": "127.0.0.1:8091",
              "statement": "PREPARE p1 AS SELECT * FROM airline WHERE iata = \"U2\";",
              "uses": 0
            }
          }
        ],
        "status": "success",
        "metrics": {
          "elapsedTime": "25.323496ms",
          "executionTime": "25.173646ms",
          "resultCount": 2,
          "resultSize": 7891,
          "serviceLoad": 12
        }
      }

      In this example, the names of the prepared statements are identical, but they are associated with different query contexts.

      1 The name of the prepared statement for the default query context
      2 The name of the prepared statement showing the associated query context

      For field names and meanings, see Statements.

      For query plan field names and meanings, see Query Profiling Details.

      Monitor and Manage Completed Requests

      By default, the system:completed_requests catalog maintains a list of the most recent completed requests that have run longer than a predefined threshold of time. (You can also log completed requests that meet other conditions that you define.)

      For each completed request, this catalog maintains information such as requestId, statement text, prepared name (if prepared statement), request time, service time, and so on. This information provides a general insight into the health and performance of the query node and the cluster.

      Get Completed Requests

      To get a list of all logged completed requests, use the Admin REST API or a SQL++ query.

      • REST API

      • SQL++

      To get a list of all logged completed requests using the Admin REST API:

      curl -u $USER:$PASSWORD $BASE_URL/admin/completed_requests

      To get a list of all logged completed requests using SQL++:

      SELECT * FROM system:completed_requests;

      Note that the completed state means that the request was started and completed by the Query service, but it does not mean that it was necessarily successful. The request could have been successful, or completed with errors.

      To find requests that completed successfully, search for completed requests whose state is completed and whose errorCount field has the value 0.

      • SQL++

      To get a list of all logged completed requests, including only successful requests:

      SELECT * FROM system:completed_requests
      WHERE state = "completed" AND errorCount = 0;

      To get the query plan for completed requests, include meta().plan in a SQL++ query. See Query Profiling.

      • SQL++

      To view completed requests with SQL++, including the query plan:

      SELECT *, meta().plan FROM system:completed_requests;

      Purge the Completed Requests

      To purge a specific completed request, use the Admin REST API or a SQL++ query.

      • REST API

      • SQL++

      To purge a completed request uuid with the Admin REST API:

      curl -u $USER:$PASSWORD -X DELETE $BASE_URL/admin/completed_requests/uuid

      To purge a completed request uuid with SQL++:

      DELETE FROM system:completed_requests WHERE requestId = "uuid";

      To purge completed requests for a given time period, use a SQL++ query.

      • SQL++

      To purge the completed requests for a given time period:

      DELETE FROM system:completed_requests WHERE requestTime LIKE "2015-09-09%";

      Completed Request Details

      To try the examples in this section, first run a query which takes at least 1000ms (the default value of the completed-threshold query setting) to get registered in the system:completed_requests keyspace.

      Run a long query

      For this example, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

      Query
      SELECT * FROM route ORDER BY sourceairport LIMIT 5;

      Getting completed requests, as described in Get Completed Requests, returns results similar to the following.

      [
        // ...
        {
          "completed_requests": {
            "clientContextID": "a19a61ab-cd9e-46c9-be71-92623ff85741",
            "cpuTime": "912.408423ms",
            "elapsedTime": "3.762926948s",
            "errorCount": 0,
            "errors": [],
            "n1qlFeatCtrl": 76,
            "node": "127.0.0.1:8091",
            "phaseCounts": {
              "fetch": 24023,
              "primaryScan": 24023,
              "primaryScan.GSI": 24023,
              "sort": 24028
            },
            "phaseOperators": {
              "authorize": 1,
              "fetch": 1,
              "primaryScan": 1,
              "primaryScan.GSI": 1,
              "project": 1,
              "sort": 2,
              "stream": 1
            },
            "phaseTimes": {
              "authorize": "15.111µs",
              "fetch": "3.641125449s",
              "instantiate": "332.963µs",
              "parse": "1.04015ms",
              "plan": "602.878µs",
              "plan.index.metadata": "25.849µs",
              "plan.keyspace.metadata": "11.586µs",
              "primaryScan": "101.118572ms",
              "primaryScan.GSI": "101.118572ms",
              "project": "33.273783ms",
              "run": "3.760767643s",
              "sort": "666.364325ms",
              "stream": "1.617688ms"
            },
            "queryContext": "default:travel-sample.inventory",
            "remoteAddr": "127.0.0.1:37684",
            "requestId": "e170bf67-d364-4ed7-9698-784bbb779d18",
            "requestTime": "2024-05-21T14:31:46.882Z",
            "resultCount": 5,
            "resultSize": 17714,
            "scanConsistency": "unbounded",
            "serviceTime": "3.762768429s",
            "state": "completed",
            "statement": "SELECT * FROM route ORDER BY sourceairport LIMIT 5;",
            "statementType": "SELECT",
            "useCBO": true,
            "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:126.0) Gecko/20100101 Firefox/126.0",
            "users": "builtin:Administrator",
            "~qualifier": "threshold"
          }
        }
      ]

      For field names and meanings, see Requests.

      For query plan field names and meanings, see Query Profiling Details.

      Configure the Completed Requests

      You can configure the system:completed_requests keyspace by specifying parameters through the Admin API /admin/settings endpoint.

      You can specify the conditions for completed request logging using the completed field. This field takes a JSON object containing the names and values of logging qualifiers. Completed requests that meet the defined qualifiers are logged.

      curl $BASE_URL/admin/settings -u $USER:$PASSWORD \
        -H 'Content-Type: application/json' \
        -d '{"completed": {"user": "marco", "error": 12003}}'

      Logging Qualifiers

      You can specify the following logging qualifiers. A completed request is logged if any of the qualifiers are met (logical OR).

      threshold

      The execution time threshold in milliseconds.

      aborted

      Whether to log requests that generate a panic.

      error

      Log requests returning this error number.

      client

      Log requests from this IP address.

      user

      Log requests with this user name.

      context

      Log requests with this client context ID.

      For full details, see Logging parameters.

      The basic syntax adds a qualifier to the logging parameters, i.e. any existing qualifiers are not removed. You can change the value of a logging qualifier by specifying the same qualifier again with a new value.

      To add a new instance of an existing qualifier, use a plus sign (+) before the qualifier name, e.g. +user. To remove a qualifier, use a minus sign (-) before the qualifier name, e.g. -user.

      For example, the following request will add user simon to those tracked, and remove error 12003.

      curl $BASE_URL/admin/settings -u $USER:$PASSWORD \
        -H 'Content-Type: application/json' \
        -d '{"completed": {"+user": "simon", "-error": 12003}}'

      Similarly, you could remove all logging by execution time with the following request, as long as the value matches the existing threshold.

      curl $BASE_URL/admin/settings -u $USER:$PASSWORD \
        -H 'Content-Type: application/json' \
        -d '{"completed": {"-threshold": 1000}}'

      Tagged Sets

      You can also specify qualifiers that have to be met as a group for the completed request to be logged (logical AND).

      To do this, specify the tag field along with a set of qualifiers, like so:

      curl $BASE_URL/admin/settings -u $USER:$PASSWORD \
        -H 'Content-Type: application/json' \
        -d '{"completed": {"user": "marco", "error": 12003, "tag": "both_user_and_error"}}'

      In this case, the request will be logged when both user and error match.

      The tag name can be any string that is meaningful and unique. Requests that match a tagged set of conditions are logged with a field ~tag, which is set to the name of the tag.

      To add a qualifier to a tagged set, specify the tag name again along with the new qualifier:

      curl $BASE_URL/admin/settings -u $USER:$PASSWORD \
        -H 'Content-Type: application/json' \
        -d '{"completed": {"client": "172.1.2.3", "tag": "both_user_and_error"}}'

      You cannot add a new instance of an existing qualifier to a tagged set using a plus sign (+) before the qualifier name. For example, you cannot add a user qualifier to a tagged set that already contains a user qualifier. If you need to track two users with the same error, create two tagged sets, one per user.

      You can remove a qualifier from a tagged set using a minus sign (-) before the qualifier name, e.g. -user. When you remove the last qualifier from a tagged set, the tagged set is removed.

      You can specify multiple tagged sets. In this case, completed requests are logged if they match all of the qualifiers in any of the tagged sets.

      You can also specify a mixture of tagged sets and individual qualifiers. In this case, completed requests are logged if they match any of the individual qualifiers, or all of the qualifiers in any of the tagged sets.

      Completed Threshold

      The completed-threshold field provides another way of specifying the threshold qualifier within the completed field.

      This field sets the minimum request duration after which requests are added to the system:completed_requests catalog. The default value is 1000ms. Specify 0 to log all requests and -1 to not log any requests to the keyspace.

      To specify a different value, use:

      curl $BASE_URL/admin/settings -u $USER:$PASSWORD \
        -H 'Content-Type: application/json' \
        -d '{"completed-threshold":0}'

      Completed Limit

      The completed-limit field sets the number of most recent requests to be tracked in the system:completed_requests catalog. The default value is 4000. Specify 0 to not track any requests and -1 to set no limit.

      To specify a different value, use:

      curl $BASE_URL/admin/settings -u $USER:$PASSWORD \
        -H 'Content-Type: application/json' \
        -d '{"completed-limit":1000}'

      Query Profiling

      Query profiling enables you to obtain more detailed monitoring information and finer execution timings for any query. You can set query profiling to the following levels:

      • off — query profiling is disabled.

      • phases — query profiling is enabled, including information about the phases of query execution.

      • timings — query profiling is enabled, including information about the phases of query execution, and detailed timing information.

      You can set query profiling in the following ways:

      • At the node level, so that it is enabled for all queries on that node.

      • At the request level, for individual queries.

      For more information about Query settings and parameters, see Configure Queries.

      Enable Query Profiling for a Query Node

      To activate query profiling at the node level, specify the profile setting using the Admin REST API (/admin/settings endpoint).

      See the current node-level query settings

      The following request gets the current node-level query settings.

      Request
      curl $BASE_URL/admin/settings -u $USER:$PASSWORD
      Results
      {
        "atrcollection": "",
        "auto-prepare": false,
        "cleanupclientattempts": true,
        "cleanuplostattempts": true,
        "cleanupwindow": "1m0s",
        "completed": {
          "aborted": null,
          "threshold": 1000
        },
        "completed-limit": 4000,
        "completed-threshold": 1000,
        "controls": false,
        "cpuprofile": "",
        "debug": false,
        "functions-limit": 16384,
        "keep-alive-length": 16384,
        "loglevel": "INFO",
        "max-index-api": 4,
        "max-parallelism": 1,
        "memory-quota": 0,
        "memprofile": "",
        "mutexprofile": false,
        "n1ql-feat-ctrl": 76,
        "numatrs": 1024,
        "pipeline-batch": 16,
        "pipeline-cap": 512,
        "plus-servicers": 16,
        "prepared-limit": 16384,
        "pretty": false,
        "profile": "off",
        "request-size-cap": 67108864,
        "scan-cap": 512,
        "servicers": 4,
        "timeout": 0,
        "txtimeout": "0s",
        "use-cbo": true
      }
      Save node-level query settings to a file

      The following request saves the current node-level query settings to the file query_settings.json.

      Request
      curl $BASE_URL/admin/settings -u $USER:$PASSWORD -o ./query_settings.json
      Set node-level query settings from a file

      Assuming that you have edited the file query_settings.json to specify the query settings you want, the following request sets the node-level query settings according to the file.

      Request
      curl $BASE_URL/admin/settings -u $USER:$PASSWORD \
        -X POST \
        -d@./query_settings.json
      Set node-level query settings explicitly

      The following request explicitly sets query profiling at the node level.

      Request
      curl $BASE_URL/admin/settings -u $USER:$PASSWORD \
        -H 'Content-Type: application/json' \
        -d '{"profile": "phases"}'
      Results
      {
        // ...
        "profile":"phases",
        "request-size-cap": 67108864,
        "scan-cap": 512,
        "servicers": 4,
        "timeout": 0,
        "txtimeout": "0s",
        "use-cbo": true
      }

      Enable Query Profiling for a Request

      To activate profiling at the request level, you can:

      • Specify the profile setting using the Query REST API (/query/service endpoint).

      • Specify the profile setting using the cbq command line tool.

      • REST API

      • SQL++

      To set query settings using the REST API, specify the parameters in the request body.


      The following statement sets the profiling to phases:

      curl $BASE_URL/query/service -u $USER:$PASSWORD \
        -d 'profile=phases&statement=SELECT * FROM `travel-sample`.inventory.airline LIMIT 1'

      The following statement sets the profiling to timings:

      curl $BASE_URL/query/service -u $USER:$PASSWORD \
        -d 'profile=timings&statement=SELECT * FROM `travel-sample`.inventory.airline LIMIT 1'

      To set query settings using the cbq shell, use the \SET command.


      The following statement sets the profiling to phases:

      \set -profile "phases";
      SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;

      The following statement sets the profiling to timings:

      \set -profile "timings";
      SELECT * FROM `travel-sample`.inventory.airline LIMIT 1;

      The Query Workbench automatically enables Query profiling, with detailed timing information. To disable or enable Query profiling with the Query Workbench, specify the Collect query timings option using the Query Preferences.

      Query Profiling Details

      You can access the profiling information in the following ways:

      When a query executes a user-defined function, profiling information is available for the SQL++ queries within the user-defined function as well.

      Profiling Details in Query Responses

      When profiling is enabled:

      • If you are using the cbq shell or the Query REST API, query profiling information is returned with the query results.

      • If you are using the Query workbench, query profiling information is not returned with the query results.

      Phases Profile

      If you are using the cbq shell or the Query REST API, the following statistics are returned when profile is set to phases:

      {
        "requestID": "06d6c1c2-1a8a-4989-a856-7314f9eddee5",
        "signature": {
          "*": "*"
        },
        "results": [
          {
            "airline": {
              "callsign": "MILE-AIR",
              "country": "United States",
              "iata": "Q5",
              "icao": "MLA",
              "id": 10,
              "name": "40-Mile Air",
              "type": "airline"
            }
          }
        ],
        "status": "success",
        "metrics": {
          "elapsedTime": "12.77927ms",
          "executionTime": "12.570648ms",
          "resultCount": 1,
          "resultSize": 254,
          "serviceLoad": 12
        },
        "profile": {
          "phaseTimes": {
            "authorize": "19.629µs",
            "fetch": "401.997µs",
            "instantiate": "147.686µs",
            "parse": "4.545234ms",
            "plan": "409.364µs",
            "primaryScan": "6.103775ms",
            "run": "6.699056ms"
          },
          "phaseCounts": {
            "fetch": 1,
            "primaryScan": 1
          },
          "phaseOperators": {
            "authorize": 1,
            "fetch": 1,
            "primaryScan": 1
          },
          "requestTime": "2021-04-30T18:37:56.394Z",
          "servicingHost": "127.0.0.1:8091"
        }
      }
      Timings Profile

      If you are using the cbq shell or the Query REST API, the following statistics are returned when profile is set to timings:

      {
        "requestID": "268a1240-6864-43a2-af13-ccb8d1e50abf",
        "signature": {
          "*": "*"
        },
        "results": [
          {
            "airline": {
              "callsign": "MILE-AIR",
              "country": "United States",
              "iata": "Q5",
              "icao": "MLA",
              "id": 10,
              "name": "40-Mile Air",
              "type": "airline"
            }
          }
        ],
        "status": "success",
        "metrics": {
          "elapsedTime": "2.915245ms",
          "executionTime": "2.755355ms",
          "resultCount": 1,
          "resultSize": 254,
          "serviceLoad": 12
        },
        "profile": {
          "phaseTimes": {
            "authorize": "18.096µs",
            "fetch": "388.122µs",
            "instantiate": "31.702µs",
            "parse": "646.157µs",
            "plan": "120.427µs",
            "primaryScan": "1.402918ms",
            "run": "1.936852ms"
          },
          "phaseCounts": {
            "fetch": 1,
            "primaryScan": 1
          },
          "phaseOperators": {
            "authorize": 1,
            "fetch": 1,
            "primaryScan": 1
          },
          "requestTime": "2021-04-30T18:40:13.239Z",
          "servicingHost": "127.0.0.1:8091",
          "executionTimings": {
            "#operator": "Authorize",
            "#stats": {
              "#phaseSwitches": 4,
              "execTime": "1.084µs",
              "servTime": "17.012µs"
            },
            "privileges": {
              "List": [
                {
                  "Target": "default:travel-sample.inventory.airline",
                  "Priv": 7,
                  "Props": 0
                }
              ]
            },
            "~child": {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 1,
                "execTime": "2.474µs"
              },
              "~children": [
                {
                  "#operator": "PrimaryScan3",
                  "#stats": {
                    "#itemsOut": 1,
                    "#phaseSwitches": 7,
                    "execTime": "18.584µs",
                    "kernTime": "8.869µs",
                    "servTime": "1.384334ms"
                  },
                  "bucket": "travel-sample",
                  "index": "def_inventory_airline_primary",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "airline",
                  "limit": "1",
                  "namespace": "default",
                  "scope": "inventory",
                  "using": "gsi"
                },
                {
                  "#operator": "Fetch",
                  "#stats": {
                    "#itemsIn": 1,
                    "#itemsOut": 1,
                    "#phaseSwitches": 10,
                    "execTime": "25.64µs",
                    "kernTime": "1.427752ms",
                    "servTime": "362.482µs"
                  },
                  "bucket": "travel-sample",
                  "keyspace": "airline",
                  "namespace": "default",
                  "scope": "inventory"
                },
                {
                  "#operator": "InitialProject",
                  "#stats": {
                    "#itemsIn": 1,
                    "#itemsOut": 1,
                    "#phaseSwitches": 9,
                    "execTime": "6.006µs",
                    "kernTime": "1.825917ms"
                  },
                  "result_terms": [
                    {
                      "expr": "self",
                      "star": true
                    }
                  ]
                },
                {
                  "#operator": "Limit",
                  "#stats": {
                    "#itemsIn": 1,
                    "#itemsOut": 1,
                    "#phaseSwitches": 4,
                    "execTime": "2.409µs",
                    "kernTime": "2.094µs"
                  },
                  "expr": "1"
                },
                {
                  "#operator": "Stream",
                  "#stats": {
                    "#itemsIn": 1,
                    "#itemsOut": 1,
                    "#phaseSwitches": 6,
                    "execTime": "46.964µs",
                    "kernTime": "1.844828ms"
                  }
                }
              ]
            },
            "~versions": [
              "7.0.0-N1QL",
              "7.0.0-4960-enterprise"
            ]
          }
        }
      }

      For field names and meanings, see Profile.

      Profiling Details in System Catalogs

      The system:active_requests and system:completed_requests system catalogs always return profiling information regarding query phases: that is, phase times, phase counts, and phase operators.

      The system:active_requests, system:completed_requests, and system:prepareds system catalogs also support the meta().plan virtual attribute. This captures the whole query plan, and includes profiling information regarding execution timings.

      To get execution timing information from these system catalogs, you must explicitly specify meta().plan in the projection list for the SELECT query.

      Within these system catalogs, not all statements have a meta().plan attribute.

      When request profiling is set to timings, profiling information is likely to use 100KB+ per entry in the system:completed_requests keyspace.

      • Due to the added overhead of running both profiling and logging, turn on both of them only when needed. Running only one of them continuously has no noticeable affect on performance.

      • Profiling does not carry any extra cost beyond memory for completed requests, so it’s fine to run it continuously.

      Plan Details

      Getting the plan for a statement that you ran when the profile was set to timings returns results similar to the following.

      [
        {
        // ...
          "plan": {
            "#operator": "Authorize",
            "#stats": {
              "#phaseSwitches": 4,
              "execTime": "1.725µs",
              "servTime": "21.312µs"
            },
            "privileges": {
              "List": [
                {
                  "Priv": 7,
                  "Props": 0,
                  "Target": "default:travel-sample.inventory.route"
                }
              ]
            },
            "~child": {
              "#operator": "Sequence",
              "#stats": {
                "#phaseSwitches": 2,
                "execTime": "1.499µs"
              },
              "~children": [
                {
                  "#operator": "PrimaryScan3",
                  "#stats": {
                    "#heartbeatYields": 6,
                    "#itemsOut": 24024,
                    "#phaseSwitches": 96099,
                    "execTime": "84.366121ms",
                    "kernTime": "3.021901421s",
                    "servTime": "69.320752ms"
                  },
                  "bucket": "travel-sample",
                  "index": "def_inventory_route_primary",
                  "index_projection": {
                    "primary_key": true
                  },
                  "keyspace": "route",
                  "namespace": "default",
                  "scope": "inventory",
                  "using": "gsi"
                },
                {
                  "#operator": "Fetch",
                  "#stats": {
                    "#heartbeatYields": 7258,
                    "#itemsIn": 24024,
                    "#itemsOut": 24024,
                    "#phaseSwitches": 99104,
                    "execTime": "70.34694ms",
                    "kernTime": "142.630196ms",
                    "servTime": "3.021959695s"
                  },
                  "bucket": "travel-sample",
                  "keyspace": "route",
                  "namespace": "default",
                  "scope": "inventory"
                },
                {
                  "#operator": "InitialProject",
                  "#stats": {
                    "#itemsIn": 24024,
                    "#itemsOut": 24024,
                    "#phaseSwitches": 96100,
                    "execTime": "15.331951ms",
                    "kernTime": "3.219612458s"
                  },
                  "result_terms": [
                    {
                      "expr": "self",
                      "star": true
                    }
                  ]
                },
                {
                  "#operator": "Order",
                  "#stats": {
                    "#itemsIn": 24024,
                    "#itemsOut": 24024,
                    "#phaseSwitches": 72078,
                    "execTime": "147.889352ms",
                    "kernTime": "3.229055752s"
                  },
                  "sort_terms": [
                    {
                      "expr": "(`route`.`sourceairport`)"
                    }
                  ]
                },
                {
                  "#operator": "Stream",
                  "#stats": {
                    "#itemsIn": 24024,
                    "#itemsOut": 24024,
                    "#phaseSwitches": 24025,
                    "execTime": "11.851634134s"
                  }
                }
              ]
            },
            "~versions": [
              "7.0.0-N1QL",
              "7.0.0-4960-enterprise"
            ]
          }
        }
      ]

      For field names and meanings, see Execution Timings.

      Monitor Your User Info

      The system:my_user_info catalog maintains a list of all information of your profile.

      To see your current information, use:

      SELECT * FROM system:my_user_info;

      This will result in a list similar to:

      [
        {
          "my_user_info": {
            "domain": "local",
            "external_groups": [],
            "groups": [],
            "id": "jane",
            "name": "Jane Doe",
            "password_change_date": "2019-05-07T02:31:53.000Z",
            "roles": [
              {
                "origins": [
                  {
                    "type": "user"
                  }
                ],
                "role": "admin"
              }
            ]
          }
        }
      ]

      Monitor All User Info

      The system:user_info catalog maintains a list of all current users in your bucket and their information.

      To see the list of all current users, use:

      SELECT * FROM system:user_info;

      This will result in a list similar to:

      [
        {
          "user_info": {
            "domain": "local",
            "external_groups": [],
            "groups": [],
            "id": "jane",
            "name": "Jane Doe",
            "password_change_date": "2019-05-07T02:31:53.000Z",
            "roles": [
              {
                "origins": [
                  {
                    "type": "user"
                  }
                ],
                "role": "admin"
              }
            ]
          }
        },
        {
          "user_info": {
            "domain": "ns_server",
            "id": "Administrator",
            "name": "Administrator",
            "roles": [
              {
                "role": "admin"
              }
            ]
          }
        }
      ]

      Monitor Nodes

      The system:nodes catalog shows the datastore topology information. This is separate from the Query clustering view, in that Query clustering shows a map of the Query cluster, as provided by the cluster manager, while system:nodes shows a view of the nodes and services that make up the actual datastore, which may or may not include Query nodes.

      • The dichotomy is important in that Query nodes could be clustered by one entity (e.g. Zookeeper) and be connected to a clustered datastore (e.g. Couchbase) such that each does not have visibility of the other.

      • Should SQL++ be extended to be able to concurrently connect to multiple datastores, each datastore will report its own topology, so that system:nodes offers a complete view of all the storage nodes, whatever those may be.

      • The system:nodes keyspace provides a way to report services advertised by each node as well as services that are actually running. This is datastore dependent.

      • Query clustering is still reported by the /admin endpoints.

      To see the list of all current node information, use:

      SELECT * FROM system:nodes;

      This will result in a list similar to:

      [
        {
          "nodes": {
            "name": "127.0.0.1:8091",
            "ports": {
              "cbas": 8095,
              "cbasAdmin": 9110,
              "cbasCc": 9111,
              "cbasSSL": 18095,
              "eventingAdminPort": 8096,
              "eventingSSL": 18096,
              "fts": 8094,
              "ftsSSL": 18094,
              "indexAdmin": 9100,
              "indexHttp": 9102,
              "indexHttps": 19102,
              "indexScan": 9101,
              "indexStreamCatchup": 9104,
              "indexStreamInit": 9103,
              "indexStreamMaint": 9105,
              "kv": 11210,
              "kvSSL": 11207,
              "n1ql": 8093,
              "n1qlSSL": 18093
            },
            "services": [
              "cbas",
              "eventing",
              "fts",
              "index",
              "kv",
              "n1ql"
            ]
          }
        }
      ]

      Monitor Applicable Roles

      The system:applicable_roles catalog maintains a list of all applicable roles and grantee of each bucket.

      To see the list of all current applicable role information, use:

      SELECT * FROM system:applicable_roles;

      This will result in a list similar to:

      [
        {
          "applicable_roles": {
            "grantee": "anil",
            "role": "replication_admin"
          }
        },
        {
          "applicable_roles": {
            "bucket_name": "travel-sample",
            "grantee": "anil",
            "role": "select"
          }
        },
        {
          "applicable_roles": {
            "bucket_name": "*",
            "grantee": "anil",
            "role": "select"
          }
        }
      ]

      For more examples, take a look at the blog: Optimize SQL++ performance using request profiling.

      Monitor Statistics

      The system:dictionary catalog maintains a list of the on-disk optimizer statistics stored in the _query collection within the _system scope.

      If you have multiple query nodes, the data retrieved from this catalog will be the same, regardless of the node on which you run the query.

      To see the list of on-disk optimizer statistics, use:

      SELECT * FROM system:dictionary;

      This will result in a list similar to:

      [
        {
          "dictionary": {
            "avgDocKeySize": 12,
            "avgDocSize": 278,
            "bucket": "travel-sample",
            "distributionKeys": [
              "airportname",
              "faa",
              "city"
            ],
            "docCount": 1968,
            "indexes": [
              {
                "indexId": "bc3048e87bf84828",
                "indexName": "def_inventory_airport_primary",
                "indexStats": [
                  {
                    "avgItemSize": 24,
                    "avgPageSize": 11760,
                    "numItems": 1968,
                    "numPages": 4,
                    "resRatio": 1
                  }
                ]
              },
              // ...
            ],
            "keyspace": "airport",
            "namespace": "default",
            "scope": "inventory"
          }
        },
        // ...
      ]

      This catalog contains an array of dictionaries, one for each keyspace for which optimizer statistics are available. Each dictionary gives the following information:

      Name Description Schema

      avgDocKeySize
      required

      Average doc key size.

      Integer

      avgDocSize
      required

      Average doc size.

      Integer

      bucket
      required

      The bucket for which statistics are available.

      String

      keyspace
      required

      The keyspace for which statistics are available.

      String

      namespace
      required

      The namespace for which statistics are available.

      String

      scope
      required

      The scope for which statistics are available.

      String

      distributionKeys
      required

      Distribution keys for which histograms are available.

      String array

      docCount
      required

      Document count.

      Integer

      indexes
      required

      An array of indexes in this keyspace for which statistics are available.

      Indexes array

      node
      required

      The query node where this dictionary cache is resident.

      String

      Indexes

      Name Description Schema

      indexId
      required

      The index ID.

      String

      indexName
      required

      The index name.

      String

      indexStats
      required

      An array of statistics for each index, with one element for each index partition.

      Index Statistics

      Name Description Schema

      avgItemSize
      required

      Average item size.

      Integer

      avgPageSize
      required

      Average page size.

      Integer

      numItems
      required

      Number of items.

      Integer

      numPages
      required

      Number of pages.

      Integer

      resRatio
      required

      Resident ratio.

      Integer

      For further details, see UPDATE STATISTICS.

      Monitor Cached Statistics

      The system:dictionary_cache catalog maintains a list of the in-memory cached subset of the optimizer statistics.

      If you have multiple query nodes, the data retrieved from this node shows cached optimizer statistics from all nodes. Individual nodes may have a different subset of cached information.

      To see the list of in-memory optimizer statistics, use:

      SELECT * FROM system:dictionary_cache;

      This will result in a list similar to:

      [
        {
          "dictionary_cache": {
            "avgDocKeySize": 12,
            "avgDocSize": 278,
            "bucket": "travel-sample",
            "distributionKeys": [
              "airportname",
              "faa",
              "city"
            ],
            "docCount": 1968,
            "indexes": [
              {
                "indexId": "bc3048e87bf84828",
                "indexName": "def_inventory_airport_primary",
                "indexStats": [
                  {
                    "avgItemSize": 24,
                    "avgPageSize": 11760,
                    "numItems": 1968,
                    "numPages": 4,
                    "resRatio": 1
                  }
                ]
              },
              // ...
            ],
            "keyspace": "airport",
            "namespace": "default",
            "node": "172.23.0.3:8091",
            "scope": "inventory"
          }
        },
        // ...
      ]

      This catalog contains an array of dictionary caches, one for each keyspace for which optimizer statistics are available. Each dictionary cache gives the same information as the system:dictionary catalog.

      For further details, see UPDATE STATISTICS.

      Monitor Functions

      The system:functions catalog maintains a list of all user-defined functions across all nodes. To see the list of all user-defined functions, use:

      SELECT * FROM system:functions;

      This will result in a list similar to:

      [
        {
          "functions": {
            "definition": {
              "#language": "inline",
              "expression": "(((`fahrenheit` - 32) * 5) / 9)",
              "parameters": [
                "fahrenheit"
              ],
              "text": "((fahrenheit - 32) * 5/9)"
            },
            "identity": {
              "bucket": "travel-sample",
              "name": "celsius",
              "namespace": "default",
              "scope": "inventory",
              "type": "scope"
            }
          }
        },
        {
          "functions": {
            "definition": {
              "#language": "javascript",
              "library": "geohash-js",
              "name": "geohash-js",
              "object": "calculateAdjacent",
              "parameters": [
                "src",
                "dir"
              ]
            },
            "identity": {
              "name": "adjacent",
              "namespace": "default",
              "type": "global"
            }
          }
        },
        // ...
      ]

      This catalog contains the following attributes:

      Name Description Schema

      definition
      required

      The definition of the function.

      Definition object

      identity
      required

      The identity of the function.

      Identity object

      Definition

      Name Description Schema

      #language
      required

      The language of the function.

      Example: inline

      String

      parameters
      required

      The parameters required by the function.

      String array

      expression
      optional

      For inline functions only: the expression defining the function.

      String

      text
      optional

      For inline functions: the verbatim text of the function.


      For SQL++ managed user-defined functions: the external code defining the function.

      String

      library
      optional

      For external functions only: the library containing the function.

      String

      name
      optional

      For external functions only: the relative name of the library.

      String

      object
      optional

      For external functions only: the object defining the function.

      String

      Identity

      Name Description Schema

      name
      required

      The name of the function.

      String

      namespace
      required

      The namespace of the function.

      Example: default

      String

      type
      required

      The type of the function.

      Example: global

      String

      bucket
      optional

      For scoped functions only: the bucket containing the function.

      String

      scope
      optional

      For scoped functions only: the scope containing the function.

      String

      Monitor Cached Functions

      The system:functions_cache catalog maintains a list of recently-used user-defined functions across all nodes. The catalog also lists user-defined functions that have been called recently, but do not exist. To see the list of recently-used user-defined functions, use:

      SELECT * FROM system:functions_cache;

      This will result in a list similar to:

      [
        {
          "functions_cache": {
            "#language": "inline",
            "avgServiceTime": "3.066847ms",
            "expression": "(((`fahrenheit` - 32) * 5) / 9)",
            "lastUse": "2022-03-09 00:17:59.60659793 +0000 UTC m=+35951.429537902",
            "maxServiceTime": "3.066847ms",
            "minServiceTime": "0s",
            "name": "celsius",
            "namespace": "default",
            "node": "127.0.0.1:8091",
            "parameters": [
              "fahrenheit"
            ],
            "scope": "inventory",
            "text": "((fahrenheit - 32) * 5/9)",
            "type": "scope",
            "uses": 1
          }
        },
        {
          "functions_cache": {
            "#language": "javascript",
            "avgServiceTime": "56.892636ms",
            "lastUse": "2022-03-09 00:15:46.289934029 +0000 UTC m=+35818.007560703",
            "library": "geohash-js",
            "maxServiceTime": "146.025426ms",
            "minServiceTime": "0s",
            "name": "geohash-js",
            "namespace": "default",
            "node": "127.0.0.1:8091",
            "object": "calculateAdjacent",
            "parameters": [
              "src",
              "dir"
            ],
            "type": "global",
            "uses": 4
          }
        },
        {
          "functions_cache": {
            "avgServiceTime": "3.057421ms",
            "lastUse": "2022-03-09 00:17:25.396840275 +0000 UTC m=+35917.199008929",
            "maxServiceTime": "3.057421ms",
            "minServiceTime": "0s",
            "name": "notFound",
            "namespace": "default",
            "node": "127.0.0.1:8091",
            "type": "global",
            "undefined_function": true,
            "uses": 1
          }
        }
      ]

      This catalog contains the following attributes:

      Name Description Schema

      #language
      required

      The language of the function.

      Example: inline

      String

      name
      required

      The name of the function.

      String

      namespace
      required

      The namespace of the function.

      Example: default

      String

      parameters
      required

      The parameters required by the function.

      String array

      type
      required

      The type of the function.

      Example: global

      String

      scope
      optional

      For scoped functions only: the scope containing the function.

      String

      expression
      optional

      For inline functions only: the expression defining the function.

      String

      text
      optional

      For inline functions: the verbatim text of the function.


      For SQL++ managed user-defined functions: the external code defining the function.

      String

      library
      optional

      For external functions only: the library containing the function.

      String

      object
      optional

      For external functions only: the object defining the function.

      String

      avgServiceTime
      required

      The mean service time for the function.

      String

      lastUse
      required

      The date and time when the function was last used.

      String

      maxServiceTime
      required

      The maximum service time for the function.

      String

      minServiceTime
      required

      The minimum service time for the function.

      String

      node
      required

      The query node where the function is cached.

      String

      undefined_function
      required

      Whether the function exists or is undefined.

      Boolean

      uses
      required

      The number of uses of the function.

      Number

      Each query node keeps its own cache of recently-used user-defined functions, so you may see the same function listed for multiple nodes.

      Monitor Cached Tasks

      The system:tasks_cache catalog maintains a list of recently-used scheduled tasks, such as index advisor sessions. To see the list of recently-used scheduled tasks, use:

      SELECT * FROM system:tasks_cache;

      This will result in a list similar to:

      [
        {
          "tasks_cache": {
            "class": "advisor",
            "delay": "1h0m0s",
            "id": "bcd9f8e4-b324-504c-a98b-ace90dba869f",
            "name": "aa7f688a-bf29-438f-888f-eeaead87ca40",
            "node": "10.143.192.101:8091",
            "state": "scheduled",
            "subClass": "analyze",
            "submitTime": "2019-09-17 05:18:12.903122381 -0700 PDT m=+8460.550715992"
          }
        },
        {
          "tasks_cache": {
            "class": "advisor",
            "delay": "5m0s",
            "id": "254abec5-5782-543e-9ee0-d07da146b94e",
            "name": "ca2cfe56-01fa-4563-8eb0-a753af76d865",
            "node": "10.143.192.101:8091",
            "results": [
              // ...
            ],
            "startTime": "2019-09-17 05:03:31.821597725 -0700 PDT m=+7579.469191487",
            "state": "completed",
            "stopTime": "2019-09-17 05:03:31.963133954 -0700 PDT m=+7579.610727539",
            "subClass": "analyze",
            "submitTime": "2019-09-17 04:58:31.821230131 -0700 PDT m=+7279.468823737"
          }
        }
      ]

      This catalog contains the following attributes:

      Name Description Schema

      class
      required

      The class of the task.

      Example: advisor

      string

      delay
      required

      The scheduled duration of the task.

      string

      id
      required

      The internal ID of the task.

      string

      name
      required

      The name of the task.

      string

      node
      required

      The node where the task was started.

      string

      state
      required

      The state of the task.

      Values: scheduled, cancelled, completed

      string

      subClass
      required

      The subclass of the task.

      Example: analyze

      string

      submitTime
      required

      The date and time when the task was submitted.

      string

      results
      optional

      Not scheduled tasks: the results of the task.

      Any array

      startTime
      optional

      Not scheduled tasks: the date and time when the task started.

      string (date-time)

      stopTime
      optional

      Not scheduled tasks: the date and time when the task stopped.

      string (date-time)

      Refer to ADVISOR Function for more information on index advisor sessions.

      Monitor Transactions

      The system:transactions catalog maintains a list of active Couchbase transactions. To see the list of active transactions, use:

      SELECT * FROM system:transactions;

      This will result in a list similar to:

      [
        {
          "transactions": {
            "durabilityLevel": "majority",
            "durabilityTimeout": "2.5s",
            "expiryTime": "2021-04-21T12:53:48.598+01:00",
            "id": "85aea637-2288-434b-b7c5-413ad8e7c175",
            "isolationLevel": "READ COMMITED",
            "lastUse": "2021-04-21T12:51:48.598+01:00",
            "node": "127.0.0.1:8091",
            "numAtrs": 1024,
            "scanConsistency": "unbounded",
            "status": 0,
            "timeout": "2m0s",
            "usedMemory": 960,
            "uses": 1
          }
        // ...
        }
      ]

      This catalog contains the following attributes:

      Name Description Schema

      durabilityLevel
      required

      Durability level for all mutations within a transaction.

      string

      durabilityTimeout
      required

      Durability timeout per mutation within the transaction.

      string (duration)

      expiryTime
      required

      string (date-time)

      id
      required

      The transaction ID.

      string

      isolationLevel
      required

      The isolation level of the transaction.

      string

      lastUse
      required

      string (date-time)

      node
      required

      The node where the transaction was started.

      string

      numAtrs
      required

      The total number of active transaction records.

      integer

      scanConsistency
      required

      The transactional scan consistency.

      string

      status
      required

      integer

      timeout
      required

      The transaction timeout duration.

      string (duration)

      usedMemory
      required

      integer

      uses
      required

      integer

      Refer to SQL++ Support for Couchbase Transactions for more information.

      Monitor Sequences

      The system:sequences catalog maintains a list of loaded sequences on any node: that is, sequences that have been accessed since the last restart. To see the list of loaded sequences, use:

      SELECT * FROM system:sequences;

      This will result in a list similar to:

      [
        {
          "sequences": {
            "bucket": "travel-sample",
            "cache": 50,
            "cycle": false,
            "increment": 1,
            "max": 9223372036854776000,
            "min": -9223372036854776000,
            "name": "seq1",
            "namespace": "default",
            "namespace_id": "default",
            "path": "`default`:`travel-sample`.`inventory`.`seq1`",
            "scope_id": "inventory",
            "value": {
              "73428daec3c68d8632ae66b09b70f14d": null,
              "~next_block": 0
            }
          }
        },
      // ...
      ]

      This catalog contains the following attributes:

      Name Description Schema

      bucket
      required

      The bucket containing the sequence.

      string

      cache
      required

      The sequence’s cache size.

      integer

      cycle
      required

      Whether the sequence is set to cycle.

      boolean

      increment
      required

      The sequence step value.

      integer

      min
      required

      The minimum value permitted for the sequence.

      integer

      max
      required

      The maximum value permitted for the sequence.

      integer

      name
      required

      The name of the sequence.

      string

      namespace
      required

      Namespace to which the sequence belongs.

      string

      namespace_id
      required

      ID of the namespace to which the sequence belongs.

      string

      path
      required

      The fully qualified sequence name.

      string

      scope_id
      required

      ID of the scope to which the sequence belongs.

      string

      value
      required

      The current value of the sequence on each Query node.

      Values object

      Values

      Name Description Schema

      <UUID>
      required

      The name of this property is the UUID of a Query node.

      The value of this property is the current value of the sequence on that node.

      Integer

      ~next_block
      optional

      The starting vale of the next block of values that can be reserved for the sequence.

      Integer

      For further details, see CREATE SEQUENCE.

      Monitor All Sequences

      The system:all_sequences catalog maintains a list of all defined sequences. To see the list of all defined sequences, use:

      SELECT * FROM system:all_sequences;

      This will result in a list similar to:

      [
        {
          "sequences": {
            "bucket": "travel-sample",
            "cache": 50,
            "cycle": false,
            "increment": -1,
            "max": 9223372036854776000,
            "min": 0,
            "name": "seq4",
            "namespace": "default",
            "namespace_id": "default",
            "path": "`default`:`travel-sample`.`inventory`.`seq4`",
            "scope_id": "inventory",
            "value": {
              "73428daec3c68d8632ae66b09b70f14d": 10,
              "~next_block": -40
            }
          }
        },
        {
          "sequences": {
            "bucket": "travel-sample",
            "cache": 50,
            "cycle": true,
            "increment": 5,
            "max": 1000,
            "min": 0,
            "name": "seq3",
            "namespace": "default",
            "namespace_id": "default",
            "path": "`default`:`travel-sample`.`inventory`.`seq3`",
            "scope_id": "inventory",
            "value": {
              "73428daec3c68d8632ae66b09b70f14d": 5,
              "~next_block": 255
            }
          }
        },
      // ...
      ]

      This catalog gives the same information as the system:sequences catalog.

      For further details, see CREATE SEQUENCE.