Settings and Parameters

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

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

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

    Service-level Query Settings

    Service Node

    The administrator at the system level

    Server side

    cURL statements

    Request-level Parameters

    Request (statement)

    Each user

    Client side

    cbq command-line parameters, cURL statements, or client programming

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

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

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

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

    {"auto-prepare":false,"completed":{"aborted":null,"threshold":1000},"completed-limit":4000,"completed-threshold":1000,"controls":false,"cpuprofile":"","debug":false,"functions-limit":16384,"keep-alive-length":16384,"loglevel":"INFO","max-index-api":4,"max-parallelism":1,"memprofile":"","mutexprofile":false,"n1ql-feat-ctrl":12,"pipeline-batch":16,"pipeline-cap":512,"prepared-limit":16384,"pretty":false,"profile":"off","request-size-cap":67108864,"scan-cap":512,"servicers":4,"timeout":0}

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

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

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

    Table of Query Setting Levels and Overrides

    Service-Level Query Settings

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

    These settings can not be set by cbq.

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

    Table 3. Service-Level Query Settings
    Name Description Schema

    auto-prepare
    optional

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

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

    boolean

    completed
    optional

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

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

    completed-limit
    optional

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

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

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

    integer (int32)

    completed-threshold
    optional

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

    Specify 0 to track all requests, independent of duration.

    Specify any negative number to track none.

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

    integer (int32)

    controls
    optional

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

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

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

    Default : false
    Example : true

    boolean

    cpuprofile
    optional

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

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

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

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

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

    string

    debug
    optional

    Use debug mode.

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

    boolean

    distribute
    optional

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

    boolean

    keep-alive-length
    optional

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

    integer (int32)

    loglevel
    optional

    Log level used in the logger.

    All values, in descending order of data:

    DEBUG — For developers. Writes everything.

    TRACE — For developers. Less info than DEBUG.

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

    WARN — For admin. Only abnormal items.

    ERROR — For admin. Only errors to be fixed.

    SEVERE — For admin. Major items, like crashes.

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

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

    max-index-api
    optional

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

    integer (int32)

    max-parallelism
    optional

    Specifies the maximum parallelism for queries on this node.

    If the value is zero or negative, the maximum parallelism is restricted to the number of allowed cores. Similarly, if the value is greater than the number of allowed cores, the maximum parallelism is restricted to the number of allowed cores.

    (The number of allowed cores is the same as the number of logical CPUs. In Community Edition, the number of allowed cores cannot be greater than 4. In Enterprise Edition, there is no limit to the number of allowed cores.)

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

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

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

    integer (int32)

    memprofile
    optional

    Filename to write the diagnostic memory usage log.

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

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

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

    string

    mutexprofile
    optional

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

    boolean

    n1ql-feat-ctrl
    optional

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

    integer (int32)

    pipeline-batch
    optional

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

    integer (int32)

    pipeline-cap
    optional

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

    integer (int32)

    prepared-limit
    optional

    Maximum number of prepared statements in the cache.

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

    integer (int32)

    pretty
    optional

    Specifies whether query results are returned in pretty format.

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

    boolean

    profile
    optional

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

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

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

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

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

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

    enum (off, phases, timings)

    request-size-cap
    optional

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

    integer (int32)

    scan-cap
    optional

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

    Use 0 or a negative number to disable.

    Smaller values reduce GC while larger values reduce indexer backfill.

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

    integer (int32)

    servicers
    optional

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

    integer (int32)

    timeout
    optional

    Maximum time to spend on the request before timing out (ns).

    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.

    The service-level timeout setting is an integer, representing a duration in nanoseconds. It must not be delimited by quotes, and must not include a unit.

    Specify 0 or a negative integer to disable.
    Default : 0
    Example : 500000000

    integer (int64)

    Logging parameters

    Name Description Schema

    aborted
    optional

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

    boolean

    client
    optional

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

    string

    context
    optional

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

    Refer to client_context_id for more information.

    string

    error
    optional

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

    integer (int32)

    tag
    optional

    A unique string which tags a set of qualifiers.

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

    string

    threshold
    optional

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

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

    integer (int32)

    user
    optional

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

    string

    Request-Level Parameters

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

    While cbq is a sandbox to test code on your local machine, your production query settings are set with the cURL commands on your server.

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

    Table 4. Request-Level Parameters
    Name Description Schema

    args
    Optional

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

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

    array

    auto_execute
    Optional

    Specifies that prepared statements should be executed automatically as soon as they are created. This saves you from having to make two separate requests in cases where you want to prepare a statement and execute it immediately.

    Refer to Auto-Execute for more information.

    Default

    false

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

    boolean

    batch_args
    Optional

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

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

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

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

    array of arrays

    batch_named_args
    Optional

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

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

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

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

    array of objects

    client_context_id
    Optional

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

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

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

    string

    compression
    Optional

    Compression format to use for response data on the wire.

    Values are case-insensitive.

    Default

    "NONE"

    Example
    cbq> \set -compression "zip";

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

    controls
    Optional

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

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

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

    false

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

    boolean

    creds
    Optional

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

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

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

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

    array

    encoded_plan
    Optional

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

    string

    encoding
    Optional

    Desired character encoding for the query results.

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

    Default

    "UTF-8"

    string

    format
    Optional

    Desired format for the query results.

    Values are case-insensitive.

    Default

    "JSON"

    Example
    cbq> \set -format "XML";

    enum (JSON, XML, CSV, TSV)

    max_parallelism
    Optional

    Specifies the maximum parallelism for the query.

    If the value is zero or negative, the parallelism for the query is set to the server-level max-parallelism setting. Similarly, if the value is greater than the server-level max-parallelism, the parallelism for the query is limited to the server-level setting.

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

    The same as the number of partitions of the index selected for the query.

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

    integer (int32)

    metrics
    Optional

    Specifies that metrics should be returned with query results.

    Default

    true

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

    boolean

    namespace
    Optional

    Specifies the namespace to use.

    Example
    cbq> \set -namespace travel-sample;

    string

    pipeline_batch
    Optional

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

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

    integer (int32)

    pipeline_cap
    Optional

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

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

    integer (int32)

    prepared
    Required if statement not provided

    The prepared form of the N1QL statement to be executed.

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

    Prepare the query result of the most expensive hotel:

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

    Response:

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

    Execute the prepared statement:

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

    string

    pretty
    Optional

    Specifies the query results returned in pretty format.

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

    Default

    true

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

    boolean

    profile
    Optional

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

    off

    No profiling information is added to the query response.

    phases

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

    timings

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

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

    "off"

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

    enum (off, phases, timings)

    readonly
    Optional

    Controls whether a query can change a resulting recordset.

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

    • CREATE INDEX

    • DROP INDEX

    • INSERT

    • MERGE

    • UPDATE

    • UPSERT

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

    false

    Example
    cbq> \set -readonly true;

    boolean

    scan_cap
    Optional

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

    Use 0 or a negative number to disable.

    Smaller values reduce GC while larger values reduce indexer backfill.

    The index channel capacity is configurable per request.

    Default

    512

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

    integer (int32)

    scan_consistency
    Optional

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

    not_bounded

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

    at_plus

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

    request_plus

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

    statement_plus

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

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

    Values are case-insensitive.

    Default

    "not_bounded"

    Example
    cbq> \set -scan_consistency "at_plus";

    enum (not_bounded, at_plus, request_plus, statement_plus)

    scan_vector
    Required if scan_consistency is at_plus and scan_vectors not provided

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

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

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

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

    Scan vectors have two forms:

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

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

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

    For queries referencing multiple buckets, use scan_vectors.

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

    array, object

    scan_vectors
    Required if scan_consistency is at_plus and scan_vector not provided

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

    The scan vectors can be Full or Sparse.

    object

    scan_wait
    Optional

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

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

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

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

    • ns (nanoseconds)

    • us (microseconds)

    • ms (milliseconds)

    • s (seconds)

    • m (minutes)

    • h (hours)

    Specify 0 or a negative integer to disable.
    Default

    ""

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

    string (duration)

    signature
    Optional

    Include a header for the results schema in the response.

    Default

    true

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

    boolean

    statement
    Required if prepared not provided

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

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

    string

    timeout
    Optional

    Maximum time to spend on the request before timing out.

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

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

    The request-level timeout parameter is a string. Its format includes an amount and a mandatory unit, e.g. 10ms (10 milliseconds) or 0.5s (half a second). Valid units are:

    • ns (nanoseconds)

    • us (microseconds)

    • ms (milliseconds)

    • s (seconds)

    • m (minutes)

    • h (hours)

    Specify a duration of 0 or a negative duration to disable.
    Default

    "0s"

    Example
    cbq> \set -timeout "30m";
    
    $ curl http://localhost:8093/query/service -u user:pword -d 'statement=select * from default&timeout=30m'

    string (duration)

    $<identifier>
    Optional

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

    A named parameter consists of two parts:

    1. The $ character

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

    Named parameters apply to prepared also.

    See section Named Parameters VS. Positional Parameters for examples.

    JSON value

    Named Parameters VS. Positional Parameters

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

    Table 5. Named Parameters VS. Positional Parameters
    Statement Args

    Named Parameters

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

    $nval = "smith"

    $aval = 45

    Positional Parameters

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

    [ "smith", 45 ]

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

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

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

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