barba sizing for the cloud · • what is percentile and how it is used for sizing. • how...

OracleDatabaseSizingForTheCloud

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

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

Motivation(Savings$$$)

144 CPUs, 512GB, 66TB

48 CPUs, 480GB, 42TB

Cloudification

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

TableofContents• LearningObjectives• StandardizeASHdata• Max,Min,Mean,andMedian• Percentiles• Decisions… Decisions… Decisions…• Headroom• SPEC• Controllingtheresources• Reducingtheconsumption

LearningObjectivesAttheendofthispresentationyoushouldhavelearned:• HowtostandardizetheASHdatatoperiodic10secondsampletimeincluding0CPUutilization.

• Whatispercentileandhowitisusedforsizing.• Howpowerfulismyexistingprocessorcomparedtothehardwareusedinthecloud.

• Makeadecisionrelatedtopercentileandheadroomforspikesinutilizationandfuturegrowth.

FacultyIntroduction

JorgeBarba– AccentureOperationsCloudInfrastructurePrincipalatAccentureEnkitec GroupBlog:https://jorgebarbablog.wordpress.comEmail:jorge.barba@accenture.com

JorgeisanInfrastructurePrincipalintheInfrastructureConsulting- Cloudserviceline.Hehasover20yearsofinformationtechnologyexperienceincludingtimeinconsultingandindustry.Jorgeearnedabachelor'sdegreeinComputerEngineeringfromtheUniversityofMexicoandamaster'sdegreeinBusinessAdministrationfromRollinsCollege.Heiscurrentlycollaboratingwiththeteambuildingthecloudsuitethatintegratesthesizingtoolforclientstomeasuretheirresourcesfootprintandmigratetothecloud.

StandardizeASHDataAnymovetowardstandardizationisamoveintherightdirection.

DaveWilliamson.

ASHandAWR

V$ACTIVE_SESSION_HISTORY DBA_HIST_ACTIVE_SESS_HISTORY

Settings

statistics_level

snap_interval

retention

dba_hist_active_sess_historyThefollowingqueryshowsthesampletimeandthenumberofsamples.Thisinterpretsas3sessionsactiveat16:00:53,and5sessionsactiveat16:11:34andsoon.

non_standard_time

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.

Standardizationprocess(2of5)

This PL/SQL block fills the table with 0s for cpu_data column and sample_time at 10s intervals.

Standardizationprocess(3of5)Addstandardtimetonon_standard_time table

Updatethatnewaddedcolumntohavethestandardcorrespondingdatevalue

Standardizationprocess(4of5)ThetableNON_STANDARD_TIMEhasnowacolumnwiththesampletimestandardized.

Standardizationprocess(5of5)Wecanjointhetwotablestobringthecpu_data valuesintoCPU_STANDARD

Finally…Thetablecpu_standard canbeusedtographtheCPUactivityofthedatabaseandtofurtheranalyzethedataforsizingpurposes.

Max,Min,Mean,andMedianTherearelies,damnlies,andstatistics.

MarkTwain.

SampletimeRecommendedretentionperiodis45days.Forthepurposesofthisdemonstrationusing8daysretentionperiod.ForchartingwegeneratehourlypointswithMAXfunction.

HourlyChartThetablecpu_standard canbeusedtographtheCPUactivityofthedatabaseandtofurtheranalyzethedataforsizingpurposes.Max=27,Min=1.

10secondChartThespikeof27activesessionshappenedonlyforonesample,therestis1sand2s

MeanandMedianTheaverage(mean)andthemedianutilizationofthedatabase:

Fordetailsontopicsonthispresentationvisit:https://jorgebarbablog.wordpress.comORhttps://orapeeps.com

PercentilesStatisticsarelikeabikini.Whattheyrevealisinteresting.Butwhattheyhideisvital.

AaronLevenstein.

Percentiles

• WhatistheExadataCloudServiceconfigurationthatIcanuseformydatabaseworkload?– Answer:– Using95th percentile:§ ExadataCloudServiceX5-2½Rackwith36OCPUsat60%CPUUtilization

Percentiles

2. Weorderthemascending

1. Letsstartwithadataset,whichcanbeourowncpu_datacolumnvalues

90th percentile

Percentiles

• BecauseIneedtogetthevalueinthemiddleofpositionbetween18and19Igettheaveragebetweenposition18and19whicharevalues9and10,itsaverageis(9+10)/2=9.5

• ByFormula:

90th percentile

Percentiles• 90th percentile

Percentile (in our case the 9.5): is the value below which a percentage (in our case 90%) of data falls.

Percentiles• Backtoourmainexample:

• 8daysretentionincpu_standard• Totalof69,120datapoints(360*24*8)

PercentilesBytheHour

PercentilesNowthatwehaveone97thpercentilevalueforeachhourwecancalculatethemaximum97thpercentilevalueforthewholedatasetgroupedbythehour.“Thebusiest97thpercentilehour”

PercentilesIfwewerewillingtohavelongerwaitswecouldusethe95thpercentileinsteadofthe97thpercentile.Withthe95thpercentilemeanswegetaCPUvalueonwhich95%ofvaluesfallbelowand5%areabove.Thismeansthatwecanhavewaitsfor5%ofthehour;5%ofeachhouris3minutes.Wecanbuildatabletoshowhowlongthewaitscouldbeaccordingtoeachpercentilevalue:

Questionis:Howdowepickthepercentilethatisappropriateformydatabaseworkload?

Samethingagain,makingsureallisclear.Repetitionisthemotheroflearning,thefatherofaction,whichmakesitthearchitectofaccomplishment.

ZigZaglar.

CompleteNewExampleStartprofilingthedatabaseworkloadbygettingmax,min,meanandmedian.

Hourly

CPUutilizationaroundbusiesthour

The95th percentile

If we use our 95th percentile because we are willing to tolerate a maximum of 3 min. wait, the calculation will be:

PercentilestableIf we get the results for various percentile values we can build a table like the following:

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.

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/

Howlong(2CPUs)

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.

CPUsrequired(SourceSystem)

How much pain are we willing to tolerate?We have to make a decision

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?

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?

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:

OraclePublicCloudShapesHere we are starting a new example (repetition is good for learning)Our example now targets the Oracle Public CloudHere are the shapes:

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?

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:

ControllingResourceUtilizationandReduceConsumptionItdoesnotmatterhowmanyresourcesyouhave.Ifyoudon’tknowhowtousethem,theywillneverbeenough.

Government

ControllingtheresourcesOne key to a healthy database is maintaining a healthy CPU load.

Excessive CPU load can destabilize the server and expose operating system bugs.

Sizingfor300or600?ReductionofCPUutilizationanalyzingAWRreports

HowaboutaHealthCheck?

Optimizingtheresourcesutilization

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

InstanceAllocation

FailoverScenarios

AEGSizingProcess

Summary• StandardizeASHdata• Max,Min,Mean,andMedian• Percentiles• Decisions… Decisions… Decisions…• Headroom• SPEC• Controllingtheresources• Reducingtheconsumption• Professionalservices

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

https://jorgebarbablog.wordpress.com

barba sizing for the cloud · • what is percentile and how it is used for sizing. • how...

Documents

in barba al filosofo!

kit-barba-ciega...title: kit-barba-ciega.pdf author: ana...

5.barba v. liceo de cagayan

pedro martin barba and (---) (---)

barba azul nature reserve november 2015...

david manzano barba alberto quinto martínez

caa303 - sizing for sap s/4hana and sap s/4hana cloud

densify cloud optimisation & automation: right-sizing the...

notas de eugenio barba

barba-jacob para hechizados

sizing cloud markets in asia pacific - forrester - feb 2012

barba mater

sizing up the aws storage options -...

barba. ``three faces. . .´´

kit-barba-ciega · title: kit-barba-ciega.pdf author: ana...

barba azul nature reserve

barba azul nature reserve november 2016 update...

barba armchair - amazon s3...barba armchair named after the...

gustavo barba, deputy president copac

guerrade la barba