N1ql query timeouts

Java SDK
1

Query-service returns result with timeout status for parameterized query with 100 parameters.
Additional information:

  1. Cluster information: 4 nodes:
    first node: index-service
    second node: data-service
    third node: data-service
    fourth node: query-node
    All nodes are in one local network (each node have 100Mb network interface)
  2. Primary and GSI indexes created for object’s field (test bucket have 7 secondary indexes). Also bucket have 17 views in one design document for comparing performance with view-based queries.
  3. How test works: I’m adding 2000 objects and after that trying execute query with 100 arguments, sample query:
    SELECT * FROM bucket_name WHERE type=‘ObjectType1’ AND (fieldId=? OR fieldId=? OR fieldId=? …OR fieldId=?);
  1. N1ql query:
    N1qlParams queryParams = N1qlParams.build()
    .consistency(ScanConsistency.REQUEST_PLUS)
    .scanWait(20, TimeUnit.SECONDS)
    .serverSideTimeout(25, TimeUnit.SECONDS);
    N1qlQuery.parameterized(rawQuery, args, queryParams),

where:
rawQuery - query string from #3
args - JsonArray with arguments.
5) couchbase server: 4.1 (on CentOS 6.3), couchbase java sdk: 2.2.4

This query executes several times with different arguments.
Sometimes this query works proper and returns result but most of time I have timeouts.
How I can eliminate timeouts in simple n1ql-queries or how I must modify my queries.

Thanks,
Anton

1 Like
2

Alright, lets start with the basics :slightly_smiling:

  • Can you please share the EXPLAIN result of the query you are performing?
  • When does it start to timeout regularly (how many fields do you need to OR together)?
  • So is the observation the more you OR together the longer the query takes to return?
  • How many results are returned when you increase the OR chaining?
  • Btw while ? is I think working the recommended style for positional arguments is $1, $2 and so forth.
  • Just as an experiment: does the performance change if you don’t use positional params but rather just string together the full query and pass it in?
3
  1. explain plan:
    { "requestID": "98f5beda-256a-435f-8dcb-fb9c6ca47793", "signature": "json", "results": [ { "#operator": "Sequence", "~children": [ { "#operator": "IndexScan", "index": "idxChannelId", "keyspace": "testBucket", "namespace": "default", "spans": [ { "Range": { "Inclusion": 1, "Low": [ "null" ] } } ], "using": "gsi" }, { "#operator": "Parallel", "~child": { "#operator": "Sequence", "~children": [ { "#operator": "Fetch", "keyspace": "testBucket", "namespace": "default" }, { "#operator": "Filter", "condition": "(((testBucket.meta_type) = \"ChannelDao_\") and (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((testBucket.channelID) = 2820) or ((testBucket.channelID) = 3591)) or ((testBucket.channelID) = 2824)) or ((testBucket.channelID) = 8202)) or ((testBucket.channelID) = 8206)) or ((testBucket.channelID) = 1038)) or ((testBucket.channelID) = 8723)) or ((testBucket.channelID) = 8984)) or ((testBucket.channelID) = 8730)) or ((testBucket.channelID) = 8734)) or ((testBucket.channelID) = 8738)) or ((testBucket.channelID) = 1059)) or ((testBucket.channelID) = 8229)) or ((testBucket.channelID) = 3877)) or ((testBucket.channelID) = 1830)) or ((testBucket.channelID) = 1072)) or ((testBucket.channelID) = 3382)) or ((testBucket.channelID) = 8248)) or ((testBucket.channelID) = 1851)) or ((testBucket.channelID) = 6203)) or ((testBucket.channelID) = 6207)) or ((testBucket.channelID) = 1855)) or ((testBucket.channelID) = 2368)) or ((testBucket.channelID) = 1093)) or ((testBucket.channelID) = 8519)) or ((testBucket.channelID) = 1097)) or ((testBucket.channelID) = 8521)) or ((testBucket.channelID) = 3658)) or ((testBucket.channelID) = 2381)) or ((testBucket.channelID) = 8788)) or ((testBucket.channelID) = 4182)) or ((testBucket.channelID) = 2392)) or ((testBucket.channelID) = 4186)) or ((testBucket.channelID) = 2396)) or ((testBucket.channelID) = 3679)) or ((testBucket.channelID) = 1379)) or ((testBucket.channelID) = 356)) or ((testBucket.channelID) = 8300)) or ((testBucket.channelID) = 3692)) or ((testBucket.channelID) = 1390)) or ((testBucket.channelID) = 8304)) or ((testBucket.channelID) = 1394)) or ((testBucket.channelID) = 373)) or ((testBucket.channelID) = 6007)) or ((testBucket.channelID) = 1659)) or ((testBucket.channelID) = 2943)) or ((testBucket.channelID) = 2947)) or ((testBucket.channelID) = 2181)) or ((testBucket.channelID) = 3719)) or ((testBucket.channelID) = 1160)) or ((testBucket.channelID) = 137)) or ((testBucket.channelID) = 1678)) or ((testBucket.channelID) = 1168)) or ((testBucket.channelID) = 2962)) or ((testBucket.channelID) = 659)) or ((testBucket.channelID) = 2966)) or ((testBucket.channelID) = 156)) or ((testBucket.channelID) = 1181)) or ((testBucket.channelID) = 1189)) or ((testBucket.channelID) = 680)) or ((testBucket.channelID) = 684)) or ((testBucket.channelID) = 2989)) or ((testBucket.channelID) = 688)) or ((testBucket.channelID) = 2996)) or ((testBucket.channelID) = 693)) or ((testBucket.channelID) = 1718)) or ((testBucket.channelID) = 4280)) or ((testBucket.channelID) = 3258)) or ((testBucket.channelID) = 1985)) or ((testBucket.channelID) = 3778)) or ((testBucket.channelID) = 194)) or ((testBucket.channelID) = 451)) or ((testBucket.channelID) = 1989)) or ((testBucket.channelID) = 1990)) or ((testBucket.channelID) = 3271)) or ((testBucket.channelID) = 1994)) or ((testBucket.channelID) = 3275)) or ((testBucket.channelID) = 2512)) or ((testBucket.channelID) = 6103)) or ((testBucket.channelID) = 8153)) or ((testBucket.channelID) = 2009)) or ((testBucket.channelID) = 217)) or ((testBucket.channelID) = 6107)) or ((testBucket.channelID) = 8157)) or ((testBucket.channelID) = 3039)) or ((testBucket.channelID) = 1251)) or ((testBucket.channelID) = 1255)) or ((testBucket.channelID) = 3816)) or ((testBucket.channelID) = 2281)) or ((testBucket.channelID) = 6122)) or ((testBucket.channelID) = 1522)) or ((testBucket.channelID) = 242)) or ((testBucket.channelID) = 1779)) or ((testBucket.channelID) = 4084)) or ((testBucket.channelID) = 2805)) or ((testBucket.channelID) = 246)) or ((testBucket.channelID) = 3831)) or ((testBucket.channelID) = 251)) or ((testBucket.channelID) = 3835)) or ((testBucket.channelID) = 255)))" }, { "#operator": "InitialProject", "result_terms": [ { "expr": "(meta(testBucket).id)" } ] }, { "#operator": "FinalProject" } ] } } ] } ], "status": "success", "metrics": { "elapsedTime": "40.556948ms", "executionTime": "40.456427ms", "resultCount": 1..
  2. I get timeouts right after start execution (in my test i’m always use 100 ‘OR’), but after restarting index-service node for some time I don’t have timeouts.
  3. yes
  4. I’m using only existing data for OR, so if I add more OR then result set will have more data.
  5. Oh, sorry, I have Utility class which converts ‘?’ to ‘$1’, ‘$2’… and final query will have only ‘$N’ for parameters.
  6. In case of query without parameters it performs better, but not fast.

Interesting thing: after executing query without parameters (WHERE type=‘OBjectType1’ AND (channelId=1 OR channelId=2… OR channelId=100)) my main query with parameters is executing with no timeouts, but how query with constants can affect queries with parameters?

4

I’m not aware of any general limitations to the number of positional arguments and their impact on the actual query time here, but maybe @geraldss or @keshav_m have some pointers

5

Also, I observed ‘Index scan timed out - cause: Index scan timed out’ after executing my query

6

@antonIntroPRO that’s a good hint, so it looks like scanning the index takes up the bulk of the time, causing the timeout on the application side. How is the index “idxChannelId” defined?

7

CREATE INDEX idxChannelId ON `testBucket`(`channelID`) WHERE meta_type=‘ChannelDao_’ USING GSI;

8

Hi guys, do you have any suggestion ?

9

Hi - does the bucket have only 2000 objects? If not, what is the total size of the bucket?

It will help me narrow down possibilities if it’s possible to change REQUEST_PLUS to NOT_BOUNDED and see if you encounter any timeouts.

Thanks!

10

Hi Siri!
Bucket have only these 2000 objects. I have tried change REQUEST_PLUS to NOT_BOUNDED, but it haven’t help and I still have timeout excetions.

Thanks!

11

Hi - there’s something clearly very wrong. There’s no reason for a scan to block with that few documents and without any consistency requirement! Can you please send me the log files? You could upload it this way:

curl --upload-file <FILE NAME> https://s3.amazonaws.com/customers.couchbase.com/<YOUR COMPANY NAME>/

Thanks!
Siri