ORACLE BLOCK SIZE, LVM STRIPE SIZE AND RAID STRIPE SIZE

[Rod White]

Hi

If I create a filesystem with a block size of 8192 using "mkfs -F vxfs -o inode=unlimited,bsize=8192" for example, then I create an Oracle Database with block size of 8192 (recommended as each database block is one filesystem block), then should the RAID 0 LV have been create with a stripe size of 8192 or the default value of 16K, or the hardware controller default stripe size of 64K ?

That is, when the RAID 0 LV is striped over hardware LUNS (defined using a 4SI RAID controller) should the "stripe size" of the hardware Logical drive be the same as the LV stripe size ?

I ask this because the default stripe size in SAM for the LV is 16k while the HP A5856A RAID 4Si PCI 4-Channel Ultra2 SCSI Controller
Installation and Administration Guide recommends a stripe size of 64K ?

I was wondering what the effect would be if all of these, the DB block size, the LV stripe size and the hardware stripe size were all the same, or should the stripe sizes be the same and be a multiple of the filesystem block sizes ?

What I'm doing is configuring multiple mirror LUNS using the raid controller (irm) and then striping over these using LVM then putting a 8192 block size filesystem on that, then putting database datafiles with a block size of 8192 bytes.

Rod

[RAC_1]

Matching oracle block size with FS block size is OK. But stripping the lvol and stripping the on storage is not a good idea. If you are stripping on RAID, stripping should be ased on what you are going to put on RAID. If just oracle, then matching it with 8192 may help.

You certainly should not go for LV stripping and RAID stripping, as it is double work that you are doing. Also hardware stripping is faster than software stripping(LVM)

[Vladimir Fabecic]

I agree with RAC.
Golden rule: Let the hardware do RAID operation (RAID controller) and let CPU do application stuff!
When I have hardware RAID I never do "software RAID operations" and in that case have best application performance, expecially with databases. I would even avoid LVM if I could.

[Eric Antunes]

Hi,

No Stripes are need for Oracle:

"
--- Logical volumes ---
LV Name /dev/vg01/vol1
VG Name /dev/vg01
LV Permission read/write
LV Status available/syncd
Mirror copies 1
Consistency Recovery MWC
Schedule parallel
LV Size (Mbytes) 30016
Current LE 469
Allocated PE 938
Stripes 0
Stripe Size (Kbytes) 0
Bad block on
Allocation PVG-strict/distributed
IO Timeout (Seconds) default
...

Best Regards,

Eric Antunes

[Hein van den Heuvel]

The software (LVM) striping may be useful to spread IO over multple spindles and even over mutlipel controllers.
But if there is just 1 controller, and it already can stripe, then I see no point in adding a software stripe layer.

fwiw,
Hein.

[Patrice Le Guyader]

Hi Rod,

The parameter db_file_multiblock_read_count is also significant because the database is using it in the optimizer to make the execution plan of the query (Full scan or not) and so the number of disks I/O. If you have put the db_block_size at 8192 bytes and this parameter is at 8, your max I/O will be of 64Ko, your hardware default strip size. Unfortunatly it seems that stripping is not recommended by oracle except on datafiles with some recommandations.

I attached a note from oracle about RAID usage. Perhaps some experts would have some advices about it and on usage of db_block_size/db_file_multiblock_read_count ?

Hope this helps
Kenavo
Pat

[Eric Antunes]

Hi again,

About db_file_multiblock_read_count it depends of the I/O default block size (usually 512K). So, for the default I/O size and a db_block_size equal to 8192 (8K), I would recommend db_file_multiblock_read_count equal to 64 (512K/8K=64). Or 32 at least...

Best Regards,

Eric Antunes

[A. Clay Stephenson]

Despite what you have been told in your Oracle classes, the filesystem block size for vxfs filesystem has nothing to do with performance. Vxfs (unlike hfs or ufs) filesystems are extent-based rather than block-based so that the i/o operations are always attempted to be done in large chunks. Blocks, as applied to vxfs filesystems, serve one purpose. They define the minimum storage quanta for a single file. There is no performance penalty from leaving the block at the default 1K and you do improve the storage efficiency of the filesystem itself.

[Steven E. Protter]

Shalom Rod,

Based on experience with oracle and a question I asked on this toppic several years ago, your setup will have no impact on performance.

The most important area is the construction of the LUNS. If they are raid 5 then write perfomance will be impacted significantly.

SEP

[Eric Antunes]

Hi Clay,

If this will be an Oracle server and knowing that the standard block size for Oracle is 8K - one table row normally fits in one 8K block but it may need two for large tables - I don't see any reason for creating logical volumes with 1K size since Oracle will never send to the OS those kind of small writes.

About db_file_multiblock_read_count it specifies the maximum number of blocks read in one I/O operation during a sequential scan: the Oracle optimizer will choose to fetch the entire table if:

a) The table size is small and, because of that, there is no benefits in going via the indexes;

b) There are no valid indexing strategy for that query.

So db_file_multiblock_read_count should be 4 to 16 for OLTP environments and equal to ( OS Block Size / Oracle block size ) for Datawarehouse ones...

Best Regards,

Eric Antunes