mysql clusters

Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.|

MySQLHighAvailabilityInnoDBClusterandNDBCluster

MarkSwarbrick

Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.


SafeHarborStatementThefollowingisintendedtooutlineourgeneralproductdirecOon.ItisintendedforinformaOonpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfuncOonality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andOmingofanyfeaturesorfuncOonalitydescribedforOracle’sproductsremainsatthesolediscreOonofOracle.

2


AlmostallorganizaOonsrequiretheirmostcriOcalsystemstobehighlyavailable

3

100%


InnoDBCluster

4

or

NDBCluster


• MySQLInnoDBCluster– EasyHAbuiltintoMySQL5.7+– Writeconsistency– ReadScalability– AppfailoverusingMySQLRouter– Managementviamysqlsh– InnoDBstorageengine

• MySQLNDBCluster– Inmemorydatabase– AutomaOcsharding– NaOveaccessviaseveralAPI– Read/writeconsistency– Read/writescalability– NDBstorageengine

HowDoTheTwoCompareI


HowDoTheTwoCompareII

MySQLInnoDBCluster MySQLNDBCluster

StorageEngine InnoDB NDBCLUSTER

DistributedArchitecture Sharednothing Sharednothing

ConsistencyModel WeakConsistency StrongConsistency

Sharding No Yes

ArbitraOon No Yes

LoadBalancing No Yes

NoSQLAPIs MySQLDocumentStore NaOveNDBAPI

OperaOonalComplexity Medium High

AdministraOon Standard(MySQL) Custom(MySQL+NDB)


InnoDBCluster

7


InnoDBCluster

8

AppServerswithMySQLRouter

MySQLGroupReplicaOon

MySQLShellSetup,Manage,Orchestrate

“HighAvailabilitybecomesacorefirstclassfeatureofMySQL!”

Copyright©2016,Oracleand/oritsaffiliates.Allrightsreserved.| 9

MySQLInnoDBCluster:Vision

“Asingleproduct—MySQL—withhighavailabilityandscalingfeaturesbakedin;providinganintegratedend-to-endsoluEonthatiseasytouse.”


• OneProduct:MySQL– Allcomponentscreatedtogether– Testedtogether– Packagedtogether

•  EasytoUse– Oneclient:MySQLShell– Easypackaging– IntegratedorchestraOon– Homogenousservers

•  FlexibleandModern– SQLandNoSQLtogether– ProtocolBuffers– Developerfriendly

•  SupportRead/WriteScaleOut– Shardedclusters– FederatedsystemofNreplicasets– Eachreplicasetmanagesashard

10

MySQLInnoDBCluster:Goals


MySQLConnectorApplicaOon

MySQLRouter


MySQLRouter

MySQLShell


MySQLRouter


MySQLRouter

MySQLInnoDBCluster:HighLevelArchitecture

MySQLInnoDBcluster

MySQLEnterpriseMonitor

…


MySQLGroupReplicaOonNa7velydistributedandhighlyavailablereplicasets


MySQLGroupReplicaOon:WhatIsIt?• GroupReplicaOonlibrary

– ImplementaOonofReplicatedDatabaseStateMachinetheory• MySQLGCSisbasedonPaxos(variantofMencius)

– ProvidesvirtuallysynchronousreplicaOonforMySQL5.7+– SupportedonallMySQLplaGorms

•  Linux,Windows,Solaris,OSX,FreeBSD“MulE-masterupdateanywherereplicaEonpluginforMySQLwithbuilt-inconflictdetec1onandresolu1on,automa1cdistributedrecovery,andgroupmembership.”

13





MySQLGroupReplicaOon:WhatDoesItProvide?

• AhighlyavailabledistributedMySQLdatabaseservice– Removestheneedformanuallyhandlingserverfail-over– Providesdistributedfaulttolerance– EnablesAcOve/AcOveupdateanywheresetups– AutomatesreconfiguraOon(adding/removingnodes,crashes,failures)– AutomaOcallydetectsandhandlesconflicts

14


MySQLGroupReplicaOon:Architecture

NodeTypesR:Trafficrouters/proxies:mysqlrouter,haproxy,sqlproxy,...M:mysqldnodesparOcipaOnginGroupReplicaOon

15


MySQLServer:FullStack


MySQLRouterTransparentapplica7onconnec7onrou7ng


MySQLRouter:2.1• NaOvesupportforInnoDBclusters

– UnderstandsGroupReplicaOontopology– UOlizesmetadataschemastoredoneachmember

•  BootstrapsitselfandsetsupclientrouOngfortheInnoDBcluster•  AllowsforintelligentclientrouOngintotheInnoDBcluster•  SupportsmulO-masterandsingleprimarymodes

• Coreimprovements– Logging– Monitoring– Performance– Security

18




labs.mysql.com


MySQLShellSingletoolfordevelopment,setup,management,orchestra7on,andmonitoring


MySQLShell

20

Asingleunifiedclientforalladministra7veandopera7onstasks




• MulO-Language:JavaScript,Python,andSQL– Naturallyscriptable

•  SupportsbothDocumentandRelaOonalmodels•  ExposesfullDevelopmentandAdminAPI”MySQLShellprovidesthedeveloperandDBAwithasingleintuiEve,flexible,andpowerfullinterfaceforallMySQLrelatedtasks!”


MySQLShell:AdminAPI

• mysql-js>dba.help()•  Theglobalvariable'dba'isusedtoaccesstheMySQLAdminAPI

• PerformDBAoperaOons– ManageMySQLInnoDBclusters

•  Createclusters•  DeployMySQLinstances•  Getclusterinfo•  Start/StopMySQLInstances•  ValidateMySQLinstances…

DatabaseAdministra7onInterface

labs.mysql.com





MySQLShell–DeployMySQLInstancesshell>mysqlsh

mysql-js>dba.deployLocalInstance(3306)

mysql-js>dba.deployInstance(‘hanode2:3306’)

mysql-js>dba.deployInstance(‘hanode3:3306’)

22


MySQLShell–CreateanInnoDBClustermysql-js>\connectroot@hanode1:3306

mysql-js>cluster=dba.createCluster(‘NewAppCluster')

mysql-js>cluster.addInstance('root@hanode2:3306')

mysql-js>cluster.addInstance('root@hanode3:3306')

23


MySQLShell–AddaMySQLRoutershell>mysqlrouter--bootstraphanode1:3306

shell>mysqlrouter&

shell>mysqlsh--uriroot@localhost:6446

24


MySQLEnterpriseMonitor• NaOveholisOcsupportforInnoDBclusters

– Topologyviews– Detailedmetricsandgraphs– BestPracOceadvice

• MonitoringofMySQLRouters• MonitoringofGroupReplicaOon

25


NDBCluster

26


DrivingDatabaseRequirements

9thApril2015 Copyright2015,oracleand/oritsaffiliates.Allrightsreserved 27

RockSolidAvailability

In-MemoryReal-Time

Performance

ExtremeRead&WriteScalability

ElasOcity


HLR/HSS

Billing,AuC,VLR

AuC,CallRouOng,Billing

LocaOonUpdates

Pre&PostPaid

• Massivevolumesofwritetraffic• <3msdatabaseresponse• DownOme&losttransacOons=lost$• ExtremlycostsensiOvemarket

MySQL Cluster in Action: http://bit.ly/oRI5tF

NoTrade-Offs:CellularNetwork


MySQLClusterOverview• MemoryopOmizedtableswithdurability• PredictableLow-Latency,BoundedAccessTimeREAL-TIME

• Auto-Sharding,AcOve-AcOve• ACIDCompliant,OLTP+Real-TimeAnalyOcsHIGHSCALE,READS+WRITES

FULLYELASTIC

•  AcOve-AcOve,Sharednothing,noSinglePointofFailure•  SelfHealing+On-LineOperaOons99.999%AVAILABILITY

•  Key/Value+Complex,RelaOonalQueries•  SQL+Memcached+JavaScript+Java+HTTP/REST&C++SQL+NoSQL

•  OpenSource+CommercialEdiOons,Easytouseanddeploy•  Commodityhardware+Management,MonitoringToolsLOWTCO


•  Addandremovestorageandperformancecapacityinseconds•  Fullycloudenabled


UserSession

Sessionmanagement

Sessionmanagement

LocaOonUpdates,Charactermovements,...

• Massivevolumesofwritetraffic• <3msdatabaseresponse• DownOme&losttransacOons=lost$• ExtremlysensiOvecrowd

NoTrade-Offs:MassiveParallelOnlineGames


MySQLClusterArchitecture

MySQLClusterDataNodes

Clients

ApplicaOonLayer

DataLayer



DataParOOoning/Auto-sharding

17/01/17 Copyright2015,oracleand/oritsaffiliates.Allrightsreserved 32

User-id(PK) Service(PK) Data

1773467253 chat xxx

6257346892 chat xxx

1773467253 photos xxx


8235602099 reminders xxx

8437829249 loca7on xxx






1773467253 chat xxx

6257346892 chat xxx






ShardKey


NodeGroup

DataNode1

• DatatransparentlyshardedbetweenNodeGroups•  EachfragmentacOveinoneDataNodewithsynchronousreplicaOonto2ndDataNodeinsameNodeGroup


DataNode2

NodeGroup

DataNode3 DataNode4




• DBAchooseswhichpartofPrimaryKeytouseasshardkey

•  Fragmentforeachrowdecidedbyhashingtheshardingkey



1773467253 chat xxx

6257346892 chat xxx





ShardKey

NodeGroup

DataNode1 DataNode2

NodeGroup

DataNode3 DataNode4


• DataautomaOcallyrearrangedtousenewcapacity• DesignedtobeaslowbackgroundprocessnotimpacOngreal-Omeperformance.


NodeGroup

DataNode1 DataNode2

NodeGroup

DataNode3 DataNode4

NodeGroup

DataNode5 DataNode6

On-lineScalingandElasOcityRe-parOoning


MySQLCluster7.2:1.2BillionUPDATEsperMinute

• NoSQLC++API,flexaSynchbenchmark

•  30xIntelE5-2600IntelServers,2socket,64GB

• ACIDTransacOons,withSynchronousReplicaOon

hxp://www.mysql.com/why-mysql/white-papers/mysql-cluster-benchmarks-1-billion-writes-per-minute/0

5

10

15

20

25

2 4 6 8 10 12 14 16 18 20 22 24 26 28 30

Mill

ions

of U

PDAT

Es p

er S

econ

d

MySQL Cluster Data Nodes



MySQLNDBCluster7.5

• ReadOpOmizedTables

• FullyReplicated

CapacityandScaleOut

• MySQL5.7• JSONDataType• GeneratedColumns• Records-Per-KeyOpOmizaOon

ImprovedSQL

• ImprovedReporOng• ImprovedLogging• ImprovedVisibility• ImprovedRestore

Management

RC


• Readingfrombackupallowstoreadfromanycopy

• Previouslyallreadsweredirectedtowardstheprimaryfragmentonly

ConfidenOal–OracleInternal/Restricted/HighlyRestricted 40

MySQLNDB7.5:Readingfrombackup

NodeGroup

DataNode1 DataNode2


NextStepsLearnMore•  www.mysql.com/cluster•  hxps://dev.mysql.com/doc/mysql-innodb-cluster/en/•  AuthenOcMySQLCurriculum:hxp://oracle.com/educaOon/mysql

TryitOut•  dev.mysql.com/downloads/cluster/7.4GAand7.5DMR1

•  hxps://labs.mysql.com/

Letusknowwhatyouthink•  hxp://mysqlhighavailability.com/•  forums.mysql.com/list.php?25



Thankyou!

mysql clusters

Technology