MySQL – Heatwave [In Memory Query Accelerator]
Intro
This is the data era and most organizations are investing more in data
analytics. Data analytics needs more processing, memory, and
storage. To get a competitive advantage over other organizations invest
more to gain more performance, for analytics database performance is key. OLAP (Online Analytics processing) databases are more focused on
the fast retrieval of select operations. Database technologies are changing in
rapidly space, and new features are booming every quarter.
analytics. Data analytics needs more processing, memory, and
storage. To get a competitive advantage over other organizations invest
more to gain more performance, for analytics database performance is key. OLAP (Online Analytics processing) databases are more focused on
the fast retrieval of select operations. Database technologies are changing in
rapidly space, and new features are booming every quarter.
Oracle came up with a new invention for the MySQL database. MySQL HeatWave is
a fully managed database service powered by the HeatWave in-memory query
accelerator. It’s the only cloud service that combines transactions, real-time
analytics across data warehouses and data lakes, and machine learning in one
MySQL Database—without the complexity, latency, risks, and cost of ETL
duplication. It’s available on OCI, AWS, and Azure.
a fully managed database service powered by the HeatWave in-memory query
accelerator. It’s the only cloud service that combines transactions, real-time
analytics across data warehouses and data lakes, and machine learning in one
MySQL Database—without the complexity, latency, risks, and cost of ETL
duplication. It’s available on OCI, AWS, and Azure.
What is MySQL heatwave?
HeatWave uses a columnar in-memory representation that facilitates vectorized
processing. The use of columnar representation data is encoded and compressed
prior to being loaded in memory. This results in significant performance
improvements and a reduced memory footprint, which translates into reduced
costs for customers.
processing. The use of columnar representation data is encoded and compressed
prior to being loaded in memory. This results in significant performance
improvements and a reduced memory footprint, which translates into reduced
costs for customers.
Figure 1: MYSQL heatwave columnar representation.
The best way to test out MySQL heatwave features is via
Oracle Live Labs.
In this blog, I will elaborate on how you can test the query performance using
heatwave and non-heatwave.
heatwave and non-heatwave.
OCI Heatwave architecture :
Figure 2 : OCI MYSQL heatwave architecture
This this blog our objective is to cover the below-mentioned sections.
Objectives
- Provision MySQL Database System in Oracle Cloud
- Enable HeatWave Cluster in MySQL Database
- Load Sample data into HeatWave Cluster
- Execute SQL Query to see the difference in performance and execution time.
Setup Network :
As the first step create a new VPN called “heatwave-VPN“.
Figure 1: Create VPN
Add firewall rules :
Once the VPN creation is complete, let’s add firewall rules which are required
for the mysql server connection.
for the mysql server connection.
1. Navigation Menu > Networking > Virtual Cloud Networks
2.
Open heatwave-vcn
3. Click public subnet-heatwave-vcn
4. Click
Default Security List for heatwave-vcn
5. Click Add Ingress Rules page
under Ingress Rule
Figure 2: Add firewall rules.
Create Mysql DB and enable heatwave :
The next step is to create a MySQL environment with an enabling heatwave
feature. As this is for testing create a standalone server. To test the
heatwave feature make sure to enable heatwave configuration.
feature. As this is for testing create a standalone server. To test the
heatwave feature make sure to enable heatwave configuration.
Figure 3 : Enable heatwave service
We are not enabling backup as this is a test server.
Once the MySQL database system creation is complete, it will appear in green
color.
color.
We are going to have a connection via cloud shell, so create ssh keys using
“ssh-keygen -t rsa” in the cloud shell as mentioned below.
chanaka_ya@cloudshell:~ (ca-toronto-1)$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/chanaka_ya/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/chanaka_ya/.ssh/id_rsa.
Your public key has been saved in /home/chanaka_ya/.ssh/id_rsa.pub.
Create a compute instance to log in to MySQL database service.
Create a computer instance called heatwave-client in a public subnet and
copy the public key to the heatwave-client instance.
Now connect to the instance and install MySQL-shell, using MySQL-shell you
can access the MySQL server.
chanaka_ya@cloudshell:.ssh (ca-toronto-1)$ ssh -i id_rsa opc@ip_address_of_client_instance
FIPS mode initialized
The authenticity of host '40.233.82.142 (40.233.82.142)' can't be established.
ECDSA key fingerprint is SHA256:q17nqoqKwfYCde2eIKk0V+jFwAbqoThHA0HPF5KFgq8.
ECDSA key fingerprint is SHA1:G1Hdn/PE7nezwyWgAcwWpeU4UAU.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '40.233.82.142' (ECDSA) to the list of known hosts.
Activate the web console with: systemctl enable --now cockpit.socket
[opc@heatwave-client ~]$
[opc@heatwave-client ~]$
[opc@heatwave-client ~]$ sudo yum install mysql-shell -y
Ksplice for Oracle Linux 8 (x86_64) 39 MB/s | 3.9 MB 00:00
MySQL 8.0 for Oracle Linux 8 (x86_64) 34 MB/s | 3.0 MB 00:00
MySQL 8.0 Tools Community for Oracle Linux 8 (x86_64) 8.0 MB/s | 491 kB 00:00
MySQL 8.0 Connectors Community for Oracle Linux 8 (x86_64) 187 kB/s | 30 kB 00:00
Oracle Software for OCI users on Oracle Linux 8 (x86_64) 72 MB/s | 100 MB 00:01
Oracle Linux 8 BaseOS Latest (x86_64) 70 MB/s | 64 MB 00:00
Oracle Linux 8 Application Stream (x86_64) 69 MB/s | 49 MB 00:00
Oracle Linux 8 Addons (x86_64) 20 MB/s | 6.9 MB 00:00
Latest Unbreakable Enterprise Kernel Release 7 for Oracle Linux 8 (x86_64) 63 MB/s | 23 MB 00:00
Dependencies resolved.
=========================================================================================================================================================================================================================================================================================================================
Package Architecture Version Repository Size
=========================================================================================================================================================================================================================================================================================================================
Installing:
mysql-shell x86_64 8.0.33-1.el8 ol8_MySQL80_tools_community 27 M
Installing dependencies:
python39-libs x86_64 3.9.16-1.module+el8.8.0+21116+ee8c18cf.1 ol8_appstream 8.2 M
python39-pip-wheel noarch 20.2.4-7.module+el8.6.0+20625+ee813db2 ol8_appstream 1.1 M
python39-setuptools-wheel noarch 50.3.2-4.module+el8.5.0+20364+c7fe1181 ol8_appstream 497 k
Installing weak dependencies:
python39 x86_64 3.9.16-1.module+el8.8.0+21116+ee8c18cf.1 ol8_appstream 33 k
python39-pip noarch 20.2.4-7.module+el8.6.0+20625+ee813db2 ol8_appstream 1.9 M
python39-setuptools noarch 50.3.2-4.module+el8.5.0+20364+c7fe1181 ol8_appstream 871 k
Enabling module streams:
python39 3.9
Transaction Summary
=========================================================================================================================================================================================================================================================================================================================
Install 7 Packages
Total download size: 39 M
Installed size: 229 M
Downloading Packages:
(1/7): python39-3.9.16-1.module+el8.8.0+21116+ee8c18cf.1.x86_64.rpm 376 kB/s | 33 kB 00:00
(2/7): python39-pip-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch.rpm 9.7 MB/s | 1.9 MB 00:00
(3/7): python39-libs-3.9.16-1.module+el8.8.0+21116+ee8c18cf.1.x86_64.rpm 22 MB/s | 8.2 MB 00:00
(4/7): python39-setuptools-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch.rpm 5.1 MB/s | 871 kB 00:00
(5/7): mysql-shell-8.0.33-1.el8.x86_64.rpm 33 MB/s | 27 MB 00:00
(6/7): python39-setuptools-wheel-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch.rpm 1.9 MB/s | 497 kB 00:00
(7/7): python39-pip-wheel-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch.rpm 1.6 MB/s | 1.1 MB 00:00
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 40 MB/s | 39 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : python39-setuptools-wheel-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch 1/7
Installing : python39-pip-wheel-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch 2/7
Installing : python39-libs-3.9.16-1.module+el8.8.0+21116+ee8c18cf.1.x86_64 3/7
Installing : python39-3.9.16-1.module+el8.8.0+21116+ee8c18cf.1.x86_64 4/7
Running scriptlet: python39-3.9.16-1.module+el8.8.0+21116+ee8c18cf.1.x86_64 4/7
Installing : python39-setuptools-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch 5/7
Running scriptlet: python39-setuptools-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch 5/7
Installing : python39-pip-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch 6/7
Running scriptlet: python39-pip-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch 6/7
Installing : mysql-shell-8.0.33-1.el8.x86_64 7/7
Running scriptlet: mysql-shell-8.0.33-1.el8.x86_64 7/7
Verifying : mysql-shell-8.0.33-1.el8.x86_64 1/7
Verifying : python39-3.9.16-1.module+el8.8.0+21116+ee8c18cf.1.x86_64 2/7
Verifying : python39-libs-3.9.16-1.module+el8.8.0+21116+ee8c18cf.1.x86_64 3/7
Verifying : python39-pip-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch 4/7
Verifying : python39-pip-wheel-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch 5/7
Verifying : python39-setuptools-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch 6/7
Verifying : python39-setuptools-wheel-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch 7/7
Installed:
mysql-shell-8.0.33-1.el8.x86_64 python39-3.9.16-1.module+el8.8.0+21116+ee8c18cf.1.x86_64 python39-libs-3.9.16-1.module+el8.8.0+21116+ee8c18cf.1.x86_64 python39-pip-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch
python39-pip-wheel-20.2.4-7.module+el8.6.0+20625+ee813db2.noarch python39-setuptools-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch python39-setuptools-wheel-50.3.2-4.module+el8.5.0+20364+c7fe1181.noarch
Complete!
[opc@heatwave-client ~]$
Now let’s validate the connection to the MySQL server using the MySQL
client server.
mysqlsh -uadmin -p -h mysql-instance-ip --sql
Load data sample :
Now let’s load the sample db called mysql_customer_orders dump.
MySQL 10.0.1.192:33060+ ssl SQL > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mysql_audit |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.0008 sec)
MySQL 10.0.1.192:33060+ ssl SQL > js
Switching to JavaScript mode...
MySQL 10.0.1.192:33060+ ssl JS > util.loadDump("https://objectstorage.us-ashburn-1.oraclecloud.com/p/0pZRzTl1hFLchwAcornQVePE7eXxp1u6rjVVF3i7a5qN7HASVk4CtTQ9BK9y4xIG/n/mysqlpm/b/plf_mysql_customer_orders/o/mco_nocoupon_dump_05242023/", {progressFile: "progress.json", loadIndexes:false})
Loading DDL and Data from OCI prefix PAR=/p/secret/n/mysqlpm/b/plf_mysql_customer_orders/o/mco_nocoupon_dump_05242023/, prefix='mco_nocoupon_dump_05242023/' using 4 threads.
Opening dump...
Target is MySQL 8.0.34-u3-cloud (MySQL Database Service). Dump was produced from MySQL 8.0.33-u3-cloud
Fetching dump data from remote location...
Listing files - done
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
3 thds loading 100% (1.63 GB / 1.63 GB), 26.28 MB/s, 6 / 6 tables done
Executing common postamble SQL
44 chunks (37.22M rows, 1.63 GB) for 6 tables in 1 schemas were loaded in 1 min 6 sec (avg throughput 27.43 MB/s)
0 warnings were reported during the load.
Once the dump is loaded to the mysql server validate the databases. This shows a new database called “mysql_customer_orders”.
MySQL 10.0.1.192:33060+ ssl SQL > show databases;
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| mysql |
| mysql_audit |
| mysql_customer_orders |
| performance_schema |
| sys |
+-----------------------+
6 rows in set (0.0010 sec)
MySQL 10.0.1.192:33060+ ssl SQL >
Add HeatWave Cluster to the DB system
To test this out, let’s create a heat wave cluster. Navigate the MySQL database and click more-action to Add HeatWave cluster.
For this testing, we will create a heatwave cluster with 2 nodes. Each one consists of 1TB of memory.
Load to MySQL heatwave cluster
We can load data to the heatwave cluster using the “CALL sys.heatwave_load(JSON_ARRAY(‘mysql_customer_orders’), NULL);” command.
MySQL 10.0.1.192:33060+ ssl SQL > CALL sys.heatwave_load(JSON_ARRAY('mysql_customer_orders'), NULL);
+------------------------------------------+
| INITIALIZING HEATWAVE AUTO PARALLEL LOAD |
+------------------------------------------+
| Version: 2.20 |
| |
| Load Mode: normal |
| Load Policy: disable_unsupported_columns |
| Output Mode: normal |
| |
+------------------------------------------+
6 rows in set (19.4460 sec)
+-----------------------------------------------------------------------------------------+
| OFFLOAD ANALYSIS |
+-----------------------------------------------------------------------------------------+
| Verifying input schemas: 1 |
| User excluded items: 0 |
| |
| SCHEMA OFFLOADABLE OFFLOADABLE SUMMARY OF |
| NAME TABLES COLUMNS ISSUES |
| ------ ----------- ----------- ---------- |
| `mysql_customer_orders` 6 33 3 table(s) are not loadable |
| |
| Total offloadable schemas: 1 |
| |
+-----------------------------------------------------------------------------------------+
10 rows in set (19.4460 sec)
+-----------------------------------------------------------------------------------------------------------------------------+
| CAPACITY ESTIMATION |
+-----------------------------------------------------------------------------------------------------------------------------+
| Default encoding for string columns: VARLEN (unless specified in the schema) |
| Estimating memory footprint for 1 schema(s) |
| |
| TOTAL ESTIMATED ESTIMATED TOTAL DICTIONARY VARLEN ESTIMATED |
| SCHEMA OFFLOADABLE HEATWAVE NODE MYSQL NODE STRING ENCODED ENCODED LOAD |
| NAME TABLES FOOTPRINT FOOTPRINT COLUMNS COLUMNS COLUMNS TIME |
| ------ ----------- --------- --------- ------- ---------- ------- --------- |
| `mysql_customer_orders` 6 1.56 GiB 2.44 MiB 14 0 14 12.00 s |
| |
| Sufficient MySQL host memory available to load all tables. |
| Sufficient HeatWave cluster memory available to load all tables. |
| |
+-----------------------------------------------------------------------------------------------------------------------------+
12 rows in set (19.4460 sec)
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXECUTING LOAD |
+---------------------------------------------------------------------------------------------------------------------------------------+
| HeatWave Load script generated |
| Retrieve load script containing 12 generated DDL command(s) using the query below: |
| Deprecation Notice: "heatwave_load_report" will be deprecated, please switch to "heatwave_autopilot_report" |
| SELECT log->>"$.sql" AS "Load Script" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id; |
| |
| Adjusting load parallelism dynamically per internal/external table. |
| Using current parallelism of 32 thread(s) as maximum for internal tables. |
| |
| Using SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
| |
| Proceeding to load 6 table(s) into HeatWave. |
| |
| Applying changes will take approximately 11.52 s |
| |
+---------------------------------------------------------------------------------------------------------------------------------------+
14 rows in set (19.4460 sec)
+-----------------------------------------------------+
| LOADING TABLE |
+-----------------------------------------------------+
| TABLE (1 of 6): `mysql_customer_orders`.`customers` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table loaded successfully! |
| Total columns loaded: 3 |
| Table loaded using 1 thread(s) |
| Elapsed time: 588.54 ms |
| |
+-----------------------------------------------------+
8 rows in set (19.4460 sec)
+-------------------------------------------------------+
| LOADING TABLE |
+-------------------------------------------------------+
| TABLE (2 of 6): `mysql_customer_orders`.`order_items` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table loaded successfully! |
| Total columns loaded: 6 |
| Table loaded using 21 thread(s) |
| Elapsed time: 6.22 s |
| |
+-------------------------------------------------------+
8 rows in set (19.4460 sec)
+--------------------------------------------------+
| LOADING TABLE |
+--------------------------------------------------+
| TABLE (3 of 6): `mysql_customer_orders`.`orders` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table loaded successfully! |
| Total columns loaded: 5 |
| Table loaded using 32 thread(s) |
| Elapsed time: 12.14 s |
| |
+--------------------------------------------------+
8 rows in set (19.4460 sec)
+----------------------------------------------------+
| LOADING TABLE |
+----------------------------------------------------+
| TABLE (4 of 6): `mysql_customer_orders`.`products` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table loaded successfully! |
| Total columns loaded: 4 |
| Table loaded using 1 thread(s) |
| Elapsed time: 143.79 ms |
| |
+----------------------------------------------------+
8 rows in set (19.4460 sec)
+--------------------------------------------------+
| LOADING TABLE |
+--------------------------------------------------+
| TABLE (5 of 6): `mysql_customer_orders`.`stores` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table loaded successfully! |
| Total columns loaded: 11 |
| Table loaded using 1 thread(s) |
| Elapsed time: 176.37 ms |
| |
+--------------------------------------------------+
8 rows in set (19.4460 sec)
+-------------------------------------------------+
| LOADING TABLE |
+-------------------------------------------------+
| TABLE (6 of 6): `mysql_customer_orders`.`users` |
| Commands executed successfully: 2 of 2 |
| Warnings encountered: 0 |
| Table loaded successfully! |
| Total columns loaded: 4 |
| Table loaded using 1 thread(s) |
| Elapsed time: 88.20 ms |
| |
+-------------------------------------------------+
8 rows in set (19.4460 sec)
+-------------------------------------------------------------------------------+
| LOAD SUMMARY |
+-------------------------------------------------------------------------------+
| |
| SCHEMA TABLES TABLES COLUMNS LOAD |
| NAME LOADED FAILED LOADED DURATION |
| ------ ------ ------ ------- -------- |
| `mysql_customer_orders` 6 0 33 19.35 s |
| |
+-------------------------------------------------------------------------------+
6 rows in set (19.4460 sec)
Query OK, 0 rows affected (19.4460 sec)
MySQL 10.0.1.192:33060+ ssl SQL >
Execute Query :
Let’s execute two queries with the heatwave and without the heatwave engine. To fetch the result query took (0.0007 sec).
With Heatwave :
MySQL 10.0.1.192:33060+ ssl SQL > USE performance_schema;
Default schema set to `performance_schema`.
Fetching global names, object names from `performance_schema` for auto-completion... Press ^C to stop.
MySQL 10.0.1.192:33060+ ssl performance_schema SQL > SELECT NAME, LOAD_STATUS FROM rpd_tables,rpd_table_id WHERE rpd_tables.ID = rpd_table_id.ID;
+-----------------------------------+---------------------+
| NAME | LOAD_STATUS |
+-----------------------------------+---------------------+
| mysql_customer_orders.orders | AVAIL_RPDGSTABSTATE |
| mysql_customer_orders.products | AVAIL_RPDGSTABSTATE |
| mysql_customer_orders.order_items | AVAIL_RPDGSTABSTATE |
| mysql_customer_orders.customers | AVAIL_RPDGSTABSTATE |
| mysql_customer_orders.users | AVAIL_RPDGSTABSTATE |
| mysql_customer_orders.stores | AVAIL_RPDGSTABSTATE |
+-----------------------------------+---------------------+
6 rows in set (0.0007 sec)
MySQL 10.0.1.192:33060+ ssl performance_schema SQL >
Execute query
MySQL 10.0.1.192:33060+ ssl mysql_customer_orders SQL > select `o`.`ORDER_ID` AS `order_id`,`o`.`ORDER_DATETIME` AS `ORDER_DATETIME`,
-> `o`.`ORDER_STATUS` AS `order_status`, `c`.`CUSTOMER_ID` AS `customer_id`,
-> `c`.`EMAIL_ADDRESS` AS `email_address`,`c`.`FULL_NAME` AS `full_name`,
-> sum((`oi`.`QUANTITY` * `oi`.`UNIT_PRICE`)) AS `order_total`,
-> `p`.`PRODUCT_NAME` AS `product_name`,`oi`.`LINE_ITEM_ID` AS `LINE_ITEM_ID`,
-> `oi`.`QUANTITY` AS `QUANTITY`,`oi`.`UNIT_PRICE` AS `UNIT_PRICE`
-> from (((`orders` `o` join `order_items` `oi` on((`o`.`ORDER_ID` = `oi`.`ORDER_ID`)))
-> join `customers` `c` on((`o`.`CUSTOMER_ID` = `c`.`CUSTOMER_ID`)))
-> join `products` `p` on((`oi`.`PRODUCT_ID` = `p`.`PRODUCT_ID`)))
-> group by `o`.`ORDER_ID`,`o`.`ORDER_DATETIME`,`o`.`ORDER_STATUS`,`c`.`CUSTOMER_ID`
-> ,`c`.`EMAIL_ADDRESS` ,`c`.`FULL_NAME`,`p`.`PRODUCT_NAME`
-> ,`oi`.`LINE_ITEM_ID`,`oi`.`QUANTITY`,`oi`.`UNIT_PRICE` limit 10;
+------------+---------------------+--------------+-------------+-----------------------------+----------------+--------------------+------------------------+--------------+----------+------------+
| order_id | ORDER_DATETIME | order_status | customer_id | email_address | full_name | order_total | product_name | LINE_ITEM_ID | QUANTITY | UNIT_PRICE |
+------------+---------------------+--------------+-------------+-----------------------------+----------------+--------------------+------------------------+--------------+----------+------------+
| 66945236 | 2022-05-04 00:00:00 | COMPLETE | 81 | laurice.karl@internalmail | Laurice Karl | 142.95 | Women's Pyjamas (Grey) | 2 | 5 | 28.59 |
| 937221547 | 2022-11-29 00:00:00 | COMPLETE | 90 | ward.stepney@internalmail | Ward Stepney | 39.16 | Boy's Socks (Black) | 2 | 2 | 19.58 |
| 66965712 | 2022-08-23 00:00:00 | COMPLETE | 81 | laurice.karl@internalmail | Laurice Karl | 88.34 | Boy's Sweater (Green) | 1 | 2 | 44.17 |
| 2142210292 | 2022-12-02 00:00:00 | COMPLETE | 99 | luis.pothoven@internalmail | Luis Pothoven | 195.79999999999998 | Girl's Trousers (Red) | 1 | 5 | 39.16 |
| 803321349 | 2022-02-14 00:00:00 | COMPLETE | 89 | august.arouri@internalmail | August Arouri | 88.34 | Boy's Sweater (Green) | 3 | 2 | 44.17 |
| 401691328 | 2022-05-26 00:00:00 | COMPLETE | 86 | twila.coolbeth@internalmail | Twila Coolbeth | 29.400000000000002 | Boy's Sweater (Red) | 1 | 3 | 9.80 |
| 401678819 | 2022-12-08 00:00:00 | COMPLETE | 86 | twila.coolbeth@internalmail | Twila Coolbeth | 158.4 | Women's Coat (Black) | 2 | 5 | 31.68 |
| 28659 | 2022-04-01 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 138.2 | Men's Jeans (Grey) | 1 | 5 | 27.64 |
| 271110786 | 2022-01-12 00:00:00 | COMPLETE | 84 | lovie.ritacco@internalmail | Lovie Ritacco | 104.56 | Boy's Hoodie (Grey) | 1 | 4 | 26.14 |
| 1204991830 | 2022-09-25 00:00:00 | COMPLETE | 92 | luana.berends@internalmail | Luana Berends | 97.5 | Girl's Jeans (Grey) | 1 | 2 | 48.75 |
+------------+---------------------+--------------+-------------+-----------------------------+----------------+--------------------+------------------------+--------------+----------+------------+
10 rows in set (0.7336 sec)
Without Heatwave engine:
Now we are executing the same query without the heatwave engine, To fetch the result query took 15.8542 sec.
MySQL 10.0.1.192:33060+ ssl mysql_customer_orders SQL > SET SESSION use_secondary_engine=OFF;
Query OK, 0 rows affected (0.0005 sec)
MySQL 10.0.1.192:33060+ ssl mysql_customer_orders SQL > select `o`.`ORDER_ID` AS `order_id`,`o`.`ORDER_DATETIME` AS `ORDER_DATETIME`,
-> `o`.`ORDER_STATUS` AS `order_status`, `c`.`CUSTOMER_ID` AS `customer_id`,
-> `c`.`EMAIL_ADDRESS` AS `email_address`,`c`.`FULL_NAME` AS `full_name`,
-> sum((`oi`.`QUANTITY` * `oi`.`UNIT_PRICE`)) AS `order_total`,
-> `p`.`PRODUCT_NAME` AS `product_name`,`oi`.`LINE_ITEM_ID` AS `LINE_ITEM_ID`,
-> `oi`.`QUANTITY` AS `QUANTITY`,`oi`.`UNIT_PRICE` AS `UNIT_PRICE`
-> from (((`orders` `o` join `order_items` `oi` on((`o`.`ORDER_ID` = `oi`.`ORDER_ID`)))
-> join `customers` `c` on((`o`.`CUSTOMER_ID` = `c`.`CUSTOMER_ID`)))
-> join `products` `p` on((`oi`.`PRODUCT_ID` = `p`.`PRODUCT_ID`)))
-> group by `o`.`ORDER_ID`,`o`.`ORDER_DATETIME`,`o`.`ORDER_STATUS`,`c`.`CUSTOMER_ID`
-> ,`c`.`EMAIL_ADDRESS` ,`c`.`FULL_NAME`,`p`.`PRODUCT_NAME`
-> ,`oi`.`LINE_ITEM_ID`,`oi`.`QUANTITY`,`oi`.`UNIT_PRICE` limit 10;
+----------+---------------------+--------------+-------------+------------------------+-----------+-------------+------------------------+--------------+----------+------------+
| order_id | ORDER_DATETIME | order_status | customer_id | email_address | full_name | order_total | product_name | LINE_ITEM_ID | QUANTITY | UNIT_PRICE |
+----------+---------------------+--------------+-------------+------------------------+-----------+-------------+------------------------+--------------+----------+------------+
| 1 | 2022-02-04 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 93.28 | Boy's Pyjamas (Grey) | 1 | 4 | 23.32 |
| 1 | 2022-02-04 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 20.96 | Boy's Shorts (Blue) | 2 | 2 | 10.48 |
| 1 | 2022-02-04 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 59.1 | Boy's Shirt (White) | 3 | 2 | 29.55 |
| 2 | 2022-02-08 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 30.33 | Women's Dress (Black) | 1 | 3 | 10.11 |
| 2 | 2022-02-08 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 185 | Women's Jacket (Blue) | 2 | 5 | 37.00 |
| 3 | 2022-02-09 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 50.55 | Women's Dress (Black) | 1 | 5 | 10.11 |
| 4 | 2022-02-10 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 76.68 | Girl's Shorts (Green) | 1 | 2 | 38.34 |
| 4 | 2022-02-10 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 44 | Girl's Pyjamas (Red) | 2 | 4 | 11.00 |
| 4 | 2022-02-10 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 156.64 | Girl's Trousers (Red) | 3 | 4 | 39.16 |
| 5 | 2022-02-11 00:00:00 | COMPLETE | 56 | tamika.hu@internalmail | Tamika Hu | 34.64 | Girl's Pyjamas (Black) | 1 | 4 | 8.66 |
+----------+---------------------+--------------+-------------+------------------------+-----------+-------------+------------------------+--------------+----------+------------+
10 rows in set (15.8542 sec)
Conclusion
MYSQL heatwave is a game changer for OLAP environments. Processing large amounts of data within seconds helps business reports and machine learning processes. Also, This technology can be used to gain the advantage of machine learning. Even applications can offload reports for heatwave clusters.