Query Settings

Description of Service-level query settings and methods to set and use them.

The Couchbase Query Service is made of two components, Service-level query settings and Request-level parameters, which are set, used, and behave differently:

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

The Request-level Parameters overwrite 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 set a Service-level query setting, while the Query Service is running, use the /admin/settings REST API endpoint with a curl statement.

To see a list of the current Query Settings, enter:

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

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

{"completed-limit":7000,"completed-threshold":0,"controls":false,"cpuprofile":"","debug":false,"keep-alive-length":16384,"loglevel":"INFO","max-parallelism":1,"memprofile":"","pipeline-batch":16,"pipeline-cap":512,"prepared-limit":16384,"pretty":false,"profile":"off","request-size-cap":67108864,"scan-cap":512,"servicers":32,"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 below Service-Level Query Settings table.

Table of Query Setting Levels and Overwrites

Some query settings are service-level or request-level only, while some are both levels with slightly different names.

Table 2.
Service-level Name	Request-level Name
Service-level Only Settings completed-limit completed-threshold cpuprofile debug keep-alive-length loglevel memprofile request-size-cap servicers _ NOTE: These settings can not be set by cbq.	Request-level Only Settings args batch-args batch_named_args client_context compression creds encoded_plan encoding format namespace prepared scan_consistency scan_vector scan_vectors scan_wait statement $<identifier>
Both Service-level and Request-level Settings
controls max-parallelism metrics pipeline-batch pipeline-cap pretty profile readonly scan-cap signature timeout	controls max_parallelism metrics * pipeline_batch pipeline_cap pretty profile readonly * scan_cap signature * timeout

Request-level Parameters overwrite their Service-level equivalents, except metrics, readonly, and signature.

Service-Level Query Settings

Below is a description of each query setting along with examples. 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. These are the only two ways to set these settings, and not all settings can be set by cbq.

Setting Type Default Description

completed-limit

int

4000

Maximum number of completed requests. 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 customers want a larger sample of slow requests.

Example

curl http://hostname:8093/admin/settings -d '{"completed-limit":7000}' -u user:pword

completed-threshold

int

1000

Cache completed query lasting longer than this many milliseconds.

Specify 0 to track all requests independent of duration.

Specify any negative number to track none.

Example

curl http://hostname:8093/admin/settings -d '{"completed-threshold":7000}' -u user:pword

controls

bool

false

[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.

Example

cbq> \set -controls true;

curl http://hostname:8093/admin/settings -d '{"controls":true}' -u user:pword

cpuprofile

string

""

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.

Example

curl http://hostname:8093/admin/settings -d '{"cpuprofile":"/tmp/info.txt"}' -u user:pword

debug

bool

false

Use debug mode.

When set to true, extra logging is provided.

Example

curl http://hostname:8093/admin/settings -d '{"debug":true}' -u user:pword

keep-alive-length

int

16384

Maximum size of buffered result.

Example

curl http://hostname:8093/admin/settings -d '{"keep-alive-length":7000}' -u user:pword

loglevel

string

"INFO"

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.

Example

curl http://hostname:8093/admin/settings -d '{"loglevel":"DEBUG"}' -u user:pword

max-parallelism

int

1

[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.

A server-wide max_parallelism parameter defaults to "1" and will be used when a request does not include this parameter.

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

Example

cbq> \set -max_parallelism 3;

curl http://hostname:8093/admin/settings -d '{"max_parallelism":0}' -u user:pword

memprofile

string

""

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.

Example

curl http://hostname:8093/admin/settings -d '{"memprofile":"/tmp/memory-usage.log"}' -u user:pword

pipeline-batch

int

16

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

Example

cbq> \set -pipeline_batch 64;

curl http://hostname:8093/admin/settings -d '{"pipeline-batch":64' -u user:pword

pipeline-cap

int

512

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

Example

cbq> \set -pipeline_cap 1024;

curl http://hostname:8093/admin/settings -d '{"pipeline-cap":1024}' -u user:pword

prepared-limit

int

16384

[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.

Example

curl http://hostname:8093/admin/settings -d '{"prepared-limit":65536}' -u user:pword

pretty

bool

false

[Optional] Specifies the query results returned in pretty format.

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

Example

cbq> \set -pretty true;

curl http://hostname:8093/admin/settings -d '{"pretty":false}' -u user:pword

profile

string

off

[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.

Example

cbq> \set -profile "phases";

curl http://hostname:8093/admin/settings -d '{"profile":"phases"}' -u user:pword

request-size-cap

int

67108864

Maximum size of a request.

Example

curl http://hostname:8093/admin/settings -d '{"request-size-cap":70000}' -u user:pword

scan-cap

int

512

[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.

Example

cbq> \set -scan_cap 1024;

curl http://hostname:8093/admin/settings -d '{"scan-cap":1024}' -u user:pword

servicers

int

32

The number of service threads for the query.

Example

curl http://hostname:8093/admin/settings -d '{"servicers":8}' -u user:pword

timeout

string (duration format)

"0s"

[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-wide timeout parameter, and the minimum of that and the request timeout is what gets applied.

Its format includes an amount and a mandatory unit. Valid units are:

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

Ex: "10ms" (10 milliseconds) and "0.5s" (half a second).

Specify 0 or a negative integer to disable.

Example

cbq> \set -timeout "30m";

curl http://hostname:8093/admin/settings -d '{"timeout":"30m"}' -u user:pword

Request-Level Parameters

This table contains details of all the parameters that can be passed in a request to the /query/service endpoint:

Parameter Name Type Default Description

args

list

[Optional] If the statement has 1 or more positional parameters, this parameter needs to be in the request; this is a list of JSON values, one for each positional parameter in the statement.

Positional parameters apply to prepared also.

Example

cbq > \set -args ["LAX", 6];

See section Named Parameters VS. Positional Parameters for details.

batch_args

list of list

[Optional] Applies to POST requests containing UPDATE/INSERT/DELETE statements.

DML statements containing positional parameters.

Example

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

These require the values to be given in batch_args, which contains a list of lists.

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

batch_named_args

list of object

[Optional] Applies to POST requests only, containing a UPDATE/INSERT/DELETE statement.

DML statements containing named parameters.

Example

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

These require the values to be given in batch_named_args, which contains a list of objects.

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

client_context_id

string

[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.

Note:

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

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

compression

string

"NONE"

[Optional] Compression format to use for response data on the wire.

Possible values are ZIP, RLE, LZMA, LZO, or NONE.

Values are case-insensitive.

Example

cbq> \set -compression "zip";

controls

bool

false

[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.

Example

cbq> \set -controls true;

curl http://hostname:8093/admin/settings -d '{"controls":true}' -u user:pword

creds

list

[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;

encoded_plan

string

[Optional] For later, multiple executions, a query can be prepared, which results in five properties, of which one is called encoded_plan. This can then be used to execute the query.

Example: Prepare the query result of the most expensive hotel.

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

Response:

{
  "requestID": "a339a496-7ed5-4625-9c64-0d7bf584a1bd",
  "signature": "json",
  "results": [
  {   "encoded_plan": "H4sIAAAJbogA/5yRQU/6QBDFvwpZ/gdIIAAA==",
        "name": "fave_tweets",

Use the encoded_plan to execute that prepared statement.

$ curl -v http://localhost:8093/query/service -H "Content-Type: application/json" -d  \
'{ "prepared":"pricy_hotel", "encoded_plan":"H4sIAAAJbogA/5yRQU/6QBDFvwpZ/gdIIAAA==", "$r":9.5 }'

Both the encoded plan and the prepared N1QL statement output the same.

encoding

string

"UTF-8"

[Optional] Desired character encoding for the query results.

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

format

string

"JSON"

[Optional] Desired format for the query results.

Possible values are JSON, XML, CSV, and TSV.

Values are case-insensitive.

Example

cbq> \set -format "XML";

max_parallelism

string

"1"

[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.

A server-wide max_parallelism parameter defaults to "1" and will be used when a request does not include this parameter.

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

Example

cbq> \set -max-parallelism 3;

curl http://hostname:8093/admin/settings -d '{"max-parallelism":0}' -u user:pword

metrics

bool

false

[Optional] Specifies that metrics should be returned with query results.

There is also a server wide metrics parameter which defaults to true. If a request does not include metrics, the server wide metrics will be used.

Example

cbq> \set -metrics false;

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

namespace

string

[Optional] Specifies the namespace to use.

There is a server-wide namespace parameter, which is used if a request does not specify a namespace.

Example

cbq> \set -namespace travel-sample;

pipeline_batch

int

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

Example

cbq> \set -pipeline_batch 64;

curl http://hostname:8093/admin/settings -d '{"pipeline-batch":64}' -u user:pword

pipeline_cap

int

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

Example

cbq> \set -pipeline_cap 1024;

curl http://hostname:8093/admin/settings -d '{"pipeline-cap":1024}' -u user:pword

prepared

string

[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 name, max(price) FROM `travel-sample` WHERE type="hotel";

Response:

{
  "requestID": "a339a496-7ed5-4625-9c64-0d7bf584a1bd",
  "signature": "json",
  "results": [
  {   "encoded_plan": "H4sIAAAJbogA/5yRQU/6QBDFvwpZ/gdIIAAA==",
        "name": "fave_tweets",

Use the encoded_plan to execute that prepared statement.

$ curl -v http://localhost:8093/query/service -H "Content-Type: application/json" -d  \
'{ "prepared":"pricy_hotel", "encoded_plan":"H4sIAAAJbogA/5yRQU/6QBDFvwpZ/gdIIAAA==", "$r":9.5 }'

Both the encoded plan and the prepared N1QL statement output the same.

pretty

bool

true

[Optional] Specifies the query results returned in pretty format.

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

Example

cbq> \set -pretty true;

curl http://hostname:8093/admin/settings -d '{"pretty":false}' -u user:pword

profile

string

"off"

[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.

Example

cbq> \set -profile "phases";

curl http://hostname:8093/admin/settings -d '{"profile":"phases"}' -u user:pword

readonly

bool

false

[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.

There is a server-wide readonly parameter; and if that is true, it supersedes the request readonly parameter.

Example

cbq> \set -readonly true;

scan_cap

int

512

[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.

Example

cbq> \set -scan_cap 1024;

curl http://hostname:8093/admin/settings -d '{"scan-cap":1024}' -u user:pword

scan_consistency

string

"not_bounded"

[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.

Example

cbq> \set -scan_consistency "at_plus";

scan_vector

list or object

[scan_vector or scan_vectors is required if scan_consistency=at_plus]

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

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

value: a vbucket’s sequence number (a JSON number)
guard: a vbucket’s UUID (a string)

Example

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

Scan vectors have two forms:

Full scan vector: an array of value, guard] entries, giving an entry for every vbucket in the system.
Sparse scan vectors: provide entries for specific vbuckets, mapping a vbucket number (a string) to a 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.

scan_vectors

object

[scan_vector or scan_vectors is required if scan_consistency=at_plus]

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

The scan vectors can be Full or Sparse.

scan_wait

string (duration format)

""

[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. Valid units are:

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

Ex: 10ms (10 milliseconds) and 0.5s (half a second).

Specify 0 or a negative integer to disable.

Example

cbq> \set -scan_wait "30m";

signature

bool

true

[Optional] Include a header for the results schema in the response.

There is a server-wide signature parameter which defaults to true and will be used if a request does not include signature.

Example

cbq> \set -signature false;

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

statement

string

[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.

timeout

string (duration format)

"0s"

[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-wide timeout parameter, and the minimum of that and the request timeout is what gets applied.

Its format includes an amount and a mandatory unit. Valid units are:

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

Ex: 10ms (10 milliseconds) and 0.5s (half a second).

Specify 0 or a negative integer to disable.

Example

cbq> \set -timeout "30m";

curl http://hostname:8093/admin/settings -d '{"timeout":"30m"}' -u user:pword

$<identifier>

json_value

[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:

The $ character
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.

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	[ "smith", 45 ]
Positional Parameters	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 N1QL REST API, see N1QL REST API.

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