使用 MySQL HeatWave 處理事務.pdf

1、Transaction Processing with MySQL HeatWaveSriram Vrinda Product Management MySQL HeatWaveAgenda Introduction MySQL HeatWave:Fully Automated Database as a ServiceOLTP with MySQL HeatWaveScalability Data protection and durabilityHigh Availability ReliabilitySecurityObservabilitySummary Copyright 2024,

2、Oracle and/or its affiliatesCopyright 2024,Oracle and/or its affiliatesIntroduction Managing data can be complicated and expensiveReduce costs and risksHigh cost and risk to run and maintainSimplify and accelerate time to valueBarriers to innovationCopyright 2024,Oracle and/or its affiliatesAutomate

3、d operations without manual interventionsCost to upgrade and secureIs there a better way to meet business requirements?Pay for what the business needsCost to run and optimizeSecure and unified data architectureMultiple database vendors and security modelsReduce costs and risksHigh cost and risk to r

4、un and maintainSimplify and accelerate time to valueBarriers to innovationAbility to support multiple workload typesDisparate systems for transactions,analytics,and ML slowing down modern app devProductive low-code/no-code development toolsTime spent integrating data and building appsIntegrated data

5、 eco-system and multi-cloud supportHighly distributed data and disconnected cloudsCopyright 2024,Oracle and/or its affiliatesWhat if your cloud database could do the following:Make it trivial to patch and upgrade thousands of databasesRemove need to track versions+one-off fixes for every databaseAut

6、omatically deploy critical security bug fixes into production databases as soon as they are availableSimplify planning of hardware capacity for workloads to meet all future business requirementsSimplify maintaining and testing disaster-recovery infrastructureFully automate processes for database lif

7、ecycle operationsProvide 24x7 support for every database availability issueAutomatically file service requests+gather all diagnostics information for every database issueSignificantly reduce operational and licensing costsHow should databases be managed in the future?Copyright 2023,Oracle and/or its

8、 affiliatesCopyright 2024,Oracle and/or its affiliatesMySQL HeatWave Fully Automated MySQL Database as a Service Eliminate system and database administration with a fully managed database service Customize operational policies to meet governance requirementsComplete automation and management Automat

9、ically configure,secure,update,tune,and scale databasesWorkload optimizations Unified platform for transactions and analytics with built-in MLEfficient database lifecycle management Eliminate infrastructure management by offloading it to Oracle Increase administrator productivity with user-controlle

10、d cloud automation Web and API-driven lifecycle operations Provisioning,updates,backups,etc.Built-in Observability Database management service for expanded database metrics and query performance diagnosticsCopyright 2024,Oracle and/or its affiliatesCopyright 2024,Oracle and/or its affiliatesOLTP wit

11、h MySQL HeatWave100%developed,managed,and supported by OracleMySQL HeatWave:fully managed database serviceAutomationMySQL HeatWaveDatabaseHigh AvailabilityRead ReplicasBackupQuery AccelerationAutoPilot AutoMLSecurity Patch&UpgradeProvision&ConfigureOSOS Security Patch&UpgradeOS InstallationServerHar

12、dware Provisioning&MaintenanceStorageStorage Provisioning&MaintenanceData CenterRack&SpacePower,HVAC,NetworkingCopyright 2024,Oracle and/or its affiliatesOLTP with MySQL HeatWaveServer side capabilities InnoDB transactional storage engineACID compliantData integrity through foreign key constraintsSt

13、ored Procedures to improve developer productivity.Triggers to enforce complex business rules at the database level.Views to ensure sensitive information is not compromised.SQL OptimizerCost-based optimizerCommon Table Expressions also known as WITH queriesWindow Functions to reduce code complexity a

14、nd help developers be more productiveDevelop and run modern apps Document Store for developing both SQL and NoSQL document applicationsNative JSON data type for managing unstructured dataEnhanced GIS to support geography and Spatial Reference Systems(SRS).Ability to manage by exceptionOpenSSL as the

15、 default TLS/SSL library in MySQLPerformance Schema with queries that are up to 30 x faster.Invisible Indexes to better manage software upgrades and database changes for applications that run 24x7Copyright 2024,Oracle and/or its affiliatesMySQL HeatWave Components MySQL DB System:Interface to automa

16、te the management of tasks such as provisioning,backup and restore,monitoring,etc.A compute instance(with resources defined by the associated shape)Two default configurations for most of the shapes:StandaloneStandalone:Optimized for standalone DB systems and read replicasHAHA:Optimized for highly av

17、ailable DB systemsOracle Linux Operating SystemMySQL Server Enterprise Edition version 8.0 or higherChoose from two categories of shapes:ECPU and OCPUVirtual Network Interface Card(VNIC)that attaches the DB system to a subnet of the Virtual Cloud Network(VCN).Network-attached block storage Uses the

18、OCI Block Volume service Higher Performance option over iscsi Provides a linear performance scale of 75 IOPS/GB up to a maximum of 50,000 IOPS per volume Throughput scales at the rate 600 KB/s/GB up to a maximum of 680 MB/s per volumeCopyright 2024,Oracle and/or its affiliatesMySQL DB System abstrac

19、tionConnect effortlessly via endpoints,bypassing architectural complexities.Use the best architecture for your needs,optimizing key non-functional requirements without application changes.Embrace tomorrows demands,ensuring adaptability,performance,security,scalability,and cost-efficiency.Copyright 2

20、024,Oracle and/or its affiliatesChoose from two categories of shapes:ECPU and OCPUCopyright 2024,Oracle and/or its affiliatesMySQL HeatWave Scalability MySQL HeatWave Scalability Read replicasCopyright 2024,Oracle and/or its affiliatesIncrease capacity for read-intensive workloadsAdd and remove Read

21、 Replicas for horizontal read elasticityEasy to deploy and maintainYou can create a maximum of 18 read replicas of a DB systemBuilt-in Load Balancer for the read-only endpoint5-Tuple Hash Load Balancing Policy:source IP and port,destination IP and port,protocolHA Cluster continues reliable and ready

22、 for switchover or failoverMySQL HeatWave Scalability Cross Region Read replicasCopyright 2024,Oracle and/or its affiliatesOutbound replication can be used to copy transactions from a DB system to another OCI regionThis is not a managed functionality,you are responsible for configuring and maintaini

23、ng the channelHybrid deploymentsDisaster RecoveryCross-region deploymentsDB system to DB systemCopyright 2024,Oracle and/or its affiliatesMySQL HeatWave data protection and durability MySQL HeatWave data protection and durabilityBackups Automatic backup:Created automatically at a time selected while

24、 creating the DB system.Define the retention period between 1 and 35 days Default retention period is 7 days For a high availability DB system,the automatic backups are created on the primary instanceManual backup:Create a manual backup by an action in the Console,or a request through the REST API R

25、etain the manual backup for a minimum of 1 day and a maximum of 365 daysOperator backup:MySQL Support team creates this backup to assist in investigating potential issues with your service.Copyright 2024,Oracle and/or its affiliatesMySQL HeatWave data protection and durabilityPoint-In-Time RecoveryR

26、estore data from a DB system to a new DB system at the latest available point-in-time or a specific point-in-timePre-requisitesEnable automatic backups and enable point-in-time recovery on the backup plan of the DB systemMySQL HeatWave Service takes an initial full backup(Backup type:Full,Creation T

27、ype:automatic)and incremental backups thereafterDB system operations such as stop,start,restart,or upgrade does not impact the point-in-time recovery operationProvides Recovery Point Objective(RPO)of approximately five minutes for an active DB system,while the daily backup provides you a RPO of 24 h

28、oursCopyright 2024,Oracle and/or its affiliatesCross-Region Backup CopyComing soon!Copyright 2024,Oracle and/or its affiliatesCopyright 2024,Oracle and/or its affiliatesMySQL HeatWave High Availability MySQL HeatWave High Availability Higher uptime and zero data loss toleranceCopyright 2024,Oracle a

29、nd/or its affiliatesMySQL HeatWave High Availability Higher uptime and zero data loss toleranceCopyright 2024,Oracle and/or its affiliatesA high availability DB system is made up of three MySQL instances:a primary instance and two secondary instancesProvisioned across different availability or fault

30、 domainsThe primary instance functions as a read/write endpointAutomatic or Manual Promotion of a Secondary InstanceMySQL HeatWave High Availability Higher uptime and zero data loss toleranceCopyright 2024,Oracle and/or its affiliatesSLA 99.99%Automatic failoverManual switchoverRolling upgrades duri

31、ng maintenanceLess than 30 seconds impactMySQL version upgrades and OS security patchesOnline change of shape and configuration of a high availability DB system and read replicas with minimal downtime Copyright 2024,Oracle and/or its affiliatesRecovery Time(RTO)and Potential Data Loss(RPO)service SL

32、OHigh Availability RTO/RPO MatrixFailure and Maintenance EventsDowntime(RTO)Potential Data Loss(RPO)Periodic software and hardware maintenance updatesSeconds to minutesZeroLocalized,per instance events,including:Storage connectivity failuresNetwork connectivity failuresFull database failuresMinutesZ

33、eroAvailability or fault domain failures(depending on high availability type)Complete storage failuresMinutesZeroMySQL HeatWave supports crash recovery,which ensures durability and enables data recovery in the event of an unexpected server exit.Copyright 2024,Oracle and/or its affiliatesMySQL HeatWa

34、ve Reliability MySQL HeatWave ReliabilityMaximize reliability with automated upgrades and patchingCopyright 2024,Oracle and/or its affiliatesNo customer actions requiredNo need to schedule patches,track patch contents or request one-off patchesMinimal downtimeExisting connections to the primary are

35、closed and no new connections are permitted.Always up-to-date security fixesFrequent patches to meet compliance requirementsFull-stack patchingIncludes database and all cloud infrastructure12345Continuous delivery of new cloud featuresMultiple new features available every monthExample:zero-effort,ze

36、ro-downtime automatic hardware upgradesIf you manage your own database servers:1.Procure new hardware and install in data center2.Test and validate hardware and networking3.Install database software and configure database environment4.Validate sizing and performance5.Create and test migration strate

37、gy6.Schedule and execute production migration(with downtime?)If you run MySQL HeatWave:Zero-effort hardware upgrades:Hardware refreshes occur transparently during regularly scheduled maintenance windowsNo hardware planning or testingNo database downtimeNo extra costCopyright 2024,Oracle and/or its a

38、ffiliatesCopyright 2024,Oracle and/or its affiliatesMySQL HeatWave Security Copyright 2024,Oracle and/or its affiliatesMySQL HeatWave Security Activity AuditingAudit service automatically records calls to MySQL HeatWave API endpoints as log events.UserAssessmentAssesses the database and highlights a

39、ccounts that could pose a risk.Connection controlRemove brute force attacks attempting to guess MySQL login credentialsSimplified certificate management(BYOC)Easily create,deploy,and manage Secure Sockets Layer/Transport Layer Security(SSL/TLS)certificates available in Oracle Cloud.Simplified access

40、 managementSign-On with flexible authentication options:support for federated,social,and delegated sign-on.Built-in,server-side features to implement additional security measures Asymmetric encryption with key generation and digital signatures:to increase the protection of confidential data using bo

41、th public and private keys and implement digital signatures to confirm the identity of people signing documents.Data masking and deidentification:to help protect private data against external attacks and malicious employees.Database firewall:protecting against database-specific attacks,such as SQL i

42、njections.Compliance attestations&certifications:Global:PCI DSS,SOC 1 2 3,CSA STAR,ISO/IEC 27001/27017/27018/27701,etc.Regional:HIPAA,CSF,HITRUST,EU CoC,ENS,C5,MTCS,IRAP,ISMS,MeitY,etc.Advanced securityCopyright 2024,Oracle and/or its affiliatesCopyright 2024,Oracle and/or its affiliatesMySQL HeatWa

43、ve Observability Copyright 2024,Oracle and/or its affiliatesOCI monitoring and Database Management MySQL HeatWave Observability OCI Monitoring Find performance bottlenecks Collect metrics Define thresholdsDatabase Management Fix problem queries Rapidly troubleshoot performance issuesOps Insights Pre

44、dictive Insights with AI Trend and forecastMySQL Autopilot indexing(Coming soon)Recommends secondary indexes for OLTP workloadsCopyright 2024,Oracle and/or its affiliatesCREATE/DROPIndexesRowsidxQueriesDMLsRowsidxQueriesDMLsBase tablesBase tablesQueriesDMLsCREATE/DROPIndexesAutopilot Indexing(Coming

45、 soon)ML automation with MySQL HeatWave FeaturesML-based feature designed to help optimize database systems for better cost and performanceConsiders both query and DML performance Recommends CREATE and DROP of indexesGenerates DDLs for index creation/dropProvides performance predictionProvides stora

46、ge predictionContinuous learning and adaptingBenefitsDBAs no longer need to manually identify the secondary indexes for the database workload.Considers both the query performance and the cost of maintaining the indexesPredicts expected improvement without creating the indexes Provides explanation fo

47、r the recommendationsOriented by performance objectives:throughput,latency,storageCopyright 2024,Oracle and/or its affiliatesMaximizing reliability with automated proactive monitoringMySQL HeatWave service finds the problems before you doOracle Cloud Operations uses continuous monitoring for each da

48、tabase:8000+metrics and 1500+alarms Much broader than any on-premises customer Consolidated monitoring of entire stack:infrastructure,load balancer,connection manager,databaseAutomatic service requests are generated for each deviation Immediate investigation and resolution by cloud ops Root cause an

49、alysis for every issue Zero customer actions requiredCopyright 2024,Oracle and/or its affiliatesCopyright 2023,Oracle and/or its affiliates Overview of Autonomous Database Serverless Copyright 2024,Oracle and/or its affiliatesSummaryOne database is better than twoCopyright 2024,Oracle and/or its aff

50、iliatesAnalytics tools and applicationsTransactional applicationsInnoDB storage engineHeatWaveanalytics clusterOne service for OTLP&OLAPNo ETL duplicationUnmatched performance,at a fraction of the costReal-time analyticsImproved securityApplications work without changes12 with MySQL HeatWaveReduce c

51、osts and risksSimplify and accelerate time to valueCopyright 2024,Oracle and/or its affiliatesAuto-patching,auto-backups,auto-configurationElastic auto-scale with enterprise business continuityAlways-on security and complianceUnified platform for transactions and analytics with built-in MLBuilt-in low-code/no-code development toolsDeep integration with multi-cloud ecosystemMySQL HeatWaveFully-managed,cloud native,Database as a Service