Re: On AIX, blocksize an DataWarehouses

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 22 2006 - 14:49:31 CST