 |
IDS Forum
Re: A question of load . . .
Posted By: ART KAGEL, BLOOMBERG/ 731 LEXIN
Date: Friday, 1 September 2006, at 10:35 a.m.
In Response To: Re: A question of load . . . (Keith Simmons)
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.
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 . . .
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 . . .
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.
|
|
