In this blog, I have covered an Overview of HeatWave, a MySQL Database Service, which is part of the 1Z0-1093 Certification. We cover this in detail in our Oracle DBA to Cloud DBA training Program [To Register for FREE Class, find the link at bottom]
If you are using MySQL Database & you have too long-running select queries it is probably, because of a lack of relevant indexes, an uncertain schema that leads to poor queries, or insufficient hardware.
It means, sometimes even if you doing it right, the query execution time could be too long regarding what the application or your users expect. It is often true for reporting, real-time analytics, or Business intelligence (BI) queries.
To improve the performance, Oracle developed HeatWave, which allows you to simply run high-performance analytics against your database.
Topics that we’ll cover in this blog:
- Introduction to HeatWave
- HeatWave Architecture
- Features of HeatWave
- Adding a HeatWave Cluster to a Database System
- Managing HeatWave Cluster
Introduction to Oracle MySQL HeatWave Service
HeatWave is a new integrated, high-performance, in-memory query accelerator for MySQL Database Service that accelerates MySQL performance by orders of magnitude for analytics and transactional queries.
It scales out to thousands of cores, is 1100X faster than Amazon Aurora, and 2.7X faster than Amazon Redshift at one-third the value.
MySQL Database Service, with HeatWave, is that the only service that permits database admins and app developers to run OLTP and OLAP workloads directly from their database. The service is optimized for and exclusively available in Oracle Cloud Infrastructure (OCI).
Key points:
- HeatWave has a brand new engine designed for the Cloud, integrated with MySQL, optimize for OCI.
- New distributed algorithms were invented at Oracle.
- Massively partitioned & parallel architecture for High Scalability.
- Optimize for lower-cost AMD shapes & standard networking.
- Integrated with object store to provide best storage performance and cost.
- Machine learning-based automation to adapt to varying workloads.
- The result is a service that provides the fastest performance for analytics at a lower cost.
Image Source: Oracle Cloud
Image Source: Oracle Cloud
HeatWave Architecture
A HeatWave cluster includes a MySQL DB System node and two or more HeatWave nodes. The DB System node features a HeatWave plugin that’s responsible for cluster management, loading data into the HeatWave cluster, query data using HeatWave Cluster, and returning query results to the MySQL DB System. HeatWave nodes store data in memory and process analytics queries.
The number of HeatWave nodes required depends on the dimensions of your data and therefore the amount of compression that’s achieved when loading the data into the HeatWave cluster.
Features of HeatWave
- New integrated, high-performance in-memory query accelerator – HeatWave is a new, in-memory query accelerator developed for MySQL Database Service. HeatWave uses machine learning to intelligently automate operations such as provisioning, increasing DBA productivity, and reducing costs.
- MySQL delivers real-time analytic – HeatWave accelerates MySQL queries by 400X using massively parallel processing to provide real-time analytics. HeatWave is 2.7X faster than Amazon Redshift using in-memory analytics and it is 1100X faster than Amazon Aurora. it can scale up to thousands of OCI cores.
- Single database for OLTP and OLAP – Oracle MySQL Database Service, with its integrated HeatWave, is the only service that enables database admins and app developers to run OLTP and OLAP workloads directly from their MySQL database.
How to Add HeatWave Cluster into Database System: Step by Step
Before adding HeatWave Cluster into the Database System you must Ensure the DB System was created using MySQL.HeatWave.VM.Standard.E3 shape.
1) Register & Log in to the Oracle free Cloud account.
Note: First, you should have an Oracle Free Cloud Account and if you don’t have one, then you can register FREE using my step-by-step video & guide that I covered in episode 30. You can get it by visiting here.
2. Add your HeatWave cluster in one of the following ways:
- Select the DB System and choose Add HeatWave Cluster from the Actions icon (three dots) on the same line as your DB System.
- Open the DB System and select HeatWave Cluster from the Resources list. On the HeatWave Cluster Information frame, select Add HeatWave Cluster.
-
- Shape: The shape for the HeatWave nodes. Shapes are resource templates, defining the number of OCPUs, RAM, and so on. Click Change Shape to select a shape for your HeatWave nodes. You must use the same shape as that used by the associated DB System.
- Node Count: The number of HeatWave nodes to create. Optionally, click Estimate Node Count to generate a node count estimate to determine the number of nodes required based on the shape you selected and the size of your data.
4.Click Add HeatWave Cluster to create the HeatWave cluster.
Managing HeatWave Cluster
1.How to load Data into the HeatWave Cluster
- Before loading data into the HeatWave cluster, the data/tables have to be prepared.
- Preparing tables involves:
- Modifying table definitions to exclude certain columns, define string column encodings, add data placement keys.
- Specify HeatWave (RAPID) as the secondary engine for the table (Note that InnoDB is the primary engine).
- Loading a table into a HeatWave cluster requires executing an ALTER TABLE operation with the SECONDARY_LOAD keyword.
- mysql> ALTER TABLE sales SECONDARY_UNLOAD;
2. How to query data using the HeatWave cluster
- If a query accesses a table that is not loaded, the query is not offloaded to the HeatWave cluster for processing
- Queries that meet certain prerequisites are automatically offloaded from the MySQL DB System to the HeatWave cluster for accelerated processing
- Results are returned to the MySQL DB System node and to the MySQL client or application that issued the query
- When a table is loaded, data is sliced horizontally and distributed among HeatWave nodes
- After a table is loaded, changes to a table’s data on the MySQL DB System node are automatically propagated to the HeatWave nodes
- No user action is required to keep data synchronized
Reference links:
- MySQL Database as a Service in Oracle Cloud
- Oracle Cloud Database Services Specialist 2021 Certification[1Z0-1093-21]
- Databases In Oracle Cloud (OCI)
- 18 Hands-on labs for Oracle DBA to Cloud DBA
Next Task For You
If you want to upgrade your career from an Oracle DBA to Oracle Cloud DBA, and wants to clear Oracle Cloud Database Service Specialist[1Z0-1093-21] & Oracle Cloud Autonomous Database Certification[1Z0-931-21] with 18 Hands-On labs, then register for a FREE class, and don’t miss an opportunity to gain a plethora of insights on becoming a certified Oracle Cloud DBA.
Leave a Reply