OracleDatabaseSizingForTheCloud
Copyright © 2015 Accenture All rights reserved. 2
Sizingquestions/scenarios
1. HowmanyvCPUsdoweneedinaVMtoallocatetheworkloadofdatabasePRD?2. Evaluate300databasesin30Groupsinto4differentconfigurationsanddecidethebest
platformforeachgroupusingCPUcorereductionasmaincriteria.3. DomyPRODdatabasesfitinanX6-21/2Rack?4. WhatistheminimumnumberofHPEBL460cGen9bladesthatIcanusetoallocatemy
workload?5. WhatistheExadataCloudServiceconfigurationthatIcanuseformydatabaseworkload?– Answer:– Using95th percentile:§ ExadataCloudServiceX5-2¼Rackwith24OCPUsat90%CPUUtilization§ ExadataCloudServiceX5-2½Rackwith36OCPUsat60%CPUUtilization
Copyright © 2015 Accenture All rights reserved. 3
SizingOverview• Resources:v CPU,Memory,Disk,IOPS,MBPS
• ExistingSystemv 144CPUs,512GB,66TB,806,000IOPS,15,000MBPS
• DatabaseWorkloadq X CPUs,XGB,XTB,XIOPS,XMBPS
• TargetSystem1v 48CPUs,480GB,42TB,530,000IOPS,10,000MBPS
• TargetSystem2v ? unknown
v Nomenclature:
o CPU=CPUThreado Core=CPUCore
v Intelo 1CPUCore=2CPUThreads
v VMWare:o vCPU=CPUThread
v Oracle:o OCPU=CPUCore
v Therefore:o 1OCPU=2vCPUs
Copyright © 2015 Accenture All rights reserved. 4
Motivation(Savings$$$)
144 CPUs, 512GB, 66TB
48 CPUs, 480GB, 42TB
Cloudification
Copyright © 2015 Accenture All rights reserved. 5
Fromasimplequestion…
Accenture Enkitec GroupOracle Database SizingProfessional Services
Cloud InfrastructureTransformation• Cloud Migration• Private Cloud• Implementation• Cloud Service Integration• Cloud Security
Other Resources• Memory• Disk• IOPS • MBPS
Optimizing Resources
• Accenture Enkitec Group Oracle Database Health Check Professional Services
Controlling Resources
• Oracle Database Resource Manager
Resource: CPUDatabase Workload ASH/AWRStandardized ASH DataPercentilesCPU RequiredSPECHeadroom
Sizing Question
Answer:
Percentile
System/Processor #CPUs
%Utilization
Copyright © 2015 Accenture All rights reserved. 6Copyright © 2015 Accenture All rights reserved.
TableofContents• LearningObjectives• StandardizeASHdata• Max,Min,Mean,andMedian• Percentiles• Decisions… Decisions… Decisions…• Headroom• SPEC• Controllingtheresources• Reducingtheconsumption
Copyright © 2015 Accenture All rights reserved. 7Copyright © 2015 Accenture All rights reserved.
LearningObjectivesAttheendofthispresentationyoushouldhavelearned:• HowtostandardizetheASHdatatoperiodic10secondsampletimeincluding0CPUutilization.
• Whatispercentileandhowitisusedforsizing.• Howpowerfulismyexistingprocessorcomparedtothehardwareusedinthecloud.
• Makeadecisionrelatedtopercentileandheadroomforspikesinutilizationandfuturegrowth.
Copyright © 2015 Accenture All rights reserved. 8
FacultyIntroduction
JorgeBarba– AccentureOperationsCloudInfrastructurePrincipalatAccentureEnkitec GroupBlog:https://jorgebarbablog.wordpress.comEmail:[email protected]
JorgeisanInfrastructurePrincipalintheInfrastructureConsulting- Cloudserviceline.Hehasover20yearsofinformationtechnologyexperienceincludingtimeinconsultingandindustry.Jorgeearnedabachelor'sdegreeinComputerEngineeringfromtheUniversityofMexicoandamaster'sdegreeinBusinessAdministrationfromRollinsCollege.Heiscurrentlycollaboratingwiththeteambuildingthecloudsuitethatintegratesthesizingtoolforclientstomeasuretheirresourcesfootprintandmigratetothecloud.
9
StandardizeASHDataAnymovetowardstandardizationisamoveintherightdirection.
DaveWilliamson.
Copyright © 2015 Accenture All rights reserved. 10
ASHandAWR
V$ACTIVE_SESSION_HISTORY DBA_HIST_ACTIVE_SESS_HISTORY
Copyright © 2015 Accenture All rights reserved. 11
Settings
statistics_level
snap_interval
retention
Copyright © 2015 Accenture All rights reserved. 12
dba_hist_active_sess_historyThefollowingqueryshowsthesampletimeandthenumberofsamples.Thisinterpretsas3sessionsactiveat16:00:53,and5sessionsactiveat16:11:34andsoon.
Copyright © 2015 Accenture All rights reserved. 13
non_standard_time
Copyright © 2015 Accenture All rights reserved. 14
Standardizationprocess(1of5)
Create a new table with the sample time starting at N0 seconds and 10s interval.
Sample time should not have gaps in this new table meaning all 10s intervals should be included.
Copyright © 2015 Accenture All rights reserved. 15
Standardizationprocess(2of5)
This PL/SQL block fills the table with 0s for cpu_data column and sample_time at 10s intervals.
Copyright © 2015 Accenture All rights reserved. 16
Standardizationprocess(3of5)Addstandardtimetonon_standard_time table
Updatethatnewaddedcolumntohavethestandardcorrespondingdatevalue
Copyright © 2015 Accenture All rights reserved. 17
Standardizationprocess(4of5)ThetableNON_STANDARD_TIMEhasnowacolumnwiththesampletimestandardized.
Copyright © 2015 Accenture All rights reserved. 18
Standardizationprocess(5of5)Wecanjointhetwotablestobringthecpu_data valuesintoCPU_STANDARD
Copyright © 2015 Accenture All rights reserved. 19
Finally…Thetablecpu_standard canbeusedtographtheCPUactivityofthedatabaseandtofurtheranalyzethedataforsizingpurposes.
20
Max,Min,Mean,andMedianTherearelies,damnlies,andstatistics.
MarkTwain.
Copyright © 2015 Accenture All rights reserved. 21
SampletimeRecommendedretentionperiodis45days.Forthepurposesofthisdemonstrationusing8daysretentionperiod.ForchartingwegeneratehourlypointswithMAXfunction.
Copyright © 2015 Accenture All rights reserved. 22
HourlyChartThetablecpu_standard canbeusedtographtheCPUactivityofthedatabaseandtofurtheranalyzethedataforsizingpurposes.Max=27,Min=1.
Copyright © 2015 Accenture All rights reserved. 23
10secondChartThespikeof27activesessionshappenedonlyforonesample,therestis1sand2s
Copyright © 2015 Accenture All rights reserved. 24
MeanandMedianTheaverage(mean)andthemedianutilizationofthedatabase:
Fordetailsontopicsonthispresentationvisit:https://jorgebarbablog.wordpress.comORhttps://orapeeps.com
25
PercentilesStatisticsarelikeabikini.Whattheyrevealisinteresting.Butwhattheyhideisvital.
AaronLevenstein.
Copyright © 2015 Accenture All rights reserved. 26
Percentiles
• WhatistheExadataCloudServiceconfigurationthatIcanuseformydatabaseworkload?– Answer:– Using95th percentile:§ ExadataCloudServiceX5-2½Rackwith36OCPUsat60%CPUUtilization
Copyright © 2015 Accenture All rights reserved. 27
Percentiles
2. Weorderthemascending
1. Letsstartwithadataset,whichcanbeourowncpu_datacolumnvalues
90th percentile
Copyright © 2015 Accenture All rights reserved. 28
Percentiles
• BecauseIneedtogetthevalueinthemiddleofpositionbetween18and19Igettheaveragebetweenposition18and19whicharevalues9and10,itsaverageis(9+10)/2=9.5
• ByFormula:
90th percentile
Copyright © 2015 Accenture All rights reserved. 29
Percentiles• 90th percentile
Percentile (in our case the 9.5): is the value below which a percentage (in our case 90%) of data falls.
Copyright © 2015 Accenture All rights reserved. 30
Percentiles• Backtoourmainexample:
• 8daysretentionincpu_standard• Totalof69,120datapoints(360*24*8)
Copyright © 2015 Accenture All rights reserved. 31
PercentilesBytheHour
Copyright © 2015 Accenture All rights reserved. 32
PercentilesNowthatwehaveone97thpercentilevalueforeachhourwecancalculatethemaximum97thpercentilevalueforthewholedatasetgroupedbythehour.“Thebusiest97thpercentilehour”
Copyright © 2015 Accenture All rights reserved. 33
PercentilesIfwewerewillingtohavelongerwaitswecouldusethe95thpercentileinsteadofthe97thpercentile.Withthe95thpercentilemeanswegetaCPUvalueonwhich95%ofvaluesfallbelowand5%areabove.Thismeansthatwecanhavewaitsfor5%ofthehour;5%ofeachhouris3minutes.Wecanbuildatabletoshowhowlongthewaitscouldbeaccordingtoeachpercentilevalue:
Questionis:Howdowepickthepercentilethatisappropriateformydatabaseworkload?
34
Samethingagain,makingsureallisclear.Repetitionisthemotheroflearning,thefatherofaction,whichmakesitthearchitectofaccomplishment.
ZigZaglar.
Copyright © 2015 Accenture All rights reserved. 35
CompleteNewExampleStartprofilingthedatabaseworkloadbygettingmax,min,meanandmedian.
Copyright © 2015 Accenture All rights reserved. 36
Hourly
Copyright © 2015 Accenture All rights reserved. 37
CPUutilizationaroundbusiesthour
Copyright © 2015 Accenture All rights reserved. 38
The95th percentile
If we use our 95th percentile because we are willing to tolerate a maximum of 3 min. wait, the calculation will be:
Copyright © 2015 Accenture All rights reserved. 39
PercentilestableIf we get the results for various percentile values we can build a table like the following:
Copyright © 2015 Accenture All rights reserved. 40
Howmuchpain
Here is where we come back to our question of what percentile to use.Answer: It depends on how much pain (wait) we are willing to tolerate.
If we choose 4 CPUs for our system our potentially longest wait on our worst hour would be 36 seconds; if we are willing to tolerate a wait of 1.8 min in our worst hour we can choose the 2 CPUs.
For this particular example we observe that going from 36 seconds to 1.8 min. will save 2 CPUs out of 4 CPUs, meaning I would have to double the amount of resources if I want the extra coverage.
Copyright © 2015 Accenture All rights reserved. 41
Samplesabove(2CPUs)
To get the code to populate the table samples_above, visit:https://jorgebarbablog.wordpress.com/2016/12/29/sizing-post-4-how-much-pain-are-we-willing-to-tolerate/
Copyright © 2015 Accenture All rights reserved. 42
Howlong(2CPUs)
Copyright © 2015 Accenture All rights reserved. 43
Samplesabove(1CPU)
The first row shows that there are 35 samples above 1 CPU. There are 360 samples in one hour, thus 35 samples represent 9.7%, very close to 10% which is 6 minutes.
Copyright © 2015 Accenture All rights reserved. 44
CPUsrequired(SourceSystem)
How much pain are we willing to tolerate?We have to make a decision
Copyright © 2015 Accenture All rights reserved. 45
Headroom
Because we want to leave some headroom for unexpected spikes in CPU utilization we could start assigning 3 vCPUs to this database workload for a 25% headroom.
v Consider at least a 25% headroom.
The advantage when using VMs is that the resources can be reallocated relatively easy.
For an Oracle Public Cloud service we could accommodate in a 2 OCPUs = 4 vCPUs for a 50% headroom.
What if the Source System is a different serverThan the Target System?
Copyright © 2015 Accenture All rights reserved. 46
StandardPerformanceEvaluationCorporation
Assume we identified we required 24 CPUs for our database workload when running our database on a BL460c Gen9 server with the Intel Xeon E5-2699 v3 processor, which has a “result per CPU thread” of 18.8.
How many CPU threads are needed in the new DL360 Gen9 with Intel Xeon E5-2640 v3 to cover for those 24 CPUs?
Copyright © 2015 Accenture All rights reserved. 47
AllocationinTargetSystemincludingHeadroom
If we required 20 CPUs in our target server DL360 and we want a 30% headroom for unexpected spikes and growth, this means our 20 CPUs represent 70%, in order to get a total we calculate like this:
Copyright © 2015 Accenture All rights reserved. 48
OraclePublicCloudShapesHere we are starting a new example (repetition is good for learning)Our example now targets the Oracle Public CloudHere are the shapes:
Copyright © 2015 Accenture All rights reserved. 49
StandardPerformanceEvaluationCorporation
Assume we identified we required 16 CPUs for our database workload when running our database on a BL460c Gen9 server with the Intel Xeon E5-2699 v3 processor, which has a “result per CPU thread” of 18.8.
How many CPU threads are needed in the OPC shapes with an Intel Xeon E5-2698 v3 with 19.7 “result per CPU thread” to cover for those 16 CPUs?
Copyright © 2015 Accenture All rights reserved. 50
AllocationintheCloudincludingheadroom
If we require 16 CPUs in our cloud target and we want a 50% headroom for unexpected spikes and growth, this means our 16 CPUs represent 50%, in order to get a total we calculate like this:
51
ControllingResourceUtilizationandReduceConsumptionItdoesnotmatterhowmanyresourcesyouhave.Ifyoudon’tknowhowtousethem,theywillneverbeenough.
Government
Copyright © 2015 Accenture All rights reserved. 52
ControllingtheresourcesOne key to a healthy database is maintaining a healthy CPU load.
Excessive CPU load can destabilize the server and expose operating system bugs.
Copyright © 2015 Accenture All rights reserved. 53
Sizingfor300or600?ReductionofCPUutilizationanalyzingAWRreports
HowaboutaHealthCheck?
Copyright © 2015 Accenture All rights reserved. 54
Optimizingtheresourcesutilization
Copyright © 2015 Accenture All rights reserved. 55
SizingComplexity
• MultipleInstance,hundredsofdatabases• Differenttypesofhardware:
• X6-2,HP(intel),IBM(Power7),Sun(SPARC)• MultipleTargetConfigurationswith
multiplenodes• Several“WhatIf”Scenarios• MultiplePercentileoptions• Resources:CPU,RAM,Disk,IOPS,MBPS,
PHYIO,Network,Interconnect• Databaseallocationlayout• Instanceallocationlayout• GroupingsbyVM• FailoverScenarios• UtilizationandResourceallocationCharts
Copyright © 2015 Accenture All rights reserved. 56
InstanceAllocation
Copyright © 2015 Accenture All rights reserved. 57
FailoverScenarios
Copyright © 2015 Accenture All rights reserved. 58
AEGSizingProcess
Copyright © 2015 Accenture All rights reserved. 59Copyright © 2015 Accenture All rights reserved.
Summary• StandardizeASHdata• Max,Min,Mean,andMedian• Percentiles• Decisions… Decisions… Decisions…• Headroom• SPEC• Controllingtheresources• Reducingtheconsumption• Professionalservices
Copyright © 2015 Accenture All rights reserved. 60
Fromasimplequestion…
Accenture Enkitec GroupOracle Database SizingProfessional Services
Cloud InfrastructureTransformation• Cloud Migration• Private Cloud• Implementation• Cloud Service Integration• Cloud Security
Other Resources• Memory• Disk• IOPS • MBPS
Optimizing Resources
• Accenture Enkitec Group Oracle Database Health Check Professional Services
Controlling Resources
• Oracle Database Resource Manager
Resource: CPUDatabase Workload ASH/AWRStandardized ASH DataPercentilesCPU RequiredSPECHeadroom
Sizing Question
Answer:
Percentile
System/Processor #CPUs
%Utilization
61
Q&A
https://jorgebarbablog.wordpress.com