Selecting Oracle db_block_size with VA7110 array

[Joanne Crawley]

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.

[Steven E. Protter]

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

[Ken Hubnik_2]

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]

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]

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

[Joanne Crawley]

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]

32K block size is optimal for VA disk array
Eugeny

[Jean-Luc Oudart]

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

[Jean-Luc Oudart]

Also this document

Rgds,
Jean-Luc

[Joanne Crawley]

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.

[twang]

Guidelines for DB_BLOCK_SIZE:
=============================

The setting of the parameter DB_BLOCK_SIZE depends on the type of data you are
looking at, the application and implementation.

The main considerations are as follows:

1. Balance with Operating System Block Size

For good performance, Oracle Block size should be made equal to or a
multiple of the operating system blocksize. If you do not do this then the
operating system may be doing many reads and writes to process Oracle
blocks. This is inefficient and wastes CPU cycles.

If your rows are small and you use a large blocksize, when you fetch a block
you may get lots of rows that you are (probably) not interested in. The
operating system has to do more work to get the row(s) that you are
interested in.

On the other hand, if you were interested in the extra rows then altough
this may waste CPU on the initial fetch then you have already loaded the
block containing the rows in to the buffer cache.

Larger blocks also mean more rows cached for the same number of
DB_BLOCK_BUFFERS (taking up more memory).


2. Index Branches

Larger Oracle block sizes may give better index balancing with more rows in
each branch.


3. Locking/Block Contention

Remember that with more processes looking at the same block you are more
likely to get block contention.


4. Row Length

If your rows are comparatively large then you may need a large blocksize to
(possibly) prevent chaining.

[Yogeeraj_1]

hi,

allow me post my findings:

Here are a couple of pros and cons (some of them will be both pros and cons -- depends on your perspective)....

going from 2k to Xk where X > 2...

o the amount of block overhead in your database will decrease. There is a fixed amount of overhead per block. If you have a block that is 4 times LARGER you will have less then 1/4 the block overhead after you change over.

o you will have less row chaining and perhaps less row migrations. Row chaining happens when a row is too large to fit on one contigous block -- this is much less likely in an Xk block then a 2k block. Row migration happens when we attempt to update a row but discover there is insufficient space on the block to do so. We leave the "head" of the row on the original block (cannot change the rowid) and put the row itself on another block entirely. Going from 2k with a 10% pctfree (default) to an Xk block with 10% free means we have perhaps more free space on each block to accomidate this (but we also have more rows per block so it might not matter all of the time).

o you will have more people contending for the same block perhaps. We do lots of stuff at the block level -- you may notice more contention in this area.

o you will have the same amount of data cached (volume) but the granularity of the data will be N times larger. For every row you want, you might get N times as many rows pulled along. This can be good -- this can be bad. It really depends on what you are doing. In a heavy duty OLTP system with lots and lots of scattered reads all over the place -- this might be bad as no one is going after the same blocks (eg: I read a row on a block and no one else ever needs that block -- i cached lots of stuff no one is going to use and I may have bumped out some other data that we could have used). In a query intensive system (DSS, Data Warehouse), it may be very good as I was going to read the blocks right around my data anyway. And - people are going to be needing that data again and again.

o you'll get more rows / block in indexes. This will result in indexes that require less maintenance as they tend to need to reorganize themselves less frequently.

o larger block sizes allow for larger index keys (although whether or not THIS is a good thing is in question as well, there is something to be said for reasonably sized index entries). The size of the index keys is a little less then about 40% of the block size. In an 8k database for example, you'll get:create index t_idx on t(x,y,z)
*
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

Ok a 2k block -- it would be less.




Also -- try to find the native OS blocksize of your system and make sure to use a block size that is at least as large as this AND is a multiple of this if possible. Some of the old considerations (eg: pre 7.3 -- you had to consider the blocksize would limit the maxextents of a table) do not apply.



hope this helps too!

regards
Yogeeraj

[Hein van den Heuvel]

re: 2KB blocks mean 6KB of an IO is wasted
... Depends on the file system. NOT on Raw devices.

re: 32KB is optimal for a VA storage subsystem
... So what? is it optimal for the application?
While 2KB is probably sub-optimal as pointed out (too much overhead, row fragmentation), I believe 32KB is unlikely to be optimal for anything but Data Warehouse style usage.
- While each 32KB comes in over the wire in 'no time' at all these days, once you start hitting
1000 IO/second the bandwidth requirements start to play a role.
- Contention risk as pointed out earlier.
- cache granularity! if you have a limited SGA, say 4GB, that maps to 131K buffers, but at 8KB it give you half a million distinct pages to cache. What is the access pattern of your application? Lots of little blobs all over (OLTP) or large chunks of rows probably placed close together (BW).

With a 'middle of the road' 8Kb or 16KB you
are more likely to be in the right ballpark.

And don't forget, Oracle now allows you to mix and match multiple block sizes in a single DB!

Good luck,
Hein.

[Jean-Luc Oudart]

Joanne,

I don't what happen with the second document.
"SAME and the HP XP512".

I don't think that the VA configuration should drive the choice of your DB_BLOCK_SIZE but your application should.

You mentioned, and this is right, you can replicate long batch run, but not the online users transactions unless you use the specific tools to simulate this kind of workload.

We had to do this exercise recently to statisfy ourselvers with the performance of the (future) database for both Bach and Online.
We went to a benchmarking center with transactions and database and run different simulations.
Of course, benchmarking does not come cheap.

I attached another document on VA performance , that you may already have.

Regards,
Jean-Luc
PS : let us know how you get on with your performance tests.

[Jean-Luc Oudart]

Sorry,
I don't know what is happenning with the attachment. I use Acrobat 5.0 here with no pb.
Any pb during upload ???

The document is HP :
"disk array performance guidelines with application to the hp StorageWorks
virtual array"

Rgds,
Jean-Luc

[Alexander M. Ermes]

Hi there.
Just my 2ct to this.
We are running a database block size of 8k on 95 percent of our databases.
Less requests to much reading, more is wasting space.
As storage we use :

Jamaica box
FC30 Galaxy
SC10
FC10
EMC 8530

On all systems we have satifying results in performance.

Rgds
Alexander M. Ermes