SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Are these SQLIO results ok? Need to ensure that new SAN IO latency is topnotch


Are these SQLIO results ok? Need to ensure that new SAN IO latency is topnotch

Author
Message
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6046 Visits: 11771
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.
eforta.it
eforta.it
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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 Smile

BTW, check this for a quick hit too:

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

Cheers

Michael
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6046 Visits: 11771
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.
Leo.Miller
Leo.Miller
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1084 Visits: 1525
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
sql-lover
sql-lover
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1683 Visits: 1930
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? ...
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20953 Visits: 17246
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" ;-)
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20953 Visits: 17246
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" ;-)
sql-lover
sql-lover
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1683 Visits: 1930
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.
jasonmorris
jasonmorris
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 Visits: 1529
If it's not windows 2008 you will need to set the offset also for the drive.
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20953 Visits: 17246
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search