you need to know about MySQL 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 May 25, 2023
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
Agenda 1. MySQL – A short review 2. High Availability & Security 3. Developer Productivity 4. Analytics, Machine Learning, Autopilot 5. Lakehouse: Processing on Object Store 6. Management & Observability 7. Multi Cloud 3
5 The evolution of MySQL over the past decade • Refactoring • Modularizing • Adding interfaces • Adding functionality as plugins • Remove functionality from core, e.g. Query cache • Transactional storage engines (InnoDB) • UTF8 • Modernizing the tool chain • and much more... MySQL has been an Oracle Global Business Unit since 2010 MySQL has been an Oracle Global Business Unit since 2010 https://archive.fosdem.org/2020/schedule/event/mysql8
8 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 MySQL HeatWave Databases Services (for OLTP) MySQL HeatWave (for Analytics) MySQL HeatWave AutoML MySQL HeatWave on AWS MySQL HeatWave on Azure MySQL HeatWave Lakehouse (Preview) Community, Enterprise, Cloud Service (HeatWave) Community, Enterprise, Cloud Service (HeatWave) https://www.mysql.com/products
9 MySQL Enterprise Edition Data protection and regulatory compliance Data protection and regulatory compliance MySQL Enterprise Masking De-identify, Anonymize Sensitive Data MySQL Enterprise Firewall Block SQL Injection Attacks, Intrusion Detection MySQL Enterprise TDE AES 256 encryption, Key Management MySQL Enterprise Audit User Activity Auditing, Regulatory Compliance MySQL Enterprise Authentication External Authentication Modules MySQL Enterprise Monitor Monitor Changes in Database Configurations, User Permissions, Database Schema, Passwords MySQL Enterprise Encryption Public/Private Key Cryptography, Asymmetric Encryption MySQL Enterprise Backup Secure Backups, AES 256 encryption
10 Areas of innovation High Availability & Security Developer Productivity Analytics, Machine Learning, Autopilot Lakehouse: Processing on Object Store Management & Observability Multi Cloud
12 MySQL InnoDB Cluster: Group Replication, Router & Shell MySQL InnoDB Cluster • MySQL Group Replication: Automatic membership changes, network partition handling, consistency... • MySQL Shell Configuring, 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 A complete High Availability solution for MySQL
13 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 Disaster tolerance for InnoDB Cluster deployments Disaster tolerance for InnoDB Cluster deployments
14 MySQL InnoDB ReplicaSet Administer a set of instances running replication Administer a set of instances running replication • Think “asynchronous Replication” • Asynchronous Replication Architecture • RPO = ! 0 / RTO = minutes Read Scaleout Simple Replication architecture • Operations based on the idea of MySQL InnoDB Cluster • MySQL Shell Configuring, Adding, Removing members • CLONE Plugin used to automatically provision members, • Fully integrated MySQL Router to route application traffic
15 Latest authentication methods • MySQL Enterprise Edition extend Community Authentication and provides ready to use external authentication modules to easily integrate existing security • Supports Linux PAM, Windows Active Directory, native LDAP… • MySQL 8.0 now support • Kerberos Authentication using Kerberos requires a KDC server Applications and MySQL servers are able to mutually authenticate users and MySQL services • FIDO (Fast IDentity Online) Enables authentication to MySQL Server using devices such as smart cards, security keys, and biometric readers Because authentication can occur other than by providing a password, FIDO enables password less authentication Device authentication is often used in conjunction with password authentication
16 Multifactor Authentication (MFA) • New system variable – authentication_policy • Controls how many authentication factors can be used and types of authentication permitted for each factor • Specify MFA using CREATE USER and ALTER USER • Add/Modify/Drop authentication methods for new/existing accounts • New authentication factors stored in mysql.user table in USER_ATTRIBUTES column • Accounts can now have up to three authentication values • Client programs have now --password1, --password2, and --password3 options
18 MySQL Long-Term Support (LTS) Releases • stable: bugfix & security patches only • backwards compatibility • every 2 years • support lifecycle: 5y premier + 3y extended MySQL Versioning: LTS & Innovation Releases MySQL Innovation Releases • leading-edge innovations • easy migration between LTS & Innovation • every quarter • support lifecycle: short term
19 MySQL embraces Cloud Native https://dev.mysql.com/doc/refman/8.0/en/docker-mysql-getting-started.html • Lots of new features in MySQL 8.0 • set persist, restart, performance_schema.error_log • MySQL is easy to run in containers • Oracle MySQL Community Edition image • Server, Router, k8s Operator, NDB Cluster, … • https://hub.docker.com/u/mysql • Oracle MySQL Enterprise Edition image • Support for DNS SRV (since MySQL 8.0.18+) • MySQL Connector support for DNS SRV (RFC 2782) • DNS request for service results in list of results • JDBC, .NET, ODBC, NodeJS, Connector/C, Python… • https://dev.mysql.com/doc/refman/8.0/en/connecting-using-dns-srv.html
20 • Developed by MySQL (InnoDB Cluster) team @ Oracle • Designed to operate a MySQL InnoDB Cluster in Kubernetes • Integrated with MySQL InnoDB Cluster • Coordinated development between both projects • MySQL Operator for Kubernetes • K8s Operator leverages MySQL InnoDB Cluster to deploy the declared architecture Keeps cluster healthy Replaces instances when another fails Recovers complete cluster goes offline Can recover in certain quorum majority loss situations • Backup & Recovery MySQL Operator for Kubernetes Published on GitHub: https://github.com/mysql/mysql-operator
21 MySQL Operator for Kubernetes - Features • Automated deployment and management of • MySQL Server • MySQL Router Self-healing • Backup & Restore • Scaleup/Scaledown of Router & Server Level III Maturity Operator • Rolling upgrades with minimal downtime • Configuration Management • Deploy from InnoDB CLONE • Private container registries Next step: Level IV
22 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... MySQL Shell Overview Interface for Development and Administration of MySQL Interface for Development and Administration of MySQL https://dev.mysql.com/doc/mysql-shell/8.0/en
23 • Supports dump and load of instances, schemas and tables • Parallel loading of tables or table chunks • Progress state tracking • Resume and reset capability • Can add invisible columns with primary keys to tables • Splits larger tables in smaller chunks (reads 2GB/s+) • Loads chunks in parallel (writes 200MB/s+) • Support for native storage in OCI Object Storage • Recommended tool to migrate data to MySQL Database Services • Commands • util.dumpInstance() dump an entire database instance, including users • util.dumpSchemas() dump a set of schemas • util.dumpTables() dump a set of schemas • util.loadDump() load a dump into a target database MySQL Shell: Load & Dump utilities Create logical dumps & do logical restores, with a focus on ease of use, performance & integration Create logical dumps & do logical restores, with a focus on ease of use, performance & integration https://dev.mysql.com/blog-archive/mysql-shell-dump-load-part-1-demo/
24 MySQL Shell Debugging and Diagnostics Utilities • Enable you to analyze the performance of your servers (5.7+) and generate diagnostics reports on overall health, performance under load, and individual queries • Generates files in both TSV and YAML format • Standalone servers, members of replication topologies, InnoDB Clusters • The topology is detected automatically and diagnostics are generated for that topology • The diagnostic report is generated as a zip file to either the local directory or a specified path MySQL JS> util.debug.collectDiagnostics() util.debug.slowQueryDiagnostics() util.debug.collectHighLoadDiagnostics() Analyze the performance of your servers & generate diagnostics reports Analyze the performance of your servers & generate diagnostics reports https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-diagnostics.html
25 MySQL Shell for VS Code (preview) World’s most popular open source database + world’s most popular IDE World’s most popular open source database + world’s most popular IDE DB Notebook Interface • Write, Execute, Edit MySQL Shell GUI Console • Full Power of MySQL Shell Full MySQL HeatWave Integration • Manage MySQL HeatWave Instances https://blogs.oracle.com/mysql/post/introducing-mysql-shell-for-vs-code https://marketplace.visualstudio.com/items?itemName=Oracle.mysql-shell-for-vs-code
26 Fast, Secure HTTPS Access for your MySQL Data Fast, Secure HTTPS Access for your MySQL Data MySQL REST Service (preview) https://blogs.oracle.com/mysql/post/introducing-the-mysql-rest-service • The MySQL REST Service (MRS) enables fast and secure HTTPS access for your MySQL data • Implemented as a MySQL Router feature, MRS provides the ability to publish RESTful Web Services for interacting with the data stored in MySQL solutions
27 Available for your Available for your On-Premise MySQL Solutions & MySQL HeatWave instances On-Premise MySQL Solutions & MySQL HeatWave instances MySQL REST Service - Architecture Overview https://blogs.oracle.com/mysql/post/introducing-the-mysql-rest-service A MRS setup consists of the following elements: • Standalone MySQL Server, MySQL InnoDB Cluster(Set), MySQL HeatWave on OCI for serving the application data • MySQL Shell & MySQL Shell for VS Code, for configuring & managing MRS, as well as enabling RESTful Web Service support for selected database schema objects • MySQL Routers, serving JSON data via RESTful Web Services
29 MySQL HeatWave – Single database for OLTP, OLAP, ML Valorizing your data has never been so easy! Valorizing your data has never been so easy! https://www.mysql.com/products/mysqlheatwave
30 MySQL HeatWave Cloud Service 100% developed, managed, and supported by the MySQL team 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
31 *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 400G, 64 cores 400G, 64 cores MySQL HeatWave dramatically speeds up analytic queries
32 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 30TB TPCH, MySQL HeatWave is faster faster, , cheaper cheaper & & easier easier to use than all the competitive database services to use than all the competitive database services MySQL HeatWave performance and price comparison www.oracle.com/mysql/heatwave/performance
33 MySQL HeatWave AutoML Accelerate ML initiatives, increase security, and reduce costs Build, train, deploy, & explain machine learning models within MySQL HeatWave, at no additional cost Build, train, deploy, & explain machine learning models within MySQL HeatWave, at no additional cost https://dev.mysql.com/doc/heatwave/en/mys-hwaml-machine-learning.html
34 MySQL HeatWave AutoML automates the ML lifecycle Dataset Data preprocessing Algorithm selection Adaptive sampling Feature selection Hyper-parameter tuning Tuned model Model explainer Prediction explainer Regulatory compliance Fairness Repeatability Causality Trust All models can be explained! All models can be explained! https://blogs.oracle.com/mysql/post/mysql-heatwave-new-machine-learning-multicloud-capabilities Support of: • Regression • Classification • Recommender systems • Unsupervised anomaly detection • Multi-variate time series forecasting
35 Produces more accurate results Trains models 25X faster on average 1% of the cost Scales as more modes are added HeatWave ML vs Redshift ML: Benchmarks summary See Benchmark details: https://www.oracle.com/mysql/heatwave/performance/
37 Automate the most important & challenging aspects of achieving high query performance at scale Automate the most important & challenging aspects of achieving high query performance at scale MySQL Autopilot: Machine Learning Based Automation https://www.mysql.com/products/mysqlheatwave/autopilot
39 Now in Beta Now in Beta MySQL HeatWave Lakehouse 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 https://www.mysql.com/products/mysqlheatwave/lakehouse
40 MySQL HeatWave can process data from multiple data sources Oracle Golden Gate OCI Object Storage AWS Aurora AWS Redshift Data can be in a file or other databases Data can be in a file or other databases • Query data across MySQL, the object store, or both—using standard MySQL commands • Up to 400 TB of data—the HeatWave cluster scales to 512 nodes • Querying the database is as fast as querying the object store https://www.mysql.com/products/mysqlheatwave/lakehouse
41 Query performance of MySQL HeatWave Lakehouse Annual provisioned cost for HeatWave $1,589,036; for Redshift $2,261,760; for Snowflake $2,242,560 Pricing for Redshift is based on 1-year reserved instance, paid upfront. Snowflake is based on standard edition *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. 17x 17x faster than Snowflake, faster than Snowflake, 6x 6x faster than Redshift faster than Redshift https://github.com/oracle/heatwave-tpch
42 Load performance of MySQL HeatWave Lakehouse Annual provisioned cost for HeatWave $1,589,036; for Redshift $2,261,760; for Snowflake $2,242,560 Pricing for Redshift is based on 1-year reserved instance, paid upfront. Snowflake is based on standard edition *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. 8x 8x faster than Redshift, faster than Redshift, 2.7x 2.7x faster than Snowflake faster than Snowflake https://github.com/oracle/heatwave-tpch
44 Metrics • MySQL metrics to diagnose and troubleshoot problems • Create alarms on key metrics based on thresholds Fleet monitoring • Unified fleet view of MySQL databases across Oracle Cloud and on-premises Drill down to a specific MySQL from the Fleet • Quickly identify expensive queries that impact the performance of their applications • Visualize query activity to gain further insight into performance beyond query statistics • Filter for specific query problems like full table scans and bad indexes using advanced global search options MySQL Observability in OCI
45 Predictive Insights • Insights and recommendations to right size your infrastructure and optimize resource utilization • Forecast demand for changing workloads • Quickly isolate the largest, most utilized, and fastest growing databases • Detect performance degradation in business-critical SQL • Aggregate and compare across databases • Trend and forecast metrics seasonality via custom analytics for solving specific issues MySQL Capacity and Performance Management
of Enterprises Using Public Cloud Have Adopted a Multicloud Infrastructure Provider Strategy https://www.oracle.com/lu/news/announcement/98-percent-enterprises-adopted-multicloud-strategy-2023-02-09
47 • MySQL HeatWave is available in 40+ Oracle Cloud Regions • … but also on Oracle Cloud@Customer • … and also in Amazon Web Services & Microsoft Azure MySQL HeatWave - Ready for the distributed cloud Optimized to deliver the best price performance in each cloud Optimized to deliver the best price performance in each cloud
52 • The best MySQL releases ever, as widely acknowledged by the MySQL Community • Commitment to open source, with continued code contributions (as opposed to AWS, who forked MySQL to create the closed source Aurora) • A constant stream of innovation, with new products and services—including MySQL HeatWave—helping users and customers tackle the new challenges that they are facing • Unparalleled MySQL Database expertise from Oracle, the leading database company Oracle is committed to MySQL ’s success
53 • Modernize transactional applications using Amazon Aurora, RDS for MySQL, Azure Database for MySQL, Google’s Cloud SQL for MySQL with supporting analytical workloads from Amazon Redshift, Snowflake, Azure Synapse, and Google BigQuery • Modernize mixed workload applications using Amazon Aurora, RDS for MySQL, Azure Database for MySQL, GCP Cloud SQL for MySQL (initially without separate analytics database) • Modernize mixed workload applications using MySQL either on-premises or in the cloud • Rely on MySQL HeatWave for in-database machine learning to avoid using a separate machine learning service with data coming from MySQL, MySQL-based services, or analytics databases Modernizing applications with MySQL HeatWave Key use cases Key use cases
54 Performance of MySQL HeatWave on AWS *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. 20x 20x faster than Redshift, faster than Redshift, 16x 16x faster than Snowflake, faster than Snowflake, 16x 16x faster than BigQuery faster than BigQuery
55 Discovering MySQL Database Service • Discovering MySQL Database Service – Episode 1 – Introduction – http://dasini.net/blog/2021/08/03/discovering-mysql-database-service-episode-1-introduction/ • Discovering MySQL Database Service – Episode 2 – Create a compartment – http://dasini.net/blog/2021/08/10/discovering-mysql-database-service-episode-2-create-a-compartment/ • Discovering MySQL Database Service – Episode 3 – Create a Virtual Cloud Network – http://dasini.net/blog/2021/08/17/discovering-mysql-database-service-episode-3-create-a-virtual-cloud-network/ • Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket – http://dasini.net/blog/2021/08/24/discovering-mysql-database-service-episode-4-dump-your-mysql-data-into-an-object-storage-bucket/ • Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump – http://dasini.net/blog/2021/08/31/discovering-mysql-database-service-episode-5-create-a-mysql-db-system-from-a-mysql-shell-dump/ • Discovering MySQL Database Service – Episode 6 – Update the Private Subnet Security List – http://dasini.net/blog/2021/09/07/discovering-mysql-database-service-episode-6-update-the-private-subnet-security-list/ • Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session – http://dasini.net/blog/2021/09/14/discovering-mysql-database-service-episode-7-use-a-bastion-ssh-port-forwarding-session/ • Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell – http://dasini.net/blog/2021/09/21/discovering-mysql-database-service-episode-8-connect-to-mysql-database-service-using-mysql-shell/ • Discovering MySQL Database Service – Episode 9 – Connect to MySQL Database Service Using MySQL Workbench – http://dasini.net/blog/2021/09/28/discovering-mysql-database-service-episode-9-connect-to-mysql-database-service-using-mysql-workbench/ • Discovering MySQL Database Service – Episode 10 – Connect to MySQL Database Service Using OCI Cloud Shell – http://dasini.net/blog/2021/10/05/discovering-mysql-database-service-episode-10-connect-to-mysql-database-service-using-oci-cloud-shell/ • ... Tutorials where I show you, step by step, how to use MDS and some other OCI services Tutorials where I show you, step by step, how to use MDS and some other OCI services http://dasini.net/blog/en
freshdaz
ma_cho29
nashiusagi
maroon1st
chiroruxx
star_zero
soyuka
92thunder
irumaru
j5ik2o
masayaaoyama
hollycummins
shoheimitani
jponch
garrettdimon
keathley
andyhume
lauravandoore
danielanewman
maltzj
lynnandtonic
bkeepers
sugarenia
soudai
reverentgeek
