I have a Bucket with 1 operation per Second(insert). The Document Consists of many attributes including timestamp and imei. I have created 3 indexes one for timestamp, one for imei and one for both with desc on timestamp. Timestamp format is ‘2018-10-07Τ00:00:00’.
The main queries i perform are: count all documents per imei, count documents between selected dates and find last update per imei.
Although the queries are executed in ms, when i use the operation from java sdk I see on the Query Monitor that they take more than 30’’ seconds each.
What would you suggest to improve the response time?
I have already dropped Primary index with no result.
@kaizerman It would be great if you can share your index definition. Can you run explain and see whether it is using the index or not? Also try to execute the query from Query Workbench or cbq to see if the issue is with the java sdk.
In addition to Raju’s query around the explain/index…
As a guess, are you running a larger number of these concurrently from your Java app? Perhaps in a loop? As work builds up and various resource thresholds are reached, it will slow down.
Also, note that the query returns a set of metrics which you could have your app log when it’s higher than the threshold you expect.
Thank you fro your responses
Index definitions:
CREATE INDEX ix3 ON XBucket(imei,timestamp DESC)
CREATE INDEX ix2 ON XBucket(imei)
CREATE INDEX ix1 ON XBucket(timestamp)
sample explain plan
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan3”,
“covers”: [
“cover ((XBucket.imei))”,
“cover ((XBucket.timestamp))”,
“cover ((meta(XBucket).id))”,
“cover (max(cover ((XBucket.timestamp))))”
],
“index”: “ix3”,
“index_group_aggs”: {
“aggregates”: [
{
“aggregate”: “MAX”,
“depends”: [
1
],
“expr”: “cover ((XBucket.timestamp))”,
“id”: 3,
“keypos”: 1
}
],
“depends”: [
1
]
},
“index_id”: “1b09a0ba0c664bb3”,
“index_projection”: {
“entry_keys”: [
3
]
},
“keyspace”: “XBucket”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “$1”,
“inclusion”: 3,
“low”: “$1”
},
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “cover (max(cover ((XBucket.timestamp))))”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “select max(timestamp) from XBucket where imei= $1”
}
All the queries Performed from query Workbench have significant low response time
But when those are performed from the java sdk, I can see from the Query Monitor that the duration is very long. I can understand the there might be an issue with the concurrency but even with lower number of queries executed sequentially from only one user the response time is still an issue.
this is the servers vitals response and i would also like to notice that we have another Bucket on the server with the same configuration with more documents but less transactions per hour that works perfectly with the same configuration.
“uptime":"1483h11m17.723501749s","local.time":"2018-10-09 04:49:00.489034984 +0000 UTC","version":"2.0.0-N1QL”,“total.threads”:14835,“cores”:4,“gc.num”:215495280,“gc.pause.time”:“20.722585642s”,“gc.pause.percent”:0,“memory.usage”:161413664,“memory.total”:1432018371904,“memory.system”:3103372088,“cpu.user.percent”:0.0635,“cpu.sys.percent”:0.0079,“request.completed.count”:163754,“request.active.count”:0,“request.per.sec.1min”:1.6266,“request.per.sec.5min”:1.4665,“request.per.sec.15min”:1.3188,“request_time.mean”:“147.487731ms”,“request_time.median”:“15.723378ms”,“request_time.80percentile”:“30.268358ms”,“request_time.95percentile”:“267.146083ms”,“request_time.99percentile”:“390.691406ms”,“request.prepared.percent”:0}`
Drop these indexes and try again.
1 Like
Thanks, it worked.
It seems that the extra indexes where delaying the system.