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.
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 MySQLdatabase to support OLTP and OLAP;
memoryin a partitioned manner;
No need to make any changes.
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.
The following content is taken from Oracle’s official website at //m.sbmmt.com/link/4228bfbd579799d63cb20810ef5c04d1
A MySQL database meets both the needs of OLTP and OLAP
High-performance in-memory query accelerator
##In-database Machine Learning
MySQL Autopilot
TPC-H performance is better than similar products
Always maintains a stable high level during peak hours Performance, lower costs and no downtime
Developed, managed, and supported by the MySQL Engineering team
Asymmetric encryption with key generation and digital signatures
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".
For MySQL HeatWave data, you can load it in the following three ways:
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 thatautomatically 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.
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 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 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.
Thishybrid 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.
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.
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.
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.
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.
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/)
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!