Operating System - HP-UX
1748227 Members
4175 Online
108759 Solutions
New Discussion юеВ

Re: Selecting Oracle db_block_size with VA7110 array

 
Joanne Crawley
Occasional Contributor

Selecting Oracle db_block_size with VA7110 array

We have an opportunity to rebuild a database which currently has db_block_size set to 2k and we are wondering if we should increase the db_block_size. All sources seem to agree that a 2k block size is too small and 4k to 8k is more appropriate for an OLTP application.
The database is to be built on an HP VA7110 array, set to raid level 1+0, with a 2Gb read/write cache. The array contains 14x70Gb disks and the usable disk capacity has been configured into one large LUN of 400Gb.
This LUN has a vxfs filesystem created on it which has block size of 8k (fstyp -v gives f_frsize=8192 on this filesystem).
All sources seem to agree that db_block_size should be a multiple of the os block size, i.e. 8k in this case.
As I understand it, the goal is to thereby reduce the disk head movements required to fetch a block of oracle data and thereby increase performance. My question is, since the disks are effectively hidden behind a large cache, will we actually get any performance benefits by increasing db_block_size from 2k to perhaps 4k or 8k, and should we match the size db_block_size to the filesystem block size of 8k.

15 REPLIES 15
Steven E. Protter
Exalted Contributor

Re: Selecting Oracle db_block_size with VA7110 array

Obviously you can redo your filesystem any way you want. The missing piece of information is will your database actually benefit from the change.

You need to run Oracle analysis(stats pack) on your database and look at the structure. It is quite possible that your database might benefit from the change, especially if you have large records.

It is equally possible without looking at the database that the change will do you no good.

Because the array is probably RAID 5 behind the LUN, I think the best idea you have would be settinig it as Raid 1/0. That really helps Oracle out.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Ken Hubnik_2
Honored Contributor

Re: Selecting Oracle db_block_size with VA7110 array

I had a Oracle DBA tell me once that Oracle recomended an 8k block size and the the filesytem block size should match so they would also align on the boundaries.
Massimo Bianchi
Honored Contributor

Re: Selecting Oracle db_block_size with VA7110 array

Hi,
best performance is gained when all matches,

oracle block size = file system block size.

In this case 8K is optimal.

When all matches, you reduce disk movement, and also every I/O is fully used.

If you use a oracle block size of 2K, and a fs block size of 8k, you waste 6k every time... no good !

In my experience a well tune and matching block size will give you a 20% difference.

Massimo


Jean-Luc Oudart
Honored Contributor

Re: Selecting Oracle db_block_size with VA7110 array

Having same type of subject a while ago.
We went for db_block_size 8K and storage we have XP128 with 8X70Gb disk (RAID0+1). On top of that we created a single large volume group with disk stripping (stripe size 64Kb).
We are happy with the performance of the IO subsystem
You may find that tuning the Application and the Oracle database may improve a lot the performance.
Tuning the "cursor" parameters improved the time spent by Oracle in the transactions.
Loading the data in a certain way (it means you know the application behaviour) improved the performance too.

Jean-Luc
fiat lux
Joanne Crawley
Occasional Contributor

Re: Selecting Oracle db_block_size with VA7110 array

Thanks for all the above posts especially Jean-Luc.

We are loth to increase the db_block_size of the database without a good reason, basically because doubling db_block_size means that we have to halve db_block_buffers for the sga to fit into the same amount of memory. This could potentially have a detrimental effect on access of randomly located data.

Does anyone have any specific information or experience on the interaction between Oracle db_block_size and filesystem block size when the physical disks are, as in this case, 'hidden' behind the read/write cache of the VA7110. The cache page size of the array is 64k and the striping segment size of its disks is 256k (it seems that these parameters cannot be altered).

Alternatively, what metrics could we use to decide if increasing the db_block_size will improve performance.
Eugeny Brychkov
Honored Contributor

Re: Selecting Oracle db_block_size with VA7110 array

32K block size is optimal for VA disk array
Eugeny
Jean-Luc Oudart
Honored Contributor

Re: Selecting Oracle db_block_size with VA7110 array

Ok, saying that 2Kb db_block_size is not common these days.
You may want to re-think the database block size for the long term.
If you can run 2 databases with (2 different block size) and run statspack you may find (or not) differences. Need to be tested.

In the mean time have a look at the attached document

Rgds,
Jean-Luc
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Selecting Oracle db_block_size with VA7110 array

Also this document

Rgds,
Jean-Luc
fiat lux
Joanne Crawley
Occasional Contributor

Re: Selecting Oracle db_block_size with VA7110 array

Jean-Luc, thanks again.

We have already read the first document that you attached - Optimal Storage Configuration Made Easy and we have been converted and are setting up the database with SAME methodology.

I could not access your second attachment, but I'm guessing that it was Auto-SAME (HP/Oracle White Paper), which dicusses the kind of database build on the VA7110 we are going to do. Unfortunately, it does not go into a discussion of db_block_size - the database configuration used for tests appeared to have a mixture of 2Kb and 16kb block size and was using raw devices, hence filesystem block size is not an issue.

Because we are building the database from scratch, it seems worthwhile building it with the 'right' db_block_size for this hardware configuration. I am still hoping that someone out there has set of cookbook guidelines to use (not including the trivial case stuff).

In the meantime we will build the database with differing db_block_sizes and test the application and see what happens. The only problem I can forsee this approach is that it is easy to replicate the long batch jobs, which should run faster with larger db_block_sizes. The difficulty comes with replicating the random data access created by user transactions - and this is the very area favoured by our current small db_block_size.