February 9, 2011 at 3:25 am
Hello,
I have many issues relating to this subject....but my attempt to write something ended up being over 2 pages long!! so i will start with a specific question to see how it goes!!
we support OLTP databases, and at the top end we have 150GB activedb (heavily used), 300GB archivedb (rarely used) and decent tempdb usage. currently the hardware a customer gets depends on how much they spend....but as we all know....expectiations rarely meet actual findings and most of the customers under-spec'd their systems to save money and are now moaning about performance!!
the problem is that we hold the data on a massive RAID10 lun on an MD3000i iSCSI SAN which is shared between customers. i have only been here for 6 months so had no input....but need to find a new strategy for any new customers i feel....on some systems we are getting 2000ms logflush wait time and up to 5000ms I/O wait time!!
this RAID10 LUN contains all the datafiles, logfiles, tempdb, system db's and full text indexes (*everyone winces!!) i have tried to explain that logical partitions are useless when it comes to db storage but without providing proof the hardware guys dont want to listen....so i am waiting for my lab!
what i need to know is how queue depth and filegroup/datafile numbers should be altered when you have limited spindles....i would say that most LUN's presented to the servers are luckly if they manage to get a single spindle to themselves
ie: in an ideal world 15 disks in a 14 disk RAID10 gives 7 active spindles split between 2 servers gives 3.5 spindles per server....add the main database, log file writes, tempdb work, multi-threaded parallel queries and 16 filegroups with 1 datafile each split by application module rather than splitting indexes and tables (my boss's idea!!)....most people cannot see the problem here....but i think we have a massive queue problem!!
if im correct each datafile can spawn an i/o thread....say half are used regularly gives 8 threads plus the transaction log writes and other I/O....i would guess there are probably 12 I/O requests being thrown at any one time to a LUN that maybe has 1 or 2 spindles to respond!! most people think....oh your SAN has 14 drives so its all good....but most of those spindles are busy with other requests
my questions are:
1. should we reduce the number of filegroups to only 2 or 3 to contain non-clustered indexes and heavily used tables rather than 16 to reduce the number of requests or does it not make much of a difference? (i was told that the space reclaimed by index rebuilds is more efficient with extra filegroups as space can be reclaimed from each one....but i think that was made up to support the existing setup!!)
2. do the filegroups still need 1 datafile per CPU core if we do not have enough spindles to handle that number of requests? what number should we use for an 8 core server that only has 1 spindle available?
3. should we reduce the queue depth to around 3/4 due to the lower number of spindles?
4. should we still create 1 tempdb datafile per CPU when limited by spindles?
I know there is a lot here....but i appreciate any thoughts and especially anyone with experience in testing any of these theories
regards,
lilywhites
November 30, 2011 at 1:59 pm
Same aggregate with different logical volumes at Windows, would not prove any performance gain. Per statement, I am presuming that 16 filegroups are going to 16 disks (again same aggregate), you have a huge disk contention. Average Disk Queue Length + Average Disk sec/Read + Average Disk
secWrite will give you right answer. You are going in a right direction to prove disk waits. Collect as many metrics as required including some physical disk counters in the performance monitor.
Somethins to know:
PhysicalDisk\Avg. Disk Sec/Read
This measures the average time, in seconds, to read data from the disk. If the number is larger than 25 milliseconds (ms), that means the disk system is experiencing latency when reading from the disk. For mission-critical servers hosting SQL Server® and Exchange Server, the acceptable threshold is much lower, approximately 10 ms. The most logical solution here is to replace the current disk system with a faster disk system.
PhysicalDisk\Avg. Disk Sec/Write
This measures the average time, in seconds, it takes to write data to the disk. If the number is larger than 25 ms, the disk system experiences latency when writing to the disk. For mission-critical servers hosting SQL Server and Exchange Server, the acceptable threshold is much lower, approximately 10 ms. The likely solution here is to replace the disk system with a faster disk system.
PhysicalDisk\Avg. Disk Queue Length
This indicates how many I/O operations are waiting for the hard drive to become available. If the value here is larger than the two times the number of spindles, that means the disk itself may be the bottleneck.
You probably have gone thru several different KB articles. But here is one them:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply