IDS Forum

RE: A question of load . . .

Posted By: John Miller iii
Date: Friday, 1 September 2006, at 12:23 p.m.

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

I would like to add a comment to Art's response. The logging attribute=
to
be buffered or unbuffered logging is not controlled by the create datab=
ase
statement, but the create database statement sets the default mode for =
the
database. Each user has control over the logging attributed buffered
or non-buffered. So for people who are running in a non-buffered loggi=
ng
mode but have batch or restartable programs changing the logging
attribute for just their session to buffered logging can be done.

I was unable to reproduce the problem mention below. I tried starting=
my
version 10 server with a physical log of 96 and my logical log buffer a=
t 32
and it started just fine.

Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 0 48 0 0 0.00

phybegin physize phypos phyused %used

1:263 3500 697 0 0.00

Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-2 0 16 0 0 0 0.0 0.=
0

John

=

"Robert =

Roussey\(MIS\)" =

<Robert.Roussey@S =
To

piritAir.com> ids@iiug.org =

Sent by: =
cc

ids-bounces@iiug. =

org Subj=
ect

RE: A question of load . . . [74=
01]

=

09/01/2006 07:45 =

AM =

=

=

Please respond to =

ids@iiug.org =

=

=

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 no=
t

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 25=
6
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 neede=
d
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 o=
n

average from 75-98.5% of the logical log buffer space is unused. This i=
s
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 numbe=
r
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 th=
e

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! Wit=
h

> 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)*10=
0

> >
> > bufwaits*100 / (pagreads+bufwrits)
> > Bufwaits Ratio (BWR): 2.39% (4819402*100) / (30380617+171168299)
> > Buffer Turnover(Max):2239.43 (pagreads+bufwrits)/(BUFFERS=3D90000)
> > 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 LEXI=
N

> > > 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 =3D (1443280 / (35477827 + 7908428)) * 100.00 =3D 3.3200
> > > > BTR =3D (((35477827 + 7908428) / 90000) / 321.33) =3D 1.5002
> > > > RAU =3D (7071512/(2346196+86301+4646343)) * 100.00 =3D 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 whe=
n

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.