Re: On AIX, blocksize an DataWarehouses

Frank,

To address the filesystemio_options question, the best results we have seen on AIX 5L and 9i are jfs2 using CIO. The setall basically allows for the use of CIO:
metalink article: Note:272520.1
Configure FILESYSTEMIO_OPTIONS initialization parameter to configure a database to use either direct I/O or concurrent I/O when accessing datafiles, depending on the file system that is used to store them.

When you specify the value SETALL for this parameter: .Datafiles on a JFS file system are accessed using direct I/O .Datafiles on a JFS2 file system are accessed using concurrent I/O

> Interesting. No change on JFS2 blocksize (since it is only 4k)?
> Blocksize alignment between AIX and Oracle (32k - 32k) seems to be a
> good performance improvement (even recommended in some docs) in data
> warehouses.

No, I have never seen a recommendation for that. Can you point me to one? I would love to try to test that, I can see in theory how it may help.

> Max parallel servers is up to 256, seems too high then. I'll set it to
> 128 an see what happens.

How did that go? "IF" I use parallel options I generally let oracle decide how to divide up the parallel processes. I use these parameters and then just set the DOP on tables as needed. The parallel_adaptive_multi_user parameter allows oracle to back off parallelism if the box is being over taxed.

PARALLEL_AUTOMATIC_TUNING = TRUE
PARALLEL_ADAPTIVE_MULTI_USER =TRUE.
> Maybe a wide scope question, but where is the frontier between a bad
> system tunning and slow hardware. I mean, when should I say " the db is
> well tunned, any performance problem is due to the IO subsystem (fibre,
> SAN, SCSI or whatever)"?

Great question, here is my opinion:
Look at your IO response time for full table scans: select
(time_waited_micro/1000) / total_waits "AvgWait - ms" from v$system_event where event like '%scattered%';

5-10ms is good for SAN, if it is much higher you may want to look for hot files or look into your layout or how your san is configured.

Look at your CPU and memory utilization, are you using more than 70% CPU? are you swapping? Ask your Unix admin if the IO chanels are saturated or doing fine.

If you are seeing signs that the IO is providing a big enough bottleneck for concern, you have to approach it from the hardware side and look at the DB. Find your big FTS queries and see if they need tuning. See if there are some very hot files where you can spread out the load. Should your SGA be larger to hold more buffers? Our datawarehouse uses about 20 gigs of buffer cache.

Mike Fullerton

Frank R. wrote:
> On Nov 22, 12:09 am, "mfuller..._at_gmail.com" <mfuller..._at_gmail.com>
> wrote:
> > We use AIX 5.3 for our databases and IBM recommends CIO and JFS2. This
> > is better than direct IO.
> >
>
> Interesting. No change on JFS2 blocksize (since it is only 4k)?
> Blocksize alignment between AIX and Oracle (32k - 32k) seems to be a
> good performance improvement (even recommended in some docs) in data
> warehouses.
>
> > Using different block sizes does not effect IO, an IO request for 8
> > disk bocks ( 4 x 8k block size) is the same to the IO subsystem as a 8
> > disk blocks (1 x 32k block size). To reduce your IO response time you
> > need a fast IO subsystem and a good sized buffer cache. If you are
> > getting single row queries then reading 8k of blocks is better than 32k
> > of block reads but in a warehouse environment you will most likely be
> > getting many full tablescans. What you gain with bigger block sizes is
> > faster block processing. Each block has to be looked for in the
> > buffercache has table (requires a latch), then either it is found in
> > the buffer cache or disk. as oracle runs through all the buffers it
> > wants it will send batches of blocks needed on disk to the IO subsystem
> > and buffers in memory have to read the header, maybe do a current read
> > and so on. When dealing your situation you have to realize that the
> > total number of logical IO's consumes many resources and bigger blocks
> > reduces the amount of work that has to happen for each block. Physical
> > IO should be thought of as a missed LIO, and if your PIO is high you
> > either need to do one of two things: Tune the sql to touch less blocks
> > or increase the buffer cache to hold more blocks of data.
> >
> > You also have large waits on your parallel queries, you may need to
> > reduce the parallel queries so that you are not overloading the IO
> > subsystem with more than it can handle.
>
> Max parallel servers is up to 256, seems too high then. I'll set it to
> 128 an see what happens.
>
> > Making sure your files are
> > appropriately spread out and your raid system is fast enough to handle
> > your requirements will be the best for your IO, along with using
>
>
>
> Maybe a wide scope question, but where is the frontier between a bad
> system tunning and slow hardware. I mean, when should I say " the db is
> well tunned, any performance problem is due to the IO subsystem (fibre,
> SAN, SCSI or whatever)"?
>
> > CIO/jfs2 and filesystemio_options=setall. Using these options also
>
> I had just changed to it. But SETALL is supposed to be using both
> direct io and c io, isn't it?
> http://www.ixora.com.au/notes/filesystemio_options.htm
>
> > reduces the AIX buffer cache which gives you more memory to use for the
> > oracle buffer cache as well. You should really read the oracle
> > datawarehousing guide:http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/t...
> >
>
> Once again, thanks a lot.
>
> > >From this point your next step is to tune queries.
> > Frank R. wrote:
> > > On 21 nov, 17:29, DA Morgan <damor..._at_psoug.org> wrote:
> > > > Frank R. wrote:
> > > > > Hello everybody,
> >
> > > > > I'm currently involved in a Siebel Analytics/Datawarehouse deployment.
> > > > > The datawarehouse database (9.2.0.8 AIX 5.3) was originally created
> > > > > with a 8kb blocksize (standard) which as far as i've read anywhere or
> > > > > asked anyone, is quite small, reccommending 32kb instead.
> >
> > > > > Apart from a stripping strategy between several disks, blocksize seems
> > > > > critical in I/O improvement and I need advice on the relationship
> > > > > between oracle blocksize and AIX blocksize.http://www.dizwell.com/prod/node/58
> >
> > > Thanks for the link. Very interesting, really.
> >
> > > > > ATM, the OS blocksize is
> > > > > 4kb but, what would be the perfect size for it ¿32k too? ¿any oracle
> > > > > blocksize multiple?
> >
> > > > > Thanks in advance for your help.Based on what metric(s) are you concluding that block size has any
> > > > relationship to any performance issues you are trying to correct?
> > > > If you don't have numbers that support the conclusion you are most
> > > > likely going in the wrong direction.
> >
> > > Yeah, I know, i need "much" more testing, but:
> >
> > > Top 5 Timed Events
> > > ~~~~~~~~~~~~~~~~~~
> > > % Total
> > > Event Waits Time (s)
> > > Ela Time
> > > -------------------------------------------- ------------ -----------
> > > --------
> > > PX Deq Credit: send blkd 98,776 13,100
> > > 51.44
> > > db file sequential read 341,578 6,892
> > > 27.06
> > > PX Deq Credit: need buffer 53,359 3,447
> > > 13.53
> > > buffer busy waits 23,228 968
> > > 3.80
> > > log buffer space 1,012 333
> > > 1.31
> >
> > > Also, 600 ms datafile read access seemed bad so, right now the data is
> > > being redistributed between all disks
> >
> > > Moreover, I'm using async I/O, gonna try direct I/O (where
> > > db_block_size "doesn't matter") instead, and see the differences.
> >
> > > Thanks again.
> >
> > > > --
> > > > Daniel A. Morgan
> > > > University of Washington
> > > > damor..._at_x.washington.edu
> > > > (replace x with u to respond)
> > > > Puget Sound Oracle Users Groupwww.psoug.org
Received on Wed Nov 29 2006 - 16:33:50 CST