Couchbase Queries

SQL++
1

Hello all,

I have a 70GIG Bucket. The bucket has the following index.
create PRIMARY INDEX primary-index ON default USING GSI;

Users can now query the bucket in the Query Workbench.

One user is running The following query:
select * from default where userID = 2693101;

The query is has been running for the past 30 minutes. The User needs this query to be faster.
I will create the appropriate index and we should be good to go.

I will have multiple case like the above in our testing environment.
I might have multiple users writing all different sorts of queries. Different queries might need all different sorts of indexes.

How can I track which queries are running right now?
How do I track queries that been running for more than 15 minutes, so that I can sit down with the developer and help him out?

Thanks,

Steeve

2

I think you should create index on userID ,for example

CREATE INDEX `idx-userID` ON `default`(userID) USING GSI;

then you can execute the following N1QL to verify if the index works:

explain select * from default where userID = 2693101;

you should see "index": "idx-userID", from result.
then you can query again.

4

Thanks for the tip. My question was more about the following:

How can I track which queries are running right now?
How do I track queries that been running for more than 15 minutes, so that I can sit down with the developer and help him out?

I gave the query example to give context.

5

may be you can check bucket Analytics about the index, such as

6

How does the Index Stats can help identify which queries are currently running on my cluster?

7

Index Stats might not show which queries are currently running on my cluster.
It can only hint which index is using.
may be @geraldss or @cihangirb can help.

8

Hi @steevebisson,
in Watson (or 4.5) we have just implemented a monitoring feature that does exactly what you are asking for.
I’m not going to quote the whole manual, but here are a couple of things that might help you:

  1. select * from system:active_requests;
    This reports the active requests currently running on the node.
    It reports anything from documents processed per each phase to execution time so far, statement, request id etc.
    You can use standard n1ql syntax to filter, limit, sort and even save the results as you please.
  2. delete from system:active_requests where RequestId = “…”
    Yup, you can kill the guilty party too.
  3. select * from system:completed_requests
    We even have a keyspace storing requests that have deemed to be too long, so even if the culprit has gone, you can still sit down with the developer.
    The threshold beyond which requests get logged by default is set to 1 second, and the maximum number of requests logged is limited to the last 4000, though these values can be tweaked on the fly via the /admin/settings endpoint.

OK, that was more than a couple, so I’ll stop here, but there’s definitely more where that came from.

One last thing: please note that these keyspaces are ->per query node<-, so it’s best if you direct your client straight to the query node, rather than ns_server.

HTH,
Marco

2 Likes
9

Awesome Thanks! upgrading my cluster right now. Very exited to check this out!

1 Like