Asynch IO and Oracle

Asynch IO and Oracle

Post by Fidel » Thu, 07 Sep 2000 20:14:14



Hi,

I was wondering what a good value for asynchronous IO on AIX
would be. The default was 1 min 10 max (number of servers). I
read somewhere that on a 4 disk system values of 10 min 40 max
was recommended.

I have a 6 CPU S80 server with 8 GB of RAM and 8 drawers of
SSA disks (128 disks of 9 GB). I am running 16 Oracle databases
on it. Most are small but I have 4 large databases with 50
datafiles each. Each of the 4 large databases has 4 database
writer processes (dbw0 dbw1 dbw2 dbw3).

I set min to 100 and max to 1000 in 'smit aio'. 4*4*50 also
implies a value of that size. If I use 'ps -Af|grep kproc'
there are 1017 processes, so all AIO processes are started
and being used. However I am not seeing much performance
improvement from back when the maximum was set to 100.

Except that occasionally the system seemed to freeze, with
uptime and ps showing almost nothing running, while users were
complaining about slow queries. I contributed that to lack of
AIO processes and upped it to 1000.

What is a good value for those AIO parameters ? Or is 1000
excessive and hurting performance ? Should I use 4 database
writers for each database on a 6 CPU system with asynch IO,
or just one ?

Thanks,

Rob
--
Get tons of referrals - Sign up now ! http://www.refmatrix.com/
The Adsenger information resource - http://www.xs4all.nl/~rob/adsenger.html

 
 
 

Asynch IO and Oracle

Post by cyb.. » Thu, 07 Sep 2000 22:09:01




Quote:> Hi,

> I was wondering what a good value for asynchronous IO on AIX
> would be. The default was 1 min 10 max (number of servers). I
> read somewhere that on a 4 disk system values of 10 min 40 max
> was recommended.

> I have a 6 CPU S80 server with 8 GB of RAM and 8 drawers of
> SSA disks (128 disks of 9 GB). I am running 16 Oracle databases
> on it. Most are small but I have 4 large databases with 50
> datafiles each. Each of the 4 large databases has 4 database
> writer processes (dbw0 dbw1 dbw2 dbw3).

> I set min to 100 and max to 1000 in 'smit aio'. 4*4*50 also
> implies a value of that size. If I use 'ps -Af|grep kproc'
> there are 1017 processes, so all AIO processes are started
> and being used. However I am not seeing much performance
> improvement from back when the maximum was set to 100.

> Except that occasionally the system seemed to freeze, with
> uptime and ps showing almost nothing running, while users were
> complaining about slow queries. I contributed that to lack of
> AIO processes and upped it to 1000.

> What is a good value for those AIO parameters ? Or is 1000
> excessive and hurting performance ? Should I use 4 database
> writers for each database on a 6 CPU system with asynch IO,
> or just one ?

> Thanks,

> Rob
> --
> Get tons of referrals - Sign up now ! http://www.refmatrix.com/
> The Adsenger information resource -

http://www.xs4all.nl/~rob/adsenger.html
The aio may be getting flooded by the db writer, try increasing the
maxreqs parameter for aio (I'm currently using 12288 instead of the
default 4096). nb, requires reboot to take effect.
Also try checking to see if you are running out of bufstructs using
vmtune -a.  If fsbufswaitcnt is non-zero and increasing then set the
vmtune -b parameter to be greater than the default 93 (I'm currently
using 200 for that value). nb, requires umount/mount of filesystems to
take effect.
You may also want to check your other vmtune settings for minperm (-p),
maxperm (-P), minfree (-f), maxfree (-F) and maxpgahead (-R) as the
defaults aren't ideal for oracle.  There's another setting that may be
relevant, the sync_release_ilock (-s) but I'm not familiar with that
one, and it doesn't appear to be documented.
It might also be worth checking the init.ora files of all instances to
see if they are actually set to use aio.
Also make sure that your machine is running at least ML-02 as aio was
wobbly prior to that.
General questions: what kind of disk subsystem are you using, and what
kind of RAID are you using?

Regards,
Clive

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Asynch IO and Oracle

Post by Connor McDonal » Thu, 07 Sep 2000 20:05:22



> Hi,

> I was wondering what a good value for asynchronous IO on AIX
> would be. The default was 1 min 10 max (number of servers). I
> read somewhere that on a 4 disk system values of 10 min 40 max
> was recommended.

> I have a 6 CPU S80 server with 8 GB of RAM and 8 drawers of
> SSA disks (128 disks of 9 GB). I am running 16 Oracle databases
> on it. Most are small but I have 4 large databases with 50
> datafiles each. Each of the 4 large databases has 4 database
> writer processes (dbw0 dbw1 dbw2 dbw3).

> I set min to 100 and max to 1000 in 'smit aio'. 4*4*50 also
> implies a value of that size. If I use 'ps -Af|grep kproc'
> there are 1017 processes, so all AIO processes are started
> and being used. However I am not seeing much performance
> improvement from back when the maximum was set to 100.

> Except that occasionally the system seemed to freeze, with
> uptime and ps showing almost nothing running, while users were
> complaining about slow queries. I contributed that to lack of
> AIO processes and upped it to 1000.

> What is a good value for those AIO parameters ? Or is 1000
> excessive and hurting performance ? Should I use 4 database
> writers for each database on a 6 CPU system with asynch IO,
> or just one ?

> Thanks,

> Rob
> --
> Get tons of referrals - Sign up now ! http://www.veryComputer.com/
> The Adsenger information resource - http://www.veryComputer.com/~rob/adsenger.html

Are you using file system based AIO or raw based AIO...If the former,
then if you're really keen on getting the max benefit for AIO, then look
at moving to raw.

HTH
--
===========================================
Connor McDonald
http://www.veryComputer.com/

We are born *, wet and hungry...then things get worse

 
 
 

Asynch IO and Oracle

Post by Jonathan Lewi » Fri, 08 Sep 2000 05:46:03


In theory, if you are running Async I/O, you should
not be running multiple db-writers as these were
Oracle's method for creating 'pseudo-'async I/O.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk


>Hi,

>I was wondering what a good value for asynchronous IO on AIX
>would be. The default was 1 min 10 max (number of servers). I
>read somewhere that on a 4 disk system values of 10 min 40 max
>was recommended.

>I have a 6 CPU S80 server with 8 GB of RAM and 8 drawers of
>SSA disks (128 disks of 9 GB). I am running 16 Oracle databases
>on it. Most are small but I have 4 large databases with 50
>datafiles each. Each of the 4 large databases has 4 database
>writer processes (dbw0 dbw1 dbw2 dbw3).

>I set min to 100 and max to 1000 in 'smit aio'. 4*4*50 also
>implies a value of that size. If I use 'ps -Af|grep kproc'
>there are 1017 processes, so all AIO processes are started
>and being used. However I am not seeing much performance
>improvement from back when the maximum was set to 100.

>Except that occasionally the system seemed to freeze, with
>uptime and ps showing almost nothing running, while users were
>complaining about slow queries. I contributed that to lack of
>AIO processes and upped it to 1000.

>What is a good value for those AIO parameters ? Or is 1000
>excessive and hurting performance ? Should I use 4 database
>writers for each database on a 6 CPU system with asynch IO,
>or just one ?

>Thanks,

>Rob
>--
>Get tons of referrals - Sign up now ! http://www.refmatrix.com/
>The Adsenger information resource - http://www.xs4all.nl/~rob/adsenger.html

 
 
 

Asynch IO and Oracle

Post by Walter O » Sat, 09 Sep 2000 03:59:18



Quote:>> I set min to 100 and max to 1000 in 'smit aio'. 4*4*50 also
>> implies a value of that size. If I use 'ps -Af|grep kproc'
>> there are 1017 processes, so all AIO processes are started
>> and being used. However I am not seeing much performance
>> improvement from back when the maximum was set to 100.

And have a look at the queue_depth of your devices (lsattr -El hdiskX ).
Typical values are 3, so you can't sent more than 3 concurrent I/O
operations to the disk. Therefore the maximum reasonable value for aio
maxserver would be 3 times the number of disks. If you mirror your logical
volumes, it would be 3 times the number of disks with primary copies, as
each "logical" I/O will result in two physical I/Os.
You should only need one db writer per instance unless you have huge SGAs.

--
Walter Orb

 
 
 

Asynch IO and Oracle

Post by Viacheslav Leichinsk » Sat, 09 Sep 2000 15:56:01




Quote:

> In theory, if you are running Async I/O, you should
> not be running multiple db-writers as these were
> Oracle's method for creating 'pseudo-'async I/O.

Sorry, I am not familar with AIX but I can add to Jonathan note
there is statement in Oracle Tuning manual that async I/O is used
only with parallel queries and hash joins. So you can no performance
gain from async I/O at all.
Regards, Slava.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Asynch IO and Oracle

Post by Fidel » Sat, 09 Sep 2000 16:33:44



>And have a look at the queue_depth of your devices (lsattr -El hdiskX ).
>Typical values are 3, so you can't sent more than 3 concurrent I/O
>operations to the disk. Therefore the maximum reasonable value for aio
>maxserver would be 3 times the number of disks. If you mirror your logical
>volumes, it would be 3 times the number of disks with primary copies, as
>each "logical" I/O will result in two physical I/Os.
>You should only need one db writer per instance unless you have huge SGAs.

The queue depth is 8, so that should be OK.

What are huge SGA's in your opinion ? The 4 large databases have
SGA's from 50 to 200 MB, block_buffers from 100 to 200 MB, largest
databases have a total sga of about 400 MB. Those values are tuned
and are reasonable, keeping reloads at less than 5% for the SGA
and buffer hits above 95%.

I am not clear on this database writer thing. In Oracle 8 you have
db_writer_processes and dbwr_io_slaves. The last parameter was
called db_writers in Oracle 7. The documentation I've seen says
not to use multiple db_writers or IO_slaves when using AIO. But
there is no mention on whether to use only one database writer
with AIO.

R
--
Get tons of referrals - Sign up now ! http://www.refmatrix.com/
The Adsenger information resource - http://www.xs4all.nl/~rob/adsenger.html

 
 
 

Asynch IO and Oracle

Post by Connor McDonal » Sat, 09 Sep 2000 17:52:44





> > In theory, if you are running Async I/O, you should
> > not be running multiple db-writers as these were
> > Oracle's method for creating 'pseudo-'async I/O.

> Sorry, I am not familar with AIX but I can add to Jonathan note
> there is statement in Oracle Tuning manual that async I/O is used
> only with parallel queries and hash joins. So you can no performance
> gain from async I/O at all.
> Regards, Slava.

> Sent via Deja.com http://www.veryComputer.com/
> Before you buy.

The manual is not correct - asncy I/O is also used by the database
writer, which is of course, crucial to good write performance.

HTH
--
===========================================
Connor McDonald
http://www.veryComputer.com/

We are born *, wet and hungry...then things get worse