LOGBUFF size
Frank saw occasional sessions in "G" (waiting on the logical-log buffer) despite LOGBUFF set to 256KB, and asked whether raising it further would help. Fernando Nunes and Art Kagel explained that with unbuffered (or ANSI) logging the buffer is flushed at every COMMIT, so it rarely fills and LOGBUFF size makes little difference; check onstat -l to see pages used per flush. Advice: ignore rare G flags, put logical logs on the fastest/least busy disk, or selectively use SET BUFFERED LOG for sessions that can tolerate losing a commit (HDR buffers were also mentioned as a possible cause). Frank accepted that it was harmless for now.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
We set ,
LOGBUFF 256
Occasionally, I still see this sometimes,
58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
1 0 0
580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
2 29 48411
58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
1 116 14986
So, a session is waiting for the log buffer .
The doc ( below) seems not to recommend to set it higher or .... .
What do you think or comments ?
Thanks
Frank
LOGBUFF configuration parameter
Use the LOGBUFF configuration parameter to specify the size in kilobytes
for the three logical-log buffers in shared memory.
onconfig.std value LOGBUFF 64 units Kilobytes values An integer in the
range of 32 - (32767 * pagesize / 1024), where pagesize is the default
system page size. The value must be evenly divisible by the default system
page size. If the value is not evenly divisible by the page size, the
database server rounds down the size to the nearest value that is evenly
divisible by the page size. takes effect After you edit your onconfig file
and restart the database server.
Usage
The three logical log buffers permit user threads to write to the active
buffer while one of the other buffers is being flushed to disk. If flushing
is not complete by the time the active buffer fills, the user thread begins
writing to the third buffer.
If the RTO_SERVER_RESTART configuration parameter is enabled, set the value
of the LOGBUFF configuration parameter to 256 kilobytes. If the value of
the LOGBUFF configuration parameter is less than 256 kilobytes, a warning
message displays when you restart the server.
Otherwise, set the value of the LOGBUFF configuration parameter to 32
kilobytes for standard workloads or 64 kilobytes for heavy workloads. The
database server uses the LOGBUFF parameter to set the size of internal
buffers that are used during recovery. If you set LOGBUFF too high, the
database server can run out of memory and shut down during recovery.
If you log user data in smart large objects, increase the size of the log
buffer to make the system more efficient. The database server logs only the
portion of a smart-large-object page that changed.
You can view information about the logical log buffers by running the onstat
-l command.
*Parent topic:* Database configuration parameters
<http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_
adr_0007.htm?lang=en-us>
*Related reference*:
onstat -l command: Print physical and logical log information
<http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_
adr_0598.htm?lang=en-us>
--001a11c1728c309b400533c148a4
A couple of questions:
1- Do you have HDR?
2- What is the logmode of your databases? Buffered, Unbuffered?
The reasons behind the questions are:
- If you hae HDR, when the buffers are flushed in the primary (to disk)
they are also copied to the "HDR buffers". We have 12 of these but
occasionally if the secondary or the network is dragging they may fill up
and cause waits on the primary
- If you have unbuffered logging like most customers do, the size of the
LOGBUFF becomes rather irrelevant because most of the times it will never
get full. Because each time a transaction is closed the buffer is flushed.
And this may cause some extra contention.... And there's little you can
do... the options would be:
a) Change your databases to buffered logging -> BAD IDEA as you may cause
inconsistencies between the database and external systems
b) Change the buffer mode at session level.... sessions that could live
with buffered can be set to it. But the other sessions will continue to
force flushing
c) Put your logs on the fasted disk you have... keepin mind that seek
timemay not be very important and the writes are sequential... the number
of IOPs is crucial
Now... after describing a bad scenario.... what really i your problem? A
few sessions with G flag once in a while? If yes, ignore it... no one will
notice...
Keep in mind that the flush occurs many times per second in an heavily used
systems.... When you see a session in "G" chances are it went htough many
other "G" cycles before you process that information...
Now... if you want this solved, please vote on this feature:
http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=45166
It would help.... The basic idea is simple and in some formi already used
in DB2 and Oracle. SQL Server seems to have implemented what we have in one
of the latest releases:
The problem of buffering is that you can tell the application a COMMIT was
ok, when you still can loose it. So why not BUFFER anyway and delay the
answer to the application until you actually flush?
The implementation can't be that simple... the worst case scenario would be
a single application running on a server... the buffer would never fill, so
it would be stuck... some sort of timer must be implemented to force the
flush if meanwhile the buffer hasn't fill up.
Regards
On Thu, May 26, 2016 at 5:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Hi,
>
> We set ,
>
> LOGBUFF 256>
> Occasionally, I still see this sometimes,
>
> 58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
> 1 0 0
> 580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
> 2 29 48411
> 58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
> 1 116 14986
>
> So, a session is waiting for the log buffer .
>
> The doc ( below) seems not to recommend to set it higher or .... .
>
> What do you think or comments ?
>
> Thanks
> Frank
>
> LOGBUFF configuration parameter>
> Use the LOGBUFF configuration parameter to specify the size in kilobytes
> for the three logical-log buffers in shared memory.
> onconfig.std value LOGBUFF 64 units Kilobytes values An integer in the
> range of 32 - (32767 * pagesize / 1024), where pagesize is the default
> system page size. The value must be evenly divisible by the default system
> page size. If the value is not evenly divisible by the page size, the
> database server rounds down the size to the nearest value that is evenly
> divisible by the page size. takes effect After you edit your onconfig file
> and restart the database server.
> Usage
>
> The three logical log buffers permit user threads to write to the active
> buffer while one of the other buffers is being flushed to disk. If flushing
> is not complete by the time the active buffer fills, the user thread begins
> writing to the third buffer.
>
> If the RTO_SERVER_RESTART configuration parameter is enabled, set the value
> of the LOGBUFF configuration parameter to 256 kilobytes. If the value of
> the LOGBUFF configuration parameter is less than 256 kilobytes, a warning
> message displays when you restart the server.
>
> Otherwise, set the value of the LOGBUFF configuration parameter to 32
> kilobytes for standard workloads or 64 kilobytes for heavy workloads. The
> database server uses the LOGBUFF parameter to set the size of internal
> buffers that are used during recovery. If you set LOGBUFF too high, the
> database server can run out of memory and shut down during recovery.
>
> If you log user data in smart large objects, increase the size of the log
> buffer to make the system more efficient. The database server logs only the
> portion of a smart-large-object page that changed.
>
> You can view information about the logical log buffers by running the
> onstat
> -l command.> *Parent topic:* Database configuration parameters
>
> <
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0007.htm?lang=en-us
> >
> *Related reference*:
> onstat -l command: Print physical and logical log information>
> <
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0598.htm?lang=en-us
> >
>
> --001a11c1728c309b400533c148a4
>
>
>
>
*******************************************************************************
> 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...
--001a1146d40e5dfa2f0533c21b43
HI, Fernando,
Our database is No HDR and Unbuffered logging.
I think , as you suggested, I can ignore it for now.
This is not happening often. It happens just sometimes with some busier
update jobs involved , it did not last long in our current database.
Yes, no one noticed it.
We will have three times of more volumes of transactions soon, I just
want to make sure it would not get worse next. By the way, we have lot of
memory ( hundred of GBs), we can give it as much as it needs :-)
This is the first time I heard the size of LOGBUFF is irrelevant for
unbuffered logging, interesting ...
Thanks
Frank
On Thu, May 26, 2016 at 1:27 PM, Fernando Nunes <domusonline@gmail.com>
wrote:
> A couple of questions:
>
> 1- Do you have HDR?
> 2- What is the logmode of your databases? Buffered, Unbuffered?
>
> The reasons behind the questions are:
>
> - If you hae HDR, when the buffers are flushed in the primary (to disk)
> they are also copied to the "HDR buffers". We have 12 of these but
> occasionally if the secondary or the network is dragging they may fill up
> and cause waits on the primary
>
> - If you have unbuffered logging like most customers do, the size of the
> LOGBUFF becomes rather irrelevant because most of the times it will never
> get full. Because each time a transaction is closed the buffer is flushed.
> And this may cause some extra contention.... And there's little you can
> do... the options would be:
>
> a) Change your databases to buffered logging -> BAD IDEA as you may cause
> inconsistencies between the database and external systems
> b) Change the buffer mode at session level.... sessions that could live
> with buffered can be set to it. But the other sessions will continue to
> force flushing
> c) Put your logs on the fasted disk you have... keepin mind that seek
> timemay not be very important and the writes are sequential... the number
> of IOPs is crucial
>
> Now... after describing a bad scenario.... what really i your problem? A
> few sessions with G flag once in a while? If yes, ignore it... no one will
> notice...
> Keep in mind that the flush occurs many times per second in an heavily used
> systems.... When you see a session in "G" chances are it went htough many
> other "G" cycles before you process that information...
>
> Now... if you want this solved, please vote on this feature:
>
> http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=45166
>
> It would help.... The basic idea is simple and in some formi already used
> in DB2 and Oracle. SQL Server seems to have implemented what we have in one
> of the latest releases:
>
> The problem of buffering is that you can tell the application a COMMIT was
> ok, when you still can loose it. So why not BUFFER anyway and delay the
> answer to the application until you actually flush?
> The implementation can't be that simple... the worst case scenario would be
> a single application running on a server... the buffer would never fill, so
> it would be stuck... some sort of timer must be implemented to force the
> flush if meanwhile the buffer hasn't fill up.
>
> Regards
>
> On Thu, May 26, 2016 at 5:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Hi,
> >
> > We set ,
> >
> > LOGBUFF 256> >
> > Occasionally, I still see this sometimes,
> >
> > 58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
> > 1 0 0
> > 580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
> > 2 29 48411
> > 58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
> > 1 116 14986
> >
> > So, a session is waiting for the log buffer .
> >
> > The doc ( below) seems not to recommend to set it higher or .... .
> >
> > What do you think or comments ?
> >
> > Thanks
> > Frank
> >
> > LOGBUFF configuration parameter> >
> > Use the LOGBUFF configuration parameter to specify the size in kilobytes
> > for the three logical-log buffers in shared memory.
> > onconfig.std value LOGBUFF 64 units Kilobytes values An integer in the
> > range of 32 - (32767 * pagesize / 1024), where pagesize is the default
> > system page size. The value must be evenly divisible by the default
> system
> > page size. If the value is not evenly divisible by the page size, the
> > database server rounds down the size to the nearest value that is evenly
> > divisible by the page size. takes effect After you edit your onconfig
> file
> > and restart the database server.
> > Usage
> >
> > The three logical log buffers permit user threads to write to the active
> > buffer while one of the other buffers is being flushed to disk. If
> flushing
> > is not complete by the time the active buffer fills, the user thread
> begins
> > writing to the third buffer.
> >
> > If the RTO_SERVER_RESTART configuration parameter is enabled, set the
> value
> > of the LOGBUFF configuration parameter to 256 kilobytes. If the value of
> > the LOGBUFF configuration parameter is less than 256 kilobytes, a warning
> > message displays when you restart the server.
> >
> > Otherwise, set the value of the LOGBUFF configuration parameter to 32
> > kilobytes for standard workloads or 64 kilobytes for heavy workloads. The
> > database server uses the LOGBUFF parameter to set the size of internal
> > buffers that are used during recovery. If you set LOGBUFF too high, the
> > database server can run out of memory and shut down during recovery.
> >
> > If you log user data in smart large objects, increase the size of the log
> > buffer to make the system more efficient. The database server logs only
> the
> > portion of a smart-large-object page that changed.
> >
> > You can view information about the logical log buffers by running the
> > onstat
> > -l command.> > *Parent topic:* Database configuration parameters
> >
> > <
> >
>
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0007.htm?lang=en-us
> > >
> > *Related reference*:
> > onstat -l command: Print physical and logical log information> >
> > <
> >
>
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0598.htm?lang=en-us
> > >
> >
> > --001a11c1728c309b400533c148a4
> >
> >
> >
> >
>
>
*******************************************************************************
> > 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...
>
> --001a1146d40e5dfa2f0533c21b43
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a114222c8ddb0220533c2eb82
"irrelevant" may be a too strong work...
But do a teste...run "onstat -l"
Somewhere near the top you'll see on average how many pages were used when
it was flushed... in busy OLTP systems this is often around 1.x... Not even
two pages.
Ceck your values...
Again... for those busy jobs... if you think you could live with a
potential "COMMIT" that is lost... and if the do many COMMITS, try to run
them with "SET BUFFEED LOG".
This would make their COMMITS NOT to force a flush....
I had the idea of writing an article about this... not sure if I did....
Time permitting I must get back to it...
Regards
On Thu, May 26, 2016 at 7:26 PM, FRANK <yunyaoqu@gmail.com> wrote:
> HI, Fernando,
>
> Our database is No HDR and Unbuffered logging.
>
> I think , as you suggested, I can ignore it for now.
>
> This is not happening often. It happens just sometimes with some busier
> update jobs involved , it did not last long in our current database.
> Yes, no one noticed it.
>
> We will have three times of more volumes of transactions soon, I just
> want to make sure it would not get worse next. By the way, we have lot of
> memory ( hundred of GBs), we can give it as much as it needs :-)
>
> This is the first time I heard the size of LOGBUFF is irrelevant for
> unbuffered logging, interesting ...
>
> Thanks
> Frank
>
> On Thu, May 26, 2016 at 1:27 PM, Fernando Nunes <domusonline@gmail.com>
> wrote:
>
> > A couple of questions:
> >
> > 1- Do you have HDR?
> > 2- What is the logmode of your databases? Buffered, Unbuffered?
> >
> > The reasons behind the questions are:
> >
> > - If you hae HDR, when the buffers are flushed in the primary (to disk)
> > they are also copied to the "HDR buffers". We have 12 of these but
> > occasionally if the secondary or the network is dragging they may fill up
> > and cause waits on the primary
> >
> > - If you have unbuffered logging like most customers do, the size of the
> > LOGBUFF becomes rather irrelevant because most of the times it will never
> > get full. Because each time a transaction is closed the buffer is
> flushed.
> > And this may cause some extra contention.... And there's little you can
> > do... the options would be:
> >
> > a) Change your databases to buffered logging -> BAD IDEA as you may cause
> > inconsistencies between the database and external systems
> > b) Change the buffer mode at session level.... sessions that could live
> > with buffered can be set to it. But the other sessions will continue to
> > force flushing
> > c) Put your logs on the fasted disk you have... keepin mind that seek
> > timemay not be very important and the writes are sequential... the number
> > of IOPs is crucial
> >
> > Now... after describing a bad scenario.... what really i your problem? A
> > few sessions with G flag once in a while? If yes, ignore it... no one
> will
> > notice...
> > Keep in mind that the flush occurs many times per second in an heavily
> used
> > systems.... When you see a session in "G" chances are it went htough many
> > other "G" cycles before you process that information...
> >
> > Now... if you want this solved, please vote on this feature:
> >
> >
> http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=45166
> >
> > It would help.... The basic idea is simple and in some formi already used
> > in DB2 and Oracle. SQL Server seems to have implemented what we have in
> one
> > of the latest releases:
> >
> > The problem of buffering is that you can tell the application a COMMIT
> was
> > ok, when you still can loose it. So why not BUFFER anyway and delay the
> > answer to the application until you actually flush?
> > The implementation can't be that simple... the worst case scenario would
> be
> > a single application running on a server... the buffer would never fill,
> so
> > it would be stuck... some sort of timer must be implemented to force the
> > flush if meanwhile the buffer hasn't fill up.
> >
> > Regards
> >
> > On Thu, May 26, 2016 at 5:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > > Hi,
> > >
> > > We set ,
> > >
> > > LOGBUFF 256> > >
> > > Occasionally, I still see this sometimes,
> > >
> > > 58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
> > > 1 0 0
> > > 580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
> > > 2 29 48411
> > > 58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
> > > 1 116 14986
> > >
> > > So, a session is waiting for the log buffer .
> > >
> > > The doc ( below) seems not to recommend to set it higher or .... .
> > >
> > > What do you think or comments ?
> > >
> > > Thanks
> > > Frank
> > >
> > > LOGBUFF configuration parameter> > >
> > > Use the LOGBUFF configuration parameter to specify the size in
> kilobytes
> > > for the three logical-log buffers in shared memory.
> > > onconfig.std value LOGBUFF 64 units Kilobytes values An integer in the
> > > range of 32 - (32767 * pagesize / 1024), where pagesize is the default
> > > system page size. The value must be evenly divisible by the default
> > system
> > > page size. If the value is not evenly divisible by the page size, the
> > > database server rounds down the size to the nearest value that is
> evenly
> > > divisible by the page size. takes effect After you edit your onconfig
> > file
> > > and restart the database server.
> > > Usage
> > >
> > > The three logical log buffers permit user threads to write to the
> active
> > > buffer while one of the other buffers is being flushed to disk. If
> > flushing
> > > is not complete by the time the active buffer fills, the user thread
> > begins
> > > writing to the third buffer.
> > >
> > > If the RTO_SERVER_RESTART configuration parameter is enabled, set the
> > value
> > > of the LOGBUFF configuration parameter to 256 kilobytes. If the value
> of
> > > the LOGBUFF configuration parameter is less than 256 kilobytes, a
> warning
> > > message displays when you restart the server.
> > >
> > > Otherwise, set the value of the LOGBUFF configuration parameter to 32
> > > kilobytes for standard workloads or 64 kilobytes for heavy workloads.
> The
> > > database server uses the LOGBUFF parameter to set the size of internal
> > > buffers that are used during recovery. If you set LOGBUFF too high, the
> > > database server can run out of memory and shut down during recovery.
> > >
> > > If you log user data in smart large objects, increase the size of the
> log
> > > buffer to make the system more efficient. The database server logs only
> > the
> > > portion of a smart-large-object page that changed.
> > >
> > > You can view information about the logical log buffers by running the
> > > onstat
> > > -l command.> > > *Parent topic:* Database configuration parameters
> > >
> > > <
> > >
> >
> >
>
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0007.htm?lang=en-us
> > > >
> > > *Related reference*:
> > > onstat -l command: Print physical and logical log in
I wrote a little bit about it back in 2010 when I was messing around with an
OLTP benchmark.
http://www.informix-dba.com/2010/05/oltp-benchmark-cheats.html
Setting buffered logging improved my Transactions Per Minute by 10%.
YMMV of course. All of my sessions had buffered logging enabled so I didn't
have any unbuffered logging sessions forcing my partially full log buffers
to disk.
Andrew
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Fernando Nunes
Sent: Thursday, May 26, 2016 2:18 PM
To: ids@iiug.org
Subject: Re: LOGBUFF size [37198]
"irrelevant" may be a too strong work...
But do a teste...run "onstat -l"
Somewhere near the top you'll see on average how many pages were used when
it was flushed... in busy OLTP systems this is often around 1.x... Not even
two pages.
Ceck your values...
Again... for those busy jobs... if you think you could live with a potential
"COMMIT" that is lost... and if the do many COMMITS, try to run them with
"SET BUFFEED LOG".
This would make their COMMITS NOT to force a flush....
I had the idea of writing an article about this... not sure if I did....
Time permitting I must get back to it...
Regards
On Thu, May 26, 2016 at 7:26 PM, FRANK <yunyaoqu@gmail.com> wrote:
> HI, Fernando,
>
> Our database is No HDR and Unbuffered logging.
>
> I think , as you suggested, I can ignore it for now.
>
> This is not happening often. It happens just sometimes with some
> busier update jobs involved , it did not last long in our current
database.
> Yes, no one noticed it.
>
> We will have three times of more volumes of transactions soon, I just
> want to make sure it would not get worse next. By the way, we have lot
> of memory ( hundred of GBs), we can give it as much as it needs :-)
>
> This is the first time I heard the size of LOGBUFF is irrelevant for
> unbuffered logging, interesting ...
>
> Thanks
> Frank
>
> On Thu, May 26, 2016 at 1:27 PM, Fernando Nunes
> <domusonline@gmail.com>
> wrote:
>
> > A couple of questions:
> >
> > 1- Do you have HDR?
> > 2- What is the logmode of your databases? Buffered, Unbuffered?
> >
> > The reasons behind the questions are:
> >
> > - If you hae HDR, when the buffers are flushed in the primary (to
> > disk) they are also copied to the "HDR buffers". We have 12 of these
> > but occasionally if the secondary or the network is dragging they
> > may fill up and cause waits on the primary
> >
> > - If you have unbuffered logging like most customers do, the size of
> > the LOGBUFF becomes rather irrelevant because most of the times it
> > will never get full. Because each time a transaction is closed the
> > buffer is
> flushed.
> > And this may cause some extra contention.... And there's little you
> > can do... the options would be:
> >
> > a) Change your databases to buffered logging -> BAD IDEA as you may
> > cause inconsistencies between the database and external systems
> > b) Change the buffer mode at session level.... sessions that could
> > live with buffered can be set to it. But the other sessions will
> > continue to force flushing
> > c) Put your logs on the fasted disk you have... keepin mind that
> > seek timemay not be very important and the writes are sequential...
> > the number of IOPs is crucial
> >
> > Now... after describing a bad scenario.... what really i your
> > problem? A few sessions with G flag once in a while? If yes, ignore
> > it... no one
> will
> > notice...
> > Keep in mind that the flush occurs many times per second in an
> > heavily
> used
> > systems.... When you see a session in "G" chances are it went htough
> > many other "G" cycles before you process that information...
> >
> > Now... if you want this solved, please vote on this feature:
> >
> >
> http://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=4
> 5166
> >
> > It would help.... The basic idea is simple and in some formi already
> > used in DB2 and Oracle. SQL Server seems to have implemented what we
> > have in
> one
> > of the latest releases:
> >
> > The problem of buffering is that you can tell the application a
> > COMMIT
> was
> > ok, when you still can loose it. So why not BUFFER anyway and delay
> > the answer to the application until you actually flush?
> > The implementation can't be that simple... the worst case scenario
> > would
> be
> > a single application running on a server... the buffer would never
> > fill,
> so
> > it would be stuck... some sort of timer must be implemented to force
> > the flush if meanwhile the buffer hasn't fill up.
> >
> > Regards
> >
> > On Thu, May 26, 2016 at 5:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > > Hi,
> > >
> > > We set ,
> > >
> > > LOGBUFF 256> > >
> > > Occasionally, I still see this sometimes,
> > >
> > > 58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
> > > 1 0 0
> > > 580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
> > > 2 29 48411
> > > 58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
> > > 1 116 14986
> > >
> > > So, a session is waiting for the log buffer .
> > >
> > > The doc ( below) seems not to recommend to set it higher or .... .
> > >
> > > What do you think or comments ?
> > >
> > > Thanks
> > > Frank
> > >
> > > LOGBUFF configuration parameter> > >
> > > Use the LOGBUFF configuration parameter to specify the size in
> kilobytes
> > > for the three logical-log buffers in shared memory.
> > > onconfig.std value LOGBUFF 64 units Kilobytes values An integer in
> > > the range of 32 - (32767 * pagesize / 1024), where pagesize is the
> > > default system page size. The value must be evenly divisible by
> > > the default
> > system
> > > page size. If the value is not evenly divisible by the page size,
> > > the database server rounds down the size to the nearest value that
> > > is
> evenly
> > > divisible by the page size. takes effect After you edit your
> > > onconfig
> > file
> > > and restart the database server.
> > > Usage
> > >
> > > The three logical log buffers permit user threads to write to the
> active
> > > buffer while one of the other buffers is being flushed to disk. If
> > flushing
> > > is not complete by the time the active buffer fills, the user
> > > thread
> > begins
> > > writing to the third buffer.
> > >
> > > If the RTO_SERVER_RESTART configuration parameter is enabled, set
> > > the
> > value
> > > of the LOGBUFF configuration parameter to 256 kilobytes. If the
> > > value
> of
> > > the LOGBUFF configuration parameter is less than 256 kilobytes, a
> warning
> > > message displays when you restart the server.
> > >
> > > Otherwise, set the value of the LOGBUFF configuration parameter to
> > > 32 kilobytes for standard workloads or 64 kilobytes for heavy
workloads.
> The
> > > database server uses the LOGBUFF parameter to set the size of
> > > internal buffers that are used during recovery. If yo
First, if your databases are UNBUFFERED or ANSI mode logged then the size
of the logical log buffer is not important and increasing it will not
improve performance at all since the buffer is flushed whenever a COMMIT
record is written to it not when it is filled.
That said, even if all of your databases are BUFFERED log, the write
performance of the chunks containing the logical log is the biggest factor
in determining whether the logical log buffers flush quickly enough to
prevent sessions from having to wait for them to flush. In a BUFFERED log
situation, bigger logical log buffers will sometimes help here, but keep in
mind that the bigger the buffer is the more data is at risk for
catastrophic loss if the server crashes with many transactions still
unflushed. You have to balance safety with performance.
Recommendation: Move the logical logs to a faster or less active disk
structure.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, May 26, 2016 at 12:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Hi,
>
> We set ,
>
> LOGBUFF 256>
> Occasionally, I still see this sometimes,
>
> 58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
> 1 0 0
> 580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
> 2 29 48411
> 58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
> 1 116 14986
>
> So, a session is waiting for the log buffer .
>
> The doc ( below) seems not to recommend to set it higher or .... .
>
> What do you think or comments ?
>
> Thanks
> Frank
>
> LOGBUFF configuration parameter>
> Use the LOGBUFF configuration parameter to specify the size in kilobytes
> for the three logical-log buffers in shared memory.
> onconfig.std value LOGBUFF 64 units Kilobytes values An integer in the
> range of 32 - (32767 * pagesize / 1024), where pagesize is the default
> system page size. The value must be evenly divisible by the default system
> page size. If the value is not evenly divisible by the page size, the
> database server rounds down the size to the nearest value that is evenly
> divisible by the page size. takes effect After you edit your onconfig file
> and restart the database server.
> Usage
>
> The three logical log buffers permit user threads to write to the active
> buffer while one of the other buffers is being flushed to disk. If flushing
> is not complete by the time the active buffer fills, the user thread begins
> writing to the third buffer.
>
> If the RTO_SERVER_RESTART configuration parameter is enabled, set the value
> of the LOGBUFF configuration parameter to 256 kilobytes. If the value of
> the LOGBUFF configuration parameter is less than 256 kilobytes, a warning
> message displays when you restart the server.
>
> Otherwise, set the value of the LOGBUFF configuration parameter to 32
> kilobytes for standard workloads or 64 kilobytes for heavy workloads. The
> database server uses the LOGBUFF parameter to set the size of internal
> buffers that are used during recovery. If you set LOGBUFF too high, the
> database server can run out of memory and shut down during recovery.
>
> If you log user data in smart large objects, increase the size of the log
> buffer to make the system more efficient. The database server logs only the
> portion of a smart-large-object page that changed.
>
> You can view information about the logical log buffers by running the
> onstat
> -l command.> *Parent topic:* Database configuration parameters
>
> <
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0007.htm?lang=en-us
> >
> *Related reference*:
> onstat -l command: Print physical and logical log information>
> <
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0598.htm?lang=en-us
> >
>
> --001a11c1728c309b400533c148a4
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--94eb2c05be5642dd140533c48276
Hi, Fernando,
I tried "onstat -l" a couple of times( see below for its top part. today
might not be a busy day). I saw buffer waiting some times
I am very interested in your paper if available!
Thanks
Frank
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:12:22 -- 19140800 KbytesPhysical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-2 121 128 29978045 237725 126.10
phybegin physize phypos phyused %used
2:53 1950000 6520 4015 0.21
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-1 0 128 1053794263 129546559 106542993 8.1 1.2
Subsystem numrecs Log Space used
OLDRSAM 1053523516 124822591348
SBLOB 268379 40393852
CDR 111 6216
HA 2257 99308
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:13:07 -- 19140800 KbytesPhysical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 107 128 29978941 237732 126.10
phybegin physize phypos phyused %used
2:53 1950000 7416 4897 0.25
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-2 9 128 1053872795 129553345 106547469 8.1 1.2
Subsystem numrecs Log Space used
OLDRSAM 1053602048 124831042356
SBLOB 268379 40393852
CDR 111 6216
HA 2257 99308
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:15:08 -- 19140800 KbytesPhysical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 43 128 29985168 237783 126.10
phybegin physize phypos phyused %used
2:53 1950000 13771 1826 0.09
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-3 0 128 1054240644 129578725 106557089 8.1 1.2
Subsystem numrecs Log Space used
OLDRSAM 1053969896 124872195792
SBLOB 268379 40393852
CDR 111 6216
HA 2258 99352
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:15:29 -- 19140800 KbytesPhysical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-2 34 128 29985808 237788 126.10
phybegin physize phypos phyused %used
2:53 1950000 14411 2457 0.13
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-1 9 128 1054283654 129582401 106558728 8.1 1.2
Subsystem numrecs Log Space used
OLDRSAM 1054012906 124877581612
SBLOB 268379 40393852
CDR 111 6216
HA 2258 99352
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:17:21 -- 19140800 KbytesPhysical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 37 128 29989907 237820 126.10
phybegin physize phypos phyused %used
2:53 1950000 18642 6691 0.34
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-3 0 128 1054691356 129616696 106577152 8.1 1.2
Subsystem numrecs Log Space used
OLDRSAM 1054399845 124920773552
SBLOB 289142 47318172
CDR 111 6216
HA 2258 99352
Buffer Waiting
Buffer ioproc flags
L-2 0 0x61 0
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:18:37 -- 19140800 KbytesPhysical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 49 128 29994283 237854 126.10
phybegin physize phypos phyused %used
2:53 1950000 23015 11076 0.57
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-2 0 128 1054944817 129649345 106606670 8.1 1.2
Subsystem numrecs Log Space used
OLDRSAM 1054653306 124947683616
SBLOB 289142 47318172
CDR 111 6216
HA 2258 99352
Buffer Waiting
Buffer ioproc flags
L-1 0 0x61 0
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
20:19:03 -- 19140800 KbytesPhysical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-2 48 128 29996341 237870 126.10
phybegin physize phypos phyused %used
2:53 1950000 25201 13261 0.68
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages
pages/io
L-2 0 128 1055095956 129667578 106622661 8.1 1.2
Subsystem numrecs Log Space used
OLDRSAM 1054804445 124964063960
SBLOB 289142 47318172
CDR 111 6216
HA 2258 99352
On Thu, May 26, 2016 at 3:18 PM, Fernando Nunes <domusonline@gmail.com>
wrote:
> "irrelevant" may be a too strong work...
> But do a teste...run "onstat -l"
> Somewhere near the top you'll see on average how many pages were used when
> it was flushed... in busy OLTP systems this is often around 1.x... Not even
> two pages.
> Ceck your values...
>
> Again... for those busy jobs... if you think you could live with a
> potential "COMMIT" that is lost... and if the do many COMMITS, try to run
> them with "SET BUFFEED LOG".
> This would make their COMMITS NOT to force a flush....
> I had the idea of writing an article about this... not sure if I did....
> Time permitting I must get back to it...
> Regards
>
> On Thu, May 26, 2016 at 7:26 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > HI, Fernando,
> >
> > Our database is No HDR and Unbuffered logging.
> >
> > I think , as you suggested, I can ignore it for now.
> >
> > This is not happening often. It happens just sometimes with some busier
> > update jobs involved , it did not last long in our current database.
> > Yes, no one noticed it.
> >
> > We will have three times of more volumes of transactions soon, I just
> > want to make sure it would not get worse next. By the way, we have lot of
> > memory ( hundred of GBs), we can give it as much as it needs :-)
> >
> > This is the first time I heard the size of LOGBUFF is irrelevant for
> > unbuffered logging, interesting ...
> >
> > Thanks
> > Frank
> >
> > On Thu, May 26, 2016 at 1:27 PM, Fernando Nunes <domusonline@gmail.com>
> > wrote:
> >
> > > A couple of questions:
> > >
> > > 1- Do you have HDR?
> > > 2- What is the logmode of your databases? Buffered, Unbuffered?
> > >
> > > The reasons behind the questions are:
> > >
> > > - If you hae HDR, when the buffers are flushed in the primary (to disk)
> > > they are also copied to the "HDR buffers". We have 12 of these but
> > > occasionally if the secondary or the network is dragging they may fill
> up
> > > and cause waits on the primary
> > >
> > > - If you have unbuffered logging like most customers do, the size of
> the
> > > LOGBUFF becomes rather irrelevant because most of the times it will
> never
> > > get full. Because each time a transaction is closed the buffer is
> > flushed.
> > > And this may cause some extra contention.... And there's little you can
> > > do... the options would be:
> > >
> > > a) Change your databases to buffered logging -> BAD IDEA as you may
> cause
> > > inconsistencies between the database and external systems
> > > b) Change the buffer mode at session level.... sessio
Thanks Art and Andrew for the info!
We have a lot of small transactions , about one million per hour.
So, when each COMMIT trigger a buffer flush, it would have a high
frequent IO flush.... ( reducing this might help bigger ??)
Our system is quite reliable( no frequent crash) , as Andrew
benchmarked, buffered logging might help ... to be explored ...
Thanks
Frank
On Thu, May 26, 2016 at 4:19 PM, Art Kagel <art.kagel@gmail.com> wrote:
> First, if your databases are UNBUFFERED or ANSI mode logged then the size
> of the logical log buffer is not important and increasing it will not
> improve performance at all since the buffer is flushed whenever a COMMIT
> record is written to it not when it is filled.
>
> That said, even if all of your databases are BUFFERED log, the write
> performance of the chunks containing the logical log is the biggest factor
> in determining whether the logical log buffers flush quickly enough to
> prevent sessions from having to wait for them to flush. In a BUFFERED log
> situation, bigger logical log buffers will sometimes help here, but keep in
> mind that the bigger the buffer is the more data is at risk for
> catastrophic loss if the server crashes with many transactions still
> unflushed. You have to balance safety with performance.
>
> Recommendation: Move the logical logs to a faster or less active disk
> structure.
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on the IIUG, nor any other organization with which I am
> associated either explicitly, implicitly, or by inference. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity with which I am affiliated nor those of the entities themselves.
>
> On Thu, May 26, 2016 at 12:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Hi,
> >
> > We set ,
> >
> > LOGBUFF 256> >
> > Occasionally, I still see this sometimes,
> >
> > 58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
> > 1 0 0
> > 580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
> > 2 29 48411
> > 58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
> > 1 116 14986
> >
> > So, a session is waiting for the log buffer .
> >
> > The doc ( below) seems not to recommend to set it higher or .... .
> >
> > What do you think or comments ?
> >
> > Thanks
> > Frank
> >
> > LOGBUFF configuration parameter> >
> > Use the LOGBUFF configuration parameter to specify the size in kilobytes
> > for the three logical-log buffers in shared memory.
> > onconfig.std value LOGBUFF 64 units Kilobytes values An integer in the
> > range of 32 - (32767 * pagesize / 1024), where pagesize is the default
> > system page size. The value must be evenly divisible by the default
> system
> > page size. If the value is not evenly divisible by the page size, the
> > database server rounds down the size to the nearest value that is evenly
> > divisible by the page size. takes effect After you edit your onconfig
> file
> > and restart the database server.
> > Usage
> >
> > The three logical log buffers permit user threads to write to the active
> > buffer while one of the other buffers is being flushed to disk. If
> flushing
> > is not complete by the time the active buffer fills, the user thread
> begins
> > writing to the third buffer.
> >
> > If the RTO_SERVER_RESTART configuration parameter is enabled, set the
> value
> > of the LOGBUFF configuration parameter to 256 kilobytes. If the value of
> > the LOGBUFF configuration parameter is less than 256 kilobytes, a warning
> > message displays when you restart the server.
> >
> > Otherwise, set the value of the LOGBUFF configuration parameter to 32
> > kilobytes for standard workloads or 64 kilobytes for heavy workloads. The
> > database server uses the LOGBUFF parameter to set the size of internal
> > buffers that are used during recovery. If you set LOGBUFF too high, the
> > database server can run out of memory and shut down during recovery.
> >
> > If you log user data in smart large objects, increase the size of the log
> > buffer to make the system more efficient. The database server logs only
> the
> > portion of a smart-large-object page that changed.
> >
> > You can view information about the logical log buffers by running the
> > onstat
> > -l command.> > *Parent topic:* Database configuration parameters
> >
> > <
> >
>
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0007.htm?lang=en-us
> > >
> > *Related reference*:
> > onstat -l command: Print physical and logical log information> >
> > <
> >
>
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0598.htm?lang=en-us
> > >
> >
> > --001a11c1728c309b400533c148a4
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --94eb2c05be5642dd140533c48276
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a114222c8cbb9e30533d40bc8
If you try buffered logging, remember that your average log buffer flush is
only writing 1.2pages so your transactions are fairly small so set the
logical log buffers smaller rather than larger to reduce the data in flight
and therefore at risk if you crash.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Sat, May 28, 2016 at 12:51 AM, FRANK <yunyaoqu@gmail.com> wrote:
> Thanks Art and Andrew for the info!
>
> We have a lot of small transactions , about one million per hour.
>
> So, when each COMMIT trigger a buffer flush, it would have a high
> frequent IO flush.... ( reducing this might help bigger ??)
>
> Our system is quite reliable( no frequent crash) , as Andrew
> benchmarked, buffered logging might help ... to be explored ...
>
> Thanks
> Frank
>
> On Thu, May 26, 2016 at 4:19 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > First, if your databases are UNBUFFERED or ANSI mode logged then the size
> > of the logical log buffer is not important and increasing it will not
> > improve performance at all since the buffer is flushed whenever a COMMIT
> > record is written to it not when it is filled.
> >
> > That said, even if all of your databases are BUFFERED log, the write
> > performance of the chunks containing the logical log is the biggest
> factor
> > in determining whether the logical log buffers flush quickly enough to
> > prevent sessions from having to wait for them to flush. In a BUFFERED log
> > situation, bigger logical log buffers will sometimes help here, but keep
> in
> > mind that the bigger the buffer is the more data is at risk for
> > catastrophic loss if the server crashes with many transactions still
> > unflushed. You have to balance safety with performance.
> >
> > Recommendation: Move the logical logs to a faster or less active disk
> > structure.
> >
> > Art
> >
> > Art S. Kagel, President and Principal Consultant
> > ASK Database Management
> > www.askdbmgt.com
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on the IIUG, nor any other organization with which I
> am
> > associated either explicitly, implicitly, or by inference. Neither do
> > those opinions reflect those of other individuals affiliated with any
> > entity with which I am affiliated nor those of the entities themselves.
> >
> > On Thu, May 26, 2016 at 12:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > > Hi,
> > >
> > > We set ,
> > >
> > > LOGBUFF 256> > >
> > > Occasionally, I still see this sometimes,
> > >
> > > 58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
> > > 1 0 0
> > > 580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
> > > 2 29 48411
> > > 58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
> > > 1 116 14986
> > >
> > > So, a session is waiting for the log buffer .
> > >
> > > The doc ( below) seems not to recommend to set it higher or .... .
> > >
> > > What do you think or comments ?
> > >
> > > Thanks
> > > Frank
> > >
> > > LOGBUFF configuration parameter> > >
> > > Use the LOGBUFF configuration parameter to specify the size in
> kilobytes
> > > for the three logical-log buffers in shared memory.
> > > onconfig.std value LOGBUFF 64 units Kilobytes values An integer in the
> > > range of 32 - (32767 * pagesize / 1024), where pagesize is the default
> > > system page size. The value must be evenly divisible by the default
> > system
> > > page size. If the value is not evenly divisible by the page size, the
> > > database server rounds down the size to the nearest value that is
> evenly
> > > divisible by the page size. takes effect After you edit your onconfig
> > file
> > > and restart the database server.
> > > Usage
> > >
> > > The three logical log buffers permit user threads to write to the
> active
> > > buffer while one of the other buffers is being flushed to disk. If
> > flushing
> > > is not complete by the time the active buffer fills, the user thread
> > begins
> > > writing to the third buffer.
> > >
> > > If the RTO_SERVER_RESTART configuration parameter is enabled, set the
> > value
> > > of the LOGBUFF configuration parameter to 256 kilobytes. If the value
> of
> > > the LOGBUFF configuration parameter is less than 256 kilobytes, a
> warning
> > > message displays when you restart the server.
> > >
> > > Otherwise, set the value of the LOGBUFF configuration parameter to 32
> > > kilobytes for standard workloads or 64 kilobytes for heavy workloads.
> The
> > > database server uses the LOGBUFF parameter to set the size of internal
> > > buffers that are used during recovery. If you set LOGBUFF too high, the
> > > database server can run out of memory and shut down during recovery.
> > >
> > > If you log user data in smart large objects, increase the size of the
> log
> > > buffer to make the system more efficient. The database server logs only
> > the
> > > portion of a smart-large-object page that changed.
> > >
> > > You can view information about the logical log buffers by running the
> > > onstat
> > > -l command.> > > *Parent topic:* Database configuration parameters
> > >
> > > <
> > >
> >
> >
>
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0007.htm?lang=en-us
> > > >
> > > *Related reference*:
> > > onstat -l command: Print physical and logical log information> > >
> > > <
> > >
> >
> >
>
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0598.htm?lang=en-us
> > > >
> > >
> > > --001a11c1728c309b400533c148a4
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --94eb2c05be5642dd140533c48276
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a114222c8cbb9e30533d40bc8
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--94eb2c05c48a9b4b0e0533e29106
blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px
#715FFA solid !important; padding-left:1ex !important; background-color:white
!important; } Also you might want to consider having a transaction running
periodically that is using unbuffered logging update a single row. That would
be enough to force a flush of the current log buffer and therefor set an upper
limit on the in flight data.
Sent from Yahoo Mail for iPad
On Saturday, May 28, 2016, 3:12 AM, Art Kagel <art.kagel@gmail.com> wrote:
If you try buffered logging, remember that your average log buffer flush is
only writing 1.2pages so your transactions are fairly small so set the
logical log buffers smaller rather than larger to reduce the data in flight
and therefore at risk if you crash.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
ASK Database Management
|
|
|
| | |
|
|
|
| |
ASK Database Management
Database Consulting and Support
| |
|
|
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Sat, May 28, 2016 at 12:51 AM, FRANK <yunyaoqu@gmail.com> wrote:
> Thanks Art and Andrew for the info!
>
> We have a lot of small transactions , about one million per hour.
>
> So, when each COMMIT trigger a buffer flush, it would have a high
> frequent IO flush.... ( reducing this might help bigger ??)
>
> Our system is quite reliable( no frequent crash) , as Andrew
> benchmarked, buffered logging might help ... to be explored ...
>
> Thanks
> Frank
>
> On Thu, May 26, 2016 at 4:19 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > First, if your databases are UNBUFFERED or ANSI mode logged then the size
> > of the logical log buffer is not important and increasing it will not
> > improve performance at all since the buffer is flushed whenever a COMMIT
> > record is written to it not when it is filled.
> >
> > That said, even if all of your databases are BUFFERED log, the write
> > performance of the chunks containing the logical log is the biggest
> factor
> > in determining whether the logical log buffers flush quickly enough to
> > prevent sessions from having to wait for them to flush. In a BUFFERED log
> > situation, bigger logical log buffers will sometimes help here, but keep
> in
> > mind that the bigger the buffer is the more data is at risk for
> > catastrophic loss if the server crashes with many transactions still
> > unflushed. You have to balance safety with performance.
> >
> > Recommendation: Move the logical logs to a faster or less active disk
> > structure.
> >
> > Art
> >
> > Art S. Kagel, President and Principal Consultant
> > ASK Database Management
> > http://www.askdbmgt.com
> >
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on the IIUG, nor any other organization with which I
> am
> > associated either explicitly, implicitly, or by inference. Neither do
> > those opinions reflect those of other individuals affiliated with any
> > entity with which I am affiliated nor those of the entities themselves.
> >
> > On Thu, May 26, 2016 at 12:28 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > > Hi,
> > >
> > > We set ,
> > >
> > > LOGBUFF 256> > >
> > > Occasionally, I still see this sometimes,
> > >
> > > 58087a28 Y--P--- 4082303 saapmgr - 551cf760 0
> > > 1 0 0
> > > 580882e8 G-BPX-- 3671545 saapmgr - 526d0898 0
> > > 2 29 48411
> > > 58088ba8 Y--P--- 2478091 saapmgr - 55a96230 0
> > > 1 116 14986
> > >
> > > So, a session is waiting for the log buffer .
> > >
> > > The doc ( below) seems not to recommend to set it higher or .... .
> > >
> > > What do you think or comments ?
> > >
> > > Thanks
> > > Frank
> > >
> > > LOGBUFF configuration parameter> > >
> > > Use the LOGBUFF configuration parameter to specify the size in
> kilobytes
> > > for the three logical-log buffers in shared memory.
> > > onconfig.std value LOGBUFF 64 units Kilobytes values An integer in the
> > > range of 32 - (32767 * pagesize / 1024), where pagesize is the default
> > > system page size. The value must be evenly divisible by the default
> > system
> > > page size. If the value is not evenly divisible by the page size, the
> > > database server rounds down the size to the nearest value that is
> evenly
> > > divisible by the page size. takes effect After you edit your onconfig
> > file
> > > and restart the database server.
> > > Usage
> > >
> > > The three logical log buffers permit user threads to write to the
> active
> > > buffer while one of the other buffers is being flushed to disk. If
> > flushing
> > > is not complete by the time the active buffer fills, the user thread
> > begins
> > > writing to the third buffer.
> > >
> > > If the RTO_SERVER_RESTART configuration parameter is enabled, set the
> > value
> > > of the LOGBUFF configuration parameter to 256 kilobytes. If the value
> of
> > > the LOGBUFF configuration parameter is less than 256 kilobytes, a
> warning
> > > message displays when you restart the server.
> > >
> > > Otherwise, set the value of the LOGBUFF configuration parameter to 32
> > > kilobytes for standard workloads or 64 kilobytes for heavy workloads.
> The
> > > database server uses the LOGBUFF parameter to set the size of internal
> > > buffers that are used during recovery. If you set LOGBUFF too high, the
> > > database server can run out of memory and shut down during recovery.
> > >
> > > If you log user data in smart large objects, increase the size of the
> log
> > > buffer to make the system more efficient. The database server logs only
> > the
> > > portion of a smart-large-object page that changed.
> > >
> > > You can view information about the logical log buffers by running the
> > > onstat
> > > -l command.> > > *Parent topic:* Database configuration parameters
> > >
> > > <
> > >
> >
> >
>
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0007.htm?lang=en-us
> > > >
> > > *Related reference*:
> > > onstat -l command: Print physical and logical log information> > >
> > > <
> > >
> >
> >
>
>
http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.adref.doc/ids_a
dr_0598.htm?lang=en-us
> > > >
> > >
> > > --001a11c1728c309b400533c148a4
> > >
> > >
> > >
> > >
> >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --94eb2c05be5642
As you can see on the logical logging bloack, pages/io is 1.2. So your
using, on avrerage less that 4K (if your system page size is 2K) or 8K (if
your system page size is 4K).
There's really no point in increasing the LOGBUFF.
Again, this is VERY common in OLTP systems with a reasonable number of
sessions.
Meanwhile don't think about reducing it too much... You're not really
wasting a significant amount of memory and the LOGBUFF must be large enough
to contain a checkpoint record that can be relatively large if you have a
reasonable number of sessions.
To the best of my knowledge we don't write the full size of the LOGBUFF to
disk. Only what is used.
So, again, on relatively busy OLTP systems the size of LOGBUFF is rather
irrelevant.
I believe someone mentioned using BUFFERED logging... I'm still waiting to
meet a customer that once he understands how this works considers using it.
The problem with BUFFERED is not that it will lead to database
inconsistency... That is assured in any case by fast recovery... The
problem with BUFFERED log is that an application may assume something is
done, when in fact it WON'T be.. at the time it receives the acknowledge of
the COMMIYT it IS done, but a crash may UNDO it.
From a database perspective it will be consistently rolled back to the
state before that transaction... But meanwhile you may have informed your
customer a movement was done... Or you may have sent a report to a
regulator... or you may have published that information to another system
using some message queuing etc.
As I wrote... I never seen a customer decide in favor of BUFFERED logging
after imagining the possible scenarios.
Some sort of "GROUP COMMIT" would be the solution... but it has to be
implemented in teh database server. That's the reason for the RFE I
mentioned.
Regards.
On Fri, May 27, 2016 at 3:34 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Hi, Fernando,
>
> I tried "onstat -l" a couple of times( see below for its top part. today
> might not be a busy day). I saw buffer waiting some times
> I am very interested in your paper if available!
>
> Thanks
> Frank
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:12:22 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-2 121 128 29978045 237725 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 6520 4015 0.21
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-1 0 128 1053794263 129546559 106542993 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1053523516 124822591348
>
> SBLOB 268379 40393852
>
> CDR 111 6216
>
> HA 2257 99308
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:13:07 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-1 107 128 29978941 237732 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 7416 4897 0.25
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-2 9 128 1053872795 129553345 106547469 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1053602048 124831042356
>
> SBLOB 268379 40393852
>
> CDR 111 6216
>
> HA 2257 99308
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:15:08 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-1 43 128 29985168 237783 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 13771 1826 0.09
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-3 0 128 1054240644 129578725 106557089 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1053969896 124872195792
>
> SBLOB 268379 40393852
>
> CDR 111 6216
>
> HA 2258 99352
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:15:29 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-2 34 128 29985808 237788 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 14411 2457 0.13
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-1 9 128 1054283654 129582401 106558728 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1054012906 124877581612
>
> SBLOB 268379 40393852
>
> CDR 111 6216
>
> HA 2258 99352
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:17:21 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-1 37 128 29989907 237820 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 18642 6691 0.34
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-3 0 128 1054691356 129616696 106577152 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1054399845 124920773552
>
> SBLOB 289142 47318172
>
> CDR 111 6216
>
> HA 2258 99352
> Buffer Waiting
> Buffer ioproc flags
> L-2 0 0x61 0
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:18:37 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-1 49 128 29994283 237854 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 23015 11076 0.57
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-2 0 128 1054944817 129649345 106606670 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1054653306 124947683616
>
> SBLOB 289142 47318172
>
> CDR 111 6216
>
> HA 2258 99352
> Buffer Waiting
> Buffer ioproc flags
> L-1 0 0x61 0
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:19:03 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-2 48 128 29996341 237870 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 25201 13261 0.68
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-2 0 128 1055095956 129667578 106622661 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1054804445 124964063960
>
> SBLOB 289142 47318172
>
> CDR 111 6216
>
> HA 2258 99352
>
> On Thu, May 26, 2016 at 3:18 PM, Fernando Nunes <domusonline@gmail.com>
> wrote:
>
> > "irrelevant" may be a too strong work...
> > But do a teste...run "onstat -l"
> > Somewhere near the top you'll see on average how many pages were used
> when
> > it was flushed... in busy OLTP systems this is often around 1.x... Not
> even
> > two pages.
> > Ceck your values...
> >
> > Again... for those busy jobs... if you think you could live with a
> > potential "COMMIT" that is los
Frank,
regarding my "paper".... I have written an article about this in the past
(Feb 2014):
http://informix-technology.blogspot.co.uk/search?q=buffered+logging
Regards
On Fri, May 27, 2016 at 3:34 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Hi, Fernando,
>
> I tried "onstat -l" a couple of times( see below for its top part. today
> might not be a busy day). I saw buffer waiting some times
> I am very interested in your paper if available!
>
> Thanks
> Frank
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:12:22 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-2 121 128 29978045 237725 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 6520 4015 0.21
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-1 0 128 1053794263 129546559 106542993 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1053523516 124822591348
>
> SBLOB 268379 40393852
>
> CDR 111 6216
>
> HA 2257 99308
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:13:07 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-1 107 128 29978941 237732 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 7416 4897 0.25
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-2 9 128 1053872795 129553345 106547469 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1053602048 124831042356
>
> SBLOB 268379 40393852
>
> CDR 111 6216
>
> HA 2257 99308
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:15:08 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-1 43 128 29985168 237783 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 13771 1826 0.09
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-3 0 128 1054240644 129578725 106557089 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1053969896 124872195792
>
> SBLOB 268379 40393852
>
> CDR 111 6216
>
> HA 2258 99352
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:15:29 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-2 34 128 29985808 237788 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 14411 2457 0.13
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-1 9 128 1054283654 129582401 106558728 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1054012906 124877581612
>
> SBLOB 268379 40393852
>
> CDR 111 6216
>
> HA 2258 99352
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:17:21 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-1 37 128 29989907 237820 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 18642 6691 0.34
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-3 0 128 1054691356 129616696 106577152 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1054399845 124920773552
>
> SBLOB 289142 47318172
>
> CDR 111 6216
>
> HA 2258 99352
> Buffer Waiting
> Buffer ioproc flags
> L-2 0 0x61 0
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:18:37 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-1 49 128 29994283 237854 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 23015 11076 0.57
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-2 0 128 1054944817 129649345 106606670 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1054653306 124947683616
>
> SBLOB 289142 47318172
>
> CDR 111 6216
>
> HA 2258 99352
> Buffer Waiting
> Buffer ioproc flags
> L-1 0 0x61 0
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 7 days
> 20:19:03 -- 19140800 Kbytes> Physical Logging
> Buffer bufused bufsize numpages numwrits pages/io
> P-2 48 128 29996341 237870 126.10
>
> phybegin physize phypos phyused %used
>
> 2:53 1950000 25201 13261 0.68
> Logical Logging
> Buffer bufused bufsize numrecs numpages numwrits recs/pages
> pages/io
> L-2 0 128 1055095956 129667578 106622661 8.1 1.2
>
> Subsystem numrecs Log Space used
>
> OLDRSAM 1054804445 124964063960
>
> SBLOB 289142 47318172
>
> CDR 111 6216
>
> HA 2258 99352
>
> On Thu, May 26, 2016 at 3:18 PM, Fernando Nunes <domusonline@gmail.com>
> wrote:
>
> > "irrelevant" may be a too strong work...
> > But do a teste...run "onstat -l"
> > Somewhere near the top you'll see on average how many pages were used
> when
> > it was flushed... in busy OLTP systems this is often around 1.x... Not
> even
> > two pages.
> > Ceck your values...
> >
> > Again... for those busy jobs... if you think you could live with a
> > potential "COMMIT" that is lost... and if the do many COMMITS, try to run
> > them with "SET BUFFEED LOG".
> > This would make their COMMITS NOT to force a flush....
> > I had the idea of writing an article about this... not sure if I did....
> > Time permitting I must get back to it...
> > Regards
> >
> > On Thu, May 26, 2016 at 7:26 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > > HI, Fernando,
> > >
> > > Our database is No HDR and Unbuffered logging.
> > >
> > > I think , as you suggested, I can ignore it for now.
> > >
> > > This is not happening often. It happens just sometimes with some busier
> > > update jobs involved , it did not last long in our current database.
> > > Yes, no one noticed it.
> > >
> > > We will have three times of more volumes of transactions soon, I just
> > > want to make sure it would not get worse next. By the way, we have lot
> of
> > > memory ( hundred of GBs), we can give it as much as it needs :-)
> > >
> > > This is the first time I heard the size of LOGBUFF is irrelevant for
> > > unbuffered logging, interesting ...
> > >
> > > Thanks
> > > Frank
> > >
> > > On Thu, May 26, 2016 at 1:27 PM, Fernando Nunes <domusonline@gmail.com
> >
> > > wrote:
> > >
> > > > A couple of questions:
> > > >
> > > > 1- Do you have HDR?
> > > > 2- What is the logmode of your databases? Buffered, Unbuffered?
> > > >
> > > > The reasons behind the questions are:
> > > >
> > > > - If you hae HDR, when the buffers are flushed in the primary (to
> disk)
> > > > they are also copied to the "HDR buffers". We have 12 of these but
> > > > occasionally if