IDS Forum

RE: A question of load . . .

Posted By: Robert Roussey\(MIS\)
Date: Friday, 1 September 2006, at 10:45 a.m.

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

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.

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.