No results found

Your search did not match any results.

We suggest you try the following to help find what you're looking for:

  • Check the spelling of your keyword search.
  • Use synonyms for the keyword you typed, for example, try “application” instead of “software.”
  • Try one of the popular searches shown below.
  • Start a new search.
Contact Us Sign in to Oracle Cloud

Performance comparison of HeatWave with Snowflake, Amazon Redshift, Amazon Aurora, and Amazon RDS for MySQL


1. Common setup for analytic workload test

  • The workload is derived from the TPC's TPC-H benchmark*
  • Generate TPC-H data using the TPC-H data generation tool
  • Provision and configure the target service
  • Create TPC-H schema on the target service instance
  • Import TPC-H data generated to the target service instance
  • Run queries derived from TPC-H to test the performance
  • For best performance numbers, always do multiple runs of the query and ignore the first (cold) run
  • You can always do an explain plan to make sure that you get the best expected plan

2. Common setup for mixed workload test

  • The workload is derived from CH-benCHmark**
  • The OLTPBench framework (with changes made to support HeatWave) was used to run the mixed workload benchmark
  • Provision and configure the target service instance
  • Create the mixed workload schema (TPC-C and CH-benCH) on the target service instance
  • Generate and load the 1000 W (100 GB) dataset to the target service instance
  • Set incoming rate of TPC-C transactions at 500/sec (30K txns/min) and incoming rate of CH-benCH transactions at 4/sec (240 txns/min)
  • Run 128 concurrent sessions of TPC-C (OLTP) and 4 concurrent sessions of CH-benCH (OLAP)
  • Note the OLTP and OLAP throughput and average latency as reported

3. HeatWave specific setup

  • Use optimal encodings for the columns that will be loaded into HeatWave. Five of the string columns in the TPC-H schema are VARLEN encoded while others are DICTIONARY encoded.
  • Use custom data placement for the tables that will be loaded into HeatWave. For LINEITEM table, l orderkey is used as the data placement key. For the other tables, primary keys (default) are used as the data placement key.
  • Mark the tables as offloadable and load them into HeatWave
  • For each query, force offload to HeatWave using the hint (set_var(use_secondary_engine=forced))
  • A straight_join hint is required for certain queries to get the optimal query plan for HeatWave
  • Reference HeatWave TPC-H GitHub for TPC-H specific setup details and HeatWave GitHub for CH-benCHmark specific setup details

4. Amazon Redshift specific setup

  • Determine the best shape and cluster size for the experiments. In our experiments, we got the best results when we had 1 TB of uncompressed data per dc2.8xlarge node.
  • For efficient ingest, follow the guidelines for enhanced VPC routing
  • Use the default parameters as specified by the Amazon documentation
  • Make sure that the sort keys and distribution keys for each table are optimal for queries
  • Use the scripts provided by awslabs

5. Amazon Aurora specific setup

  • Use the largest shape possible so that as much of the data can fit into the buffer cache as possible
  • For the 1 TB and 4 TB TPC-H datasets:
    • Use the db.r5.24xlarge shapes
    • Set the innodb_buffer_pool size to 630 GB
    • Here are other settings that were modified from their default value in our TPC-H experiments: innodb_sort_buffer_size=67108864; lock_wait_timeout =86400; max_heap_table_size=103079215104; tmp_table_size=103079215104.
  • For the 1000 W TPC-C dataset (used in mixed workload tests):
    • Use the db.r5.8xlarge shape
    • Use the default parameter settings for innodb_buffer_pool and other parameters
    • Disable result cache
    • Enable parallel query
  • Set aurora_disable_hash_join = 0 and aurora_parallel_query = ON to use parallel query
  • Follow the Best Practices for Aurora database configuration for any other tuning
  • For parallel query to work, make sure that none of the tables are partitioned
  • A straight_join hint can be used if the query plan looks sub-optimal

6. Amazon RDS for MySQL specific setup

  • Use the largest shape possible so that as much of the data can fit into the buffer cache as possible
  • For the 4 TB TPC-H datasets, use the db.r5.24xlarge shapes
  • Set the innodb_buffer_pool size to 650 GB
  • Here are other settings that were modified from their default value in our TPC-H experiments: innodb_buffer_pool_instances=64; innodb_page_cleaners=64; innodb_read_io_threads=64; temptable_max_ram=64424509440; max_heap_table_size=103079215104; tmp_table_size=103079215104.
  • Follow the Best Practices for Amazon RDS for any other tuning
  • Make sure all the primary and foreign key indexes are created
  • A straight_join hint can be used if the query plan looks sub-optimal

7. Snowflake on AWS specific setup

  • For the 10 TB TPC-H workload, use the X-large cluster
  • Use clustering on ORDERS(o_orderdate) and LINEITEM(l_shipdate) tables
  • You can also try without clustering and pick the scheme that gives better performance

8. Results


4 TB TPC-H

HeatWave Amazon Redshift Amazon Aurora Amazon RDS for MySQL
Instance shape MySQL.HeatWave.
VM.Standard.E3
dc2.8xlarge db.r5.24xlarge db.r5.24xlarge
Cluster size 10 + 1 MDS 4 1 1
Total elapsed time 224 seconds 728 seconds 130 hours 338 hours
Annual cost USD$34,073 USD$110,560 USD$67,843 USD$54,393

10 TB TPC-H

HeatWave Amazon Redshift Snowflake on AWS
Instance shape MySQL.HeatWave.
VM.Standard.E3
ra3.4xlarge -
Cluster size 25 + 1 MDS 8 X-Large (16)
Total elapsed time 346.5 seconds 2380.4 seconds 2350.5 seconds
Annual cost USD$80,536 USD$150,784 USD$420,480

Note: Redshift and Snowflake numbers for 10 TB TPC-H are provided by a third-party.


30 TB TPC-H

HeatWave Amazon Redshift Snowflake on AWS Azure Synapse Google Big Query
Instance shape MySQL.HeatWave.
VM.Standard.E3
ra3.16xLarge - DW 15000c 5000 slots
Cluster size 75 + 1 MDS (MySQL.HeatWave.
BM.Standard.E3)
20 3X-Large (64) - -
Geomean Time 11.4 seconds 32.32 seconds 78.17 seconds 35.44 seconds 108.5 seconds
Annual cost USD$251,714 USD$1,507,840 USD$1,681,920 USD$1,249,956 USD$1,020,000

Note: 30 TB TPC-H numbers for Amazon Redshift, Snowflake, Azure Synapse and Google Big Query are derived from independent benchmark testing in October 2020.

* Disclaimer: Benchmark queries are derived from the TPC-H benchmark, but results are not comparable to published TPC-H benchmark results since these do not comply with the TPC-H specification.


1000 W (100 GB) CH-benCHmark

HeatWave Amazon Aurora
Instance shape MySQL.HeatWave.VM.Standard.E3 db.r5.8xlarge
Cluster size 2 + 1 MDS 1
OLTP throughput (txns/min) 30,000 30,000
OLTP latency 0.02 seconds 0.02 seconds
OLAP throughput (txns/min) 6.6 0.06
OLAP latency 35 seconds 637 seconds
Annual cost USD$9,293 USD$22,614

** Disclaimer: CH-benCHmark queries were derived from the TPC-C and CH-benCH queries specified in the OLTPBench framework and are not comparable to any published TPC-C or TPC-H benchmark results since these do not comply with the TPC specifications.

Notes: All costs include only the cost of compute. Storage costs are not included and are extra.