File Allocation Unit Size for SQL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

we have the scenario with all 3 situations raid 1, raid 5, and 10. let's talk about Raid 10 with stripe size = 32KB;
1)let's say we match the File Allocation Unit Size with the stripe size, but this will always involve 2 IOs for SQL.
2)let's say we double the File Allocation Unit Size for the stripe size (to make one SQL IO for each allocation), then it will be 2 IOs to get and to give to the RAID.

In both situations, there are 2 IOs involved, but differently. Which one is more costly?

for RAID 5 , did you mean 32K Stripe for 3 disks and 64/3 is not a whole number for 4 disks?

thanks

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Member_2_231077

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

dlethe
so, when file allocation unit size is 64KB on RAID10(With stripe size of 32KB), it does 1/2 to 1 IO; how about file allocation unit size of 32KB on RAID10 with stripe size of 32KB?

ASKER

andyalder
are you meaning stripe size of 64K is good for sequential I/Os and not for randon I/Os? If so, what will be the best balance for stripe size for both sequential & random IOs?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

dlethe
thanks for clarifying that. I have seen SQL implemented on disks with file allocation unit size to 8KB, in the past!! (that would be 8 IOs)

can you point me to some reading material on the para you wrote :
"
With stripe size of 32KB on RAID10, each disk only does a single IO. Furthermore, more sophisticated RAID engines will balance read requests so the 2 disks that make up the RAID0 part of the RAID10 that are least busy will do the read, and the other 2 disks won't do anything, so you only have 1/2 of an I/O per disk."
I do not understand fully yet how 32K stripe still does it within 1 or less IO. Is this true for both reads and writes?

thanks

SOLUTION

Member_2_231077

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

thribhu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

David Todd

Hi,

For SQL DATA, it reads an extent - 64k.

For the log files I'd suggest the OS default for the allocation unit.

Cheers
David

ASKER

Thanks for your inputs. The server I have in mind has 2 HBA ports, each with 8 disks for RAID 10 configuration. so how do we calculate the stripe width? Do I need to be concerned about it or only the stripe unit size.

SOLUTION

thribhu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Member_2_231077

You can't do that in the real world, both the controllers see all the disks and all the hosts. You can't split a RAID 10 array down the middle except in very specific circumstances.

Just configure the switch and/or HBA so that it doesn't see the other path. Easy.

thribhu

anushahanna,
I am sorry, What are you looking for, i feel the question is deviating with diff answers given......

ASKER

thribhu, do you mean stripe unit size as element size?
in my case, it will be 32K. so my stripe size/width will be 128K for each RAID group.

so back the original question, how superior is the config
"
Stripe Unit Size of SAN Raid array = 32K
Stripe Width of SAN Raid array = 128K
File Allocation Unit Size = 64K
" and better than when the FAUZ is 32K.?

is this an optimal setup or what can be improved?

ASKER

andyalder, you said in some specific circumstances, you can split a RAID 10 array down. can you please explain what those circumstances will be?

dlethe, I like your idea of making the pair disk from the other port. can you pl explain the part where you said 'it doesn't see the other path'

thanks much!

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.