September 26, 2014 at 7:01 am
We have a rather large and busy OLTP database of about 1.3 TB in size. We've provided the disk storage from an EMC VNX5600 SAN. The DBA team constantly finds itself at odds with the Infrastructure group over the performance of the SAN.
We utilize a DMV query from Glenn Berry to obtain metrics for all of the individual data files of our servers. The query shows I/O stall times in milliseconds for reads and writes and calculates an average.
SELECT DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf WITH (NOLOCK)
ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
Our data file drives are allocated from a RAID-5 pool, and our transaction log files are allocated from a RAID-1+0 pool.
What we consistently see however is that I/O stalls per read are about 86.3 ms while write I/O shows 13.1 ms on the data file drives, and on the log file drives we see 26.5 ms for reads and 3.0 ms for writes.
This seems to contradict what I thought that I knew about disk I/O. Reads should always occur faster than writes.
Does anyone have any insight into why we are seeing this? Does it indicate that the EMC may be misconfigured, or that it isn't operating right? Do you see similar characteristics in your environment?
Thanks for anything you share that might shed some light on this for us!
Larry
September 26, 2014 at 10:10 am
I've recently been trained to look after a new Dell Compellent SAN which my company bought so I'd thought I'd share what I'm seeing.
The data in a Compellent is in a RAID 10 config, until a "replay" is taken which then converts the data into a RAID 5 stripe (for reads). However any new writes coming in go in as RAID 10 replacing the RAID 5 stripe.
All our volumes are showing consistently lower latency for writes (2 ms avg) than reads (5 ms avg). Although I would point out we're not taxing the system (yet).
This is more than likely due to the cache configuration on the SAN controllers, with writes taking precedence. This could be the same for you, all I can advise is give your support team a call.
September 27, 2014 at 7:03 am
Cold: Many thanks for the reply! You provided me with some good information. I'm very curious about how you've got your infrastructure for the SAN wired together. If you are obtaining those metrics using a similar DMV query you're achieving very good performance out of your setup!
I do see some files with performance characteristics similar to yours, but almost half of the files on the server exceed more than 50 ms per read I/O. I know that the SAN internally moves files between SSD, SAS, and SATA storage depending on frequency of access, however all data files get accessed many times a day. As I understand it, the SAN only shuffles data blocks around between storage mediums once per day. Many of the worst performing files also have some of the highest total I/O counts.
I'm going to have to find a back-door route to speak with someone at EMC - our Infrastructure team have stated that I can't contact them directly at work. 🙁
Larry
September 29, 2014 at 2:50 am
We're running a Dell Compellent, with two SC8000 controllers (16GB of cache) and two SC220 enclosures each with 14 x 15k disk (one hot spare per enclosure).
It's a very simple setup as we only have one tier of storage (at the moment). The stats I'm getting are from the Compellent's monitoring tool, Enterprise Manager however the DMVs do give similar stats.
I would point out that we are in no way taxing the system at present. We're in the process of upgrading from our old 2005 instance (running DAS) to SQL 2012 instance running on top of the SAN. So we're got one database live in the new instance (which is a kinda staging database that feeds into our DWH) with another 40 being log shipped. It'll be interesting to see how the stats change as we migrate databases across.
Also, we did see an increase in IO for some of our volumes a couple of weeks ago. Going from ~5ms to ~20ms, when we investigated it was due to one of the power supplies on one of the controllers failing, which disabled the cache on that controller. Dell SC8000s have no internal batteries so this is "feature", apparently.
I'd definitely try and speak to someone at EMC, 50ms seems very high. What do your SAN guys mean by "...can't contact them directly at work"?
September 29, 2014 at 8:45 am
My company uses EMC 15k VMAX SAN with tiering as well. I run into this frequently, where the SQL DMVs are reporting way out of line figures vs. what the SAN monitoring shows.
It crops up in SQL Sentry monitoring, running DMV queries, and via a 3rd party service auditing tool. They'll show crazy high read times, while the SAN monitoring is barely flinching. The only general SQL best practice difference I've found is that we don't format the drives in 64k allocation units. However we go with the EMC recommendation for that.
Luckily, my SAN guy is really nice, and he sits right behind me, so I have an easy time confirming that the SAN is just dandy.
My advice would be to try to find another source showing disk IO issues[/url]. Either wait stats or perfmon counters, outlined by the linked article.
I'm not sure those DMVs are entirely reliable, or perhaps the way queries AVG them is skewing things a bit. For instance, backups, index/stats maintenance, full text maintenance, and DBCC checks can cause spikes that don't necessarily mean your disks are under-performing in any way.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply