topic Re: Poor SQL Read Performance On EMA12000. in HPE EVA Storage

Poor SQL Read Performance On EMA12000.

James Bennett_5 — Thu, 16 Jun 2005 23:53:54 GMT

Hello!

Are SAN seems to be suffering from very poor read performance in SQL, causing many latching issues with queries (PAGELATCHEIO_SH). The write numbers from the sqlstress utility looks good, but the read performance is a fraction of even our local SCSI drives.

After beating my head off the wall for a few days, does anyone have any tweaks for the HSG80 W/ SQL on a server 2000 platform?

Re: Poor SQL Read Performance On EMA12000.

James Bennett_5 — Fri, 17 Jun 2005 08:24:48 GMT

Oops...

More Info:

ACS 8.5F

3 X 4314R W/ 14 18.2 U3 10,000 RPM Drives
U3 Single Port Interfaces In 4314R(s)

Re: Poor SQL Read Performance On EMA12000.

Mark Poeschl_2 — Fri, 17 Jun 2005 12:14:09 GMT

What sorts of containers are you presenting to your server? RAID5, RAID0+1? How many spindles in each? That's going to have a huge impact on performance. Have you run VTDPY to see if you can see where the bottleneck is from the controllers' point of view?

Your firmware is WAY out of date - you're three major releases back. Whether that's affecting performance or not I couldn't say, but from a maintainability point of view I'd highly recommend an upgrade.

Re: Poor SQL Read Performance On EMA12000.

James Bennett_5 — Wed, 29 Jun 2005 22:54:03 GMT

Sorry for the delay coming back to this... Work has been killer.

Few specs:

HSG_TOP>show units
LUN Uses Used by
------------------------------------------------------------------------------

D1 STRIPE1
D2 STRIPE2
HSG_TOP>

HSG_TOP>show d1
LUN Uses Used by
------------------------------------------------------------------------------

D1 STRIPE1
LUN ID: 6000-1FE1-000B-C000-0009-0420-5803-01D6
NOIDENTIFIER
Switches:
RUN NOWRITE_PROTECT READ_CACHE
READAHEAD_CACHE WRITEBACK_CACHE
MAXIMUM_CACHED_TRANSFER_SIZE = 32
Access:
SQL_SAN
State:
ONLINE to this controller
Not reserved
Size: 355563890 blocks
Geometry (C/H/S): ( 69993 / 20 / 254 )
HSG_TOP>

HSG_TOP>show d2
LUN Uses Used by
------------------------------------------------------------------------------

D2 STRIPE2
LUN ID: 6000-1FE1-000B-C000-0009-0420-5803-01F5
NOIDENTIFIER
Switches:
RUN NOWRITE_PROTECT READ_CACHE
READAHEAD_CACHE WRITEBACK_CACHE
MAXIMUM_CACHED_TRANSFER_SIZE = 32
Access:
SQL_SAN
State:
ONLINE to this controller
Not reserved
Size: 355563890 blocks
Geometry (C/H/S): ( 69993 / 20 / 254 )

HSG_TOP>show stripe1
Name Storageset Uses Used by
------------------------------------------------------------------------------

STRIPE1 stripeset M1 D1
M10
M2
M3
M4
M5
M6
M7
M8
M9
Switches:
CHUNKSIZE = 128 blocks
State:
NORMAL
M1 (member 0) is NORMAL
M2 (member 1) is NORMAL
M3 (member 2) is NORMAL
M4 (member 3) is NORMAL
M5 (member 4) is NORMAL
M6 (member 5) is NORMAL
M7 (member 6) is NORMAL
M8 (member 7) is NORMAL
M9 (member 8) is NORMAL
M10 (member 9) is NORMAL
Size: 355563890 blocks
HSG_TOP>

HSG_TOP>show stripe2
Name Storageset Uses Used by
------------------------------------------------------------------------------

STRIPE2 stripeset M11 D2
M12
M13
M14
M15
M16
M17
M18
M19
M20
Switches:
CHUNKSIZE = 128 blocks
State:
NORMAL
M11 (member 0) is NORMAL
M12 (member 1) is NORMAL
M13 (member 2) is NORMAL
M14 (member 3) is NORMAL
M15 (member 4) is NORMAL
M16 (member 5) is NORMAL
M17 (member 6) is NORMAL
M18 (member 7) is NORMAL
M19 (member 8) is NORMAL
M20 (member 9) is NORMAL
Size: 355563890 blocks
HSG_TOP>

HSG_TOP>show m1
Name Storageset Uses Used by
------------------------------------------------------------------------------

M1 mirrorset DISK10000 STRIPE1
DISK30000
Switches:
POLICY (for replacement) = BEST_PERFORMANCE
COPY (priority) = NORMAL
READ_SOURCE = LEAST_BUSY
MEMBERSHIP = 2, 2 members present
State:
NORMAL
DISK10000 (member 0) is NORMAL
DISK30000 (member 1) is NORMAL
Size: 35556389 blocks
HSG_TOP>

Re: Poor SQL Read Performance On EMA12000.

James Bennett_5 — Wed, 29 Jun 2005 22:55:45 GMT

I have an identical unit W/ 8.7F-1 and the behaviour is the same.

Let me post what SQLIO is telling me....

Re: Poor SQL Read Performance On EMA12000.

James Bennett_5 — Wed, 29 Jun 2005 22:59:05 GMT

The following SQLIO is from our G: on this unit (Non-SAN) SCSI directly in our DL-580. 72GB 10,000 RPM U320. Database will run off this drive with no performance issues (latching problems) in SQL.

sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file G:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file G:\testfile.dat needs to be: 1073741824 bytes
current file size: 0 bytes
need to expand by: 1073741824 bytes
expanding G:\testfile.dat ... done.
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 1277.19
MBs/sec: 9.97
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 12
Max_Latency(ms): 114
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 4 6 8 9 9 9 6 5 4 4 4 3 3 2 2 2 2 2 2 1 1 1 12
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file G:\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 638.69
MBs/sec: 39.91
latency metrics:
Min_Latency(ms): 3
Avg_Latency(ms): 24
Max_Latency(ms): 235
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 1 3 3 4 5 4 4 4 4 4 4 3 3 3 3 2 3 3 2 2 35
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file G:\testfile.dat
using 128KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 328.55
MBs/sec: 41.06
latency metrics:
Min_Latency(ms): 5
Avg_Latency(ms): 48
Max_Latency(ms): 445
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 1 1 1 2 2 2 3 3 3 2 2 3 2 2 2 2 65
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file G:\testfile.dat
using 256KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 183.95
MBs/sec: 45.98
latency metrics:
Min_Latency(ms): 10
Avg_Latency(ms): 86
Max_Latency(ms): 588
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 1 1 1 1 1 1 1 89
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file G:\testfile.dat
using 8KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 2739.10
MBs/sec: 21.39
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 5
Max_Latency(ms): 26
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 5 10 13 11 13 25 12 3 1 1 1 2 2 1 0 0 0 0 0 0 0 0 0 0
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file G:\testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 809.39
MBs/sec: 50.58
latency metrics:
Min_Latency(ms): 3
Avg_Latency(ms): 19
Max_Latency(ms): 194
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 1 1 2 4 5 6 5 6 5 5 4 4 4 4 3 3 3 3 4 28
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file G:\testfile.dat
using 128KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 406.04
MBs/sec: 50.75
latency metrics:
Min_Latency(ms): 6
Avg_Latency(ms): 38
Max_Latency(ms): 558
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 1 1 1 2 1 1 2 1 2 2 2 2 2 2 2 75
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file G:\testfile.dat
using 256KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 206.59
MBs/sec: 51.64
latency metrics:
Min_Latency(ms): 11
Avg_Latency(ms): 76
Max_Latency(ms): 354
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 0 1 94
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file G:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 1347.99
MBs/sec: 10.53
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 11
Max_Latency(ms): 138
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 1 5 6 8 8 9 8 5 5 5 4 4 4 3 2 2 2 2 1 1 1 1 1 10
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file G:\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 748.28
MBs/sec: 46.76
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 20
Max_Latency(ms): 239
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 1 3 4 4 5 5 5 4 4 4 4 4 4 3 3 3 3 2 2 2 2 29
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file G:\testfile.dat
using 128KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 406.81
MBs/sec: 50.85
latency metrics:
Min_Latency(ms): 4
Avg_Latency(ms): 38
Max_Latency(ms): 373
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 1 1 1 2 2 2 3 2 3 3 3 3 3 2 2 3 2 2 60
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file G:\testfile.dat
using 256KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 253.12
MBs/sec: 63.28
latency metrics:
Min_Latency(ms): 8
Avg_Latency(ms): 62
Max_Latency(ms): 518
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 2 2 2 1 2 2 2 82
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file G:\testfile.dat
using 8KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 6385.12
MBs/sec: 49.88
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 2
Max_Latency(ms): 29
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 1 2 96 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file G:\testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 2112.19
MBs/sec: 132.01
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 6
Max_Latency(ms): 1946
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 49 6 1 1 1 1 0 1 1 2 3 2 3 1 3 4 1 1 1 2 5 0 1 4 5
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file G:\testfile.dat
using 128KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 1070.39
MBs/sec: 133.79
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 14
Max_Latency(ms): 71
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 34 3 0 0 0 0 0 3 1 1 2 1 1 0 1 0 8 8 1 2 2 1 2 28
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file G:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file G:\testfile.dat
using 256KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: G:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 541.32
MBs/sec: 135.33
latency metrics:
Min_Latency(ms): 2
Avg_Latency(ms): 28
Max_Latency(ms): 496
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 14 0 6 0 0 0 0 2 0 0 2 1 0 0 0 1 4 1 0 1 2 0 63

Re: Poor SQL Read Performance On EMA12000.

James Bennett_5 — Wed, 29 Jun 2005 23:03:00 GMT

This SQLIO is from D2 in our SAN (i:/).

sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file I:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 2761.50
MBs/sec: 21.57
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 5
Max_Latency(ms): 42
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 30 11 0 3 13 27 13 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file I:\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 419.22
MBs/sec: 26.20
latency metrics:
Min_Latency(ms): 4
Avg_Latency(ms): 37
Max_Latency(ms): 119
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file I:\testfile.dat
using 128KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 224.73
MBs/sec: 28.09
latency metrics:
Min_Latency(ms): 2
Avg_Latency(ms): 69
Max_Latency(ms): 559
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 4 3 2 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 88
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file I:\testfile.dat
using 256KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 109.56
MBs/sec: 27.39
latency metrics:
Min_Latency(ms): 4
Avg_Latency(ms): 144
Max_Latency(ms): 536
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 23 7 3 9 5 2 2 1 1 0 1 1 0 0 0 0 0 0 0 0 43
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file I:\testfile.dat
using 8KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 3368.40
MBs/sec: 26.31
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 4
Max_Latency(ms): 53
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 2 7 15 22 17 16 10 4 3 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 1
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file I:\testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 500.09
MBs/sec: 31.25
latency metrics:
Min_Latency(ms): 3
Avg_Latency(ms): 31
Max_Latency(ms): 473
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 1 1 1 2 2 3 2 1 1 2 3 1 0 0 0 0 80
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file I:\testfile.dat
using 128KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 210.44
MBs/sec: 26.30
latency metrics:
Min_Latency(ms): 2
Avg_Latency(ms): 74
Max_Latency(ms): 440
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 16 7 8 2 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 63
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 10 secs to file I:\testfile.dat
using 256KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 106.74
MBs/sec: 26.68
latency metrics:
Min_Latency(ms): 4
Avg_Latency(ms): 148
Max_Latency(ms): 452
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 35 9 2 6 4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 42
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file I:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 2367.69
MBs/sec: 18.49
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 6
Max_Latency(ms): 85
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 14 2 2 7 10 11 12 12 12 6 4 3 2 1 1 1 0 0 0 0 0 0 0 0 1
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file I:\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 788.40
MBs/sec: 49.27
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 19
Max_Latency(ms): 72
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 1 1 2 3 5 5 6 6 7 6 7 6 6 5 5 4 25
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file I:\testfile.dat
using 128KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 396.16
MBs/sec: 49.52
latency metrics:
Min_Latency(ms): 2
Avg_Latency(ms): 39
Max_Latency(ms): 262
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 2 2 2 2 3 83
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file I:\testfile.dat
using 256KB random IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 178.17
MBs/sec: 44.54
latency metrics:
Min_Latency(ms): 9
Avg_Latency(ms): 88
Max_Latency(ms): 399
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 99
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file I:\testfile.dat
using 8KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 3301.20
MBs/sec: 25.79
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 4
Max_Latency(ms): 30
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 4 11 20 14 14 10 7 5 4 3 2 2 1 1 0 1 0 0 0 0 0 0 0 0 0
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file I:\testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 808.50
MBs/sec: 50.53
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 19
Max_Latency(ms): 62
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 1 1 1 1 1 1 1 1 2 2 2 3 4 5 5 6 6 6 6 6 5 5 4 27
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file I:\testfile.dat
using 128KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 385.00
MBs/sec: 48.12
latency metrics:
Min_Latency(ms): 2
Avg_Latency(ms): 40
Max_Latency(ms): 138
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 96
sqlio v1.5.SG
using system counter for latency timings, -1908617296 counts per second
parameter file used: param.txt
file I:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 10 secs from file I:\testfile.dat
using 256KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
using specified size: 1024 MB for file: I:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 192.91
MBs/sec: 48.22
latency metrics:
Min_Latency(ms): 16
Avg_Latency(ms): 82
Max_Latency(ms): 190
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100

Re: Poor SQL Read Performance On EMA12000.

James Bennett_5 — Wed, 29 Jun 2005 23:04:20 GMT

As you can see the IO/s and MB/s start off great, but go down the drain when the size of the IO/s increase.

The SCSI drive directly attached does not suffer from this same drop off.

Am I seeing things that aren't there?

If not does anybody know why my little local drives seem to work faster?

Re: Poor SQL Read Performance On EMA12000.

comarow — Sat, 02 Jul 2005 04:27:22 GMT

Increasing pixscan helps improve latch improvements. Increase it over 500.

On you san, make sure your diolm is at the most 100. It would be better even lower, but DECWINDOWs won't start below 100.

Bpb

Re: Poor SQL Read Performance On EMA12000.

Uwe Zessin — Sat, 02 Jul 2005 05:46:03 GMT

Sigh, Bob, you need to turn off you VMS-only glasses!

This is an SQL server database on a ProLiant DL580 runnig Windows 2000.

Re: Poor SQL Read Performance On EMA12000.

James Bennett_5 — Thu, 07 Jul 2005 11:24:24 GMT

Bingo!

After further testing, looks like it was the 8.5 ACS version. Was able to clean up all the issues with 8.7.

Thanks!

Re: Poor SQL Read Performance On EMA12000.

Uwe Zessin — Thu, 07 Jul 2005 13:25:02 GMT

Strange, may I point out that on "Jun 30, 2005 03:55:45 GMT" you wrote:

> I have an identical unit W/ 8.7F-1 and the behaviour is the same.

Re: Poor SQL Read Performance On EMA12000.

James Bennett_5 — Thu, 07 Jul 2005 15:55:18 GMT

I'm a liar...

After further testing the 8.7F-1 install performed to a much better spec (latching performance) than the 8.5F based HSG80.

One of the technical support people from HP's Eterprise Group said that this was due to 8.7 handling the caching operations better than 8.5.

Dunno about that, but it certainly works well now.

Re: Poor SQL Read Performance On EMA12000.

generic_1 — Thu, 07 Jul 2005 23:42:45 GMT

Heres some food for thought.
Its easy for the OS to dump writes off into the sans cache and seem fast as long as its not so far behind its overflowing. These are so old even, if they have prefetching I cant see it competing with the new san stuff for reads. Local disks are usually faster.
Friends dont let friends partion HSG80s LOL
Spread your disks out accross the controllers IE 10000 20100 30200 40300.
The more disks the better.
Keep your firmware up to date on these or they will do evil unto you.
Ive seen raid groups teleport to blackholes never to be seen again, but reapear after initailizing the "Unused Disks" for no apparent reason other than old firmware.
Its going to be slower than newer stuff because you have active passive controllers, only one active at a time basically.
Make sure you dont have a memory leak on your Server.
Check your Switch and server logs for IO errors.
If you can do Logical volume striping with a product like veritas that helps performance.
If by chance you get to upgrade, lots of small luns, spread evenly accross all of the controllers and disks, on the outside of the platters, is fast with a stripe size of 1024.