Optimizing Cube.js Latency with ClickHouse for High-Concurrency Use Cases

[CleverFool77]

Problem

Hi Cube.js Community, 👋

We're planning to use Cube.js with ClickHouse as our primary data warehouse, and we're very impressed with ClickHouse's low-latency and high-concurrency capabilities.

Our primary goal is to provide a fast analytical API for a real-time dashboard. Given that ClickHouse is already so performant, we want to ensure that introducing Cube.js doesn't add unnecessary latency. We're concerned about the overhead of the semantic layer.

My question is: what are the key architectural and configuration best practices for minimizing latency and ensuring Cube.js doesn't become a bottleneck when sitting on top of a highly concurrent and low-latency database like ClickHouse?

Specifically, we'd appreciate any insights on:

• How to properly tune concurrency settings (CUBEJS_CONCURRENCY) to match ClickHouse's capabilities.
• Strategies for designing pre-aggregations that maximize query performance without creating an unmanageable number of materialized views.
• When is it best to use Cube Store for pre-aggregations versus using ClickHouse itself? As clickhouse also provides pre-aggregations and cache which is much faster.
• Any other specific ClickHouse-related configurations within Cube.js that we should consider.

Thank you in advance for your help!

[igorlukanin]

Hi @CleverFool77 👋

Thanks for asking! I'd say it's quite a broad question that's hard to answer in general. I know from practice that Cube can power applications that generate dozens and hundreds of requests per second but each application has it's own specifics when it comes to the data model, multi-tenancy configuration, access control configuration, diversity of queries being run, etc.

It is also tru that introducing Cube will add some latency to queries as the benefits of the semantic layer come at a cost.

However, I'd suggest doing two things here:

• Defining what your expected workload or performance criteria is.
• Testing Cube + ClickHouse and fine-tuning based on results.

How to properly tune concurrency settings (CUBEJS_CONCURRENCY) to match ClickHouse's capabilities.

You can start with the default settings and gradually increase concurrency, monitoring ClickHouse performance and resource consumption. At some point, the performance would degrade, indicating that you've reached peak concurrency for your ClickHouse setup.

Strategies for designing pre-aggregations that maximize query performance without creating an unmanageable number of materialized views.

I'd suggest identifying groups of slow queries and gradually introducing pre-aggregations to cover them. Sometimes, for some apps, it's desirable to cover app queries with pre-aggs and run Cube in the rollup-only mode. In your case, covering just a fraction of queries might be the way to go.

When is it best to use Cube Store for pre-aggregations versus using ClickHouse itself? As clickhouse also provides pre-aggregations and cache which is much faster.

Generally, in cases when ClickHouse doesn't give you desired performance. One of such cases might be queries with joins.

[mjvankampen]

I am running into this I think. When doing 10-20 requests at the same time, there is a lot of wait time. I get flame graphs that look like this and I have a bit of hard time understand from docs/support (thanks Omar!) what is the cause for this. Especially the wait period where nothing seems to be happening. I'm unsure what Cube is waiting on, is it Clickhouse?

CUBEJS_CONCURRENCY is 50 and CUBEJS_DB_MAX_POOL 200. Looking at the clickhouse resource consumption it neglible. I also see this behaviour when hitting the cache.

[igorlukanin]

Goeiedag @mjvankampen 👋 It's really hard to say what's happening judging by these two screenshots. Cube version, deployment settings, resource consumption, etc. need to be factored in. Since you're already talking to Cube Cloud support, please continue that conversation and ask to escalate to the product team.