MTCacheICDE2004

-1-MTCache:TransparentMid-TierDatabaseCachinginSQLServerAbstractManyapplicationstodayruninamulti-tierenviron-mentwithbrowser-basedclients,mid-tier(application)serversandabackenddatabaseserver.Mid-tierdatabasecachingattemptstoimprovesystemthroughputandscal-abilitybyoffloadingpartofthedatabaseworkloadtoin-termediatedatabaseserversthatpartiallyreplicatedatafromthebackendserver.Thefactthatsomequeriesareoffloadedtoanintermediateservershouldbecompletelytransparenttoapplications-oneofthekeydistinctionsbetweencachingandreplication.MTCacheisaprototypemid-tierdatabasecachingsolutionforSQLServerthatachievesthistransparency.ItbuildsonSQLServer'ssup-portformaterializedviews,distributedqueriesandrepli-cation.ThispaperdescribesMTCacheandreportsexperimentalresultsontheTPC-Wbenchmark.Theex-perimentsshowthatasignificantpartofthequerywork-loadcanbeoffloadedtocacheservers,resultingingreatlyimprovedscale-outontheread-dominatedworkloadsofthebenchmark.Replicationoverheadwassmallwithanaveragereplicationdelayoflessthantwoseconds.1.IntroductionManyapplicationstodayaredesignedforamulti-tierenvironmenttypicallyconsistingofbrowser-basedclients,mid-tierapplicationserversandabackenddatabaseserver.Applicationserversdonotmaintainpersistentstateandtypicallyrunonfairlyinexpensivemachines.Hence,bot-tlenecksintheapplicationservertiercanbesolvedeasilyandcheaplybyincreasingthenumberofservers.Allpersistentstateismaintainedbythebackenddata-base,whichtypicallyrunsonasinglehigh-endmachine.Auserrequestmaycausetensorevenhundredsofqueriesagainstthebackenddatabase.Theoverallresponsetimeseenbyauserisoftendominatedbytheaggregatequeryresponsetime,particularlywhenthebackendsystemishighlyloaded.Toimproveperformanceandscalability,onemusteitherreducetheloadonthebackendserverorincreaseitscapacity.Thegoalofmid-tierdatabasecach-ingistotransfersomeoftheloadfromthebackenddata-baseservertointermediatedatabaseservers.Aninterme-diateserverhasalocaldatabasestoringacopyofsomeofthedatafromthebackenddatabase,whichallowssomequeriestobecomputedlocally.Akeyrequirementofmid-tierdatabasecachingthatalsodistinguishescachingfromreplicationisthatitmustbetransparenttoapplications.Thatis,addingcachingshouldnotrequireanychangesinapplications.Inparticu-lar,applicationsshouldnotbeawareofwhatiscachedandshouldnotberesponsibleforroutingrequeststothecacheortothebackendserver.Ifapplicationsarerequiredtorouterequeststotheappropriateserver,thecachingstrat-egycannotbechangedwithoutchangingapplications.ThispaperdescribesMTCache[21],amid-tierdata-basecachesolutionforMicrosoftSQLServerthatachievesthisgoal.WedescribeourdesignandprototypeimplementationandreportexperimentalresultsontheTPC-Wbenchmark.MTCacheisbasedonthefollowingapproach.•AshadowdatabaseiscreatedontheMTCacheserverthatcontainsthesametablesasthebackenddatabaseandalsothesameconstraints,indexes,views,permis-sions.Alltablesareemptybutthestatisticsmain-tainedontables,indexesandmaterializedviewsre-flectthedataonthebackendserver.•WhatdatatocacheisdefinedbycreatingmaterializedviewsontheMTCacheserver.Thesematerializedviewsmaybeselectionsandprojectionsoftablesormaterializedviewsonthebackendserver.•ThematerializedviewsontheMTCacheserverarekeptuptodatebySQLServerreplication.Whenaviewiscreated,amatchingreplicationsubscriptionisautomaticallycreatedandtheviewispopulated.•AllqueriesaresubmittedtotheMTCacheserverwhoseoptimizerdecideswhethertocomputeaquerylocally,remotelyorpartlocallyandpartremotely.Optimizationisentirelycostbased.•Allinserts,deletesandupdatesaresubmittedtotheMTCacheserver,whichthentransparentlyforwardsthemtothebackendserver.MTCacheexploitsSQLServer’ssupportformaterial-izedviews,distributedqueriesandtransactionalreplica-tion.Theuseofreplicationisnotinherentinourdesign;othermechanismsforpropagatingupdatesliketwo-phasecommitcouldbeused.Ourapproachresemblestheap-Per-Å[e-mail][e-mail][e-mail]-2-proachtakenbyDBCache[2][3][14][20].However,DBCacheappearstobelimitedtocachingofcompletetableswhilewealsoallowcachingofhorizontalandverti-calsubsetsoftablesandmaterializedviews.Inaddition,DBCacheappearstoalwaysusethecachedversionofatablewhenitisreferencedinaquery,regardlessofthecost.InMTCachethisisnotalwaysthecase:thedecisionisdeeplyintegratedintotheoptimizationprocessandisentirelycost-based.Forinstance,ifthereisanindexonthebackendthatgreatlyreducesthecostofthequery;itwillbeexecutedonthebackenddatabase.MTCachealsoincludesimprovedoptimizationofpa-rameterizedqueriesthatresultsinmoreefficientuseofcacheddata.ThisisdonebyproducingdynamicplanswherethedecisionwhethertouseacachedviewismadeatruntimebasedontheactualparametervaluesThisisthefirstimplementationofdynamicplansinanindustrial-strengthdatabasesystem.Dynamicplansarecrucialfortheperformanceofparameterizedqueriesinacachingenvironmentbecausetheyexploitthecacheddataeffi-cientlywhileavoidingtheneedforfrequentreoptimiza-tion.TimesTenalsooffersamid-tiercachingsolutionbuiltontheirin-memorydatabaseman