What’s New with MySQL & MySQL HeatWave - Announcements at Oracle CloudWorld Las Vegas in September 2023

What’s New with MySQL & MySQL HeatWave Olivier Dasini MySQL
Cloud Principal Solutions Architect EMEA [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini October 2023 Announcements at Oracle CloudWorld Las Vegas in September 2023

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

4 MySQL HeatWave Overview

5 MySQL is the #1 Open Source Database https://db-engines.com/en/ranking

6 Release GA Date Premier Support End Extended Support End Sustaining Support End MySQL 5.0 Oct 2005 Dec 2011 N/A Indefinite MySQL 5.1 Dec 2008 Dec 2013 N/A Indefinite MySQL 5.5 Dec 2010 Dec 2015 Dec 2018 Indefinite MySQL 5.6 Feb 2013 Feb 2018 Feb 2021 Indefinite MySQL 5.7 Oct 2015 Oct 2020 Oct 2023 Indefinite MySQL 8.0 Apr 2018 Apr 2025 Feb 2026 Indefinite Tempos fugit! http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf Why Upgrade ? - Support Life Cycle

7 MySQL Versions Release Cadence https://blogs.oracle.com/mysql/post/introducing-mysql-innovation-and-longterm-support-lts-versions MySQL Innovation and Long-Term Support (LTS) versions

8 MySQL Innovation and Long-Term Support (LTS) versions • We are now transitioning to a versioning model where you can choose between 2 kinds of releases: – Innovation – Long-Term Support (LTS) • Both Innovation and LTS are production-grade quality • If you desire access to the latest features and improvements and enjoy staying on top of the latest technologies, the MySQL Innovation releases might be the best fit for you – These releases are ideal for developers and DBAs working in fast-paced development environments with high levels of automated tests and modern continuous integration techniques for faster upgrade cycles • On the other hand, if your environment requires established behavior, then the LTS releases are the way to go – These releases contain only necessary fixes, so they reduce the risks associated with changes in the database software's behavior https://blogs.oracle.com/mysql/post/introducing-mysql-innovation-and-longterm-support-lts-versions

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 Cloud Services (PaaS) MySQL HeatWave Databases Services (for OLTP) MySQL HeatWave (for Analytics) MySQL HeatWave AutoML (for Machine Learning) MySQL HeatWave on AWS MySQL HeatWave on Azure MySQL HeatWave Lakehouse Community, Enterprise, Cloud Service (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 a.k.a MDS: (OLTP) General Purpose Optimized for OLTP MySQL HeatWave Lakehouse (Lakehouse) MySQL Heatwave (Analytics) OLTP + Analytics (OLAP) + Machine Learning InnoDB Lakehouse InnoDB RAPID InnoDB RAPID

12 MySQL HeatWave Optimized for data warehouse , machine learning, and OLTP

13 *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

14 See documented performance comparisons that show how HeatWave is 6.5X faster than Amazon Redshift at half the cost, 1400X faster than Amazon Aurora at half the cost, and 5400X faster than Amazon RDS for MySQL at two-thirds the cost 30TB TPCH, MySQL HeatWave is faster, cheaper & easier to use than all the competitive database services MySQL HeatWave performance and price comparison www.oracle.com/mysql/heatwave/performance

15 MySQL HeatWave Lakehouse Process data in object store and transactional database  Determines right cluster size for processing object store data  Estimates load time  Auto schema inference Scales from 16 GB to 512 TB

16 MySQL HeatWave can process data from multiple data sources e.g. Oracle Golden Gate, ... OCI Object Storage AWS Aurora AWS Redshift Data can be in a file or other databases → No requirement to have data in MySQL https://www.mysql.com/products/mysqlheatwave/lakehouse

17 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

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

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 • 25x faster than Redshift ML • Explainable • No additional cost Time-series forecasting 19 Copyright © 2023, Oracle and/or its affiliates Training, inference, explanation with HeatWave AutoML

Copyright © 2023, Oracle and/or its affiliates Comparison with other
cloud database ML solutions 20

21 End to end support for ML in MySQL HeatWave Social ECommerce FinTech SaaS InnoDB HeatWave OLTP OLAP ML Tools Machine Learning Autopilot Lakehouse Database Exports MySQL HeatWave Analytics tools Database

22 45 regions in 23 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 And also Cloud @Customer & EU Soveriegn Cloud 100% renewable energy by 2025 August 2023 https://www.oracle.com/cloud/public-cloud-regions

23 Oracle Cloud Infrastructure Europe Locations MySQL HeatWave Databases Service(s) is/are part of all of them https://www.oracle.com/cloud/public-cloud-regions August 2023

24 MySQL HeatWave is available in multiple clouds Optimized for price performance in each cloud

25 MySQL HeatWave on AWS • MySQL HeatWave runs natively on AWS, optimized for AWS infrastructure • Data doesn’t leave AWS – saves egress cost, and avoids compliance approvals • Lowest latency access to MySQL HeatWave • Tight integration with the AWS ecosystem – S3, CloudWatch, PrivateLink • Easier migration from other databases (e.g., Amazon Aurora, Redshift, Snowflake) OCI and AWS Regions – August 2023 Create & manage a MySQL DB System with a HeatWave Cluster to use with AWS applications https://dev.mysql.com/doc/heatwave-aws/en

26 MySQL HeatWave on Azure Connecting to MySQL HeatWave on OCI from Azure VNET • Familiar Azure-native user experience • Automated identity, networking, and monitoring integration • Private interconnect and networking with < 2 ms latency • Use Microsoft Azure services with MySQL HeatWave • Collaborative support https://www.oracle.com/cloud/azure/oracle-database-for-azure

27 • Fully managed MySQL database service for OLTP, analytics, machine learning, object store • Helps both MySQL and non-MySQL workloads • Best performance and price performance for analytics • Fully automated in-database machine learning • MySQL Autopilot automates data management • Available in OCI, AWS, Azure MySQL HeatWave Summary

28 High Availability & Disaster Recovery Complete native HA & DR solutions for MySQL

29 MySQL Group Replication + Router + Shell = InnoDB Cluster MySQL InnoDB Cluster • MySQL Group Replication: Automatic membership changes, network partition handling, consistency... • MySQL Shell Conﬁguring, Adding, Removing members • MySQL Router to route application traffic • InnoDB CLONE to automatically provision members, fully integrated in InnoDB • RPO = 0 • RTO = seconds A complete High Availability solution for MySQL

30 MySQL InnoDB ClusterSet .High Availability (Failure Within a Region) • RPO = 0 • RTO = seconds (automatic failover) Disaster Recovery (Region Failure) • RPO != 0 • RTO = minutes or more (manual failover) • No write performance impact Features • Easy to use • Familiar interface and usability mysqlsh, CLONE, ... • Add/remove nodes/clusters online • Router integration, no need to reconfigure application if the topology changes A complete Disaster Recovery solution for MySQL

31 MySQL InnoDB Cluster Read Replicas  From MySQL 8.1.0 with MySQL InnoDB Cluster Read Replicas, it's now possible to add asynchronous replicas to the database topology, to be used to offload read traffic away from primary or secondaries – To have dedicated read replicas, special purpose read replicas (e.g. for reporting) – To scale beyond what the secondaries can handle by adding multiple read replicas  MySQL InnoDB Cluster handles the operation of these replicas, including automatically redirecting traffic by MySQL Router, MySQL Shell does configuration, initial provisioning, the replication configuration and any topology changes desired  Read Replicas also work with MySQL InnoDB ClusterSet, where Replicas can be added to both primary and secondary clusters  MySQL Router is aware of those Read Replicas and will redirect read traffic to them  Those read replicas will replicate from either the primary or one of the secondaries and will automatically reconnect to another member if replication breaks or any membership changes happened (e.g. new primary was elected) Offload read workloads https://blogs.oracle.com/mysql/post/announcing-mysql-innodb-cluster-read-replicas

32

33 MySQL Shell Interface for Development & Administration of MySQL

34 MySQL Shell Overview MySQL Server 5.7 MySQL 8.0 Upgrade Checker Prompt Themes Auto Completion & Command History MySQL Server 8.0 Document Store X Dev API SQL CLI Output Formats (Table, JSON, Tabbed) Batch Execution JavaScript Python SQL importJSON Dump Utilities InnoDB Cluster / ReplicaSet / ClusterSet etc... Interface for Development and Administration of MySQL

35 • MySQL Shell integrated into Visual Studio Code (still preview mode) • Visual Studio Code is the mostpopular IDE with developers https://marketplace.visualstudio.com/items?itemName=Oracle.mysql-shell-for-vs-code MySQL Shell for VS Code (preview)

36 MySQL REST Services Publish RESTful Web Services for interacting with the data stored in MySQL Public preview

37 MySQL REST Service (preview) : Architecture Fast, Secure HTTPS Access for your MySQL Data https://blogs.oracle.com/mysql/post/introducing-the-mysql-rest-service

38 MySQL REST Service (MRS) Fast, Secure HTTPS Access for your MySQL Data

39 Generative AI and vector store Interact with MySQL HeatWave in natural language Private preview

40 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

41 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

44 Machine Learning on HeatWave Lakehouse Use HeatWave AutoML to perform ML operations on data loaded from object storage

45 HeatWave AutoML also supports Lakehouse files Training, inference and explanations on data in object store https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave

46 Lakehouse support https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave  Build ML models on data in object store or the database  Use the same APIs as used for data in the database  No additional cost – reuse the same HeatWave Cluster

47 JavaScript support in MySQL HeatWave Write stored routines in JS and execute them inside MySQL HeatWave Lim ited Availability

48 JavaScript support in MySQL HeatWave Simplify the execution of complex operations https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave

49 JavaScript Stored Programs in MySQL HeatWave Queries that call stored procedures written in JavaScript can be accelerated by HeatWave https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave CREATE FUNCTION construct_url (path VARCHAR(50), search VARCHAR(20)) RETURNS VARCHAR(100) LANGUAGE JAVASCRIPT AS $$ let url = `${path}${search && !search.startsWith('?') ? '?' : ''}${search ?? ''}`; return encodeURI(url); $$ CREATE PROCEDURE update_item_urls(OUT url_count INT) LANGUAGE JAVASCRIPT AS $$ let result = mysql.getSession().runSql( `UPDATE my_table SET url = construct_url(path, CONCAT('item=',product)) WHERE product IS NOT NULL` ); url_count = result.getAffectedItemsCount(); $$ SELECT construct_url('/page', 'query=шел лы'); /page?query=%D1%88%D0%B5%D0%BB%D0%BB%D1%8B JavaScript stored programs are first-class objects in MySQL HeatWave – simplify the execution of complex operations  Seamless MySQL ↔ JavaScript type conversion for input / output arguments  Can be used anywhere a SQL stored function can be used – e.g. SELECT, WHERE, ORDER BY  Support for DML, DDL, Views  Existing XDevAPI used to execute SQL inside JavaScript

50 MySQL Autopilot Indexing Optimizes the DB for better performance & reduced cost by making recommendations to create and drop indexes Lim ited Availability

51 Auto Scheduling Auto Change Propagation Auto Query Time Estimation Auto Query Plan Improvement NEW: Adaptive Query Execution Auto Thread Pooling Auto Error Recovery NEW: Autopilot indexing Auto Parallel Load Auto Data Placement Auto Encoding NEW: Auto Unload NEW: Auto Compression Adaptive Data Flow Auto Provisioning Auto Shape Prediction Auto Schema Inference Adaptive Data Sampling Workload-aware ML-powered automation Increases productivity and helps eliminate human errors

52 MySQL Autopilot Indexing https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave MySQL Autopilot Indexing features  Considers both query and DML performance  Recommends CREATE and DROP of indexes  Generates DDLs for index creation/drop  Provides performance prediction  Provides storage prediction  Provides explanation for the recommendations Benefits of ML automation with MySQL HeatWave  Customize index recommendations based on individual workloads  Predicts expected improvement without creating the indexes  Provides explanations  Continuous learning and adapting  Oriented by performance objectives: throughput, latency, storage Eliminate the tedious activities related to effective indexing

53 And also ...

54 • Native JSON Support in HeatWave (limited availability) – Efficiently store and process JSON data • JSON acceleration with MySQL HeatWave – Accelerate performance by several orders of magnitude • New analytic operators – With support for new analytic operators including CUBE, HyperLogLog, Qualify, ... • HeatWave Lakehouse is now available on AWS – Enable query data in Amazon S3 object storage • Support private connections via AWS PrivateLink – Databases are isolated from the public internetDatabases are isolated from the public internet • HeatWave AutoML now supports text columns in the database – Text can of type – TINYTEXT, TEXT, MEDIUMTEXT or LONGTEXT • ... Even more new features https://www.oracle.com/news/announcement/ocw-mysql-heatwave-vector-store-generative-ai-2023-09-20

55 • MySQL HeatWave is a fully managed database service that provides, in a single cloud database service: – an online transaction processing (OLTP) database, – a real-time in-memory data warehouse, – in-database automated machine learning, – lakehouse, – and Generative AI (in private preview) capabilities • MySQL HeatWave provides the best performance and price performance in the industry for analytics processing in both data warehouse and lakehouse environments. MySQL HeatWave

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 56 Copyright © 2023, Oracle and/or its affiliates

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

Contribution to MySQL Become part of the contributor community for
the MySQL open-source project, https://forums.oracle.com/ords/apexds/post/contributing-code-to-mysql-8037 • What Contributor should have • A wish to change/fix something in MySQL or have a new feature • Downloaded MySQL source code http://dev.mysql.com/downloads/ • An account in bugs.mysql.com http://bugs.mysql.com or • Working GitHub account https://github.com • Signed Oracle Contribution Agreement (OCA) https://oca.opensource.oracle.com/ • OCA Is a short legal agreement which protects both you as a contributor and Oracle from legal attack. By signing the OCA, you agree that Oracle is legally allowed to use your code in Oracle software and that the code is, to the best of your knowledge, unencumbered by any patent issues Copyright © 2023, Oracle and/or its affiliates

Oracle Operates MySQL at Cloud Scale to Deliver Innovations Faster
Copyright © 2023, Oracle and/or its affiliates High Availability Security Backup Patching AutoPilot Analytics Machine Learning Lakehouse Operate MySQL at Cloud Scale Oracle operates MySQL HeatWave across 41 public cloud regions in 22 countries, providing deep insights into product requirements. Multi-cloud operations across OCI, AWS and Azure increase intelligence. Improve MySQL Database Knowledge gained from operating MySQL HeatWave at scale results in improvements to MySQL Database including High Availability, Security, Analytics, Machine Learning, and more.

“Oracle announced MySQL HeatWave with Autopilot last August, which may
very well have been the single greatest innovation in open source cloud databases in the last 20 years to that point. Now Oracle has gone beyond its original unifying of OLTP and OLAP in HeatWave, with MySQL HeatWave ML. Oracle is bringing all of the machine learning processing and models inside the database, so that customers not only avoid managing ML databases apart from the core database, but also eliminate the hassles of ETL, gaining speed, accuracy, and cost-effectiveness in the bargain.” “This latest announcement from Oracle is the third major release of MySQL HeatWave in just over 12 months. Oracle has delivered more cloud database innovations during that timeframe than most cloud database vendors have delivered in the last decade. Not only does the in-database HeatWave ML make Redshift ML look like yesterday’s tech in terms of engineering, performance and cost, but the latest MySQL HeatWave TPC-DS benchmarks demonstrate that Amazon Redshift, Snowflake, Azure Synapse and Google BigQuery are all slower and more expensive. It’s rather clear who’s innovating in cloud databases and who’s being complacent.” Feedback from analysts Copyright © 2023, Oracle and/or its affiliates

What’s New with MySQL & MySQL HeatWave - Announcements at Oracle CloudWorld Las Vegas in September 2023

What’s New with MySQL & MySQL HeatWave - Announcements at Oracle CloudWorld Las Vegas in September 2023

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript