In part 2, we figured out a benchmark for the low-performant API, now we start to deep dive to see if we can find the root cause and remediation.
From API to N1QL
Now we need to diagnose the API; before finger-pointing to the Database, we need to exclude any extra heavy operation/processing inside the API implementation. By checking the code, we have made sure there is not any extra operation apart from the Couchbase query, but there are two queries within a single API call because it’s a search API with pagination, the response body contains both data and meta { data: { ... }, meta: { total: xx}}.
Essentially for data and meta the API triggers two N1QL queries,"SELECT *,TOSTRING(META().id) AS id from `member` WHERE _type='User' AND (( `oidc`[0].`sub` = 'b213acd3-a2d6-4c37-9a16-aab293833ee7') OR ( `email` = 'xxx@gmail.com'))"
"SELECT COUNT(META().id) as total from `member` WHERE _type='User' AND (( `oidc`[0].`sub` = 'b213acd3-a2d6-4c37-9a16-aab293833ee7') OR ( `email` = 'xxx@gmail.com'))"
It is not ideal, but considering there is only one record returned from the 1st N1QL and the Where clauses are the same, there should be only a little bit of overhead for the aggregation(COUNT) from the 2nd N1QL. So the performance bottleneck points to the Where clause.
Index, index, index!
Creating the right index — with the right keys, in the right order, and using the right expressions — is critical to query performance in any database system.
We run the N1QL in the Couchbase web console, with the explain statement we can check if the query hit the correct index or not.
{ |
TL;DR, recapping as below,
expA AND ( expB OR expC)is transformed to(expA AND expB) OR (expA AND expC)`which follows the Distributive property.A few indexes are hit:
user_type_idx,federationId_idxanduser_emailso the query didn’t run with a very very slow Primary index scanning.If you are sharp-eyed, you will find
user_type_idxis scanned twice, the whole query plan is generated as follows,
UnionScan ( |
- More importantly, IntersectScan should be avoided, see: Developer Portal | Couchbase
Deep dive on index definition
We checked the current index definition,
CREATE INDEX `user_email` ON `member`(`email`) |
They are indexes on specific fields, but less than efficiency, generally, a single composite index which meets the criteria will be more performant than intersections on multiple singular indexes. i.e. an index contains both _type and email` can provide faster single scan than two scans separately on _type and email.
So we changed the indexes as follows,CREATE INDEX `federationId_idx` ON `member`(`_type`,((`oidc`[0]).`sub`)) WHERE (`_type` = "User") WITH { "defer_build":true }
CREATE INDEX `user_email` ON `member`(`_type`,`email`) WHERE (`_type` = "User") WITH { "defer_build":true }
The defer_build is a trivial, now we have a wider index(including _type )and the data set of index has also been narrowed down with only User documents by a Where clause.
Let’s see the difference with optimized index.
| Single query with email under 20it/s | CPU | Latency(avg) | Latency(p90) |
|---|---|---|---|
| Unoptimized index | 20% | 62.33ms | 95.66ms |
| Optimized index | 10% | 37.62ms | 53.68ms |
It will be more obviously with 100 it/s for the singular query.
Apart from the metric, the explain statement also explains the IntersectScan is gone.{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IndexScan2",
"index": "federationId_idx",
"index_id": "884e8f1fdc96c64",
"index_projection": {
"primary_key": true
},
"keyspace": "member",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"User\"",
"inclusion": 3,
"low": "\"User\""
},
{
"high": "\"b213acd3-a2d6-4c37-9a16-aab293833ee7\"",
"inclusion": 3,
"low": "\"b213acd3-a2d6-4c37-9a16-aab293833ee7\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan2",
"index": "user_email",
"index_id": "3b4b2219e70c4b63",
"index_projection": {
"primary_key": true
},
"keyspace": "member",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"User\"",
"inclusion": 3,
"low": "\"User\""
},
{
"high": "\"xxx@gmail.com\"",
"inclusion": 3,
"low": "\"xxx@gmail.com\""
}
]
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"keyspace": "member",
"namespace": "default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`member`.`_type`) = \"User\") and (((((`member`.`oidc`)[0]).`sub`) = \"b213acd3-a2d6-4c37-9a16-aab293833ee7\") or ((`member`.`email`) = \"xxx@gmail.com\")))"
},
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "self",
"star": true
},
{
"as": "id",
"expr": "to_string((meta(`member`).`id`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT *,TOSTRING(META().id) AS id from `member` WHERE _type='User' AND (( `oidc`[0].`sub` = 'b213acd3-a2d6-4c37-9a16-aab293833ee7') OR ( `email` = 'xxx@gmail.com'))"
}
Comparison
Now we go back to the original query (the one with OR clause), we start from 20 it/s and increase the rate up to 100 it/s.
What’s the next?
We already had a massive improvement in terms of API performance by eliminating the IntersectScan, but the UnionScan is still there, I don’t have a great idea about how to remove it, but depending on the Expectation of the API RPS and the use case, if the OR pattern is really needed in the where query, I may consider to trigger two N1QLs from the application instead of using UnionScan as a workaround.