A scientific strategy to benchmarking SQL processing engines on AWS

Deciding on the correct SQL processing resolution for large-scale information analytics is a vital resolution for organizations. As information volumes develop exponentially, the know-how panorama has advanced to supply numerous choices for processing and analyzing this info effectively. This put up presents a scientific framework for evaluating and benchmarking SQL processing engines on AWS, utilizing Apache JMeter to conduct sensible efficiency testing at scale.

The AWS analytics ecosystem

AWS gives a wealthy portfolio of SQL processing options to fulfill numerous analytical wants:

• Serverless question companies – Amazon Athena is a serverless, interactive question service that makes use of customary SQL to investigate information in Amazon Easy Storage Service (Amazon S3), providing computerized scaling, parallel question execution, and pay-per-query pricing with no infrastructure administration required
• Knowledge warehouse options – Amazon Redshift gives scalable, high-performance cloud information warehousing with serverless choices, zero-ETL integrations, AI-powered question help, and seamless machine studying (ML) integration for contemporary analytics at scale
• Managed open supply engines – Amazon EMR helps Apache Spark SQL, Apache Trino (previously PrestoSQL), and different distributed question frameworks
• Self-managed choices – You’ll be able to deploy open supply engines like Apache Spark, Apache Flink, and Trino on Amazon Elastic Kubernetes Service (Amazon EKS) for higher management
• Companion options – You’ll be able to entry specialised large information analytics instruments by way of AWS Market

These choices are additional enhanced by trendy open desk codecs akin to Apache Iceberg, Delta Lake, and Apache Hudi, which convey essential enterprise options like ACID (Atomicity, Consistency, Isolation, and Sturdiness) transactions, schema evolution, and time journey capabilities to information lakes. These SQL processing options function underneath the AWS Shared Accountability Mannequin. AWS manages the safety of the underlying infrastructure and companies, and clients are answerable for safe configuration, entry administration, and information safety inside their testing environments. This division of accountability stays vital when evaluating and benchmarking totally different SQL engines. Correct safety configuration and implementation by clients is crucial for sustaining a safe analytics atmosphere.

Analysis challenges in SQL engine choice

The wealthy ecosystem of SQL processing choices creates important analysis challenges. Every SQL engine employs distinctive architectural approaches and optimization methods, making direct comparisons advanced. Organizations embarking on this analysis journey face a number of interconnected obstacles:

• Creating environments that precisely mirror manufacturing eventualities
• Creating take a look at datasets that mirror real-world information traits and volumes
• Replicating real-world question patterns and concurrency ranges
• Sustaining uniform testing situations throughout totally different engine architectures
• Controlling infrastructure bills all through the analysis course of

Efficiency issues at petabyte scale

When evaluating options for petabyte-scale deployments, the complexity intensifies significantly. A number of vital elements come into play:

• Useful resource administration – Distributed SQL engines require exact balancing of CPU, reminiscence, and storage assets. Suboptimal useful resource allocation can result in question failures and efficiency degradation, notably as information volumes develop.
• Knowledge distribution patterns – How information is distributed throughout partitions or nodes considerably impacts question efficiency. Knowledge skew can create processing bottlenecks, with some nodes dealing with disproportionate workloads whereas others stay underutilized.
• Concurrency dealing with – Excessive-concurrency environments demand refined workload scheduling and useful resource isolation mechanisms. The power to keep up constant efficiency underneath various concurrent masses turns into a vital differentiator between options.
• Significant metrics – Efficiency analysis at scale requires complete metrics evaluation:
  • Imply, median, and percentile response occasions (notably p90 and p95)
  • Question throughput underneath various concurrency ranges
  • Scalability traits throughout numerous workload sorts
  • Useful resource utilization effectivity throughout peak masses

Limitations of conventional benchmarks

Though industry-standard benchmarks like TPC-DS and TPC-H present helpful insights, our expertise with a number of buyer engagements has proven that tailor-made, workload-specific testing usually reveals efficiency traits not captured by these standardized assessments. That is very true for advanced, multi-tenant environments with numerous question patterns. Organizations that complement customary benchmarks with workload-specific testing usually expertise shorter proof-of-concept cycles, optimized analysis prices, and extra environment friendly testing operations. This complete strategy helps scale back uncertainty within the remaining resolution choice course of.

Stipulations

Earlier than you dive into the analysis course of, be sure to have the next stipulations:

• An AWS account with acceptable permissions to create and handle Amazon Elastic Compute Cloud (Amazon EC2) situations and entry the SQL engines you propose to benchmark.
• Fundamental familiarity with AWS companies, notably Amazon EC2 and the SQL engines you plan to judge (akin to Athena, Amazon Redshift, or Amazon EMR).
• Expertise with SQL and information analytics ideas.
• Entry to the SQL engines you select to benchmark. This put up assumes you’ve already arrange the engines you need to take a look at. For setup directions, confer with the AWS documentation for every service.
• A dataset appropriate to your benchmarking wants. Dataset creation and loading should not coated on this put up. Construct petabyte-scale artificial take a look at information with Amazon EMR on EC2 gives prescriptive steering to generate take a look at datasets at scale. Be certain your take a look at datasets are saved in S3 buckets with encryption enabled (utilizing SSE-KMS or SSE-S3) and that each one service connections use TLS for information in transit.

Advantages of Apache JMeter

As organizations scale their analytics workloads to petabyte ranges, there’s a rising want for a strong, structured strategy to SQL question efficiency testing. Though many organizations develop customized testing frameworks or use numerous benchmarking instruments, these approaches usually lack standardization and might be tough to copy throughout totally different SQL engines. The complexity of recent information architectures, mixed with the number of obtainable SQL processing options, calls for a scientific analysis methodology. Apache JMeter emerges as a robust resolution to deal with this problem. Although historically recognized for net software testing, JMeter’s extensible structure and strong function set make it notably well-suited for SQL efficiency testing at scale.JMeter gives a number of benefits for evaluating SQL engines:

• Assist for a number of protocols and connections
• Capability to simulate advanced concurrent workloads
• Constructed-in efficiency metrics and reporting
• Extensible structure for customized testing eventualities
• Integration capabilities with steady integration and steady supply (CI/CD) pipelines

Via this proposed framework, which has been validated throughout a number of buyer engagements at petabyte scale, we goal to assist organizations make extra knowledgeable choices when choosing a SQL processing resolution. Our expertise working with clients to evaluate numerous AWS Analytics companies and open supply options has demonstrated {that a} systematic analysis strategy considerably reduces proof-of-concept cycles and optimizes useful resource investments. This framework has helped organizations successfully consider companies like Athena, Amazon Redshift, and Amazon EMR, alongside open supply options akin to Trino on Amazon EKS, based mostly on their particular workload profiles and efficiency necessities.With this system, organizations can accomplish the next:

• Navigate the advanced panorama of large-scale information processing applied sciences
• Scale back proof-of-concept cycles from months to weeks
• Decrease infrastructure prices throughout analysis phases
• Make data-driven choices about know-how choice
• Higher align know-how decisions with enterprise necessities
• Set up repeatable testing patterns for future evaluations

Testing methodology in observe

A profitable SQL engine analysis requires understanding and replicating real-world workload patterns. Our methodology, refined by way of quite a few buyer engagements, focuses on complete testing throughout a number of dimensions whereas remaining adaptable to particular organizational wants.

Question sample choice

We start by choosing consultant question patterns that mirror manufacturing workloads:

• Aggregation queries that summarize massive datasets utilizing operations like SUM, AVG, and COUNT
• Complicated be a part of operations that take a look at the engine’s means to mix information effectively throughout a number of tables
• String operations that consider textual content processing capabilities
• Nested queries that assess the engine’s optimization capabilities for advanced question constructions

A rigorously chosen set of 8–10 queries usually gives enough protection whereas protecting the analysis manageable. These ought to mirror your precise workload traits and enterprise necessities.

Knowledge quantity variations

Testing throughout totally different information volumes is vital for understanding scalability traits. We construction our assessments round various information scan ranges:

• Small-scale scans – Queries accessing 1–7 days of knowledge (megabytes to gigabytes)
• Giant-scale scans – Queries spanning 14–30 days (terabytes to petabytes)

This strategy evaluates each I/O effectivity with massive datasets and metadata dealing with with smaller, frequent queries, serving to perceive how companies like Amazon EMR, Amazon Redshift, or Athena optimize question execution throughout totally different entry patterns.

Concurrency testing

Actual-world analytics environments not often course of single queries in isolation. Our methodology incorporates the next options:

• Progressive concurrency testing beginning at decrease ranges (usually 16, 32, 64, and 128 parallel queries), although these numbers might be adjusted based mostly in your take a look at infrastructure capability and particular necessities. We suggest beginning with smaller concurrency ranges and steadily scaling as much as perceive efficiency traits
• Various question complexity and frequency (known as question weights) to simulate practical workload distributions. This implies some queries are run extra usually or are extra resource-intensive than others, mimicking real-world utilization patterns.
• Blended question patterns working concurrently to check useful resource administration.
• Constant execution throughout totally different date ranges to judge scaling habits.

This strategy is especially vital when evaluating managed companies just like the workload administration capabilities of Amazon Redshift or the useful resource allocation methods of Amazon EMR.

Question weight distribution

Manufacturing environments usually see various frequencies of various question sorts. Our framework incorporates weighted question distribution to simulate real-world eventualities extra precisely. In a typical distribution, frequent light-weight queries may signify 60% of the workload, advanced analytical queries may comprise 30%, and resource-intensive information processing operations may make up the remaining 10%.This weighted strategy makes certain efficiency testing displays precise utilization patterns moderately than synthetic benchmarking eventualities. The precise distribution ought to mirror your group’s particular workload patterns.

Sequential vs. concurrent testing

Our methodology implements two distinct testing phases:

• Sequential testing – Establishes baseline efficiency metrics:
  • Runs every question sort independently throughout totally different date ranges
  • Runs a number of iterations to offer consistency and establish variability
  • Helps perceive particular person question efficiency traits
• Concurrent testing – Simulates real-world multi-user eventualities:
  • Implements weighted question distributions
  • Assessments totally different concurrency ranges to establish scaling limitations
  • Evaluates useful resource administration capabilities of various engines

JMeter effectively implements each testing phases whereas sustaining constant take a look at situations throughout SQL engines. Its means to deal with numerous JDBC connections makes it notably appropriate for testing AWS analytics companies.Via this structured strategy, organizations can collect complete efficiency information reflecting their particular use circumstances, enabling knowledgeable SQL engine choice choices whereas sustaining core rules of systematic analysis and practical workload simulation.

Check plans

To judge SQL engines’ efficiency underneath various workloads, we designed two take a look at eventualities: sequential and concurrent execution plans. Every state of affairs was executed throughout totally different information volumes by adjusting the question date vary filters to cowl 1, 7, 14, and 30 days. These variations simulate typical analytical workloads with progressively growing information sizes.For sequential runs, every take a look at was handled as a definite batch, grouping all queries (Question 1 to Question 9) underneath the identical date vary—every question will scan information for 1, 7, 14, and 30 days with acceptable date filtering within the question’s the place predicate. We used JMeter to seize common question response occasions for every batch. This configuration was run 3 times, and the ultimate metrics mirror the common response time throughout these iterations to make sure reliability and account for environmental variance.Though three iterations present preliminary insights, in case you observe important variations in outcomes (usually greater than 10% deviation between runs), think about increasing to 10 or extra iterations. This extra sampling helps set up statistical significance, establish true efficiency patterns, and distinguish outliers (past three customary deviations) from regular variations. Doc any constant anomalies, as a result of they could point out vital efficiency or safety issues to your particular atmosphere.The next desk reveals the pattern take a look at plans template for the sequential take a look at plan run.

For the concurrent take a look at plan, we launched a probabilistic weighted distribution to the queries (Question 1 to Question 9), simulating a extra practical production-like atmosphere the place question frequency varies based mostly on enterprise relevance and utilization patterns. This added a layer of complexity to raised mirror how the SQL engine would carry out underneath real-world concurrent entry patterns.The next desk reveals the pattern take a look at plans template for the concurrent take a look at plan run.

For instance, for configuration of *7 days concurrent run with **128 concurrency, the proposed configuration distributes Question 1 to Question 9 with acceptable weighted submissions such that Question 9 is executed the best variety of occasions within the general 128 executions submitted throughout all 9 queries for this run.

JMeter setup

To start, you will need to arrange JMeter on a machine that may deal with the specified take a look at load. An EC2 occasion is a versatile and cost-effective choice. Select an occasion sort with enough vCPUs to help your most deliberate concurrency. For instance, a c6i.4xlarge or larger is often appropriate for average to excessive throughput testing eventualities. For the working system, you may select Amazon Linux, which is optimized for AWS. For production-grade testing environments, deploy the JMeter EC2 occasion in a non-public subnet of a digital personal cloud (VPC) with acceptable safety teams that permit solely required connections. This community isolation helps preserve safety whereas executing efficiency assessments. Think about using Amazon Digital Non-public Cloud (Amazon VPC) endpoints for safe entry to AWS companies.

After the occasion is provisioned, set up Java (Java 17 LTS or Java 21 LTS) and obtain the newest model of JMeter. Make sure to configure the system with acceptable JVM choices to allocate enough heap reminiscence for large-scale take a look at executions. Consult with Getting Began to study extra.

# Set up Java
sudo yum replace -y # For Amazon Linux
sudo yum set up java-17-amazon-corretto -y

# Obtain JMeter and place the suitable jdbc driver for the engine of your choice underneath lib folder
wget https://downloads.apache.org//jmeter/binaries/apache-jmeter-5.6.3.tgz
tar -xvzf apache-jmeter-5.6.3.tgz
cd apache-jmeter-5.6.3/lib

# Launch JMeter in GUI mode (if utilizing a GUI-capable setup) or use CLI for distant testing
./bin/jmeter

JMeter ideas

Earlier than you create take a look at plans in JMeter, it’s vital to grasp just a few foundational ideas that affect how your take a look at plan behaves—akin to thread teams, user-defined variables, and JDBC connection. These parts allow the simulation of real-world question masses, together with concurrency and pacing.

Check plans

The take a look at plan is the top-level container for a JMeter take a look at. It defines the general testing technique, together with the queries to execute, their parameters, and the concurrent person habits. These plans are represented as jmx recordsdata that may then be used for CLI-based execution. JMeter helps each GUI and CLI modes. It’s extremely advisable that you just use the JMeter GUI primarily for creating take a look at plans as jmx, and use the CLI for big load assessments. You can too run thread teams consecutively for sequential execution. The default habits is to run all thread teams in parallel fitted to concurrent execution. Consult with Constructing a Check Plan to study extra about choices obtainable with take a look at plans.

Consumer-defined variables

Consumer-defined variables are international parameters that you may reuse all through the take a look at plan. They’re useful for outlining database credentials, server URLs, or question parameters. For instance:DB_URL=jdbc:trino://trino-cluster.instance.com:8889?SSL=true #Allow SSL/TLS

You’ll be able to configure authentication (person title and password) by way of your group’s authorized strategies, akin to AWS Secrets and techniques Supervisor (see Transfer hardcoded secrets and techniques to AWS Secrets and techniques Supervisor) AWS Identification and Entry Administration (IAM) roles, or different safe credential administration programs.

Thread teams

A thread group represents a gaggle of digital customers (threads) executing take a look at actions. Every thread simulates a single person sending requests to the SQL engine. This can be utilized to simulate concurrent runs. For instance, within the previous template, Question 3 has 19% weightage throughout 128 runs. This implies .19*128=25 complete runs, so we set the thread group to 25.

JDBC connection configuration

JDBC connection configuration units up the database connection for the take a look at. It specifies the database URL, driver, and credentials required for executing SQL queries. Key fields to configure are database URL and JDBC driver class. The next desk summarizes the totally different configuration settings.

JDBC requests

The JDBC request executes SQL queries in opposition to the database utilizing the configuration outlined within the JDBC connection configuration.

For instance, following command runs the JMeter in CLI mode:

# Run benchmarks in CLI mode 
./jmeter -n -t .jmx -l .log -e -o /output/

The output folder will comprise an HTML report with totally different statistics. The next screenshot illustrates 128 concurrent runs.

Monitoring and logging

For complete visibility and audit necessities, allow AWS CloudTrail logging, VPC Move Logs, and service-specific logs (like Amazon S3 entry logs). These logs might be centralized in Amazon CloudWatch Logs for monitoring and evaluation. This gives correct audit trails whereas evaluating totally different SQL engines and helps monitor entry patterns and potential safety occasions.

Submit-test steps

After working your JMeter assessments, proceed with the next steps:

1. Overview the HTML report’s key metrics, together with response occasions, throughput, and error charges throughout totally different question sorts and concurrency ranges.
2. Run an identical take a look at plans throughout your candidate SQL engines for direct efficiency comparability.
3. Refine your take a look at plans based mostly on preliminary findings, specializing in areas the place efficiency variations are important.
4. Consider the price implications alongside efficiency metrics to make a balanced resolution.

These steps will help you systematically consider and choose probably the most appropriate SQL engine to your analytics workloads.

Sources

Within the previous steps, we walked by way of a UI-based setup for JMeter together with take a look at plans. Now we have created just a few pattern JMeter take a look at plans for each sequential and concurrent runs together with pattern take a look at reviews. You’ll be able to modify the plans to suit your wants.

1. JMeter pattern report
2. JMeter take a look at plan for sequential run
3. JMeter take a look at plan for concurrent run

Clear up

After you full your benchmarking course of, clear up the assets to keep away from pointless prices:

1. Cease or delete the EC2 situations used for working JMeter.
2. Relying on which SQL engines you used for testing, clear up energetic assets.
3. Overview your AWS Administration Console to substantiate no energetic assets stay.
4. For those who created take a look at datasets in Amazon S3 or different storage companies particularly for this benchmarking, think about deleting them in the event that they’re now not wanted.
5. Though JMeter take a look at plans and outcomes don’t incur AWS prices, arrange or delete native recordsdata as wanted to your record-keeping.

Abstract

Deciding on the correct SQL processing resolution for large-scale analytics calls for a scientific, data-driven strategy. Our JMeter framework will help organizations successfully consider totally different SQL engines by simulating real-world workload patterns throughout numerous question sorts, information volumes, and concurrency ranges. This system reduces proof-of-concept cycles and gives insights past conventional benchmarks, serving to you assess managed AWS companies like Athena and Amazon Redshift and open supply options on Amazon EKS.

Concerning the authors