September 30, 2010 at 11:49 pm
Hi,
We ran SQLIO on our f:\ drive of windows server to benchmark the f:\ drive. But we are unable to analyse the results.How to analyse? Are there any standards ? Please help me on this?
Here are the results :
threadsreadorwritedurationsecondssectorsizekbiopatterniosoutstandingfilesizembios_secmbs_seclatencyms_minlatencyms_avglatencyms_max
2W12064random100.020461456696593648
64W12064random128134771692.75105.7914047447722
64W12064sequential128134771728.91108.0515046434926
64R12064random128134771762.03110.126345604941
64R12064sequential128134771778.77111.1713745164744
64W12064sequential64134771685.76105.367924055385
32W12064random128134771697.21106.0713423885576
64R12064random64134771719.66107.476823435500
32W12064sequential128134771729.14108.0712623432501
64R12064sequential64134771733.31108.337323405370
64W12064random64134771740.65108.797623312526
32R12064random128134771760.43110.023223022622
32R12064sequential128134771777.81111.1112422802415
32W12064sequential64134771686.01105.377312074198
64W12064random32134771695.46105.963612014179
16W12064random128134771716.46107.276611864173
32R12064random64134771720.03107.56311844294
64W12064sequential32134771730.19108.133411771324
32R12064sequential64134771729.75108.12111774203
16W12064sequential128134771730.44108.155911761332
32W12064random64134771760.71110.046911561301
64R12064random32134771761.85110.113411561327
16R12064random128134771763.87110.2412111541340
64R12064sequential32134771777.48111.093111461277
16R12064sequential128134771781.96111.376311421223
16W12064sequential64134771687.57105.47346043607
64W12064sequential16134771687.44105.46166043605
8W12064random128134771708.69106.79135973623
16R12064random64134771716.52107.28255943740
64R12064random16134771717.11107.31135943723
32W12064random32134771719.2107.45355933674
8W12064sequential128134771728.8108.0515590742
32W12064sequential32134771728.25108.0132590727
16R12064sequential64134771729.88108.11295893642
64R12064sequential16134771731.83108.23135893652
64W12064random16134771735.59108.4717588736
16W12064random6413477176011031579720
32R12064random32134771762.17110.1331579754
8R12064random128134771764.88110.3123578748
8R12064sequential128134771780.87111.314573700
32R12064sequential32134771781.88111.3631572699
64W12064random8134771688.65105.5483023310
8W12064sequential64134771688.04105.533023307
32W12064sequential16134771686.86105.4223023313
4W12064random128134771712.18107.0192983305
16W12064random32134771714.84107.17142973294
32R12064random16134771715.78107.23142973433
8R12064random64134771723.96107.7462963420
4W12064sequential128134771727.33107.955295434
16W12064sequential32134771730.14108.1312295439
64W12064sequential8134771729.88108.1112295433
8R12064sequential64134771733.29108.3372943389
32R12064sequential16134771731.1108.19132943338
8W12064random64134771756.94109.87290440
32W12064random16134771757.24109.8217290431
4R12064random128134771758.05109.87125290464
16R12064random32134771763.89110.24123289469
64R12064random8134771764.39110.2711289461
4R12064sequential128134771775.79110.986287422
16R12064sequential32134771781.21111.3214286401
64R12064sequential8134771780.47111.2713286422
4W12064sequential64134771684.84105.331513163
16W12064sequential16134771687.94105.4961513159
64W12064sequential4134771687.52105.4741513169
2W12064random128134771696.54106.0311503148
8W12064random32134771718.43107.431483158
32W12064random8134771716.07107.2571483154
4R12064random64134771718.19107.3841483267
16R12064random16134771721.9107.6161483277
64R12064random4134771714.31107.1461483266
2W12064sequential128134771728.25108.013147287
8W12064sequential32134771728.85108.053147284
32W12064sequential8134771728.88108.056147291
4R12064sequential64134771732.56108.2841473205
16R12064sequential16134771731.55108.2271473284
64R12064sequential4134771730.24108.1461473223
64W12064random4134771739.72108.736146293
4W12064random64134771762.06110.123144288
16W12064random16134771758.61109.916144287
2R12064random128134771763.09110.1916144303
8R12064random32134771759.84109.994144308
32R12064random8134771765.73110.354144293
2R12064sequential128134771781.03111.318143272
8R12064sequential32134771777.38111.084143209
32R12064sequential8134771779.3111.28143270
64W12064random2134771693.34105.835753092
2W12064sequential64134771686.32105.391753082
8W12064sequential16134771684.8105.31753095
32W12064sequential4134771686.72105.423753085
4W12064random32134771715.4107.211743078
16W12064random8134771715.13107.193743093
2R12064random64134771711.73106.981743152
2R12064sequential64134771702.73106.424743143
2W12064random64134771739.92108.74173220
8R12064random16134771717.93107.372733163
32R12064random4134771717.81107.362733175
4W12064sequential32134771728.2108.01173212
16W12064sequential8134771728.34108.02273220
64W12064sequential2134771727.9107.99273219
8R12064sequential16134771720.11107.59733120
32R12064sequential4134771720.56107.535733128
8W12064random16134771754.31109.64172215
32W12064random4134771758.18109.88472218
4R12064random32134771760.05110372255
16R12064random8134771755.46109.71272183
16R12064sequential8134771758.28109.89572202
64R12064random2134771765.08110.31571188
4R12064sequential32134771770.2110.63471191
64R12064sequential2134771765.88110.36771143
2W12064random32134771692.61105.781373055
4W12064sequential16134771688.39105.521373050
16W12064sequential4134771684.42105.271373050
64W12064sequential1134771686.46105.41373055
8W12064random8134771714.75107.171363049
32W12064random2134771717.76107.362363051
64W12064random1134771737.39108.58136181
4R12064random16134771712.89107.054363078
16R12064random4134771718.53107.43363105
32R12064random2134771754.48109.65636138
64R12064random1134771719.04107.442363091
2W12064sequential32134771727.96107.99036161
8W12064sequential8134771728.38108.02036164
32W12064sequential2134771729.04108.06236165
4R12064sequential16134771712.2107.011363067
16R12064sequential4134771722.03107.625363070
32R12064sequential2134771754.35109.64136176
64R12064sequential1134771720.06107.51363064
4W12064random16134771760.54110.03135180
16W12064random4134771764.09110.25135178
2R12064random32134771767.8110.4813596
8R12064random8134771765.9110.36335155
2R12064sequential32134771767.06110.44135165
8R12064sequential8134771769.64110.6335161
4W12064random8134771710.77106.921183023
16W12064random2134771717.86107.361183203
2R12064random16134771698.2106.132183044
8R12064random4134771710.29106.894183055
32R12064random1134771717.75107.352183046
2W12064sequential16134771684.3105.261183040
8W12064sequential4134771687.34105.451183043
32W12064sequential1134771685.95105.371183036
2R12064sequential16134771723.77107.731183029
8R12064sequential4134771704.12106.50183046
32R12064sequential1134771717.2107.321183031
2W12064random16134771738.64108.66117157
8W12064random4134771755.12109.69017155
32W12064random1134771760.38110.02117154
4R12064random8134771761.02110.06417155
16R12064random2134771765.62110.35217138
4W12064sequential8134771731108.1811794
16W12064sequential2134771728.03108117137
4R12064sequential8134771766.88110.43117112
16R12064sequential2134771767.86110.49117169
2W12064random8134771691.57105.72183028
4W12064random4134771750.12109.3818142
8W12064random2134771713.82107.11183022
16W12064random1134771758109.871843
2R12064random8134771762.74110.1728145
4R12064random4134771723.21107.7283035
8R12064random2134771765.4110.3328133
16R12064random1134771707.97106.74283031
2W12064sequential8134771728.4108.0218150
4W12064sequential4134771685.1105.31183038
8W12064sequential2134771730.99108.181846
16W12064sequential1134771688105.5183039
2R12064sequential8134771747.58109.2208165
4R12064sequential4134771715.02107.18083021
8R12064sequential2134771765.55110.3418171
16R12064sequential1134771706106.62183031
2W12064random4134771673.4104.5804144
4W12064random2134771702.13106.38143015
8W12064random1134771753.95109.6214135
2R12064random113477411.0925.69043013
2R12064random213477849.2153.0704135
2R12064random4134771507.9394.24043021
4R12064random113477834.5852.16043021
4R12064random2134771516.6794.7904139
8R12064random1134771501.793.85043008
2W12064sequential4134771686.82105.42143026
4W12064sequential2134771726.97107.9314130
8W12064sequential1134771682.9105.18143034
2R12064sequential4134771660.67103.79043017
4R12064sequential2134771691.5105.7114165
8R12064sequential1134771661.5103.84143014
2W12064random2134771608.38100.52023007
2R12064sequential2134771653.15103.3212125
4R12064sequential1134771593.599.59023014
4W12064random1134771742.95108.9301131
2W12064sequential2134771728.87108.050186
4W12064sequential1134771683.59105.22013030
2W12064sequential1134771563.297.7003021
2R12064sequential1134771551.996.99003013
October 1, 2010 at 2:02 am
It looks like a good sata drive, good stable overall performance.
But you have to compare it with other drive to have some kind of baseline.
No need to run so many tests and to go that high in thread count, as you can see, once the avg_latencyms start to rise steadily as (thread cound x ioOustanding) increase, your drive has reached it's io/sec limit (> 16 in your case).
You should also run 10/20 mins tests (and not 120sec) if it's a SAN.
Make also sure you are using a big filesize (10GB or 20GB) to avoid hitting the disk cache (SAN can have very big cache).
Depending on what you plan to use the drive for you have to focus on different things.
If you plan to store backups or regular files, you want the highest MB/sec as possible in sequential read and write.
If you plan to put tempdb on it, you want highest io/sec as possible.
If you plan to store log files you are looking for good sequential write MB/sec troughput.
If you plan to put the data file of a DB you are looking for 8KB and 64KB random Read and Write with highest IO/sec as possible.
October 12, 2010 at 5:40 am
Thanks for your reply....
Based up on above results how I can make a decision:
Which files I have to place on this drive to get high performance benefit and please explain how to make decisions based up on the output of SQLIO.
Sorry this much late response as I got stucked on to other issues.
Thanks,
Vamsy
October 12, 2010 at 6:03 am
Any one please help me on this..........
October 12, 2010 at 6:47 am
You don't take decision based on a single SQLIO, it works the other way around, you have expectation (xxx IO/s or xxx MB/s) and you use SQLIO to make sure you can reach it.
If you have no idea of how much IO/s or MB/s you need then ... there is no way to tell you if the drive will be enough for the job.
You could put anything on this drive, but to know if the performance will be good, it all depend on what is "good" for you, there are way too many different variables.
Now, based on what we have here, the drive would be ok for any DB files (data, log or backup) but don't expect it to keep up if you have too much load.
Don't mix different kind of load though (no log and data on the same drive).
October 12, 2010 at 7:22 am
Thanks for your suggestions:
Is this the way of predicting how much IOs do I need? Collecting avg disk reads/sec and avg disk writes/sec using perfmon
Thanks,
Vamsy
October 12, 2010 at 7:54 am
Yes, you can see how many IO you are using right now.
If you can test that on a DB server already running you would already have a good idea of what to expect.
If you see no improvement when switching to faster disk, that means your disk is not the bottleneck.
October 13, 2010 at 8:04 am
PLEASE do yourself a favor and get a professional to help you configure your IO system correctly. Right now is the ONLY time you will have to do MANY things correctly - and from my experience almost EVERYONE does most things SUBOPTIMALLY from an IO configuration standpoint.
I do have to note that it really doesn't make much sense to run a test that you have no ability to interpret the output of!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 13, 2010 at 9:24 am
A) Yes, get a professional in. You'll learn a lot, and have a better setup.
B) For a simplified system, build yourself a table with rather less factors: Change the IO size (8, 64, 256, and for sequential 1024), and random/sequential and read/write. Analyze all the random together, and all the sequential together. See what the curves look like.
October 14, 2010 at 8:41 am
Take a look here: http://msdn.microsoft.com/en-us/library/cc966412.aspx
IO size = 64 KB is not enough.
October 15, 2010 at 2:26 am
Actually most sas disk will have the same throughput for IO size of 8K and above, so you really just need to test for 8K.
October 15, 2010 at 7:27 am
Oliiii (10/15/2010)
Actually most sas disk will have the same throughput for IO size of 8K and above, so you really just need to test for 8K.
Do you have any reference you can point me to to confirm that statement?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 15, 2010 at 9:20 am
I didn't read it online, it's something we've noticed here while testing sas disk (so i don't have a link).
Later it came up in a High perf IO subsystem presentation we had with a msft PFE and he confirmed that most sas disk reach their max throughput with 8K+ io size, which is quite easy to test if you have a sas disk on hand.
If all you have a is a LUN you'll have to crank up the concurent IO to offset whatever latency you have and use a file big enough to avoid hitting the cache.
I should add that it's only true for sequential IO (but since we are talking about throughput i guess that was obvious).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply