Settings and Parameters

You can configure the Query service using cluster-level query settings, node-level query settings, and request-level query parameters.

There are three ways of configuring the Query service. You can specify cluster-level settings for all nodes running the Query service in the cluster. You can specify node-level settings for a single node running the Query service. You can also specify parameters for individual requests. Cluster-level query settings, node-level query settings, and request-level parameters must be set and used in different ways.

Table 1. Comparison of Query Settings and Parameters
	Setting Per	Set By	Set On	Set Via
Cluster-level query settings	Cluster	The administrator at the system level	Server side	The CLI, cURL statements, or the UI
Node-level query settings	Service Node	The administrator at the system level	Server side	cURL statements
Request-level parameters	Request (statement)	Each user	Client side	`cbq` command-line parameters, cURL statements, client programming, or the UI

Cluster-level settings and node-level settings are collectively referred to as service-level settings.

Query Setting Levels and Equivalents

Some query settings are cluster-level, node-level, or request-level only, while some apply to more than one level with slightly different names.

If a cluster-level setting has an equivalent node-level setting, then changing the cluster-level setting overwrites the node-level setting for all Query nodes in the cluster.

You can change a node-level setting for a single node to be different to the equivalent cluster-level setting. Changing the node-level setting does not affect the equivalent cluster-level setting. However, you should note that the node-level setting may be overwritten by subsequent changes at the cluster-level. In particular, specifying query settings via the CLI or the UI makes changes at the cluster-level.

If a request-level parameter has an equivalent node-level setting, the node-level setting usually acts as the default for the request-level parameter, as described in the tables below. Setting a request-level parameter overrides the equivalent node-level setting.

Furthermore, for numeric values, if a request-level parameter has an equivalent node-level setting, the node-level setting dictates the upper-bound value of the request-level parameter. For example, if the node-level timeout is set to 500, then the request-level parameter cannot be set to 501 or any value higher.

Table 2. Single-Level Settings
Cluster-Level Only Settings	Node-Level Only Settings	Request-Level Only Parameters
queryTmpSpaceDir queryTmpSpaceSize queryCurlWhitelist	auto-prepare completed cpuprofile debug distribute functions-limit keep-alive-length max-index-api memprofile mutexprofile plus-servicers request-size-cap servicers	args auto_execute client_context_id compression creds durability_level encoded_plan (deprecated) encoding format kvtimeout metrics namespace prepared preserve_expiry query_context readonly scan_consistency scan_vector scan_vectors scan_wait signature statement txid txstmtnum tximplicit txdata use_fts $<identifier>

Table 3. Settings for Cluster-Level and Node-Level
Cluster-Level Name	Node-Level Name	Request-Level Name
queryCleanupClientAttempts queryCleanupLostAttempts queryCleanupWindow queryCompletedLimit queryCompletedThreshold queryLogLevel queryN1qlFeatCtrl queryPreparedLimit	cleanupclientattempts cleanuplostattempts cleanupwindow completed-limit completed-threshold loglevel n1ql-feat-ctrl prepared-limit	N/A

Table 4. Settings for Node-Level and Request-Level
Cluster-Level Name	Node-Level Name	Request-Level Name
N/A	atrcollection controls pretty profile	atrcollection controls pretty profile

Table 5. Settings for Cluster-Level, Node-Level, and Request-Level
Cluster-Level Name	Node-Level Name	Request-Level Name
queryMaxParallelism queryMemoryQuota queryNumAtrs queryPipelineBatch queryPipelineCap queryScanCap queryTimeout queryTxTimeout queryUseCBO	max-parallelism memory-quota numatrs pipeline-batch pipeline-cap scan-cap timeout txtimeout use-cbo	max_parallelism memory_quota numatrs pipeline_batch pipeline_cap scan_cap timeout txtimeout use_cbo

Cluster-Level Query Settings

To set a cluster-level query setting, use the Query Settings REST API (/settings/querySettings endpoint) with a cURL statement, or the Advanced Query Settings in the Couchbase Web Console. You can also set all of the cluster-level query settings using the setting-query command.

The table below contains details of all cluster-level query settings.

Cluster-Level Query Settings

Unresolved include directive in modules/settings/pages/query-settings.adoc - include::rest-api:partial$query-settings/definitions.adoc[]

Access Unresolved include directive in modules/settings/pages/query-settings.adoc - include::rest-api:partial$query-settings/definitions.adoc[]

Node-Level Query Settings

To set a node-level query setting, use the Admin REST API (/admin/settings endpoint) with a cURL statement. These settings cannot be set by cbq.

To see a list of the current Query Settings, while the Query Service is running, enter:

curl http://localhost:8093/admin/settings -u user:pword

This will output the entire list of node-level query settings:

{
  "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
}

To output to a file for editing multiple settings at a single time, add the -o filename option. For example:

curl http://localhost:8093/admin/settings -u user:pword -o ./query_settings.json

The table below contains details of all node-level query settings.

Node-Level Query Settings

Unresolved include directive in modules/settings/pages/query-settings.adoc - include::n1ql:partial$n1ql-rest-api/admin/definitions.adoc[]

Request-Level Parameters

To set a request-level parameter, use the Query Service REST API (/query/service endpoint) with a cURL statement, or the cbq command, or a client program. You can also set request-level parameters using the Run-Time Preferences window in the Query Workbench.

While cbq is a sandbox to test code on your local machine, your production query settings are set with the cURL commands on your server.

CBQ Shell
REST API

To set request-level parameters in cbq, use the \SET command. The parameter name must be prefixed by a hyphen.

\SET -timeout "30m";
\SET -pretty true;
\SET -max_parallelism 3;
SELECT * FROM "world" AS hello;

To set request-level parameters with the REST API, specify the parameters in the request body or the query URI.

curl http://localhost:8093/admin/settings -u Administrator:password \
  -d 'statement=SELECT * FROM "world" AS hello;
    & timeout=30m
    & pretty=true
    & max_parallelism=3'

The table below contains details of all request-level parameters, along with examples.

Request-Level Parameters

Unresolved include directive in modules/settings/pages/query-settings.adoc - include::n1ql:partial$n1ql-rest-api/query/definitions.adoc[]

Credentials Unresolved include directive in modules/settings/pages/query-settings.adoc - include::n1ql:partial$n1ql-rest-api/query/definitions.adoc[]

Transactional Scan Consistency

If the request contains a BEGIN TRANSACTION statement, or a DML statement with the tximplicit parameter set to true, then the scan_consistency parameter sets the transactional scan consistency. If you specify a transactional scan consistency of request_plus, statement_plus, or at_plus, or if you specify no transactional scan consistency, the transactional scan consistency is set to request_plus; otherwise, the transactional scan consistency is set as specified.

Table 6. Transactional scan consistency
Scan consistency at start of transaction	Transactional scan consistency
Not set	`request_plus`
`not_bounded`	`not_bounded`
`request_plus` `statement_plus` `at_plus`	`request_plus`

Any DML statements within the transaction that have no scan consistency set will inherit from the transactional scan consistency. Individual DML statements within the transaction may override the transactional scan consistency. If you specify a scan consistency of not_bounded for a statement within the transaction, the scan consistency for the statement is set as specified. When you specify a scan consistency of request_plus, statement_plus, or at_plus for a statement within the transaction, the scan consistency for the statement is set to request_plus.

However, request_plus consistency is not supported for statements using a full-text index. If any statement within the transaction uses a full-text index, by means of the SEARCH function or the Flex Index feature, the scan consistency is set to not_bounded for the duration of the full-text search.

Table 7. Overriding the transactional scan consistency
Scan consistency for statement within transaction	Inherited scan consistency
Not set	Transactional scan consistency (`not_bounded` for full-text search)
`not_bounded`	`not_bounded`
`request_plus` `statement_plus` `at_plus`	`request_plus` (`not_bounded` for full-text search)

Named Parameters and Positional Parameters

Named parameters use a variable name to define the value of each parameter, while numbered and unnumbered positional parameters use a list of arguments to define the value of each parameter by position. Requests which use named parameters and positional parameters should contain the appropriate placeholders, as summarized in the table below.

Unresolved include directive in modules/settings/pages/query-settings.adoc - include::ROOT:partial$query-context.adoc[]

Arguments Statement

Table 8. Named Parameters and Positional Parameters
Arguments	Statement
Named Parameters `\SET -$country "France"; (1)(2) \SET -$altitude 500;`	`SELECT COUNT(*) FROM airport WHERE country = $country AND geo.alt > $altitude;`
Positional Parameters `\SET -args ["France", 500]; (1)(2)(3)`	`SELECT COUNT() FROM airport WHERE country = $1 AND geo.alt > $2;` `SELECT COUNT() FROM airport WHERE country = ? AND geo.alt > ?;`

Named Parameters

\SET -$country "France"; (1)(2)
\SET -$altitude 500;

SELECT COUNT(*) FROM airport
WHERE country = $country AND geo.alt > $altitude;

Positional Parameters

\SET -args ["France", 500]; (1)(2)(3)

SELECT COUNT(*) FROM airport
WHERE country = $1 AND geo.alt > $2;

SELECT COUNT(*) FROM airport
WHERE country = ? AND geo.alt > ?;

For more details about the SQL++ REST API, refer to Query Service REST API.
For more details about the Admin REST API, refer to Admin REST API.
For more details about the Query Settings API, refer to Cluster Query Settings API.
For more details about API content and settings, refer to REST API reference.

Settings and Parameters

Query Setting Levels and Equivalents

Cluster-Level Query Settings

Node-Level Query Settings

Request-Level Parameters

Transactional Scan Consistency

Named Parameters and Positional Parameters

Related Links