1 / 41

I/O Analysis with SAR

I/O Analysis with SAR. David Kurtz Go-Faster Consultancy Ltd. david.kurtz@go-faster.co.uk www.go-faster.co.uk. Who am I?. DBA Independent Consultant Performance Tuning Oracle/PeopleSoft. sar - system activity reporter. measures system resource utilisation CPU Memory I/O activity

adonia
Download Presentation

I/O Analysis with SAR

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. Who am I? • DBA • Independent Consultant • Performance Tuning • Oracle/PeopleSoft 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 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 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 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 Go-Faster Consultancy Ltd.

  7. sadc • system activity data collector • measures CPU, Memory and Disk utilisation • all measures written to file 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 statisticsDATE=`date +%Y%m%d%H%M%S`#15 minute duration, 1 second intervalnohup /usr/lbin/sa/sadc 1 900 /oracle/PROD/ARCH/slsop/${DATE}.log & 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 Go-Faster Consultancy Ltd.

  10. sar • system activity report • can read system directly • or, can read sadc output file • produces formatted reports 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 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 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 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 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 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 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) ) 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; / 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; / 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 Go-Faster Consultancy Ltd.

  21. ODBC data source Go-Faster Consultancy Ltd.

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

  23. Define MS query Go-Faster Consultancy Ltd.

  24. Graph in Excel 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 ... Go-Faster Consultancy Ltd.

  26. 30 second rolling average 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 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 Go-Faster Consultancy Ltd.

  29. EMC drive map 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’ ; Go-Faster Consultancy Ltd.

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

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

  33. Redo disks 80-100% utilised 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 Go-Faster Consultancy Ltd.

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

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

  37. 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. Conclusion Go-Faster Consultancy Ltd.

  38. 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 Conclusion 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 Go-Faster Consultancy Ltd.

  40. Any Questions? Go-Faster Consultancy Ltd.

  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