N1QL response time inconsistent


#1

We have been exploring Couchbase N1QL as an alternative to MySQL and MongoDB and have found that the query performance is inconsistent and below par even on simple single-field queries. The outbound bucket used in the example below contains 3.5 million documents

cbq> select * from outbound where agent_id = 9001 and group_id = 7050 limit 1;
{
“requestID”: “b628e8a6-1f45-4238-ac26-47aa498457bb”,
“signature”: {
"": ""
},
“results”: [
{
“outbound”: {
“agent_id”: 9001,
“c3_id”: 1,
“called_number”: “16468107012”,
“duration”: 69,
“group_id”: 7050,
“hangup_cause”: null,
“recording_filename”: “3218_9001_16468107012.mp3”,
“session_id”: “eJDzxQbXR”,
“status”: “Answered”,
“time”: 1.399950081e+09,
“uuid”: “7.154027998590868e15”
}
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “130.241522ms”,
“executionTime”: “130.092024ms”,
“resultCount”: 1,
“resultSize”: 501
}
}

The fields queried upon: agent_id and group_id are both indexed as can be seen by the output of explain.

cbq> explain select * from outbound where agent_id = 9001 and group_id = 7050 }
{
“requestID”: “3b690b35-9ab5-4f9f-a156-6c07ca7c3a0b”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “group_id”,
“keyspace”: “outbound”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
“7050.000001”
],
“Inclusion”: 1,
“Low”: [
“7050”
]
},
“Seek”: null
}
],
“using”: “view”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “outbound”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “(((outbound.agent_id) = 9001) and ((outbound.group_id) = 7050))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “1”
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “35.060232ms”,
“executionTime”: “34.88145ms”,
“resultCount”: 1,
“resultSize”: 2715
}
}

Limiting the query to a single field as in “SELECT * FROM outbound WHERE agent_id = 9001 LIMIT 1;” takes around the same amount of time. Is there any further optimizations that can be applied to make the response better and consistent??


#2

@jayesh1017
Please email me:
(I have sent you my email address as a private message to you on this forum, so pls look at your private message inbox, and email me)

I will help you get a refreshed build which can help alleviate some of your issues. Pls. contact me to proceed.


#3

Hi @jayesh1017,

I have requested our team to reach out to you and get you a faster version. fyi, we will be adding an optimization to apply the LIMIT early in the execution pipeline when possible.

Thanks,
Gerald


#4

Thank you folks. I’m in touch with ilam to take this further.


#5

Yep - responded to email and will take it from there.


#6

Are there any updates on this that could be useful to others that are OK to share?

Thanks.


#7

@Eugaia have you tried the beta release? What perf issues are you having?


#8

I’m not having any performance issues in particular, it’s just that the info here wasn’t disclosed, and I’m reading through loads of posts to update my knowledge.

Also, I might have been wrong, but in the explain response, it didn’t appear that indexes were use for agent_id, only for group_id. If I’m wrong on this, can someone explain which part of the explain result indicates indexing on the agent_id?

Thanks.


#9

Hi @Eugaia, both indexes would be used in the beta release. Furthermore, if there is a compound index on both group_id and agent_id, that index will be preferred, and will provide the best performance. Please continue to ask and share. These forums are here for that purpose.

Thanks,
Gerald


#10

Can you please explain what part of the ‘explain’ statement shows that the index on agent_id is being used.

Thanks.


#11

That EXPLAIN statement was not generated using the beta release.


#12

Ah, OK. Thanks for the explanation.