Re: commit_write= BATCH, NOWAIT; ... how long do I wait?
From: joel garry <joel-garry_at_home.com>
Date: Fri, 10 Jul 2009 17:05:34 -0700 (PDT)
Message-ID: <bf755481-fa36-475b-8f3d-f73510841f45_at_t33g2000yqe.googlegroups.com>
On Jul 9, 11:18 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Pat" <pat.ca..._at_service-now.com> wrote in message
>
> news:b21e89a6-7fb0-4206-a45f-43b242ebd361_at_y19g2000yqy.googlegroups.com...
>
>
>
> > We're running Oracle 10.2.0.4 (64 bit) on top of red-hat linux.
>
> > Recently, we've started configuring the database servers with:
>
> > alter system set commit_write=BATCH, NOWAIT;
>
> > In the event of a database/server crash we can afford the loss of a
> > few transactions. Frankly, we can probably afford the loss of 5
> > minutes or so worth of transactions, so we're not deeply concerned by
> > the durability loss associated with running in asynchronous commit
> > mode.
>
> > However, I at least, am more than a little curious as to how much data
> > we're putting at risk here.
>
> > Does anybody know how long Oracle will buffer redo in memory before it
> > commit when running in this mode? I'm operating under a theory that it
> > probably commits every time either A) its redo buffer is full or B)
> > the oldest redo entry is older than x, but that's pure speculation on
> > my part.
>
> > Is there any guarantee *at all* here that data older than "x" is on
> > disk? I've worked with other databases (mysql/innodb) where there's a
> > guarantee that it'll flush the redo within 1 second of your commit if
> > you run in "weakly" durable mode, but I can't seem to find any Oracle
> > doc that specifies if there is such a commitment.
>
> I don't think there's any documentation that states explicitly anything
> like:
> "if you set commit_write to 'batch, nowait' then your change will remain
> unsecured for N seconds"'.
>
> However, there is documentation that states that the log writer (LGWR)
> wakes up approximately every three seconds or when there is 1MB of
> log in the buffer even if nothing else kicks it, : so you can probably
> assume
> that the most you can lose is about 1MB or 3 seconds, whichever is larger.
>
Date: Fri, 10 Jul 2009 17:05:34 -0700 (PDT)
Message-ID: <bf755481-fa36-475b-8f3d-f73510841f45_at_t33g2000yqe.googlegroups.com>
On Jul 9, 11:18 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Pat" <pat.ca..._at_service-now.com> wrote in message
>
> news:b21e89a6-7fb0-4206-a45f-43b242ebd361_at_y19g2000yqy.googlegroups.com...
>
>
>
> > We're running Oracle 10.2.0.4 (64 bit) on top of red-hat linux.
>
> > Recently, we've started configuring the database servers with:
>
> > alter system set commit_write=BATCH, NOWAIT;
>
> > In the event of a database/server crash we can afford the loss of a
> > few transactions. Frankly, we can probably afford the loss of 5
> > minutes or so worth of transactions, so we're not deeply concerned by
> > the durability loss associated with running in asynchronous commit
> > mode.
>
> > However, I at least, am more than a little curious as to how much data
> > we're putting at risk here.
>
> > Does anybody know how long Oracle will buffer redo in memory before it
> > commit when running in this mode? I'm operating under a theory that it
> > probably commits every time either A) its redo buffer is full or B)
> > the oldest redo entry is older than x, but that's pure speculation on
> > my part.
>
> > Is there any guarantee *at all* here that data older than "x" is on
> > disk? I've worked with other databases (mysql/innodb) where there's a
> > guarantee that it'll flush the redo within 1 second of your commit if
> > you run in "weakly" durable mode, but I can't seem to find any Oracle
> > doc that specifies if there is such a commitment.
>
> I don't think there's any documentation that states explicitly anything
> like:
> "if you set commit_write to 'batch, nowait' then your change will remain
> unsecured for N seconds"'.
>
> However, there is documentation that states that the log writer (LGWR)
> wakes up approximately every three seconds or when there is 1MB of
> log in the buffer even if nothing else kicks it, : so you can probably
> assume
> that the most you can lose is about 1MB or 3 seconds, whichever is larger.
>
I'm now idly wondering if the data you can lose is something besides incoming user data, that can make recovery difficult? (Maybe someone trips over the power cord during writing metadata changes or writing undo or something). I'm wondering if for that reason one should only set this in sessions that limit it to incoming user data.
Pat, are you using all the tricks to make the imp as fast as possible? (like http://www.oracledba.co.uk/tips/import_speed.htm ). Which parameters are you using?
jg
-- _at_home.com is bogus. http://www.java-entrepreneur.com/50226711/amr_oraclesun_to_wreak_havoc_on_enterprise_software_market.phpReceived on Fri Jul 10 2009 - 19:05:34 CDT