| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2006-09-14 : 11:37:49
|
I'm confused on how to calculate the max i/o for a drive. I need to find the bottleneck for SQL Server.I read over this (http://sqljunkies.com/Article/D1B7C756-4725-4D31-A53D-C0A47976E6BB.scuk) and I am now confused. quote:
Suppose from the disk drive specifications you find out that the average seek time is 3.9 ms. The average rotational latency is 2 ms. Then the theoretical disk I/O capacity is (3.9ms + 2ms) / 1000 = 169 I/Os per second. The theoretical limit is almost never reached, so take 75 percent of the theoretical limit (169 * .75) = 127 I/O’s per second. This gives you a disk I/O capacity to work with.
Where does (3.9ms + 2ms) / 1000 = 169 I/Os per second calculate? Why is 1000 used? Because its milliseconds?Any help here would ne nice... Thanks. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2006-09-14 : 11:46:28
|
| (3.9ms + 2ms) / 1000 <> 169 1000ms / ((3.9ms + 2ms) = 169 somebody got their formula wrong. It would make sense for 1000 to be used for milliseconds. |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-09-14 : 11:52:47
|
quote:
Originally posted by AndrewMurphy
(3.9ms + 2ms) / 1000 <> 169 1000ms / ((3.9ms + 2ms) = 169 somebody got their formula wrong. It would make sense for 1000 to be used for milliseconds.
Wow thanks for the quick reply  So my cheeta is (3.6ms seek + 2 avg latency) read and (4ms + 2ms) write according to the seagate website. Is it safe to that my max IOPS for reads is 178.5 and writes is 166.6 or is there something else that this guy got wrong? Thanks,Daniel |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-14 : 11:53:50
|
| Have you identified that you actually have a disk bottleneck?CODO ERGO SUM |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-09-14 : 12:05:01
|
quote:
Originally posted by Michael Valentine Jones
Have you identified that you actually have a disk bottleneck?CODO ERGO SUM
Nope just looking into things. In the ::fn_virtualfilestats table the IOStallMS is growing quite a bit each day.2006-09-11: 602268962006-09-12: 614509372006-09-13: 630337282006-09-14: 64483402What is that about 20min per day? (61450937 - 60226896)/1000/60What should I be looking for? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-14 : 12:17:16
|
| It looked to me like the article explained fairly well what you should be looking for. Avg. disk queue length, etc.If you haven't even identified if there is a disk bottleneck, calculating the max disk I/O's per second seems pointless.CODO ERGO SUM |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-09-14 : 12:22:49
|
quote:
Originally posted by Michael Valentine Jones
It looked to me like the article explained fairly well what you should be looking for. Avg. disk queue length, etc.If you haven't even identified if there is a disk bottleneck, calculating the max disk I/O's per second seems pointless.CODO ERGO SUM
Well on that disk array (Raid 1) we see a sustained value of 400-600 for writes with minimal reads at times. These seem to last for a few minutes and then things settle down. If my MAX IOPS on the cheeta is only 166.6 for writes then 400 would be a bottleneck, right?What I'm trying to say is: I believe there to be a bottleneck with the disks, but I am unsure of exactly how to identify one. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-09-14 : 13:18:29
|
| how many spindles? how is your RAID cache set? (percent read/write etc.) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-14 : 13:37:53
|
quote:
Originally posted by SQLServerDBA_Dan
quote:
Originally posted by Michael Valentine Jones
It looked to me like the article explained fairly well what you should be looking for. Avg. disk queue length, etc.If you haven't even identified if there is a disk bottleneck, calculating the max disk I/O's per second seems pointless.CODO ERGO SUM
Well on that disk array (Raid 1) we see a sustained value of 400-600 for writes with minimal reads at times. These seem to last for a few minutes and then things settle down. If my MAX IOPS on the cheeta is only 166.6 for writes then 400 would be a bottleneck, right?What I'm trying to say is: I believe there to be a bottleneck with the disks, but I am unsure of exactly how to identify one.
What does "value of 400-600 for writes" mean exactly? Where is that number coming from?What does the Avg. disk queue length look like during these high activity times?CODO ERGO SUM |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-09-14 : 14:01:37
|
| That's coming from perfmon. Disk Reads/sec & Disk Writes/sec. Disk reads and writes show about 400-600 when sum'd together but most of the time are very low. The Disk queue is usually about 5-12 but sometimes goes to 20 and can spike up to 100+.Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
|
|
|
|
15k cheeta drives max i/o?