High IO Waiting...

Hey all,

   I have an application that extracts data from a schema, transforms it and places it into a datamart within the same database. I'm noticing a high amount of IO waiting varying between 20 and 75% during each run.

From what I read, this doesn't necessarily mean there is a problem because the service times are low and there aren't any jobs waiting at the disk level. But a high IO wait like that tells me there is room for improvment.

The system is setup as follows (don't blame me, I just got here. :)... I'm not sure of the systems exact stats but here's what I know

Solaris 5.8 dual CPU
8 gigs of ram (~4gig still free)
18 gigs of swap (only 2 of which used)
Database is stripped across 2 physical disks with a stripe size of 12 gig. (I didn't know you could stripe as large as 12 gig!?) Mirrored to 2 other physical disks on the same controller..

From a database perspective, performance wise, everything is fine but the biggest waiting is on IO. I gathered statisics (iostat, mpstat) while the refresh is running and my conclusion is the system is IO bound. Like I said, upwards to 70% of the CPU is waiting for IO to complete. I charted the IO Activity per stripe (or slice as my admin calls them) and its obvious the data isn't well distributed, some stripes are more heavily hit. The solution to this is to decrease the stripe size and i'd probably set it to 1 meg. The Oracle documention I have read recommends this.

Some questions I have are:
-How else can I reduce the amount of time my CPU is waiting on IO to
complete? Should I consider multiple DBWR's or slaves? Since my disks aren't maxed out, will this help? I've never had to do this before so i'm going to read up on it tonight.

-Should I reduce the stripe size to a more reasonable amount, say 1
meg?

-How do I tell if there is any contention on the controller? IO wait
is high and at times disk % busy is high but i'm wondering if I put the mirrors on a separate controller would that help throughput? Isn't having source and its mirror on the same controller bad?

If you have any other suggestions it would be greatly appreciated.

Dave Received on Mon May 05 2003 - 15:54:14 CDT