Using N1QL, Timeout

.NET SDK
,
1

Hey guys,

My Environment:

  • Version 4.0.0-2213 DEV Edition (build-2213)
  • The Test and CouchBase is in the same server.

I have a doubt, How work the timeout in .Net SDK.

I ran a query that I make different form:

1 -

string[] iwps = .............;

SELECT q.period , q.category, SUM(q.sumActual) as Actual,SUM(q.sumEarn) as Earn ,SUM(q.sumForecast) as Forecast FROM default as q USE INDEX (MasterQrsIndexCustom USING GSI) WHERE q.type = 'IwpCumulative' AND q.parent IN [" + string.Join(",", iwps) + "] GROUP BY q.period, q.category";

iwps = 437 iwp. ej: "CostCode::1000::Budget::Cumulative::1"

The Result with 100 documents = Fine!.
The Result with 1.000.000 documents = Timeout.
The Result with 1.000.000 documents using CBQ = Fine!.

I thought maybe its the query or my focus is wrong.

2 -

string[] iwps = …;

I make other document with this values ( iwps )

I create a document to lookup a make a join.

The Result with 100 documents = Fine!.
The Result with 1.000.000 documents = Timeout.
The Result with 1.000.000 documents using CBQ = Fine!.

3 -

string[] iwps = …;

SELECT * FROM bucket USE KEYS [ values that inside iwps ]

The Result with 100 documents = Fine!.
The Result with 1.000.000 documents = Timeout.
The Result with 1.000.000 documents using CBQ = Fine!.

Well, I need to know why happend timeout, how many documents I could work without happend timeout.

I fill bucket with 1.000.000 documents.

SELECT * FROM default

The Result with 1.000.000 documents = return with error but “The function evaluation was disabled because of an out of memory exception.” .
The Result with 1.000.000 documents using CBQ = Fine!.

"status": "success",
"metrics": {
    "elapsedTime": "13m54.187893s",
    "executionTime": "13m54.187893s",
    "resultCount": 1000000,
    "resultSize": 390798472
}

But the result is very different Because esta operation was 3 minutes and the operation before was 6 sec.

Therefore, why throw me at 6 sec timeout and another query work during 3 min?

I using this configuration.

_config = new ClientConfiguration
            {
                Servers = new List<Uri>
                  {
                       new Uri("http://127.0.0.1:8091/pools")
                  },
                UseSsl = false,
                DefaultOperationLifespan = int.MaxValue,
                BucketConfigs = new Dictionary<string, BucketConfiguration>
                                  {
                                    {"default", new BucketConfiguration
                                    {
                                      BucketName = "default",
                                      UseSsl = false,
                                      Password = "",
                                      DefaultOperationLifespan = 2000,
                                      PoolConfiguration = new PoolConfiguration
                                      {
                                        MaxSize = 10,
                                        MinSize = 5,
                                        SendTimeout = int.MaxValue, // this value wa
                                        ConnectTimeout=int.MaxValue
                                      }
                                    }}}
            };
            _cluster = new Cluster(_config);

I was doing all cases with Method Get with the keys and after I used linq and did it fine!

Regards.

2

@Antx2207 -

The timeout can be configured in the .NET SDK on a per request basis:

var queryRequest = new QueryRequest()
     .Statement("SELECT * FROM `beer-sample` LIMIT $1")
     .AddPositionalParameter(10)
     .Timeout(new TimeSpan(0, 0, 0, 0, 5));

 var result = await bucket.QueryAsync<dynamic>(queryRequest);

Try adjusting the timeout and see what happens.

-Jeff

3

Yes, Its true… I tried that too, but dont work.

Its very confusing

4

@Antx2207 -

Could you post the raw HTTP request? Just the querystring values (assuming you are doing a GET)…I would like to see what the client is sending the server.

-Jeff

5

@jmorris,

Could you please tell me how I could get the http request?

I try to use Fiddler but no working. I try to find some way to able but I cant.

6

@Antx2207

This should work:

              var uri = queryRequest.GetRequestUri();

-Jeff

7

@jmorris,

The uri:

.Net:

{http://127.0.0.1:8093/query?statement=SELECT q.* FROM default as q WHERE q.type = 'IwpCumulative' AND q.period = 5  AND q.sumActual < 400 AND q.category <> 'Budget'&timeout=1200000ms}

The result was successfully done but there was an exception with it.

Message:
The operation has timed out
StackTrace:
   at System.Net.HttpWebRequest.GetResponse()
   at Couchbase.N1QL.QueryClient.Get[T](Uri requestUri)

CBQ:

The result CBQ.
{
“category”: “Qty”,
“parent”: “Iwp::98”,
“period”: 5,
“sumActual”: 295,
“sumEarn”: 447,
“sumForecast”: 574,
“sumPlanned”: 550,
“sumValue”: 0,
“type”: “IwpCumulative”
},
.
.
.
.
.
.
{
“category”: “Cost”,
“parent”: “Iwp::9”,
“period”: 5,
“sumActual”: 384,
“sumEarn”: 594,
“sumForecast”: 477,
“sumPlanned”: 509,
“sumValue”: 0,
“type”: “IwpCumulative”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “25.5782182s”,
“executionTime”: “25.5772198s”,
“resultCount”: 215,
“resultSize”: 61368
}

8

@Antx2207 -

I see the problem; the server timeout is sent, but the client timeout (.NET stack) is using the default timeout specified by .NET. I created a Jira ticket for a fix: Loading...

If you would like you can add yourself as a watcher and be alerted when the status changes.

-Jeff

9

@jmorris,

Thanks a lot!!

10

Hi @jmorris,

I saw that you solved the problem.

And the big question and I know that is very hurry ask this, but when is release from SDK 2.1.4.

http://issues.couchbase.com/browse/NCBC/fixforversion/12811#selectedTab=com.atlassian.jira.plugin.system.project%3Aversion-summary-panel

Because, this query throw timeout but at cbq does not.

select 
t.category,
t.period,
sum(t.sumActual) 
from 
default as q 
inner join default as p on keys q.parent 
inner join default as t on keys p.iwpCumulatives 
where
q.type = 'IwpCumulative' 
and q.period = 50
and q.sumActual > 0 
and q.category = 'Porcentaje' 
group by t.category,t.period
order by t.period,t.category;

Regards.

11

@Antx2207 -

Version 2.1.4 of the Couchbase .NET SDK is scheduled for Tuesday August 4th assuming all goes well with testing.

-Jeff

12

@jmorris,

Thanks a lot, I’ll be awaiting.

Regards.

13

Hi @jmorris,

Do you have any news about release?

Regards

14

@Antx2207 -

It’s going through QE; as soon as it passes I’ll push the packages to NuGet.

-Jeff

15

Hi Jeff,

Just wondering if NuGet package has been released already?
I’m currently testing and I’m facing same Timeout Issue when having > 1 mil documents with 2.1.4.0 version.

Thanks in advance for your kind support.

-Victor

16

Hey @victorvilla,

I don’t use this version yet, but I had another problem with timeout, but I did read the source and I saw that another error about timeout was this configuration:

I did set MaxAcquireIterationCount = int.MaxValue

BucketConfigs = new Dictionary<string, BucketConfiguration>
                                  {
                                    {"default", new BucketConfiguration
                                    {
                                      BucketName = "default",
                                      UseSsl = false,
                                      Password = "",
                                      DefaultOperationLifespan = 2000,
                                      PoolConfiguration = new PoolConfiguration
                                      {
                                        MaxSize = 10,
                                        MinSize = 5,
                                        SendTimeout = int.MaxValue,
                                        ConnectTimeout=int.MaxValue,
                                        MaxAcquireIterationCount = int.MaxValue
                                      }
                                    }}}
            };

Regards.

17

@victorvilla -

2.1.4 is available from NuGet: NuGet Gallery | CouchbaseNetClient 2.1.4

-Jeff

18

Thanks for the hint @Antx2207 !!
I have actually changed it too but I still see same timeout exception

((System.Net.WebException)(((Couchbase.N1QL.QueryResult<object>)(result)).Exception))
"The operation has timed out"
"at System.Net.HttpWebRequest.GetResponse()\r\n   at Couchbase.N1QL.QueryClient.Get[T](Uri requestUri)"

It started happening as you indicated when going above 1’000,000 documents even with new version 2.1.4 which I have already updated and if I use any conditions in a WHERE statement.

The way I’m creating my request is:

var  TimeSpan timeout = new TimeSpan(0, 30, 0);
var queryRequest = new QueryRequest()
                                   .Statement("SELECT * FROM `TestBucket` WHERE device_id=$1") 
                                   .AddPositionalParameter("2362098967") 
                                   .Timeout(timeout);

Do you have any other suggestions?

Thanks in advance for your help.

-Victor

19

@victorvilla -

Use the ClientConfiguration.QueryRequestTimeout since the timeout is on the client-side; the QueryRequest.Timeout(…) is the server timeout so that the query doesn’t run indefinitely.

In a later release (2.2.0) the two values will be synced, but not in 2.1.4.

MaxAcquireIterationCount is for tuning the time the SDK will wait for a connection while doing a Key/Value request; it does not have any affect on the N1QL or View queries.

-Jeff

20

Thanks @jmorris !!
It looks like we’re getting there… However I now get the following error :

{Couchbase.N1QL.Error}	Couchbase.N1QL.Error
Code: 5000
"GSI scan error - cause: Index scan timed out "

Any ideas?

Also, do you know if there are any options for performance tunning when using N1QL?
It looks like it is just too much effort to lookup trough 1’000,000 documents, but in this POC we will be aiming to actually search trough at least 1’000,000,000 docs to measure performance.