A newer version of this documentation is available.

View Latest

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.

    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.

    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

    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.

    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:

    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.

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

    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:

    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.

    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:

    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 > ?

    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.