topic Re: oracle and filesystem block size in Operating System - HP-UX

oracle and filesystem block size

Aashish Raj — Mon, 10 Jun 2002 02:09:29 GMT

hi,

Oracle says that oracle block size should be multiple of OS block size but different filesystems can be created with different filesystem block sizes.How oracle takes care of that.

What about the block size of raw volume.

Thanks
AR

Re: oracle and filesystem block size

Steven Sim Kok Leong — Mon, 10 Jun 2002 03:02:44 GMT

Hi,

You take care of the Oracle block size by specifying the value for db_block_size and db_file_multiblock_read_count in your Oracle init.ora.

Both must be a multiple of the OS block size for efficient file I/O operations on the database files. The setting of db_file_multiblock_read_count will depend on your how your records are structured.

You can find out an existing OS filesystem block size by typing:

# fstyp -v /dev/vg00/lvol6

To create an OS filesystem of a specific block size, use newfs -b option e.g. for 16384 bytes:

# newfs -b 16384 /dev/vg00/rlvol6

For raw volumes (raw logical volumes) used for the database, I would think that the read is related by the size of the logical extents (not sure on this part). NOTE that raw volumes are VERY difficult to administer and manage at the OS level.

If you really want to improve I/O performance for your Oracle database, you should think about striping (RAID 0) and striping with parity (the ideal is RAID 5 among the other striping with party RAIDs such as 3 and 4).

To stripe your logical volume, use lvcreate with the -i option. man lvcreate for more information.

Hope this helps. Regards.

Steven Sim Kok Leong

Re: oracle and filesystem block size

Aashish Raj — Mon, 10 Jun 2002 04:29:53 GMT

Thanks Steven !

What i mean to ask is ...lets say oracle block size is 16k and i create a new filesystem with block size of say 8k or 4k, then how oracle is going to treat datafiles added to this FS.
According to oracle, oracle block size should be multiple of OS block size so
oracle block size >=OS block size

Will oracle allow me to create a datafiles on this filesystem(i am talking only for oracle 8i where oracle block size is same for all datafiles)

Thanks
AR

Re: oracle and filesystem block size

Steven Sim Kok Leong — Mon, 10 Jun 2002 05:10:57 GMT

Hi,

Why not? Oracle will be more than happy to create the datafiles for you on the filesystem (regardless of OS block size).

During our I/O performance comparison between HFS, VxFS and raw volumes for Oracle database performance using just tablespace creation duration, we compared the performance with varying Oracle block sizes and OS filesystem block sizes for HFS and VxFS. We were able to create the datafiles.

It matters only for the I/O performance where Oracle writes in db_block_size bytes of data in each block and reads in db_file_multiblock_read_count.

Hope I get your query right and hope this helps. Regards.

Steven Sim Kok Leong

Re: oracle and filesystem block size

zhaogui — Mon, 10 Jun 2002 07:18:36 GMT

How come I cannot create a file system with 16384 block size? Here is the error message,
#newfs -F vxfs -b 16384 /dev/vg00/rlvtest
vxfs mkfs: bsize must be a power of 2 >= 1024 and <= 8192

Another thing I noticed that if I use default block size, f_bsize: is also 8k, but f_frsize is 1024. If I use 8k blk size, then both f_bsize and f_frsize change to 8k. What's f_frsize in fstyp output?

Re: oracle and filesystem block size

Steven Sim Kok Leong — Mon, 10 Jun 2002 07:39:50 GMT

Hi,

The maximum block size for HFS is 64 kb while the maximum block size for VxFS is only 8 kb.

The fragment size represents the smallest amount of disk space to be allocated to a file. It must be a power of two no smaller than DEV_BSIZE and no smaller than one-eighth of the file system block size.

Thus, if you have a lot of very small files, each of them less than 1 kb in size, then by reducing the fragment size, you save filesystem space because instead of occupying 8 kb for each file, only 1 kb is used for each file.

Hope this helps. Regards.

Steven Sim Kok Leong

Re: oracle and filesystem block size

zhaogui — Mon, 10 Jun 2002 08:47:42 GMT

So it seems "-b 4096" in newfs will only change fragment size, it will never change block size, then how to change block size?

Re: oracle and filesystem block size

Steven Sim Kok Leong — Mon, 10 Jun 2002 09:01:33 GMT

Hi,

Changing of fragment size independent of block size is available for HFS. man newfs_hfs for more information.

-b block size
-f fragment size

For VxFS, you can defragment your filesystem unlike HFS.

Hope this helps. Regards.

Steven Sim Kok Leong

Re: oracle and filesystem block size

Pete Randall — Mon, 10 Jun 2002 10:25:37 GMT

AR,

Refer to the following thread:
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x96b70bce6f33d6118fff0090279cd0f9,00.html

It may not seem to apply at first, but keep reading, I'm sure you'll discover its pertinence.

Pete

Re: oracle and filesystem block size

zhaogui — Tue, 11 Jun 2002 01:21:28 GMT

If f_frsize is fragment size, then how about f_bsize? Is block size the same as f_frsize? Where can I get the description of each item in the output of "fstyp -v "?
Here is what I got,
#newfs -F vxfs -b 4096 /dev/vg00/rlvtest
version 3 layout
106496 sectors, 26624 blocks of size 4096, log size 512 blocks
unlimited inodes, 26624 data blocks, 26080 free data blocks
1 allocation units of 32768 blocks, 32768 data blocks
last allocation unit has 26624 data blocks
first allocation unit starts at block 0
overhead per allocation unit is 0 blocks

#fstyp -v /dev/vg00/lvtest
vxfs
version: 3
f_bsize: 8192
f_frsize: 4096
f_blocks: 26624
f_bfree: 26075
f_bavail: 25668
f_files: 6544
f_ffree: 6512
f_favail: 6512
f_fsid: 1073741841
f_basetype: vxfs
f_namemax: 254
f_magic: a501fcf5
f_featurebits: 0
f_flag: 0
f_fsindex: 6
f_size: 26624

You see, both block size and f_frsize seems have been set to 4096 but f_bsize is still 8192, what's f_bsize?

#newfs -F vxfs -b 8192 /dev/vg00/rlvtest
version 3 layout
106496 sectors, 13312 blocks of size 8192, log size 256 blocks
unlimited inodes, 13312 data blocks, 13024 free data blocks
1 allocation units of 32768 blocks, 32768 data blocks
last allocation unit has 13312 data blocks
first allocation unit starts at block 0
overhead per allocation unit is 0 blocks

#fstyp -v /dev/vg00/lvtest
vxfs
version: 3
f_bsize: 8192
f_frsize: 8192
f_blocks: 13312
f_bfree: 13027
f_bavail: 12926
f_files: 3264
f_ffree: 3232
f_favail: 3232
f_fsid: 1073741841
f_basetype: vxfs
f_namemax: 254
f_magic: a501fcf5
f_featurebits: 0
f_flag: 0
f_fsindex: 6
f_size: 13312

Re: oracle and filesystem block size

Steven Sim Kok Leong — Tue, 11 Jun 2002 01:38:43 GMT

Hi,

Interesting findings.

f_frsize is the block size of the filesystem and f_bsize is the preferred filesystem block size (which is 8192 bytes for vxfs). Thus, the preferred filesystem block size f_bsize will always be fixed for a filesystem. On the other hand, f_frsize is the configured filesystem block size and changes according to the options to newfs.

man 2 statvfs.

ulong f_bsize; /* preferred file system block size */
ulong f_frsize; /* fundamental file system block size */

You are right. The fragment size is not identified in fstyp. This is interesting.

Hope this helps. Regards.

Steven Sim Kok Leong

Re: oracle and filesystem block size

Steven Sim Kok Leong — Tue, 11 Jun 2002 01:46:18 GMT

Hi,

As indicated in previous responses, note that setting the fragment size is only applicable in HFS.

HFS differentiates between fragment size and block size. man newfs_hfs shows the option to set the fragment size whereas man newfs_vxfs does not.

Hope this helps. Regards.

Steven Sim Kok Leong

Re: oracle and filesystem block size

Bill Thorsteinson — Tue, 11 Jun 2002 13:14:07 GMT

It seems this question generated a lot of issues.
I generally don't consider it
much of an issue if you are using powers of 2 for all the
values. A block size of 4k
and multiblock read count of 8
give you a read size of 32k. These are generally minumal values. Increase either value by a factor of 2 and you get optimal read values up to 64k block sizes on disk.

By default your minumum extent
size is a multiple of 5 so
all this tuning goes to waste.
Be sure to set the value
for minumum extent size on
all you tablespaces to a
value that is a power of 2.
I use the value of my default
initial extent

Re: oracle and filesystem block size

PAUL CHEN_2 — Mon, 28 Jul 2003 14:47:48 GMT

I would like to know when I create a database with db_block_size 8K, should I create the file system block size and frament size with 8k to get better performance?