Query on Index fields are running slow

SQL++
1

Hi There,

We are doing a POC with 17M of records with just one cluster.

Here is the configuration details.
Service Memory Quotas in megabytes per server node
Data : 16000 MB
Index : 14336 MB
Search : 512 MB
Analytics : 1024 MB
Eventing : 256 MB
The overall machine has 32 GB of RAM.

Even though we created the necessary indices, Count queries on the index field is taking seconds to return the result.
If I am running with multiple indexes, its even worst.

Here is an exmaple document ;
{
“compacted”: {
“5”: 0,
“7”: 0,
“9”: 0,
“13”: 0,
“21”: 1521181126,
“25”: “00000810b599e6da52f303e3728614879fdb897bb375063ba404e974902f746b”,
“27”: 1490562003,
“31”: “642180315231845818”,
“33”: 5132932509,
“37”: “140140”,
“41”: “XXXXXX”,
“45”: 1,
“47”: “33”,
“49”: “510”,
“51”: “9258”,
“53”: “42974444”,
“55”: 1521181126,
“63”: “US”,
“67”: “201640”,
“73”: 0,
“77”: 4,
“79”: 0,
“89”: 0,
“91”: 0,
“97”: “TIER2”,
“103”: 0,
“105”: “”,
“109”: 1288846231,
“113”: “Partial”,
“119”: “20992556”,
“121”: “20749049”,
“161”: 0,
“241”: “SUBJECT”,
“261”: 0,
“273”: “20749049”,
“275”: “20992556”,
“297”: “9258”,
“299”: “SUBJECT”,
“311”: 1,
“315”: “510”,
“317”: 1521181126,
“321”: “Partial”,
“323”: “33”,
“333”: 1490562003,
“337”: “US”,
“343”: 585,
“349”: “10610”,
“-1”: “9258”,
“-2”: “42974444”,
“-3”: “510”
},
“updated_at”: “2018-03-16 09:14:44.0”
}

Here is an example query :
select count(*) from QUEST_DEV_USER_PROFILE where (compacted.21 >= 1535439600) AND (compacted.41 = ‘XXXXXX’) AND (compacted.45 = ‘0’) AND (compacted.63 = ‘US’)

We have GSI indexes created on the all the used fields in the above query. Having said that the query is returing me rsults in 5 - 10 seconds.

Not sure what is missing here. But reading about slowness on the forums, seems like the results should come in milliseconds with just 17 M of records.

Can you please help here and provide some pointers?

2 
CREATE INDEX ix11 ON  QUEST_DEV_USER_PROFILE( compacted.`63`, compacted.`45`, compacted.`41`, compacted.`21`);
SELECT  count(1) 
FROM QUEST_DEV_USER_PROFILE 
WHERE  (compacted.`21`  >= 1535439600) 
                  AND (compacted.`41`  = "XXXXXX") 
                 AND (compacted.`45`  = "0") 
                 AND (compacted.`63`  = "US");

Index Grouping And Aggregation in 5.5 EE

Create the Right Index, Get the Right Performance.

3

Thanks. But I do have a composite index created which includes the above fields too.

CREATE INDEX ind_composite_whole ON QUEST_DEV_USER_PROFILE((compacted.35),(compacted.45),(compacted.51),(compacted.41),(compacted.37),(compacted.21),(compacted.-2),(compacted.19),(compacted.427),(compacted.287),(compacted.279),(compacted.107),(compacted.99),(compacted.63),(compacted.53))

Still the query is taking time. Also, the PLAN does not show that index “ind_composite_whole” is being used for the above query. Since the combination or the order of the fields are not always same, what else can we do here to achieve performance ?

4

To use index leading index key must present as part of query predicate.
Once index is chosen the query predicate that has consecutive leading index key values only pushed to indexer. Rest of predicates are applied post scan.

You can read this understand more https://blog.couchbase.com/wp-content/uploads/2017/10/N1QL-A-Practical-Guide-2nd-Edition.pdf

5

I have created a GSI index on 17M of records. But the count on that index is still taking 30s.

Index definition -
CREATE INDEX ind11_cn ON QUEST_DEV_USER_PROFILE((compacted.63)) USING GSI

Query :
select count(*) from QUEST_DEV_USER_PROFILE where (compacted.63 = ‘US’)

Took 30s for the result.

Why it is taking this much of time?

6

Depends on CB version? EE vs CE? What type of storage manager you are using? How big the machines?

Please post the EXPLAIN plan and use profiling described in the pdf to see where it taking time.

7

We are using CB 5.5 EE. The machine has 500 GB of SSD and 32 GB RAM.

Here is the Plan

{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“servTime”: “3.0003ms”
},
“privileges”: {
“List”: [
{
“Target”: “default:QUEST_DEV_USER_PROFILE”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“servTime”: “3.8819774s”
},
“covers”: [
“cover (((QUEST_DEV_USER_PROFILE.compacted).63))”,
“cover ((meta(QUEST_DEV_USER_PROFILE).id))”,
“cover (count())"
],
“index”: “ind11_cn”,
“index_group_aggs”: {
“aggregates”: [
{
“aggregate”: “COUNT”,
“expr”: “1”,
“id”: 2,
“keypos”: -1
}
]
},
“index_id”: “c04c79041767ddef”,
“index_projection”: {
“entry_keys”: [
2
]
},
“keyspace”: “QUEST_DEV_USER_PROFILE”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “"US"”,
“inclusion”: 3,
“low”: “"US"”
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:03.8819”,
“#time_absolute”: 3.8819774
},
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “InitialProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 8,
“kernTime”: “3.8819774s”
},
“result_terms”: [
{
“expr”: "cover (count())”
}
]
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 4
}
}
]
}
]
},
“#time_normal”: “00:00.0030”,
“#time_absolute”: 0.0030003
},
{
“#operator”: “Stream”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“kernTime”: “3.8849777s”
}
}
],
“~versions”: [
“2.0.0-N1QL”,
“5.5.1-3511-enterprise”
]
}

8

The plan looks good and count is done by indexer. You can try increasing indexer memory. cc @deepkaran.salooja

9

How do I increase the indexer memory and by how much?

10

@vgupta, can you check what is the resident ratio report in the UI stats graph for this index?

@vsr1, “servTime”: “3.8819774s” doesn’t it mean 3sec vs 30 seconds seen by the user?

11

@vgupta , “servTime”: “3.8819774s” why do u say 30s?

12

Hi Deepak, first time when i ran the query it took 30s. Running the same query twice and thrice decreased the size.
But again, why it is still taking 3s ?

Resident ratio is 21%.

Also here is the cluster settings

Service Memory Quotas

Data : 8000 MB
Index : 22000 MB
Search : 512 MB
Analytics : 1024 MB
Eventing : 256 Mb.

Also even if I am doing a simple count on index, I am getting results in 5.62 s. The plan shows that the proper index was used with cover. But this should return the counts in ms since the index already has the counts.

Query : select count(compacted.63) from QUEST_DEV_USER_PROFILE

Plan :
{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“servTime”: “4.0037ms”
},
“privileges”: {
“List”: [
{
“Target”: “default:QUEST_DEV_USER_PROFILE”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“servTime”: “5.615416s”
},
“covers”: [
“cover (((QUEST_DEV_USER_PROFILE.compacted).63))”,
“cover ((meta(QUEST_DEV_USER_PROFILE).id))”,
“cover (count(cover (((QUEST_DEV_USER_PROFILE.compacted).63))))”
],
“index”: “ind11_cn”,
“index_group_aggs”: {
“aggregates”: [
{
“aggregate”: “COUNT”,
“depends”: [
0
],
“expr”: “cover (((QUEST_DEV_USER_PROFILE.compacted).63))”,
“id”: 2,
“keypos”: 0
}
],
“depends”: [
0
]
},
“index_id”: “c04c79041767ddef”,
“index_projection”: {
“entry_keys”: [
2
]
},
“keyspace”: “QUEST_DEV_USER_PROFILE”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“inclusion”: 0,
“low”: “null”
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:05.6154”,
“#time_absolute”: 5.615416
},
{
“#operator”: “Parallel”,
“#stats”: {
“#phaseSwitches”: 1
},
“copies”: 4,
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 4
},
“~children”: [
{
“#operator”: “InitialProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 23,
“kernTime”: “22.461664s”
},
“result_terms”: [
{
“expr”: “cover (count(cover (((QUEST_DEV_USER_PROFILE.compacted).63))))”
}
]
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7
}
}
]
}
}
]
},
“#time_normal”: “00:00.0040”,
“#time_absolute”: 0.004003700000000001
},
{
“#operator”: “Stream”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“kernTime”: “5.6194197s”
}
}
],
“~versions”: [
“2.0.0-N1QL”,
“5.5.1-3511-enterprise”
]
}

Is there is something odd with the Service Memory Quotas settings?

13

With a lower resident ratio, the storage engine needs to read pages from disk and the queries will be slow. As the disk pages get cached, running the same query again is faster.

For the below query, you can try partitioned index

select count(*) from QUEST_DEV_USER_PROFILE where (compacted. 63 = ‘US’)

CREATE INDEX ind11_cn ON QUEST_DEV_USER_PROFILE(( compacted . 63 )) PARTITION BY hash((meta().id)) WITH { “num_partition”:16 }

And then set query max_parallelism to 16

curl http://hostname:8093/admin/settings -d ‘{“max-parallelism”:16}’ -u user:pword

As the resident ratio is at 20%, to get better/predictable performance you may want to spread index on 2 index service nodes(or use a machine with higher RAM). If you have multiple index nodes in your cluster, creating a partitioned index would automatically do that.

14

Thanks Deepkaran.

I actually tried with below partitioned index :

CREATE INDEX ind11_cn_part ON QUEST_DEV_USER_PROFILE((compacted.63)) PARTITION BY hash((compacted.63)) WITH { “num_partition”:8 }

And found that the query was still taking about 5s. The plan did shows that the ind11_cn_part was getting used.

Also, why the above create index is doing hash of meta().id instead of compacted.63?

Is there a way I can increase the resident ratio to 100% ?

Regarding the Memory quota, when I assign the memory to a bucket, is that only includes the data or data + indexes?

15

As all your partitions are on a single node, you’ll need to increase max-parallelism for scans on partitions to be processed concurrently. That should bring down the latency.

curl http://hostname:8093/admin/settings -d ‘{“max-parallelism”:16}’ -u user:pword

You can create partition by “compacted.63” as well as long as it has sufficient number of unique values so that your partitions don’t become skewed.

You’ll need to assign more memory to Index service memory quota to increase the resident ratio(if there is not enough memory on this node, you can add another node).

Memory assigned for a bucket only goes towards data. Memory assigned to index service is utilized for indexes.

16

Does that means that I need to keep all my data in the memory instead of Disk?

Also I have changes the Memory quota and increased the Index RAM. Now the Resident Ration is 96.1% .

But still running on count, it took some 23.47 seconds.

Query : select count(*) from QUEST_DEV_USER_PROFILE USE INDEX (ind11_cn_part using gsi) where compacted.63 = ‘US’

Plan :

{
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “Authorize”,
“#stats”: {
“#phaseSwitches”: 3,
“servTime”: “14.0029ms”
},
“privileges”: {
“List”: [
{
“Target”: “default:QUEST_DEV_USER_PROFILE”,
“Priv”: 7
}
]
},
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 1
},
“~children”: [
{
“#operator”: “IndexScan3”,
“#stats”: {
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“servTime”: “23.4558512s”
},
“covers”: [
“cover (((QUEST_DEV_USER_PROFILE.compacted).63))”,
“cover ((meta(QUEST_DEV_USER_PROFILE).id))”,
“cover (count())"
],
“index”: “ind11_cn_part”,
“index_group_aggs”: {
“aggregates”: [
{
“aggregate”: “COUNT”,
“expr”: “1”,
“id”: 2,
“keypos”: -1
}
],
“partial”: true
},
“index_id”: “304f7218575581a9”,
“index_projection”: {
“entry_keys”: [
2
]
},
“keyspace”: “QUEST_DEV_USER_PROFILE”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: ““US””,
“inclusion”: 3,
“low”: ““US””
}
]
}
],
“using”: “gsi”,
“#time_normal”: “00:23.4558”,
“#time_absolute”: 23.4558512
},
{
“#operator”: “Parallel”,
“#stats”: {
“#phaseSwitches”: 1
},
“copies”: 4,
“~child”: {
“#operator”: “InitialGroup”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 16,
“kernTime”: “1m33.810394s”
},
“aggregates”: [
"sum(cover (count()))”
],
“group_keys”: []
}
},
{
“#operator”: “IntermediateGroup”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“kernTime”: “23.4558512s”
},
“aggregates”: [
“sum(cover (count()))"
],
“group_keys”: []
},
{
“#operator”: “FinalGroup”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“kernTime”: “23.4558512s”
},
“aggregates”: [
"sum(cover (count()))”
],
“group_keys”: []
},
{
“#operator”: “Parallel”,
“#stats”: {
“#phaseSwitches”: 1
},
“copies”: 4,
“~child”: {
“#operator”: “Sequence”,
“#stats”: {
“#phaseSwitches”: 4
},
“~children”: [
{
“#operator”: “InitialProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 23,
“kernTime”: “1m33.8113928s”
},
“result_terms”: [
{
“expr”: “sum(cover (count(*)))”
}
]
},
{
“#operator”: “FinalProject”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7
}
}
]
}
}
]
},
“#time_normal”: “00:00.0140”,
“#time_absolute”: 0.0140029
},
{
“#operator”: “Stream”,
“#stats”: {
“#itemsIn”: 1,
“#itemsOut”: 1,
“#phaseSwitches”: 7,
“kernTime”: “23.4698541s”
}
}
],
“~versions”: [
“2.0.0-N1QL”,
“5.5.1-3511-enterprise”
]
}

17

Once you increase the RAM quota, storage will not proactively fetch pages from disk unless you issue the query. Are you getting these numbers after resident ratio has reached 96%? Have you applied the max-parallelism setting.

18

Yes, I ran the query after setting max-parallelism to 16.

19

So the query is running in 23 seconds with max-parallelism set to 16 and what is the latency without doing the setting? How many CPUs are there on the box?

20

The machine has 4 CPUs or max-parallelism set to 4