 |
IDS Forum
Re: LOGBUFF size
Posted By: Fernando Nunes
Date: Wednesday, 3 August 2016, at 12:30 p.m.
In Response To: Re: LOGBUFF size (FRANK)
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 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_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
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > > > 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
> > >
> > >
> > >
> > >
> >
> >
>
> *******************************************************************************
> > > 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...
> >
> > --001a1146d40e07b6be0533c3a634
> >
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a11356f761b90d20533d3ce2c
>
>
>
> *******************************************************************************
> 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...
--001a1143e38629d88e05392d57f7
Messages In This Thread
- LOGBUFF size
FRANK -- Thursday, 26 May 2016, at 12:28 p.m.
- Re: LOGBUFF size
Fernando Nunes -- Thursday, 26 May 2016, at 1:27 p.m.
- Re: LOGBUFF size
FRANK -- Thursday, 26 May 2016, at 2:26 p.m.
- Re: LOGBUFF size
Fernando Nunes -- Thursday, 26 May 2016, at 3:18 p.m.
- RE: LOGBUFF size
Andrew Ford -- Thursday, 26 May 2016, at 3:30 p.m.
- Re: LOGBUFF size
FRANK -- Friday, 27 May 2016, at 10:34 a.m.
- Re: LOGBUFF size
Fernando Nunes -- Sunday, 29 May 2016, at 4:37 a.m.
- Re: LOGBUFF size
Fernando Nunes -- Wednesday, 3 August 2016, at 12:30 p.m.
- Re: LOGBUFF size
Art Kagel -- Thursday, 26 May 2016, at 4:19 p.m.
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
