Tech: Project Kit
Applications with MySQL HeatWave
Technical Presentation
Alexandre Fagundes
[Link]@[Link]
Cloud Architect, Oracle Latin America
MySQL: An extremely popular database
Copyright © 2024, Oracle and/or its affiliates
MySQL is the #1 Open Source Database
Copyright © 2024, Oracle and/or its affiliates
MySQL is the most popular database for developers
Which databases have you used in the last 12 months?
MySQL 51%
PostgreSQL 38%
MongoDB 27%
Redis 26%
SQLite 25%
MS SQL Server 18%
Jetbrains survey 2023
Copyright © 2024, Oracle and/or its affiliates
Innovative organizations across many industries run MySQL – Do you?
Social E-Commerce Tech Finance Manufacturing
Copyright © 2024, Oracle and/or its affiliates
MySQL powers Open Source applications
Custom Apps Development Content management and Learning platforms
eCommerce
Copyright © 2024, Oracle and/or its affiliates
Challenges for developers and DBAs…
…And how MySQL HeatWave uniquely addresses them
Copyright © 2024, Oracle and/or its affiliates
What we’ve heard from customers
We provide one MySQL cloud database service for
Complex and costly to use separate
OLTP and real-time analytics across data warehouses
systems for transactions and analytics
and data lakes—without ETL duplication
We provide automated in-database ML with an
Want to leverage ML and generative AI explanation of models and results, without ETL, plus
on all their data generative AI with Vector store (private preview)
We provide a fully managed database service with
Spend too much time on manual
machine learning-powered automation and built-in
management tasks
advanced security features
Want to use multiple clouds MySQL HeatWave is available on OCI, AWS, Azure, and
in your data center
Copyright © 2024, Oracle and/or its affiliates
MySQL is optimized for OLTP, not designed for analytic processing
OLTP DB
Separate analytics database
OLTP Applications
Complex ETL
ETL Service No real-time analytics
Security & compliance risks
OLAP DB
Increased costs
OLAP Applications
Copyright © 2024, Oracle and/or its affiliates
MySQL HeatWave overview
Transactions, real-time analytics across data warehouse and data lake, and machine learning in one database service
Social, eCommerce, gaming, healthcare, fintech, IoT apps. Analytics and ML tools.
Queries Results
MySQL HeatWave Object Store
Database
Analytics Autopilot exports
OLTP In-database ML
Data remains in object store,
processing is done in HeatWave
For both non-MySQL and MySQL workloads
Copyright © 2024, Oracle and/or its affiliates
One database is better than two
One service for OTLP & OLAP
No ETL duplication
Transactional
applications
Unmatched performance, at a
fraction of the cost
Analytics tools and InnoDB HeatWave
applications storage analytics Real-time analytics
engine cluster
Improved security
Applications work without changes
1>2 with MySQL HeatWave
Copyright © 2024, Oracle and/or its affiliates
In-Memory hybrid columnar processing
Copyright © 2024, Oracle and/or its affiliates
Massively parallel architecture
• High-fanout partitioning
• Machines & CPU cores can further process partitioned data in parallel
• Optimized for cache size and memory hierarchy of underlying hardware
13 Copyright © 2024, Oracle and/or its affiliates
Overlap compute with communication
Copyright © 2024, Oracle and/or its affiliates
Backups
Manual or Automatic
• Retention Period
• When to Backup
• Full or Incremental
• Point-in-Time Recovery (only non-HA DB Systems)
Copyright © 2024, Oracle and/or its affiliates
High Availability
Single-click HA
Copyright © 2024, Oracle and/or its affiliates
High Availability
Single-click HA
• SLA 99.99%
• Automatic failover
• Zero Data Loss during failure event
• Option to manually switch-over
• Rolling upgrades during maintenance
Copyright © 2024, Oracle and/or its affiliates
Read Replicas
Scale Read Workloads
• Increase capacity for read-intensive workloads
• Add and remove Read Replicas for horizontal elasticity
• Easy to deploy and maintain
• Built-in Load Balancer for the read-only endpoint
• HA Cluster is ready for switchover or failover
Copyright © 2024, Oracle and/or its affiliates
Read Replicas
Single-click Read Replicas
• A single click creates a Read Replica
• Provision
• Launch
• Setup Replication
• Monitor and Manage
• Read Replicas are associated with a DB System
• RO endpoints in the DB System
• Up to 18 max per DB System
• Requires a shape of 4 OCPUs or larger
• CLI, SDK and Terraform support
Copyright © 2024, Oracle and/or its affiliates
Load Balancer
Use Your Replicas Efficiently
When using Read Replicas a Load Balancer Endpoint is automatically provisioned in your DB System.
• Managed by the service
• Materializes as a Read-Only endpoint
• Round robins traffic across Read Replicas
• Manages Read Replica backends automatically
Copyright © 2024, Oracle and/or its affiliates
Massive amount of data stored in files
• Databases are systems of record
• Files are repository for other types of data (e.g IoT, web content, log files)
• Over 80% of the data we generate is in files
• 99.5% of collected data remains unused
• Lack of time, resources, and expertise to process different data formats across different data sources
Social Voice
Devices
Object Store
Events
Sensors
Copyright © 2024, Oracle and/or its affiliates
MySQL HeatWave Lakehouse
Query half a PB data in the object store—in a variety of file formats
• Query data in MySQL, in the object
store, or across both—using standard
SQL syntax
• Up to 500 TB of data—the HeatWave
cluster scales to 512 nodes
• Querying the data in the object store
is as fast as querying the database –
an industry first!
• Scale out data processing in the
object store, data is not copied to the
MySQL Database: for both MySQL
and non-MySQL workloads
Copyright © 2024, Oracle and/or its affiliates
Very simple to query files in the object store
STANDARD SQL syntax generated by MySQL Autopilot, no human required
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 files and tables
mysql> SELECT count(*) FROM Sensor, SALES WHERE [Link] > 30 AND [Link] =
[Link];
Copyright © 2024, Oracle and/or its affiliates
Challenge #2: Organizations want to leverage
ML and generative AI with all their data
Copyright © 2024, Oracle and/or its affiliates
The pain of using AWS services
Amazon Redshift with RDS and ML Modeling
Amazon RDS
RDS to Redshift Pipeline
Utilizing Lambda, Glue, & S3
Redshift ML does NOT provide in-database ML; exports data to SageMaker via Amazon S3
Copyright © 2024, Oracle and/or its affiliates
Machine learning in action with MySQL HeatWave
OLTP Analytics
Applications Tools
Social
Real-time
Real-time ML
analytics on
recommendations
trends
ECommerce
FinTech
SaaS
Copyright © 2024, Oracle and/or its affiliates
Challenge #3: Organizations spend too much
time on manual management tasks
Copyright © 2024, Oracle and/or its affiliates
Manual management tasks consume resources
• On-premises:
• Database management: provisioning, configuration, backup, HA, patching, security & more
• Operating system management: installation, patching, upgrades...
• Infrastructure management: purchase and maintenance of servers, storage
• Data center management: space, power, cooling, disaster recovery & more
• In the cloud with a managed database service:
• Provisioning: right-sizing a database
• Data loading: optimizing load time, memory usage, encoding, data placement
• Query execution: performance tuning, prioritization of queries
• Failure handling: actions to handle an error recovery
Copyright © 2024, Oracle and/or its affiliates
MySQL HeatWave: fully managed database service
100% developed, managed, and supported by Oracle
Automation MySQL HeatWave
High Availability
Read Replicas
Backup
Query Acceleration
Database MySQL AutoPilot
HeatWave AutoML
HeatWave Lakehouse
Security Patch & Upgrade
Provision & Configure
OS Security Patch & Upgrade
OS
OS Installation
Server Hardware Provisioning & Maintenance
Storage Storage Provisioning & Maintenance
Rack & Space
Data Center
Power, HVAC, Networking
Copyright © 2024, Oracle and/or its affiliates
Machine learning-powered automation for MySQL HeatWave
High query performance at scale, higher OLTP throughput, and the best price performance
Copyright © 2024, Oracle and/or its affiliates
Auto Provisioning
Machine learning prediction of memory usage to estimate cluster size
Traditional Provisioning Flow Auto Provisioning
Provision right
Analyze DB schemas/tables cluster size
MySQL
Guess a cluster size
Iterate
HeatWave
Nodes
Adaptive Sampling
Datasets Accuracy in memory prediction 0.01%
Error %
0.1%
TPCH 1024G 98.4%
TPCDS 1024G 96.9%
Cust A 98.3%
Sample Size
Cust B 96.9%
< 0.1% of data scanned for prediction
31 Copyright © 2024, Oracle and/or its affiliates
Auto provisioning with MySQL HeatWave Lakehouse
How to determine the right cluster size required for processing data in object store?
Copyright © 2024, Oracle and/or its affiliates
Auto Shape Prediction
Determines the optimal instance type
Workload
MySQL • New statistics
Server • Intelligent sampling
InnoDB
statistics MySQL Autopilot SQL interface Predictions over time • ML models inside
the server
Query Metrics Machine learning model
• Periodic prediction
Traces/Logs
(future) Data aggregation and sampling
Shape: Shape:
MySQL 2.16 MySQL
Shape 4.32
Core Core Core Core Core Core
Heavy change Light
I/O I/O
InnoDB 16 GB InnoDB 32 GB Memory
Memory
Copyright © 2024, Oracle and/or its affiliates
Same performance with HeatWave for data in DB or in object store
Develop applications with data on object store without any performance impact
120 Query execution time: 10 TB TPC-H
100
80 1.75 minutes
Query time (seconds)
60 1.3 minutes
59 seconds
40
47 seconds
20
14 seconds 14 seconds
0
HeatWave HeatWave Snowflake Amazon Redshift Google Big Query Databricks
Lakehouse
Significantly improves flexibility and reduces costs
Configuration: MySQL HeatWave Lakehouse: 512 nodes; Snowflake: 4X-Large Cluster; Databricks: 3X-Large Cluster; Amazon Redshift: 20-ra3.16xlarge; Google BigQuery: 6400 slots
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.
Copyright © 2024, Oracle and/or its affiliates
Challenge #4: Organizations want to use
multiple clouds
Copyright © 2024, Oracle and/or its affiliates
MySQL HeatWave is optimized for multiple clouds
Maximum flexibility and choice
Optimized for best price performance in each cloud
Copyright © 2024, Oracle and/or its affiliates
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)
Combine 5 AWS services into ONE
Copyright © 2024, Oracle and/or its affiliates
Roadmap of capabilities on OCI and AWS
As of March 2024 - [Link]
Key capabilities Available on OCI Available on AWS
Fully managed service
OLTP and OLAP in MySQL
Query acceleration for analytics and mixed workloads
Machine learning-powered automation (MySQL Autopilot for HeatWave and OLTP)*
Advanced security*
In-database machine learning (HeatWave AutoML)
Scale-out data management
Interactive query and data management console Coming soon
Performance and workload monitoring from the console Coming soon
Interactive MySQL HeatWave AutoML console Coming soon
Adding HeatWave to any MySQL shape Coming soon
MySQL HeatWave Lakehouse Limited availability
* Auto thread pooling and auto shape prediction in MySQL Autopilot as well as the MySQL HeatWave database firewall will be available soon on OCI.
Copyright © 2024, Oracle and/or its affiliates
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
[Link]
Copyright © 2024, Oracle and/or its affiliates
Enabling hybrid deployments
OLTP on-premises, OLAP in the cloud
Copyright © 2024, Oracle and/or its affiliates
DB system Inbound and Outbound Replication
Cross-region replication
• DB System to DB System
Live Migrations
• Minimize downtime
Hybrid deployments
• On-premises and other cloud vendors
• Disaster Recovery
• Capacity bursting
• HeatWave for Analytics, ML, Lakehouse
Copyright © 2024, Oracle and/or its affiliates
Available in your data center
OCI Dedicated Region
Self-contained cloud region
MySQL HeatWave and all Oracle public
cloud services in your data center
Public cloud economics and security
Meet data residency and latency
requirements
Copyright © 2024, Oracle and/or its affiliates
Why MySQL HeatWave for new and existing applications?
Simplicity of OLTP, real-time
analytics across DW and data Unmatched performance and Ready for the
lakes, and ML in ONE cloud price- performance distributed cloud
database service
MySQL HeatWave:
Eliminate the cost and 4X faster than Redshift, 10X better price-perf
Deploy on OCI, AWS, Azure
complexity of separate 4X faster than Snowflake, 15X better price-perf
1,400X faster than Aurora, 2,200X better price-
analytics database, lakehouse, perf Replicate data from OLTP on-
ML, and ETL services premises apps to MySQL
MySQL HeatWave Lakehouse HeatWave for analytics in the
Avoid the latency and security cloud
11X better price-performance vs Redshift
risks of data movement 15X better price-performance vs Databricks
between data stores 19X better price-performance vs Snowflake Deploy in your data center with
22X better price-performance vs BigQuery OCI Dedicated Region
MySQL and Amazon Aurora-
based applications work MySQL Autopilot: automation to achieve
without changes high query performance at scale, higher
OLTP throughput, and get the best price-
performance
Copyright © 2024, Oracle and/or its affiliates
Get started with MySQL
HeatWave
Get $300 in credits and try free for 30 days
[Link]/mysql/free
Migrate to MySQL HeatWave with free expert guidance
Migration program
Request a free MySQL HeatWave workshop
Ask your account manager
Learn more about MySQL HeatWave
[Link]/mysql
Copyright © 2024, Oracle and/or its affiliates
