Re: 8k block size upgrade concern

From: <yong321_at_yahoo.com>
Date: 29 Jun 2005 20:27:08 -0700
Message-ID: <1120102028.638899.293840@o13g2000cwo.googlegroups.com>

IANAL_VISTA wrote:

> What specific & verifiable evidence do you have that going from a 4K block
> size to an 8K block size will reduce the run time?

Bert Scalzo investigated the effect of db_block_size on performance running an OLTP Oracle database on Linux (see http://www.linuxjournal.com/article/5840). It's understandable that going from 2k to 4k increased performance by 138% (see DB4 in the article); using db_block_size smaller than filesystem block size is definitely a no-no. But it's interesting to see that changing db_block_size from 4k to 8k reduced the author's data load time by almost 32% (see DB6). Since Linux ext2 filesystem most likely has a 4k block size (I doubt he changed it), this benchmark seems to contradict the common belief that performance is the best when db_block_size matches file system I/O size; instead doubling it may improve performance, everything being equal.

Incidentally, I/O chunk size is better said to refer to filesystem block size, not memory page size because they may differ. See Fabrizio's message at
http://groups-beta.google.com/group/comp.databases.oracle.server/msg/69ff44b368a2c424 But as Noons says, they usually match.

Yong Huang Received on Wed Jun 29 2005 - 22:27:08 CDT