MySQL HeatWave - Supercharge your cloud workloads with a unified OLTP, Analytics & ML database

MySQL HeatWave Lyon 18/04/2024 Supercharge your cloud workloads with a
unified OLTP , Analytics & ML database Olivier Dasini Cloud Solutions Architect @ Oracle MySQL [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Slides : https://speakerdeck.com/freshdaz

Copyright © 2024, Oracle and/or its affiliates. All rights reserved.
2 Me, Myself & I 2 Olivier DASINI  MySQL Geek  Enjoying databases for 20+ years  Addicted to MySQL for 15+ years  MySQL Writer, Blogger and Speaker  Also former : DBA, Consultant, Architect, Trainer, ...  Cloud Solutions Architect at Oracle MySQL  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/  Slides: https://speakerdeck.com/freshdaz

3 Agenda 1. Oracle Cloud Infrastructure - Designed to run any application, faster & more securely, for less 2. MySQL HeatWave – One Database for OLTP, OLAP, ML & Lakehouse – Analytics - In-Memory Query Accelerator – Lakehouse - Fast analytics across databases and object storage – Machine Learning - Fully automated in-database machine learning 3. Generative AI and Vector Store - Interact with MySQL HeatWave in natural language 3

4 Oracle Cloud Infrastructure Designed to run any application, faster and more securely, for less 4

5 48 regions in 24 countries including Paris & Marseille; 12 Azure Interconnect Regions Oracle Cloud Infrastructure Global Locations MySQL HeatWave Databases Service(s) is/are part of all of them MySQL HeatWave Databases Service(s) is/are part of all of them And also Cloud @Customer & EU Soveriegn Cloud 100% renewable energy by 2025 January 2024 https://www.oracle.com/cloud/public-cloud-regions

6 BUSINESS ANALYTICS Analytics Cloud, Fusion Analytics Analytics SERVERLESS Events, Functions, API Gateway BUSINESS & INDUSTRY SaaS ERP, HCM, SCM, Sales, Marketing, Service, Vertical Industry APP INTEGRATION Integration Cloud, Workflow, Notifications, Email Delivery Applications Open Source DBs & Others MySQL MySQL, NoSQL, Postgres, Search Indexing, Distributed Cache ORACLE DBs ATP, ADW, DBCS VM/BM, JSON, Dedicated, Exadata, Exadata C@C Databases 40 + COMMERCIAL REGIONS / GOV REGIONS / CLOUD@CUSTOMER MESSAGING Streaming, Queueing, Service Connector BIG DATA Big Data, Data Flow, Data Integration, Data Catalog, Golden Gate AI SERVICES Data Science, Text Analytics, Vision Anomaly Detection Data & AI OS, VMWARE Autonomous Linux, OS Mgmt Service, Marketplace COMPUTE Bare metal, VM, CPUs, GPUs, HPC CONTAINERS Containers, Kubernetes, Service Mesh, Registry NETWORKING VCN, LB, Service Gateway, FC, VPN, Cluster Networking STORAGE NVMe, Block, File, Object, Archive, Data Transfer Core Infrastructure OBSERVABILITY Monitoring, Logging, Logging Analytics, Notifications, Events, Operations Insights, APM, Management Cloud CLOUD OPS IAM, Compartments, Tagging, Console, Cost Advisor SECURITY Cloud Guard, Security Zones, Vault, KMS, Data Safe, DDoS, WAF Governance & Administration INFRASTRUCTURE as CODE Resource Manager, Terraform, Ansible LOW CODE APEX, Digital Assistant APPDEV Visual Builder Studio, GraalVM, Helidon, SQL Developer, Shell, APIs/CLI/SDKs/Docs Developer services Complete cloud capabilities

7 Compare OCI with AWS, Azure, and GCP • Find the service you’re looking for with OCI https://www.oracle.com/cloud/service-comparison/ https://www.oracle.com/cloud/service-comparison/

8 MySQL HeatWave One Database for OLTP, OLAP , ML & Lakehouse

9 The MySQL universe - The view from the moon… MySQL Commercial/Enterprise MySQL Community + MySQL Enterprise Backup MySQL Enterprise Monitor MySQL Enterprise Authentication MySQL Enterprise Audit MySQL Enterprise TDE MySQL Enterprise Masking MySQL Enterprise Firewall MySQL Technical Support … MySQL Cluster CGE MySQL Cluster NDB + MySQL Enterprise + MySQL Cluster Manager MySQL Community MySQL Server MySQL Client, Workbench MySQL Shell MySQL GR plugin & InnoDB Cluster & Router MySQL Operator for Kubernetes MySQL Connector (C API, Java, Node.js, others) MySQL Support for MS VS Code (Preview) … MySQL Cluster NDB MySQL NDB Storage Engine MySQL NDB Operator for Kubernetes MySQL HeatWave (Cloud Services) MySQL HeatWave Databases Services (for OLTP) MySQL HeatWave (for Analytics) MySQL HeatWave Lakehouse MySQL HeatWave AutoML (for Machine Learning) MySQL HeatWave on AWS MySQL HeatWave on Azure Community, Enterprise, Cloud Services (HeatWave) https://www.mysql.com/products

10 MySQL HeatWave Cloud Service 100% developed, managed, and supported by the MySQL team Automation MySQL HeatWave Database High Availability Read Replicas Backup Query Acceleration AutoPilot AutoML Security Patch & Upgrade Provision & Configure OS OS Security Patch & Upgrade OS Installation Server Hardware Provisioning & Maintenance Storage Storage Provisioning & Maintenance Data Center Rack & Space Power, HVAC, Networking

11 … the MySQL Cloud services made by the MySQL Team The MySQL HeatWave “Family” “MySQL HeatWave” MySQL HeatWave for OLTP (OLTP) General Purpose Optimized for OLTP MySQL HeatWave Lakehouse (Lakehouse) OLTP + Analytics (OLAP) + Machine Learning MySQL HeatWave Analytics (Warehouse) OLTP + Analytics (OLAP) + Machine Learning InnoDB Lakehouse InnoDB RAPID InnoDB RAPID

12 Analytics In-Memory Query Accelerator

13 MySQL HeatWave - MySQL in-Memory Query Accelerator Architecture

14 *Benchmark queries are derived from the TPC-H benchmark, but results are not comparable to published TPC-H benchmark results since they do not comply with the TPC-H specification 400Gb, 64 cores MySQL HeatWave dramatically speeds up analytic queries

15 MySQL HeatWave Optimized for Data Warehouse , Machine Learning, and OLTP

16 Already lowest cost in industry for data warehouse TPC-H 10TB price performance comparison 13x better than Redshift 28x better than Snowflake 28x better than BigQuery 62x better than Databricks 3 year reserved, paid upfront Standard Edition 1 year reserved 1 year reserved Benchmark queries are derived from the TPC-H benchmarks, but results are not comparable to published TPC-H benchmark results since these do not comply with the TPC-H specifications. https://www.oracle.com/mysql/heatwave/performance/#heatwave-on-oci

17 Lakehouse Fast analytics across databases and object storage

18 MySQL HeatWave Lakehouse Process data in object store and transactional database  Process and query 100’s of TB of data in the object store — in a variety of file formats  Query data across MySQL, the object store, or both—using standard MySQL commands  Querying the database is as fast as querying the object store Scales from 16 GB to 512 TB

19 InnoDB mysql> CREATE TABLE Sensor (date DATE, degree INT) ENGINE=DATALAKE ENGINE_ATTRIBUTE = ‘{“dialect”:{“format”:”csv”}, “file”: [{“prefix”:”temp_sensor_1.csv”}]}’ SECONDARY_ENGINE=RAPID; mysql> ALTER TABLE Sensor SECONDARY_LOAD; mysql> SELECT count(*) FROM Sensor, SALES WHERE Sensor.degrees >30 and Sensor.date=SALES.date; Object Store Devices Social Voice Sensors Events Process and query 100’s of TB of data in the object store — in a variety of file formats MySQL HeatWave Lakehouse NEW

20 Provides flexibility to develop applications on object store without any performance, cost impact Same price-performance when data inside MySQL or in object store HeatWave HeatWave Lakehouse Snowflake Redshift Google Big Query Databricks 0 10 20 30 40 50 60 70 80 90 100 1.5 1.5 41.9 20.2 41.4 92.5 10TB TPC-H Price-Performance Price-Performance (cents) • 10 HeatWave Nodes, X-Large cluster for Snowflake; 10 nodes of ra3.4xlarge for Redshift; 800 slots for Google BigQuery; Large cluster for Databricks • Standard edition price for Snowflake; 3 yr upfront price for Redshift; 1 year reserved price for Google BigQuery and Databricks https://www.oracle.com/mysql/heatwave/performance/#heatwave-lakehouse

21 Machine Learning Fully automated in-database machine learning

22 In-database machine learning with MySQL HeatWave AutoML Accelerate ML initiatives, increase security, and reduce costs

HeatWave AutoML automates the ML lifecycle & all models can
be explained Dataset Data preprocessing Algorithm selection Adaptive sampling Feature selection Hyper-parameter tuning Tuned model Model explainer Prediction explainer Regulatory compliance Fairness Repeatability Causality Trust HeatWave ML leverages Oracle AutoML technology to automate the process of training a machine learning model https://dev.mysql.com/doc/heatwave/en/heatwave-machine-learning.html Copyright © 2024, Oracle and/or its affiliates 23

24 MySQL HeatWave AutoML uses a set of SQL routines Machine Learning with MySQL HeatWave is so simple • You only need to use a limited set of SQL routines: ✔ ML_TRAIN: Trains a machine learning model for a given training dataset ✔ ML_PREDICT_ROW: Makes predictions for one or more rows of data ✔ ML_PREDICT_TABLE: Makes predictions for a table of data ✔ ML_EXPLAIN_ROW: Explains predictions for one or more rows of data ✔ ML_EXPLAIN_TABLE: Explains predictions for a table of data ✔ ML_SCORE: Computes the quality of a model ✔ ML_MODEL_LOAD: Loads a machine learning model for predictions and explanations ✔ ML_MODEL_UNLOAD: Unloads a machine learning model • In addition, with MySQL HeatWave ML, there is no need to move or reformat your data • Data and machine learning models never leave the MySQL Database Service, which saves you time and effort while keeping your data and models secure

25 Classification Classify warranty claims Identify similar users Recommend movies Recommender System Loan default prediction Predict flight delay Rain fall prediction Regression Predict Advt spend ROI Demand forecasting Anomaly Detection Detect anomalous credit card spend Identify game hacker Fully automated in-database machine learning • In-database • Secure • Fully automated training • 25x faster than Redshift ML • Explainable • No additional cost Time-series forecasting Training, inference, explanation with HeatWave AutoML

26 MySQL HeatWave - OLTP + OLAP + ML Example : Loan Approval

27 Generative AI and Vector Store Interact with MySQL HeatWave in natural language

28 Generative AI with MySQL HeatWave vector store  Users can query and retrieve information in natural language  Efficient searching of documents in HeatWave Lakehouse Users can interact with MySQL HeatWave in natural language https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave Private preview

29 Vector store provides context to LLM for more relevant results Users can interact with MySQL HeatWave in natural language https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave

30 Vector store provides context to LLM for more relevant results Users can interact with MySQL HeatWave in natural language https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave Private preview

31 Vector store provides context to LLM for more relevant results Users can interact with MySQL HeatWave in natural language https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave Private preview

32 MySQL HeatWave Social ECommerce FinTech SaaS InnoDB HeatWave OLTP OLAP ML Tools Machine Learning Autopilot Lakehouse Database Exports MySQL HeatWave Analytics tools Database One Database for Transactional, Data Warehouse, Lakehouse & Machine Learning

Get $300 in credits and try free for 30 days
Get started with MySQL HeatWave oracle.com/mysql/free Learn more about MySQL HeatWave oracle.com/mysql Request a guided workshop Ask your account manager 34 Copyright © 2024, Oracle and/or its affiliates

Merci! Q&R Olivier Dasini Cloud Solutions Architect @ Oracle MySQL
[email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Twitter : @freshdaz

38 MySQL Autopilot: machine learning-powered automation Workload aware automation for analytics, OLTP and Lakehouse Workload aware automation for analytics, OLTP and Lakehouse

39 • MySQL Autopilot indexing (limited availability) – Helps customers eliminate the time-consuming tasks of creating optimal indexes for their OLTP workloads and maintaining those over time as workloads evolve. MySQL Autopilot automatically determines the indexes customers should create or drop from their tables to optimize their OLTP throughput, using machine learning to make a prediction based on individual application workloads. In addition, Autopilot indexing predicts the expected improvement with the recommended indexes without creating those indexes and without incurring compute or storage overhead on the users’ tenancy • Auto compression – Helps customers determine the optimal compression algorithm for each column, which improves load and query performance with faster data compression and decompression. By reducing memory usage, customers can cut costs by up to 25 percent • Adaptive query execution – Helps customers optimize the execution plan of a query after the query has started to execute, improving the performance of ad hoc queries by up to 25 percent. It uses information obtained from the partial execution of the query to adjust data structures and system resources and then independently optimizes query execution for each HeatWave node based on actual data distribution at run time • Auto load and unload – Autopilot automatically loads the columns being used in an application workload to HeatWave and automatically unload tables that were never or rarely queried. This helps free up memory and reduce costs for customers, without having to manually perform this task NEW MySQL Autopilot: machine learning-powered automation Help improve performance and scalability without requiring database tuning expertise Help improve performance and scalability without requiring database tuning expertise

MySQL HeatWave - Supercharge your cloud workloa...

MySQL HeatWave - Supercharge your cloud workloads with a unified OLTP, Analytics & ML database

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript