LOGBUFF Setting
A DBA on IDS 11.70 (Solaris) saw repeated online.log warnings that the checkpoint log record may not fit in the logical log buffer, recommending LOGBUFF of at least 130 (it was set to 128), and asked how to size LOGBUFF. Responders explained the warning comes from a conservative worst-case estimate based on the number of user threads/open transactions at checkpoint time (roughly 40 threads per 1KB of LOGBUFF), not from LOGSIZE. Since the system uses unbuffered logging, the buffer is flushed at every commit, so a bigger buffer costs only memory (3 buffers, more with HDR/RSS/SDS) with no extra data-loss risk. Suggestions ranged from 192KB to 512KB; the poster agreed to simply raise LOGBUFF.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, We are running Informix 11.70.FC7W2 on Solaris 11. We have been getting the following message in the online.log. 01/24/17 15:24:55 Checkpoint log record may not fit into the logical log buffer. Recommended minimum value for LOGBUFF is 130. We currently have LOGBUFF set to 128 and RTO_SERVER_RESTART 0. We are running a busy OLTP system with unbuffered DB's. What is the recommended setting for LOGBUFF and how to you calculate it? Thank You, --Dave --f403045ea5243e29980546ec366e
A very conservative calculation is being conducted here, suggesting that=20
every one of your currently existing user threads might have a real=20
transaction (with some modifications) open at the same time. If that=20
would be the case (not likely) and a checkpoint WOULD HAVE to occur in=20
this moment, then this would have to contain a record for each of these=20
transactions which would result in a very large checkpoint log record,=20
potentially larger than your log buffers, in which case the checkpoint and =
whole engine had to fail.
So presumably you'd see well over 5400 threads active (look at onstat -u =
| grep maximum), but as long as you know the majority of them is only=20
reading, or at least not having transactions open at the same time, you=20
could safely ignore the warning.
In case you continue to have this many threads and want to get rid of the=20
warning, take 40 threads per configured 1kB LOGBUFF for reaching at a=20
buffer size large enough to avoid the warning.
HTH,
Andreas
From: "Informix DBA" <in4mixdba@gmail.com>
To: ids@iiug.org
Date: 25.01.2017 15:41
Subject: LOGBUFF Setting [38559]
Sent by: ids-bounces@iiug.org
Hi,=20
We are running Informix 11.70.FC7W2 on Solaris 11. We have been getting=20
the following message in the online.log.=20
01/24/17 15:24:55 Checkpoint log record may not fit into the logical log=20
buffer.=20
Recommended minimum value for LOGBUFF is 130.=20
We currently have LOGBUFF set to 128 and RTO=5FSERVER=5FRESTART 0. We are=20
running a busy OLTP system with unbuffered DB's.=20
What is the recommended setting for LOGBUFF and how to you calculate it?=20
Thank You,=20
--Dave=20
--f403045ea5243e29980546ec366e=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Dave Rule of thumb is that it should be large enough for the system throughput, but not so large that you could not afford to lose that data in the case of a catastrophic server event and you had to recover and roll-forward log files. I too have a busy OLTP system and have LOGBUFF set to 256. You could try 130 but I would suggest a bit bigger and would try 192 (binary-type sizes always work better :-) ). Keith On 25 January 2017 at 14:40, Informix DBA <in4mixdba@gmail.com> wrote: > Hi, > > We are running Informix 11.70.FC7W2 on Solaris 11. We have been getting > the following message in the online.log. > > 01/24/17 15:24:55 Checkpoint log record may not fit into the logical log > buffer. > Recommended minimum value for LOGBUFF is 130. > > We currently have LOGBUFF set to 128 and RTO_SERVER_RESTART 0. We are > running a busy OLTP system with unbuffered DB's. > > What is the recommended setting for LOGBUFF and how to you calculate it? > > Thank You, > > --Dave > > --f403045ea5243e29980546ec366e > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1145ac32c8f2eb0546ed0650
I think you might be mixing up LOGBUF and LOGSIZE. The logical log buffer
is a buffer
that can group logical log records together before writing them to disk.
If you have
unbuffered logging then every commit from an unbuffered logging database
will
write this buffer to disk and not allow the commit to continue until the
write
has completed. So the data is always on disk for every commit.
The warning you are receiving has to do with the size of a single log
record. You can
examine your logs with onlog and look at the size column. Logical Log
records must
fit into a single buffer and in your case you have a record which is larger
than 128K.
If you are not hurting for 1MB of memory I would suggest 512KB. There are
three buffers
so this means you will be using 1.5 MB of memory for the log buffers.
There will be 0
chance of additional loss of data by expanding these buffers, only a chance
of wasting memory.
If these buffers are undersized then there will be allot of extra I/O done.
So increasing
the size in your case my reduce the I/O to the disk the logical logs reside
on.
John F. Miller III
miller3@us.ibm.com
ids-bounces@iiug.org wrote on 01/25/2017 07:38:50 AM:
> From: "Keith Simmons" <smiley73@gmail.com>
> To: ids@iiug.org
> Date: 01/25/2017 07:39 AM
> Subject: Re: LOGBUFF Setting [38561]
> Sent by: ids-bounces@iiug.org
>
> Dave
>
> Rule of thumb is that it should be large enough for the system
throughput,
> but not so large that you could not afford to lose that data in the case
of
> a catastrophic server event and you had to recover and roll-forward log
> files. I too have a busy OLTP system and have LOGBUFF set to 256. You
could
> try 130 but I would suggest a bit bigger and would try 192 (binary-type
> sizes always work better :-) ).
>
> Keith
>
> On 25 January 2017 at 14:40, Informix DBA <in4mixdba@gmail.com> wrote:
>
> > Hi,
> >
> > We are running Informix 11.70.FC7W2 on Solaris 11. We have been getting
> > the following message in the online.log.
> >
> > 01/24/17 15:24:55 Checkpoint log record may not fit into the logical
log
> > buffer.
> > Recommended minimum value for LOGBUFF is 130.
> >
> > We currently have LOGBUFF set to 128 and RTO=5FSERVER=5FRESTART 0. We a=
re
> > running a busy OLTP system with unbuffered DB's.
> >
> > What is the recommended setting for LOGBUFF and how to you calculate
it?
> >
> > Thank You,
> >
> > --Dave
> >
> > --f403045ea5243e29980546ec366e
> >
> >
> > ************************************************************
> > *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a1145ac32c8f2eb0546ed0650
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Since you are running unbuffered logging, in all probability you are not
performing IO on a full log buffer. That means that a larger log buffer size
does not impact either the risk or IO performance. The only impact would be
memory. You can see this by running onstat -l. Chances are your average pages
per flush tends to be fairly low.
If you are running HDR, RSS, or SDS, then that memory impact would be
compounded by the log apply buffers which are used on the secondary servers,
however.
The checkpoint log record contains a bit of information about each of the open
transactions during the checkpoint. If your system tends to have a lot of open
transactions, then the checkpoint log record would need to also be larger.
That's why you are getting the warning message.
Madison Pruet
Retired and Loving it
On Wednesday, January 25, 2017 8:41 AM, Informix DBA <in4mixdba@gmail.com>
wrote:
Hi,
We are running Informix 11.70.FC7W2 on Solaris 11. We have been getting
the following message in the online.log.
01/24/17 15:24:55 Checkpoint log record may not fit into the logical log
buffer.
Recommended minimum value for LOGBUFF is 130.
We currently have LOGBUFF set to 128 and RTO_SERVER_RESTART 0. We are
running a busy OLTP system with unbuffered DB's.
What is the recommended setting for LOGBUFF and how to you calculate it?
Thank You,
--Dave
--f403045ea5243e29980546ec366e
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you all. We are not tight on memory and we can certainly spare a few
MB. I will go ahead and bump up the value of LOGBUFF.
Thank You,
--Dave
On Wed, Jan 25, 2017 at 10:59 AM, John Miller iii <miller3@us.ibm.com>
wrote:
> I think you might be mixing up LOGBUF and LOGSIZE. The logical log buffer
> is a buffer
> that can group logical log records together before writing them to disk.
> If you have
> unbuffered logging then every commit from an unbuffered logging database
> will
> write this buffer to disk and not allow the commit to continue until the
> write
> has completed. So the data is always on disk for every commit.
>
> The warning you are receiving has to do with the size of a single log
> record. You can
> examine your logs with onlog and look at the size column. Logical Log
> records must
> fit into a single buffer and in your case you have a record which is larger
> than 128K.
>
> If you are not hurting for 1MB of memory I would suggest 512KB. There are
> three buffers
> so this means you will be using 1.5 MB of memory for the log buffers.
> There will be 0
> chance of additional loss of data by expanding these buffers, only a chance
> of wasting memory.
> If these buffers are undersized then there will be allot of extra I/O done.
> So increasing
> the size in your case my reduce the I/O to the disk the logical logs reside
> on.
>
> John F. Miller III
> miller3@us.ibm.com
>
> ids-bounces@iiug.org wrote on 01/25/2017 07:38:50 AM:
>
> > From: "Keith Simmons" <smiley73@gmail.com>
> > To: ids@iiug.org
> > Date: 01/25/2017 07:39 AM
> > Subject: Re: LOGBUFF Setting [38561]
> > Sent by: ids-bounces@iiug.org
> >
> > Dave
> >
> > Rule of thumb is that it should be large enough for the system
> throughput,
> > but not so large that you could not afford to lose that data in the case
> of
> > a catastrophic server event and you had to recover and roll-forward log
> > files. I too have a busy OLTP system and have LOGBUFF set to 256. You
> could
> > try 130 but I would suggest a bit bigger and would try 192 (binary-type
> > sizes always work better :-) ).
> >
> > Keith
> >
> > On 25 January 2017 at 14:40, Informix DBA <in4mixdba@gmail.com> wrote:
> >
> > > Hi,
> > >
> > > We are running Informix 11.70.FC7W2 on Solaris 11. We have been getting
>
> > > the following message in the online.log.
> > >
> > > 01/24/17 15:24:55 Checkpoint log record may not fit into the logical
> log
> > > buffer.
> > > Recommended minimum value for LOGBUFF is 130.
> > >
> > > We currently have LOGBUFF set to 128 and RTO=5FSERVER=5FRESTART 0. We
> a=
> re
> > > running a busy OLTP system with unbuffered DB's.
> > >
> > > What is the recommended setting for LOGBUFF and how to you calculate
> it?
> > >
> > > Thank You,
> > >
> > > --Dave
> > >
> > > --f403045ea5243e29980546ec366e
> > >
> > >
> > > ************************************************************
> > > *******************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a1145ac32c8f2eb0546ed0650
> >
> >
> >
> ************************************************************
> ***************=
> ****
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f403045ea52422581e0546f10167
On a busy OLTP system with UNBUFFERED LOGGING, increasing the LOGBUFF size
will hardly improve performance.
My point is simple... if you run "onstat -l" you'll see something like:
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-1 0 64 1083329 101140 34419 10.7
2.9
Subsystem numrecs Log Space used
OLDRSAM 1082915 154473756
SBLOB 11 988
HA 403 17732
But almost for sure in the "pages/io" you'll see 1.x
That's because a busy OLTP system will be constantly COMMITing and
consequently constantly flushing the log buffer.
So, increasing the LOGBUFF will have two effects:
1- Get rid of that message... although it's highly improbable that the
worst case scenario would ever happen, you'll sleep better and that's good
2- You'll be "wasting" the increased size in memory (* 3) which nowadays is
completely irrelevant. If you're using HDR, it will be "* 15" (3 for the
normal buffers and 12 more for the replication buffers - and here I have
some doubts if it will have any effect. Eventually yes, it may reduce your
G flags on the primary, in case you have them....)
So, in conclusion, for the quality of your sleep, just get on with it :)
Regards.
On Wed, Jan 25, 2017 at 3:59 PM, John Miller iii <miller3@us.ibm.com> wrote:
> I think you might be mixing up LOGBUF and LOGSIZE. The logical log buffer
> is a buffer
> that can group logical log records together before writing them to disk.
> If you have
> unbuffered logging then every commit from an unbuffered logging database
> will
> write this buffer to disk and not allow the commit to continue until the
> write
> has completed. So the data is always on disk for every commit.
>
> The warning you are receiving has to do with the size of a single log
> record. You can
> examine your logs with onlog and look at the size column. Logical Log
> records must
> fit into a single buffer and in your case you have a record which is larger
> than 128K.
>
> If you are not hurting for 1MB of memory I would suggest 512KB. There are
> three buffers
> so this means you will be using 1.5 MB of memory for the log buffers.
> There will be 0
> chance of additional loss of data by expanding these buffers, only a chance
> of wasting memory.
> If these buffers are undersized then there will be allot of extra I/O done.
> So increasing
> the size in your case my reduce the I/O to the disk the logical logs reside
> on.
>
> John F. Miller III
> miller3@us.ibm.com
>
> ids-bounces@iiug.org wrote on 01/25/2017 07:38:50 AM:
>
> > From: "Keith Simmons" <smiley73@gmail.com>
> > To: ids@iiug.org
> > Date: 01/25/2017 07:39 AM
> > Subject: Re: LOGBUFF Setting [38561]
> > Sent by: ids-bounces@iiug.org
> >
> > Dave
> >
> > Rule of thumb is that it should be large enough for the system
> throughput,
> > but not so large that you could not afford to lose that data in the case
> of
> > a catastrophic server event and you had to recover and roll-forward log
> > files. I too have a busy OLTP system and have LOGBUFF set to 256. You
> could
> > try 130 but I would suggest a bit bigger and would try 192 (binary-type
> > sizes always work better :-) ).
> >
> > Keith
> >
> > On 25 January 2017 at 14:40, Informix DBA <in4mixdba@gmail.com> wrote:
> >
> > > Hi,
> > >
> > > We are running Informix 11.70.FC7W2 on Solaris 11. We have been getting
>
> > > the following message in the online.log.
> > >
> > > 01/24/17 15:24:55 Checkpoint log record may not fit into the logical
> log
> > > buffer.
> > > Recommended minimum value for LOGBUFF is 130.
> > >
> > > We currently have LOGBUFF set to 128 and RTO=5FSERVER=5FRESTART 0. We
> a=
> re
> > > running a busy OLTP system with unbuffered DB's.
> > >
> > > What is the recommended setting for LOGBUFF and how to you calculate
> it?
> > >
> > > Thank You,
> > >
> > > --Dave
> > >
> > > --f403045ea5243e29980546ec366e
> > >
> > >
> > > ************************************************************
> > > *******************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a1145ac32c8f2eb0546ed0650
> >
> >
> >
> ************************************************************
> ***************=
> ****
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001a113fd9a27cb6e10546f509ec