January 9, 2015 at 11:37 am
I got the following cenario: 2 storages Dell Equallogic PS 6110 XV, each with 24 disks sas, 600gb 15 k, about 4000 IOPS; And 2 storages Dell Equallogic PS 6110XS with 7 ssd discs, and 17 sas disks 10k.
And 2 datacenters, each one with a storage pool: 1 PS 6110 XV plus PS110XS.
One Sql Server database with 4tb divided by: 10 datafiles, 1 logfile
My previous infrastructure I'd one Lun for each file, but to the new environment, the Dell vendor, tell us that create bigger luns to store many datafiles is better than create a lun for each datafile.
But, I have my questions about this. I really believe that this solution was proposed to make the storage replication possible, and not to prioritize performance.
According to Dell, my new 2TB volumes each one with many datafiles, are splited between the two storages, based on IO consumption. But, I don't have a desireable performance.
So, what is better? Split my datafiles for various volumes with size near than my datafiles, or, keep these huge volumes with many datafiles?
The I/O could be better, if I separate this files? I believe that the result could be better with minor volume sizes, because, only the datafile with high IO consumption will be moved between the storages.
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64) Jun 24 2011 13:09:43 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) ==> Windows Server 2008 R2 Enterprise 64-bit (6.1, Build 7601)
Thanks
January 9, 2015 at 3:46 pm
I had a great conversation with Alan Hirt about this last night at the New England SQL Server User Group.
The way he put it is that, in the old days, we had actual disks that we used to separate out files, filegroups, data & logs, onto. Now, with everyone on some type of SAN, we get LUNs, which look a lot like disks. But they aren't. And what happens is, we tend to think of them as disks and treat them as disks, but on most SANs, the LUN is a shared resource running on disks with other resources. His argument, and I think it's pretty good, is that instead of just looking at a breakdown by LUN, which won't get us what we need, we have to look at what kind of IOPs we're looking for. Then we work with the SAN team to meet that. If you're getting adequate performance out of the standard shared LUN (which is maximized to save storage space, not for performance), then you're all good. If not, simply taking the same architecture, but making the LUNs a little smaller and separating those, isn't adequate. You actually have to work with the SAN team to re-architect the LUN to make it faster.
It was a great conversation. Focused my understanding better (I hope).
Is that at all helpful?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2015 at 2:00 pm
Personally, I sometimes think some of that is a line of hooie from SAN admins that have their own agenda or are, maybe, just lazy. I'm not a SAN admin but it's my understanding that a LUN can be assigned to part of a disk, a whole disk, or many disks. I see no reason why logical drive letters and the like cannot actually be pointed to separate physical drives in a manner meant to bring more spindles into play for the combination of operations that SQL Server uses. There's no reason that I can see as to why the MDF drive(s) can't be physically separate from the NDF drive(s) and why TempDB can't have its own drive(s) (except for the fact that drive sizes are huge and TempDB usually is not so it would be a waste).
Physics hasn't changed. Bring more spindles into play and you'll get better performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2015 at 3:31 pm
No, they can adjust the disks, but most of the time the disks are adjusted for the most money saving setting, saving disk space, not speed. However, before you just walk in and say "I want it fast" I'd argue that you have to prove that the default is slow. Plus, again stealing from Alan, he mentioned, and I hadn't thought about it, how people will very carefully separate their log files to a different disk or lun, because, logs write sequentially, so we need to keep them separate... and then pile 50 different logs onto the same drive which is then writing how? Not sequentially. It's bouncing all over the place for each of the different files.
It was a really interesting talk, and Alan is a data guy, not a SAN admin.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2015 at 6:14 pm
Yeah... thinking about this, its a really interesting subject.
SAN looks better then DAS, in flexibility, and contrary to popular belief, SAN is not a performance technology.
This concept of disk abstraction (vdisks), is still very unclear to me.
Its something very similar as:
1) The host send a blocked-based accessed request to storage;
2) The SCSI encapsulates the data into an acceptable packet to SWTICH;
3) The SWITCH SAN converts the data;
4) So, where the data is read (LUN? vDisk? Where?)
How the storage gets the binary data and slice it by the logical units?
How the vDisks are bounced between the best LUN?
The storage is able to identify the portion of data and move just this portion?
Because, if I have a 2tb volume, with 3, 4 or more data files. The logical unit is a shared resource, ok, but the LUN that is a shared resource, then the LUN that likely won a new address, based on most faster disks, to respond an request, and the time to something like this happen should be placed on the table.
But your answer Grant, clarified a lot.
Thank you.
January 10, 2015 at 8:43 pm
Grant Fritchey (1/10/2015)
No, they can adjust the disks, but most of the time the disks are adjusted for the most money saving setting, saving disk space, not speed. However, before you just walk in and say "I want it fast" I'd argue that you have to prove that the default is slow. Plus, again stealing from Alan, he mentioned, and I hadn't thought about it, how people will very carefully separate their log files to a different disk or lun, because, logs write sequentially, so we need to keep them separate... and then pile 50 different logs onto the same drive which is then writing how? Not sequentially. It's bouncing all over the place for each of the different files.It was a really interesting talk, and Alan is a data guy, not a SAN admin.
Heh... for the very reason you stated, it wouldn't be me that piles 50 high frequency log files onto a single disk set. 😀 Nor even 50 MDFs if I could get away with it.
But, point taken. Even if you could work some serious magic on the SAN to double or maybe even triple the IOPS, that pales in comparison to what bad code can do to a system. Like I've said many times, "Performance is in the code".
--Jeff Moden
Change is inevitable... Change for the better is not.
January 11, 2015 at 5:45 am
Glad to hear it's helpful. As this technology grows and changes, it all starts to resemble magic.
I think the most interesting takeaway I got from the discussion was that, essentially, the logic behind our break up of data, log, tempdb, is still sound. But simply addressing it at the most fundamental level, the lun/disk, isn't right.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply