August 8, 2006 at 12:15 pm
We are setting up a new xseries ibm server for OLTP. We have two RAID controllers (2 GB/sec serial attached scsi and a dual channel standard RAID card). The controller manual says to 128 KB block size for OLTP and 16KB blocks for std raid. We are concerned about the big difference between the two controllers. Any ideas or recommendations? Should we benchmark using SQLIO
The server config
OS on 2 drive RAID1 using SAS RAID
logs on 2 drive RAID1 using SAS RAid
tempdb on 2 drive RAID1 using SAS Raid
data on 8 drive RAID10 using dual channel RAID controller
backups on 2 drive RAid 0 using dual channel RAID controller
August 8, 2006 at 2:07 pm
Should be 128 KB block size for SAS (not OLTP)
August 10, 2006 at 9:33 am
The recommended block size for SQL Server is 64K, because that is the size of most SQL Server I/O operations. Whether or not that trumps the recommendation in the hardware manual is hard to say, both are generic one-size-fits-all recommendations.
You could try to get some information from the vendor specifically for an OLTP SQL Server installation. If they think you should use 16K or 128K instead they should be able to explain why.
If you have the time to rebuild and benchmark your new server in multiple configurations, then by all means do so.
August 10, 2006 at 10:00 am
Just a clarification on SQL Server I/O operations ...
SQL reads in extents - 64 kb blocks or 8 pages
SQL writes pages - 8 kb
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 10, 2006 at 10:29 am
Thanks for the reply.
The only stripe we found in document was xseries sql server 2005, redbook article and it says to use 64kb stripe but for SAN, which we do NOT have. no mention of non-san
August 10, 2006 at 10:50 am
I've tried various configs for arrays and to be honest never been able to measure any difference. Tran logs and backups are best set with 64kb blocks and the data drives to 8kb ( the difference between sequential and random read writes ) However I understand sql reads in 64kb and writes in 8kb , it has been said at microsoft that it may read 128kb. Whichever you go it's a balance and the reality is that it probably makes little difference in operation. Remember in a good system most of your reads will come from cache so physical config is not an issue, the exceptions being tran logs and tempdb.
The only observation I would make is that by setting tempdb on less spindles than your data drive you are effectively setting tempdb as a bottleneck - I'd always want to have as many, if not more, spindles for tempdb than data.
btw stripes and blocks are different, you'll probably have less control over stripe.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 10, 2006 at 11:21 am
One additional point about blocksize. A blockize of 8 kb will make more efficient use of your existing disk reaources for storage than a 64 kb blocksize. This is because there is less 'wasted' space when writing files. An example would be in writing a 1 kb file. If your blocking is at 64 kb you would 'waste' 63 kb of disk resource as opposed to using 8 kb blocking you 'waste' is only 7 kb. Usually for DBMS file systems this is not a real concern because thes file systems are dedicated to databses and the low number of files that reside on them.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 10, 2006 at 11:45 am
The real concern would be how to dispose of the bodies of the miscreants putting 1kb files on my data drives.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply