Choosing the correct shape for a MySQL HeatWave instance in Oracle Cloud Infrastructure (OCI) when migrating your on-premise MySQL database involves assessing your current workload requirements and matching them to the available MySQL HeatWave shapes. A “shape” in (OCI) defines the compute resources (CPUs, memory, maximum network bandwidth, etc.) allocated to your MySQL Database System (DB System) and HeatWave cluster. Here’s a quick step-by-step guide to help you make an informed decision.  If you want to collect more detailed information about your MySQL instance, see this post – “MySQL Commands To Gather Runtime Statistics For Database Performance Assessment“.

Understand Your On-Premise Workload

In order to understand your current workload, you will need information such as data size, workload type, query performance requirements and current resource usage.  For the data size, you will need to e stimate the total size of your database, including tables, indexes, and any anticipated growth. HeatWave is optimized for in-memory analytics, so the data you plan to query with HeatWave must fit into the cluster’s memory.

Is your workload primarily  transactional (OLTP), analytical (OLAP), or a mix of both? HeatWave excels at accelerating analytical queries, so also pay attention to the analytics portion if you’re adding a HeatWave cluster.

What types of queries are you running?  MySQL performs great with OLTP queries, but you will need to identify the complexity and frequency of your analytical queries. More complex queries or higher concurrency may require more compute power or additional HeatWave nodes.  Analyze your on-premise system’s CPU, memory, and I/O usage under peak load to establish a baseline so you can replicate the compute power you need to match what you currently have on-premise.

HeatWave Shapes

In OCI, there are two main categories of shapes – ECPU shapes for the MySQL DB System (OLTP) and HeatWave-specific shapes for the HeatWave OLAP cluster nodes.  

A shape is a template that determines the number of ECPUs, amount of memory, and other resources that are allocated to a MySQL DB system, a HeatWave cluster node, or a read-replica. Both the DB system and the HeatWave cluster have their own shapes. Read-replicas can have the same or different shape as compared to the DB system, but with a write-heavy source instance, the read-replica should have the same shape as the source database so it can process the replicated transactions without falling behind the source. Each shape for the DB system or read replica is associated with a list of default configurations. The configuration contains a collection of variables that define the operations of the MySQL instance. There are two default configurations for most of the shapes:

  • DB System standalone for OLTP: ECPU shapes are architecture-agnostic (supporting AMD or Intel processors) and most ECPU shapes support HeatWave clusters.  ECPU shapes are optimized for standalone DB systems and read replicas. And most shapes (MySQL.8+) support read-replicas.  See Supported Shapes.
  • HA: Optimized for highly-available DB systems. See High Availability.

For OLAP, HeatWave-specific shapes define the resources for the HeatWave cluster nodes, which are separate from the DB System shape. Examples include HeatWave.32GB (small workloads) and HeatWave.512GB (larger workloads).

Match The Shapes To Your Requirements

For the MySQL DB System (OLTP), you will want to choose a shape which supports your transactional workload.  The DB System handles all incoming client connections, transactional (OLTP) queries, and serves as the source for HeatWave’s analytical data.  Determine your maximum concurrent connections during peak load. For example, if your on-premise MySQL handles 500 simultaneous connections at peak, you’ll need to replicate or exceed that capacity.  Each connection consumes memory (e.g., for thread stacks, buffers) and CPU (for query processing).  High concurrency increases contention for CPU and memory, potentially degrading performance if undersized.

For example, a small transactional workload might use a shape with 4-8 ECPUs, while a high-availability (HA) setup with mixed OLTP/OLAP might need 16+ ECPUs.

For the HeatWave OLAP, select a HeatWave shape based on your analytical data size and performance needs.  

  • Use HeatWave.32GB for datasets under 50 GB.
  • Use HeatWave.512GB for larger datasets, scaling the number of nodes as needed.
  • For datasets exceeding 10 TB, pair with MySQL.HeatWave.BM.Standard and estimate node count.  

When configuring a HeatWave cluster in the Oracle Cloud Console, use the “Estimate Node Count” feature. Input your data size and selected shape (HeatWave.32GB or HeatWave.512GB), and it will suggest the number of nodes required. This helps ensure your cluster can handle your data in memory.

HeatWave requires the data to be loaded into memory for analytics, so the total memory across all HeatWave nodes must exceed your analytical dataset size.  For example: A 1 TB dataset with HeatWave.512GB (512 GB per node) requires at least 2 nodes (1 TB ÷ 512 GB ≈ 2).  (This is if you decide to not use data compression.)  HeatWave compresses data as it is loaded, which permits HeatWave nodes to store more data. More data per node reduces costs by minimizing the size of the HeatWave Cluster required to store the data.

While data compression results in a smaller HeatWave Cluster, decompression operations that occur as data is accessed affect performance to a small degree. Specifically, decompression operations have a minor effect on query runtimes, on the rate at which queries are offloaded to HeatWave during change propagation, and on recovery time from Object Storage.  If data storage size is not a concern, disable data compression by setting the rapid_compression session variable to OFF before loading data.

You may add additional nodes for redundancy, performance, or larger datasets. The minimum is one node, but the maximum depends on your tenancy limits and workload.

Key Shape Considerations

Ensuring that the number of ECPUs and memory in your MySQL DB System and HeatWave instance can handle the peak number of connections requires a focused analysis of your workload’s concurrency, resource demands, and the specific roles of the DB System and HeatWave cluster.

The DB System (running MySQL) handles all incoming client connections, transactional (OLTP) queries, and serves as the source for HeatWave’s analytical data.  The HeatWave Cluster accelerates analytical (OLAP) queries and doesn’t directly manage client connections—it relies on the DB System to route queries to it. Thus, peak connection handling is primarily a DB System concern.

Determine your maximum concurrent connections during peak load. For example, if your on-premise MySQL handles 500 simultaneous connections at peak, you’ll need to replicate or exceed that capacity in the cloud.  Remember – each connection consumes memory and CPU.  High concurrency increases contention for CPU and memory, potentially degrading performance if the shape is undersized.

Step-by-step Approach – Assess Current Peak Connection Load

You will want to assess your current peak connection load on your current on-premise MySQL instance:

Run SHOW STATUS LIKE 'Threads_connected'; during peak usage to see active connections.
Use SHOW VARIABLES LIKE 'max_connections'; to see the configured limit (default is 151, but is often increased).
Monitor CPU and memory usage (e.g., via top or htop) during peak load to correlate with connection counts.

For example, you might find that your current instance has 500 peak connections, 70% CPU usage on 8 cores, 32 GB memory used.

Estimate Resource Requirements Per Connection

MySQL allocates memory for each connection based on settings like thread_stack (default 256 KB), read_buffer_size, sort_buffer_size, etc. A rough estimate is 0.5-2 MB per connection, depending on query complexity and configuration.  So, for 500 connections: 500 × 1 MB (avg.) = 500 MB minimum, plus additional buffers.

The CPU requirements per connection really depends on query type. Simple OLTP queries might use a fraction of a core, while complex queries need more. Assume 0.01-0.05 ECPUs per active connection as a starting point.  For example:  If your peak is 500 connections: 500 × 0.02 ECPUs = 10 ECPUs minimum during peak.  The key word here is minimum – you will definitely want to refine this formula during the proof-of-concept.  

If you are using the HeatWave Cluster (for OLAP), remember that HeatWave doesn’t manage the connections directly, as the queries are routed through the DB System.  So, the DB System’s ECPU’s must handle the overhead of offloading the analytical queries to HeatWave.  If 20% of your 500 connections (100) are running analytical queries, make sure your DB System has enough ECPU’s to manage these connections while HeatWave handles the complex queries.

Choose A DB System Shape

Oracle Cloud’s ECPU-based shapes define compute and memory together. For ECPU’s, match or exceed your peak CPU needs. For 500 connections at 0.02 ECPUs each, start with 10-16 ECPUs.  As for memory, account for connection overhead, InnoDB buffer pool (for data/index caching), and OS overhead. For 500 connections + 32 GB baseline, aim for 40-64 GB (5-8 ECPUs at 8 GB/ECPU).  A shape with 16 ECPUs (128 GB memory) provides headroom for 500 connections and moderate OLTP load.

MySQL Configuration Variables

Configurations have a default set of user, system, or initialization variables. You can edit the user and initialization variables, but not the system variables.

HeatWave maintains several variables that configure its operation. Variables are set when the HeatWave Cluster is enabled. Most HeatWave variable settings are managed by OCI and cannot be modified directly.

Set max_connections in the DB System Configuration to be more than your peak load.  For example, if your peak is 500 connections, set this number to 600, for 500-peak plus a buffer.

There are memory-related variables to check.   The default value for innodb_buffer_pool_size in MySQL HeatWave is not explicitly fixed, as it is automatically configured based on the instance size and workload. MySQL HeatWave typically allocates around 70-80% of the instance’s memory to the InnoDB buffer pool to optimize performance.  Be sure to check the value to be sure it is at least 70% of the available DB System memory size, but not set above 80%.  I would not change the recommended values for other memory variables such as  thread_stack, read_buffer_size, etc. unless you have specific workloads which require a bit of tuning.

During the POC, monitor the threads_running (active queries) versus the threads_connected (total connections) to be sure you have enough CPU and you have sufficient memory.  You always want to add a buffer (20-50%) to your ECPU’s and memory for unexpected spikes in activity and for future growth.

Account for Additional Features

If you need High Availability (HA), choose a shape with enough resources to support three MySQL instances (primary + two secondaries). The HeatWave cluster attaches to the primary instance.

If you plan to query data in Object Storage via HeatWave Lakehouse, ensure your shape supports it (e.g., HeatWave.512GB or MySQL.HeatWave.VM.Standard for versions prior to MySQL 8.4.0-u3).

And don’t forget about growth – factor in future data growth and scalability. ECPU shapes allow flexibility to scale up later.

Test and Validate

You (obviously) will want to do a proof-of-concept (POC).  Provision a small test DB System and HeatWave cluster in Oracle Cloud with a subset of your data. Test your queries to verify performance.  If performance lags, scale up the DB System shape (more ECPUs) or add HeatWave nodes. You can change shapes post-migration with a restart.  For OLAP queries, be sure to modify each table to use the secondary storage engine RAPID, and then start the initial load from the DB System to the HeatWave Cluster.

You can also simulate a peak load by using tools like mysqlslap or sysbench.  During your POC or while stress-testing, check the CPU utilization (via the Oracle Cloud console), memory usage via the console, buffer pool stats via  SHOW ENGINE INNODB STATUS, connection errors via SHOW STATUS.  If the CPU is above 80% or if most of the memory has been consumed, scale up the shape to the next largest size and repeat the tests.

Plan the Migration

The database and application migration can be complicated, and deserves it’s own bog post – but not all applications are the same, so it would be difficult to write a post to cover all migration varieties. For most migrations, you will simply export your on-premise MySQL database using MySQL Shell.  Note – mysqldump is an option, but MySQL Shell provides a lot of advantages over mysqldump, which is single-threaded.  You can also have a near-zero downtime migration by using MySQL’s replication feature.  Import the Shell dump files into a provisioned DB System with an ECPU shape that supports HeatWave, add the HeatWave cluster and load your analytical tables into it for acceleration – then point your application to your new HeatWave instance.

Final Tips

During the POC, or even during the final migration, s tart with a conservative shape and scale up as needed – the MySQL DB System allows shape changes with minimal downtime. For datasets >10 TB or specific needs (e.g., MySQL.256 shape), contact Oracle Support or your sales representative due to limited hardware availability. Remember to review the latest Oracle HeatWave Service Guide for updated shape details, as options evolve.