IDS Forum

RE: A question of load . . .

Posted By: Paul Watson
Date: Friday, 1 September 2006, at 10:48 a.m.

In Response To: RE: A question of load . . . (Robert Roussey\(MIS\))

Sounds like an undocumented feature :-)

Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457
Web: www.oninit.com

GO FURTHER with DB2
GET THERE FASTER with Informix.
Attend the IDUG 2006 European Conference.
Vienna, Austria. 2-6 October 2006
Visit http://www.iiug.org/conf for more information.

> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf Of Robert Roussey(MIS)
> Sent: 01 September 2006 09:45
> To: ids@iiug.org
> Subject: RE: A question of load . . . [7401]
>
>
> As a side note to that, IDS 10 seems to require that PHYS log
> buffer and LOGICAL log buffer be the same size.
> And if you don't have them the same, it changes them for you!
> Perhaps JMiller could address that.
>
> Bob Roussey
> Unix / Informix Administration
> Spirit Airlines
> Robert.Roussey@SpiritAir.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf Of ART KAGEL, BLOOMBERG/ 731 LEXIN
> Sent: Friday, September 01, 2006 10:35 AM
> To: ids@iiug.org
> Subject: Re: A question of load . . . [7399]
>
> 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.
>
>
> **************************************************************
> *****************
> 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.