Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Are these SQLIO results ok? Need to ensure that new SAN IO latency is topnotch Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 12:36 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
Hi,

Just run SQLIO on my new Cluster and got very interesting results that I would like to share and/or get some feedback.

1st, I am not so sure about the SAN specific’s model and cache, but I’ve been told it’s a DELL Compellent SC8000.

I did two rounds of tests, each node, after failing over (Cluster is not in production yet). I tested the SQL mount point s(data and logs) using a 30GB file. On tempdb mount point I used a 5GB file due space restrictions.

I requested tempdb to be tier1; we use tempdb a lot, it’s a bottleneck on our old systems. It is my understanding that the tier1 put disks on a RAID10 layout. The databases are DW type, mostly reads, but nightly imports on few critical tables.

Having said that, these some of the results ....

Doing Writes:

sqlio v1.5.SG
using system counter for latency timings, 2474111 counts per second
parameter file used: data_param.txt
file E:\SQL_Data\MSSQL\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 120 secs to file E:\SQL_Data\MSSQL\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 30480 MB for file: E:\SQL_Data\MSSQL\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 824.99
MBs/sec: 51.56
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 18
Max_Latency(ms): 627
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 2 4 7 10 12 14 12 10 7 5 3 2 3 1 1 0 0 0 0 0 0 4

sqlio v1.5.SG
using system counter for latency timings, 2474111 counts per second
parameter file used: data_param.txt
file E:\SQL_Data\MSSQL\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 120 secs to file E:\SQL_Data\MSSQL\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 30480 MB for file: E:\SQL_Data\MSSQL\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 9406.30
MBs/sec: 73.48
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 1
Max_Latency(ms): 33
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 92 2 1 1 1 1 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0

Doing Reads:

sqlio v1.5.SG
using system counter for latency timings, 2474111 counts per second
parameter file used: data_param.txt
file E:\SQL_Data\MSSQL\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 120 secs from file E:\SQL_Data\MSSQL\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 30480 MB for file: E:\SQL_Data\MSSQL\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 1779.39
MBs/sec: 111.21
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 8
Max_Latency(ms): 39
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 1 1 1 91 1 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0

sqlio v1.5.SG
using system counter for latency timings, 2474111 counts per second
parameter file used: data_param.txt
file E:\SQL_Data\MSSQL\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 120 secs from file E:\SQL_Data\MSSQL\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 30480 MB for file: E:\SQL_Data\MSSQL\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 13686.86
MBs/sec: 106.92
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 32
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+
%: 16 83 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

Any feedback and opinion is highly appreciated ... my main interest is discovered any potential problem, if one, prior going live and before installing the SQL 2012 fail-over instance.
Post #1397988
Posted Tuesday, December 18, 2012 1:24 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 3,122, Visits: 11,405
The 64K random read and random write tests may be OK, but I would not call them spectacular, especially the 64K random write.

You should probably test again, adding more threads, until you reach an average Avg_Latency in the range of 25 to 50 ms, to find the maximum throughput it can sustain.

If possible, increase the size of the test file to make sure you are measuring the disk speed and not the SAN cache speed and size.





Post #1398006
Posted Tuesday, December 18, 2012 1:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
Michael Valentine Jones (12/18/2012)
The 64K random read and random write tests may be OK, but I would not call them spectacular, especially the 64K random write.

You should probably test again, adding more threads, until you reach an average Avg_Latency in the range of 25 to 50 ms, to find the maximum throughput it can sustain.

If possible, increase the size of the test file to make sure you are measuring the disk speed and not the SAN cache speed and size.



Fantastic post, thanks!

Yes, I was thinking on increasing the test file size to 50GB, maybe bigger. I do not know the SAN's cache specs yet. I need to ask our SAN admin.

Now, in case the numbers are slow (I'm not a SAN expert) what kind of tweaks or changes can be done at SAN level? MPIO is installed plus I patched Windows on both nodes.
Post #1398029
Posted Tuesday, December 18, 2012 8:09 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 16, 2014 8:08 PM
Points: 449, Visits: 1,333
Looks to me like the disks aren't formatted at 64Kb. I'd definitly check this as there are some gains to be made here.

Leo
Post #1398116
Posted Tuesday, December 18, 2012 8:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
Leo.Miller (12/18/2012)
Looks to me like the disks aren't formatted at 64Kb. I'd definitly check this as there are some gains to be made here.

Leo


I am not following you. The 64 KB was specified by me. The test uses 64 and 8KB page sizes.
Post #1398123
Posted Wednesday, December 19, 2012 9:25 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
Increased the test file size to 150GB and performed another round of tests ...

64KB random IOs

sqlio v1.5.SG
using system counter for latency timings, 2474091 counts per second
parameter file used: data_param.txt
file E:\SQL_Data\MSSQL\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 120 secs to file E:\SQL_Data\MSSQL\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 150480 MB for file: E:\SQL_Data\MSSQL\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 677.04
MBs/sec: 42.31
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 23
Max_Latency(ms): 660
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 2 4 8 9 9 11 12 11 8 7 4 3 1 1 1 1 0 0 0 0 0 0 7

64KB sequential IOs

sqlio v1.5.SG
using system counter for latency timings, 2474091 counts per second
parameter file used: data_param.txt
file E:\SQL_Data\MSSQL\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads writing for 120 secs to file E:\SQL_Data\MSSQL\testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 150480 MB for file: E:\SQL_Data\MSSQL\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 700.92
MBs/sec: 43.80
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 22
Max_Latency(ms): 665
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 2 4 7 8 10 11 12 11 9 7 4 2 1 1 1 1 0 0 0 0 0 0 6


64KB random IOs (reads)

sqlio v1.5.SG
using system counter for latency timings, 2474091 counts per second
parameter file used: data_param.txt
file E:\SQL_Data\MSSQL\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 120 secs from file E:\SQL_Data\MSSQL\testfile.dat
using 64KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using specified size: 150480 MB for file: E:\SQL_Data\MSSQL\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 1794.08
MBs/sec: 112.13
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 8
Max_Latency(ms): 80
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 7 1 1 1 86 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1

Wonder why I can't get anything lower or equal tha 20ms on random writes... or, what should I tweak to get better latency values.

I do like the read and random IO results though, 8ms average. Giving the case of the file's size, 150GB, I do not believe is being cached by the SAN disk controller ...

Any opinions?
Post #1398500
Posted Wednesday, December 19, 2012 12:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 7, 2013 4:23 PM
Points: 40, Visits: 48
Hi sql lover,

I'm doing a similar exercise with a clients SAN here in NZ.

Before I even did SQLIO, I actually spent a few 30 minute sessions with our SAN tech (and VMWare tech) going over both configurations to confirm what is setup and how. You need to have some basic understanding of SANs before doing this.

So go hit up BrentOzar.com and check out his amazing blog entries covering SAN performance:

http://www.brentozar.com/archive/2011/09/automated-tiered-storage-for-databases/
http://sqlserverpedia.com/blog/sql-server-performance-tuning/sqlio-tutorial/

The other thing to bear-in-mind is if your servers are virtualized as the multipathing configuration will affect performance (in my case VMWare multipathing was set up incorrectly for the SAN here.....):

http://www.brentozar.com/archive/2009/05/san-multipathing-part-2-what-multipathing-does/

Good luck!

Cheers

Michael
Post #1398622
Posted Wednesday, December 19, 2012 12:50 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
Hi Michael,

Thanks for reply.

While SAN and storage is not my main area of expertise, I do have a basic understanding of them and more important yet, how SQL interacts or should be configured on a SAN. My question about what to tweak is more towards the SAN, not MS-SQL.

The environment is a real Cluster, not VMware.

And those two are fantastic link / resources. I actually had the chance to look at them before.
Post #1398634
Posted Wednesday, December 19, 2012 1:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 7, 2013 4:23 PM
Points: 40, Visits: 48
Ah ok :) hehe suck eggs! Sorry!

Hmmm well I thought the performance you posted is good. I'm assuming the sets you've posted are the fastest results out of the cmd file Brent Ozar posted?

In terms of tweaking the SAN, you could look at disk sets in the RAID configurations? Maybe make it wider? One problem I have with mine is the old RAID 6 vendor answer to redundancy has been applied, thus making my writes absolutely awful...

What type of disk is in there? SAS 10k? SAS 15k?

Do you know what kind of throughput and response time you need?

The only other thing I can think of is ensuring your read heavy elements and your write heavy elements sit on different enclosures within the SAN? This'll prevent thrashing on one particular set of disks if the IOP traffic is switching all over the show (between random and sequential). I think Brent did a post on this somewhere......I'll try and dig it out.

The other question is what will sit with the Clusters allocated disk? What other services on the network will use it? Or is it all dedicated (I'm so jealous if you've managed to persuade the SAN tech to do that btw!!!).

Cheers

Michael

Post #1398641
Posted Wednesday, December 19, 2012 2:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
@Michael,

Yes, after last round of test and using 150GB file, I think that the 64KB test (writes and reads) looks good, specially the sequential.

Our systems are DW type of databases, so I am not too concern about the writes. But I was expecting 20ms or lower on the writing results. I think I will ask our SAN admin to create another LUN, so I can map it, assign to the cluster, and do another round of tests. I may ask for a different SAN configuration on that LAN and maybe do a customize Windows formatting too.

I think, not sure, our SAN does automatic tiered storage, but I can "force" or dictate which LUN goes on which tier. I'll validate that with our SAN expert.

Thanks again for reply!
Post #1398661
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse