GoldenGate Microservices on OCI
Introduction: The GoldenGate Microservices architecture is a modern data integration solution designed for the microservices era. It provides a ... Read More
Découvrez pourquoi Eclipsys a été nommée 2023 Best Workplaces in Technology, Great Place to Work® Canada et Canada's Top 100 SME !
En savoir plus !MySQL HeatWave is an in-memory query acceleration technology introduced by Oracle for the MySQL Database Service on Oracle Cloud Infrastructure. It allows users to run MySQL queries on large sets of data in real-time by utilizing the massively parallel processing capabilities of modern CPUs and GPUs.
HeatWave works by automatically synchronizing data between the MySQL database and a separate in-memory cluster. The data is then cached in memory, which allows for faster query performance as it eliminates the need for disk I/O operations.
With HeatWave, MySQL users can take advantage of a highly scalable, fault-tolerant, and secure architecture that can handle complex workloads, such as data analytics, machine learning, and business intelligence. HeatWave also provides features like automatic failover, backup and restore, and real-time analytics, making it an ideal solution for businesses that require high-speed data processing capabilities.
The architecture of MySQL HeatWave involves the following components:
When a user runs a query using the MySQL Shell, the query is sent to the HeatWave Accelerated Engine, which executes the query on the in-memory data. The results are then returned to the user through the MySQL Shell. The in-memory data is automatically synchronized with the MySQL Database Service, ensuring that the data is always up-to-date.
Overall, the architecture of MySQL HeatWave is designed to provide high-speed query performance and scalability, making it an ideal solution for businesses that require real-time analytics and data processing capabilities.
Now, let us perform a small test by creating MySQL DB in OCI, enabling Heatwave, and seeing the query performance difference.
Login to OCI Tenancy, create your compartment for this POC, and create your (Virtual Cloud Network) VCN and appropriate security rules. Also, create a compute instance in the public subnet for connecting to DB in the Private subnet and generate private and public keys.
1. Click on the databases menu and click on DB Systems under MySQL
2. Click on “Create DB System”
3. Select the Compartment, add the DB name, and select Heatwave from the DB options
4. Enter Username and Password for MySQL DB
5. Select the VCN and the Private Subnet
6. Enable Automatic Backup
7. Ensure we have 3306 and 33060 open in the Private Subnet Security Rules
8. Note down the Endpoint Private IP, which will be used to connect to MySQL DB from Compute Instance
9. Enable HeatWave Cluster in MySQL Database. Click on “More Actions” and Select “Add Heatwave Cluster”
10. Under configure Heatwave Cluster, click on “Estimate Node” or you can manually enter the number of nodes required
11. Click on generate an estimate, this will recommend the number of nodes required to process the schema data
12. Select the schema which you need to load and copy the command which can be used to manually load schema data to the HeatWave Cluster memory
13. Now we have HeatWave enabled with one node and 512 GB of memory
Under resources click on work requests to see the progress of the HeatWave cluster, also click on Heatwave to see if the state is active or not.
1. ssh to your compute instance, using your private key.
Rajesh.Madhavarao@Eclipsyss-MacBook-Pro .ssh % ssh -i ./oci-mysql-key opc@140.238.144.160 Activate the web console with: systemctl enable --now cockpit.socket Last login: Fri Apr 28 09:05:25 2023 from 24.215.68.152 [opc@mysql-heatwave ~]$ [opc@mysql-heatwave ~]$
Note: Follow the below steps to download the test schema dump to compute the instance and from there we will upload it to MySQL DB
[opc@mysql-heatwave ~]$ wget https://downloads.mysql.com/docs/airport-db.tar.gz --2023-05-03 14:31:15-- https://downloads.mysql.com/docs/airport-db.tar.gz Resolving downloads.mysql.com (downloads.mysql.com)... 23.66.195.54, 2600:140a:0:69b::2e31, 2600:140a:0:682::2e31 Connecting to downloads.mysql.com (downloads.mysql.com)|23.66.195.54|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 655687673 (625M) [application/x-gzip] Saving to: ‘airport-db.tar.gz’ airport-db.tar.gz 100%[===========================================================================================================>] 625.31M 32.1MB/s in 20s 2023-05-03 14:31:36 (30.6 MB/s) - ‘airport-db.tar.gz’ saved [655687673/655687673] [opc@mysql-heatwave ~]$ tar -xf airport-db.tar.gz
2. Use MySQL Shell to connect to MySQL DB and load the dump file.
[opc@mysql-heatwave ~]$ mysqlsh root@10.0.1.124 MySQL Shell 8.0.33 Copyright (c) 2016, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type '\help' or '\?' for help; '\quit' to exit. Creating a session to 'root@10.0.1.124' Fetching schema names for auto-completion... Press ^C to stop. Your MySQL connection id is 22 (X protocol) Server version: 8.0.33-u2-cloud MySQL Enterprise - Cloud No default schema selected; type \use <schema> to set one. MySQL 10.0.1.124:33060+ ssl JS > MySQL 10.0.1.124:33060+ ssl JS > util.loadDump("airport-db", {threads: 16, loadIndexes: "false", ignoreVersion: true,resetProgress: true}) Loading DDL and Data from 'airport-db' using 16 threads. Opening dump... NOTE: Dump format has version 1.0.2 and was created by an older version of MySQL Shell. If you experience problems loading it, please recreate the dump using the current version of MySQL Shell and try again. Target is MySQL 8.0.33-u2-cloud (MySQL Database Service). Dump was produced from MySQL 8.0.26-cloud Scanning metadata - done Checking for pre-existing objects... Executing common preamble SQL Executing DDL - done Executing view DDL - done Starting data load 8 thds loading | 100% (2.03 GB / 2.03 GB), 7.03 MB/s, 14 / 14 tables done Executing common postamble SQL 39 chunks (59.50M rows, 2.03 GB) for 14 tables in 1 schemas were loaded in 2 min 17 sec (avg throughput 15.70 MB/s) 0 warnings were reported during the load.
Note: MySQL Shell can also be downloaded by visiting this website: https://dev.mysql.com/downloads/shell/
3. Switch to SQL and verify the loaded schema and tables.
MySQL 10.0.1.124:33060+ ssl JS > \sql Switching to SQL mode... Commands end with ; Fetching global names for auto-completion... Press ^C to stop. MySQL 10.0.1.124:33060+ ssl SQL > MySQL 10.0.1.124:33060+ ssl SQL > show schemas; +--------------------+ | Database | +--------------------+ | airportdb | | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 6 rows in set (0.0010 sec) MySQL 10.0.1.124:33060+ ssl SQL > use airportdb; Default schema set to `airportdb`. Fetching global names, object names from `airportdb` for auto-completion... Press ^C to stop. MySQL 10.0.1.124:33060+ ssl airportdb SQL > show tables; +---------------------+ | Tables_in_airportdb | +---------------------+ | airline | | airplane | | airplane_type | | airport | | airport_geo | | airport_reachable | | booking | | employee | | flight | | flight_log | | flightschedule | | passenger | | passengerdetails | | weatherdata | +---------------------+ 14 rows in set (0.0012 sec)
4. Load data to Heatwave cluster:
MySQL 10.0.1.124:33060+ ssl airportdb SQL > CALL sys.heatwave_load(JSON_ARRAY("airportdb"),NULL); +-------------------------------------------------------------------------------+ | LOAD SUMMARY | +-------------------------------------------------------------------------------+ | | | SCHEMA TABLES TABLES COLUMNS LOAD | | NAME LOADED FAILED LOADED DURATION | | ------ ------ ------ ------- -------- | | `airportdb` 14 0 105 29.35 s | | | +-------------------------------------------------------------------------------+ 6 rows in set (29.5453 sec)
5. Let us query few tables and see the performance difference:
MySQL 10.0.1.124:33060+ ssl airportdb SQL > explain select count(flight_id) from booking where price>300\G; *************************** 1. row *************************** id: 1 select_type: NONE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Using secondary engine RAPID. Use EXPLAIN FORMAT=TREE to show the plan. 1 row in set, 1 warning (0.0119 sec) Note (code 1003): /* select#1 */ select count(`airportdb`.`booking`.`flight_id`) AS `count(flight_id)` from `airportdb`.`booking` where (`airportdb`.`booking`.`price` > 300.00) ERROR: 1065: Query was empty MySQL 10.0.1.124:33060+ ssl airportdb SQL > select count(flight_id) from booking where price>300; +------------------+ | count(flight_id) | +------------------+ | 21823795 | +------------------+ 1 row in set (0.0307 sec)
As we can see from the explained plan the query is using a secondary HeatWave Engine since we have Heatwave enabled and the time taken is 0.0307 seconds.
Now let us test the same query disabling the HeatWave Engine.
MySQL 10.0.1.124:33060+ ssl airportdb SQL > SET SESSION use_secondary_engine=off; Query OK, 0 rows affected (0.0005 sec) MySQL 10.0.1.124:33060+ ssl airportdb SQL > explain select count(flight_id) from booking where price>300\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: booking partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 52411514 filtered: 33.32999801635742 Extra: Using where 1 row in set, 1 warning (0.0022 sec) Note (code 1003): /* select#1 */ select count(`airportdb`.`booking`.`flight_id`) AS `count(flight_id)` from `airportdb`.`booking` where (`airportdb`.`booking`.`price` > 300.00) ERROR: 1065: Query was empty MySQL 10.0.1.124:33060+ ssl airportdb SQL > select count(flight_id) from booking where price>300; +------------------+ | count(flight_id) | +------------------+ | 21823795 | +------------------+ 1 row in set (11.3129 sec)
Here we have disabled the HeatWave engine, we can see that the explained plan is using the InnoDB engine and the query is taking 11.3129 seconds, which is significantly time consuming than the query performance with the Heatwave engine.
Here are some of the ways that MySQL HeatWave helps improve query performance:
Overall, MySQL HeatWave helps to improve query performance by leveraging in-memory processing, distributed computing, columnar storage, and automatic data management. This enables faster query processing times and more efficient use of resources, leading to improved application performance and user satisfaction.
https://dev.mysql.com/doc/heatwave/en/mys-hw-architecture.html
Introduction: The GoldenGate Microservices architecture is a modern data integration solution designed for the microservices era. It provides a ... Read More