Capacity Planning Techniques For Growing SAS Workloads

Capacity Planning Techniques forGrowing SAS WorkloadsFred Forst - SAS Institute, Inc Cary, NCMarch 16, 2012Session Number 10916

Capacity Planning Techniques forGrowing SAS Workloads SAS Business Intelligence Workload HP ALM Performance Center Load Generator Analysis of results Non-BI Workloads Know your workload Data Gathering – What does workload look like? SMF ARM SAS workload modeler macro. ‘What if’ scenarios.2

BI Workload SAS Web Report Studio (WRS) OLAP Cube dataCurrently 25 users randomly chose amongst 90 reportsEach report submits at least one query to the SAS OLAP ServerSimulate a growing user base, 25 – 400 usersWhat happens to report generation elapsed times? HP ALM Performance Center Simulates the human generating random WRS reports Produces summary & detail reports of WRS simulation3

BrowserSimulating a humanLogon WRSRepeat3 hoursGenerateReportThink1 minute1 secpause4Logoff WRSRepeat10 times

z/OSLogon WRSHP PerformanceCenterWindows serverPseudo C-code (script)GenerateReportLogoff WRS5do user 1 to max userslogon WRS urldo rpts 1 to num reportsselect random reportgenerate reportcalculate elapsed timethink think timeend;logoff WRS urlstop if max hourspause x secondsend;

Workflow Profile 90 SAS Web Report Studio Reports Four categories of reports: 6Contract status reports (67% of users)HR reports (18%)IT Network reports (10%)HR power user reports (5%)

Hardware and OS Profile IBM zEnterprise z196 model 710 5.2GHzCP Four dedicated CPs 48Gb Memory IBM System Storage DS8800 Subsystem Eight Ficon Express8 Channels z/OS 1.12 IBM WebSphere Application Server V7 JAVA V1.67

SAS Profile 8SAS V9.3Four SAS OLAP Servers load balancedSAS Metadata server (64 bit)SAS Web Report Studio

Architecture of SAS Intelligence PlatformData SourcesSAS ServersMiddle TierClientsSAS Add-In for Microsoft OfficeSAS Data SetsSAS OLAPCubesSAS MetadataServerSAS OLAP ServerSAS Data Integration StudioSAS Enterprise GuideWeb Application ServerSAS Enterprise MinerSAS Web ApplicationsSAS Forecast StudioWeb Report StudioSAS Information Map StudioInformation Delivery PortalSAS Management ConsoleBI PortletsSAS ScalablePerformance Data(SPD) EngineTablesSAS ScalablePerformance Data(SPD) ServerSAS FrameworkData ServerSAS StoredProcess ServerSAS PooledWorkspace ServerSAS WorkspaceServerThird-party DataStoresEnterpriseResource Planning(ERP)9 SystemsSAS Model ManagerBI DashboardSpawned SASprocesses fordistributed clientsJDBCSAS OLAP Cube StudioHelp Viewer for the WebOther SAS Webapplications and solutionsHTTP SAS Workflow StudioJMPOther SAS Webapplications andsolutionsOther SAS analytics and solutionsSAS Web ructureapplications& servicesJavaRMISAS RemoteServices JavaServerWeb BrowserHTTP

10

CPU usage 11WebSphere – 62% of total (most eligible fo z/AAP)OLAP srv – 18%Metadata srv –17%Other– 2%

Analysis Common Sense approach Elbow in elapsed time curve seems to be around 300 usersUsersElapsed Time (avg report time)25300 (12x)400 (16x)13.23sec53.88 (4x) A 12x increase in userssuffered only a 4xperformance degradation378(28x) A 16x increase in userssuffered a 28xperformance degradationConclusion: This hardware/softwarecombination scales very well up to 300 users12

Duncan Multiple Range TestGood ‘ol T-TestHypothesis testing:Avg Report time for 25 users same as for 50 usersH0: µ25 µ50H1: µ25 µ50Limitation: Tests the equality of only 2 groups13

Duncan Multiple Range TestH0: µ25 µ50 µ75 . µ400H1: µ25 µ50 µ75 . µ400Duncan Multiple Range test tests the hypothesis allgroups are equal.14

DuncanGroupingDuncan MultipleRange test givesstatistical validityto resultsEqual groupingscan 17.09810015.4137513.3195013.23025JJJJJ75 users and 200users membersof 2 groupings

Non-BI Workload Traditional SAS workloadDon’t have HP Performance centerWant to simulate growing # of users or growing dataStep #1 – know your users 16Batch or interactive?What SAS PROCs do they use and how often?Ad hoc usage or static jobs?What happens to SAS session elapsed times?What happens to SAS PROC elapsed times?

SAS log using option FULLSTIMERNOTE: DATA statement usedreal timeuser cpu timesystem cpu timeMemoryOS MemoryTimestamp(Total process time):2.97 seconds1.71 seconds0.68 seconds250k6072k1/24/2012 9:12:35 AMWant these historical metrics in a dataset171.Parse user SAS logs2.Use SMF option (z/OS only)3.Use ARM (any OS)

Data Gathering using SMF//STEPSAS EXEC SAS,// OPTIONS ‘SMFEXIT SMFEXIT SMF’//SYSIN DD *.your SAS code //MXGEXEC SAS9//SMFDD DISP OLD,DSN SMF data //SOURCLIB DD DISP SHR,DSN MXG.SOURCLIB//LIBRARY DD DISP SHR,DSN MXG.LIBRARY//SYSIN DD *%INC SOURCLIB(TYPESASU);PROC PRINT DATA WORK.TYPESASU;VAR SASEXCPS SASCORE SASPROC SASTCBTM;FORMAT SASTCBTM 8.4;18

Data Gathering using SMFOBSEXCPSUSEDBY PROC123447183411158719MEMORYUSEDBY PROC4766K3785K4601K13MPROCEDURENAME 300

Data Gathering using ARM%LET ARMEXEC 1;OPTIONS ARMLOC “/u/logs/user123.txt"options ARMSUBSYS (ARM PROC);%PERFINIT;%PERFSTRT(TXNNAME “PROC GLM TEST"); User code here %PERFSTOP;20;

Data Gathering using ARMWhat is wrong with this picture?Will users volunteer to code all the ARM stuff?%LET ARMEXEC 1;OPTIONSARMLOC “/u/logs/&sysuserid. &datetime.txt";ARMSUBSYS (ARM PROC);%PERFINIT;%PERFSTRT(TXNNAME “&get creative");Execute thisvia the initstmtoption User code here %PERFSTOP;21Execute thisvia the termstmtoption

G,1642709491.856716,1,1,PROCEDURE,PROC START/STOP,PROC NAME,ShortStr,PROC IO,Count64,PROC MEM,Count64,PROC 00,SAS,FREFORG,1642709491.905480,2,2,share,, IOCOUNT ,Count64, MEMCURR ,Gauge64, MEMHIGH ,Gauge64, THREADCURR ,Gauge32, THREADHIGH 42709493.594713,1,0.418874,0.000000Use the supplied ARMPROC macro to read raw 0:00:00.28100:00:00.72150:00:00.00340:00:00.6417

common SAS config filesasautos (‘ sasautos path ’ sasautos)INITSTMT “%arm start;run;"TERMSTMT "%arm stop;RUN;"orsasautos (‘ sasautos path ’ sasautos)smfsmfexit smfexit23

After gathering data 2425 Interactive SAS sessionsAverage session has 40 PROCs/DATAThink time between PROCs is 60 secondsNo pattern: Ad hoc workload

Distribution of 1.033337999.05FORMAT3210.9533700100.0025

Load generator SAS macro usingSAS/CONNECT%macro master(jobs 25,obs .25,spacing 5,think time 60,procs 40,wgt data .41,wgt sort .24,wgt univ .03,wgt means .03,wgt glm .02,wgt freq .03,wgt compare .02, wgt format .01);0Create 25 async SAS sessionsRead 25% of the dataPause 5 secs between sessionsPause 60 secs between SAS PROCSBuild job with 40 SAS PROCSRandomly chose a PROC in thisProportion. Must add to 1.0.41DATA step26.65.68PROC SORTPROC UNIVARIATE1.00

Master SAS jobsession&i%master(,,,);1. DATA step-think%do i 1 %to &jobs;2. PROC MEANS-think%sleep(5);3. PROC SORT-thinksignon session&i;4. PROC GLM-thinkrsubmit session&i;%build job;5. DATA step-think%do j 1 %to &procs; generate PROCs 40. PROC SORT%end;%update results;endrsubmit;signoff session&i;%end;27Each sessionAutomaticallyUses ARM

28

29

DATA step only – Duncan testNo degradation inDATA stepperformance onthe z196 up to125 concurrentSAS sessions30Duncan 5896211FFFFFFFFFFFjobs

31

32

Summary HP Performance Center is versatile in it’s ability tomodel BI web based apps DATA step elapsed times can be predicted closelywith polynomial regression (slide#29) No DATA step degradation on the z196 for up to 125concurrent SAS sessions (slides 29, 30) The z196 scales well (proportionally) across allvalues of concurrent SAS sessions when amount ofdata increases (slide#32)33

SAS is a registered trademark or trademark of SAS Institute Inc. in the USA andother countries. indicates USA registration. IBM is a registered trademark of International Business Machines Corporation(IBM) HP is a registered trademark of Hewlett Packard Corporation (HP) Other brand and product names are registered trademarks or trademarks oftheir respective companies.fred.forst@sas.com34

SAS OLAP Cubes SAS Add-In for Microsoft Office SAS Data Integration Studio SAS Enterprise Guide SAS Enterprise Miner SAS Forecast Studio SAS Information Map Studio SAS Management Console SAS Model Manager SAS OLAP Cube Studio SAS Workflow Studio JMP Other SAS analytics and solutions Third-party Data