IDS Forum

Re: A question of load . . .

Posted By: Chris Salch
Date: Friday, 1 September 2006, at 11:11 a.m.

In Response To: Re: A question of load . . . (ART KAGEL, BLOOMBERG/ 731 LEXIN)

Most definitely thank you all. Now I've got to see what our application
vendor, thinks about this little tidbit and see where to go from there.
I'll post what happens as soon as I know :)

On Fri, 2006-09-01 at 10:35 -0400, ART KAGEL, BLOOMBERG/ 731 LEXIN
wrote:
> Here's my reasoning:
>
> If you are using BUFFERED LOG databases then logical log buffers are not
> flushed
> to the logs on disk until they fill. For OLTP typical transaction size tends
> to be small, a few KB is normal. With the default logical log buffer size
> (32K)
> that means that on average 4-16 committed transactions are at risk for
> undetacted rollback after committing successfully if the server crashes before
> the log buffer fills and flushes. If you increase the buffer size to 256 K
> that
> increases the number of transactions at risk to from 64-128. Note when you
> increase the size of the buffer 8-fold you also increase the time needed to
> flush that buffer to disk by 8 times. All unacceptable risk.
>
> If you are using UNBUFFERED LOG databases (highly recommended for OLTP) then
> the
> logical log buffer is flushed as soon as a commit/rollback record is written
> to
> it or it fills whichever comes first. In this case from 25-75% of the buffer
> is unused during normal processing on an OLTP system with default 32K logical
> log buffers. If you increase the logical log buffer size to 256K then on
> average from 75-98.5% of the logical log buffer space is unused. This is in
> constrast to the recommendation you quote to try to use >75% of the buffers
> before flushing. Data risk is not significantly affected here however, but the
> expanded buffers accomplish nothing.
>
> Physical log buffering is another matter, though I didn't address it
> separately
> before. Here you can make a case since the physical log is not strictly
> neccessary for recovery or data integrity. There are only a small number of
> scenarios under which the restoration of physical log pages after a crash
> actually improves the data integrity. OK, for the physical log buffers I will
> admit a case can be made that increasing the buffer size, based on observation
> of the onstat -l header section, is reasonable.
>
> Art S. Kagel
>
> ----- Original Message -----
> From: Keith Simmons <ids@iiug.org>
> At: 9/01 10:20:59
>
> Although perhaps so critical nowadays,with Sans and large buffers
> external to the engine, I think there is still some milage in bigger
> log buffers, particularly on direct connect disks. The recommendation
> comes straight from a set of Informix Perormance Tuning Course Notes
> (with such worthys as John Mille and Mark Scranton on the authors
> list!). It suggests (particularly for OLTP) that to minimise I/O the
> buffers should be about 75% full when they are flushed. (any more
> produces excessive I/O, any less potentially wastes memory. Monitor
> using onstat -l |head -18. May not give much improvement (and may be
> offset by disadvantages) but is worth consideration
>
> Keith
>
> On 01/09/06, ART KAGEL, BLOOMBERG/ 731 LEXIN <kagel@bloomberg.net> wrote:
> >
> > I will submit that there is no good reason to increase the size of the
> logical
> > log buffer or the physical log buffer. It just puts more data at risk in a
> > BUFFERED LOG environment and uses up logical log space faster in an
> UNBUFFERED
> > LOG environment for little or no performance improvement.
> >
> > Art S. Kagel
> >
> > ----- Original Message -----
> > From: Keith Simmons <ids@iiug.org>
> > At: 9/01 5:54:13
> >
> > Chris
> >
> > 90000 buffers on a 2K page size O/S means 180 Mb of buffer space! With
> > 5Gb memory, even with what else is happening on the machine, I would
> > try 350000 buffers taking up 700 Mb. You will need to increase LRUs
> > (try 127) and CLEANERS (127 to match LRUs). Also watch checkpoint
> > times and be prepared to decrease the interval or LRU_MAX and LRU_MIN.
> > I also noticed your LOG_BUFF and PHYS_BUFF are still at default
> > values. There may be some milage in increasing these (even as far as
> > 128 or 256) which can help as well.
> >
> > Keith
> >
> > On 31/08/06, Chris Salch <chrissalch@letu.edu> wrote:
> > >
> > > And the ratios script shows:
> > >
> > > pgsused/(ixda-RA+idx-RA+da-RA)*100
> > > Read-Ahead Util (RAU): 99.90% 23922139/(6713181+244596+16988224)*100
> > >
> > > bufwaits*100 / (pagreads+bufwrits)
> > > Bufwaits Ratio (BWR): 2.39% (4819402*100) / (30380617+171168299)
> > > Buffer Turnover(Max):2239.43 (pagreads+bufwrits)/(BUFFERS=90000)
> > > Buffer Turnover(Min): 349.92 (pagreads+(bufwrits*(1-%
> > > cached)))/(BUFFERS)
> > >
> > > BT Period-max: 19.90/hr every 3.01 minutes.
> > >
> > > BT Period-min: 3.11/hr every 19.29 minutes.
> > >
> > > Does this look as off as I think it does?
> > >
> > > On Thu, 2006-08-31 at 18:22 -0400, Chris Salch wrote:
> > > > Those stats are reset every sunday at 1:05 am. So that BTR is a
> > > > significantly higher number than what you have shown. That would be a
> > > > BTR of about 12 ouch! (by your info)
> > > >
> > > > On Thu, 2006-08-31 at 14:57 -0400, ART KAGEL, BLOOMBERG/ 731 LEXIN
> > > > wrote:
> > > > > OK, I've calculated the metrics from the onstat -p output below. It's
> > not
> > > > > particularly useful unless you've zerod the stats more recently than
> > > server
> > > > > startup 13 days ago, but here they are:
> > > > >
> > > > > Pagreads: 7908428
> > > > > Bufwrits: 35477827
> > > > > Bufwaits: 1443280
> > > > > BUFFERS: 90000
> > > > > Time (hours) since reset: 321.33
> > > > > ixda-RA: 2346196
> > > > > idx-RA: 86301
> > > > > da-RA: 4646343
> > > > > RA-pgsused: 7071512
> > > > >
> > > > > BR = (1443280 / (35477827 + 7908428)) * 100.00 = 3.3200
> > > > > BTR = (((35477827 + 7908428) / 90000) / 321.33) = 1.5002
> > > > > RAU = (7071512/(2346196+86301+4646343)) * 100.00 = 99.8900
> > > > >
> > > > > These look fine, but a shorter accumulation period would give more
> > > reliable
> > > > > numbers. For best results you should be saving the underlying values
> at
> > > > least
> > > > > daily (some save them before and after peak load periods each day) and
> > > > > clearing
> > > > > the stats at least weekly. That will allow you to recalculate the
> > metrics
> > > > over
> > > > > several time spans and during peak periods.
> > > > >
> > > > > Art S. Kagel
> > > > >
> > > > > ----- Original Message -----
> > > > > From: Chris Salch <ids@iiug.org>
> > > > > At: 8/31 14:42:38
> > > > >
> > > > > ( Unfortunatly, none of us thought to save any of that data when
> things
> > > > > started slowing down, so all we've got is what we can remember. )
> > <SNIPPED>
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• A question of load . . .
  CHRIS SALCH -- Thursday, 31 August 2006, at 1:17 p.m.
  • RE: A question of load . . .
    Jack Parker -- Thursday, 31 August 2006, at 1:25 p.m.
  • Re: A question of load . . .
    Eric Rowell -- Thursday, 31 August 2006, at 1:32 p.m.
    • Re: A question of load . . .
      Chris Salch -- Thursday, 31 August 2006, at 2:36 p.m.
      • Re: A question of load . . .
        ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 31 August 2006, at 2:57 p.m.
        • Re: A question of load . . .
          Chris Salch -- Thursday, 31 August 2006, at 6:22 p.m.
          • Re: A question of load . . .
            Chris Salch -- Thursday, 31 August 2006, at 6:28 p.m.
            • Re: A question of load . . .
              ART KAGEL -- Thursday, 31 August 2006, at 9:28 p.m.
            • Re: A question of load . . .
              Keith Simmons -- Friday, 1 September 2006, at 5:39 a.m.
              • Re: A question of load . . .
                ART KAGEL, BLOOMBERG/ 731 LEXIN -- Friday, 1 September 2006, at 9:51 a.m.
                • Re: A question of load . . .
                  Keith Simmons -- Friday, 1 September 2006, at 10:14 a.m.
                  • Re: A question of load . . .
                    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Friday, 1 September 2006, at 10:35 a.m.
                    • RE: A question of load . . .
                      Robert Roussey\(MIS\) -- Friday, 1 September 2006, at 10:45 a.m.
                      • RE: A question of load . . .
                        Paul Watson -- Friday, 1 September 2006, at 10:48 a.m.
                      • RE: A question of load . . .
                        John Miller iii -- Friday, 1 September 2006, at 12:23 p.m.
                    • Re: A question of load . . .
                      Chris Salch -- Friday, 1 September 2006, at 11:11 a.m.
      • Re: A question of load . . .
        Acecpu@aol.com -- Friday, 1 September 2006, at 5:12 a.m.
  • Re: A question of load . . .
    ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 31 August 2006, at 2:42 p.m.
  • Re: A question of load . . .
    Norberto Valverde LLanos -- Thursday, 31 August 2006, at 3:37 p.m.
    • RE: A question of load . . .
      Ford, Andrew G -- Thursday, 31 August 2006, at 3:42 p.m.
      • RE: A question of load . . .
        ART KAGEL, BLOOMBERG/ 731 LEXIN -- Thursday, 31 August 2006, at 3:45 p.m.
  • Re: A question of load . . .
    Dimitar Bachvarov -- Friday, 1 September 2006, at 6:25 a.m.
  • Re: A question of load . . .
    FRANK LAI -- Wednesday, 27 September 2006, at 5:34 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.