N1QL queries very slow

mickael.barroux · August 19, 2016, 8:33am

Hello,

We use Couchbase Server Version: 4.1.1-5914 Enterprise Edition (build-5914)
Cluster with 4 nodes in production.
We use last version of Couchbase java client to make N1QL queries on this cluster : https://github.com/couchbase/couchbase-java-client

Part of the queries create timeouts on Couchbase side. They are visible in the administration panel, in analytics section (query_requests_5000ms). Only some of them are slow, when you run it multiple times, sometimes it’s very fast, sometimes it timeouts…

We use simple N1QL query, example : SELECT META(default).id AS couchbaseId, * FROM default WHERE type=‘alerte’ AND internauteId='74282378’
We have 18Gb RAM, only 136Mb in use, 225Mb of data… So very limited bunch of data…

I created primary index on the default bucket, and secondary index matching our queries, example : CREATE INDEX idx_alerte_internauteId_2 ON default(internauteId,type) USING GSI

If you can help me to figure out what’s going on…
Thank you so much !

mickael.barroux · August 19, 2016, 9:26am

For info, the call to the REST service http://:8091/pools/default gives the following output :

matthew.groves · August 22, 2016, 2:00pm

Could you try to EXPLAIN one of the slow queries, and post the output of that EXPLAIN command here?

mickael.barroux · August 22, 2016, 2:08pm

Hi, thank you for your reply. You’ll find the explain command result below :

[
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “idx_alerte_internauteId_1”,
“keyspace”: “default”,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“74266020"”,
"“alerte”"
],
“Inclusion”: 3,
“Low”: [
"“74266020"”,
"“alerte”"
]
}
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “default”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “(((default.type) = “alerte”) and ((default.internauteId) = “74266020”))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“as”: “couchbaseId”,
“expr”: “(meta(default).id)”
},
{
“expr”: “self”,
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
}
]

Thx

keshav_m · August 22, 2016, 5:26pm

The index definition is correct. You can get slightly improvement by doing this:
CREATE INDEX idx_alerte_internauteId_2 ON default(internauteId) where type = ‘alerte’ USING GSI; This will only handle the type: alerte

How many document do you expect in the resultset for this query and what’s the average size of the document?
Index scan has a default limit ot 2 minutes. If you have very large number of documents with the same internauteId, the index scan could time out…

mickael.barroux · August 23, 2016, 8:19am

Hi,

Thank you for your optimization for the index query I will use it.
We have only 22.921 documents by now, even queries returning no document are timing out…

Documents are quite small and there are not so many docs per internauteId…
An example of JSON document stored in CB :

{
“dateCreation”: “2016-08-02T09:55:15.274Z”,
“provenance”: “test”,
“titre”: “mon alerte”,
“dateModification”: “2016-08-02T09:55:15.274Z”,
“externalId”: “alerte::100”,
“internauteId”: “123456789”,
“criteres”: {
“salaireMin”: 33000,
“localisations”: [
{
“departements”: [],
“region”: {
“code”: 16,
“label”: “Midi-Pyrénées”
}
}
],
“salaireMax”: 40000,
“motsCles”: “”,
“contrats”: [
{
“code”: 2,
“label”: “CDD / Intérim / VIE”
},
{
“code”: 3,
“label”: “Intérim”
},
{
“code”: 4,
“label”: “Contractuel”
},
{
“code”: 13,
“label”: “Alternance”
},
{
“code”: 1,
“label”: “CDI”
}
],
“fonctions”: [
{
“code”: 30500,
“label”: “Marketing”
},
{
“code”: 30103,
“label”: " Administration des ventes"
}
],
“secteurs”: [
{
“code”: 60000,
“label”: “Industries”
}
]
},
“type”: “alerte”,
“email”: "toto@toto.fr"
}

Thank you for your help !

fx.peretmere1 · August 25, 2016, 4:05pm

If you run the same queries without the Java part (with cbq for example), does it behave the same?

As a side-note: Couchbase 4.5 brought a lot of enhancements to (among other things) N1QL. Any chance you could try this against a 4.5 cluster?