Discover MySQL HeatWave Database Service in Oracle Cloud Infrastructure

In the era of big data and real-time analytics, businesses require powerful database solutions that can handle massive workloads while delivering high-performance query processing. This session aims to introduce participants to the MySQL HeatWave Database Service, a cutting-edge technology offered in Oracle Cloud Infrastructure (OCI) that addresses these requirements.

During the session, attendees will gain a comprehensive understanding of the MySQL HeatWave Database Service and its capabilities for OLTP, OLAP, Machine Learning, and more.
The discussion will cover the fundamental concepts and architecture underlying HeatWave, highlighting its ability to provide highly scalable and efficient MySQL databases for any workload. Participants will learn how to leverage this service to accelerate query performance, significantly reducing response times for complex analytical queries.

This session was delivered at Contech 2024 by RoOUG

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL CONTECH by RoOUG - May 2024 Discover MySQL HeatWave Database Service in OCI
2. Who am I ? about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. 2
3. • @lefred • MySQL Evangelist • using MySQL since version 3.20 • devops believer • likes � � • living in � � � • h�ps://lefred.be • h�ps://github.com/lefred Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 3
4. MySQL HeatWave Database Service MySQL DBaaS in Oracle Cloud Infrastructure Copyright @ 2024 Oracle and/or its affiliates. 4
5. Copyright @ 2024 Oracle and/or its affiliates. 5
6. Terminology • OCI: Oracle Cloud Infrastructure • MySQL HeatWave: ◦ MySQL HeatWave Database Service (MHS) ◦ MySQL Database Service (MDS) • MySQL HeatWave Cluster: ◦ OLTP and OLAP query accelerator ◦ RAPID secondary engine Copyright @ 2024 Oracle and/or its affiliates. 6
7. What is MySQL HeatWave Database Service ? Copyright @ 2024 Oracle and/or its affiliates. 7
8. MySQL HeatWave Database Service MySQL HeatWave Database Service is a fully managed Oracle Cloud Infrastructure native service, developed, managed, and supported by the MySQL team in Oracle. Copyright @ 2024 Oracle and/or its affiliates. 8
9. MySQL HeatWave Database Service MySQL HeatWave Database Service is a fully managed Oracle Cloud Infrastructure native service, developed, managed, and supported by the MySQL team in Oracle. MySQL HeatWave Service is available on Oracle Cloud Infrastructure, Amazon Web Service, and Oracle Database Service in Azure (ODSA). Copyright @ 2024 Oracle and/or its affiliates. 8
10. MySQL HeatWave Database Service MySQL HeatWave Database Service is a fully managed Oracle Cloud Infrastructure native service, developed, managed, and supported by the MySQL team in Oracle. MySQL HeatWave Service is available on Oracle Cloud Infrastructure, Amazon Web Service, and Oracle Database Service in Azure (ODSA). Powered by the integrated HeatWave in-memory query accelerator, it is the only cloud- native database service that combines transactions, analytics, and machine learning services into MySQL, delivering real-time, secure analytics without the complexity, latency, and cost of ETL duplication. Copyright @ 2024 Oracle and/or its affiliates. 8
11. MySQL HeatWave Database Service - Overview Copyright @ 2024 Oracle and/or its affiliates. 9
12. MySQL HeatWave Database Service MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 10
13. MySQL HeatWave Database Service - MySQLaaS MySQL HeatWave Service is available in 32 regions on OCI: Copyright @ 2024 Oracle and/or its affiliates. 11
14. MySQL HeatWave Database Service - MySQLaaS MySQL HeatWave Service uses MySQL Enterprise Edition (thread pool, data-masking, audit, ...) Latest 8.0 and latest Innovation Release are available. Upgrades are mandatory (but can be delayed to some versions) and managed automatically by the MySQL Team or manually by the user. Copyright @ 2024 Oracle and/or its affiliates. 12
15. MySQL HeatWave Database Service - MySQLaaS The service includes all the necessary and basics features required for a managed Database Service: • Security (TLS, encryption, ...) • Backups • Point-in-Time Recovery • High Availability • Read-Replicas • Monitoring • Cloud Native Deployment APIs (Terraform, ...) • ... Copyright @ 2024 Oracle and/or its affiliates. 13
16. MySQL HeatWave Database Service - MySQLaaS Let's discover the di�erent available options by the creation of a DB instance: Copyright @ 2024 Oracle and/or its affiliates. 14
17. Production • HA enabled • 64GB memory • 1To storage • automatic backup enabled • point-in-time recovery enabled • delete protected enabled • automatic backups retentions enabled • �nal backup required Development • HA disabled • 8GB memory • 50GB storage • automatic backup enabled • point-in-time recovery enabled • delete protected disabled • automatic backups retentions disabled • �nal backup skipped MySQL HeatWave Database Service - MySQLaaS Recommended defaults: Copyright @ 2024 Oracle and/or its affiliates. 15
18. MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 16
19. MySQL HeatWave Database Service - MySQLaaS High Availability uses native MySQL Group Replication with 3 nodes. The Group uses a Single Primary as recommended. select member_host, member_state, member_role, member_version from performance_schema.replication_group_members; +------------------+--------------+-------------+----------------+ | member_host | member_state | member_role | member_version | +------------------+--------------+-------------+----------------+ | 2wvlqjejel0scjzo | ONLINE | PRIMARY | 8.0.35 | | zdjnpklomwkfu323 | ONLINE | SECONDARY | 8.0.35 | | mw3scif2neaztidv | ONLINE | SECONDARY | 8.0.35 | +------------------+--------------+-------------+----------------+ Copyright @ 2024 Oracle and/or its affiliates. 17
20. MySQL HeatWave Database Service - MySQLaaS When MySQL HeatWave is enabled, it means that HeatWave Cluster, the OLTP and OLAP in-memory will be available on demand. The password must be strong enought and must be remembered as it won't be possible to reset it. select @@version, @@version_comment; +-----------------+--------------------------+ | @@version | @@version_comment | +-----------------+--------------------------+ | 8.0.35-u2-cloud | MySQL Enterprise - Cloud | +-----------------+--------------------------+ Copyright @ 2024 Oracle and/or its affiliates. 18
21. MySQL HeatWave Database Service - MySQLaaS Networking - VCN and Subnets: the database is not open to the Internet Copyright @ 2024 Oracle and/or its affiliates. 19
22. MySQL HeatWave Database Service - MySQLaaS Placement Copyright @ 2024 Oracle and/or its affiliates. 20
23. MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 21
24. MySQL HeatWave Database Service - MySQLaaS Storage and IOPS Copyright @ 2024 Oracle and/or its affiliates. 22
25. MySQL HeatWave Database Service - MySQLaaS Storage and IOPS grow togheter: Size (GB) IOPS Throughput 512 38400 300MB 1024 76800 600MB 2048 153600 1.17GB Copyright @ 2024 Oracle and/or its affiliates. 23
26. MySQL HeatWave Database Service - MySQLaaS Backups and point-in-time recovery Copyright @ 2024 Oracle and/or its affiliates. 24
27. MySQL HeatWave Database Service - MySQLaaS Extra: Deletion plan Copyright @ 2024 Oracle and/or its affiliates. 25
28. MySQL HeatWave Database Service - MySQLaaS Extra: Con�guration Copyright @ 2024 Oracle and/or its affiliates. 26
29. MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 27
30. MySQL HeatWave Database Service - MySQLaaS Extra: Connections Copyright @ 2024 Oracle and/or its affiliates. 28
31. MySQL HeatWave Database Service - MySQLaaS MySQL HeatWave Service is the only DBaaS providing the X Protocol. Thanks to the X Protocol, you have access to the X DevAPI and use MySQL HeatWave as a NoSQL Document Store, allowing to use MySQL HeatWave without a single SQL statement and store JSON documents: JS > db.restaurants.find("borough = 'Brooklyn'") .fields("name", "cuisine").limit(10) Copyright @ 2024 Oracle and/or its affiliates. 29
32. MySQL HeatWave Database Service - MySQLaaS SSL certi�cates generated by MySQL HeatWave or Bring Your Own Certi�cate: Copyright @ 2024 Oracle and/or its affiliates. 30
33. MySQL HeatWave Database Service - MySQLaaS SSL certi�cates generated by MySQL HeatWave or Bring Your Own Certi�cate: Copyright @ 2024 Oracle and/or its affiliates. 31
34. MySQL HeatWave Database Service - MySQLaaS Extra: Crash Recovery Crash Recovery can be disabled during initial data load to accelerate the process. Copyright @ 2024 Oracle and/or its affiliates. 32
35. MySQL HeatWave Database Service - MySQLaaS Extra: Maintenance By default the maintenance window is automatically assignd but can be manually speci�ed. Copyright @ 2024 Oracle and/or its affiliates. 33
36. MySQL HeatWave Database Service - MySQLaaS Extra: Management By default, the Database Management Service is enabled. Copyright @ 2024 Oracle and/or its affiliates. 34
37. MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 35
38. MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 36
39. MySQL HeatWave Database Service - MySQLaaS Extra: Data Import It's possible to load the initial data from a MySQL Shell Dump stored on OCI Object Storage. Copyright @ 2024 Oracle and/or its affiliates. 37
40. Copyright @ 2024 Oracle and/or its affiliates. 38
41. MySQL HeatWave Database Service - MySQLaaS Read Replicas Copyright @ 2024 Oracle and/or its affiliates. 39
42. MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 40
43. resource "oci_mysql_mysql_db_system" "MDSinstance" { admin_password = var.admin_password admin_username = var.admin_username availability_domain = var.availability_domain compartment_id = var.compartment_ocid configuration_id = var.configuration_id shape_name = var.mysql_shape subnet_id = var.subnet_id data_storage_size_in_gb = var.mysql_data_storage_in_gb display_name = var.display_name count = var.existing_mds_instance_id == "" ? 1 : 0 is_highly_available = var.deploy_ha } MySQL HeatWave Database Service - MySQLaaS Deployment APIs Copyright @ 2024 Oracle and/or its affiliates. 41
44. MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 42
45. MySQL HeatWave Database Service - MySQLaaS VCN 10.0.0.0/16 Public Subnet 10.0.0.0/24 VCN 10.1.0.0/16 Public Subnet 10.1.0.0/24 Private Subnet Private Subnet 10.0.1.0/24 10.1.1.0/24 Application MySQL Database Service Oracle Cloud Infrastructure Region: us-ashburn-1 Region: eu-frankfurt-1 Availability Domain 1 Availability Domain 2 Availability Domain 3 Application MySQL Database Service MySQL Database Service MySQL Database Service Asynchronous Replication Channel Internet Gateway Application Read/Only Report Copyright @ 2024 Oracle and/or its affiliates. 43
46. MySQL HeatWave Database Service Accelerator - HeatWave Cluster Copyright @ 2024 Oracle and/or its affiliates. 44
47. Challenge: Organizations need to use separate systems for transactions and analytics Copyright @ 2024 Oracle and/or its affiliates. 45
48. • Separate analytics database • Complex ETL • No real-time analytics • Security & compliance risks • Increase costs Historically MySQL is Optimized for OLTP, not designed for analytic processing Copyright @ 2024 Oracle and/or its affiliates. 46
49. • One service for OLTP & OLAP • No ETL duplication • Unmatched performance, at a fraction of the cost • Real-time analytics • Improved security • Applications work without changes With MySQL HeatWave, one database is be�er than two ! Copyright @ 2024 Oracle and/or its affiliates. 47
50. MySQL HeatWave Cluster - Faster and Cheaper *Benchmark queries are derived from TPC-H benchmark, but results are not comparable to published TPC-H benchmark results since they do not comply with TPC-H speci�cation. Copyright @ 2024 Oracle and/or its affiliates. 48
51. MySQL HeatWave Cluster - Enable Copyright @ 2024 Oracle and/or its affiliates. 49
52. MySQL HeatWave Cluster - Enable Copyright @ 2024 Oracle and/or its affiliates. 50
53. MySQL HeatWave Cluster - Enable DB System Overview Copyright @ 2024 Oracle and/or its affiliates. 51
54. MySQL HeatWave Cluster - Enable Copyright @ 2024 Oracle and/or its affiliates. 52
55. MySQL HeatWave Cluster - Status Check the HeatWave Cluster's status via SQL: MySQL> show global status like 'rapid_%er%_status'; +----------------------+--------+ | Variable_name | Value | +----------------------+--------+ | rapid_cluster_status | ON | | rapid_service_status | ONLINE | +----------------------+--------+ 2 rows in set (0.0013 sec) MySQL> show global status like 'rapid_%number'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | rapid_cluster_ready_number | 1 | +----------------------------+-------+ 1 row in set (0.0012 sec) Copyright @ 2024 Oracle and/or its affiliates. 53
56. MySQL HeatWave Cluster - Example Data information: select format_bytes(sum(data_length)) DATA, format_bytes(sum(index_length)) INDEXES, format_bytes(sum(data_length + index_length)) 'TOTAL SIZE' from information_schema.TABLES order by data_length + index_length; +-----------+-----------+------------+ | DATA | INDEXES | TOTAL SIZE | +-----------+-----------+------------+ | 21.89 GiB | 14.06 GiB | 35.95 GiB | +-----------+-----------+------------+ 1 row in set (0.0043 sec) Copyright @ 2024 Oracle and/or its affiliates. 54
57. MySQL HeatWave Cluster - Example Query using InnoDB (before loading the data to HeatWave Cluster): select * from (select date(time_stamp) as `day`, device_id, count(*) as `tot`, max(value) as `max hum`, min(value) as `min hum`, avg(value) as `avg hum` from humidity_history group by device_id, day) a natural join (select date(time_stamp) as `day`, device_id, count(*) as `tot`, max(value) as `max temp`, min(value) as `min temp`, avg(value) as `avg temp` from temperature_history group by device_id, day) b order by day, device_id; ... 51 rows in set (8 min 14.3943 sec) Copyright @ 2024 Oracle and/or its affiliates. 55
58. call sys.heatwave_load(JSON_ARRAY('piday'), NULL); ... Query OK, 0 rows affected (2 min 52.6052 sec) MySQL HeatWave Cluster - Example Loading the data to HeatWave Cluster: Copyright @ 2024 Oracle and/or its affiliates. 56
59. MySQL HeatWave Cluster - Example Query using InnoDB (after loading the data to HeatWave Cluster): select * from (select date(time_stamp) as `day`, device_id, count(*) as `tot`, max(value) as `max hum`, min(value) as `min hum`, avg(value) as `avg hum` from humidity_history group by device_id, day) a natural join (select date(time_stamp) as `day`, device_id, count(*) as `tot`, max(value) as `max temp`, min(value) as `min temp`, avg(value) as `avg temp` from temperature_history group by device_id, day) b order by day, device_id; ... 51 rows in set (4.4113 sec) Copyright @ 2024 Oracle and/or its affiliates. 57
60. MySQL HeatWave Cluster - Example From 8 min 14 to 4.4 sec ! Copyright @ 2024 Oracle and/or its affiliates. 58
61. MySQL HeatWave Cluster - Example From 8 min 14 to 4.4 sec ! And with 2 nodes cluster, this can be reduced to 2.3 seconds ! MySQL> show global status like 'rapid_%number'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | rapid_cluster_ready_number | 2 | +----------------------------+-------+ 1 row in set (0.0020 sec) Copyright @ 2024 Oracle and/or its affiliates. 58
62. MySQL HeatWave Cluster - Example From 8 min 14 to 4.4 sec ! And with 2 nodes cluster, this can be reduced to 2.3 seconds ! MySQL> show global status like 'rapid_%number'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | rapid_cluster_ready_number | 2 | +----------------------------+-------+ 1 row in set (0.0020 sec) Copyright @ 2024 Oracle and/or its affiliates. 58
63. MySQL HeatWave Cluster - Example select * from (select date(time_stamp) as `day`, device_id, count(*) as `tot`, max(value) as `max hum`, min(value) as `min hum`, avg(value) as `avg hum` from humidity_history group by device_id, day) a natural join (select date(time_stamp) as `day`, device_id, count(*) as `tot`, max(value) as `max temp`, min(value) as `min temp`, avg(value) as `avg temp` from temperature_history group by device_id, day) b order by day, device_id; ... 51 rows in set (2.3271 sec) Copyright @ 2024 Oracle and/or its affiliates. 59
64. MySQL HeatWave Database Service Object Storage - Lakehouse Copyright @ 2024 Oracle and/or its affiliates. 60
65. Massive amount of data stored in �les • Databases are systems of record • Files are repository for other types of data (e.g IoT, web content, log �les) • Over 80% of the data we generate is in �les • 99.5% of collected data remains unused ◦ Lack of time, resources, and expertise to process di�erent data formats across di�erent data sources Copyright @ 2024 Oracle and/or its affiliates. 61
66. • Query data in MySQL, in the Object Store, or across both using SQL syntax • Up to 500TB of data (HeatWave cluster scales up to 512 nodes) • Querying the data in the Object Store is as fast as querying the database • Scale out data processing in the Object Store, data is not copied to the MySQL Database: for both MySQL and non-MySQL workloads MySQL HeatWave Lakehouse Copyright @ 2024 Oracle and/or its affiliates. 62
67. MySQL HeatWave Lakehouse Very simple to query �les in Object Store 1. System Setup • Run MySQL Autopilot on Object Store to determine cluster size and schema mapping • Execute DDLs generated by Autopilot 2. Run query across �les and tables MySQL MySQL> > SELECT SELECT count count( (* *) ) FROM FROM Sensor Sensor, , SALES SALES WHERE WHERE Sensor Sensor. .degrees degrees > > 30 30 AND AND Sensor Sensor. .date date = = SALES SALES. .date date; ; Copyright @ 2024 Oracle and/or its affiliates. 63
68. MySQL HeatWave Lakehouse - Example MySQL> CREATE TABLE `piday`.`temperature_history_archive`( `id` int unsigned NOT NULL, `time_stamp` timestamp(0) NOT NULL, `device_id` varchar(28) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `value` decimal(4,2) NOT NULL ) ENGINE=lakehouse SECONDARY_ENGINE=RAPID ENGINE_ATTRIBUTE='{"file": [{"par": "https://...<PAR>..."}], "dialect": {"format": "csv", "field_delimiter": ",", "record_delimiter": "\\n"}}'; Copyright @ 2024 Oracle and/or its affiliates. 64
69. MySQL HeatWave Lakehouse - Example MySQL> CREATE TABLE `piday`.`temperature_history_archive`( `id` int unsigned NOT NULL, `time_stamp` timestamp(0) NOT NULL, `device_id` varchar(28) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `value` decimal(4,2) NOT NULL ) ENGINE=lakehouse SECONDARY_ENGINE=RAPID ENGINE_ATTRIBUTE='{"file": [{"par": "https://...<PAR>..."}], "dialect": {"format": "csv", "field_delimiter": ",", "record_delimiter": "\\n"}}'; MySQL> ALTER TABLE /*+ AUTOPILOT_DISABLE_CHECK */ `piday`.`temperature_history_archive` SECONDARY_LOAD; Query OK, 0 rows affected (26.5884 sec) Copyright @ 2024 Oracle and/or its affiliates. 64
70. MySQL HeatWave Lakehouse - Example MySQL> CREATE TABLE `piday`.`temperature_history_archive`( `id` int unsigned NOT NULL, `time_stamp` timestamp(0) NOT NULL, `device_id` varchar(28) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=VARLEN', `value` decimal(4,2) NOT NULL ) ENGINE=lakehouse SECONDARY_ENGINE=RAPID ENGINE_ATTRIBUTE='{"file": [{"par": "https://...<PAR>..."}], "dialect": {"format": "csv", "field_delimiter": ",", "record_delimiter": "\\n"}}'; MySQL> ALTER TABLE /*+ AUTOPILOT_DISABLE_CHECK */ `piday`.`temperature_history_archive` SECONDARY_LOAD; Query OK, 0 rows affected (26.5884 sec) MySQL> select count(*) from temperature_history_archive; +----------+ | count(*) | +----------+ | 51928629 | +----------+ 1 row in set (0.0244 sec) Copyright @ 2024 Oracle and/or its affiliates. 64
71. MySQL HeatWave Database Service Oracle Analytics Cloud Copyright @ 2024 Oracle and/or its affiliates. 65
72. MySQL HeatWave & OAC Copyright @ 2024 Oracle and/or its affiliates. 66
73. MySQL HeatWave & OAC Copyright @ 2024 Oracle and/or its affiliates. 67
74. MySQL HeatWave & OAC Copyright @ 2024 Oracle and/or its affiliates. 68
75. MySQL HeatWave & OAC Copyright @ 2024 Oracle and/or its affiliates. 69
76. MySQL HeatWave & OAC Copyright @ 2024 Oracle and/or its affiliates. 70
77. MySQL HeatWave Database Service Machine Learning Copyright @ 2024 Oracle and/or its affiliates. 71
78. Machine Learning at the service of HeatWave Internally, MySQL HeatWave uses Machine Learning to improve quality and performance, this is the MySQL Autopilot components: Copyright @ 2024 Oracle and/or its affiliates. 72
79. Machine Learning at the service of HeatWave Example: MySQL HeatWave Cluster Nodes Estimation Copyright @ 2024 Oracle and/or its affiliates. 73
80. Machine Learning at the service of HeatWave Example: Auto Parallel Load/Unload • Ease of use for customers to load/unload many tables in a single command • Automatically decides the best degree of parallelism for load of individual tables CALL sys.heatwave_load(db_list, [NULL | JSON_OBJECT(options)]) CALL sys.heatwave_unload (db_list, [NULL | JSON_OBJECT (options)]) • Acceleration score for each table (and column), ranks tables and top ranked tables are loaded • Auto Unload Advisor recommends unloading unused tables based on recent query activity ◦ Frees up space that can be used for query execution ◦ Potentially other “hot” tables can be loaded • Tables loaded and unloaded in background during low activity Copyright @ 2024 Oracle and/or its affiliates. 74
81. Machine Learning at the service of HeatWave Example: Autopilot Indexing With MySQL Autopilot Indexing, users can easily identify and resolve performance issues with their database systems. Autopilot Indexing has a comprehensive set of features that allow users to tune the performance of database systems, such as: �. Considers both query and DML performance (index maintenance cost) �. Recommends CREATE and DROP of indexes �. Generates DDLs for index creation/drop �. Provides performance prediction (per query and total workload) �. Provides storage prediction for the recommended indexes �. Provides an explanation for the recommendations �. Console integration to improve user experience (on AWS) Copyright @ 2024 Oracle and/or its affiliates. 75
82. Machine Learning at the service of HeatWave Example: Autopilot Indexing (2) Note that it works particularly good with OLTP workload, for OLAP workload, using MySQL HeatWave Cluster is a be�er solution and no new index will be recommended. Copyright @ 2024 Oracle and/or its affiliates. 76
83. Machine Learning at the service of HeatWave Example: Autopilot Indexing (3) CALL sys.autopilot_index_advisor(JSON_OBJECT('target_schema', JSON_ARRAY('mydb'))); ... +----------------------------- | INDEX SUGGESTIONS +---------------------------- | Total Index suggestions: 1 | Statements analyzed: 19 | | SUGGEST TABLE INDEXED REASON ESTIMATED ESTIMATED | ACTION NAME COLUMNS SUGGESTED FOOTPRINT PERF IMPACT | ------- ----- ------- --------- --------- ----------- | CREATE `imdb`.`principals` `category`, `ordering` Missing Index 2.06 GiB + HIGH | | Expected performance benefit after applying all Index suggestions: 2.9% | Expected storage footprint after applying all Index suggestions: + 2.06 GiB | 0 bytes freed up by dropping indexes. | 2.06 GiB required for creating indexes. | NOTE: Indexes will be stored efficiently at time of creation. | To accommodate efficient future inserts, size may double. | +- ... Copyright @ 2024 Oracle and/or its affiliates. 77
84. Machine Learning at the service of HeatWave Example: Autopilot Indexing (4) { "SQL": "CREATE INDEX `autoidx_tab1102_col4_col2` ON `mydb`.`principals` ( `category`, `ordering` );", "explanation": [ { "reason": "Covering Index", "query_text": "SELECT COUNT ( * ) FROM `principals` WHERE `category` = ?", "estimated_gain": "6.9x" } ] } Copyright @ 2024 Oracle and/or its affiliates. 78
85. MySQL HeatWave at the service of ML Machine Learning is a branch of arti�cial intelligence that enables computers to learn from data and make predictions. Copyright @ 2024 Oracle and/or its affiliates. 79
86. MySQL HeatWave at the service of ML Example: Load Approval Copyright @ 2024 Oracle and/or its affiliates. 80
87. MySQL HeatWave at the service of ML Traditional MySQL Data Flow Copyright @ 2024 Oracle and/or its affiliates. 81
88. MySQL HeatWave at the service of ML Much easier with MySQL HeatWave ! Everything stays in HeatWave ! MySQL MySQL> > CALL CALL sys sys. .ML_TRAIN ML_TRAIN( ('heatwaveml_bench.bank_marketing_train' 'heatwaveml_bench.bank_marketing_train', , 'y' 'y', , JSON_OBJECT JSON_OBJECT( ('task' 'task', , 'classification' 'classification') ), , @contech @contech) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (8 8 min min 15.2575 15.2575 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 82
89. MySQL HeatWave at the service of ML Much easier with MySQL HeatWave ! Everything stays in HeatWave ! MySQL MySQL> > CALL CALL sys sys. .ML_TRAIN ML_TRAIN( ('heatwaveml_bench.bank_marketing_train' 'heatwaveml_bench.bank_marketing_train', , 'y' 'y', , JSON_OBJECT JSON_OBJECT( ('task' 'task', , 'classification' 'classification') ), , @contech @contech) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (8 8 min min 15.2575 15.2575 sec sec) ) ML_TRAIN(table_name, target_column_name, model_name, [options]) • Use AutoML to train a ML model based on input user table • The trained ML model will be stored in MySQL DB (Model Catalog) • task can be 'classi�cation', 'regression', 'forecasting', 'anomaly_detection', 'recommendation' Copyright @ 2024 Oracle and/or its affiliates. 82
90. Current Steps MySQL HeatWave at the service of ML Much simpler for DB developers Copyright @ 2024 Oracle and/or its affiliates. 83
91. Current Steps Steps using MySQL HeatWave ML MySQL> call sys.ml_train("titanic_train", "target", "index", "titanic_model"); MySQL HeatWave at the service of ML Much simpler for DB developers Copyright @ 2024 Oracle and/or its affiliates. 83
92. MySQL HeatWave at the service of ML Inference MySQL MySQL> > CALL CALL sys sys. .ML_MODEL_LOAD ML_MODEL_LOAD( (@contech @contech, , NULL NULL) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.8177 0.8177 sec sec) ) MySQL MySQL> > CALL CALL sys sys. .ML_SCORE ML_SCORE( ('heatwaveml_bench.bank_marketing_test' 'heatwaveml_bench.bank_marketing_test', , 'y' 'y', , @contech @contech, , 'accuracy' 'accuracy', , @score @score, , null null) ); ; Query OK Query OK, , 0 0 rows rows affected affected ( (2.2259 2.2259 sec sec) ) MySQL MySQL> > SELECT SELECT @score @score; ; --------------------+ --------------------+ | | @score @score | | + +--------------------+ --------------------+ | | 0.9041580557823181 0.9041580557823181 | | + +--------------------+ --------------------+ 1 1 row row in in set set ( (0.0006 0.0006 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 84
93. SELECT SELECT sys sys. .ML_PREDICT_ROW ML_PREDICT_ROW( ( JSON_OBJECT JSON_OBJECT( ("age" "age", , "30" "30", , "job" "job", , "services" "services", , "marital" "marital", , "married" "married", , "education" "education", , "secondary" "secondary", , "default1" "default1", , "no" "no", , "balance" "balance", , "7032" "7032", , "housing" "housing", , "no" "no", , "loan" "loan", , "no" "no", , "contact" "contact", , "cellular" "cellular", , "day" "day", , "17" "17", , "month" "month", , "jul" "jul", , "duration" "duration", , "402" "402", , "campaign" "campaign", , "1" "1", , "pdays" "pdays", , "-1" "-1", , "previous" "previous", , "0" "0", , "poutcome" "poutcome", , "unknown" "unknown" "id" "id", , "0" "0") ), , @contech @contech, , NULL NULL) ); ; MySQL HeatWave at the service of ML Copyright @ 2024 Oracle and/or its affiliates. 85
94. SELECT SELECT sys sys. .ML_PREDICT_ROW ML_PREDICT_ROW( ( JSON_OBJECT JSON_OBJECT( ("age" "age", , "30" "30", , "job" "job", , "services" "services", , "marital" "marital", , "married" "married", , "education" "education", , "secondary" "secondary", , "default1" "default1", , "no" "no", , "balance" "balance", , "7032" "7032", , "housing" "housing", , "no" "no", , "loan" "loan", , "no" "no", , "contact" "contact", , "cellular" "cellular", , "day" "day", , "17" "17", , "month" "month", , "jul" "jul", , "duration" "duration", , "402" "402", , "campaign" "campaign", , "1" "1", , "pdays" "pdays", , "-1" "-1", , "previous" "previous", , "0" "0", , "poutcome" "poutcome", , "unknown" "unknown" "id" "id", , "0" "0") ), , @contech @contech, , NULL NULL) ); ; { { "id" "id": : 0.0 0.0, , "age" "age": : 30.0 30.0, , "day" "day": : 17.0 17.0, , "job" "job": : "services" "services", , "loan" "loan": : "no" "no", , "month" "month": : "jul" "jul", , "pdays" "pdays": : -1.0 -1.0, , "balance" "balance": : 7032.0 7032.0, , ... ... "poutcome" "poutcome": : "unknown" "unknown", , "previous" "previous": : 0.0 0.0, , "education" "education": : "secondary" "secondary", , "Prediction" "Prediction": : "no" "no", , "ml_results" "ml_results": : { {"predictions" "predictions": : { {"y" "y": : "no" "no"} }, , "probabilities" "probabilities": : { { "no" "no": : 0.8799999952316284 0.8799999952316284, , "yes" "yes": : 0.11999999731779099 0.11999999731779099} }} } } } MySQL HeatWave at the service of ML Copyright @ 2024 Oracle and/or its affiliates. 85
95. MySQL HeatWave at the service of ML - Demo demo Copyright @ 2024 Oracle and/or its affiliates. 86
96. MySQL HeatWave Database Service for Developers Copyright @ 2024 Oracle and/or its affiliates. 87
97. Using MySQL without a single line of SQL We saw that MySQL HeatWave is the only MySQLaaS with the X protocol. This allows to use MySQL as a Document Store without the need of writing any SQL line (CRUD): collection collection. .add add( ({ { name name: : 'fred' 'fred', , age age: : 48 48 } }) ) . .add add( ({ { name name: : 'scott' 'scott', , age age: : 49 49 } }) ) . .execute execute( () ) collection collection. .find find( ('name = "fred"' 'name = "fred"') ). .fields fields( ("age" "age") ) { { "age" "age": : 48 48 } } Copyright @ 2024 Oracle and/or its affiliates. 88
98. Example in import import * *; ; class class Main Main { { public public static static void void main main( (String String args args[ [] ]) ) { { Session Session mySession mySession = = new new SessionFactory SessionFactory( () ) . .getSession getSession( ("mysqlx://10.0.1.1:33060/docstore?user=resto&password=Passw0rd!" "mysqlx://10.0.1.1:33060/docstore?user=resto&password=Passw0rd!") ); ; Schema Schema myDb myDb = = mySession mySession. .getSchema getSchema( ("docstore" "docstore") ); ; Collection Collection myColl myColl = = myDb myDb. .getCollection getCollection( ("restaurants" "restaurants") ); ; DocResult DocResult myDocs myDocs = = myColl myColl. .find find( ("name like :param" "name like :param") ). .limit limit( (1 1) ) . .bind bind( ("param" "param", , "Green%" "Green%") ). .execute execute( () ); ; System System. .out out. .println println( (myDocs myDocs. .fetchOne fetchOne( () )) ); ; mySession mySession. .close close( () ); ; } } } } Copyright @ 2024 Oracle and/or its affiliates. com com. .mysql mysql. .cj cj. .xdevapi xdevapi. . 89
99. Writing Store Procedures in • Traditional MySQL operations have primarily relied on SQL for querying and manipulating data. • MySQL has never been known for its store procedures, and their usage has always been discouraged. • However, in modern application development, there's a growing need for �exibility and extensibility within the database itself. • Introducing JavaScript support in MySQL through GraalVM opens up a realm of possibilities for developers to leverage familiar tools and libraries directly within the database engine. Copyright @ 2024 Oracle and/or its affiliates. 90
100. Writing Store Procedures in CREATE CREATE FUNCTION FUNCTION gcd_js gcd_js ( (a a INT INT, , b b INT INT) ) RETURNS RETURNS INT INT LANGUAGE LANGUAGE JAVASCRIPT JAVASCRIPT AS AS $$ $$ let let [ [x x, , y y] ] = = [ [Math Math. .abs abs( (a a) ), , Math Math. .abs abs( (b b) )] ]; ; while while ( (y y) ) [ [x x, , y y] ] = = [ [y y, , x x % % y y] ]; ; return return x x; ; $$ $$ Copyright @ 2024 Oracle and/or its affiliates. 91
101. Writing Store Procedures in CREATE CREATE FUNCTION FUNCTION gcd_js gcd_js ( (a a INT INT, , b b INT INT) ) RETURNS RETURNS INT INT LANGUAGE LANGUAGE JAVASCRIPT JAVASCRIPT AS AS $$ $$ let let [ [x x, , y y] ] = = [ [Math Math. .abs abs( (a a) ), , Math Math. .abs abs( (b b) )] ]; ; while while ( (y y) ) [ [x x, , y y] ] = = [ [y y, , x x % % y y] ]; ; return return x x; ; $$ $$ For more info check h�ps://blogs.oracle.com/mysql/post/javascript-support-in-mysql-the-uuid-example MySQL MySQL > > select select js_uuid_to_datetime js_uuid_to_datetime( (uuid uuid( () )) ); ; + +-----------------------------+ -----------------------------+ | | js_uuid_to_datetime js_uuid_to_datetime( (uuid uuid( () )) ) | | + +-----------------------------+ -----------------------------+ | | 2024 2024- -03 03- -20 20 22 22: :31 31: :20.824 20.824 | | + +-----------------------------+ -----------------------------+ 1 1 row row in in set set ( (0.0009 0.0009 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 91
102. MySQL HeatWave Database Service Migration Copyright @ 2024 Oracle and/or its affiliates. 92
103. Migration to MySQL HeatWave Service in OCI The best method to migrate from a MySQL instance on-premise or in another cloud is to use MySQL Shell. MySQL Shell can also be used to migrate from MariaDB to MySQL HeatWave. Copyright @ 2024 Oracle and/or its affiliates. 93
104. Migration to MySQL HeatWave Service in OCI There are two supported methods: �. dump to an intermediate storage ◦ local disk ◦ Object Storage bucket (recommended) �. stream the data directly to the DB Instance in OCI (copy) Copyright @ 2024 Oracle and/or its affiliates. 94
105. Migration to MySQL HeatWave Service in OCI MySQL Shell Dump & Load MySQL Shell provides a method to dump an instance (or a schema or a table), a logical dump performed in parallel, that can be prepared to be compatible with MySQL HeatWave: MySQL JS > util.dumpInstance('fromOnPrem', {osBucketName: "migration", users: true, osNamespace:'xxxxxxxx', threads:8, ocimds: true, compatibility: ["force_innodb","strip_de�ners","create_invisible_pks", "skip_invalid_accounts","strip_restricted_grants", "strip_tablespaces", "ignore_wildcard_grants", "strip_invalid_grants"]}) Copyright @ 2024 Oracle and/or its affiliates. 95
106. Migration to MySQL HeatWave Service in OCI MySQL Shell Dump & Load If the instance is already existing, we can load the data using MySQL Shell Load: MySQL JS > util.loadDump("https://o..com/p/F..W/n/i..j/b/migration/o/fromOnPrem/@.manifest.json", {progressFile: "progress.json", ignoreVersion: true}) If the instance needs to be created, we can use the dump as initial data during instance's creation: Copyright @ 2024 Oracle and/or its affiliates. 96
107. Migration to MySQL HeatWave Service in OCI VCN 10.0.0.0/16 Public Subnet Object Storage Logical-Backups Bucket 10.0.0.0/24 Private Subnet 10.0.1.0/24 MySQL Server Oracle Cloud Infrastructure On Premise MySQL HeatWave Database Service Region: us-ashburn-1 Internet Gateway MySQL Shell Compute Instance util.instanceDum p() util.load() 1 2 Copyright @ 2024 Oracle and/or its affiliates. 97
108. Migration to MySQL HeatWave Service in OCI MySQL Shell Copy Instance It's possible to bypass an intermediate storage and copy directly to a MySQL HeatWave instance using MySQL Shell copyInstance utility: MySQL JS> util.copyInstance('mysql://admin@10.0.1.140', {"compatibility": ["force_innodb", "skip_invalid_accounts", "strip_definers", "strip_restricted_grants", "strip_tablespaces", "ignore_wildcard_grants", "strip_invalid_grants", "create_invisible_pks"], users: "true", threads: 4}) Copyright @ 2024 Oracle and/or its affiliates. 98
109. When the dump is loaded, for a smooth migration, it's possible to also create an inbound replication channel to replicate from the source (MySQL on- prem for example) to the new MySQL HeatWave instance: Migration to MySQL HeatWave Service in OCI MySQL Replication to OCI - Inbound Channel Copyright @ 2024 Oracle and/or its affiliates. 99
110. Migration to MySQL HeatWave Service in OCI Copyright @ 2024 Oracle and/or its affiliates. 100
111. Migration to MySQL HeatWave Service in OCI Copyright @ 2024 Oracle and/or its affiliates. 101
112. Migration to MySQL HeatWave Service in OCI VCN 10.0.0.0/16 Public Subnet Object Storage Dump-from-on-prem Bucket 10.0.0.0/24 Private Subnet 10.0.1.0/24 MySQL Shell Compute Instance MySQL Server Oracle Cloud Infrastructure On Premise MySQL HeatWave Database Service Region: us-ashburn-1 Internet Gateway REPLICATION Copyright @ 2024 Oracle and/or its affiliates. 102
113. MySQL HeatWave Database Service Integration with MySQL Shell for Visual Studio Code Copyright @ 2024 Oracle and/or its affiliates. 103
114. It's possible to manage and use the MySQL HeatWave Instance directly from MySQL Shell for Visual Studio Code. MySQL Shell the Companion for MySQL HeatWave Service Copyright @ 2024 Oracle and/or its affiliates. 104
115. MySQL Shell the Companion for MySQL HeatWave Service Copyright @ 2024 Oracle and/or its affiliates. 105
116. MySQL Shell for Visual Studio Code is very useful to load data to a MySQL HeatWave Cluster MySQL Shell the Companion for MySQL HeatWave Service Copyright @ 2024 Oracle and/or its affiliates. 106
117. MySQL Shell the Companion for MySQL HeatWave Service Copyright @ 2024 Oracle and/or its affiliates. 107
118. MySQL HeatWave Database Service What's next ? Copyright @ 2024 Oracle and/or its affiliates. 108
119. What's next in MySQL HeatWave ? Copyright @ 2024 Oracle and/or its affiliates. 109
120. What's next in MySQL HeatWave ? • Generative AI and vector store • and much more to some, stay tuned ! Copyright @ 2024 Oracle and/or its affiliates. 109
121. Share your � to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 110
122. More Technical Information about MySQL HeatWave • h�ps://lefred.be/wp-content/uploads/2024/02/MySQL-HeatWave-A-Deep-Dive-Into- Optimizations.pdf • h�ps://lefred.be/wp-content/uploads/2024/02/MySQL-HeatWave-ML.pdf • h�ps://lefred.be/wp-content/uploads/2024/02/MySQL-HeatWave-Lakehouse-Scaling- Queries-to-Thousands-of-Cores.pdf • h�ps://lefred.be/wp-content/uploads/2024/02/Developing-GenAI-and-vector-store- applications-with-MySQL-HeatWave.pdf • h�ps://lefred.be/wp-content/uploads/2024/02/MySQL-JavaScript-Belgian-MySQL- Days.pdf Copyright @ 2024 Oracle and/or its affiliates. 111
123. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 112