May 3, 2011 at 10:44 am
Hi,
i need help with this configuration:
6 sas disk on hp smart array p410I how should i configure the disk for best sql performance
4 disk in raid5 put DATA file in it and 2 disk in mirror put LOG file in it or 6 disk in RAID10 and put DATA+LOG in it.
what will give me best performance (READ and WRITE)?
THX
May 3, 2011 at 10:55 am
It depends.
What is the usual load on your database? Is it mainly OLTP or mainly reporting? What is the usual load? Average amount of data changed per day?
--
Thiago Dantas
@DantHimself
May 3, 2011 at 11:49 am
That few disks, you may be at a wash for performance. Next question would be concerning how much storage space is required?
I would be more tempted to go the raid 5 route under these circumstances.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 4, 2011 at 9:43 am
Personally i'd go with the 4 disk RAID5 for database files, and 2 disk RAID1 for OS and LogFiles. But as always...it comes down to "it depends"; load, usage etc
May 6, 2011 at 8:54 am
Raid 10 is better than Raid 5
Raid 10 is specially use for Tempdb,Log Files and Indexes Files,If you have enough space on these disk then go to RAID 10 other wise RAID 5
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 6, 2011 at 9:35 am
As has already been stated, the answer is "it depends". That being said there are some guidelines.
RAID 5 write performance is very poor compared to RAID 10 due to the parity issue. This is why it isn't typically recommended for OLTP data devices which are write intensive. However, RAID 10 is more expensive. What I have often seen through the years is RAID 5 for reporting data structures such as data warehouse/data marts that are loaded infrequently and in bulk and RAID 10 for OLTP. It is strongly recommended as a best practice, as someone mentioned above, that TempDB be on RAID 10 since a significant percentage of your overall I/O occurs in TempDB regardless of whether it's OLTP or BI. If you can, it is also recommended that RAID 10 be used for the device(s) that house your log files.
"Beliefs" get in the way of learning.
May 10, 2011 at 7:22 am
I can perhaps assist here with a practical example. My prod systems are all san attached with high performance raid 10 raid sets, but my DR servers have to exist on internal disks. This means trying to figure out exactly how to set up 8 internal disks ( all 15k sas ).
Now I hate raid 5 or even worse raid 6, but in the end I put the o/s and binaries on a raid 1 pair, partitioned into a C: and a D: The remaining disks I put into raid 5 as one drive and used folders for the various sql areas ( I didn't partition the drive )
In base tests this server ( near identical spec to prod box except for storage ) is within 10% of base performance tests against the prod box. Database backups and Restores, index rebuilds, high performance sequential io tests, high performance random io tests. The prod box runs against a dedicated san storage costing nearly £200k ( with over 50 dedicated 15k spindles )
( yes I was surprised too! )
--
That's my practical experience - hope it helps.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 12, 2011 at 1:34 pm
Use Microsoft SQLIO and benchmark both configuartions; different hardware has different quirks and results; the rules of thumb are not always correct, despite all theory to the contrary (generally due to a poor implentation of the one that "should" be faster, a bug in firmware or drivers, etc.).
May 12, 2011 at 3:26 pm
I'm not dismissing sql io or iometer but be wary of results. There's nothing quite like backups and restores + some hefty sql code to really sort out differences. I have a number of posts covering benchmarking storage on my blog and website, including code to run tests. I used these as part of an exercise to scale a system - the story is too long for a forum, but some of my blog posts cover the basics. ( I had to be diplomatic as I was threatened with potential legal action over posting my test results on storage )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply