April 26, 2011 at 8:42 pm
dant12 (4/26/2011)
AgreedThe usual "depends" apply perfectly here.
Must always keep in mind RAID 5 is not best suited for heavy write operations (such as tempdb and transaction logs)
Again, that depends. You can have a RAID 5 array perform just as well as a RAID 10 - depending on the number of spindles. You may not be able to afford enough spindles on RAID 10 to maintain disk space requirements. Also, due to fewer spindles (caused by budget constraints) you could see worse performance on a RAID 10 than with a RAID 5 (we currently are seeing that on one of our setups - raid 5 outperforms raid10 due in large part to spindles allocated).
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
April 26, 2011 at 10:35 pm
the more the merrier, I always say!
Also, to get this clear since the op mentioned 2TB drives, it is also better to have more disks with fewer bytes per disk in your RAID setup than to have fewer disks in the TB range
Would you mind sharing what was the threshold(ARRAY size) where the RAID 5 outperformed RAID 10, also the RPM on the disks
--
Thiago Dantas
@DantHimself
April 27, 2011 at 7:30 am
Performance varies. R5 has a write penalty, but it's not that huge unless you are really stressing the system.
The bigger thing is reliability, especially if you buy drives from the same batch. R5 can withstand one loss, and it takes time to rebuild (which impacts performance). R10 can potentially withstand 1/2 the drive failures. It could also fail with 2, but those whole strategy is playing the odds.
If I had 6 drives, rather than 2 R5 arrays, I think I'd look at one R10 array, just to increase reliability.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 27, 2011 at 3:09 pm
We dont have any buchmark system. Right now we have loaded this 100GB data in a Testing SQL server, its configuration is 4GB RAM, one Dual core processor 2 SATA hard disks, Performance of select queries are very bad. Our main aim is high speed data read.
Actually we collect these Medication formulary data from whole insurance compaines and stored in our Database and give this data to Physicians to write prescriptions. If anything happend in data storage, we reload whole data again. Weekly we will get changes of these data (Around 10GB). We will update these changes on every Sunday.
Cost of RAID 10 is high so can we change to all RAID1 arrays?
Hard Disk :- 4 RAID Contollers (SCSI fiber channel) and 4 arrays
1. RAID Array level - 1 - for OS
2. RAID Array level - 1 - Temp DB and system Databases
3. RAID Array level - 1 - Application Database files
4. RAID Array level - 1 - Transaction Log
Pls advice me.
Thank you
Jefy
April 27, 2011 at 3:19 pm
jefydominic (4/27/2011)
We dont have any buchmark system. Right now we have loaded this 100GB data in a Testing SQL server, its configuration is 4GB RAM, one Dual core processor 2 SATA hard disks, Performance of select queries are very bad. Our main aim is high speed data read.Actually we collect these Medication formulary data from whole insurance compaines and stored in our Database and give this data to Physicians to write prescriptions. If anything happend in data storage, we reload whole data again. Weekly we will get changes of these data (Around 10GB). We will update these changes on every Sunday.
Cost of RAID 10 is high so can we change to all RAID1 arrays?
Hard Disk :- 4 RAID Contollers (SCSI fiber channel) and 4 arrays
1. RAID Array level - 1 - for OS
2. RAID Array level - 1 - Temp DB and system Databases
3. RAID Array level - 1 - Application Database files
4. RAID Array level - 1 - Transaction Log
Pls advice me.
Thank you
Jefy
Raid 1 will give you no speed increase, only redundancy, hence the suggestion for RAID 5 which can be cheaper than RAID 10.
Right now you are basically using a home desktop PC as a production database.
If you don't have heavy OLTP load and no, or at least low, requirement for high uptime and also the ability to just reload data in the case of data loss, you might benefit in using interely RAID 0 instead of RAID 1.
RAID 0 will give you no hardware redundancy whatsoever but will help with I/O throughput. Might be better to keep RAID 1 on OS and installs and RAID 0 for everything else.
As usual, it depends.
--
Thiago Dantas
@DantHimself
April 27, 2011 at 3:47 pm
stick tempdb and the systemdbs with the data files on R10. You likely won't get a huge benefit from separating them, and I'd combine them first.
Make sure you drop backups on the OS drive then for separation.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 27, 2011 at 5:48 pm
Hi Steve,
You mean combaine SystemDbs, TempDB, Application database and Transaction log and store in RAID array, is it? We will not get any benefit to split these into 3 sparate RAID arrays, is it?
Thank you
Jefy
April 27, 2011 at 7:15 pm
what he meant was to leave the database transaction log together with the database data files. the idea behind splitting those is improving DML (insert,update,delete) performance.
since your load will be only once a week and most operations will be data retrieval, the gains don't seem to outweight the costs
--
Thiago Dantas
@DantHimself
April 27, 2011 at 9:11 pm
Thiago is correct. I would do
Raid 1 - OS and backups
RAID 10 - tempdb, system dbs, data files
RAID 1 - log files
How many drives do you have?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 28, 2011 at 3:20 pm
We can use following 3 RAID controller arrays.
1. RAID 1 - OS and backups
2. RAID 10 - tempdb, system dbs, data files
3. RAID 1 - Application data and log files
what is best RAID controller? Fiber channel type?
CPU 2 Quad Processors >=3MHz, 64Bit
Memory 16GB RAM per processor, total : 32GB
OS: Windows 2008 64Bit
SQL Server Enterprise edition, 64Bit
we can continue with above CPU and RAM configuration, is it?
Thank you
Jefy
April 28, 2011 at 3:24 pm
hi Steve,
Right we didn't purchase any server. We are planning to purchase a server.
This is our first huge database setup. So we dont have much knowledge about SQL server resource calculation. If you know any reference site please give me.
Thank you
Jefy
April 28, 2011 at 4:50 pm
Google searching i found and this seems pretty good
--
Thiago Dantas
@DantHimself
April 29, 2011 at 7:45 am
A few links:
http://blogs.msdn.com/b/bartd/archive/2010/06/16/sql-server-sizing-resources.aspx
If you don't have an existing system, it's all just a guess, and most people just guess high and hope for the best. Getting dual quad cores, maybe in a 4x machine with 2 sockets empty gives you room to grow. I would also go with as much memory as you can stuff in there. That takes a load of the I/O subsystem.
In terms of drives, you want as many as you can afford as well. That's a balance with memory, but I'd hope you get 8 or more.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 29, 2011 at 5:32 pm
I have taken hardware configuration from DELL SQL Advisor, It is given below.
DELL Power Edge server 2950
CPU - one quad intel processor 64Bit
RAM - 32GB
HBAs - 2 (Single Port Fibre Channel)
Storage System : Dell | EMC CX3-10 (2 additional DAEs)
4 RAID arrays.
1. RAID 1/0 - OS and backups
2. RAID 1/0 - tempdb, system dbs
3. RAID 1/0 - Application database
4. RAID 1/0 - Log files
I think above configuration is too expensive.
Please check above configuration
Thank you
Jefy
April 29, 2011 at 5:36 pm
What's too expensive? The price is based on performance. If performance matters, you pay more. If price matters more, performance suffers.
There isn't a good way for us to "check" your configuration for the workload. That might be fine or it might not. A lot depends on your application code and query mix.
If 4 RAID arrays costs too much, can you go with 3 and add more memory? I'd go with at least 3
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply