HeatWave MySQL for OLTP with High Availability, Auto-Indexing and JavaScript [LRN3513].pdf

1、LRN3513-HeatWave MySQL for OLTP with High Availability,Auto-Indexing and JavaScriptMark Matthews,Architect for HeatWave,OracleLuciano Burotti,Database Administrator,Mercado LibreOscar Mullin,Diretor Snior,Engenharia,Mercado LibreSeptember 10,2024Oracle CloudWorld Copyright 2024,Oracle and/or its aff

2、iliates1The following is intended to outline our general product direction.It is intended for information purposes only,and may not be incorporated into any contract.It is not a commitment to deliver any material,code,or functionality,and should not be relied upon in making purchasing decisions.The

3、development,release,timing,and pricing of any features or functionality described for Oracles products may change and remains at the sole discretion of Oracle Corporation.The materials in this presentation pertain to Oracle Health,Oracle,Oracle Cerner,and Cerner Enviza which are all wholly owned sub

4、sidiaries of Oracle Corporation.Nothing in this presentation should be taken as indicating that any decisions regarding the integration of any EMEA Cerner and/or Enviza entities have been made where an integration has not already occurred.Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates

5、2Safe harbor statementAgendaHeatWave Service OverviewAutopilot IndexingJavaScript Stored Functions and ProceduresHighly Available DBSystemsMercado Libres HeatWave ExperienceOracle CloudWorld Copyright 2024,Oracle and/or its affiliates3TRANSACTIONS,REAL-TIME ANALYTICS ACROSS DATA WAREHOUSE AND DATA L

6、AKE,MACHINE LEARNING,GENAI IN ONE DATABASE SERVICEHeatWaveOracle CloudWorld Copyright 2024,Oracle and/or its affiliates4QueriesQueriesResultsResultsSocial,eCommerce,IoT,gaming,fintech apps Analytics/ML/GenAI toolsObject StorageDatabase Database exportsexportsDatabase exportsStreaming dataData Source

7、sEnterprise AppsWeb/SocialLogfilesIoTMySQL databaseHeatWaveOLAPOLAPOLTPOLTPAutoMLAutoMLAutopilotAutopilot Vector storeVector storeGenAIGenAIOracle CloudWorld Copyright 2024,Oracle and/or its affiliates5HeatWave MySQL Autopilot IndexingToo few indexes-low query performanceToo many slower DML,more sto

8、rage,slower query optimization“TNSTAFL”(Theres No Such Thing As A Free Lunch)My 8th Grade Economics TeacherWorkloads change over time requiring re-evaluation of indexing strategyThis is tedious workMachine Learning functionality is built into Heatwave MySQL so already availableWhy use Machine Learni

9、ng to Optimize Indexing?Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates6Considers both query and DML performance(index maintenance cost)Recommends CREATE and DROP of indexesGenerates DDLs for index creation/dropProvides performance prediction(per query and total workload)Provides stora

10、ge prediction for the recommended indexesProvides explanation for the recommendationsWhat Does HeatWave MySQL Autopilot Indexing Do?Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates7Advice is per-schemaFive queries accessing tables in the schema before invoking the advisorUsing HeatWave

11、MySQL Autopilot Indexing in PracticeOracle CloudWorld Copyright 2024,Oracle and/or its affiliates8CALL sys.autopilot_index_advisor(JSON_OBJECT(target_schema,JSON_ARRAY(bb_seats_sf7);HeatWave MySQL Autopilot Indexing-SuggestionsOracle CloudWorld Copyright 2024,Oracle and/or its affiliates9HeatWave My

12、SQL Autopilot Indexing Actionable GuidanceOracle CloudWorld Copyright 2024,Oracle and/or its affiliates10HeatWave MySQL Autopilot Indexing ExplanationsOracle CloudWorld Copyright 2024,Oracle and/or its affiliates11HeatWave MySQL Autopilot Indexing ExplanationsOracle CloudWorld Copyright 2024,Oracle

13、and/or its affiliates12HeatWave MySQL Autopilot Indexing DDL GenerationOracle CloudWorld Copyright 2024,Oracle and/or its affiliates13SELECT log-$.sql AS SQL Script FROM sys.autopilot_index_advisor_report WHERE type=sql ORDER BY id;Workloads change over time requiring re-evaluation of indexing strat

14、egyTedious work to evaluate SQL statements and schema structures You are still in control,but have better information to make decisionsYour data never leaves the database ML is built-inWhy use HeatWave MySQL Autopilot Indexing?Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates14Oracle Clo

15、udWorld Copyright 2024,Oracle and/or its affiliates15JavaScript Stored Functions and ProceduresOracle CloudWorld Copyright 2024,Oracle and/or its affiliates16Data MovementJavaScript Applications are popular Powerful for light weight front-end and server-side applications Works great with SQL servers

16、,communicate with database via connectorsHow to handle data-intensive use cases?Data Validation JSON&String processing /Formatting Data Cleaning/TransformationProblem:Need to move data to clientOracle CloudWorld Copyright 2024,Oracle and/or its affiliates17Stored FunctionsStored ProceduresKeep data

17、in ServerMobile AppsOperational SystemsWeb AppsDecision SupportHandle data-intensive app functionality in stored programsMinimize data movementReduce costImprove SecuritySimplify complex ETL ELTLimitations in procedural SQL stored programs Not expressive:Hard to use,lacks basic constructs like conta

18、iners(arrays,maps)Not efficient:Challenging to optimize due to interpreted codeInsufficient Development eco-system:Editors,debuggers,testing frameworks,reusable 3rd Party librariesFew experienced programmersOracle CloudWorld Copyright 2024,Oracle and/or its affiliates18Just Like SQL Stored Programs,

19、but now Improved Developer Experience Security at its core State-of-the-art optimizations Designed for both Cloud Service and on-premExecute JavaScript Stored Procedures and Stored Functions via GraalVMGenerally Available(GA)Now!HeatWave for OCI,AWS,and Azure MySQL 9.0 Enterprise EditionJavaScript S

20、tored Functions and Procedures for MySQLOracle CloudWorld Copyright 2024,Oracle and/or its affiliates19What is GraalVM?Secure SandboxJIT CompilerOne VMMultipleLanguagespythonrubywww.graalvm.orgEcoEco-system of compiler technologiessystem of compiler technologiesHigh PerformanceProfile guided JIT com

21、piler Ahead-of-time(AOT)compilation of the language implementation to native codeAdvanced compiler optimizations,such as aggressive inlining and partial escape analysisGraal.JSJavaScript Implementation based on ECMAScript 2023Competitive performance with V8 engineImplemented using Graal Polyglot Fra

22、mework,that allows multiple languages inside the same VMVirtual MachineFully memory managedSecure sandboxSupport for developer toolsOracle CloudWorld Copyright 2024,Oracle and/or its affiliates20SecurityCode IsolationPrevents visibility or interaction between any two different stored programs execut

23、ed on GraalVMAdds protection against JIT spraying and side-channel attacks.SandboxingEach stored program runs inside GraalVM strict sandboxing policy that blocks any unauthorized access toFile systemThread managementNetwork accessNative AccessMySQL PrivilegesUses MySQL Privileges for stored programs

24、SQL execution inside JavaScript uses DEFINER and INVOKER security contextOracle CloudWorld Copyright 2024,Oracle and/or its affiliates21PerformanceMySQL Native Integration Custom built VM for MySQL using Graal native image feature Scalable thread management using MySQL enterprise thread pool for exe

25、cution inside GraalVM VM configured and optimized for cloud instanceGraal Optimizations Graal EE Compiler optimizations.Aggressive inlining and partial escape analysis Profile guided JIT compilation Graal.JS competitive performance with v8 engineOracle CloudWorld Copyright 2024,Oracle and/or its aff

26、iliates22Resource UtilizationAuto Configuration Memory and compute resources are configured based on the cloud instance shape Resource Management Lazy allocation:resource utilization is zero if feature not used.Memory utilization is capped:benefits from GraalVM garbage collection Concurrency regulat

27、ed by MySQL enterprise thread poolResource Monitoring Resource utilization available via MySQL status variableOracle CloudWorld Copyright 2024,Oracle and/or its affiliates23Defining JavaScript Stored ProgramsSimple SyntaxLANGUAGE clause now allows JavaScriptExtensible string quoting mechanism to enc

28、lose non-SQL language sourceAS$AS$JavaScript$JavaScript$Function EnvironmentNo function redefinition in JavaScript requiredSQL argument identifiers directly available in JavaScriptAuto Type-ConversionTransparent MySQL JavaScript type conversionSupports all variations of INT,FLOATS,DATETIME,VARCHAR(u

29、tf8mb4),JSON,TEXT,BLOBOracle CloudWorld Copyright 2024,Oracle and/or its affiliates24Accessing JavaScript from SQLSELECTUse anywhere where SQL stored functions can be usedExpressions,Projection,WHERE clause,GROUP-BY,JOIN,ORDER BY,HAVING etc.DMLs,DDLs,VIEWsSupport inside all DMLs(INSERT,UPDATE,DELETE

30、 etc)DDLs including CREATE TABLE AS SELECTSupport inside VIEWs InteroperabilityInvoke JavaScript&SQL functions and procedures inside existing SQL stored functions or proceduresChain JavaScript&SQL stored functions together using input/output arguments Oracle CloudWorld Copyright 2024,Oracle and/or i

31、ts affiliates25Accessing SQL from JavaScriptStatement TypesSimple SQL statementsPrepared statements with bind parametersData Access APIExecute SQL inside JavaScript using XDevAPISeamless MySQL JavaScript type conversion for query resultsSession StateContinue transactions inside JavaScriptAccess all

32、session state inside JavaScript such as session variables&temporary tablesOracle CloudWorld Copyright 2024,Oracle and/or its affiliates26Debugging FacilitiesStandard Streams Access language standard output and error streams inside MySQL Error Handling Translates unhandled JavaScript exceptions into

33、MySQL errors Allow access to JavaScript stack traces in case of unhandled runtime error Translates MySQL errors and warnings into JavaScript exceptions while executing SQL statements inside JavaScriptKey Take-AwaysOracle CloudWorld Copyright 2024,Oracle and/or its affiliates27Express complex logic i

34、n database using JavaScriptPush part of data-intensive application inside the databaseBenefit from GraalVM Enterprise Edition optimizations at no additional costIntegrate with MySQL cloud-only features seamlesslyReduce data movement costOracle CloudWorld Copyright 2024,Oracle and/or its affiliates28

35、Highly Available HeatWave MySQL DbSystemsWhats Going to Be Covered?High-level architecture of(HA)DbSystemsPlacement considerationsHow are failures detectedWhat happens during failoverTips for successful HA implementationsOracle CloudWorld Copyright 2024,Oracle and/or its affiliates29Ingredients for

36、a Heatwave DbSystem InstanceOracle CloudWorld Copyright 2024,Oracle and/or its affiliates30VirtualVirtualMachineBlock StorageOCI ObjectObject StorageApplicationVCNVCNService Owned/ManagedHeatWave Service Takes Care Of.Providing API to manage your HeatWave systems provision,configure,monitor and oper

37、ate,terminateSupporting YouProvisioningComputeBlock VolumesObject Storage BucketsPrivate Endpoints,Network Load BalancersMaintainingPatch and upgrade operating system and database softwareReplace failed virtual machines,block volumes,hypervisorsSecuringSIEMS,Detection and Response,vulnerability scan

38、ning for operating system,compute,networkMeet compliance requirements for regulated markets Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates31You Take Care OfConfiguring DbSystem To Meet Your Workload(see Support on last slide)Understanding HA at a conceptual level and how it impacts yo

39、ur application(s)Required schema attributesTables must have a primary key or one will be provided for youDecide placement of HA DbSystem instancesUnderstand impact during switchover and failoverDML implementation changes if required to ensure replication is successfulOracle CloudWorld Copyright 2024

40、,Oracle and/or its affiliates32Regional HA DbSystemOracle CloudWorld Copyright 2024,Oracle and/or its affiliates33ApplicationApplicationApplicationAD1/FD1AD2/FD2AD3/FD3Private Subnet 10.124.16.0/24R/W Endpoint VIP10.124.16.25Group Replication(Paxos)HA DbSystem PlacementRegional SubnetMultiple Availa

41、bility Domains 1 per Availability DomainOne Availability Domain 1 per Fault DomainAD-Local Subnet1 per Fault DomainOracle CloudWorld Copyright 2024,Oracle and/or its affiliates34HA DbSystem Failover/SwitchoverOracle CloudWorld Copyright 2024,Oracle and/or its affiliates35ApplicationApplicationApplic

42、ationAD1/FD1AD2/FD2AD3/FD3Private Subnet 10.124.16.0/24R/W Endpoint VIP10.124.16.25Group Replication(Paxos)HA DbSystem Switchover User-Initiated,or MaintenanceOracle CloudWorld Copyright 2024,Oracle and/or its affiliates36ApplicationApplicationApplicationAD1/FD1AD2/FD2AD3/FD3Private Subnet 10.124.16

43、.0/24R/W Endpoint VIP10.124.16.25Group Replication(Paxos)HA DbSystem Failover Automated Failure DetectionOracle CloudWorld Copyright 2024,Oracle and/or its affiliates37ApplicationApplicationApplicationAD1/FD1AD2/FD2AD3/FD3Private Subnet 10.124.16.0/24R/W Endpoint VIP10.124.16.25XXXGroup Replication(

44、Paxos)What to know about Group Replication Failure Detection“Detect”phase5 second timeout to not receive a message from another group member “suspect”a failureNetwork partitioningResource exhaustion(memory,CPU)Software crash“Suspect Phase”10 seconds after“suspected”failure group member attempts to p

45、ropagate its view of the suspected failed member to the group”Expel Phase”If remainder of group agree on“suspected failure”,after 25 seconds,the group member is expelled primary election happens automatically if required Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates38Reasons for Fail

46、ures Under Your ControlResource exhaustion(memory,CPU,disk)due to mismatch between workload andConfigurationCPU/Memory ShapeStorage Shape(larger volumes have higher throughput)Software crash Remaining on older versions than latest available“in series”Oracle CloudWorld Copyright 2024,Oracle and/or it

47、s affiliates39What Happens During Primary Switchover/FailoverHeatWave HA DbSystems are always Single Primary modeClients always connect to the same endpoint with a stable IP addressIn-flight transactions are terminated implicitly rolled backEstablished connections to MySQL are terminated clients mus

48、t reconnectService fences/unfences nodes as necessary,moves floating private IP to new primaryWhat it feels like to your application depends on the chosen transaction consistency levelOracle CloudWorld Copyright 2024,Oracle and/or its affiliates40Group Replication Transaction Consistency GuaranteesC

49、onfigurable group_replication_consistencyEVENTUALBEFORE_ON_PRIMARY_FAILOVERBEFOREAFTERBEFORE_AND_AFTEROracle CloudWorld Copyright 2024,Oracle and/or its affiliates41Group Replication Consistency-EVENTUALRO and RW transactions do not wait for preceding transactions to be applied before executing.New

50、primary can accept new RO and RW transactions before the previous primary transactions are all applied.RO transactions could result in outdated values.RW transactions could result in a rollback due to conflicts.Prioritizes availability over consistencyOnly use this mode if your application tolerates

51、 eventual consistencyOracle CloudWorld Copyright 2024,Oracle and/or its affiliates42Group Replication Consistency-BEFORE_ON_PRIMARY_FAILOVERDefault used in HeatWave MySQLNew RO or RW transactions with a newly elected primary that is applying backlog from the old primary are held(not applied)until an

52、y backlog has been applied.Clients always see the latest value on the primary.Guarantees consistency-clients must be able to handle the delay in the event that a backlog is being applied.Delay should be minimal,but it does depend on the size of the backlog.Oracle CloudWorld Copyright 2024,Oracle and

53、/or its affiliates43Group Replication Consistency-BEFORERW transaction waits for all preceding transactions to complete before being applied.RO transaction waits for all preceding transactions to complete before being executed.RO reads the latest value by only affecting the latency of the transactio

54、n.Reduces the overhead of synchronization on every RW transaction,by ensuring synchronization is used only on RO transactions.Includes the consistency guarantees of BEFORE_ON_PRIMARY_FAILOVER.Remember“does depend on the size of the backlog”?Oracle CloudWorld Copyright 2024,Oracle and/or its affiliat

55、es44Group Replication Consistency-AFTERRW transaction waits until its changes have been applied to all of the other members.No effect on RO transactions.Ensures when a transaction is committed on the local member,any subsequent transaction reads the written value or a more recent value on any group

56、member.Reduces the overhead of synchronization on every RO transaction,.Ensures that synchronization is used only on RW transactions.Includes consistency guarantees provided by BEFORE_ON_PRIMARY_FAILOVER.Remember“does depend on the size of the backlog”?Oracle CloudWorld Copyright 2024,Oracle and/or

57、its affiliates45Group Replication Consistency-BEFORE_AND_AFTERA RW transaction waits forAll preceding transactions to complete before being appliedUntil its changes have been applied on other members.A RO transaction waits for all preceding transactions to complete before execution takes place.Inclu

58、des the consistency guarantees provided by BEFORE_ON_PRIMARY_FAILOVER.Remember“does depend on the size of the backlog”?Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates46“Does depend on the size of the backlog”Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates47Reducing To-Be-

59、Applied Transaction BacklogGroup Replication uses row-based binary logsOne“event”per row change Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates48mysql DELETE FROM my_very_large_table WHERE record_ts 0;DODONEBreak large INSERT batches into smaller onesLarge batches generally do not work

60、 well transactionally HA or notEvaluate whether your application really requires a transaction around a large batchIf not break the batch into smaller ones“Escape Hatch”disable HA,perform large batch,enable HA Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates53Also Important to Understan

61、dWorkload/Throughput at secondaries and replicas may not mirror primaryDependency Tracking(WRITESET)Group Replication Flow Control monitors(for every group member):Certifier queue sizeReplication applier queue sizeTotal number of transactions certifiedTotal number of remote transactions applied in t

62、he memberTotal number of local transactions Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates54Key Factors for a Successful HA DbSystemPlacement selected supports application latency and availability requirementsCPU,memory,storage shapes and configuration support stable operation under w

63、orkloadNo (very)large transactionsApplication implemented to tolerate unavailability/delay to support failoversNo“set and forget”need to monitor and adjust to workload changes over timeOracle CloudWorld Copyright 2024,Oracle and/or its affiliates55Oracle CloudWorld Copyright 2024,Oracle and/or its a

64、ffiliates56Mercado Libres StoryOracle CloudWorld Copyright 2024,Oracle and/or its affiliates57Join Mercado Libre for More DetailsMercadoLibres Challenge:Operating Thousands of MySQL Instances in Multicloud LRN2219Thursday,Sep 12|9:00 AM-9:45 AM PDT|Marco Polo 805,The Venetian,Level 1Oracle CloudWorl

65、d Copyright 2024,Oracle and/or its affiliates58Questions?Thank YouOracle CloudWorld Copyright 2024,Oracle and/or its affiliates59Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates60Join us forInnovations in HeatWave and MySQL-The Present and the FutureWednesday,Sep 11|9:45 AM-10:30 AM PDT

66、|Galileo 904,The Venetian,Level 1Nipun AgarwalSenior Vice President,MySQL&HeatWave Development,OracleBuild Generative AI ApplicationsIntegrated and Automated with HeatWave GenAIWednesday,Sep 11|8:30 AM-9:15 AM PDT|Venetian Level 2:EEdward ScrevenChief Corporate Architect,OracleThe following is inten

67、ded to outline our general product direction.It is intended for information purposes only,and may not be incorporated into any contract.It is not a commitment to deliver any material,code,or functionality,and should not be relied upon in making purchasing decisions.The development,release,timing,and

68、 pricing of any features or functionality described for Oracles products may change and remains at the sole discretion of Oracle Corporation.The materials in this presentation pertain to Oracle Health,Oracle,Oracle Cerner,and Cerner Enviza which are all wholly owned subsidiaries of Oracle Corporation.Nothing in this presentation should be taken as indicating that any decisions regarding the integration of any EMEA Cerner and/or Enviza entities have been made where an integration has not already occurred.Oracle CloudWorld Copyright 2024,Oracle and/or its affiliates61Safe harbor statement