Couchbase’s cbq command-line tool can log queries that exceed a configurable execution time, acting as a built-in profiler for your N1QL statements.
Here’s a cbq session showing a slow query being logged:
# Connect to the cluster
cbq -u Administrator -p password -s http://localhost:8091
# Set the slow query log threshold to 100 milliseconds
SET.`log.slowQuery.threshold` = 100ms;
# Execute a query that will likely exceed the threshold
SELECT * FROM my_bucket WHERE name = "Alice";
# Wait for a bit, then check the logs (or observe cbq output if it's verbose)
# If the query is slow enough, it will appear in the cbq output or a log file.
# To view the logs directly if not displayed:
# (The exact location depends on your Couchbase installation and configuration)
# tail -f /opt/couchbase/var/lib/couchbase/logs/query.log
This cbq command-line interface is more than just a query executor; it’s a direct conduit to the Couchbase query engine’s internal state and configuration. When you execute SET. commands, you’re not just changing a setting for that session, you’re directly interacting with the query service’s configuration parameters. The log.slowQuery.threshold parameter specifically tells the query service’s internal timer, "If any N1QL statement takes longer than this amount of time to complete, please record it."
The system solves the problem of identifying performance bottlenecks in your N1QL queries without needing external monitoring tools or complex instrumentation. It surfaces queries that are actually taking a long time for real users or real requests, not just queries that look complex. You can then take these logged queries and optimize them, either by adding indexes, rewriting the query logic, or even re-evaluating your data model.
Let’s dive into the internal mechanics. When log.slowQuery.threshold is set, the query service starts a timer for each incoming N1QL statement. This timer begins when the query execution starts and stops when it finishes. If the elapsed time is greater than the value set in log.slowQuery.threshold, the query service logs a detailed record of that query. This record includes the full N1QL statement, the execution time, and often details about the plan the query executed. This is crucial because it gives you not just what was slow, but also how Couchbase attempted to execute it.
The primary lever you control is the log.slowQuery.threshold value itself. You can set it to a very low number (e.g., 10ms) to catch even minor delays, or a higher number (e.g., 5000ms or 5s) if you only care about truly egregious performance issues. The unit can be milliseconds (ms) or seconds (s).
Another important, though less frequently adjusted, parameter is log.slowQuery.enabled. By default, it’s often true, but you can explicitly disable it with SET.log.slowQuery.enabled = false;`. While you’re unlikely to disable it if you’re trying to find slow queries, it’s good to know it exists.
When a query is logged, Couchbase provides a wealth of information. It’s not just the SQL text. You’ll see the elapsed time, and importantly, the executionPlan. Understanding this plan is key. It tells you if Couchbase used an index, if it performed a full bucket scan, or if it had to resort to other, less efficient methods. For example, if you see a plan that indicates a PrimaryScan on a large dataset for a query that filters on a specific field, that’s a strong signal that a GSI (Global Secondary Index) is missing or not being used effectively.
The cbq tool itself also has settings that can affect what you see. For instance, SET.output.json = true;` can be useful if you want to programmatically parse the slow query logs. However, for interactive debugging, the default text output is usually sufficient.
Here’s a subtle point: the log.slowQuery.threshold applies to the entire query execution, from the moment the query service receives the request until it sends back the results. This includes the time spent planning the query, fetching data, and serializing the results. Sometimes, a query might have a fast execution plan but take a long time to serialize a massive result set. While this is still a "slow query," the optimization strategy might differ (e.g., limiting results, pagination) compared to optimizing a slow execution plan.
The next logical step after identifying slow queries is to optimize them, which often involves creating or refining Global Secondary Indexes (GSIs).