Evaluate query performance in your cluster

Load testing is important to evaluate and ensure that your query performance meets you requirements.

In this article, we'll show you how to evaluate query performance in your cluster by sending concurrent or sequential queries using the Azure Data Explorer REST API.

Prerequisites

  • Permissions to create a service principal in your Azure Active Directory
  • Permissions to grant the service principal Viewer role in your Azure Data Explorer database
  • Your API unit testing tool must be able to retrieve a bearer token to authenticate against our REST API requests.

Retrieve a bearer (authentication) token

  1. Create a service principal and make a note of the following information that you'll need later:

    • AppID or ClientID
    • TenantID
    • Secret
  2. Grant the service principal Viewer role in your database.

  3. Create a POST request to https://login.microsoftonline.com/<My_Tenant_id>/oauth2/v2.0/token? using the TenantID you noted earlier. Send the POST request using your preferred tool with the following parameters:

    Key Value Description
    grant_type client_credentials The OAuth 2.0 grant type
    client_id <app-or-client-id> The appId or ClientID you noted earlier
    client_secret <client-secret> The secret you noted earlier
    scope https://\<cluster-name>.<region-name>.kusto.windows.net/.default The URI for your cluster. You can get the URI from the overview page of your Azure Data Explorer cluster.

    Note

    For Synapse Data Explorer pools, use https://<pool-name>.kusto.azuresynapse.net/.default. You can get the URI from the overview page of your Azure Synapse Data Explorer pool.

    The following image shows an example of the sending request parameters using Postman:

    Screenshot of POST request, showing parameters.

  4. Check the response body for the bearer token and copy it as you'll need later. The following image shows the response body using Postman.

    Screenshot of POST request, showing the bearer token.

  5. Test the bearer token by sending a request to the Azure Data Explorer REST API and verifying the response. Build the query using the following format: <cluster-uri>/v2/rest/query. You can get the cluster query URI from the overview page Azure Data Explorer cluster or Synapse Data Explorer pool. Send the POST request using your preferred tool with the following parameters in the header:

    Key Value Description
    Accept application/json The content type of the request
    Authorization <bearer-token> The bearer token you noted earlier
    Accept-Encoding deflate The compression format of the request
    Content-Type application/json; charset=utf-8 The content type indicating the media type of the request
    Host <cluster-name>.<region>.kusto.windows.net The cluster query URI without the protocol
    x-ms-client-request-id <uuid> A random UUID to identify the request. If you're using Postman, you can use Postman.Query to generate the UUID.
    x-ms-user-id <user-name> A user name for reference. We recommend specifying one as it's useful when tracing and Root Cause Analysis query performance.
    x-ms-app <app-name> A app name for reference. We recommend specifying one as it's useful when tracing and Root Cause Analysis query performance.

    The following image shows an example of sending a request to the Azure Data Explorer REST API using Postman.

    Screenshot of POST request, showing the parameters for the API request.

    In the body of the request, include the following:

    Key Description
    db The name of the database you want to query.
    csl The Kusto query you want to run. The query must be sent as a string literal. If used, encode string literals. We recommend verifying the query before you send it by testing it in the web UI.

    Screenshot of POST request, showing the parameters for the body of the API request.

    In the response, you'll get a JSON object with the result of the query.

Load testing

In this section, you'll learn how to load testing your cluster using the Azure Data Explorer REST API. You can use any load testing tools you prefer. The following steps will guide you through testing using Grafana k6 and Apache JMeter.

Before you begin

Before you start load testing, consider the following:

  • Size your cluster appropriately to achieve an optimal price/performance ratio. Running tests on undersized cluster will likely result in high query response times.

  • Configure the load test to reflect the estimated queries/second that you intend to send to your cluster. For example, 1 query/second translates to 86400 queries/day.

  • Scale out is triggered by sustained CPU usage, and query and ingestion operations. Therefore, you should consider the following when planning your load test:

    • Running a few short load tests that last a matter of minutes is unlikely to trigger your cluster to scale out.
    • Running load tests for longer periods can generate unnecessary load on the cluster that can potentially trigger a scale-out event and additional costs.

    Hence, configure your load tests appropriately. Typically, running a load test for 5 mins with 20 virtual users can generate 7 queries per second, which is equivalent to sending nearly 200,000 queries to your cluster in an 8-hour business day.

  • Load tests are synthetic in nature. This means that all users from the same location send the same requests for a defined duration. Your real-life scenarios typically have users from many locations, sending different queries to your cluster. Bear this in ming when reviewing the results of the tests.

Grafana k6 is an open-source tool built for load testing using JavaScript that integrates with many other tools such as Microsoft Visual Studio Code and GitHub.

Prerequisites for Grafana k6

  • An IDE
  • Grafana k6. Choose between using a local installation with unlimited tests (k6.io) or the k6 Cloud offering

Run load tests using Grafana k6

In the following steps, you'll run load tests using a local installation of Grafana k6 and the latest version of Microsoft Visual Studio Code.

  1. In Visual Studio Code, create a file with the following JavaScript code:

    import { sleep } from 'k6'
    import http from 'k6/http'
    
    export const options = {
      ext: {
        loadimpact: {
          distribution: { 'amazon:fr:paris': { loadZone: 'amazon:fr:paris', percent: 100 } },
          apm: [],
        },
      },
      thresholds: {},
      scenarios: {
        Scenario_1: {
          executor: 'constant-vus',
          gracefulStop: '30s',
          duration: '<duration-in-minutes>',
          vus: <number-of-users>,
          exec: 'scenario_1',
        },
      },
    }
    
    export function scenario_1() {
      let response;
      let authtoken = '<access_token>';
    
      response = http.post(
        '<cluster-uri>/v2/rest/query',
        '{\r\n  "db":"<database-name>",\r\n  "csl":"<kusto-query>",\r\n  "properties":"{\\"Options\\":{\\"queryconsistency\\":\\"strongconsistency\\"},\\"Parameters\\":{},\\"ClientRequestId\\":\\"k6.Query;<random-uuid>\\"}"\r\n}',
        {
          headers: {
            Accept: 'application/json',
            'Content-Type': 'application/json; charset=utf-8',
            Host: '<cluster-uri>',
            'Accept-Encoding': 'gzip',
            'x-ms-client-request-id': '<app-name>;<random-uuid>',
            'x-ms-user-id': '<user-name>',
            'x-ms-app': '<app-name>',
            Authorization:
              authtoken,
          },
        }
      )
      // Automatically added sleep
      sleep(1)
    }
    

    Use the following table to replace the placeholders in the code:

    Parameter Example value Description
    duration 1m The number of minutes for the load test to run
    vus 20 The number of virtual users to create for simulating the load
    access_token <bearer-token> The bearer token you noted earlier
    cluster-uri https://\<cluster-name>.<region-name>.kusto.windows.net/.default The URI for your cluster. You can get the URI from the overview page of your Azure Data Explorer cluster.
    database-name Sampledb The name of the database you want to query.
    kusto-query "Tablename | take 10" The Kusto query you want to run. The query must be sent as a string literal. If used, encode string literals. We recommend verifying the query before you send it by testing it in the web UI.
    random-uuid e9f884e4-90f0-404a-8e8b-01d883023bf1 A random UUID to identify the request. Use any internet-based generator to generate a UUID.
    Host <cluster-name>.<region>.kusto.windows.net The cluster query URI. For Azure Synapse Data Explorer pools, use the relevant Data Explorer pool URI.
    x-ms-client-request-id e9f884e4-90f0-404a-8e8b-01d883023bf1 A random UUID to identify the request. If you're using Postman, you can use Postman.Query to generate the UUID.
    x-ms-user-id <domain/user-name> A user name for reference. We recommend specifying one as it's useful when tracing and Root Cause Analysis query performance.
    x-ms-app k6 A app name for reference. We recommend specifying one as it's useful when tracing and Root Cause Analysis query performance.

    Tip

    • If you plan on including multiple queries in the same load test script, distinguish each query by including a query identifier in the client request ID. For example, to include the query identifier Q001, use the client request ID 'Q001; e9f884e4-90f0-404a-8e8b-01d883023bf1'. You can then use this identifier in to distinguish each query and the time it took to execute.
    • Grafana k6 provides an option to configure complex load tests using the option() function. You can also create multiple scenarios in the same load test. With the Grafana k6 Cloud offering you can create, configure, and execute load tests.
  2. In the Azure Data Explorer web UI, run the following query to create a dashboard to view performance results for queries run in the last hour.

    .show queries
    | where StartedOn >=ago(1h)
    | where Application == "k6"
    | where Database == "<database-name>"
    | where State == "Completed"
    | summarize percentile(Duration, 95), avg(Duration), min(Duration), max(Duration), count() by Text
    | extend P95_seconds = format_timespan(percentile_Duration_95,"s.ff"), Avg_seconds = format_timespan(avg_Duration,"s.ff")
    | extend Min_seconds = format_timespan(min_Duration,"s.ff"), Max_seconds = format_timespan(max_Duration,"s.ff"), TotalExecutions = count_
    | project-away percentile_Duration_95, avg_Duration, min_Duration, max_Duration, count_
    

    The query results show the time it takes to execute all the requests sent by Grafana k6.

  3. In Visual Studio Code, use PowerShell or Bash to run the script, as follows:

    Tip

    Before starting a full test run, perform a small load test execution with 1-2 virtual users for 10 seconds. This will help you identify if there are any HTTP errors, or authentication or authorization issues before you launch the full load test run.

    k6 run \<your-k6-test-script-file-name>.js
    

    The following image shows the command running and the progress bar showing the progress of the load test.

    Screenshot of the load test running, showing the progress bar.

  4. Once the load test completes, take a look at the results Grafana k6 results and web UI dashboard results. For information about Grafana k6 results, see Metrics (k6.io).

    The following image shows the Grafana k6 results with the most important metrics highlighted.

    Screenshot of the Grafana k6 results, highlighting the  most important metrics.

Troubleshoot Grafana k6 issues

The following is a list of the common issues you might encounter when using Grafana k6:

Issue Resolution
Authentication The bearer token is valid for 3600 seconds normally. Always retrieve the latest token before running a test to avoid getting http authentication failure errors.
Authorization Ensure that the service principal has the right Viewer role on the cluster database.

Next steps