linear scalability, geo-distribution and extreme data

Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.|

LinearScalability,Geo-distributionandExtremeDataAvailabilityOracleSharding

NageshBattula,Sr.PrincipalProductManagerOracleHAProductManagement

@nageshbattula

MarkDilman,DirectorOracleProductDevelopment

CharlieBaker,Sr.DirectorOracleDyn GBU


SafeHarborStatementThefollowingisintendedtooutlineourgeneralproductdirection.Itisintendedforinformationpurposesonly,andmaynotbeincorporatedintoanycontract.Itisnotacommitmenttodeliveranymaterial,code,orfunctionality,andshouldnotberelieduponinmakingpurchasingdecisions.Thedevelopment,release,andtimingofanyfeaturesorfunctionalitydescribedforOracle’sproductsremainsatthesolediscretionofOracle.

2


ProgramAgenda

OverviewofOracleSharding

What’snewinOracleDatabase18c – Sharding

ChinaTelecom– ShardingforWeChatIoTApplication

OracleDyn’sexperiencewithOracleSharding

Summary

1

2

3

4

3

5


ProgramAgenda


What’snewinOracleDatabase18c – Sharding



Summary

1

2

3

4

4

5

Copyright©2017, Oracleand/oritsaffiliates.Allrightsreserved.| 5

OracleSharding– AnElasticRelationalDatabase

BA

DCFEHG

JILK

BADC

FEHG

JILK

OnegiantDBshardedinto

manysmallDBs

TablePartitions

Shard #1 Shard #3Shard #2

• Horizontalpartitioningofdataacrossupto1000independentOracleDatabases(shards)

• Shared-nothinghardwarearchitecture– Eachshardrunsonaseparateserver– Nosharedstorage– Noclusterware

• Dataispartitionedusingashardingkey(i.e.account_id)


OracleDatabaseSharding– Benefits

LinearScalability

Addshardsonlinetoincreasedatabasesizeandthroughput.Onlinesplitandrebalance.

ExtremeAvailability

Shared-nothinghardwarearchitecture.Faultofoneshard

hasnoimpactonothers.

GeographicDistribution

Userdefineddataplacementforperformance,availability,DRortomeetregulatoryrequirements.

…

…

…

…


ShardCatalog/Coordi

nator

ConnectionPool

ShardDirectors

ShardingKey

…

…

Architecture&KeyFeaturesofOracleSharding

Shards

• Autodeploymentofupto1000shardswithreplication– Declarativespecificationoftopology– SupportsActiveDataGuardandOracleGoldenGate

• Multipleshardingmethods– ConsistentHash,List,RangeorComposite

• Centralizedschemamaintenance– NativeSQLforshardedandduplicatedtables

• DirectroutingandProxyrouting• Onlinescale-outw/autoreshardingorscale-in

OracleDatabase18c – ShardingFeatures:• MidtierSharding– Affinityformidtierandshards• PDBSharding– SupportsMultitenant• RACSharding– Virtuallysharddataacrossinstances• AutomaticShardingWorkbench

– ShardingAdvisor&DataMigration


AutomatedDataAvailabilityConfiguration

8

ActiveDataGuardwithFast-StartFailover

GoldenGate‘chunk-level’active-activereplicationwithautomaticconflictdetection/resolution(OGG12.3)

Partition_F Partition_F

Partition_A

Partition_C

Optionally– complementreplicationwithOracleRACforserverHA


DeclarativeSpecificationofSDBConfiguration

9

createshardcatalog-database<shardcathost>:1521:shardcat-usersdb_admin/passwd_sdb_admin

addgsm–gsmsharddirector1-listener1571-pwdpasswd_gsmcatuser-catalog<shardcathost>:1521:shardcat..addcredential-credentialoracle_cred-osaccountoracle-ospassword<>

addshardgroup-shardgroupshgrp1-deploy_asprimary–regionavail_domain1

addinvitednode<>..createshard-shardgroupshgrp1-destination<host1> -credentialoracle_cred…

deploy

addservice -serviceoltp_rw_srvc-roleprimary

ShardCatalog/Coordi

nator

ConnectionPool

ShardDirectors

ShardingKey

…

…

Shards


DataModelingConsiderations• Toreapshardingbenefits,schemamustbedesignedtomaximizenumberofsingle-shardrequests

• Sharding-amenableschemaconsistsof:– ShardedTableFamily:

• Setoftablesequi-partitionedbythesharding key– Relateddataisalwaysstoredandmovedtogether– Joins&integrityconstraintchecksaredonewithinashard

• ShardingmethodandkeyarebasedonApprequirements• Sharding keymustbetheleadingcolumnofaprimarykey

– DuplicatedTables:• Non-shardedtablesarereplicatedtoallshards• Usuallycontaincommonreferencedata• Canbereadandandupdated(18.1)oneachshard

10

Customers

Orders

LineItems

Products


ShardedTables

Shard1 Shard2 ShardN

… … …

11

SchemaCreation– ShardedandDuplicatedTables

Customers Orders LineItems

Products

DatabaseTables

DuplicatedTable


CreatingaShardedTableFamilywithReferentialIntegrity

CREATE DUPLICATED TABLE Products ( ProductId INTEGER PRIMARY KEY, Name VARCHAR2(128), LastPrice NUMBER(19,4),… )TABLESPACE products_tsp ;

ExecuteDDLsontheShardCatalog

CREATE TABLESPACE SET tbs1 ;

CREATE SHARDED TABLE Customers( CustId VARCHAR2(60) NOT NULL,

FirstName VARCHAR2(60), LastName VARCHAR2(60),…CONSTRAINT pk_customers

PRIMARY KEY(CustId))PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO TABLESPACE SET tbs1 ;

CREATE SHARDED TABLE Orders ( OrderId INTEGER,CustId VARCHAR2(60),OrderDate TIMESTAMP,…CONSTRAINT pk_orders

PRIMARY KEY (CustId, OrderId),CONSTRAINT fk_orders_parent

FOREIGN KEY (CustId) REFERENCES Customers(CustId) )PARTITION BY REFERENCE (fk_orders_parent) ;


FastPathforkey-basedaccessAppTier

Routing Tier

DataTier

DirectRoutingviaShardingKey

• Connectionpoolmaintainstheshardtopologycache• Uponfirstconnectiontoashard

– Connectionpoolretrievesallshardingkeyrangesintheshard

– Connectionpoolcachesthekeyrangemappings

• DBrequestforakeythatisinanyofthecachedkeyrangesgoesdirectlytotheshard(i.e.,bypassessharddirector)

13

ApplicationServer

ShardDirectors


Coordinator(shardcatalog)

ProxyRoutingforMulti-shardQueries

• ApplicationsconnecttoQuerycoordinator/ShardCatalog– E.g. SELECT GEO, CLASS, COUNT(*) FROM CUSTOMERS GROUP BY GEO, ROLLUP(CLASS);

• Coordinatorrewritesthequerytodomostprocessingontheshards

• Supportsshardpruningandscatter-gather• Finalaggregationperformedonthecoordinator

14

ForWorkloadsthatcannotpassshardingkeyduringconnectioncheck-out

ApplicationServer


LifecycleManagementofSDB• DBAcanmanuallymoveorsplitachunkfromoneshardtoanother• Whenanewshardisadded,chunksareautomaticallyrebalanced

– UsesRMANIncrementalBackup&TransportableTablespace

• Connectionpoolsgetnotified(viaONS)aboutsplit,move,add/removeshards,auto-resharding– Applicationcaneitherreconnectoraccessread-only

• AllMAApracticesapplyforbackupandrecoveryofshardsandshardcatalog• Canpatchallshardswithonecommandviaopatchauto• EM supportsmonitoring&managementofSDB• OracleSQLDeveloperintegration

15


ProgramAgenda


What’snewinOracleDatabase18c– Sharding



Summary

1

2

3

4

16

5

PPT模板下载：www.1ppt.com/moban/ 行业PPT模板：www.1ppt.com/hangye/ 节日PPT模板：www.1ppt.com/jieri/ PPT素材下载：www.1ppt.com/sucai/PPT背景图片：www.1ppt.com/beijing/ PPT图表下载：www.1ppt.com/tubiao/ 优秀PPT下载：www.1ppt.com/xiazai/ PPT教程： www.1ppt.com/powerpoint/ Word教程： www.1ppt.com/word/ Excel教程：www.1ppt.com/excel/ 资料下载：www.1ppt.com/ziliao/ PPT课件下载：www.1ppt.com/kejian/ 范文下载：www.1ppt.com/fanwen/ 试卷下载：www.1ppt.com/shiti/ 教案下载：www.1ppt.com/jiaoan/ PPT论坛：www.1ppt.cn

Oracle Sharding for China Telecom’s

WeChat IoT Application

About China Telecom

18

n Large-scale and leading integrated information service operator in the world n Provides

Ø Landline & mobile telecommunications servicesØ Internet access servicesØ Information services and other value-added telecommunication services

n As of 2016, mobile subscribers of about 215 millionn China Telecom is building Business Support System(CRM/Billing etc.) for Narrow-

Band IoT (NB-IoT) networkØ Provides packaged offers, self service on account balance, call billing, number status, etc.,

process of problem repairs, repair progress inquiry, etc.n WeChat Service (part of BSS ecosystem) provides customer service by WeChat

Ø System will also manage 2/3/4G network usersn NB-IoT Network user base is projected to grow exponentially

Motivation for Oracle Sharding

19

n We took sharding into consideration for the projected large number of NB-IoTNetwork users

n Main benefit is scalabilityØ “We do not need to worry about what to do when we need to scale to larger number of users”

n Evaluated Oracle Sharding, MySQL, MongoDB and Maria DB Ø Determined that migration cost is too high if we go to other data stores

Ø “DBAs and Developers are familiar with Oracle database. Since Oracle has sharding, why don’t we use Oracle?”

n Migrated the application from Oracle 11g RAC to Oracle Shardingn Plan to migrate other applications of NB-IoT BSS to Oracle sharding (based on

scalability, performance and availability in production)

n Oracle 12c Sharding formally announced in March 2017Ø Sharding Beta1 released in 2015

Ø Generated lot of interest in ChinaØ In 2016 Zhejiang Mobile selected its customer center as test model

Ø HuZhou as testing ground for pre-study

n On June 29, 2017, China Telecom IOT WeChat Customer Service went into production with Oracle 12c shardingØ Application and database are both running stable after going online

Grand Release – Fast Verification

20

Current Oracle 12.2 sharded environment has 8 database servers

Created 4 independent databases：Shard Catalog and Shards across 8 nodes

Used 2-node RAC at shard-level

ShardCat shard a 1Shard A

ShardDirs shard a 2 shard c1Shard C

shard c2

Shard Bshard b 1 shard b 2

Catalog & Shard Directors

RAC RAC RAC RAC

Oracle Sharded Database

Oracle Sharding Deployment

21

n With current Oracle sharding environment, continue to deploy new applications, such as: China Telecom 10000 WeChat mini-program

n Keep in touch with Oracle experts, obtain Oracle Sharding new developments and information, upgrade to major and minor versions in time

n Increase communication with like-minded colleagues from other provincial companies, collaborate, and more widely adopt Oracle Sharding across other applications

Future Plans

22


ProgramAgenda





Summary

1

2

3

4

23

5


CustomerDrivenDatabaseDesign2 TrillionRowsofDNSQueryVolume

CharlieBakerSr.DirectorProductManagementOracleDyn GBUOctober4,2017

[email protected]


QuickAgenda

WhoisOracleDyn

TheProblem

SolutionsEvaluatedandBenchmarks

DeployedSolutionArchitecture

BenefitsandNextSteps

1

2

3

4

5

25


Dyn Strengthens Oracle Cloud Infrastructure Mission|CreatetheWorld’sMostCapableEnterpriseCloudInfrastructurefromEdgetoCore

26

Users[Customers,Partners,Employees,Things]

IT[DevOps,Administrators,

Architects]

Expectation[Highqualityexperience]

Identity

Compute BlockStorage

Database Networking

ObjectStorage

Edge

Messaging

NameResolution

DistributedContent

TrafficSteering

TheNextGenerationCloud

Internet

Core

MonitoringAvailabilityPerformanceSecurityControl

EdgeNetworking

EdgeSecurity


TrustedbyOver3,500Customers,IncludingSomeoftheMostPreeminentDigitalBrands

Over40BillionDNSQueriesPerDayOver214Billiondatapoints- poweringanalyticsandsteeringdecisions

Visitdyn.com/OOW17formoreinformationanddownloads.VisitourproductexpertsintheOracleCloudInfrastructureexpo


TheProblem– High-ScaleBilling,Reporting&Analytics• GloballyDistributedNameserversansweringDNSQueries

– FullyattributedwithASN,GEO-ID,CountryCode

• DataVolumeGenerated:– Estimatedat2 TrillionRowsfor90daysofquerydata– Rawdataof1.6TB/dayworkingouttoover144TBacross90days

• QueryLatencyMeasuredacrossMultipleSystems:– UI

• Splashpagequeries <5s• Detailedinvestigationqueries <20s

– API• Singlecustomerqueries <20s• Multiplecustomerqueries(billing/metering) <1min

28


SolutionsEvaluated

- SingleDatabase

- ShardedDatabase

ComplexsystemdesignDataloadscouldnotkeepupwithincomingdatavolumeQuerieswereunacceptablyslow

CouldingestandindexatscaleRequiredlargesharedstorageDifficultiesrunningmultipleGROUPBYqueries

LimitedexpertiseatDynUnabletosupportcomplexqueries,forcingapplicationtoimplementcomplexqueryprocessinglogicGoodPerformanceandIngestspeedComplexquerysupportPerformanceandStoragelimitedtoSingleDatabase

SuperiorIngestspeedsScalequeriesbydistributingacrossasmanyserversasneeded(upto1000)NearlimitlessTBofstoragebyaddingmoreShards(upto1000)


EvaluationResults

• LoadingfromS3toRedshift– 3to4minutes

• LoadingfromOracleObjectStoragetoShards– 500krows/second– Fullloadin1minute– 4minutesofheadroomforqueries

30

DataLoadTime

4Mins

1 Min


DeployedSolution

31

PrimaryShards

PHXRegionAD1

PrimaryShardCatalog&Director

StandbyShards

PHXRegionAD2

StandbyShardCatalog&DirectorDataGuardReplication

Shardsarereplicatedacross2differentAvailabilityDomainsforavailabilityanddisasterrecovery

UtilizedpowerfulBareMetalCloudservers(36OCPUs,512GBmemory,12.8TBlocalNVMeSSDstorage)

OracleShardedDatabase• Provenlinearscalability• Ingestspeedsscalewithnumberofshards• Constantquerytimeevenaswegrewsizeofdataset• Geo-distributedtobeclosetoourcustomers


HighSpeedDataLoad

• Utilizedfullyparalleldirect-to-sharddataloader

• ShardedarchitecturescalestheCPU,flash,andnetworkinterfaces

• Canaddshardsasneededtoaccommodatehigheringestrates

• ArchitectureappliestoIIoT,IoTandedgecomputescenarios

32

Loader Loader Loader Loader Loader

Splitter Splitter

DataSources

DataShards


TestingtheUserInterface• Basicquerytime<2seconds

– Totalrequestsoverperiodoftime

• Complexquerytime<20seconds– Requestsbygeography,operator,recursiveoverperiodoftime

33


WrapUp• OracleShardingAllowsDynto…

– Deliverfast,real-timeanalyticstoourcustomers,billingandanalyticssystems– Dynamicallyscaleourcomputinginfrastructuretohandleever-growingingestrates,whilekeepingquerytimesconstanteventhoughthedatavolumegrows

– Easilysupportgeographicdatadistributionsothatwecankeepdataclosertoourcustomers

– Collectmoredatapercustomer,sothatwecanmakebettertrafficsteeringdecisionsandprovidemorevaluetocustomers

– Createourdatalakeforanalytics– includingrecursive,RUM,synthetic,remoteaccess,CDNandWAFdatasets

• OracleDynDNSandEmailservicesofficiallylaunchatOOW– leveragingtheshardedDB

34


Schema Design

Most important aspect of sharding is proper schema design to minimize cross-shard communication

This application has one large table1. Shard by customer ID to minimize cross-shard queries2. Converted customer ID into an internal customer IDs for better data distribution3. Normalize data to reduce data size and store external to internal ID mapping4. No indexes on raw data to keep up with the incoming data rate5. Sub-partitioning by time interval (3 hours) for partition pruning and ILM (archiving/dropping old

data)6. Use compression to reduce data size (up to 5x) and speedup scans7. Use rollup table to aggregate data (1 hour granularity) to speedup queries over large time

intervals8. Distributed data splitter/loader using OCI Direct Path API

35


ProgramAgenda





Summary

1

2

3

4

36

5


DemonstratedLinearScalabilityOracleShardedDatabaseonOracleCloudInfrastructure

#ofshards

txns/sec

37

0

2,000,000

4,000,000

6,000,000

8,000,000

10,000,000

12,000,000

50 100 150 200

ShardCatalog

Swingbench

ShardDirectors


OracleSharding=BestofNoSQL+BestofOracle• OracleShardingKeyfeatures

– Automaticdatadistributionacrossasetofcommodityservers(usingconsistenthash)– Built-inandcustomizablereplication(ActiveDataGuardandGoldenGate)– Linearscalability(testedupto1,000nodes)– Plus:Compositeanduser-definedsharding,duplicatedtables,centralizedmanagement,…

• WiththestrengthofOracleRDBMSEngine– Familiarandpowerfulrelationaldatamodelandquerylanguage,includingfullnativesupportformoderndatatypeslikeJSON,Spatial,Graph,…

– Transactions,recovery,in-memory…– Industrial-strengthandscalablestorageengine– Datasecurityandaccesscontrols– DatabaseVault,LabelSecurity,…– Familiartocurrentstaff,andsupportedbyOracle

38


Summary|3Takeaways• Completeplatformforshardingan Oracledatabase

– Sharding=datapartitioning&distribution+colocationofrelateddata+datareplication+datadependentrouting+lifecyclemanagement

• Idealforapplicationswhosedataaccesspatternisprimarilyviaashardingkey andthatrequireoneormoreofthesecharacteristics:– Linearscalabilityforworkload,datasizeandnetworkthroughput– Geographicdatadistribution– FaultIsolation

• Licensing–OracleCloud:ExtremePerformance–On-premises:EnterpriseEdition+eitherActiveDataGuardorGoldenGateorRAC

39


OracleSharding|Resources

www.oracle.com/goto/oraclesharding https://nageshbattula.com

www.oracle.com/goto/maa

SocialMedia:

FollowonTwitter:@nageshbattula@OracleSharding

40

linear scalability, geo-distribution and extreme data

Documents