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 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 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 the entire list of service-level query settings:

    {"completed-limit":4000,"completed-threshold":1000,"controls":false,"cpuprofile":"","debug":false,"keep-alive-length":16384,"loglevel":"INFO","max-index-api":3,"max-parallelism":1,"memprofile":"","n1ql-feat-ctrl":0,"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 below Service-Level Query Settings table.

    Table of Query Setting Levels and Overrides

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

    Table 2.
    Service-level Only Settings Request-level Only Settings

    Both Service-level and Request-level Settings

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

    The max-parallelism parameter defaults to "1" and will be used when a request does not include this parameter.

    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.
    Example
    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
    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
    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 request-level pretty parameter. If a request does not include this parameter, the server-level pretty setting will be used, which defaults to false.

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

    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://localhost:8093/query/service -u user:pword -d 'statement=select * from default&controls=true'

    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

    int

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

    metrics

    bool

    true

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

    Example
    cbq> \set -metrics false;
    
    curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&metrics=false'

    namespace

    string

    [Optional] Specifies the namespace to use.

    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://localhost:8093/query/service -u user:pword -d 'statement=select * from default&pipeline_batch=64'

    pipeline_cap

    int

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

    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

    false

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

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

    Example
    cbq> \set -pretty true;
    
    curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&pretty=true'

    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://localhost:8093/query/service -u user:pword -d 'statement=select * from default&profile=phases'

    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.
    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://localhost:8093/query/service -u user:pword -d 'statement=select * from default&scan_cap=1024'

    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.

    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-level timeout setting. The minimum of that and the request-level timeout parameter is 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://localhost:8093/query/service -u user:pword -d 'statement=select * from default&timeout=30m'

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