Oracle Doesn’t Scale Without Multiple Log Writer Processes

There has been a bit of a spat going on between Jonathan Lewis and Don Burleson. Don’s summary of the topic can be found here. I don’t care about the spat. I want to point out something clearly. Very clearly. But first, a quote from Don:

[…] Lewis failed to mention the hidden parameter _lgwr_io_slaves nor the Metalink note that clearly states that initially, only one LGWR process is started and that multiple LGWR processes will only appear under high activity.

The Oracle docs are very clear that multiple log writer processes exist:

“Prior to Oracle8i you could configure multiple log writers using the LGWR_IO_SLAVES parameter.”

Note: There is a big difference between LGWR I/O Slaves and LGWR. Don continues:

(Note: Starting in 8.1.7, lgwr_io_slaves became a hidden parameter _lgwr_io_slaves, and it remains a hidden parm in Oracle10g).

Further, Metalink note 109582.1 says that multiple log writer I/O slaves were first introduced almost a decade ago, way back in Oracle8:

Folks, there is only ever one LGWR whose job it is to clear the redo buffer. DBWR on the other hand is multi-stated (support for multiple true DBWR processes) and that is due entirely to the NUMA requirements of certain ports from the late 1990s. Although it is possible for a single DBWR to saturate a CPU, and therefore need multiple DBWR processes, that is rare (generally related to pathological latch contention on cache buffers chains).

As long as LGWR and DBWR have asynchronous I/O support—and you are not running on a NUMA-optimized port—there really should never be a reason to configure multiple DBWR processes, nor should LGWR saturate a CPU.

Let me put it this way, there are examples of large high-end systems doing OLTP that don’t need multiple LGWR processes. Here is a system with roughly 7,000 spindles, 40 FC HBAs, 128 cores and 2TB RAM that needed neither multiple LGWR nor DBWR processes. And if you think you have a workload that generates more redo on a per-CPU basis than TPC-C you’re probably wrong.

If I ever hear the term I/O slaves again, I’ll:

gross.jpg

Summary
Oracle demonstrates more LGWR scalability than 99.99942% of all production sites would ever need when they run such high end TPC-C benchmarks as the one I cited above. And please, let’s stop the I/O slave madness!

15 Responses to “Oracle Doesn’t Scale Without Multiple Log Writer Processes”


  1. 1 Hemant K Chitale July 19, 2007 at 3:10 am

    So you are comfortable with NOT using multiple DBWRs
    (ie DBWR I/O Slaves) ?
    I don’t use multiple DBWRs I/O slaveseither but keep seeing
    “recommendations” to do so. Also, that multiple DBWR I/O
    slaves can be used to “simulate” Async I/O. I’m not
    very sure how that woud really work although the “simulation” means that DBWR0 hands-over to DBWR1
    the way it would have handed-over to the OS’s Async I/O ?

    I noticed that the 10g Admin Guide includes a new parameter
    “DB_WRITER_PROCESSES” different from “DBWR_IO_SLAVES”.
    What is this new parameter for ?
    Hemant

  2. 2 kevinclosson July 19, 2007 at 4:16 am

    So you are comfortable with NOT using multiple DBWRs
    (ie DBWR I/O Slaves) ?

    Hemant,

    Don’t confuse slaves with writers. Oracle8i introduced true multiple writers. These are not slaves, but writers that tend to their own LRUs, build their own batches, perform their own I/O and post waiters. Slaves are slaves. If you have 1 writer and no async I/O (OS support), you can configure slaves and get a little relief. All told however, let’s be aware that this is 2007 and any platform that matters supports async I/O. Let’s use it.

    If you have one writer and async I/O but see free buffer waits, take a look at DBWR’s processor utilization. If it is saturating a CPU, add another DBWR process (DB_WRITER_PROCESSES=2).

    Folks: LGWR and DBWR can use slaves for systems that don’t support async I/O. If you use DBWR slaves, you can only have one DBWR. You can only ever have 1 LGWR since that is all the product supports. Having said all that, use async I/O and you will most likley not need multistated background processes (multiple DBWR/LGWR).

  3. 3 Hemant K Chitale July 19, 2007 at 1:02 pm

    My apologies. I started reading your references
    to “LGWR IO Slaves” and the paragraph beginning “As long as LGWR and DBWR have asyncronous I/O support ..” so I though you also meant DBWR I/O slaves. (because slaves are for systems that don’t provide async I/O).

    You are right. We generally do not need DBWR_IO_SLAVES. We may need DB_WRITER_PROCESSES if the single process is saturating CPU.

    I haven’t used either parameter (not being comfortable with DBWR_IO_SLAVES) and have never noticed DBWR saturating CPU (so I hadn’t come across references to DB_WRITER_PROCESSES in search of a solution for a non-existent problem).
    Hemant

  4. 4 Nitin Vengurlekar July 20, 2007 at 4:23 am

    Folks, there is absolutely no reason to use db_io_slaves anymore. Actually there probably never *really* was a reason, except for some port specific problems that appeared in a distant past. This was a point-specific fix for a point-specific problem…sorta like the ‘alter system suspend/resume’ command. If I hear one more user say they use this comand ….i’m gonna barf !

  5. 5 kevinclosson July 20, 2007 at 3:45 pm

    Nitin,

    If someone finds themselves on a platform that doesn’t support asynchronous I/O, they might need slaves. Let’s just not run Oracle on any platform that doesn’t support direct I/O…now there is an idea!

  6. 6 Sanjay Gulabani July 21, 2007 at 4:31 am

    Kevin,

    I found one exception to your recommendation — ” Multiple DB Writers on modern OSes that support async i/o only when 1 DB Writer is consuming all of the CPU.”

    I recall when I ran my tests on RHEL4 u4 for my paper see http://www.netapp.com/library/tr/3495.pdf I found that using multiple dbwriters helped some. I increased them to as many as cores (8 on that V40z system) performance improved and beyond that it didn’t help. This was an OLTP benchmark.

    Perhaps this is indicative of how well async i/o on linux is implemented.

    So yes, we’re in 2007 and all modern OSes support async i/o. But perhaps some do it better than others.

    I’d love to hear if you have had different experiences on 64bit linux with async i/o and linux kernel nfs.

    thanks,
    Sanjay Gulabani
    http://netappdb.blogspot.com/

  7. 7 kevinclosson July 21, 2007 at 6:25 am

    Sanjay,

    I read that paper some time ago and I don’t recall seeing any figures for IOPS in there. I don’t doubt you when you say you saw benefit from adding multiple DBWR processes. I just don’t find that to be the case generally–that is unless DBWR processes become CPU-bound. What sort of I/O rate did those test push?

  8. 8 Donald K. Burleson July 21, 2007 at 10:08 pm

    Peter K. xxxx,

    >> You could see why DC got so confused about IO_SLAVES and the LGWR process itself.

    The assertion that I may have misunderstood was “the log writer does not have multiple processes”.

    They can be seen with a “ps -ef|grep ORA|grep IX” command, and I guess I fail to see how they are not processes.

    >> enabled DB to sic his legal representatives for copyright violations.

    This is a LIE, Peter K.

    I have NEVER threatened ANY BLOGGER for copyright violations. I don’t even go after people who copy my whole web site:

    http://www.copyscape.com/view.php?o=56414&u=http%3A%2F%2Fwww.globaldata.ro%2Fconsultanta.html&t=1185055303&s=http%3A%2F%2Fwww.dba-oracle.com&w=50&c=

    http://www.copyscape.com/view.php?o=56414&u=http%3A%2F%2Fwww.3rpco.com%2Fservices.asp&t=1185055303&s=http%3A%2F%2Fwww.dba-oracle.com&w=20&c=

    “BC is expert at Oracle instance tuning, SQL tuning and server-side tuning for Oracle.”

    Why can’t you people play fair, without threatening “smear campaigns” and telling lies?

    BTW, my wife made me remove that complaint page, after receiving veiled threats from Jonathan Lewis that “he suspects” that my complaints about him would damage me, if they were not removed. Last time I was smeared, the scumbags felt compelled to drag my wife into the fight.

    Peter K. XXX, please retract this LIE.

  9. 9 kevinclosson July 21, 2007 at 10:14 pm

    Don,

    Keep it short and sweet here, ok.

  10. 10 Donald K. Burleson July 21, 2007 at 10:20 pm

    >> Keep it short and sweet here, ok.

    Ok, how’s this:

    Peter K. is a liar.

  11. 11 Donald K. Burleson July 21, 2007 at 10:27 pm

    The nasty nature of these assault is evident to all, and the attacks have been likened to the attack on Kathy Sierra:

    http://tkyte.blogspot.com/2007/03/this-is-really-bad.html

    “I have seen many posts in regards to comments made by “Burleson vs Lewis” fanboys that would probably not be much different than those made against Kathy.”

    FYI, this is what happened to Kathy:

    http://headrush.typepad.com/

  12. 12 kevinclosson July 21, 2007 at 10:35 pm

    Readers:

    There were 4 comments on this thread that pertained to the strife between Don Burleson and Jonathan Lewis. Although I have the utmost respect for Jonathan Lewis, I have nothing to do with this war and so I’m removing comments from Peter K and Don Burleson.

    Theis thread is about LGWR, nothing else.

  13. 13 Peter K July 25, 2007 at 3:00 pm

    Kevin,
    My apologies. You are of course right to do so.

    Peter.

  14. 14 Karl Arao January 20, 2009 at 5:00 am

    For Sanjay, Kevin:
    – So it’s okay to set the db_writer_processes equal to # of CPUs?
    – or you monitor the DBWR then if its consuming high CPU then add another one upto the # of CPUs, it’s like offloading the other DBWR of the work?

    Kevin,
    What are your thoughts about log_archive_max_processes?
    The default is 2, but the official doc and MAA best practice white paper recommend to set it to 30, this will create 30 LGWR processes.. but I still how to find out if these processes are working in parallel altogether..

    I’ll appreciate your inputs..


  1. 1 LGWR e ASYNC I/O « Oracle and other Trackback on July 24, 2007 at 2:32 pm

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




EMC Employee Disclaimer

The opinions and interests expressed on EMC employee blogs are the employees' own and do not necessarily represent EMC's positions, strategies or views. EMC makes no representation or warranties about employee blogs or the accuracy or reliability of such blogs. When you access employee blogs, even though they may contain the EMC logo and content regarding EMC products and services, employee blogs are independent of EMC and EMC does not control their content or operation. In addition, a link to a blog does not mean that EMC endorses that blog or has responsibility for its content or use.

This disclaimer was put into place on March 23, 2011.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,980 other followers

Oracle ACE Program Status

Click It

website metrics

Fond Memories

Copyright

All content is © Kevin Closson and "Kevin Closson's Blog: Platforms, Databases, and Storage", 2006-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Kevin Closson and Kevin Closson's Blog: Platforms, Databases, and Storage with appropriate and specific direction to the original content.

Follow

Get every new post delivered to your Inbox.

Join 1,980 other followers

%d bloggers like this: