Raid5 Device
-
Good day all,
We have a dual processor, 3GB ram, Raid5, Smart Array 6400 Controller (currently set to 100% write). According to fn_virtualfilestats(default,default) we have a rather large number of writes to the tempDB, about 50%, and about 15 - 20% for my other 2 main databases. The reason for this is that we use a lot of large left and left outer joins, as well as temp tables.
The Sys Admin guys dont want to enable write cache, because of the issues that can be caused by a failure IE motherboard, raid controller, etc. Data will remain on the controllers cache, but not written to disk.
What would be a good solution to this, as we are seeing slow periods due to the amount of writing that is taking place?
1. Enable write caching on the array. What can I do to stop the sysAdmin guys from stressing too much?
2. Move TempDB to a seperate drive. Set up as what?
3. Any other ideas?
Thanks in advance,
Graham
-
Not sure I completely understand your Sys Admin's concerns. The point of write cache is to ensure the data does get written to disk.
Temp DB should always be on it's own drive.
Depends on how much you want to invest and your current HW. Go to a faster write performing RAID ie RAID 10 or RAID 1; put each production data base on it's own drive.
-
The concern is this:
If a failure occurs to the motherboard or array controller while the controller is busy writing, there is going to be corruption of data. Personally I am also a bit in the dark about this, this is what I am getting told. Trying to find out if anyone else has dealt with this issue, or my sysAdmin guys talk rubbish.
No chance of going to a RAID 10. Not needed for what we are currently doing, and the cost is too much.
TempDB to what, another RAID 1 device?
Want to get the most out of what we have... Will be willing to spring for new drives of the tempDB, but not for a new RAID controller.
If I move my data files to seperate RAID 1 devices, will my read access suffer at all??
Thanks,
G
-
I posted a longer answer to this but it got lost in the ether.
So in short: One book I recommend that covers all aspects (including hardware issuest) of performance tuning is: SQL Server Query Performance Tuning Distilled 2nd Edition By Sajal Dam Published by Apress, ISBM (PBK): 1-59059-421-5
Onto some points
1. Your System Admins are correct. Do not turn on write caching as the risk to corrupt the database increases. Basically SQL Server writes data, is told it is there when it isn't and then the data goes away. Very bad.
2. Move your transaction log files to different raid 1 drives. (You want the mirror for fault tolerance and since log files are sequential, striping is not as usefull. Get fastest possible write drives for this.) Preferably 1 per important database.
3. Move Temp DB to a raid 0 drive as it is always re-built it does not need fault tolerance, it only needs speed.
4. Re-visit the design of the database. Can you eliminate outer joins? Are there other ways to improve it?
Good luck,
Brian
-
Ok, thanks for that Brian, I will have a look for that book.
Transaction logs are on a RAID1 drive already, with ALL data files on a RAID5 (including tempdb).
Basically that has answered my questions (may not have typed them out as they were in my head), but the fact that the sysAdmins are correct and there is the chance for data corruption. So then that means that the moving of the tempDB is easiest solution for now.
We are busy re-writing things, but very slowly.
What are the options for changing the outer joins to? Can nested selects be used more efficiently?
Thanks,
Graham
-
Graham,
I have no particular solution for replacing outer joins. I was suggesting more going back and analyzing the business requirements. What questions are asked, when and why? If you are doing lots of outer joins to get reports, then it may be appropriate to set up a reporting database, that is optimized for query speed e.g. denormalized, pivoted tables, etc. This may speed your entire reporting process and reduce server load thereby increasing performance.
If you have small temp tables, they might work better if stored in a table variable. Also there is no reason you can't write temporary data to a permanent table which then maybe you can index and improve performance.
Also make sure you temp db is large enough.
Actually back up 1 step and ask yourself what problem are you trying to solve and why? If the writes to tempDB are not impacting system performance then who cares. If they are then you have a better defined goal of increase system performance. Try to reduce writes to temp db. Also don't be afraid to build multi-table joins and use the Index Wizard to find the best indices.
Good luck
Brian
-
Thank you for that, got some work ahead of me, but at least I now know what direction I need to run in.
Thanks for the help!
-
To play devils advocate: Write-back caching could offer substantial performance gains in your environment.
Here's a thought:
Next outage window you have, make sure all your backups are done and have a test plan ready to determine the benefit of write-caching on your system (through a Profile playback or some other Load testing method). If you find it fast enough to justify, you can get a battery-backed up Raid Controller. All critical infrastructure should be on UPS anyhow, so they whole argument for this is moot, imho. I've been a dba and storage admin for 8 years, working specifically with small-medium SANs and I have never experienced data corruption from write-cache failure.
just my two cents, good luck!
-
Windows write caching vs hardware write caching... Perhaps your sys admins are referring to the prior - the latter is obviously nice as the hardware can have battery backups and ensure the data is written regardless of what happens to your power... But as everyone as said, moving your tempDB to a less write-intensive drive array will help things.
-
In particular, the Smart Array 6400 Controller DOES have a battery-backed cache. It will write the data to drives even after a system failure occurs, as long as the disks aren't the failure point. If the disk susbsytem is failed, you'll be rolling tape anyway.
With that particular RAID controller, it is probably safe to leave write caching turned on, but I would double check the specs with the manufacturer.
jg
-
Hi guys.
1. 6400 series comes with a backup battery to ensure data safe.
2. Write cache can speed up some queries about 70% !
3. Try to spread data in an array, index in other one and log at other. Do this by using 3 different controller channel so your IO will be 3 times faster.
4. Try to keep the grather tables or the ones that your app uses most in different arrays and controller channels. Sometimes just a feel tables are responsable for a lot of data movement.
Hope it helps
tks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply