1 / 40

David Kurtz Go-Faster Consultancy Ltd. david.kurtzgo-faster go-faster

UKOUG 2001. Go-Faster Consultancy Ltd.. 2. Who am I?. DBAIndependent ConsultantPerformance TuningOracle/PeopleSoft. UKOUG 2001. Go-Faster Consultancy Ltd.. 3. sar - system activity reporter. measures system resource utilisationCPUMemoryI/O activitythis presentation is about using sar as an a

giovanni
Download Presentation

David Kurtz Go-Faster Consultancy Ltd. david.kurtzgo-faster go-faster

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. I/O Analysis with SAR David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk

    2. UKOUG 2001 Go-Faster Consultancy Ltd. 2 Who am I? DBA Independent Consultant Performance Tuning Oracle/PeopleSoft

    3. UKOUG 2001 Go-Faster Consultancy Ltd. 3 sar - system activity reporter measures system resource utilisation CPU Memory I/O activity this presentation is about using sar as an analysis/reporting tool

    4. UKOUG 2001 Go-Faster Consultancy Ltd. 4 Why did I become interested in sar? I/O bottleneck in Payroll System 500Mb redo log files 40 switches / hour during 40 hour batch sar reported an I/O wait problem but didn’t know exactly where used other sar reports to analyse disks

    5. UKOUG 2001 Go-Faster Consultancy Ltd. 5 Environment HP-UX 11.00 sar differs between Unix flavours Oracle 8.0.5 / 8.1.6 2 EMC disk arrays raid 1+0 2 primary, 3 secondary mirrors arrays linked by SRDF synchronisation link

    6. UKOUG 2001 Go-Faster Consultancy Ltd. 6 Free Graphical Analysis Tool sadc - collect data sar - basic data report SQL*loader - load reports into tables direct in 9i? MS Excel - graphic front end

    7. UKOUG 2001 Go-Faster Consultancy Ltd. 7 sadc system activity data collector measures CPU, Memory and Disk utilisation all measures written to file

    8. UKOUG 2001 Go-Faster Consultancy Ltd. 8 sadc <t> <n> <file> t = time interval between measurements n = number of measurements file = output file #!/bin/ksh #sarit.sh #collect sar statistics DATE=`date +%Y%m%d%H%M%S` #15 minute duration, 1 second interval nohup /usr/lbin/sa/sadc 1 900 /oracle/PROD/ARCH/slsop/${DATE}.log &

    9. UKOUG 2001 Go-Faster Consultancy Ltd. 9 sadc Low overhead 1 second interval Large volume of output 66Kb / measurement dependant on number of disk devices binary file

    10. UKOUG 2001 Go-Faster Consultancy Ltd. 10 sar system activity report can read system directly or, can read sadc output file produces formatted reports

    11. UKOUG 2001 Go-Faster Consultancy Ltd. 11 sar [-f file] [-option] [<t> <n>] can read system activity directly out can read file with -f option different reports from same set of data

    12. UKOUG 2001 Go-Faster Consultancy Ltd. 12 sar options -f read file written by sadc -u CPU utilisation report, average over all CPUs -uM CPU utilisation, reporting per CPU -d Disk activity report

    13. UKOUG 2001 Go-Faster Consultancy Ltd. 13 more sar reporting options -b buffer activity -c system calls -w system switching (swap) -a access -q run queue statitics -v file usage -m shared memory

    14. UKOUG 2001 Go-Faster Consultancy Ltd. 14 For more background on sar see presentation from Unix Sig Meeting 26.9.2000 Performance Management of Oracle on Unix Gary Mulqueen

    15. UKOUG 2001 Go-Faster Consultancy Ltd. 15 load sar reports into database with SQL*Loader Oracle8i Utilities Manual Ch 4. SQL*Loader Case Studies Case 7: Extracting Data from Formatted Report

    16. UKOUG 2001 Go-Faster Consultancy Ltd. 16 cpus report HP-UX svhrms05 B.11.00 A 9000/800 12/21/00 15:45:00 cpu %usr %sys %wio %idle 15:45:01 0 9 8 43 41 1 22 7 14 58 2 81 0 0 20 3 76 4 10 11 4 56 7 8 30 ... system 39 8 15 38 15:45:02 0 0 0 2 99 1 12 16 15 57 2 38 0 0 62 3 80 1 9 10 4 8 10 2 81 ... system 37 8 11 43

    17. UKOUG 2001 Go-Faster Consultancy Ltd. 17 SQL*Loader control file LOAD DATA INFILE '..\data\cpus.sar' DISCARDMAX 999999 REPLACE INTO TABLE dmk_sar_cpus WHEN (16) != 'u' and (16) != 'm' and (16) != ' ' and (16) != 'B' and (1) != 'A' (timestamp position(1:8) DATE(8) "HH24:MI:SS" ,cpu position(9:16) ,usr position(17:24) ,sys position(25:32) ,wio position(33:40) )

    18. UKOUG 2001 Go-Faster Consultancy Ltd. 18 package for variable -- Global variable package CREATE OR REPLACE PACKAGE sar AS last_timestamp DATE := TRUNC(SYSDATE); l_offset := 0; END SAR; /

    19. UKOUG 2001 Go-Faster Consultancy Ltd. 19 trigger to handle blank dates CREATE OR REPLACE TRIGGER dmk_sar_cpus BEFORE INSERT ON dmk_sar_cpus FOR EACH ROW BEGIN IF :new.timestamp IS NULL THEN :new.timestamp := sar.last_timestamp; -- use last valid value ELSE sar.l_offset := CEIL(sar.last_timestamp - :new.timestamp); :new.timestamp := :new.timestamp + sar.l_offset; END IF; sar.last_timestamp := :new.timestamp; -- save value for later END; /

    20. UKOUG 2001 Go-Faster Consultancy Ltd. 20 query database from MS Excel define an ODBC source use MS Query to extract data from database to spreadsheet use excel to present data graphically or, use MS access to extract and graph

    21. UKOUG 2001 Go-Faster Consultancy Ltd. 21 ODBC data source

    22. UKOUG 2001 Go-Faster Consultancy Ltd. 22 Data -> Get External Data -> Create New Query Define Query in Excel

    23. UKOUG 2001 Go-Faster Consultancy Ltd. 23 Define MS query

    24. UKOUG 2001 Go-Faster Consultancy Ltd. 24 Graph in Excel

    25. UKOUG 2001 Go-Faster Consultancy Ltd. 25 Sometimes better to smooth data Use Analytic functions to generate rolling averages Available Oracle 8.1.6 MS Query rejected analytic functions! create or replace view dmk_sar_cpu_smooth as select timestamp , avg(sys) over( order by timestamp range between 29/86400 preceding and current row) sys ...

    26. UKOUG 2001 Go-Faster Consultancy Ltd. 26 30 second rolling average

    27. UKOUG 2001 Go-Faster Consultancy Ltd. 27 Disk report HP-UX svhrms05 B.11.00 A 9000/800 12/21/00 15:45:00 device %busy avque r+w/s blks/s avwait avserv 15:45:01 c0t6d0 72.00 0.50 64 692 4.83 15.37 c3t6d0 45.00 0.50 42 520 4.72 14.39 c8t6d0 3.00 0.50 3 48 7.08 7.20 c4t4d0 1.00 0.50 2 32 6.19 1.24 c11t3d2 6.00 0.50 61 2432 4.73 1.97 c14t0d3 6.00 0.50 63 2624 4.29 1.84 c11t2d3 9.00 0.50 61 2192 5.21 1.82 c14t1d3 6.00 0.50 50 2240 4.87 1.61 c11t3d3 14.00 0.50 66 2672 4.85 2.92 c14t0d4 18.00 0.50 68 2608 5.17 3.41 c11t2d4 15.00 0.50 59 2768 4.69 2.94 c14t1d4 11.00 0.50 58 2688 4.51 2.37 c11t3d4 11.00 0.50 54 2496 5.34 2.31

    28. UKOUG 2001 Go-Faster Consultancy Ltd. 28 What is each device used for? Logical Device disk device logical volume mounted at a particular point contains a particular part of the database lvdisplay - display logical volumes

    29. UKOUG 2001 Go-Faster Consultancy Ltd. 29 EMC drive map

    30. UKOUG 2001 Go-Faster Consultancy Ltd. 30 Device -> Database Extra column on table update dmk_sar_disk set lvol_desc = ’DATA' where device_name LIKE ‘c%t9d4’ ; update dmk_sar_disk set lvol_desc = ’GPIDX' where device_name LIKE ‘c%t9d6’ ;

    31. UKOUG 2001 Go-Faster Consultancy Ltd. 31 I/O metrics by database component

    32. UKOUG 2001 Go-Faster Consultancy Ltd. 32 This is what we observed: CPU reported 20-40% I/O wait

    33. UKOUG 2001 Go-Faster Consultancy Ltd. 33 Redo disks 80-100% utilised

    34. UKOUG 2001 Go-Faster Consultancy Ltd. 34 What was going on? Had already striped redo file system across 8 disks Traced problem to SRDF link upgraded from 2 to 4 links

    35. UKOUG 2001 Go-Faster Consultancy Ltd. 35 Redo After upgrading SRDF link

    36. UKOUG 2001 Go-Faster Consultancy Ltd. 36 CPU after upgrading SRDF link

    37. UKOUG 2001 Go-Faster Consultancy Ltd. 37 Conclusion OS is the foundation upon which you build your database. SAR will tell you what your CPU and your physical disks are doing. Beware: Disk arrays only tell the OS a limited version of the truth.

    38. UKOUG 2001 Go-Faster Consultancy Ltd. 38 Conclusion Don’t drown in numbers, graph it Collect data, report to flat file Load flat file into database Graph in excel Not an industrial strength solution Useful for short term use/occasional Otherwise, look at a third party software

    39. UKOUG 2001 Go-Faster Consultancy Ltd. 39 References Oracle 7 EMC Symmetrix SRDF Metalink - CR #219095 Demo Files bundled with presentation www.go-faster.co.uk -> presentations Analytic Functions Jonathan Lewis - UKOUG2000 presentation

    40. UKOUG 2001 Go-Faster Consultancy Ltd. 40 Any Questions?

    41. I/O Analysis with SAR David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk

More Related