cache is not always king - v3 · – cached data survives oracle db restart, failure of active or...

Copyright©2016, Oracleand/oritsaffiliates.Allrightsreserved.|

CacheisNotAlwaysKingATaleofTwoCaches

Tirthankar LahiriVicePresidentDataandIn-MemoryTechnologiesOracleDatabase DATABASE


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

OracleConfidential– Internal 2


AddingExternalCachesontopofDatabases• AddingaCachetoyourDatabaseArchitecturehasmanybenefits• Asadvertised…

• Mostimportant:Cache=>Cash

3

Deploy

In-Memory

Transactions


Application-TierCachesforEnterpriseDatabases

• Reduceresponsetime• Increasethroughput• Increaseapplicationavailability• Reduceloadondatabase• Allowad-hocbrowsingoncacheddata

– E.g.ifTweettableiscached,findallthetweetsby@realDonaldTrump tweetedduringnormalhours

4

AppServerAppServerAppServer

TelcoServicesFinancialServices

Real-TimeAnalytics–Dashboard,Scorecard

DataMarteCommerce,

Personalization

Application TierCache


AppServerAppServerAppServer

Application-TierCachesforEnterpriseDatabases

• Reduceresponsetime• Increasethroughput• Increaseapplicationavailability• Reduceloadondatabase• Allowad-hocbrowsingoncacheddata

– E.g.ifTweettableiscached,findallthetweetsby@realDonaldTrump tweetedduringnormalworkhours

5



DataMarteCommerce,

Personalization


Cache Cache Cache


FAD#1UsingOracleDatabaseasaCache:i-Cache(Oracle8i)

• BasicIdea: CacheselectedtablesfromOracleDatabaseinasmallerapplication-tierOracledatabase

• CachecontentsareRead-Only–Queriesoncachetablestransparentlyredirectedtocache

– DMLsdirectedautomaticallytoDatabase– Cacheperiodicallyrefreshed– Applicationmustbewillingtotoleratestaledata

6

DBClient CacheAccessSQL

Router

i-CacheApplication

TransparentDBaccess

CacheRefresh


i-CacheProject:ArchitectureBenefits

• Cacheis100%compatiblewithDatabase• FullpowerofOracleDatabaseforsophisticatedcachebrowsing/subsetoperations– E.g ifTweettableiscached,findmostretweetedtweetsof@realDonaldTrumpwithoutinvolvingDatabase

• Performancebenefits:Cancolocate cacheDBwithapplicationrunningonsamehost

7

DBClient CacheAccessSQL

Router

i-CacheApplication

TransparentDBaccess

CacheRefresh


i-CacheProject:Lessons

• Drawbacksoftheapproach:– Footprint:IOrequirements,memoryfootprint,installationsize,Managementcost,etc.

– Someapplicationchangesrequired(toleratestaleness),cannotusecacheforquerieswithinatransaction

–Overallcostesp withstandbydatabasecache

• Verdict:

8

SQLRouter

i-CacheApplication

TransparentDBaccess

CacheRefresh

CacheAccess


SpiritualSuccessorstoi-Cache: ResultCachesSimpleandtransparentqueryresultcaches

– Fullyconsistentandcompletelytransparent– InvalidatedontableDMLviaChangeNotification

1. Client-SideResultCache– Clientcacheofqueryresultsforrepeatedqueries– AvoidsDBaccessforrepeatedaccesstoread-mostlytables

2. Server-SideResultCache– Cacheofresultsofqueriesandsub-queries– Reducesprocessingwhenmanyrowsaccessedtogeneratesmallresult(e.g.max,average,groupby)

– Differentqueriesbenefitiftheysharesamesub-query(e.g.resultofanaggregationorgroupby)

9

DatabaseSharedMemory

ApplicationClientMemory

ClientResultCache

ApplicationClientMemory

ClientResultCache

ServerSideResultCache

CacheInvalidationonDMLs

Bob’sAge 44

Yan’sSalary 92k Jim’s EmpID 42

Avg Salary inOrg 86K


BacktoUsingaDatabasetoCachefromaDatabase

• Resultcachesarewidelyused…– Resultcachesaresimpleandtransparent– Butlackquery/browsecapability– NoApplicationHAbenefitseither

• Again:Backtousingadatabase…– Whataboutalightweight,easilyembeddable,andsuperfastdatabase?

– TimesTen hadbuiltabigbusinessaroundcaching– AcquiredbyOraclein2005

10



DataMarteCommerce,

Personalization


Cache Cache Cache


RelationalDatabase– Purein-memory– ACIDcompliant– StandardSQL– OracleCompatibleSQL,types

PersistentandRecoverable– DatabaseandTransactionlogspersistedonlocaldiskorflashstorage

– ReplicationtostandbyandDRsystems

11

ExtremelyFast– Microsecondsresponsetime– Veryhighthroughput

HighlyAvailable– Active-Standbyandmulti-masterreplication

– Veryhighperformanceparallelreplication

– HAandDisasterRecovery

OracleTimesTenIn-MemoryDatabase


MicrosecondResponseTime

OracleTimesTenIn-MemoryDatabase11.2.2.0- IntelXeon56702.93Ghz,2CPUs,6cores/CPU- OracleLinux5.6

7.0

1.78


HLRMobileTransactionsResponseTimeResponseTimeImprovementWithTimesTenApplication-TierDatabaseCache

Intel®[email protected]/socket2hyper-threads/core32vCPU

OracleDatabaseWithTimesTenCacheOracleDatabaseWithoutTimesTenCache

Speedup17x10x17x44x69x31x29x


ApplicationReadsonHot

Standby

Standby

• Fullypersistent– Checkpoints,logging,txns

• Real-timetransactionalreplication–Multipletopologies

• Active,Standby,ReaderFarmscale-out

–Onlineupgrades

• OracleClusterware integration– Automaticdatabaseswitchover– Automaticapplicationfailover

Read-onlysubscribers• ReaderFarm• DisasterRecovery

ApplicationTransactions

Active

SubscriberSubscriberSubscriber

CheckpointfilesLogFiles

TimesTen isDesignedforHighAvailability


CachingDatausingTimesTen• CacheGroup:Declarationoftablestocache

– HierarchyofOracledatabasetables– AlwayshasaRoottable– Optionalchildtables(FK/PK)– Optionallysubsetbyrows,columns– RelatedrowsarecalledCacheInstance

• Customerrowplusrelatedorders,orderlines,historyrows

• CachedtablesarenativeTimesTen tables– StandarddatabaseAPIs– Transactional,persistent– AutomaticsynchronizationwithOracle

CUST

ORDER HISTORY

LINEITEM

CacheGroup

CUST

ORDER HISTORY

LINEITEM


Read-OnlyCacheGroups

• Forhotreferencedata:catalogs,pricelists,etc.• UpdatesoccuronOracleandtrackedinchange

logtables(viatriggers)• Periodicrefreshofchanges,configurableinterval• Tablesarepre-loadedbydefault• Optionaldynamicloadingforqueriesbyprimarykeyforverylargetables– TransparentLoad:Insertcacheinstanceoncachemiss– LRUagingofoldercacheinstancestolimitspace

Standby

Writes

Reads

Active

Reads

AutomaticRefresh

Replication

TransparentLoad


WriteableTimesTen CacheGroups

• Usefulforupdateintensivedatalikesensordata,cellularsubscriberaccountinformation

• ChangespropagatedasynchronouslytoOracle• StaticLoading:Tablesarepre-loaded• DynamicLoading:Forverylargetablesandqueriesbyprimarykey– TransparentLoad:Insertcacheinstanceoncachemiss– LRUagingofoldercacheinstancestolimitspace

AsynchronousWritestoOracle

StandbyActive

Reads

TransparentLoad

Replication

Writes Reads


TimesTen CacheAdoptionDeployedbyThousandsofCompanies


• Deliveredlowerandconsistentresponsetime;achieved40ximprovementinbothresponsetimeandthroughput

• AutomaticdatasynchronizationbetweenTimesTenandOracleDatabase

•Withbuilt-inHA,supportsautomaticfailoverandswitchover

WhyTimesTen?

ExampleofCacheDeployment

•Industry :Finance•Business :Insurance,Banking,Investment•Application:AgentTaskManagement

•Automatictaskassignmentbasedonpredefinedrules•Manuallyreassigntaskfromoneagenttoanother

• Databasescalabilitywithextremehighconcurrencyaffectingendtoendresponsetime

• Maintainusersatisfaction• Minimalchangestoexistingarchitectureandapplication•Mustbehighlyavailable

ApplicationOverview

Challenges

Solution•OracleTimesTenApplication-TierDatabaseCache•TimesTenReplicationforHighAvailability•OracleDatabase

Active Standby

ShardA

Active Standby

ShardB

Active Standby

ShardC

Active Standby

ShardD


TimesTen CachePost-Mortem

• Reasonsforadoption– TimesTen providesveryhighperformance– SophisticatedSQLoncacheddata– CacheddatasurvivesOracleDBrestart,failureofActiveorStandbynode

• Somefactorsthatlimitadoption:– OracleDatabase,especiallyonExadata,isoftenfastenough– Nottransparent:ApplicationexplicitlyconnectstotheTimesTen cache– SubsetofOraclefunctionality- applicationre-codingmaybeneeded

• E.g.CONNECTBYqueriesorFullouterjoin,SQLcallingPLSQLfunctions

– Doesnotscale-out,explicitsharding required(likePingAn)

Confidential– OracleInternal/Restricted/HighlyRestricted 20

NotaFAD,anM-SAD(Mostly-SuccessfulAspirationinDatabases)


FAD#2Scale-OutTimesTen Cache: GlobalCacheGroups

• Sharding requiressignificantapplicationchanges• Notpossiblewhendataisnotpartitionable

– Friendsandfamilycallingplans– Networksoffriendsinsocialnetworkingsites– Relateditemsinanonlinecatalog

• CacheGridwithGlobalCacheGroups:– Scale-OutcachegroupacrossaCacheGridofmember

TimesTen databases– Cacheinstanceasunitofcachecoherence– Datashippingbetweengridmembers– UsesOracleasashareddatabase

AutomaticSynchronizationwithOracle

Member1

Member2

Member3GlobalDataSharing

Protocol

Writes Reads

Writes Reads

Writes Reads


Scale-OutTimesTen Cache:CacheGrid(TimesTen 11g)

• GlobalCacheGroups:• Allcacheinstancesaccessibleeverywhere• Ondemandloadingfromothergridmembers• OndemandloadingfromOraclewhennotingrid

• Transactionalconsistency– onlycommittedversionsshippedbetweengridmembers

• AutomaticdatasynchronizationwithOracleDatabase

AutomaticSynchronizationwithOracle

Member1

Member2

Member3GlobalDataSharing

Protocol

Writes Reads

Writes Reads

Writes Reads


GlobalDataSharingExample

T1

Joe,300

T1:Get Joe’s balance

Member1

NAME,BAL

Bob,100

Member2

NAME,BALT2 Joe, 800

T2:Add500toJoe’sbalance

NAME,BALJoe, 300Bob,100

Al,200

• DataloadedfromOraclewhennotpresentingrid

• Data-transferbetweenmembersonreference

• LocationofcacheinstancesingridtrackedusingownershiptablesinOracleDatabase


ParallelQueryonGlobalCacheGroupsSelectmax(sales)

Member2

Sales111921

Member3

Sales61532

Member1

Sales102030

max(sales)max(sales)

32

30

21 32

• Parallelqueryacrossgridforreports• QueryCoordinator:

• Determines sub-querytosendtoallmembers

• Broadcastsquerytomembers

• Membersexecuteinparallel• Coordinatorperformsfinalprocessing(sort,grouping,filteroraggregation)


Clusterware

HighAvailability ofCacheGrid

• EachGridMembercanbeanActive-Standbypairforfaulttolerance

• AutomaticfailoverwhenActiveorStandbydieinanymember

• IfbothActiveandStandbydie,dataautomaticallyredistributesbyconsistenthash

• OracleClusterware monitorsandreconfiguresgridmembers

Standby

Active

Standby

Active

Standby

Active

Standby

Active

Standby

Active

Standby

Active


MultipleLevelsofHighAvailability

Grid Member HAReplication

Automatic Host FailoverAutomatic App Failover

Active StandbyReplication

Database PersistenceTransactions

CheckpointingLogging

Recovery LogFiles

Checkpoint Files

DiskPersistence

Grid HACluster MonitorAutomatic Grid Reconfiguration

Automatic App FailoverA SA S

A S A S

ClusterMonitor


GlobalCacheGroupsPostMortem• Pluses:Scale-Out,Elastic,HA,SQL,Transactions,etc,but…• Minuses:

– Coherencyisnottransparent(onlycoherentforprimarykey/cacheinstanceaccess)• Arbitraryupdatescanmessthingsupsincedifferentnodesgetoutofsync

– Datasharingisexpensive:• DatamovementrequireswritestomappingtablesonOracleDatabase• ModifieddatamustfirstbewrittentoOracleDatabase

– Totalcost(managingalargegridofseparatedatabases)

• Verdict:



SpiritualSuccessortoCacheGrid:VelocityScale(Beta)

• SingleDBimageforallapplications- NorestrictionsonSQL(unlikesharding)- Datadistributionbyconsistenthash- ParallelQuery/DMLwithfunctionshipping

• Built-inHAviafully-activenodecopies– Nodecopiesautomaticallykeptinsync

• Fullyelastic– Dataautomaticallyredistributesontopologychange- Workloadautomaticallyusesnewnodes

DotheHardWork:Transparent,shared-nothing,scale-outIn-MemoryDatabase

28

A

A

B

BC

C

D

D


Conclusions• Application-tierdatabasecachescanbeawesome• Mostimportant:Transparency,Transparency,Transparency

– CompatibilitywithsourceDatabase– Easeofapplicationmigrationtocache-basedarchitecture

• Alsoimportant:Totalcostofsolution– Softwarecost,manageabilitycost,install,upgradepatchingetc.

• Early-stagebig-picturearchitecturedecisionsaresuper-critical• Yourscientistsweresopreoccupiedwithwhethertheycould,theydidn’tstoptothinkwhethertheyshould



Q&A

cache is not always king - v3 · – cached data survives oracle db restart, failure of active or...

Documents