Let's talk about MySQL HeatWave in one article

This article brings you relevant knowledge about MySQL, which mainly introduces the relevant content about MySQL HeatWave. MySQL HeatWave is a MySQL cloud service with built-in high-performance memory query accelerator; with the help of this service, we do not need to Any changes to the current application can improve the MySQL performance of mixed workloads by several orders of magnitude; let's take a look at it, I hope it will be helpful to everyone.

#MySQL, as the most popular database in the world, has been dominant in the trading scene for many years. At the end of 2020, OCI (Oracle Cloud Infrastructure) launched a black technology plug-in, which makes up for MySQL's shortcomings in analysis scenarios. Oracle officials claim that it is 1,400 times faster than Aurora, 6.5 times faster than Redshift, and can be divided into two parts. One cost-effective way to do this is MySQL HeatWave.

Introduction to MySQL HeatWave

MySQL HeatWave is a MySQL cloud service with a built-in high-performance in-memory query accelerator. With this service, we can improve MySQL performance for mixed workloads by orders of magnitude without making any changes to our current applications.

Compared with traditional analysis scenarios, MySQL HeatWave eliminates the need for users to use a separate analysis database, separate machine learning (ML) tools, and extract, transform, and load (ETL) replication. Meanwhile, with MySQL HeatWave Machine Learning, developers and data analysts can build, train, deploy, and interpret machine learning models in MySQL HeatWave without having to migrate data to a separate machine learning service.

MySQL HeatWave is currently available on OCI (Oracle Cloud Infrastructure), AWS (Amazon Web Services) and Microsoft Azure.

MySQL HeatWave can be attached to MDS (MySQL Database Service) to support analytical queries without being exposed to the application. The database of MySQL HeatWave is stored in memory in the form of column storage.

To simply understand MySQL HeatWave, first understand the following three contents:

• Use the same MySQL database to support OLTP and OLAP;

• Data is stored in

  memory in a partitioned manner;

• Application

  No need to make any changes .

MySQL HeatWave technical architecture

Overall architecture

The architecture of MySQL HeatWave is shown in the figure below , it exists in the entire MySQL database system in the form of a plug-in. It does not directly face the application. It can be understood that MySQL HeatWave is hung under MDS, and users do not need to modify the original data access method.

The MySQL HeatWave plug-in corresponds to several MySQL HeatWave Nodes. MySQL HeatWave data is stored in memory as column storage, and its persistent data is stored in object storage, which can be quickly restored after Node failure.

Column Storage

HeatWave data is stored in memory in column storage mode, which facilitates vectorization processing. At the same time, the data will be encoded and compressed before being loaded into the memory, which can improve performance. and reduce memory footprint, thereby reducing costs for customers.

• Do horizontal partitioning based on row storage data. Based on horizontal partitioning, queries can be executed in parallel at the node level to speed up scan, join, and group-by. , aggr and top-k operators, while the partition planning is adapted to the underlying RAPID customized hardware.

• The data within the partition is organized into columnar storage according to the schema definition to introduce vectorization execution. The unit of each vectorization calculation is a 16KiB vector, and each column corresponds to the vector combination of rows. Together they become chunks, and each partition has multiple chunks.

• In order to adapt to DMS, the vector is divided into multiple tiles, and each 64 lines forms a tile as the minimum unit of data transmission.

• In order to reduce memory usage, all stored data will be encoded or compressed.

MySQL HeatWave function

The following content is taken from Oracle’s official website at https://www.php.cn/link/4228bfbd579799d63cb20810ef5c04d1

• A MySQL database meets both the needs of OLTP and OLAP

  • No dependence on ETL
  • Provides real-time analysis
  • Enhanced security
  • No need to modify the application
  • Supports BI and data visualization tools supported by MySQL database
  • Can be used in public clouds and user data centers

• High-performance in-memory query accelerator

  • Designed with large-scale expansion and high-performance architecture
  • Optimized for the cloud
  • Optimized for high transaction volumes and connections

• ##In-database Machine Learning

  No additional machine learning services required
  • Leverage machine learning lifecycle automation to save time and reduce workload
  • Interpretable machine learning models

• MySQL Autopilot

  Automatic Configuration
  • Automatic Thread Pool
  • Automatic Sharding Prediction
  Automatic coding
  • Automatic query plan optimization
  • Automatic data placement
• ##MySQL Lake Warehouse Integration (beta)
  TPC-H performance is better than similar products
  • Quickly analyze all data
  • Scalable management and processing data architecture
  • Machine Learning drives automatic optimization to improve performance and save time
• Real-time elasticity
  Always maintains a stable high level during peak hours Performance, lower costs and no downtime
  • Avoid over-provisioning instances
• Fully managed database service
  Developed, managed, and supported by the MySQL Engineering team
  • MySQL HeatWave Interactive Console: Manage resources, run queries, and monitor performance
• Advanced Security
  Asymmetric encryption with key generation and digital signatures
  • Data desensitization
  • SQL Whitelist

MySQL HeatWave working principle

• RAPID engine supports related functions in statements;

• RAPID engine execution time is estimated to be less than InnoDB's execution time.

When the above two conditions are met at the same time, the RAPID engine, which is MySQL HeatWave, will handle the relevant business requests.

After enabling the MySQL HeatWave plug-in, MDS will use two conditions to determine whether the request goes through the RAPID engine for the received request. The engine used by MySQL HeatWave is RAPID. In the development stage, the name of MySQL HeatWave is It's "RAPID".

MySQL HeatWave data loading

Loading method

For MySQL HeatWave data, you can load it in the following three ways:

1. Manually load data, loading one table at a time;
2. Load data through automatic parallelization, which can be executed in parallel through Autopilot, which is more efficient;
3. Passed The console of MySQL HeatWave uses visual operations to complete data loading. This method is currently only available on AWS. Yes, it is true that only AWS supports the MySQL HeatWave console, and AWS is one step ahead of OCI.

The initial data loading may take a while. After completing the data loading, MySQL HeatWave will automatically remain consistent with the InnoDB data. What is worth paying attention to here is that automatically synchronizes changes. The data mode is asynchronous, and users may have to accept a data delay of up to 200ms, which means that data changes on MDS will not wait for feedback from MySQL HeatWave.

Synchronization method

MDS will synchronize data according to the following strategy:

• Every 200 ms;

• When the change transmission buffer reaches 64MB;

• In MDS, when the data changed by DML needs to be read by subsequent HeatWave query.

MySQL HeatWave deployment method

Public cloud

MySQL HeatWave can support OCI (Oracle Cloud Infrastructure), AWS (Amazon Web Services) and Microsoft Azure.

The number of HeatWave nodes required depends on data size, OCI and Azure support up to 64 nodes. On Amazon Web Services (AWS), a HeatWave cluster supports up to 128 nodes.

Hybrid deployment

Hybrid deployment refers to the way of deploying OLTP locally and deploying OLAP in the cloud. In this hybrid deployment, customers You can use MySQL Replication to replicate local MySQL data to OCI or AWS's MySQL HeatWave without the need for ETL to meet analytical business needs.

This hybrid deployment method needs to consider the data delay, which has been introduced in "Data Loading". Data is transmitted asynchronously between InnoDB and HeatWave. In addition to the network delay, it is necessary to Consider the real-time nature of data. It is understood that there is currently no MySQL HeatWave in China.

Local deployment

OCI supports deployment in the user's data center, which can meet compliance requirements and allow data to reside in the user's data center of data centers. This kind of deployment method has the following characteristics:

• It has an independent OCI cloud area and is hosted by Oracle;

• Meets the requirement that data resides in user data The needs of the center;

• meet the needs of low latency.

MySQL HeatWave cost-effectiveness

Performance comparison between MySQL HeatWave and Amazon Redshift "fastest instance" , after geometric mean calculation of 19 TPC-H test results, MySQL HeatWave is 2.7 times faster than Amazon Redshift and the cost is only one-third of Amazon Redshift.

Comparing the performance of MySQL HeatWave and Amazon Redshift "low-cost instances", MySQL HeatWave is more than 17 times ahead of Amazon Redshift in performance, and the investment cost is the same.

Judging from the official price/performance data, MySQL HeatWave is the most cost-effective compared to several other products in the picture.

MySQL HeatWave cost

In the Oracle public welfare class, we can learn about the approximate cost of using MySQL HeatWave. For this We only need to pay attention to the lower part of the figure. For an environment with 2T data volume, the monthly cost is approximately US$1,260.

This includes MDS fees, MDS storage fees and HeatWave fees.

MySQL HeatWave Multi-Cloud Differences

OCI and AWS

HeatWave works better on OCI and The difference between the Roadmaps of the two AWS clouds is quite interesting. As mentioned earlier, visual data loading can only be completed through AWS. Not only this capability, but also from the following figure, AWS is better than OCI in terms of user experience. .

(https://www.oracle.com/mysql/#roadmap)

When you need to use the console in OCI, it will jump to AWS.

Azure

For Azure users, the MySQL HeatWave service can still be used, which is MySQL HeatWave connected to OCI through Azure VNET. In other words, the OCI environment is actually used.

The purpose is to provide native user experience for Azure users, and the private interconnection method controls the network delay within 2ms.

(https://www.oracle.com/cloud/azure/oracle-database-for-azure/)

Summary

MySQL HeatWave can support use on OCI (Oracle Cloud Infrastructure), AWS (Amazon Web Services) and Microsoft Azure, and also supports the deployment of OCI to user data centers.

After enabling the MySQL HeatWave plug-in, users can use a MySQL service to meet business needs in TP and AP without modifying the business. Data synchronization is automatically completed through internal processes, without the need to maintain ETL separately, and the architecture can be kept simple. The capabilities of autonomous driving (AI) and integrated lake and warehouse give users more expectations.

MySQL HeatWave complements MySQL's ability to analyze scenarios and is of great significance to small and medium-sized enterprises.

There are two deficiencies that deserve users' attention: InnoDB's storage (expansion limitations) and data consistency issues.

Expansion limitations: MySQL HeatWave can provide expansion capabilities, but the storage problem of MySQL InnoDB has not been essentially solved. InnoDB still faces major challenges in the face of massive data.

Data consistency: For scenarios with high data consistency requirements, the delay issue from InnoDB to HeatWave (asynchronous transmission) needs to be considered.

Recommended learning: mysql video tutorial

The above is the detailed content of Let's talk about MySQL HeatWave in one article. For more information, please follow other related articles on the PHP Chinese website!