We are currently using: Enterprise Edition 7.2.0 build 5325
and are using a 3 node system.
We are hosting our nodes in EC2 instances in AWS of size r6a.large in our test environment. We are using r6a.xlarge in our production environment.
Our backend is written in node.js, and we are currently using Couchbase version 4.2.2 npm package.
We are having issues in our test environment when processing product imports, as we seem to have 2 nodes sitting at ~30% CPU utilization, and the 3rd at 100% CPU. As part of this flow, we join several collections of data, form a new document, and upsert this into a different collection. We have optimized our backend processing/queries as much as possible. The query takes ~150ms when our system is not under much load, but when a low-medium load is applied, this query begins to take upwards of 9 seconds, causing our SQS queue to back up and cause service disruption. I would understand the issue being compute power if all 3 nodes were sitting at 100%, however, that is not our case.
We are considering completely scrapping our use of Couchbase, as we have been unable to understand how to correctly manage it, and have no knowledge experts in our company.
Posting here as a last ditch effort to get some understanding of what could possibly be our issue. It doesn’t really make sense to us that it is simply a EC2 power issue, since we have 2 nodes sitting at 40% utilization.
Would switching to Capella help us deal with these types of issues, which seem unrelated to our code? Our other thought is that we will switch to Elasticsearch since we have more knowledge in our company with that.
In our production instance, it seems to manage no issues, however, we do not feel confident that this issue will not arise once we onboard more customers.
Query:
SELECT RAW {
"variant": v,
"product": p,
"productSubCategory": ps,
"productCategory": pc,
"brand": b,
"imageDetails": ARRAY_AGG(DISTINCT ide),
"attributes": ARRAY_AGG(atn)
}
FROM ${couchbasePrefix}.\`variant\` v
JOIN ${couchbasePrefix}.\`product\` p ON v.productId = p.id AND p.status != "merged"
LEFT JOIN ${couchbasePrefix}.\`imageDetails\` ide ON v.id = ide.variantId
OR (ide.variantId IS NULL AND ide.productId = p.id)
AND ide.deletedAt IS NULL
JOIN ${couchbasePrefix}.\`productSubCategory\` ps ON p.productSubCategoryId = ps.id
JOIN ${couchbasePrefix}.\`productCategory\` pc ON ps.productCategoryId = pc.id
LEFT JOIN ${couchbasePrefix}.\`brand\` b ON p.brandId = b.id
LEFT JOIN (
SELECT pa.productId AS pid, ac.name AS categoryName, ARRAY_AGG(DISTINCT a.name) AS attributeNames
FROM ${couchbasePrefix}.\`productAttribute\` pa
JOIN ${couchbasePrefix}.\`attribute\` a ON pa.attributeId = a.id
JOIN ${couchbasePrefix}.\`attributeCategory\` ac ON a.attributeCategoryId = ac.id
WHERE p.id = pa.productId AND pa.deletedAt IS NULL AND a.deletedAt IS NULL AND ac.deletedAt IS NULL
GROUP BY ac.name, pa.productId
) AS atn ON p.id = atn.pid
WHERE v.id IN [${formattedRelatedVariantIds}]
GROUP BY v, p, ps, pc, b`;
Here’s the query EXPLAIN:
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "v",
"bucket": "buildCatalog",
"index": "idx_variant_productId_id",
"index_id": "b913c9e10f255481",
"index_projection": {
"primary_key": true
},
"keyspace": "variant",
"namespace": "default",
"scope": "_default",
"spans": [
{
"exact": true,
"range": [
{
"inclusion": 0,
"index_key": "`productId`",
"low": "null"
},
{
"high": "\"b53eec6a-568d-4cc7-8b80-304a267a538c\"",
"inclusion": 3,
"index_key": "`id`",
"low": "\"b53eec6a-568d-4cc7-8b80-304a267a538c\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "v",
"bucket": "buildCatalog",
"keyspace": "variant",
"namespace": "default",
"scope": "_default"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((`v`.`id`) in [\"b53eec6a-568d-4cc7-8b80-304a267a538c\"]) and ((`v`.`productId`) is not null))"
},
{
"#operator": "NestedLoopJoin",
"alias": "p",
"on_clause": "((`v`.`productId`) = (`p`.`id`))",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "p",
"bucket": "buildCatalog",
"index": "idx_product_id",
"index_id": "e88841c5328ae232",
"index_projection": {
"primary_key": true
},
"keyspace": "product",
"namespace": "default",
"nested_loop": true,
"scope": "_default",
"spans": [
{
"exact": true,
"range": [
{
"high": "(`v`.`productId`)",
"inclusion": 3,
"index_key": "`id`",
"low": "(`v`.`productId`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "p",
"bucket": "buildCatalog",
"keyspace": "product",
"namespace": "default",
"nested_loop": true,
"scope": "_default"
}
]
}
},
{
"#operator": "NestedLoopJoin",
"alias": "ide",
"on_clause": "(((`v`.`id`) = (`ide`.`variantId`)) or ((((`ide`.`variantId`) is null) and ((`ide`.`productId`) = (`p`.`id`))) and ((`ide`.`deletedAt`) is null)))",
"outer": true,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "UnionScan",
"scans": [
{
"#operator": "IndexScan3",
"as": "ide",
"bucket": "buildCatalog",
"index": "idx_image_details_variant_id",
"index_id": "189b246323fa82e6",
"index_projection": {
"primary_key": true
},
"keyspace": "imageDetails",
"namespace": "default",
"nested_loop": true,
"scope": "_default",
"spans": [
{
"exact": true,
"range": [
{
"high": "(`v`.`id`)",
"inclusion": 3,
"index_key": "`variantId`",
"low": "(`v`.`id`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "IndexScan3",
"as": "ide",
"bucket": "buildCatalog",
"index": "idx_imageDetails_deletedAt_productId",
"index_id": "b30c6ae8b29d543a",
"index_projection": {
"primary_key": true
},
"keyspace": "imageDetails",
"namespace": "default",
"nested_loop": true,
"scope": "_default",
"spans": [
{
"exact": true,
"range": [
{
"high": "null",
"inclusion": 3,
"index_key": "`deletedAt`",
"low": "null"
},
{
"high": "(`p`.`id`)",
"inclusion": 3,
"index_key": "`productId`",
"low": "(`p`.`id`)"
}
]
}
],
"using": "gsi"
}
]
},
{
"#operator": "Fetch",
"as": "ide",
"bucket": "buildCatalog",
"keyspace": "imageDetails",
"namespace": "default",
"nested_loop": true,
"scope": "_default"
}
]
}
},
{
"#operator": "NestedLoopJoin",
"alias": "ps",
"on_clause": "((`p`.`productSubCategoryId`) = (`ps`.`id`))",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "ps",
"bucket": "buildCatalog",
"index": "idx_productSubCategory_id",
"index_id": "fef1b0a045e7f9b5",
"index_projection": {
"primary_key": true
},
"keyspace": "productSubCategory",
"namespace": "default",
"nested_loop": true,
"scope": "_default",
"spans": [
{
"exact": true,
"range": [
{
"high": "(`p`.`productSubCategoryId`)",
"inclusion": 3,
"index_key": "`id`",
"low": "(`p`.`productSubCategoryId`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "ps",
"bucket": "buildCatalog",
"keyspace": "productSubCategory",
"namespace": "default",
"nested_loop": true,
"scope": "_default"
}
]
}
},
{
"#operator": "NestedLoopJoin",
"alias": "pc",
"on_clause": "((`ps`.`productCategoryId`) = (`pc`.`id`))",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "pc",
"bucket": "buildCatalog",
"index": "idx_productCategory_id",
"index_id": "31e90de208eccecd",
"index_projection": {
"primary_key": true
},
"keyspace": "productCategory",
"namespace": "default",
"nested_loop": true,
"scope": "_default",
"spans": [
{
"exact": true,
"range": [
{
"high": "(`ps`.`productCategoryId`)",
"inclusion": 3,
"index_key": "`id`",
"low": "(`ps`.`productCategoryId`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "pc",
"bucket": "buildCatalog",
"keyspace": "productCategory",
"namespace": "default",
"nested_loop": true,
"scope": "_default"
}
]
}
},
{
"#operator": "NestedLoopJoin",
"alias": "b",
"on_clause": "((`p`.`brandId`) = (`b`.`id`))",
"outer": true,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"as": "b",
"bucket": "buildCatalog",
"index": "idx_brand_id",
"index_id": "3b4d362ad64fba12",
"index_projection": {
"primary_key": true
},
"keyspace": "brand",
"namespace": "default",
"nested_loop": true,
"scope": "_default",
"spans": [
{
"exact": true,
"range": [
{
"high": "(`p`.`brandId`)",
"inclusion": 3,
"index_key": "`id`",
"low": "(`p`.`brandId`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"as": "b",
"bucket": "buildCatalog",
"keyspace": "brand",
"namespace": "default",
"nested_loop": true,
"scope": "_default"
}
]
}
},
{
"#operator": "NestedLoopJoin",
"alias": "atn",
"on_clause": "((`p`.`id`) = (`atn`.`pid`))",
"outer": true,
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "ExpressionScan",
"alias": "atn",
"expr": "correlated (select cover ((`pa`.`productId`)) as `pid`, (`ac`.`name`) as `categoryName`, array_agg(DISTINCT (`a`.`name`)) as `attributeNames` from `default`:`buildCatalog`.`_default`.`productAttribute` as `pa` join `default`:`buildCatalog`.`_default`.`attribute` as `a` on (cover ((`pa`.`attributeId`)) = (`a`.`id`)) join `default`:`buildCatalog`.`_default`.`attributeCategory` as `ac` on ((`a`.`attributeCategoryId`) = (`ac`.`id`)) where (((((`p`.`id`) = cover ((`pa`.`productId`))) and (cover ((`pa`.`deletedAt`)) is null)) and ((`a`.`deletedAt`) is null)) and ((`ac`.`deletedAt`) is null)) group by (`ac`.`name`), cover ((`pa`.`productId`)))",
"nested_loop": true
}
]
}
},
{
"#operator": "InitialGroup",
"aggregates": [
"array_agg(DISTINCT `ide`)",
"array_agg(`atn`)"
],
"group_keys": [
"`v`",
"`p`",
"`ps`",
"`pc`",
"`b`"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"array_agg(DISTINCT `ide`)",
"array_agg(`atn`)"
],
"group_keys": [
"`v`",
"`p`",
"`ps`",
"`pc`",
"`b`"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"array_agg(DISTINCT `ide`)",
"array_agg(`atn`)"
],
"group_keys": [
"`v`",
"`p`",
"`ps`",
"`pc`",
"`b`"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"discard_original": true,
"raw": true,
"result_terms": [
{
"expr": "{\"attributes\": array_agg(`atn`), \"brand\": `b`, \"imageDetails\": array_agg(DISTINCT `ide`), \"product\": `p`, \"productCategory\": `pc`, \"productSubCategory\": `ps`, \"variant\": `v`}"
}
]
}
]
}
}
]
}
}
Thanks a bunch