The SQL++ Query Language Per-Request Memory Quota

Background

With the release of Couchbase Server 7.0, the Query Service now includes a per-request memory quota. The bulk of query service operations (N1QL requests) rely on transient values (either fetched documents or computed values). In Data or Index services, we can evict documents and indexed data to free up some memory. In SQL++ there’s nothing to evict and replace.

Furthermore, parts of the Eventing, Index, and Full-Text Search services code run inside the query service. They use SQL++ memory resources, but SQL++ has no control over them.

While SQL++ memory consumption is not an issue in general – requests quickly load and discard documents, sometimes an odd greedy request can occupy all the available memory. To handle such situation, the Couchbase Query Service allows to set Per-Request Memory Quota. When the per-request memory quota is turned on, each query request gets its own memory pool. Memory tracking operates as usual, but only the greedy query fails when the pool is exhausted.

The query service has no explicit quota that can be set. However, the query service allows only a fixed number of requests running at any one time. This option is controlled by the servicers setting, and it defaults to four times the number of cores on the query node. The overall node memory quota amounts to the number of servicers times the per-request quota. The two quotas are intimately intertwined: the per-request quota is explicit because we wanted to be clear that it’s individual requests that are being tracked, not the node in its entirety.

How to use Per-Request Memory Quota

There are two settings for the per-request memory quota in SQL++:

The /admin/settings node REST parameter memory-quota
The /query/service request REST parameter memory_quota

These settings express in megabytes the maximum amount of memory a request can use at any one time.

The default memory-quota is zero, i.e., the memory quota is turned off. The memory_quota setting always overrides the node-wide setting as long as the requested value does not exceed it.

Let’s look at a few examples. This command below sets your memory quota to 10MB for the whole node and replicates the setting to all your other nodes:

curl http://localhost:8093/admin/settings -u Administrator:password -H "content-type: application/json" -d '{"memory-quota": 10, "distribute": true}'

This command sets your memory quota to 10MB for the single request, as you can see below:

curl http://localhost:8093/query/service -u Administrator:password -d 'statement=select * from `travel-sample`&memory_quota=10&pretty=true'

Finally, the one below sets your memory quota to 10MB for the duration of the cbq session:

cbq> \set -memory_quota 10;

Responses and System Keyspaces

If your memory quota is set (by whatever means), then several SQL++ features may contain additional information, including:

• Metrics
• Controls
• System keyspaces

Let’s take a closer look at each of these responses:

Responses: Metrics

The metrics section of the response contains a usedMemory field showing the amount of document memory used to execute the request:

"metrics": {
"elapsedTime": "1.651329ms",
"executionTime": "1.439388ms",
"resultCount": 1,
"resultSize": 153,
"serviceLoad": 2,
"usedMemory": 63
}

If no document memory is used, this metric is omitted. The same omission occurs with mutations or errorCount as well.

Responses: Controls

The controls section of the response also reports the memory quota according to your settings. Here’s what it looks like:

"controls": {
"scan_consistency": "unbounded",
"use_cbo": "true",
"memoryQuota": "10",
"stmtType": "SELECT"
}

Responses: System Keyspaces

System keyspaces – both system:active_requests and system:completed_requests – also contain memory quota information. The usedMemory and memoryQuota information shows up here as well. Take a look at the example below:

"active_requests": {
"elapsedTime": "2.604037201s",
"executionTime": "2.603863968s",
"memoryQuota": 10,
"node": "127.0.0.1:8091",
"phaseCounts": {
"fetch": 15936,
"primaryScan": 16362
},
"phaseOperators": {
"authorize": 1,
"fetch": 1,
"primaryScan": 1
},
"phaseTimes": {
"authorize": "721.012µs",
"fetch": "588.220088ms",
"instantiate": "11.547µs",
"parse": "1.317113ms",
"plan": "167.599824ms",
"primaryScan": "29.589176ms"
},
"remoteAddr": "127.0.0.1:55084",
"requestId": "da0d5ed0-a3d9-4ad2-86af-ae483fdadbce",
"requestTime": "2020-11-19T15:45:27.368Z",
"scanConsistency": "unbounded",
"state": "running",
"statement": "select * from `travel-sample`",
"useCBO": true,
"usedMemory": 3187392,
"userAgent": "curl/7.58.0",
"users": "Administrator"
}

How Is Memory Used?

Before we delve into some of the mechanics of the memory quota operation, let's review how a request uses memory. The usedMemory metrics field has been introduced to gauge the memory requirements of an individual statement before it’s allowed to run. If we run the REST API query below, we get:

$ curl http://127.0.0.1:8093/query/service -u Administrator:password -d 'statement=select * from `travel-sample`;
 & memory_quota=10 & pretty=true'
...
"status": "success",
"metrics": {
"elapsedTime": "6.353013176s",
"executionTime": "6.352882377s",
"resultCount": 31591,
"resultSize": 95381286,
"serviceLoad": 2,
"usedMemory": 3264905
}
}

As you can see above, the used memory is not the size of the result set. We can decline using the formatting ( by setting pretty=false). That way, the size of the result set is as close as possible to the size of the data in storage:

$ curl http://127.0.0.1:8093/query/service -u Administrator:password -d 'statement=select * from `travel-sample`;
 & memory_quota=10 & pretty=false'
...
"status": "success",
"metrics": {"elapsedTime": "1.559014343s","executionTime": "1.558937894s","resultCount": 31591,"resultSize": 36754134,"serviceLoad": 2,"usedMemory": 588514}
}

If we send the results to a file, it removes the costs of displaying the results, decreasing the usedMemory even more:

$ curl http://127.0.0.1:8093/query/service -u Administrator:password -d 'statement=select * from `travel-sample`; & memory_quota=10 & pretty=false'>/tmp/res 2>&1; tail /tmp/res
...
"status": "success",
"metrics": {"elapsedTime": "854.674127ms","executionTime": "854.575802ms","resultCount": 31591,"resultSize": 36754134,"serviceLoad": 2,"usedMemory": 188626}
}

The Request Execution Phase Operation

The execution phase of a request employs a pipeline of operators that execute in parallel. Each operator receives values from the previous stage, processes those values, and then sends them onto the next.

The operator value exchange infrastructure includes a values queue so that each operator is not blocked by the previous one or the next one. (In fact, the execution engine is more complicated. Some operators are inlined into others and some only exist to carry out orchestration work, so value queues are not always involved, but still).

For example, a simple query like this:

SELECT * FROM bucket WHERE field = constant

uses an Index scan to produce keys, which are sent to a Fetch to retrieve documents from the key-value cache, which are sent to a Filter to exclude documents that do not apply, and those that do are sent to a Projection to extract fields and marshal them into JSON (if necessary) and finally passed to a Stream which writes them back to the client.

Values that complete the course are eventually disposed of during garbage collection.

For the example above, if there were available cores to execute all these operators in parallel – and all operators executed at exactly the same speed – there would never be more than five documents traversing the pipeline at any one time, even though the request might process any number of documents.

Of course, a Scan might produce keys much faster that a Fetch could gather documents and marshalling could be expensive. Sending results over the wire back to the client might be slow, so even if there are cores available, the queues described above will be used as buffers for values waiting to be processed along the line. In turn, this temporarily increases the amount of memory a request needs to process the sequence of incoming values.

This pattern explains why both making the Projection more efficient (pretty=false), or Stream (sending to a file rather than the terminal) has a beneficial effect on memory consumption: faster operators mean fewer values stuck in the value exchange queues.

With request load increasing, the SQL++ kernel has more operators to schedule, meaning that while they are not run, the value queue for the previous operator increases in size, meaning even more memory is required to process individual requests. All told, loaded nodes use more memory than those with little activity.

Conclusion

Before the Couchbase Server 7.0 release, the Query Service had a history of being non-involved with per-request memory usage. Now it has a clear set of options for keeping memory usage under control.