You are viewing the documentation for a prerelease version.

View Latest

Settings and Parameters

You can configure the Query service using Service-level query settings and Request-level query parameters.

There are two ways of configuring the Query service. You can specify settings for the Query service as a whole. You can also specify parameters for individual requests. Service-level query settings and Request-level parameters behave differently, and must be set and used in different ways.

Table 1. Comparison of Query Settings and Parameters
Setting Per Set By Set On Set Via

Service-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, or client programming

The Request-level Parameters override their Service-level Query Setting equivalents.
The Service-level settings will dictate the upper-bound values of the Request-level parameters. For example, if the Service-level timeout is set to 500, then the Request-level parameter cannot be set to 501 or any value higher.

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

$ curl http://hostname:8093/admin/settings -u user:pword

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

{"auto-prepare":false,"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,"memprofile":"","mutexprofile":false,"n1ql-feat-ctrl":12,"pipeline-batch":16,"pipeline-cap":512,"prepared-limit":16384,"pretty":false,"profile":"off","request-size-cap":67108864,"scan-cap":512,"servicers":4,"timeout":0}

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

$ curl http://hostname:8093/admin/settings -u user:pword -o settings.txt

To instantly change one setting, see details of each setting in the Service-Level Query Settings table below.

Table of Query Setting Levels and Overrides

Service-Level Query Settings

To set a service-level query setting, use the Admin REST API (/admin/settings endpoint) with a cURL statement.

These settings can not be set by cbq.

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

Table 3. Service-Level Query Settings
Name Description Schema

auto-prepare
optional

Specifies whether the query engine should create a prepared statement every time a N1QL request is submitted, whether the PREPARE statement is included or not.

Refer to Auto-Prepare for more information.
Default : false
Example : true

boolean

completed
optional

A nested object that sets the parameters for the completed requests catalog. All completed requests that match these parameters are tracked in the completed requests catalog.

Refer to Configure the Completed Requests for more information and examples.
Example : { "user" : "marco", "error" : 12003 }

completed-limit
optional

Sets the number of requests to be logged in the completed requests catalog. As new completed requests are added, old ones are removed.

Increase this when the completed request keyspace is not big enough to track the slow requests, such as when you want a larger sample of slow requests.

Refer to Configure the Completed Requests for more information and examples.
Default : 4000
Example : 7000

integer (int32)

completed-threshold
optional

A duration in milliseconds. All completed queries lasting longer than this threshold are logged in the completed requests catalog.

Specify 0 to track all requests, independent of duration.

Specify any negative number to track none.

Refer to Configure the Completed Requests for more information and examples.
Default : 1000
Example : 7000

integer (int32)

controls
optional

Specifies if there should be a controls section returned with the request results.

When set to true, the query response document includes a controls section with runtime information provided along with the request, such as positional and named parameters or settings.

If the request qualifies for caching, these values will also be cached in the completed_requests system keyspace.

Default : false
Example : true

boolean

cpuprofile
optional

The absolute path and filename to write the CPU profile to a local file.

The output file includes a controls section and performance measurements, such as memory allocation and garbage collection, to pinpoint bottlenecks and ways to improve your code execution.

To stop cpuprofile, run with the empty setting of "".

If cpuprofile is left running too long, it can slow the system down as its file size increases.

Default : ""
Example : "/tmp/info.txt"

string

debug
optional

Use debug mode.

When set to true, extra logging is provided.
Default : false
Example : true

boolean

distribute
optional

This field is only available with the POST method. When specified alongside other settings, this field instructs the node that is processing the request to cascade those settings to all other query nodes. The actual value of this field is ignored.
Example : true

boolean

keep-alive-length
optional

Maximum size of buffered result.
Default : 16384
Example : 7000

integer (int32)

loglevel
optional

Log level used in the logger.

All values, in descending order of data:

DEBUG — For developers. Writes everything.

TRACE — For developers. Less info than DEBUG.

INFO — For admin & customers. Lists warnings & errors.

WARN — For admin. Only abnormal items.

ERROR — For admin. Only errors to be fixed.

SEVERE — For admin. Major items, like crashes.

NONE — Doesn’t write anything.
Default : "INFO"
Example : "DEBUG"

enum (DEBUG, TRACE, INFO, WARN, ERROR, SEVERE, NONE)

max-index-api
optional

Max index API. This setting is provided for technical support only.

integer (int32)

max-parallelism
optional

Maximum number of index partitions, for computing aggregation in parallel.

A zero or negative value means the number of logical CPUs will be used as the parallelism for the query.

There is also a request-level max_parallelism parameter. If a request includes this parameter, it will be capped by the server-wide max-parallelism setting.

To enable queries to run in parallel, you must specify the Server-level max-parallelism parameter on all Query nodes.

Refer to Max Parallelism for more information.
Default : 1
Example : 0

integer (int32)

memprofile
optional

Filename to write the diagnostic memory usage log.

To stop memprofile, run with the empty setting of "".

If memprofile is left running too long, it can slow the system down as its file size increases.

Default : ""
Example : "/tmp/memory-usage.log"

string

mutexprofile
optional

Mutex profile. This setting is provided for technical support only.
Default : false

boolean

n1ql-feat-ctrl
optional

N1QL feature control. This setting is provided for technical support only.

integer (int32)

pipeline-batch
optional

Controls the number of items execution operators can batch for Fetch from the KV.
Default : 16
Example : 64

integer (int32)

pipeline-cap
optional

Maximum number of items each execution operator can buffer between various operators.
Default : 512
Example : 1024

integer (int32)

prepared-limit
optional

Maximum number of prepared statements in the cache.

When this cache reaches the limit, the least recently used prepared statements will be discarded as new prepared statements are created.
Default : 16384
Example : 65536

integer (int32)

pretty
optional

Specifies whether query results are returned in pretty format.

There is also a request-level pretty parameter. If a request does not include this parameter, the server-level pretty setting will be used, which defaults to false.
Default : false
Example : true

boolean

profile
optional

Specifies if there should be a profile section returned with the request results. The valid values are:

off — No profiling information is added to the query response.

phases — The query response includes a profile section with stats and details about various phases of the query plan and execution. Three phase times will be included in the system:active_requests and system:completed_requests monitoring keyspaces.

timings — Besides the phase times, the profile section of the query response document will include a full query plan with timing and information about the number of processed documents at each phase. This information will be included in the system:active_requests and system:completed_requests keyspaces.

If profile is not set as one of the above values, then the profile setting does not change.

Refer to Monitoring and Profiling Details for more information and examples.
Default : "off"
Example : "phases"

enum (off, phases, timings)

request-size-cap
optional

Maximum size of a request.
Default : 67108864
Example : 70000

integer (int32)

scan-cap
optional

Maximum buffered channel size between the indexer client and the query service for index scans. This parameter controls when to use scan backfill.

Use 0 or a negative number to disable.

Smaller values reduce GC while larger values reduce indexer backfill.

The index channel capacity is configurable per request.
Default : 512
Example : 1024

integer (int32)

servicers
optional

The number of service threads for the query.
Default : 32
Example : 8

integer (int32)

timeout
optional

Maximum time to spend on the request before timing out.

The default value means no timeout is applied and the request runs for however long it takes.

There is also a request-level timeout parameter. The minimum of that and the service-level timeout setting is applied.

Its format includes an amount and a mandatory unit, e.g. 10ms (10 milliseconds) or 0.5s (half a second). Valid units are:

ns (nanoseconds)
us (microseconds)
ms (milliseconds)
s (seconds)
m (minutes)
h (hours)

Specify 0 or a negative integer to disable.
Default : "0s"
Example : "30m"

string (duration)

Logging parameters

Name Description Schema

aborted
optional

If true, all requests that generate a panic are logged.
Example : true

boolean

client
optional

The IP address of the client. If specified, all completed requests from this IP address are logged.
Default : ""
Example : "172.1.2.3"

string

context
optional

The opaque ID or context provided by the client. If specified, all completed requests with this client context ID are logged.

Refer to client_context_id for more information.

string

error
optional

An error number. If specified, all completed queries returning this error number are logged.
Example : 12003

integer (int32)

tag
optional

A unique string which tags a set of qualifiers.

Refer to Configure the Completed Requests for more information.
Default : ""
Example : "both_user_and_error"

string

threshold
optional

A duration in milliseconds. If specified, all completed queries lasting longer than this threshold are logged.

This is another way of specifying the completed-threshold setting, as described in Table 3.
Default : 1000
Example : 7000

integer (int32)

user
optional

A user name, as given in the request credentials. If specified, all completed queries with this user name are logged.
Default : ""
Example : "marco"

string

Request-Level Parameters

To set a request-level parameter, use the N1QL REST API (/query/service endpoint) with a cURL statement, or the cbq command, or a client program.

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.

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

Table 4. Request-Level Parameters
Name Description Schema

args
Optional

If the statement has 1 or more positional parameters, this parameter needs to be in the request; this is an array of JSON values, one for each positional parameter in the statement.

Positional parameters apply to prepared also.
Example
cbq> \set -args ["LAX", 6];

array

batch_args
Optional

Applies to POST requests only, containing UPDATE, INSERT, DELETE statements (DML statements) with positional parameters.

Example
INSERT INTO location (id, name) VALUES ($1, $2)

These require the values to be given in batch_args, which contains an array of arrays.

The inner arrays need to match the positional parameters in the statement.

array of arrays

batch_named_args
Optional

Applies to POST requests only, containing UPDATE, INSERT, DELETE statements (DML statements) with named parameters.

Example
INSERT INTO location (id, name) VALUES ($id, $n)

These require the values to be given in batch_named_args, which contains an array of objects.

The keys in each object need to match the named parameters in the statement.

array of objects

client_context_id
Optional

A piece of data supplied by the client that is echoed in the response, if present. N1QL is agnostic about the content of this parameter; it is just echoed in the response.

  • Maximum allowed size is 64 characters; all others will be cut.

  • If it contains an escape character (‘/’) or quote ("), it will be rejected as Error code 1110.

string

compression
Optional

Compression format to use for response data on the wire.

Values are case-insensitive.

Default

"NONE"

Example
cbq> \set -compression "zip";

enum (ZIP, RLE, LZMA, LZO, NONE)

controls
Optional

Specifies if there should be a controls section returned with the request results.

When set to true, the query response document includes a controls section with runtime information provided along with the request, such as positional and named parameters or settings.

If the request qualifies for caching, these values will also be cached in the completed_requests system keyspace.
Default

false

Example
cbq> \set -controls true;

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&controls=true'

boolean

creds
Optional

Specify the login credentials in the form of user:password.

You can specify credentials for different buckets by separating them with a comma.

If credentials are supplied in the request header, then creds is ignored since HTTP Basic Authentication takes precedence and overrides creds.

Example
cbq> \set -creds travel-sample user:pword, beer-sample user:pword;

array

encoded_plan
Optional

In Couchbase Server 6.5 and later, this parameter is ignored and has no effect. It is included for compatibility with previous versions of Couchbase Server.

string

encoding
Optional

Desired character encoding for the query results.

Only possible value is UTF-8 and is case-insensitive.

Default

"UTF-8"

string

format
Optional

Desired format for the query results.

Values are case-insensitive.

Default

"JSON"

Example
cbq> \set -format "XML";

enum (JSON, XML, CSV, TSV)

max_parallelism
Optional

Specifies the maximum parallelism for the query.

A zero or negative value means the number of logical CPUs will be used as the parallelism for the query.

The server-level max-parallelism setting defaults to 1. This is used when a request does not include this parameter.

If a request includes max_parallelism, it will be capped by the server max-parallelism.

To enable queries to run in parallel, you must specify the Server-level max-parallelism parameter on all Query nodes.
Example
cbq> \set -max_parallelism 3;

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&max_parallelism=3'

integer (int32)

metrics
Optional

Specifies that metrics should be returned with query results.

Default

true

Example
cbq> \set -metrics false;

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&metrics=false'

boolean

namespace
Optional

Specifies the namespace to use.

Example
cbq> \set -namespace travel-sample;

string

pipeline_batch
Optional

Controls the number of items execution operators can batch for Fetch from the KV.

Example
cbq> \set -pipeline_batch 64;

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&pipeline_batch=64'

integer (int32)

pipeline_cap
Optional

Maximum number of items each execution operator can buffer between various operators.

Example
cbq> \set -pipeline_cap 1024;

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&pipeline_cap=1024'

integer (int32)

prepared
Required if statement not provided

The prepared form of the N1QL statement to be executed.

If both prepared and statement are present and non-empty, an error is returned.
Example

Prepare the query result of the most expensive hotel:

$ curl -v http://localhost:8093/query/service -d 'statement=PREPARE pricy_hotel FROM SELECT MAX(price) FROM `travel-sample` WHERE type="hotel";'

Response:

{
  "requestID": "b7f03c4e-06f6-4d23-bd14-b5c8ecfe0e2e",
  "signature": "json",
  "results": [
    {"encoded_plan":"H4sIAAAAAAAA/wEAAP//AAAAAAAAAAA=",
    "featureControls":12,
    "indexApiVersion":3,
    "name":"[127.0.0.1:8091]pricy_hotel",
...

Execute the prepared statement:

$ curl -v http://localhost:8093/query/service -H "Content-Type: application/json" -d '{ "prepared":"[127.0.0.1:8091]pricy_hotel" }'

string

pretty
Optional

Specifies the query results returned in pretty format.

There is also a server-level pretty setting which defaults to true. If a request does not include pretty, the server-level pretty will be used.

Default

true

Example
cbq> \set -pretty false;

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&pretty=false'

boolean

profile
Optional

Specifies if there should be a profile section returned with the request results. The valid values are:

off

No profiling information is added to the query response.

phases

The query response includes a profile section with stats and details about various phases of the query plan and execution. Three phase times will be included in the system:active_requests and system:completed_requests monitoring keyspaces.

timings

Besides the phase times, the profile section of the query response document will include a full query plan with timing and information about the number of processed documents at each phase. This information will be included in the system:active_requests and system:completed_requests keyspaces.

If profile is not set as one of the above values, then the profile setting does not change.
Default

"off"

Example
cbq> \set -profile "phases";

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&profile=phases'

enum (off, phases, timings)

readonly
Optional

Controls whether a query can change a resulting recordset.

If readonly is true, then the following statements are not allowed:

  • CREATE INDEX

  • DROP INDEX

  • INSERT

  • MERGE

  • UPDATE

  • UPSERT

When using GET requests, it’s best to set readonly to true.
Default

false

Example
cbq> \set -readonly true;

boolean

scan_cap
Optional

Maximum buffered channel size between the indexer client and the query service for index scans. This parameter controls when to use scan backfill.

Use 0 or a negative number to disable.

Smaller values reduce GC while larger values reduce indexer backfill.

The index channel capacity is configurable per request.

Default

512

Example
cbq> \set -scan_cap 1024;

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&scan_cap=1024'

integer (int32)

scan_consistency
Optional

Specify the consistency guarantee/constraint for index scanning. The valid values are:

not_bounded

No timestamp vector is used in the index scan. This is the fastest mode, because it avoids the costs of obtaining the vector and waiting for the index to catch up to the vector.

at_plus

This implements bounded consistency. The request includes a scan_vector parameter and value, which is used as a lower bound. This can be used to implement read-your-own-writes (RYOW).

request_plus

This implements strong consistency per request. Before processing the request, a current vector is obtained. The vector is used as a lower bound for the statements in the request. If there are DML statements in the request, RYOW is also applied within the request.

statement_plus

This implements strong consistency per statement. Before processing each statement, a current vector is obtained and used as a lower bound for that statement.

The default behavior is RYOW within the request, however, if you want to disable RYOW within a request, add a separate request_consistency parameter that can be set to not_bounded.

Values are case-insensitive.

Default

"not_bounded"

Example
cbq> \set -scan_consistency "at_plus";

enum (not_bounded, at_plus, request_plus, statement_plus)

scan_vector
Required if scan_consistency is at_plus and scan_vectors not provided

Specify the lower bound vector timestamp for one bucket when using at_plus scan consistency.

Scan vectors are built of two-element [value, guard] entries:

  • value: a vBucket’s sequence number (a JSON number)

  • guard: a vBucket’s UUID (a string)

Scan vectors have two forms:

  1. Full scan vector: an array of [value, guard] entries, giving an entry for every vBucket in the system.

  2. Sparse scan vectors: an object providing entries for specific vBuckets, mapping a vBucket number (a string) to each [value, guard] entry.

scan_vector can only be used if the query uses at most one bucket; if it is used for a query referencing more than one bucket, the query will fail with an error.

For queries referencing multiple buckets, use scan_vectors.

Example
scan_vector={ "5 ": [5409393,"VB5ID"], "19": [47574574, "VB19ID"] }

array, object

scan_vectors
Required if scan_consistency is at_plus and scan_vector not provided

A map from bucket names to scan vectors. See scan_vector.

The scan vectors can be Full or Sparse.

object

scan_wait
Optional

Can be supplied with scan_consistency values of request_plus, statement_plus and at_plus.

Specifies the maximum time the client is willing to wait for an index to catch up to the vector timestamp in the request.

If an index has to catch up, and the scan_wait time is exceeded while waiting, an error is returned.

Its format includes an amount and a mandatory unit, e.g. 10ms (10 milliseconds) or 0.5s (half a second). Valid units are:

  • ns (nanoseconds)

  • us (microseconds)

  • ms (milliseconds)

  • s (seconds)

  • m (minutes)

  • h (hours)

Specify 0 or a negative integer to disable.
Default

""

Example
cbq> \set -scan_wait "30m";

string (duration)

signature
Optional

Include a header for the results schema in the response.

Default

true

Example
cbq> \set -signature false;

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&signature=false'

boolean

statement
Required if prepared not provided

Any valid N1QL statement for a POST request, or a read-only N1QL statement (SELECT, EXPLAIN) for a GET request.

If both prepared and statement are present and non-empty, an error is returned.

string

timeout
Optional

Maximum time to spend on the request before timing out.

The default value means no timeout is applied and the request runs for however long it takes.

There is also a server-level timeout setting. The minimum of that and the request-level timeout parameter is applied.

Its format includes an amount and a mandatory unit, e.g. 10ms (10 milliseconds) or 0.5s (half a second). Valid units are:

  • ns (nanoseconds)

  • us (microseconds)

  • ms (milliseconds)

  • s (seconds)

  • m (minutes)

  • h (hours)

Specify 0 or a negative integer to disable.
Default

"0s"

Example
cbq> \set -timeout "30m";

$ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&timeout=30m'

string (duration)

$<identifier>
Optional

If the statement has 1 or more named parameters, there should be 1 or more named parameters in the request.

A named parameter consists of two parts:

  1. The $ character

  2. An identifier that starts with an alpha character followed by one or more alphanumeric characters.

Named parameters apply to prepared also.

See section Named Parameters VS. Positional Parameters for examples.

JSON value

Named Parameters VS. Positional Parameters

Named Parameters use a variable name to refer to each one, while Positional Parameters refer to the position each variable is used. As summarized in the below table, these two types of requests should contain the following parameters:

Table 5. Named Parameters VS. Positional Parameters
Statement Args

Named Parameters

SELECT detail FROM emp WHERE name = $nval AND age > $aval

$nval = "smith"

$aval = 45

Positional Parameters

SELECT detail FROM emp WHERE name = $1 AND age > $2
SELECT detail FROM emp WHERE name = ? AND age > ?

[ "smith", 45 ]

Positional Parameters can also be specified in a statement using ? as an alternative way to specify the same query.

For more details about the N1QL REST API, refer to N1QL REST API.

For more details about the Admin REST API, refer to Admin REST API.

For more details about API content and settings, refer to REST API reference.