Transcript I/O Analysis with SAR David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk Who am I? • DBA – Independent Consultant – Performance Tuning • Oracle/PeopleSoft UKOUG 2001 Go-Faster Consultancy Ltd. sar - system.

I/O Analysis with SAR
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
Who am I?
• DBA
– Independent Consultant
– Performance Tuning
• Oracle/PeopleSoft
UKOUG 2001
Go-Faster Consultancy Ltd.
2
sar - system activity reporter
• measures system resource utilisation
– CPU
– Memory
– I/O activity
• this presentation is about using sar as an
analysis/reporting tool
UKOUG 2001
Go-Faster Consultancy Ltd.
3
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
UKOUG 2001
Go-Faster Consultancy Ltd.
4
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
UKOUG 2001
Go-Faster Consultancy Ltd.
5
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
UKOUG 2001
Go-Faster Consultancy Ltd.
6
sadc
• system activity data collector
– measures CPU, Memory and Disk utilisation
– all measures written to file
UKOUG 2001
Go-Faster Consultancy Ltd.
7
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 &
UKOUG 2001
Go-Faster Consultancy Ltd.
8
sadc
• Low overhead
– 1 second interval
• Large volume of output
– 66Kb / measurement
• dependant on number of disk devices
• binary file
UKOUG 2001
Go-Faster Consultancy Ltd.
9
sar
• system activity report
– can read system directly
– or, can read sadc output file
– produces formatted reports
UKOUG 2001
Go-Faster Consultancy Ltd.
10
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
UKOUG 2001
Go-Faster Consultancy Ltd.
11
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
UKOUG 2001
Go-Faster Consultancy Ltd.
12
more sar reporting options
•
•
•
•
•
•
•
-b
-c
-w
-a
-q
-v
-m
buffer activity
system calls
system switching (swap)
access
run queue statitics
file usage
shared memory
UKOUG 2001
Go-Faster Consultancy Ltd.
13
For more background on sar
– see presentation from Unix Sig Meeting
26.9.2000
– Performance Management of Oracle on Unix
• Gary Mulqueen
UKOUG 2001
Go-Faster Consultancy Ltd.
14
load sar reports into database
with SQL*Loader
• Oracle8i Utilities Manual
– Ch 4. SQL*Loader Case Studies
• Case 7: Extracting Data from Formatted Report
UKOUG 2001
Go-Faster Consultancy Ltd.
15
cpus report
HP-UX svhrms05 B.11.00 A 9000/800
15:45:00
15:45:01
12/21/00
cpu
0
1
2
3
4
%usr
9
22
81
76
56
%sys
8
7
0
4
7
%wio
43
14
0
10
8
%idle
41
58
20
11
30
system
0
1
2
3
4
39
0
12
38
80
8
8
0
16
0
1
10
15
2
15
0
9
2
38
99
57
62
10
81
system
37
8
11
43
...
15:45:02
...
UKOUG 2001
Go-Faster Consultancy Ltd.
16
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)
)
UKOUG 2001
Go-Faster Consultancy Ltd.
17
package for variable
-- Global variable package
CREATE OR REPLACE PACKAGE sar AS
last_timestamp DATE := TRUNC(SYSDATE);
l_offset := 0;
END SAR;
/
UKOUG 2001
Go-Faster Consultancy Ltd.
18
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;
/
UKOUG 2001
Go-Faster Consultancy Ltd.
19
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
UKOUG 2001
Go-Faster Consultancy Ltd.
20
ODBC data source
UKOUG 2001
Go-Faster Consultancy Ltd.
21
Define Query in Excel
• Data -> Get External Data -> Create New
Query
UKOUG 2001
Go-Faster Consultancy Ltd.
22
Define MS query
UKOUG 2001
Go-Faster Consultancy Ltd.
23
Graph in Excel
UKOUG 2001
Go-Faster Consultancy Ltd.
24
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
...
UKOUG 2001
Go-Faster Consultancy Ltd.
25
30 second rolling average
UKOUG 2001
Go-Faster Consultancy Ltd.
26
Disk report
HP-UX svhrms05 B.11.00 A 9000/800
15:45:00
15:45:01
device
c0t6d0
c3t6d0
c8t6d0
c4t4d0
c11t3d2
c14t0d3
c11t2d3
c14t1d3
c11t3d3
c14t0d4
c11t2d4
c14t1d4
c11t3d4
UKOUG 2001
%busy
72.00
45.00
3.00
1.00
6.00
6.00
9.00
6.00
14.00
18.00
15.00
11.00
11.00
avque
0.50
0.50
0.50
0.50
0.50
0.50
0.50
0.50
0.50
0.50
0.50
0.50
0.50
12/21/00
r+w/s
64
42
3
2
61
63
61
50
66
68
59
58
54
blks/s
692
520
48
32
2432
2624
2192
2240
2672
2608
2768
2688
2496
Go-Faster Consultancy Ltd.
avwait
4.83
4.72
7.08
6.19
4.73
4.29
5.21
4.87
4.85
5.17
4.69
4.51
5.34
avserv
15.37
14.39
7.20
1.24
1.97
1.84
1.82
1.61
2.92
3.41
2.94
2.37
2.31
27
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
UKOUG 2001
Go-Faster Consultancy Ltd.
28
EMC drive map
UKOUG 2001
Go-Faster Consultancy Ltd.
29
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’
;
UKOUG 2001
Go-Faster Consultancy Ltd.
30
I/O metrics by database
component
UKOUG 2001
Go-Faster Consultancy Ltd.
31
This is what we observed:
CPU reported 20-40% I/O wait
UKOUG 2001
Go-Faster Consultancy Ltd.
32
Redo disks 80-100% utilised
UKOUG 2001
Go-Faster Consultancy Ltd.
33
What was going on?
• Had already striped redo file system across
8 disks
• Traced problem to SRDF link
• upgraded from 2 to 4 links
UKOUG 2001
Go-Faster Consultancy Ltd.
34
Redo After upgrading SRDF link
UKOUG 2001
Go-Faster Consultancy Ltd.
35
CPU after upgrading SRDF link
UKOUG 2001
Go-Faster Consultancy Ltd.
36
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.
UKOUG 2001
Go-Faster Consultancy Ltd.
37
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
UKOUG 2001
Go-Faster Consultancy Ltd.
38
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
UKOUG 2001
Go-Faster Consultancy Ltd.
39
Any Questions?
UKOUG 2001
Go-Faster Consultancy Ltd.
40
I/O Analysis with SAR
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk