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:
In my test database these settings were 2-3 times faster when running 15 parallel update statements (each updating 100,000 rows):
I also tried bumping the writer processes way up to these settings:
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.
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.
Pingback: Direct IO and db_writer_processes=36 on HP-UX reduced free buffer waits | Bobby Durrett's DBA Blog
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.
I’m glad it was helpful to you. Thank you for the feedback.
Pingback: Impact of Force Logging | Bobby Durrett's DBA Blog