Discover MySQL HeatWave Database Service in OCI

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.
Join this session to learn about the new features added like Machine Learning.

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL Confoo.CA - February 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 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 P P S S S S 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 8min14 to 4.4 sec ! Copyright @ 2024 Oracle and/or its affiliates. 58
61. MySQL HeatWave Cluster - Example From 8min14 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 8min14 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> SELECT count(*) FROM Sensor, SALES WHERE Sensor.degrees > 30 AND Sensor.date = SALES.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 Machine Learning Copyright @ 2024 Oracle and/or its affiliates. 65
72. 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. 66
73. Machine Learning at the service of HeatWave Example: MySQL HeatWave Cluster Nodes Estimation Copyright @ 2024 Oracle and/or its affiliates. 67
74. 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. 68
75. 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. 69
76. MySQL HeatWave at the service of ML Example: Load Approval Copyright @ 2024 Oracle and/or its affiliates. 70
77. MySQL HeatWave at the service of ML Traditional MySQL Data Flow Copyright @ 2024 Oracle and/or its affiliates. 71
78. MySQL HeatWave at the service of ML Much easier with MySQL HeatWave ! Everything stays in HeatWave ! MySQL> CALL sys.ML_TRAIN('heatwaveml_bench.bank_marketing_train', 'y', JSON_OBJECT('task', 'classification'), @confoo24); Query OK, 0 rows affected (8 min 15.2575 sec) Copyright @ 2024 Oracle and/or its affiliates. 72
79. MySQL HeatWave at the service of ML Much easier with MySQL HeatWave ! Everything stays in HeatWave ! MySQL> CALL sys.ML_TRAIN('heatwaveml_bench.bank_marketing_train', 'y', JSON_OBJECT('task', 'classification'), @confoo24); Query OK, 0 rows affected (8 min 15.2575 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. 72
80. Current Steps MySQL HeatWave at the service of ML Much simpler for DB developers Copyright @ 2024 Oracle and/or its affiliates. 73
81. 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. 73
82. MySQL HeatWave at the service of ML Inference MySQL> CALL sys.ML_MODEL_LOAD(@confoo24, NULL); Query OK, 0 rows affected (0.8177 sec) MySQL> CALL sys.ML_SCORE('heatwaveml_bench.bank_marketing_test', 'y', @confoo24, 'accuracy', @score, null); Query OK, 0 rows affected (2.2259 sec) MySQL> SELECT @score; --------------------+ | @score | +--------------------+ | 0.9041580557823181 | +--------------------+ 1 row in set (0.0006 sec) Copyright @ 2024 Oracle and/or its affiliates. 74
83. SELECT sys.ML_PREDICT_ROW( JSON_OBJECT("age", "30", "job", "services", "marital", "married", "education", "secondary", "default1", "no", "balance", "7032", "housing", "no", "loan", "no", "contact", "cellular", "day", "17", "month", "jul", "duration", "402", "campaign", "1", "pdays", "-1", "previous", "0", "poutcome", "unknown" "id", "0"), @confoo24, NULL); MySQL HeatWave at the service of ML Copyright @ 2024 Oracle and/or its affiliates. 75
84. SELECT sys.ML_PREDICT_ROW( JSON_OBJECT("age", "30", "job", "services", "marital", "married", "education", "secondary", "default1", "no", "balance", "7032", "housing", "no", "loan", "no", "contact", "cellular", "day", "17", "month", "jul", "duration", "402", "campaign", "1", "pdays", "-1", "previous", "0", "poutcome", "unknown" "id", "0"), @confoo24, NULL); { "id": 0.0, "age": 30.0, "day": 17.0, "job": "services", "loan": "no", "month": "jul", "pdays": -1.0, "balance": 7032.0, "contact": "cellular", "housing": "no", "marital": "married", "campaign": 1.0, "default1": "no", "duration": 402.0, "poutcome": "unknown", "previous": 0.0, "education": "secondary", "Prediction": "no", "ml_results": {"predictions": {"y": "no"}, "probabilities": { "no": 0.8799999952316284, "yes": 0.11999999731779099}} } MySQL HeatWave at the service of ML Copyright @ 2024 Oracle and/or its affiliates. 75
85. MySQL HeatWave Database Service Migration Copyright @ 2024 Oracle and/or its affiliates. 76
86. 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. 77
87. 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. 78
88. 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. 79
89. 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. 80
90. Migration to MySQL HeatWave Service in OCI VCN 10.0.0.0/16 Local Network 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.instanceDump() util.load() 1 2 Copyright @ 2024 Oracle and/or its affiliates. 81
91. 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. 82
92. 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. 83
93. Migration to MySQL HeatWave Service in OCI Copyright @ 2024 Oracle and/or its affiliates. 84
94. Migration to MySQL HeatWave Service in OCI Copyright @ 2024 Oracle and/or its affiliates. 85
95. Migration to MySQL HeatWave Service in OCI VCN 10.0.0.0/16 Local Network 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 public IP Oracle Cloud Infrastructure On Premise MySQL HeatWave Database Service Region: us-ashburn-1 Internet Gateway REPLICATION Copyright @ 2024 Oracle and/or its affiliates. 86
96. MySQL HeatWave Database Service Integration with MySQL Shell for Visual Studio Code Copyright @ 2024 Oracle and/or its affiliates. 87
97. 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. 88
98. MySQL Shell the Companion for MySQL HeatWave Service Copyright @ 2024 Oracle and/or its affiliates. 89
99. 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. 90
100. MySQL Shell the Companion for MySQL HeatWave Service Copyright @ 2024 Oracle and/or its affiliates. 91
101. MySQL HeatWave Database Service What's next ? Copyright @ 2024 Oracle and/or its affiliates. 92
102. What's next in MySQL HeatWave ? Copyright @ 2024 Oracle and/or its affiliates. 93
103. What's next in MySQL HeatWave ? Generative AI and vector store Copyright @ 2024 Oracle and/or its affiliates. 93
104. What's next in MySQL HeatWave ? Generative AI and vector store MySQL Autopilot indexing Copyright @ 2024 Oracle and/or its affiliates. 93
105. What's next in MySQL HeatWave ? Generative AI and vector store MySQL Autopilot indexing JavaScript support in the server (store procedure and functions with GraalVM) Copyright @ 2024 Oracle and/or its affiliates. 93
106. What's next in MySQL HeatWave ? Generative AI and vector store MySQL Autopilot indexing JavaScript support in the server (store procedure and functions with GraalVM) and much more to some, stay tuned ! Copyright @ 2024 Oracle and/or its affiliates. 93
107. Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 94
108. 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. 95
109. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 96