Evaluate query performance of Azure Data Explorer
Published Oct 11 2022 12:46 AM 1,520 Views
Microsoft

Authors: Devang shah(@devangshah) and Surya Josyula(@SuryaJ ) are Program Managers for Azure Data Explorer (Kusto) in Azure Synapse Customer Success Engineering (CSE) Team.

 

Note: Azure Data Explorer exists as both a Standalone offering (Azure Data Explorer) and part of Synapse (Synapse Data Explorer). The following content will apply to both these offerings (will be referred to as 'ADX') without any change.

 

Introduction:

Azure Data explorer is an analytics service that is designed to perform exploratory and observational analytics on structured, semi-structured, and unstructured data with low latency in near real time. It offers intuitive querying capability using the Kusto Querying Language (KQL) to explore your data and discover patterns, identify anomalies and outliers, create statistical modelling, and more! Users are always looking for a way to fairly assess the performance of their KQL queries.

 

In this blog, we will provide guidance on evaluating performance through load testing your ADX cluster

  • Considerations to keep in mind when planning the load test
  • Executing load tests with Grafana k6 or Apache JMeter
  • Examples of some load test results

and answers the following questions:

  • How do I evaluate the performance of ADX?
  • How do I test if ADX can perform well under my expected query load?
  • Can ADX handle concurrent query requirements?
  • What ingestion and query latency can be achieved with ADX?
  • Is my ADX architecture suitable for my expected ingestion and query load?
  • What's the most optimal price performance ratio that I can achieve with my ADX architecture?  

 

Load Testing

When evaluating any OLTP or OLAP engine, you want to be assured that the engine can handle the data ingestion and query load at an acceptable price point. Performance of a database or analytical store under an expected load is an important criterion to include in your evaluation. It is imperative to measure these performance metrics to drive your decision making on the following aspects:

  • Architecture: What changes do I need to make to my architecture to meet my performance criteria?
  • SKU: Which SKU is more suitable to my workload profile and performance requirements?
  • Data ingestion: What changes do I need to make in my data ingestion pipeline in terms of throughput, burst vs continuous load, upstreaming buffering vs queuing, among others?
  • Queries and requests: What adjustments do I need to make to my queries and requests to achieve the most optimal performance?
  • Integrations: What are the additional components that are consuming resources from the OLTP/OLAP system that can be tuned? 

 

Considerations

Things to keep in mind when planning your load tests:

  • Cluster Size: Size your ADX cluster appropriately to achieve an optimal price/performance ratio. Running a load test on an undersized cluster will mostly result in high query response times or query failures or instability in the cluster.
  • Estimated Load: If you know the estimated queries/second that you intend to send to your ADX cluster, then configure the load test according to that. For example, 1 query/second means 86400 queries/day.
  • Autoscaling: ADX cluster will not scale out when you run the load test for a few minutes. ADX scales out on sustained CPU usage, query, and ingestion operations and not based on a few load tests.
  • Long-running Load Test: Running load tests for longer periods can generate unnecessary load on the cluster that can potentially trigger a scale-out event and additional costs. Configure your load tests appropriately. Typically, running a load test for 5 mins with 20 virtual users can generate 7 requests/second which is equivalent to sending nearly 200k queries to ADX in an 8-hour business day.
  • Synthetic Testing: The load test is synthetic in nature meaning, all users from the same location are sending the same requests for a defined duration. Your real-life scenario will typically have users across many locations, sending different requests (queries) to the ADX cluster. So, please take the load test results into account with this perspective in mind.
  • Load Test vs Stress Test: Load testing is different from stress testing. It is often attractive to send a very large load to any system and see how it copes. However, each system operates well within its size and limits and hence can handle the appropriate load. When planning your load, consider your business scenario and create the load accordingly rather than as a theoretical possibility.
  • Single Query vs Multiple Queries: Load tests can be performed by sending a single query multiple times. After the first few executions, ADX will respond with the results cached in RAM. To execute a load test that mimics close to a real business scenario, it is advisable to generate many different queries and send them to ADX sequentially or concurrently. This will give a much more representative indication of the load that ADX can handle.

 

Tools and Guidance

We have provided detailed steps to configure and guidance on load testing ADX with Grafana k6 and Apache Jmeter.

Optimise your queries for high concurrency and performance - Optimize for high concurrency with Azure Data Explorer | Microsoft Learn

 

Example

The below screenshot shows the results of a load test conducted on ADX using Grafana k6. This load test included 10 different queries that were concurrently sent to ADX for a duration of 3 mins generating a total request volume of 2144 requests, nearly 12 requests per second. P95 response time from ADX was 2.38 seconds which was well within the desired performance measure of the customer.

 

  • ADX Cluster Profile: This load test was executed on 2 node 16 core cluster running concurrent batch ingestion and streaming ingestion. In terms of artifacts, cluster had a set of staging tables, curated tables, update policies that were loading and transforming data from staging tables to curated tables, a set of materialized views that were materializing the last known rows for a given criteria. 
  • Request Profile: Load test requests sent to ADX included 10 uniquely different queries that included 2 or 3 'where' clauses, a 'summarize' operation that calculated averages over a given timespan.

devangshah_0-1663162969157.png

 

Summary:

ADX is designed to sustain high loads and provide blazing fast query performance. There are many options to optimize data loads, control how extents are stored and improve query performance even further. Following best practices for the test queries will make sure they perform the best using Kusto's compute. We have elaborated use of 2 open-source tools - Grafana K6 and JMeter in our document. You can further use configured files from JMeter (extension ".jmx") to use in Azure Load Tests. For more information on this, see Create JMeter based load test in Azure.

 

2 Comments
Co-Authors
Version history
Last update:
‎Oct 12 2022 02:50 AM
Updated by: