topic Re: Best filesystem blocksize for Oracle/SAP on EMC in Operating System - HP-UX

Best filesystem blocksize for Oracle/SAP on EMC

Dan Hetzel — Fri, 05 Jan 2001 08:48:10 GMT

Hi all,

We'll have to install 2 brand new EMC 8430 to use in an Oracle/SAP enviromment.
They will be connected to the servers through SAN (Connectrix) with FC.

According to Oracle, blocksize should be 8192
According to SAP, filesystem blocksize should be less than or equal Oracle's blocksize.
According to Connectrix, blocksize should be 16 to 32K, for better performance.

Any comment on this ?

Thanks,

Dan

Re: Best filesystem blocksize for Oracle/SAP on EMC

Alexander M. Ermes — Fri, 05 Jan 2001 09:16:08 GMT

Hi Dan.
We use EMC equipment for Oracle Applications here. We have set the blocksize to 8192 bytes.
If you go for larger blocks, you may have some more waste and loss of disk space.
The performance is satisfying, because the EMC transfers the data mostly from the builtin hardware cache. We have a ratio of 90 % read / 10 % write on our machine. I guess, you have FC interfaces for the EMC.
Rgds
Alexander M. Ermes

Re: Best filesystem blocksize for Oracle/SAP on EMC

Dan Hetzel — Fri, 05 Jan 2001 09:30:38 GMT

Hi Alexander,

That's what I would go for if I was facing a "simple" EMC with FC connectivity.
What I'm concerned about is the SAN (Connectrix) in between.

Oracle/SAP agree on the 8k blocks but the local Connectrix engineer keeps on telling that 32K blocks would achieve far better transfer rates.

As SAN is a fairly recent technology, I don't have any previous experience to rely on :-(

Best regards,

Dan

Re: Best filesystem blocksize for Oracle/SAP on EMC

Steven Sim Kok Leong — Fri, 05 Jan 2001 10:22:16 GMT

Hi,

I am having SAP/Oracle + XP256. We are using the Oracle db_block_size of 8 kb and an filesystem block size of 1 kb using JFS. 1 kb is the default filesystem block size with JFS. We also implemented RAID 0+1 with hardware RAID 1 in the XP256 and software striping at the OS level.

Based on stkio benchmarks, we have observed marked improvements in random reads/writes as well as sequential reads/writes. At the Oracle level, tablespace creation is much faster than when it was on FW-SCSI bus and disks.

Creation of tablespace and other database I/O operations using a filesystem block size of 1 kb are very much faster than using a filesystem block size of 8 kb in our environment.

It again depends on your database environment such as tuple sizes etc.

Hope this helps. Regards.

Steven Sim
Brainbench MVP for Unix Admin
http://www.brainbench.com

Re: Best filesystem blocksize for Oracle/SAP on EMC

Darrel Louis — Fri, 05 Jan 2001 11:31:46 GMT

I have a Oracle document(A64526-01 Administrator reference for hp9000)
This is for Oracle 8.0.5, check if you can find it .
Some statements:
# For databases stored in the UNIX filesystem, use the same size for Oracle blocks as the file system blocks.
# On HP-UX, the default Oracle Block size(db_block_size) is 2Kb and the Maximum block size is 16Kb. You can set the actual block size to any multiple of 2Kb up to 16 Kb, inclusive.

I would check with Oracle what the performance degradation would be.
When increasing file-system block size to 32 Kb and if it is reccommended.

Two good books to look for:
- storage area networks: designing and implementing a mass storage system
ISBN 0-13-027959-5

- Building Storage Networks by Marc Farley
ISBN: 0-07-212050-9

A nice url I came accross the other day.
http://www.fibrechannel-europe.com/

http://www.emc.com/partnersalliances/pdfs/scaling.pdf

http://www.emc.com/partnersalliances/pdfs/oracle_tuning_wp.pdf

Have Fun

Darrel

Re: Best filesystem blocksize for Oracle/SAP on EMC

CHRIS_ANORUO — Fri, 05 Jan 2001 11:46:07 GMT

Hi Dan,

If you are using 11.0 it is recommended that you use 8192 blocksize default. See attached document:

Re: Best filesystem blocksize for Oracle/SAP on EMC

Dan Hetzel — Fri, 05 Jan 2001 12:23:08 GMT

Darrel,

Thanks for the book refs and the links.

Chris,

Thanks for your document. Unfortunately it's about oracle 32 bits. We'll be running 64 bits, I should have been giving a more accurate description... :-(

Dan

Re: Best filesystem blocksize for Oracle/SAP on EMC

Tim Malnati — Fri, 05 Jan 2001 14:58:02 GMT

Your EMC engineer told you 32K because all block transfers within the EMC use the 32K block size. If you are striping you should definitely use 32K as you stripe segment size.

Now with this said, 32K is probably not the best block size to configure into the file system. The two biggest things to consider is the size of the data within the block that regularly get updated and the optimum block size that the database engine/application can deal with internally. Parsing a 32K block takes a lot more work than smaller sizes and writing back a full 32K block is very taxing if your app has only changed 2K of the actual data. Your dealing with a balancing act here and is usually best configured slightly larger than the typical record size used within the database files.