db_writer_processes, dbwr_io_slaves with no asynch I/O on HP-UX

I’m working on an HP-UX system that doesn’t have asynchronous I/O configured and I’m getting a bunch of “free buffer waits” which indicates that the DBWR processes are having trouble writing updated blocks from memory to disk fast enough.  Some preliminary testing I’ve done implies that I should change the following init.ora parameters: db_writer_processes, dbwr_io_slaves.  Our current settings in production are:

db_writer_processes=4, dbwr_io_slaves=0

In my test database these settings were 2-3 times faster when running 15 parallel update statements (each updating 100,000 rows):

db_writer_processes=1, dbwr_io_slaves=32

I also tried bumping the writer processes way up to these settings:

db_writer_processes=36, dbwr_io_slaves=0

but this didn’t help at all.

I engaged in a forum discussion with some very helpful people here: forum thread

Prior to the forum discussion I reviewed the manuals, Oracle’s support site, some blog postings and a usergroup presentation and was left with contradictory and confusing information on what settings to try for these two parameters.  I got the impression that increasing db_writer_processes would help but in my test it did not.

I can’t come to any firm conclusions except to recommend that if you don’t have asychronous I/O on HP-UX try setting db_writer_processes=1, dbwr_io_slaves=32 (some number > 0) and see if it helps.  Of course always try any change in a test environment before making the change in production.

– Bobby

P.S.  This query should show you if you datafiles are using asynch i/o:

select ASYNCH_IO,count(*) from v$iostat_file group by ASYNCH_IO;

Here is the output on our production server with the free buffer waits:

SQL> select ASYNCH_IO,count(*) from v$iostat_file 
group by ASYNCH_IO;

ASYNCH_IO   COUNT(*)
--------- ----------
ASYNC_OFF        301

On a system with asynch I/O it looks like this:

SQL> select ASYNCH_IO,count(*) from v$iostat_file 
group by ASYNCH_IO;

ASYNCH_IO   COUNT(*)
--------- ----------
ASYNC_OFF         10
ASYNC_ON          27

The 10 files with asynch off are not data files.

p.s.  Interestingly an 11.2 manual says that HP-UX doesn’t support asynch IO on filesystems.  I haven’t verified this with a test but it looks like your best bet on HP-UX would be to use raw devices and ASM with asynch IO.

Oracle® Database Administrator’s Reference
11g Release 2 (11.2) for Linux and UNIX-Based Operating Systems

“To use asynchronous Input-Output on HP-UX, you must use an Automatic Storage Management disk group that uses raw partitions as the storage option for database files.”

In my case I’m just looking for a quick boost to an existing system with datafiles on filesystems on HP-UX so maybe in this kind of special case setting db_writer_processes=1, dbwr_io_slaves > 0 makes sense.  At least it is worth a try.

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to db_writer_processes, dbwr_io_slaves with no asynch I/O on HP-UX

  1. Pingback: Direct IO and db_writer_processes=36 on HP-UX reduced free buffer waits | Bobby Durrett's DBA Blog

  2. Jason says:

    Your blog on dbw processes within HPUX has proved invaluable to me.

    We were heavily under pressure with FREE BUFFER WAITS and no matter what I did with the db writer processes it wouldnt improve.

    I made the setting change with the dbwr_io_slaves and now I’ve see a 3 fold performance increase.

    Thanks

Leave a Reply