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 Wednesday, December 19, 2012 2:07 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: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
sql-lover (12/18/2012)
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.


I believe he meant the cluster size that the disk is formatted at, not the size of the IO for your test.

The usual recommendation is that disks that hold SQL Server data files be formatted at 64K.





Post #1398662
Posted Wednesday, December 19, 2012 2:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 07, 2013 4:23 PM
Points: 40, Visits: 48
On our IBM SAN (V7000) we have three physical enclosures which have two RAID 6 sets in them..... so you *should* be able to get split out....

Hope you get the traction you need :)

BTW, check this for a quick hit too:

http://i.brentozar.com/presentations/Ozar_SQLConnections_SQL315_BuildingFasterSQLServers.pdf

Cheers

Michael

Post #1398665
Posted Wednesday, December 19, 2012 2:15 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: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
sql-lover (12/19/2012)
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?


I would call the random IO results borderline, depending on the application.

The sequential write IO is far below what I would expect on a new system. You may be bottlenecked by the speed of the underlying disks. I would be very uncomfortable releasing that to production.

Post #1398669
Posted Wednesday, December 19, 2012 8:23 PM


SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 7:44 PM
Points: 446, Visits: 1,314
sql-lover (12/18/2012)
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.

I'm talking about the actuall formatting of the drives, By default most sysadmins who build serves use the default drive format allocation unit which is 4Kb. If you run >fsutil fsinfo ntfsinfo X: from a command line (whereX is a drive letter) you will get the drive information. You are interested in the line: Bytes Per Cluster : If it says 4096 it is formated with 4Kb allocation units. You want this to say 65536 i.e. 64Kb. This can make a noticable change in disk performance.

You may need to reformat your data dives, in which case you may need to backup your data. If you've been carefull and kept the system DBs apart from the user DBs you can backup and then restore the databases after reformatting.

Cheers

Leo
Post #1398747
Posted Wednesday, January 02, 2013 6:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:24 PM
Points: 302, Visits: 1,067
Leo.Miller (12/19/2012)If it says 4096 it is formatted with 4Kb allocation units. You want this to say 65536 i.e. 64Kb. This can make a noticeable change in disk performance.
Leo


Can you expand a bit an explain why? ...
Post #1401820
Posted Wednesday, January 02, 2013 11:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 5,958, Visits: 12,839
As already mentioned increase the number of threads for your tests, also increase the queue depth (outstanding IOs) to around 32 or 64 and run for around 5 mins at a time.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1401984
Posted Wednesday, January 02, 2013 11:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 5,958, Visits: 12,839
sql-lover (12/18/2012)

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.

Depending on the HBAs installed in the server they could have a theoretical throughput of 200MBs, you're not even getting near half of that.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1401986
Posted Wednesday, January 02, 2013 11:50 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:24 PM
Points: 302, Visits: 1,067
Perry Whittle (1/2/2013)
As already mentioned increase the number of threads for your tests, also increase the queue depth (outstanding IOs) to around 32 or 64 and run for around 5 mins at a time.


Hi Perry,

What am I going to accomplish with that? Given the current file size and settings, results are pretty much accurate, in my opinion.

What I do not get, is why increasing the allocation unit will improve the overall latency or disk performance. I do not see why or at least, would like to see the technical explanation for that.
Post #1402014
Posted Thursday, January 03, 2013 3:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:15 AM
Points: 383, Visits: 1,464
If it's not windows 2008 you will need to set the offset also for the drive.
Post #1402273
Posted Friday, January 04, 2013 7:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 5,958, Visits: 12,839
sql-lover (1/2/2013)
Hi Perry,

What am I going to accomplish with that? Given the current file size and settings, results are pretty much accurate, in my opinion.

The HBA queue depth (or outstanding I\Os) dictates how many I\O requests may be active for that bus adapter.

Higher queue depth = increased throughput.

Don't raise it to high though or you'll just saturate the storage sub system and that's often worse than setting too low.



sql-lover (1/2/2013)
What I do not get, is why increasing the allocation unit will improve the overall latency or disk performance. I do not see why or at least, would like to see the technical explanation for that.

The file allocation unit size alone will not have much effect, it's closely related to the stripe size and the partition starting offset. See this link for more info.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1402889
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse