October 14, 2007 at 5:46 am
Hi there, I'm looking for a bit of advice on the best file layout setup.
We have a Server running SQL Server 2000 that has been identified as being pretty-much disk bound. The following types of file are all stored on the same RAID device:
1. Operating System (inc swap file)
2. SQL Server databases (data and log)
3. tempDb (data and log)
4. Third-party data processing tool (inc its own substantial temp files)
I realise that this goes against most of the recommendations on file placement!
The Server performs a daily data load of approx 20Gb (inc indexing) for five hours in the morning, followed by the rest of the day processing / serving queries.
We are currently investigating the possibility of getting additional storage space to move one or more of the above items to a separate set of spindles. If this became available (probably through a NAS), what would be the best files to move? i.e. what would give us the best value?
Many thanks for any suggestions that you have.
Kind Regards,
Nigel.
October 14, 2007 at 9:59 am
You don't want to move SQL Server stuff off to a NAS. Maybe the third party processing, but not the SQL files.
You want more captive disk or SAN disk, and separate the logs from the data. I'd also look at moving tempdb to it's own physical array.
October 14, 2007 at 11:31 am
Thanks for the quick response Steve.
Yes, I should have added that local disk could be an option. As the company is more geared towards the use of SANs and NAS these days (and there may well be capacity there), this comes first on cost grounds.
October 15, 2007 at 5:06 am
Hi,
I have almost the same HW configuration. And have to move to another strongest station. Until then, because we have a lot of BlkBy, I moved TempDB to an RAMdisk created from RAM memory. I decreased the max memory for SQL Server instance and created a RAMdisk from there. About 256 MB RAM. And now I have a RAMdisk of 256 MB where I put TempDB.
October 16, 2007 at 6:27 am
Could you be a little more precise about the space your using ?
Commonly I normally keep the OS on Completely diffrent spindles and raids than from SQL.
(1) Usually a Raid 1 Mirror for the OS.
(2) I then Set up another Raid 1 Mirror on another set of spindles for the LOG FILES.
(3) Then another set in Raid 5 or better for the SQL DATA files.
(4) Then of course another set for file shares. Being either Raid 1 or 5 depending upon your needs.
I guess the common saying goes there's no real one way of doing it. It just depends on your budget and the space you have allocated for disks.
However there was a post earlier. Definately do NOT put SQL on NAS!
Also. Check what type of disks your buying. Weather, SCSI, SAS, SATA, ... They all have diffrent spindle speeds and duty cycles. I personally use SAS drives for my OS and still the tried and true SCSI for SQL. After using the SAS for almost 2 years now Ive seen very little failure from any of mine if thats a hint of whats to come with them.
October 16, 2007 at 8:32 am
Actually - I'd avoid Raid 5 altogether when dealing with SQL Server. Raid 1 or Raid 10 are the way to go. RAID5 has too much of a write penalty.
SQL server tends to keep a machine busy - unless there's a really good reason (like - this third-party tools requires being installed on the SAME box as SQLServer to run) - get the file shares and the third-party data processing tool off of there. Not having two applications competing for the few HD resources you have will make a lot of difference; also - not having two apps fighting over the RAM resources will mean you might not need to rely so darn heavily on the HD's.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 16, 2007 at 8:55 am
That also depends on budget.
Since in laymans terms, the writes are actually going to the log files till truncated and reads are in essence coming from the MDB. Why wouldn't a 5 suffice.
Most people dont have the room for RAID 10. You need twice the ammount of drives and loose half your storage space.
Not arguing the fact so please don't get me wrong.
If I had 10 available slots. filled with 300GB SCSI drives. I essentially still only have about 1.5TB of storage when you minus the overhead. In a Raid 5 I would have almost 3TB.
Granted the integrity is better with 5 but much more costly and much greater overhead.
October 16, 2007 at 9:29 am
No doubt that there's a smallish cost difference (smallish because disks tend to be cheap these days). It's just that OP is trying to play for performance, and the write overhead on RAID 5 is substantial.
As you said - it is going to come down to a matter of budget vs performance. I just think performance speaks pretty loudly for itself in that game, because the prices keep coming down so fast.
Also - if you had that many disks and the performance issues OP is talking about - I would NOT be advising to keep them in one RAID group. Even if you DID use RAID5 - that would be 2.1GB (2+1 in one, 5+1 in another , 1 hot spare). Unless of course - you need every ounce of that space (in which case you're not sizing your upgrade appropriately).
It all depends on what kind of traffic your DB deals with. My apps always seem to have a lot more writes (inserts+updates) than reads, so RAID 5 is just not an option.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2007 at 7:06 am
Matt Miller (10/16/2007)
Actually - I'd avoid Raid 5 altogether when dealing with SQL Server. Raid 1 or Raid 10 are the way to go. RAID5 has too much of a write penalty.
The very short answer to this is: This is actually not true at all.
It's impossible to make a blunt statement like RAID5 is never 'good enough', or that RAID1 or 10 is 'always right'. As ususal, this falls into the famous 'it depends' category.
What is true, is that RAID5 needs 4 i/o for a write op, whereas raid1/10 needs 2.
However, in your average SQL Server, there's far more seek ops than one might believe.
So, what's more interesting, is the 'overall performance', and for that RAID5 goes well beyond what many think it does.
Also, the level of RAID isn't all either, a very important factor is how many spindles there are in the array.
ie 3 drives as RAID5, not so good (but reads faster than RAID1 😉
15 drives as RAID5 on the other hand, performs very well for most.
What matters isn't the raid level, more important is the resulting i/o bandwidth that you get from your disks, be it 1, 5, 10, compared against the actual requirements of the particular system.
Ofc, there are 'specials' where only the sky is limit and the absolute 'best' is required.
Then RAID10 or 01 may be 'right', but there's no reason to outright disqualify RAID5 entirely.
/Kenneth
October 17, 2007 at 7:35 am
You can certainly get away with using RAID 5 for your data files. I've done it in the past, and have not come across a situation where there was a bottleneck getting the dirty pages written out.
But there came a day when I had to restore a large database from backup. With all the heat coming down over the dead production environment, I wished I had made a different choice.
So, one thing to consider is the performance in a worst-case scenario. For larger databases I will always specify the fastest disks, if my budget will allow.
October 17, 2007 at 8:03 am
Kenneth - you're right, I should have qualified. It's just that the OP seemed to be asking for "most performance bang for their buck", which is a situation where I'd shy away from RAID5.
One way or the other - for performance purposes, if it's at all possible - split the new space you get into several chunks, and get as many items off of the same RAID group. It's going to be better to have 4 separate RAID groups (even if raid 5) than one RAID 10.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2007 at 8:16 am
Well... Don't you think that RAID5 gives just that - 'most perf bang per buck'..?
I mean it's a relative between the perf given compared to the bucks it's costing... 🙂
And I agree, regardless of raidlevels, separate files as much as possible.
I usually try to keep a 'standard' of four arrays.
c:\ - os and binaries + swapfile
d:\ data only
e:\ logs only
f:\ misc.. files, backups etc etc.. everything 'volatile' that comes and goes
.. optional g:\ for thempdb if needed.
Raidlevels for each according to requirements and budget.
And preferrably the whole thing on at least two separate controllers or more.
Also keep an eye on how arrays or parts of arrays uses which channels on which controllers if you want to squeeze out as much i/o capacity as possible.
/Kenneth
October 17, 2007 at 8:38 am
well - once I started being able to buy 300GB SCSI drives out of petty cash, I suppose the "buck" part stopped being much of a factor...:cool: Our equipment tends to have to have a long duty cycle, so performance is a "gift that keeps giving" whereas a fully enclosure of disks (<6K) is a one-time "blip" on the expense sheet:hehe:.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2007 at 11:04 am
Exactly stated in my opinion. It's all based around budget.
Granted I run a 6TB SAN and need the consumable space. With a total of 43 drives, I personally needed to be in a RAID 5 environment as being in a 10 would have restricted my total overall space needed.
with no more than 250 users access these drives and 10K spindle speeds bottle necks for ME is not a problem.
However though, Keep in mind for the novices reading this. Disk Reads/Writes are not your only bottle neck to look at. In a SAN environment as well you need to look at the whole unit and it's tollerable accessability.
Are you using a SAN as direct attached or connected to your LAN or WAN? If direct attached are you using SCSi or Fiber. And what bandwidth are you sharing those connections over.
As stated above. With a RAID 5. Recovery is tremendously slower than with 10. However if configured correctly how ofter will you honestly be doing a full recovery? IMO with enough log backups you should be able to do point in time recoveries with the log's that are backed up to disk first before tape unless erased or overwritten making recovery times much faster.
October 17, 2007 at 11:39 am
Well - the RAID-5 penalty applies to any write operation. A bulk loading operation/ massive update/big report/anything requiring lots of tempDB space would also incur the penalty. You may very well find that you still have enough bandwidth left over to make that work for you, assuming your server doesn't do a LOT of writes.
"Fast enough" is a perception thing - if you find ways to spread out your traffic so that the RAID-5 is not a performance bottleneck, then go for it.
SAN'S also change the dynamic around, to the point that it's almost not a valid comparison. when each SAN controller has 5TB of RAM cache, it doesn't really matter how slow the ACTUAL disks are as long as you eventually give your disks a chance to catch up. I've been talking about directly connected HD's.
Oh- and stop spending money on those slow 10K spindles. Go for the 15.5K spindles. Nothing like a 50% increase to help things along.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply