Kevin Closson's Blog: Platforms, Databases and Storage

Home | Pages | Archives

Over-Configuring DBWR Processes – Part III

August 17, 2007 11:49 pm

Sometimes the comment thread on a blog entry are important enough to warrant yet another blog entry. This is one of those times. In my blog entry called Over-Configuring DBWR Processes Part II I offered some test results showing that it really is best to keep a single writer until such time as a single writer becomes CPU-bound. But first a disclaimer.

DISCLAIMER: There are ports of Oracle where specialized multiple DBWR code is implemented for specific hardware architecture (e.g., NUMA). I ought to know, I worked on one of the very first (if not the first) to do so with the ports to Sequent DYNIX/ptx.

There, disclaimer aside, I am talking about the Linux ports of 10g and 11g which have no such hardware specialization.

The first question in that comment thread was:

From what’s written here, it looks like you only tested 1 and 8. Is it possible that the global maximum is actually somewhere between? Maybe 2 processes is slightly faster than 1, for example?

Great question with a simple answer: use 1 writer as long as that single writer has ample bandwidth. That is a truth, but I figured I’d do some runs to milk out a fact or two. I configured 2 DBWR processes and re-ran the workload I describe in Part II of this series. I followed that up with 4 DBWR processes. Hey, it’s all auto-pilot stuff with my setup so doing this was no big deal. Let’s compare the results. Just to summarize, the previous configurations performed as follows:

And now with the 2 and 4 DBWR cases:

The way this goes is that more DBWR processes impact throughput. On the other hand, more throughput will require more DBWR work so the optimal case of 1 DBWR process will take more CPU, but remember that it is also getting more throughput. I only provide these CPU numbers to show what percentage of a single CPU any given configuration utilizes. I don’t think it is necessary to run the 6 DBWR case. But what about a wildcat configuration such as multiple DBWR processes that have hard processor affinity?

Processor Affinity and Oracle Background Processes
Well, I’d never bind an Oracle background process to a singe processor (core) for obvious reasons, but with the Linux taskset(1) command it is possible to bind a running process to a socket. For example, the following command binds pid 13276 to the seconds socket of a mulit-socket system:

# taskset –pc 2-3 13276

Building on that idea, you can grab DBWR processes (or any process for that matter) once they are running and pin them to a CPU core or a set of CPU cores. The following stupid bash shell function can be used to do this sort of thing. Note, since there are tens of thousands of skilled computer programmers out there these days, all shell scripts are considered stupid of course. The function takes an argument that it then uses in its grep regular expression for globbing ps(1) output. An example would be:

aff ora_dbw

aff()

{

local GLOB="$1"
local CORE=0
local TWIN=0

ps -ef | grep $GLOB | grep -v grep | awk '{ print $2 }'  | while read PID
do
        (( TWIN = $CORE + 1 ))

        set -x
        sudo taskset -pc ${CORE}-${TWIN} $PID
        set -    

        (( CORE = CORE + 2 ))
        [[ $CORE = 8 ]] && CORE=0 && TWIN=0
done
}

I used this script to affinity 2 DBWR processes to a single socket and the numbers didn’t change. Neither did throwing 2 DBWR processes one each to two different sockets. The fact simply remains that DBWR doesn’t benefit from having peers unless it is CPU-bottlenecked.

Posted by kevinclosson

Categories: DBWR Performance, oracle, Oracle I/O Performance, Oracle performance

Tags:

« Older Newer »

4 Responses to “Over-Configuring DBWR Processes – Part III”

1. hey Kevin. I’m (finally) set to make this change in my test environments. I’m wondering if you can share some basics in setting up a throughput test for me to get some hard numbers.

   I’m also wondering what you think of the description of db_writer_processes here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#sthref1002

   Oracle fails to really mention a negative effect. When I read something like that, I can’t help but think “the more, the merrier”.

   By Don Seiler on November 27, 2007 at 10:08 pm

2. test

   By Don Seiler on November 27, 2007 at 10:14 pm

3. […] 1, 2007 by Don Seiler I was recently re-reading parts I, II, III and IV of Kevin Closson’s series on the perils of overconfiguring your db_writer_processes […]

   By DBWR and the Devil « die Seilerwerks on December 1, 2007 at 3:14 pm

4. Yeah, in oltp environment, multiple db writer should not help us.
   But,just as you mentioned in the blog, extra db writer process will increase extra latch(cache buffer chain/LRU latch) number, so I think, maybe multiple db writer process should decrease the latch free(cache buffer chain/LRU ) contention…

   By Hechzh on March 1, 2011 at 7:53 am

Leave a Reply

You must be logged in to post a comment.

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.