MySQL 的新功能：地端和云端.pdf

《MySQL 的新功能：地端和云端.pdf》由會員分享，可在線閱讀，更多相關《MySQL 的新功能：地端和云端.pdf（33頁珍藏版）》請在三個皮匠報告上搜索。

1、Whats new in MySQLOn-premise and cloud Nipun AgarwalSenior Vice President,MySQL&HeatWaveFirst LTS Release!First LTS release April 30 8.4.0 LTSInnovation releases to start with 9.0.09.7.0 targeted LTS release-in 2 years2Copyright 2024,Oracle and/or its affiliatesFocus areas Better performanceLower co

2、stDeveloper productivity SecurityHigher availability Thread Pool ImprovementsHighly Scalable Thread-Handling Model0 01,0001,0002,0002,0003,0003,0004,0004,0005,0005,0006,0006,0007,0007,0008,0008,0009,0009,00012481632641282565121,0242,0484,0966,0008,000Transactions per SecondTransactions per SecondUse

3、rs Users Sysbench OLTP Read/Write,pareto access pattern,100GB data sizeSysbench OLTP Read/Write,pareto access pattern,100GB data sizeMySQL Enterprise Edition with Thread PoolMySQL Community Server without Thread Pool9x Better Scalablity:Sysbench OLTP Read/Write4Copyright 2024,Oracle and/or its affil

4、iatesEliminating double buffer writesAtomics*Experiments show:With ATOMIC I/O,amount of data written is similar to dblwr=OFF(half)on disk With ATOMIC I/O,time to flush is similar to dblwr=OFFCoreConfigInnoDB_DoubleWriteBuffer_pool_pages_dirtyTimeBo_sum16BP 128 GB-table=10-tablesize=40MOFF5356K00:02:

5、2283.7 GBON5356K00:34:44172.5 GBATOMIC_IO5356K00:02:2283.7 GB8BP 128 GB-table=10-tablesize=20MOFF2679K00:01:1541.9 GBON2679K00:21:5186.3 GBATOMIC_IO2679K00:01:1741.9 GB4BP 128 GB-table=5-tablesize=10MOFF1339K00:00:4920.9 GBON1339K00:11:0643.2 GBATOMIC_IO1339K00:00:4920.9 GB5Copyright 2024,Oracle and

6、/or its affiliatesEliminating double buffer writesATOMIC I/O performance at par with dblwr=OFFWrite intensive workload 6Copyright 2024,Oracle and/or its affiliatesBulk ingest support in MySQL HeatWave Parallel sort&merge and parallel build of index sub-trees Sequential writes of sorted data into dis

7、k which eliminates random disk i/o Pipelining of internal stages which overlaps compute with disk i/oThread-1Thread-2Thread-NParallel,In-Memory Sort and MergeSortedChunk-1SortedChunk-2SortedChunk-KThread-1Thread-NThread-2Sub-Tree-1Sub-Tree-2Clustered IndexSub-Tree-NParallelSub-Tree BuildConstant-Tim

8、eSub-Tree MergeIn-Place Insertions into B+TreeClustered IndexEarlier7Copyright 2024,Oracle and/or its affiliatesBulk ingest performance0100200300400500600700800Sorted DataSorted DataUnsorted DataUnsorted DataIngest time(min)Ingest TimeIngest TimeAmazon AuroraMySQL HeatWave(AWS)370 GB64 GB10 x faster

9、 than Amazon Aurora,uses less memory8Copyright 2024,Oracle and/or its affiliatesNumberNumberDescriptionDescriptionBug#25903274PERFORMANCE REGRESSION WITH PREPARED STATEMENTS.Fixed in 8.0.3.Bug#36154818 Redolog files on Windows are not using Overlapped mode.Fixed in 8.0.37.Bug#36142806Innodb_parallel

10、_read_threads 1 makes simple select count more expensive.Fixed in 8.0.37.Bug#102238log_writer uses too much CPU on small servers.Closed as not a bug.Bug#32511973SLOW PERFORMANCE OF MYSQL 8 COMPARED WITH 5.7 ON GEOGRAPHICAL SELECT.Bug#34951273Performance of scanning data_lock_waits worse than expecte

11、d with read-only trx.Fixed in 8.4.Bug#35936316Regexp/rlike function in routines extreme regression mysql 5.7 to mysql 8.Bug#93684 mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0.Bug#33684069SHOW SLAVE STATUS became expensive for Replica-Slave column name conversions.Fixed in 8.0.

12、31.Bug#35712638 Materializing performance_schema.data_locks can lead to excessive mem usage.Fixed in 8.0.37.Bug#27877386 MySQL Upgrade from 5.7 to 8.0:performance improvements.Bug#89963 Slowdown in creating new SSL connection.Closed as not a bug.9Copyright 2024,Oracle and/or its affiliatesMany Perfo

13、rmance Fixes in 8.4NumberNumberDescriptionDescriptionBug#28857534 MySQL 8.0 performance degradation on INSERT with foreign_key_checks=0.Fixed in 8.0.14.Bug#35916912Performance degradation from 8.0.30 onwards related to performance_schema.Fixed in 8.0.36.Bug#33840573Regression on DDL statements with

14、big stage/sql/checking permissions.Bug#30837086SELECT from I_S.INNODB_COLUMNS is slow in 8.0.Fixed in 8.0.21.Bug#34959356Poor performance when using HASH field to check unique.Fixed in 8.0.36.Bug#2793465360%overhead from performance schema instrumentation of stored functions.Fixed in 8.0.36.10Copyri

15、ght 2024,Oracle and/or its affiliatesMany Performance Fixes in 8.4MySQL Autopilot indexingRecommends secondary indexes for OLTP workloadsCREATE/DROPIndexesindexQueriesDMLsQueriesDMLsTables11Copyright 2024,Oracle and/or its affiliatesWorks for individual workloadsNo guess workInterpretableML models a

16、re adaptableEver-changing envNew server releasesCan predict improvementHelps plan recommendation 12MySQL Summit Copyright 2024,Oracle and/or its affiliatesWhy ML-based automation?ResultsThroughput at Par or Better Even on Benchmarks Which are Tuned0500010000150002000025000TPCC SF13SMALLBANK SF7SEATS

17、 SF7EPINIONS SF350AUCTIONMARK SF8Throughput(request/s)BenchmarkBenchmarkThroughput Throughput Tuned BenchmarkAutopilot IndexingAutopilot recommends indexes whose performance is at par or better than manually tuned benchmarks In some cases,Autopilot recommends fewer indexes which saves storage13Copyr

18、ight 2024,Oracle and/or its affiliatesStored Procedures Inside the DatabaseHandle dataHandle data-intensive app functionalityintensive app functionality Minimize data movement Reduce cost Improve Security Simplify complex ETL ELTStored FunctionsStored ProceduresKeep data in ServerMobile AppsMobile A

19、ppsWeb AppsWeb AppsOperational Operational SystemsSystemsDecision Decision SupportSupport14Copyright 2024,Oracle and/or its affiliatesMySQL Stored Programs-SQL vs JavaScriptSQL Stored ProceduresJavaScriipt Stored ProgramsExpressivenessHard to use,lacks basic constructs like containers(arrays,maps)Hi

20、ghly expressive and robustEfficiencyChallenging to optimize due to interpreted codeMany JS code analysis tools.JavaScript apps are fast and optimized by GraalVMEcosystemInsufficient:Lacks support from IDEs,debuggers,testing frameworks,Large ecosystem of tools for developers of JavaScript application

21、sAvailability of developersFew experienced programmersEspecially with MySQL Ecosystem13.8 M DevelopersThe most popular developer languageReusable 3rd Party librariesFew,mostly code examplesThousands15Copyright 2024,Oracle and/or its affiliatesHigh Performance JDK The advanced optimizing Graal compil

22、er provides ahead of time compilation,and polyglot language executionOracle GraalVMHigh-performance optimizing Just-in-Time(JIT)compilerAhead-of-Time(AOT)“Native Image”generatorMulti-language support16Copyright 2024,Oracle and/or its affiliatesJavaScript inside MySQLOn premise,OTN,MySQL HeatWave SEL

23、ECTUse anywhere where SQL stored functions can be usedExpressions,Projection,WHERE clause,GROUP-BY,JOIN,ORDER BY,HAVING etc.DMLs,DDLs,VIEWsSupport inside DMLs(INSERT,UPDATE,DELETE,)DDLs including CREATE TABLE AS SELECTSupport inside VIEWsInteroperabilityInvoke JavaScript&SQL functions and Programs i

24、nside existing SQL stored functions or proceduresSELECT col1,col2,gcd_js(col1,col2)FROM my_tableWHERE gcd_js(col1,col2)1ORDER BY gcd_js(col1,col2);CREATE TABLE gcd_tableAS SELECT gcd_js(col1,col2)FROM my_table;CREATE TABLE gcd_tableAS SELECT gcd_js(col1,col2)FROM my_table;17Copyright 2024,Oracle and

25、/or its affiliatesMySQL-JavaScriptServer extended to support ability for SQL callout from JavaScript MySQL Server ProcessMLE ComponentType ConversionEngineSQL CalloutGraalInterfaceVM HeapJavaScript ExecutionC API callsJavaScript callbacksGraal C APIs Java Wrapper OptimizerSELECT,DML,DDLPrepared Stat

26、ementsStorage EngineInnoDBLakehouseGeneric SQL Statement C+ServiceHeatwave recovery&self loadNDB clusterClone API and upgrade18Copyright 2024,Oracle and/or its affiliatesSQL inside JavaScriptStatement TypesSimple SQL statementsPrepared statements with bind parametersData Access APIExecute SQL inside

27、 JavaScript using XDevAPISeamless MySQL JavaScript type conversion for query resultsSession StateContinue transactions inside JavaScriptAccess all session state inside JavaScript such as session variables&temporary tablesCREATE PROCEDURE gen_random_age(IN row_count INT)LANGUAGE JAVASCRIPT AS$let ins

28、ertStatement=session.prepare(INSERT INTO my_table(age)VALUES(?);for(let j=0;j row_count;j+)let random_age=Math.trunc(Math.random()*100);insertStatement.bind(random_age).execute();$CREATE PROCEDURE average_age(OUT avg_age FLOAT)LANGUAGE JAVASCRIPT AS$let age_sum=0,count=0;let selectStatement=session.

29、sql(SELECT age FROM my_table);let result=selectStatement.execute(),row=null;while(row=result.fetchOne()age_sum+=row0;count+;avg_age=age_sum/count;$19Copyright 2024,Oracle and/or its affiliatesSQL-Callout OLTP Noise testBenchmark:Sysbench E4.1.8Gb Shape Impact on background OLTP same as SQL procedure

30、s20Copyright 2024,Oracle and/or its affiliatesOracle RESTData ServicesREST access to MySQL HeatWaveOracle CloudWorld Copyright 2023,Oracle and/or its affiliatesScalable and flexible Scalable and flexible access to access to MySQL HeatWaveMySQL HeatWaveMySQL HeatWaveMySQL Shell for VS Code enhanced t

31、o support new HeatWave features 22Copyright 2024,Oracle and/or its affiliatesOpenTelemetry(Otel)Open standard for telemetry dataCloud Native Compute Foundation(CNCF)projectOracle OCI is CNCF Platinum memberProvides technology to collect and export telemetryAPIs,libraries,agents,and instrumentation V

32、ery Popular second most active projectMySQL and OpenTelemetryIncludes Otel libraries to emit Traces,Metrics,LogsTraces include Spans(unit of work context)Metrics choose from 400+metrics(meters and gauges)Logs -in progress for 9.0OpenTelemetry and MySQLCorrelated Telemetry Data(Traces,Metrics,Logs)Ac

33、ross Services/StacksFeed into O&M BackendsLogsCorrelateTracesMetricsCorrelateCorrelates across technologiesAutomates problem identification and diagnosisOCI Observability and ManagementDatabase Management ServiceMonitoring,Diagnostics&Predictive Insights On-demand subscription-based cloud serviceUni

34、fied fleet monitoring and management for on-prem and cloud(MySQL Heatwave)databases Fleet summary pageVisualization-driven load and performance analysisForecast demand for changing workloadsML-driven SQL insights 24Copyright 2024,Oracle and/or its affiliatesSecurity:Bring Your Own Certificate with M

35、ySQL HeatWaveOracle CloudWorld Copyright 2023,Oracle and/or its affiliates25MySQL HeatWave allows users to point to the OCI Certificate Services so users can create,provision,manage,deploy,and automatically rotate PKI certs.OCI Certificates Service Generates and stores private keys in a FIPS Level 3

36、 HSMFIPS Level 3 HSM.Removes the error prone,manual process of purchasing,uploading,and renewing certs.Provides a Private Certificate Authority option for customers managing their own certificate chains.Provides automatic cert deployment and renewal.CA AdminOCI Certificate ServiceMySQL HeatwaveCreat

37、e Private&Public CertDeploy&ManageUsers&ApplicationsTrusted AccessmTLSPrivate Certificate BundlePublic Certificate BundleMySQL HeatWaveAdminDefines Private Certificate BundleAdding support for Authentication using the OpenID Connect StandardWhat is OpenID Connect Industry StandardSecurity protocol f

38、or verifying a users identity.Uses Oauth 2.0(IETF RFC 6749 and 6750)Cloud application focusedBenefitsSupports Single Sign-OnToken basedSecure works with MFA,Bioauthn,Simple to configureBroad use casesCloud to CloudCloud to OnPremOnPrem to Cloud Many providers,supportersOracle Access Management,OCI I

39、dentityOracle Fusion Microsoft ADFS,Ping,Okta,Security-Open IDMulti-Cloud,On-Prem,Hybrid AuthenticationImprovements in downtime:Planned Upgrade:60s 15sPlanned Switchover:60s 7s Automatically replace uncoverable instances Online changes to shape and configuration of a HA cluster PS1S2PS1S2PS1*S2 FIRS

40、TS1 NEXTP NEXTS1S2PS2High availability improvements for OLTP workloads in MySQL HeatWave 27Copyright 2024,Oracle and/or its affiliatesDescriptionSummaryChange ShapeCustomer can specify the shape for a specific read replica and can make it handle a specific read traffic profile that is more demanding

41、 resource-wiseChange ConfigurationCustomer can specify the configuration for a specific read replica and can adjust it to handle a specific read traffic profile.Change VersionCustomer can specify the version for a specific read replica and try out new features with minimal disruption.Or they may wan

42、t to pin a given replica to a specific version.Read replica enhancements for OLTP workloads R1PWritesDB SystemRW VIPR2RO VIPRO VIPHRReportingR1PWritesDB SystemRW VIPRO VIPRO VIPHRReportingR18.0.338.0.338.0.35Change shape,change version,change config28Copyright 2024,Oracle and/or its affiliatesFaster

43、 Binlog Upload for PITR Slow performance increases vulnerability window during large transactions/binlogs Designed multi-part upload where binlog split into multiple files and uploaded via multiple threads Reduces compute bottleneck and doesnt run into the object store file size limitationBinlog Upl

44、oad ProcessMulti-Part Binlog Upload Process40GB40GB 50GB(Limit)COMPUTECOMPUTEObject StorageObject Storage6GB Each PartBinlog 50 GB29Copyright 2024,Oracle and/or its affiliatesFaster Binlog Upload for PITR:upto 5x faster The size of each object can range from 100 MB to 25 GB based on overall binlog s

45、izeMax 10 threads for parallel upload of smaller objects to object storage bucketNot limited to 50GB binlog uploadBinlogBinlog Size Size(GBGB)Single File UploadSingle File UploadMultiMulti-Part UploadPart Upload1 124 seconds11 seconds4 452 seconds36 seconds161610 min,2 seconds2 min,42 seconds444415

46、min,10 seconds2 min,59 sec800800Not possible29 minutes5x faster,smaller vulnerability window30Copyright 2024,Oracle and/or its affiliatesImproved disable PITR performance 34min to 3minDoes not block other DB System operationsMulti-Threaded PITR:2x better performance Point in time recovery improvemen

47、ts VMApplying 500GB binlogEarlierNow4 core VM22 hours10 hours8 core VM14 hours7 hours1231Copyright 2024,Oracle and/or its affiliatesMySQL HeatWaveAnalyticsIn-database MLAutopilotOLTPMySQL HeatWaveAnalyticsIn-database MLAutopilotOLTPMySQL HeatWave Eliminates the need for multiple services QueriesQuer

48、iesResultsResultsSocial,eCommerce,IoT,gaming,fintech apps.Analytics and ML toolsObject StoreDatabase Database exportsexportsHeatWaveAnalyticsIn-database MLAutopilotOLTPDatabase exportsStreaming dataData SourcesEnterprise AppsWeb/SocialLog filesIoTMySQL storageScales from 16 GB to 512 TBLakehouse,Real-Time Analytics,Machine Learning,Transaction Processing in one Database Service32Copyright 2024,Oracle and/or its affiliates33Copyright 2024,Oracle and/or its affiliates