在混合负载环境中提供可靠的OLTP服务

ProtectingCriticalOLTPWorkloadsinaMixedWorkload

Environment

在混合负载环境中提供可靠的OLTP服务Agenda12345Typesof

WorkloadsChallengesofMixedWorkloadsWaystoManageMixedWorkloadsDemoLessons

LearnedAgenda12345Typesof

WorkloadsChallengesofMixedWorkloadsWaystoManageMixedWorkloadsDemoLessons

LearnedWorkload

CharacteristicsOLTPManyconcurrentusersSQLstatementsprocessafewrowsata

timeAnalyticalQueriesFewerconcurrentusersData-intensivequeriesprocessingmanyrowsDataLoadingandProcessingFewerconcurrentprocessesDMLprocessingmany

rowsAgenda12345Typesof

WorkloadsChallengesofMixedWorkloadsWaystoManageMixedWorkloadsDemoLessons

LearnedCompetitionfor

ResourcesCPUMemoryNetworkStorageMemoryAvoidcompetingfor

memoryCompetingformemoryends

badlyNetworkand

StorageOracleusesCPUwhenperformingnetworkandstorage

I/OLimitingCPUnaturallylimitsnetworkandstorage

I/OFocuson

CPUCPUResourcesandOLTP

WorkloadsAsCPUUtilizationincreases,thechanceofaprocessgettingscheduledonCPUdecreasesThishasanoticeableimpactonOLTPperformance

at60-70%CPU

utilizationCPUUtilizationChanceofgettingscheduled50%1in

266%1in

380%1in

590%1in

10TheMixedWorkloadDilemma—Opposingresourcemanagement

goalsWorkloadGoalCPU

StrategyOLTPFastResponse

TimeMinimizeAnalytical

QueriesThroughputandResponseTimeMaximizeData

ProcessingThroughputMaximizeAgenda12345Typesof

WorkloadsChallengesofMixedWorkloadsWaystoManageMixedWorkloadsDemoLessons

LearnedWaystoManageMixed

WorkloadsMultiple

DatabasesVirtualMachinesInstance

CagingMultitenantSingle

DatabaseRAC

ServicesDatabaseResource

ManagerMultipleDatabases:Virtual

MachinesAllocate

virtualmachines

foreach

workloadWorkloadscannotusemorethanallocatedCPUsServerQueryVM18

CPUsOLTPVM24

CPUsETLVM6

CPUsMultipleDatabases:Instance

CagingUseinstancecagingtocontrolthenumberofprocesseson

CPUUsetheCPU_COUNTparameterto

controlEnableaDatabaseResourceManager

planServerQueryCPU_COUNT=18OLTPCPU_COUNT=24ETLCPU_COUNT=6ServerMultipleDatabases:

MultitenantEnablea

CDBresource

planUse

instancecagingtolimit

CPUofindividual

PDBsOruseShares

orLimitsQueryPDBCPU_COUNT=18ETLPDBCPU_COUNT=6OLTPPDBCPU_COUNT=24SingleDatabase:RAC

ServicesUseservicesfordifferentworkloadsMapservicesfordifferentworkloadstodifferentnodesina

clusterOLTPNodeQueryNodeETLNodeServerSingleDatabase:DBRMConsumer

GroupsCreatea

DBRMplanMapworkloads

todifferent

consumergroupsUseShares

orLimitsOLTPQueryETLDatabaseResource

ManagerSharesDivideresourcesbetweenworkloadsusing

ratiosLimitsSethardlimitsonCPUutilizationforeachworkloadParallelQueuingControlthenumberofPXprocessesusedby

eachThesecanbecombinedtodevelopamoresopwhoirsktloicadatedCPUutilization

strategySharesOLTP

BusyQuery

BusyBothBusyOLTPQueryWorkloadSharesOLTP7Queries3Total10OLTPQueryOLTPQueryLimitsOLTP

BusyQuery

BusyBothBusyOLTPQueryOLTPQueryOLTPQueryWorkloadLimitsOLTPQueries30%Agenda12345Typesof

WorkloadsChallengesofMixedWorkloadsWaystoManageMixedWorkloadsDemoLessons

LearnedDemo

WorkloadsOLTPLotsofusersplayingonlinegamesShorttransactiontimesSensitivetohighserverCPUutilizationThisistheworkloadwewantto

protectQueries16usersrunninganalyticalquerieswhichtakeafewseconds

eachParallelexecutionwithparalleldegree

8SomequeriesperformtablescansfromdiskandsomescansarefrommemoryETLSingleuserperforminganELT

strategyLoad

Data2. RemoveduplicatesTransformationsAggregationParallelexecutionwithparalleldegree

24OLTP

WorkloadWecancontroltheworkloadbychangingThink

TimeDecreasingThinkTimeincreasesdemandIncreasingThinkTimedecreases

demand4000msrepresentslow

demand500msrepresentsexpectedpeak

demandStarttheOLTPworkload,setting

ThinkTimeto4000ms,representinglow

demandAbout9000transactionspersecondwithOLTPCPUisabout

3%Sub-millisecondresponsetimeinthedatabase12341. AdjustThinkTimeto2000

ms,representing

mediumdemanddoublesTPSdoublesOLTP

CPUResponsetimeinthedatabaseisalmostunchanged12341. AdjustThinkTimeto1000ms,representing

highdoublesdoublesdemandTPSOLTP

CPUResponsetimeinthedatabaseremainssub-millisecond1234AdjustThinkTimeto500ms,representingpeakdemandTPSdoublesOLTPCPUdoublesResponsetimeinthedatabasealsoincreases1324AdjustThinkTimeto400ms

tocheckforheadroomTPSincreasesOLTPCPUincreasesResponsetimeinthedatabasealsoincreases13241. ApplicationTimeindicatesresponsetimeinthedatabaseplustimewaitingforaconnection.Thereisashortspikeinwaitingforaconnectionwhendemandincreases.1Adjust

ThinkTimeto333mstocheckforheadroomTPSincreasesOLTPCPUincreasesResponsetimeinthedatabasealsoincreases13241. Somewaitingforconnectionsisnowpresentatalltimes,degradingoverallresponsetimes1Query

WorkloadWecancontroltheworkloadbychangingUsersIncreasingUsersincreasesdemandDecreasingUsersdecreases

demand1

Userrepresentslow

demand8Usersrepresentsexpectedpeak

demand1. ResetThink

Timeto

4000ms

torepresentlowdemand2. About9000transactionspersecondwith3. OLTPCPUis

about3%4. Sub-millisecondresponsetimeinthedatabase1234StarttheQueryworkload,settingUsersto1,representinglowdemandOLTPthroughputisunchangedQueryCPUisaround10%Sub-millisecondresponsetimeinthedatabase12341. AdjustUsersto

8,representing

peakdemand2. OLTPthroughputisunchanged3. Query

CPUis

around40%4. Responsetimein

thedatabaseincreases13241. AdjustParallelServersTargetto

162. OLTPthroughputisunchanged3. Query

CPUis

around10%4. Responsetimeinthedatabaseimproves1234ETL

WorkloadWecancontroltheworkloadbychangingDegreeofParallelism

(DoP)IncreasingDoPincreasesdemandDecreasingDoPdecreases

demandDegreeofParallelismof24representsexpected

demand1. ResetParallelServersTarget

to25611. Start

theETLworkload,setting

DoPto

24,representingexpecteddemand2. OLTPthroughputisunchanged3. ETLCPU

isaround25%4. Responsetimein

thedatabaseincreases1324IncreasingTheDemandFor

OLTPWecancontroltheOLTPworkloadbychangingThink

TimeDecreasingThinkTimeincreasesdemandIncreasingThinkTimedecreases

demand4000msrepresentslow

demand500msrepresentsexpectedpeak

demandAdjustThinkTimeto2000

ms,representingmediumdemandTPSdoublesOLTPCPUdoublesResponsetimeinthedatabaseincreases1324AdjustThinkTimeto1000ms,representinghighdemandTPSdoublesOLTPCPUdoublesResponsetimeinthedatabaseincreases211324AdjustThinkTimeto1000ms,representinghighdemandTPSdoublesOLTPCPUdoublesResponsetimeinthedatabaseincreases13241. Adjust

ThinkTimeto

500ms,representingexpectedpeak

demand2. TPS

increasesalittle

butbecomeserraticincreasesOLTP

CPUResponsetimein

thedatabaseincreases13241. Significantwaitingforconnectionsisnowpresent

atalltimes,degradingoverallresponsetimes1EnablingDatabaseResource

ManagerWecanreducecompetitionforCPUbyenablingDatabaseResource

ManagerLimittheCPUutilizationforQueryLimittheCPUutilizationforETLIncreasetheCPUavailablefor

OLTP1.

EnableDatabaseResourceManager,limitingQuery

to15%

CPUandlimitingETL

toTPS10%

CPUincreasesOLTPCPUincreases4. Responsetime

inthedatabaseimproves112341. Waitingforconnectionsissignificantlyreduced,dramaticallyimprovingoverallresponsetimes1IncreasingCompetitionFor

CPUWecanincreasecompetitionforCPUbyincreasingthelimitsonCPU

utilizationIncreasingthelimitonCPUutilizationforQueryincreasescompetitionIncreasingthelimitonCPUutilizationforETLincreasescompetitionIncreasingCPUutilizationmaydegradeOLTP

performanceIncreaseLimitonQueryto20%

CPUTPSisunchangedOLTPCPUisabout

thesameResponsetimeinthedatabaseincreasesa

little12341. Waitingforconnectionsincreasesa

little1IncreaseLimitonQueryto25%

CPUTPSbecomeserraticOLTPCPUisabout

thesameResponsetimeinthedatabaseincreases13241. Waitingforconnectionsisnowpresent

atalltimes,degradingoverallresponsetimes1DecreasingCompetitionFor

CPUWecandecreasecompetitionforCPUbydecreasingthelimitsonCPU

utilizationDecreasingthelimitonCPUutilizationforQuerydecreasescompetitionDecreasingthelimitonCPUutilizationforETLdecreasescompetitionDecreasingCPUutilizationmayimproveOLTP

performanceWecancheckforheadroombychangingThink

TimeDecreasingThinkTimeincreases

demandDBRMcanlimitthecompetitionforCPUduetoQueryand

ETL1. ResetLimitonQueryto15%

CPU11. Waitingforconnectionsissignificantlyreduced,improvingoverallresponsetimes11. Adjust

ThinkTimeto

400mstocheckfor2.TPSheadroomincreasesbut

becomeserraticincreasesOLTP

CPUResponsetimein

thedatabasealsoincreases

alittle13241. Significantwaitingforconnectionsisnowpresentatalltimes,degradingoverallresponsetimes.

Thereislittleheadroomforincreaseddemandfrom

OLTP.11. ReduceLimitonQueryto10%

CPUandLimit

onETLto

5%CPUTPSisconsistentOLTP

CPUincreases

alittle4. Responsetimein

thedatabaseimproves

alittle123411. Waitingforconnectionsissignificantlyreduced,dramaticallyimprovingoverallresponsetimes1ReduceLimitonQueryto5%

CPUTPSisconsistentOLTPCPUisaboutthesameResponsetimeinthedatabaseimproves

alittle12341. Waitingforconnectionsisreduced,improvingoverallresponsetimes1Agenda12345Typesof

WorkloadsChallengesofMixedWorkloadsWaystoManageMixedWorkloadsDemoLessons

LearnedHowDoYouDeterminetheWorkloadCPU

Limits?1. DonotlimitthecriticalOLTP

workloadDeterminethepeakOLTPCPU

Utilization%by

itselfUseasimpleformulatoestimatetheCPUavailableforother

workloads60~70–((OLTPCPU%)*1.3)=CPU

forothersExampleOLTPpeakCPUUtilizationis30%by

itselfLower

limit60–((30*1.3)=Otherworkloadscanuseupto~20%

CPUUpper

limit70–((30*1.3)=Otherworkloadscanuseupto~30%

CPULessons

Learned1. NeedtolimitsystemCPUutilizationtoprotectOLTP

workloadsAvoidCPUcontentionfromotherworkloadsLimitsgiveyouthehighestdegreeofcontrolResourceManagementwithParallelExecution曲卓

(

Christine

)邱翔虎

(Calvin)Waystolimitthe

DoPResource

ManagerTheMaxDoPsettinglimitstheDoPfora

consumergroupPARALLEL_DEGREE_LIMITThisparameterlimitstheDoPwhenusingAuto

DoPWaystocontrolsystemresourceswithparallelexecutionparallel_adaptive_multi_userReducesDoPbasedonsystem

loadUsuallyreducesDoPtoomuch—recommendsettingto

FALSEParallelstatement

queuingCreatesaFIFOqueueforparallel

statementsMakeSQLstatementswaitforpxresourcestobecomeavailablebeforeexecutionstartsinsteadofallowingSQLstatementstorunwithinsufficientpx

resourcesWhenalloftheparallelserverprocessesinthepoolareinuse,statements

queueTheBasicsofParallel

ExecutionParallelexecutionisusedtoreducetheexecutiontimeof

queriesMultipleprocessesworktogethertousemoreresourcesonthesystem,suchasCPUand

IOAsimpleconfigurationshouldbeused

todeterminethe

DoPCoordinateparallelparametersAvoidusinghintsandalter

sessionAresourcemanagementpolicyisneededwhenusing

parallelexecutionTokeepthesystemunder

controlstatementsareabletoexecutein

parallelCTopyoright

©e20n19Osracule

arnde/or

itsSaffQiliatesL.AvailablePXprocessesdefinedbythefollowingparameterswhicharedefinedper

instanceparallel_min_servers=32parallel_max_servers=64Bydefault,PXserverswillbeallocatedforparallelSQLandifallPXserversare

busy75PXWorkloadwithNoResource

ManagementPXWorkloadwithNoResource

ManagementQuery72StatusRequested

DoPPXAllocatedExecution

DoPPXWorkloadwithNoResource

ManagementQuery73StatusRequested

DoPPXAllocatedExecution

DoPARunning8168PXWorkloadwithNoResource

ManagementQuery74StatusRequested

DoPPXAllocatedExecution

DoPARunning8168BRunning122412PXWorkloadwithNoResource

ManagementQuery75StatusRequested

DoPPXAllocatedExecution

DoPARunning8168BRunning122412CRunning8168PXWorkloadwithNoResource

ManagementQuery76StatusRequested

DoPPXAllocatedExecution

DoPARunning8168BRunning122412CRunning8168DRunning1284PXWorkloadwithNoResource

ManagementQuery77StatusRequested

DoPPXAllocatedExecution

DoPARunning8168BRunning122412CRunning8168DRunning1284ERunning3201parallel_min_serversandparallel_max_serversstilldefinethenumberofpxserversavailablefor

executionparallel_servers_target

definesthepoolofpxserversavailableforSQLstatementsinthe

queuePXWorkloadwithResource

Management78PXWorkloadwithParallelStatement

QueuingQuery79StatusRequested

DoPPXAllocatedExecution

DoPA 8PXWorkloadwithParallelStatement

QueuingQuery80StatusRequested

DoPPXAllocatedExecution

DoPARunning8168PXWorkloadwithParallelStatement

QueuingQuery81StatusRequested

DoPPXAllocatedExecution

DoPARunning8168BRunning122412PXWorkloadwithParallelStatement

QueuingQuery82StatusRequested

DoPPXAllocatedExecution

DoPARunning8168BRunning122412