This document will show what makes AtScale perform under a high concurrency workload and can impact query latency.

In this document, I will show a different performance based on cluster size and hardware size for each cluster to determine what makes AtScale perform.

Load Testing Scenario

The load testing is running against postgres 14, on r6i.2xlarge, and upsize to c class with AtScale 2022.1 public release.

The number of loops is 3X on each batch Using 6 SQL queries + 3 MDX queries.

Number of concurrencies in batches of 5, 20, 50 and 100

Background

With this exercise, we are trying to eliminate the latency in query planning.

If query planning is already > 1 second long. You need help to improve the performance of your query. The only way to speed up performance is by removing the latency.

If you have two latency in your query, in this example below

You have to eliminate the 1-minute outbound latency + query planning latency to get to

All of these improvements are made by changing the HW size.

The baseline test uses a single host of 8 CPUs & 64G of RAM on AtScale 2022.1 release.

Split node

This configuration uses two hosts: one database & one engine.

Comparing the runtime, there is not much difference between the two, but what I can say is. The improvement I notice is from the UI. When you navigate in AtScale while the workload runs, there is no impact vs. a single-node system. The latency when you click on Queries, for example, with a

a single-node system will take up to 5 - 10 seconds before it shows the list of running queries vs. split DB & Engine host.

Cluster

As you can see, the gain in MDX is 2x performance between a single node AtScale vs clusters.

What if we remove the limiter on the backend? The database can handle 1000 concurrency; how does AtScale fare up?

Have some gain in the SQL performance because now the database can serve the query much faster. So the bottleneck is now at AtScale.

After removing the bottleneck from the database, let's bump up the AtScale cluster specs to 32 vCPU with 64G of RAM.

Now the delta between 2 cluster sizes has a big difference between 8 cores CPU vs. 32 cores of CPU.

Cluster + Query Engine

We will run a load test on an AtScale cluster + 1 query engine vs baseline. Then we’ll upsize the database to remove the latency of the database. Then finally, on the last test, I will upscale AtScale clusters to 48 cores and 96G of Ram.

The overall performance gained mostly in the MDX concurrency. But the latency still exists back in the backend. If we remove the database latency, you’ll get

Now when we run on a faster DB, you’ll see the latency drop significantly, and now AtScale runs as fast as the database can respond to the queries.

What if we bump up AtScale cluster specs from 16 cores to 48 cores

The performance improvement is by far the most.

If you compare the baseline vs. top of the line configurations

You don’t see much difference for single to 5 concurrent queries. But when you have 20+ users, then the difference would be significant.

This is how AtScale utilizes the cores for user queries.

There are the database cores serving the queries from AtScale.

Here is from an 8 cores cluster

The system resources have been exhausted when you have concurrent/complex queries in AtScale.

Query Complexity

MDX

SQL

Customer Data

The last test uses a customer environment with a variance of CPU on a single node. The baseline is using 10 MDX queries.

And 20 SQL Queries

Running on M5.8xlarge

When we load them with a higher CPU of 36 cores vs. 8 cores

It proved we don’t gain much because the latency is still at the backend.

But if we improve the backend database.

The results are clear.

With proper tuning on the backend source, AtScale can respond as fast as their slowest platform.

Summary

There are no tricks to improve performance. And there is no myth that AtScale would speed up your query performance. AtScale would give you the semantic layer between your database across BI tools. Agree that AtScale would give better performance against their base data because atscale would create an aggregate. But in this discussion, we are not stating that atscale does not give you better query performance, but we want to state. Atscale will give you better performance when you give AtScale better resources.

The best practice to deploy AtScale is to remove the database (Postgres) from the engine to minimize the impact of AtScale UI latency and zookeeper sending out-of-resource messages to the engine and forcing it to restart. Min of 3 node cluster is recommended (1 host for Coordinator, 1 Host for Database, and one host for engine). You can see the cluster sizing recommendation document.