Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

We're running sql server on vmware. Our san is pretty new but still I think I/O is pretty crappy even though I'm supposed to be on separate spindels. Server speeds are:

enter image description here

My client machine:

enter image description here

Is our san slow? Can I get some comparative values from other users? If so please let me know if you're on a vm or a physical machine. It looks like it's doing pretty ok writing but reading is really terrible...

share|improve this question
2  
Please post what kind of RAID array you have on the host, how many disks at what speeds (or if SSDs what model #s), and what raid array's are available to you as the client. – Ali Razeghi Nov 15 '12 at 10:56
I don't really know how many disks but I know it's a raid 5. Unfortunately most of it is a black box to me. The client is a ssd no raid so it might not be a fair comparison? – Asken Nov 15 '12 at 11:03
1  
CrystalDiskMark is good to get a brief idea of your IO perf, but I'd recommend using SQLIO to get a better look at your SAN. Brent Ozar wrote a great article about it (brentozar.com/archive/2008/09/…). – Thomas Stringer Nov 15 '12 at 14:28
1  
What kind of read speeds do you need to achieve for the project? It's possible that the hardware you've been allocated isn't sufficient to meet the goal. – Jon Seigel Nov 15 '12 at 18:01

3 Answers

up vote 6 down vote

The performance numbers that someone else gets from their array compared to the numbers that you get are totally meaningless. I could have 1000 disks in my array backed with 512 Gigs of cache or I could have 2 7200 RPM disks in a RAID 10.

Without knowning the exact configuration that your array is configured in the numbers are just numbers.

Another thing to note is that 4k IO are a worthless measure as are 512k IO. SQL Server will be doing almost all of it's IO in 64k IO.

share|improve this answer
Denny, I don't understand how he is getting 1.877MBps in 4k reads but 16MBps in writes on the same array. His client machine also get's lower read speeds than writes. I read your disk hardware chapter in the deep dives book and this doesn't make sense to me. Am I missing something or does it look like a botched test? – Ali Razeghi Nov 15 '12 at 18:22
3  
My best guess is the cache on the array is absorbing the writes giving him the 16MBps write speed. Just another reason why doing this sort of thing without knowing what the hardware underneath is, is basically pointless. – mrdenny♦ Nov 15 '12 at 18:27
1  
@mrdenny What makes you think SQL server will only generate I/O in 64K chunks? The default page size is 8k, and even though SQL server commonly does an 8 page read-ahead, the default NTFS block size is 4K and a lot of HBA drivers will break up 64 KB chunks unless tuned otherwise. That's a lot of variables, and that's only on the host side. 4k IOPS seems to me to be an excellent number of reference, although I do prefer to test all relevant chunk sizes. – Roy Nov 15 '12 at 20:49
I think it has to do with the fact that even if you are just returning 1 tiny row, SQL Server in the real world will still have to seek into a index file or be scanning tables all of which should pass through several extents before retrieving your data, but I await Denny's answer! – Ali Razeghi Nov 15 '12 at 22:08
1  
SQL Server issues almost all reads and writes per extent not per page. This is done because most extents are single object extents not mixed extents. You'll see single pages be read from disk if for some reason the SQL Server aged pages from the buffer that weren't needed, but most IO from the disk should be at the extent level, not the page level. If the HBAs, disk, etc. change that is outside of SQLs control. This is why it's usually recommended that 64k blocks be used at the disk level. – mrdenny♦ Nov 16 '12 at 23:53
show 1 more comment
up vote 2 down vote

It looks to me like your "true spindle performance" is just under 500 IOPS, although I'm not entirely sure why increasing the queue depth gives you that much higher performance. (Perhaps someone else here is able to comment on the impact of queue depth on random I/O?) Also, the higher write performance is most likely caused by write back cache on the SAN side, and you should expect this number to plummet if that cache ever goes full.

500 IOPS is what I would expect from reading across four 15k spindles, with reasonable (less than 10 ms) response times.

The close to 6.000 IOPS indicated by the QD32 4K reads is something I would associate with reading from cache, across two shelves of 15k spindles (eg. 30 spindles) or a single enterprise class SSD drive (although the Oracle Database Appliance sustains 10.000 IOPS across only twenty 15k drives). Like I wrote above though, I'm not sure how to interpret this number from CrystalDiskMark. I do suspect that we might see high response times under this workload and I assume caching on the SAN side comes into play, but I'm not sure why you don't see a similar effect on single thread read test. HBA and driver configuration posibly.

You don't provide much detail about your SAN, environment or workload, so there is no way for me to determine if the numbers indicated can be considered typical or not. Nor do I think that determination is particularly important.

What I think is important, is whether the indicated performance meets the requirements of your database. In my opinion you should try to figure out what performance you need and then work to ensure those requirements are met.

share|improve this answer
I'll see if I can dig up more information about it and improve the question. What we're trying to figure out is if we should ask for our own server or not. It's the "hardware is cheap" vs "we want ALL in vmware" argument going on... – Asken Nov 15 '12 at 11:52
In that case I would strongly suggest to work out the I/O requirements of the database, and then look at how and if these requirements can be met on vmware. My personal feelings are that I want as few layers as possible between my databases and the physical hardware, so I can more easily control and predict performance and other variables - although I'm sure some will consider me old fashioned. – Roy Nov 15 '12 at 11:58
it's a datawarehouse and reporting server so read is pretty important. i'm also old fashioned then... i'll be back. thanks – Asken Nov 15 '12 at 12:01
Consider trying iozone.org or a similar tool to give more useful metrics. – Roy Nov 15 '12 at 12:26
To answer the 'queue length' question, I notice several disks and arrays react very differently to queue length and number of threads. If I increase the outstanding IOs (queue length) it forces a different algorithm which increases the overall performance, up to a point. I also get the same exact performance results by increasing the threads writing to the disk vs increasing the queue length. – Ali Razeghi Nov 15 '12 at 18:13
up vote 1 down vote

Can I get some comparative values from other users? If so please let me know if you're on a vm or a physical machine?

Here are some performance benchmarks I did on various disk configs after turning off all caching, and there are no VMs, these are just raw disk metrics. I did not see any combination that resulted in vastly lower reads than writes. As Denny pointed out, you might be caching your writes, which means your results will mostly not be typical in the real world. The very low read performance troubles me but if you don't need more IOPS than that then it doesn't mater. It really seems like you need to figure out how many IOPS you need and demand it from your SAN admin.

I have tested and have results for 8 disk RAID 6 drives using 15k RPM disks, 10 disk RAID 6 drives using 15k RPM disks, 8 disk RAID 5 using 15k RPM disks, and 10 disk RAID 10 using 15k RPM disks. I can upload the results in a excel sheet for you to go through if you'd like.

Edit: I added all the test patterns using 2 threads on a 8 disk RAID 5 array for you to compare at: https://docs.google.com/spreadsheet/ccc?key=0Avbd5fFdssIhdExlbDVJbzltTTJwcURsaU90OE4tbmc

Threads and IOsOutstanding have almost identical results, so if you look at 2 threads and 4 IOsOutstanding, you can get almost identical results with 4 threads and 2 IOsOutstanding.

For the person who asked what the outstanding_ios might have on performance, this is what I saw:

Current results are for Pliant enterprise MLC SSD in RAID 1 (2 disks mirrored together):

Notice how the performance is the same whether I increase the outstandingIOs or the threads, but the more of either or combined you throw at the system, the faster it goes until it hit's a hard limit:

2 threads with 2 outsandingIOs

3386 IOs_Sec and 212MBs_Sec RandomIO

3486 IOs_Sec and 218MBs_sec Seq. IO

*Now we will increase the thread count to 4 with 2 Oustanding IOs *

5110 IOs_Sec and 319MBs_Sec RandomIO

5502 IOs_Sec and 344MBs_Sec Seq. IO

*Now we will reduce the thread count to 2 and increase the IOs_Outstanding to 4*

5110 IOs_Sec and 319MBs_Sec RandomIO

5471 IOs_Sec and 342MBs_Sec Seq. IO

You will continue to get throughput increases but avg. latency will go up until you hit the hard limit of maximum throughput the more you either increase the threads or outstanding IOs, at which point you will only accumulate further latency.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.