Tweet
Tweet

Slide 1

Slide 1 text

Frédéric Descamps Community Manager Oracle MySQL Confoo.CA - February 2024 Discover MySQL HeatWave Database Service in OCI

Slide 2

Slide 2 text

Who am I ? about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. 2

Slide 3

Slide 3 text

@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

Slide 4

Slide 4 text

MySQL HeatWave Database Service MySQL DBaaS in Oracle Cloud Infrastructure Copyright @ 2024 Oracle and/or its affiliates. 4

Slide 5

Slide 5 text

Copyright @ 2024 Oracle and/or its affiliates. 5

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

What is MySQL HeatWave Database Service ? Copyright @ 2024 Oracle and/or its affiliates. 7

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

MySQL HeatWave Database Service - Overview Copyright @ 2024 Oracle and/or its affiliates. 9

Slide 12

Slide 12 text

MySQL HeatWave Database Service MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 10

Slide 13

Slide 13 text

MySQL HeatWave Database Service - MySQLaaS MySQL HeatWave Service is available in 32 regions on OCI: Copyright @ 2024 Oracle and/or its affiliates. 11

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 16

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

MySQL HeatWave Database Service - MySQLaaS Placement Copyright @ 2024 Oracle and/or its affiliates. 20

Slide 23

Slide 23 text

MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 21

Slide 24

Slide 24 text

MySQL HeatWave Database Service - MySQLaaS Storage and IOPS Copyright @ 2024 Oracle and/or its affiliates. 22

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

MySQL HeatWave Database Service - MySQLaaS Backups and point-in-time recovery Copyright @ 2024 Oracle and/or its affiliates. 24

Slide 27

Slide 27 text

MySQL HeatWave Database Service - MySQLaaS Extra: Deletion plan Copyright @ 2024 Oracle and/or its affiliates. 25

Slide 28

Slide 28 text

MySQL HeatWave Database Service - MySQLaaS Extra: Con guration Copyright @ 2024 Oracle and/or its affiliates. 26

Slide 29

Slide 29 text

MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 27

Slide 30

Slide 30 text

MySQL HeatWave Database Service - MySQLaaS Extra: Connections Copyright @ 2024 Oracle and/or its affiliates. 28

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

MySQL HeatWave Database Service - MySQLaaS Extra: Management By default, the Database Management Service is enabled. Copyright @ 2024 Oracle and/or its affiliates. 34

Slide 37

Slide 37 text

MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 35

Slide 38

Slide 38 text

MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 36

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

Copyright @ 2024 Oracle and/or its affiliates. 38

Slide 41

Slide 41 text

MySQL HeatWave Database Service - MySQLaaS Read Replicas Copyright @ 2024 Oracle and/or its affiliates. 39

Slide 42

Slide 42 text

MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 40

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

MySQL HeatWave Database Service - MySQLaaS Copyright @ 2024 Oracle and/or its affiliates. 42

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

MySQL HeatWave Database Service Accelerator - HeatWave Cluster Copyright @ 2024 Oracle and/or its affiliates. 44

Slide 47

Slide 47 text

Challenge: Organizations need to use separate systems for transactions and analytics Copyright @ 2024 Oracle and/or its affiliates. 45

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

MySQL HeatWave Cluster - Enable Copyright @ 2024 Oracle and/or its affiliates. 49

Slide 52

Slide 52 text

MySQL HeatWave Cluster - Enable Copyright @ 2024 Oracle and/or its affiliates. 50

Slide 53

Slide 53 text

MySQL HeatWave Cluster - Enable DB System Overview Copyright @ 2024 Oracle and/or its affiliates. 51

Slide 54

Slide 54 text

MySQL HeatWave Cluster - Enable Copyright @ 2024 Oracle and/or its affiliates. 52

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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

Slide 59

Slide 59 text

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

Slide 60

Slide 60 text

MySQL HeatWave Cluster - Example From 8min14 to 4.4 sec ! Copyright @ 2024 Oracle and/or its affiliates. 58

Slide 61

Slide 61 text

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

Slide 62

Slide 62 text

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

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

MySQL HeatWave Database Service Object Storage - Lakehouse Copyright @ 2024 Oracle and/or its affiliates. 60

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

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://......"}], "dialect": {"format": "csv", "field_delimiter": ",", "record_delimiter": "\\n"}}'; Copyright @ 2024 Oracle and/or its affiliates. 64

Slide 69

Slide 69 text

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://......"}], "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

Slide 70

Slide 70 text

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://......"}], "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

Slide 71

Slide 71 text

MySQL HeatWave Database Service Machine Learning Copyright @ 2024 Oracle and/or its affiliates. 65

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

Machine Learning at the service of HeatWave Example: MySQL HeatWave Cluster Nodes Estimation Copyright @ 2024 Oracle and/or its affiliates. 67

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

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

Slide 76

Slide 76 text

MySQL HeatWave at the service of ML Example: Load Approval Copyright @ 2024 Oracle and/or its affiliates. 70

Slide 77

Slide 77 text

MySQL HeatWave at the service of ML Traditional MySQL Data Flow Copyright @ 2024 Oracle and/or its affiliates. 71

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

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

Slide 80

Slide 80 text

Current Steps MySQL HeatWave at the service of ML Much simpler for DB developers Copyright @ 2024 Oracle and/or its affiliates. 73

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

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

Slide 85

Slide 85 text

MySQL HeatWave Database Service Migration Copyright @ 2024 Oracle and/or its affiliates. 76

Slide 86

Slide 86 text

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

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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://[email protected]', {"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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

Migration to MySQL HeatWave Service in OCI Copyright @ 2024 Oracle and/or its affiliates. 84

Slide 94

Slide 94 text

Migration to MySQL HeatWave Service in OCI Copyright @ 2024 Oracle and/or its affiliates. 85

Slide 95

Slide 95 text

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

Slide 96

Slide 96 text

MySQL HeatWave Database Service Integration with MySQL Shell for Visual Studio Code Copyright @ 2024 Oracle and/or its affiliates. 87

Slide 97

Slide 97 text

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

Slide 98

Slide 98 text

MySQL Shell the Companion for MySQL HeatWave Service Copyright @ 2024 Oracle and/or its affiliates. 89

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

MySQL Shell the Companion for MySQL HeatWave Service Copyright @ 2024 Oracle and/or its affiliates. 91

Slide 101

Slide 101 text

MySQL HeatWave Database Service What's next ? Copyright @ 2024 Oracle and/or its affiliates. 92

Slide 102

Slide 102 text

What's next in MySQL HeatWave ? Copyright @ 2024 Oracle and/or its affiliates. 93

Slide 103

Slide 103 text

What's next in MySQL HeatWave ? Generative AI and vector store Copyright @ 2024 Oracle and/or its affiliates. 93

Slide 104

Slide 104 text

What's next in MySQL HeatWave ? Generative AI and vector store MySQL Autopilot indexing Copyright @ 2024 Oracle and/or its affiliates. 93

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

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

Slide 107

Slide 107 text

Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 94

Slide 108

Slide 108 text

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

Slide 109

Slide 109 text

Questions ? Copyright @ 2024 Oracle and/or its affiliates. 96