Re: Veritas Quickio and DB_BLOCK

From: Gaja Krishna Vaidyanatha <oraperfman_at_yahoo.com>
Date: Thu, 04 Oct 2001 12:53:58 -0700
Message-ID: <F001.003A25C0.20011004123022@fatcity.com>

Hi Satar & list,

To add to the issues and concerns that Jonathan has already so eloquently outlined, let me add a key factor that needs to be considered.

I/O tuning fundamentals require us to ensure that the filesystem blocksize = db_block_size. The default filesystem blocks size in Veritas is 1K and it is more than likely that almost every Veritas filesystem that is out there is in fact created with an 1K block size. This is true even though we are talking about Quick I/O which works on a Veritas-simulated raw device.

At any rate, creating an database (regardless whether it is OLTP or otherwise) with a db_block_size of anything other than the filesystem block size is a receipe for chronic I/O bottlenecks.

For example, if db_block_size were 2K and the filesystem block size were 8K, every 2K worth of I/O requested by Oracle, will actually result in the I/O sub-system performing 8K worth of I/O (essentially your system will be doing 4 times more I/O than what is required).

On the flipside, if you have db_block_size at 8K and the filesystem block size at 2K, you end up with a scenario, where every 8K I/O request by Oracle will cause 4 filesystem blocks to be read. When applications perform enough "single-block" index range-scans, it will cause the read-ahead algorithm to accidentally engage, resulting in 128K or even 256K worth pre-fetches, which results in "wasted I/O capacity". From a "real life" perspective, you will rarely find "true OLTP" systems these days. Most systems out there are hybrid in nature, OLTP during the day and DSS at night. And that is a huge factor to consider.

There have been many studies that have been done to measure and benchmark query performance, load time, index creation and transaction rate for various values of db_block_size. In every published study, it has been proved beyond a shred of doubt that each increase of db_block_size provides approximately 40% increase in performance (especially in query execution and large loads, which are significant for the batch window). Further, the height of an index is another "significant factor" in I/O performance during index scans, and it is dependent on db_block_size. The smaller the value of db_block_size, the larger is the "number of read system calls" Oracle will have to perform.

Bottom line, I personally will never create a database with a db_block_size less than 8K, regardless of whether we use Veritas or not, given the "hybrid" nature of most databases today. More importantly, the values of filesystem blocksize and db_block_size needs to be kept in strict equality. Failure to do so will result in severe I/O performance problems "by design".

While the 8K block size minimum is good for most hybrid systems of today, applications that are purely DSS, the recommendation of picking the largest possible db_block_size on that platform still holds good. When it doubt, go with the larger block size, as it is much easier to deal with block-level and row-level contention by proactively configuring INITRANS, MAXTRANS, PCTFREE and PCTUSED. Contention for the cache buffer chain and cache buffer lru latches can be dealt with by setting the relevant init.ora parameters.

In closing, I want to use the "car anology" in this thread - it is not relevant whether you recommend a Honda or a Benz(regardless of one's personal definition of "sex on wheels") when someone asks for a car recommendation. What matters is that you recommend a car that does not have an inherent "design problem".

Hope that helps,

Gaja

satar naghshineh <satarnag_at_yahoo.com> wrote:
> Hi Jonathan,
>
> Sweeping statement...maybe. It all depends on your
> application. That's why I put an emphasis on his/her
> application (meaning both physical structure and
> data)
> requirements. As a GENERAL rule of thumb, I
> (personally) suggest (if possible) 2k for OLTP
> databases. It's like if you ask me what car to buy,
> I
> would say a "Honda". But if you were to tell me that
> you need speed, style and sex on wheels, I would
> suggest a Benz.
>
> You and I, (especially you!) can argue the
> benefits/disadvantages of using 2k block
> size...Which
> I don't care to do. I've put the benefits/reasons of
> a
> 2k blocksize at the bottom of this reply just to
> show
> some reverse examples of the ones you replied with.
> Some of the reasons should look familiar to you! ;)
>
> Regards,
> Satar
>
> 1. The OLTP app has lots of small rows (<2k)
>
> 2. The OLTP app does massive scattered reads (you
> don't full scan in OLTP). It
> reads a block here, a block there based on some
> primary key (after doing lots of
> scattered reads to walk an index structure)
>
> 3. The OLTP app has 1,000 connected users -- each
> wants their current row(s) they
> are using in the cache.
>
> 4. A smaller block size in a database that does lots
> of scattered IO's (eg: an OLTP system) and random
> keyed reads can definitely benefit from smaller
> blocksizes.
>
> 5. The indexes for the critical access paths fit
> exactly into an N-layer tree and allow an optimum
> buffering strategy when memory is limited.
>
> 6. In almost all cases the data for a single index
> value will be (significantly) less than 2K and will
> all be found in the same block.
>
> 7. Memory is at a premium.
>
> --- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
> wrote:
> >
> > That's a fairly sweeping statement to make without
> > any justification - after all, at 2K:
> >
> > The block header is a much larger percentage
> > of the block size - so you lose space.
> >
> > The probability of wasting space from the
> > PCTFREE
> > setting increases - so you lose space.
> >
> > The memory overhead due to x$bh is fixed per
> > block,
> > so you use more memory to hold the same volume
> > (block size x block count) of data.
> >
> > Index depth may increase through having fewer
> > entries per branch block.
> >
> > Latch activity is likely to increase on range
> > scans
> > because leaf blocks hold fewer entries - you
> get
> > the same effect on tables with clustered data
> -
> > and the effectiveness of IOTs will
> particularly
> > be
> > reduced.
> >
> >
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Host to The Co-Operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> > Author of:
> > Practical Oracle 8i: Building Efficient Databases
> >
> > -----Original Message-----
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > Date: 02 October 2001 21:56
> >
> >
> > |If your application allows it, and if the
> > Application
> > |will not change in the future, then use a 2k
> block
> > |size for OLTP database.
> > |
> > |If you are not sure on the application needs,
> then
> > |stick with 4k to be safe.
> > |
>
>
> __________________________________________________
> Do You Yahoo!?
> Listen to your Yahoo! Mail messages from any phone.
> http://phone.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: satar naghshineh
> INET: satarnag_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).

Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml

Do You Yahoo!?
NEW from Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gaja Krishna Vaidyanatha
  INET: oraperfman_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Oct 04 2001 - 14:53:58 CDT

Re: Veritas Quickio and DB_BLOCK_SIZE