Query Settings

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
Note: The Request-level Parameters overwrite their Service-level Query Setting equivalents.
Note: 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.

Table 3. Service-Level Query Settings
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.

Note: 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 "".

Note: 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 "".

Note: 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 The number of bytes the Prepared Limit sizes the prepared statement 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.
Note: 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.

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

Table 4. Request-Level Parameters
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.
Note: 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.

Note: 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.

Note: 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.

Note: 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.

Note: 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.
Note: 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.
Note: 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
Note: When using GET requests, it's best to set readonly to true.
Note: 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.
Note: 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:

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

Note: 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).
Note: 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.
Note: 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.

Note: 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.

Note: 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).

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

  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.

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 ]
SELECT detail FROM emp WHERE name = ? AND age > ?
Note: 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.