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

1、Protecting Critical OLTP Workloads in a Mixed Workload Environment在混合负载环境中提供可靠的OLTP服务Agenda12345Types of WorkloadsChallenges of Mixed Workloads Ways to Manage Mixed Workloads DemoLessons LearnedAgenda12345Types of WorkloadsChallenges of Mixed Workloads Ways to Manage Mixed Workloads DemoLessons Lear

2、nedWorkload CharacteristicsOLTPMany concurrent usersSQL statements process a few rows at a timeAnalytical QueriesFewer concurrent usersData-intensive queries processing many rowsData Loading and ProcessingFewer concurrent processesDML processing many rowsAgenda12345Types of WorkloadsChallenges of Mi

3、xed Workloads Ways to Manage Mixed Workloads DemoLessons LearnedCompetition for ResourcesCPUMemoryNetworkStorageMemoryAvoid competing for memoryCompeting for memory ends badlyNetwork and StorageOracle uses CPU when performing network and storage I/OLimiting CPU naturally limits network and storage I

4、/OFocus on CPUCPU Resources and OLTP WorkloadsAs CPU Utilization increases, the chance of a process getting scheduled on CPU decreasesThis has a noticeable impact on OLTP performance at60-70% CPU utilizationCPUUtilizationChance of getting scheduled50%1 in 266%1 in 380%1 in 590%1 in 10The Mixed Workl

5、oad DilemmaOpposing resource management goalsWorkloadGoalCPU StrategyOLTPFast Response TimeMinimizeAnalytical QueriesThroughput and Response TimeMaximizeData ProcessingThroughputMaximizeAgenda12345Types of WorkloadsChallenges of Mixed Workloads Ways to Manage Mixed Workloads DemoLessons LearnedWays

6、to Manage Mixed WorkloadsMultiple DatabasesVirtual MachinesInstance CagingMultitenantSingle DatabaseRAC ServicesDatabase Resource ManagerMultiple Databases: Virtual MachinesAllocate virtualmachines foreach workloadWorkloads cannot use more than allocated CPUsServerQuery VM18 CPUsOLTP VM24 CPUsETL VM

7、6 CPUsMultiple Databases: Instance CagingUse instance caging to control the number of processes on CPUUse the CPU_COUNT parameter to controlEnable a Database Resource Manager planServerQueryCPU_COUNT=18OLTPCPU_COUNT=24ETLCPU_COUNT=6ServerMultiple Databases: MultitenantEnable a CDBresource planUse in

8、stancecaging to limit CPUof individual PDBsOr use Shares orLimitsQuery PDBCPU_COUNT=18ETL PDBCPU_COUNT=6OLTP PDBCPU_COUNT=24Single Database: RAC ServicesUse services for different workloadsMap services for different workloads to different nodes in a clusterOLTPNodeQuery NodeETLNodeServerSingle Datab

9、ase: DBRM Consumer GroupsCreate a DBRMplanMap workloads todifferent consumergroupsUse Shares orLimitsOLTPQueryETLDatabase Resource ManagerSharesDivide resources between workloads using ratiosLimitsSet hard limits on CPU utilization for each workloadParallel QueuingControl the number of PX processes

10、used by eachThese can be combined to develop a more sopwhoirsktloicadated CPU utilization strategySharesOLTP BusyQuery BusyBoth BusyOLTPQuer yWorkloadSharesOLTP7Queries3Total10OLTPQuer yOLTPQuer yLimitsOLTP BusyQuery BusyBoth BusyOLTPQuer yOLTPQuer yOLTPQuer yWorkloadLimitsOLTPQueries30%Agenda12345T

11、ypes of WorkloadsChallenges of Mixed Workloads Ways to Manage Mixed Workloads DemoLessons LearnedDemo WorkloadsOLTPLots of users playing online gamesShort transaction timesSensitive to high server CPU utilizationThis is the workload we want to protectQueries16 users running analytical queries which

12、take a few seconds eachParallel execution with parallel degree 8Some queries perform table scans from disk and some scans are from memoryETLSingle user performing an ELT strategyLoad Data2.RemoveduplicatesTransformationsAggregationParallel execution with parallel degree 24OLTP WorkloadWe can control

13、 the workload by changing Think TimeDecreasing Think Time increases demand Increasing Think Time decreases demand4000ms represents low demand500ms represents expected peak demandStart the OLTP workload, setting Think Time to 4000 ms, representing low demandAbout 9000 transactions per second withOLTP

14、 CPU is about 3%Sub- millisecond response time in the database12341.Adjust Think Time to 2000 ms,representing medium demanddoublesTPS doublesOLTP CPUResponsetime in the database is almostunchanged12341.Adjust Think Time to 1000 ms,representing highdoublesdoublesdemandTPSOLTP CPUResponsetime in the d

15、atabase remainssub-millisecond1234Adjust Think Time to 500 ms, representi ng peak demandTPS doublesOLTP CPU doublesResponse time in the database also increases1324Adjust Think Time to 400 ms to check for headroomTPS increasesOLTP CPU increasesResponse time in the database also increases13241.Applica

16、tion Time indicates response time in the database plus time waiting for a connection. There is a short spike in waiting for a connection when demand increases.1Adjust Think Time to 333 ms to check for headroomTPS increasesOLTP CPU increasesResponse time in the database also increases13241.Some waiti

17、ng for connections is now present at all times, degrading overall response times1Query WorkloadWe can control the workload by changing UsersIncreasing Users increases demand Decreasing Users decreases demand1 User represents low demand8 Users represents expected peak demand1.Reset Think Time to 4000

18、ms torepresentlowdemand2.About9000transaction s per second with3.OLTP CPU is about3%4.Sub- millisecond response time in the database1234Start the Query workload, setting Users to 1, representing low demandOLTP throughput is unchangedQuery CPU is around 10%Sub- millisecond response time in the databa

19、se12341.Adjust Users to 8,representing peakdemand2.OLTPthroughputisunchanged3.Query CPUis around 40%4.Response time in thedatabaseincreases13241.Adjust ParallelServersTarget to 162.OLTPthroughputisunchanged3.Query CPUis around 10%4.Response time in the databaseimproves1234ETL WorkloadWe can control

20、the workload by changing Degree of Parallelism (DoP)Increasing DoP increases demand Decreasing DoP decreases demandDegree of Parallelism of 24 represents expected demand1.Reset Parallel Servers Target to 25611.Start theETLworkload,setting DoPto 24,representing expecteddemand2.OLTPthroughputisunchang

21、ed3.ETL CPU isaround25%4.Responsetime in thedatabaseincreases1324Increasing The Demand For OLTPWe can control the OLTP workload by changing Think TimeDecreasing Think Time increases demand Increasing Think Time decreases demand4000ms represents low demand500ms represents expected peak demandAdjust T

22、hink Time to 2000 ms, representin g medium demandTPS doublesOLTP CPU doublesResponse time in the database increases1324Adjust Think Time to 1000 ms, representin g high demandTPS doublesOLTP CPU doublesResponse time in the database increases211324Adjust Think Time to 1000 ms, representin g high deman

23、dTPS doublesOLTP CPU doublesResponse time in the database increases13241.Adjust ThinkTime to 500 ms,representingexpectedpeak demand2.TPS increasesa little butbecomeserraticincreasesOLTP CPUResponsetime in thedatabaseincreases13241.Significan t waiting for connectio ns is now present at all times, de

24、grading overall response times1Enabling Database Resource ManagerWe can reduce competition for CPU by enabling Database Resource ManagerLimit the CPU utilization for Query Limit the CPU utilization for ETL Increase the CPU available for OLTP1.EnableDatabaseResourceManager,limitingQuery to15% CPUandl

25、imitingETL toTPS10% CPUincreasesOLTPCPUincreases4.Responsetime inthedatabaseimproves112341.Waiting for connectio ns is significant ly reduced, dramatica lly improving overall response times1Increasing Competition For CPUWe can increase competition for CPU by increasing the limits on CPU utilizationI

26、ncreasing the limit on CPU utilization for Query increases competitionIncreasing the limit on CPU utilization for ETL increases competitionIncreasing CPU utilization may degrade OLTP performanceIncrease Limit on Query to 20% CPUTPS is unchange dOLTP CPU is about the sameResponse time in the database

27、 increases a little12341.Waiting for connectio ns increases a little1Increase Limit on Query to 25% CPUTPS becomes erraticOLTP CPU is about the sameResponse time in the database increases13241.Waiting for connectio ns is now present at all times, degrading overall response times1Decreasing Competiti

28、on For CPUWe can decrease competition for CPU by decreasing the limits on CPU utilizationDecreasing the limit on CPU utilization for Query decreases competitionDecreasing the limit on CPU utilization for ETL decreases competitionDecreasing CPU utilization may improve OLTP performanceWe can check for

29、 headroom by changing Think TimeDecreasing Think Time increases demandDBRM can limit the competition for CPU due to Query and ETL1.Reset Limit on Query to 15% CPU11.Waiting for connectio ns is significant ly reduced, improving overall response times11.Adjust Think Time to 400ms to checkfor2.TPSheadr

30、oomincreasesbut becomeserraticincreasesOLTP CPUResponsetime in thedatabasealsoincreases alittle13241.Significant waiting for connections is now present at all times, degrading overall response times. There is little headroom for increased demand from OLTP.11.Reduce Limit on Query to 10% CPUand Limit

31、 onETL to 5%CPUTPS is consistentOLTP CPUincreases a little4.Responsetime in thedatabaseimproves alittle123411.Waiting for connections is significantly reduced, dramatically improving overall response times1Reduce Limit on Query to 5% CPUTPS is consistentOLTP CPU is about the sameResponse time in the

32、 database improves a little12341.Waiting for connections is reduced, improving overall response times1Agenda12345Types of WorkloadsChallenges of Mixed Workloads Ways to Manage Mixed Workloads DemoLessons LearnedHow Do You Determine the Workload CPU Limits?1.Do not limit the critical OLTP workloadDet

33、ermine the peak OLTP CPU Utilization% by itselfUse a simple formula to estimate the CPU available for other workloads6070 (OLTP CPU %) * 1.3) = CPU forothersExampleOLTP peak CPU Utilization is 30% by itselfLower limit60 (30* 1.3) = Other workloads can use up to 20% CPUUpper limit70 (30* 1.3) = Other

34、 workloads can use up to 30% CPULessons Learned1.Need to limit system CPU utilization to protect OLTP workloadsAvoid CPU contention from other workloadsLimits give you the highest degree of controlResource Management with Parallel Execution曲卓 ( Christine )邱翔虎 ( Calvin )Real-World Performance TeamOra

35、cle Database DevelopmentWays to limit the DoPResource ManagerThe Max DoP setting limits the DoP for a consumer groupPARALLEL_DEGREE_LIMITThis parameter limits the DoP when using Auto DoPWays to control system resources with parallel executionparallel_adaptive_multi_userReduces DoP based on system lo

36、adUsually reduces DoP too muchrecommend setting to FALSEParallel statement queuingCreates a FIFO queue for parallel statementsMake SQL statements wait for px resources to become available before execution starts instead of allowing SQL statements to run with insufficient px resourcesWhen all of the

37、parallel server processes in the pool are in use, statements queueThe Basics of Parallel ExecutionParallel execution is used to reduce the execution time of queriesMultiple processes work together to use more resources on the system, such as CPU and IOA simple configuration should be used todetermin

38、e the DoPCoordinate parallel parameters Avoid using hints and alter sessionA resource management policy is needed when using parallelexecutionTo keep the system under controlstatements are able to execute in parallelCTopyoright e20n19 Osracule arnde/or itsSaffQiliatesL.Available PX processes defined

39、 by the following parameters which are defined per instanceparallel_min_servers=32 parallel_max_servers=64By default, PX servers will be allocated for parallel SQL and if all PX servers are busyOracle Confidential Internal/Restricted/Highly Restricted75PX Workload with No Resource ManagementPX Workl

40、oad with No Resource ManagementQuer y72Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPPX Workload with No Resource ManagementQuer y73Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPARunnin

41、g8168PX Workload with No Resource ManagementQuer y74Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPARunnin g8168BRunnin g122412PX Workload with No Resource ManagementQuer y75Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequest

42、e d DoPPXAllocate dExecutio n DoPARunnin g8168BRunnin g122412CRunnin g8168PX Workload with No Resource ManagementQuer y76Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPARunnin g8168BRunnin g122412CRunnin g8168DRunnin g1284PX Workload with No Re

43、source ManagementQuer y77Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPARunnin g8168BRunnin g122412CRunnin g8168DRunnin g1284ERunnin g3201parallel_min_serve rs and parallel_max_serve rs still define the number of px servers available for execu

44、tionparallel_servers_t arget defines the pool of px servers available for SQL statements in the queuePX Workload with Resource Management78Oracle Confidential Internal/Restricted/Highly RestrictedPX Workload with Parallel Statement QueuingQuer y79Oracle Confidential Internal/Restricted/Highly Restri

45、ctedStatusRequeste d DoPPXAllocate dExecutio n DoPA8PX Workload with Parallel Statement QueuingQuer y80Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPARunnin g8168PX Workload with Parallel Statement QueuingQuer y81Oracle Confidential Internal/R

46、estricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPARunnin g8168BRunnin g122412PX Workload with Parallel Statement QueuingQuer y82Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPARunnin g8168BRunnin g122412CRunnin g8168PX W

47、orkload with Parallel Statement QueuingQuer y83Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPARunnin g8168BRunnin g122412CRunnin g8168DQueue d12PX Workload with Parallel Statement QueuingQuer y84Oracle Confidential Internal/Restricted/Highly R

48、estrictedStatusRequeste d DoPPXAllocate dExecutio n DoPAFinishe d8168BRunnin g122412CRunnin g8168DQueue d12PX Workload with Parallel Statement QueuingQuer y85Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPAFinishe d8168BRunnin g122412CRunnin g8

49、168DRunnin g122412PX Workload with Parallel Statement QueuingQuer y86Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPAFinishe d8168BRunnin g122412CRunnin g8168DRunnin g122412EQueue d32PX Workload with Parallel Statement QueuingQuer y87Oracle Confidential Internal/Restricted/Highly RestrictedStatusRequeste d DoPPXAllocate dExecutio n DoPdd d d dAFinishe8168BFinishe122412CFinishe8168DFinishe122412EQueue32PX Workload with Parallel Statement QueuingQuer y88Oracle Confidential Internal/Restricted/H